关于分页查询的疑惑

以前发在CSDN的一篇blog，但是没有人回复。这是我一直疑惑的问题。原文如下：
http://blog.csdn.net/catstiger/archive/2006/08/31/1148015.aspx

分页查询是经常能够遇到的问题，我们首先看看分页查询存在的理由:

方便用户：用户不可能一次察看所有数据，所以一页一页的翻看比较好。
提高性能：一次从数据库中提取所有数据会比较慢。

那么现在我来尝试反驳上述理由：

真的方便吗？我们考虑下面的情况
[list=]如果数据只有20条。
如果数据超过1000条。[/list]
第一种显然不必分页查询。奇怪的是第二种也不必，因为没有哪个用户愿意一页一页的翻到最后，如果用户查询到的数据超过了他所关心的数据范围，我认为应该让他重新输入查询条件，就像我们使用google一样。
但是作为一个友好的应用界面，我们总是希望用户可以全面的了解他的查询结果，所以有必要告诉用户：“你查到了多少数据，但是，目前只能显示前1000条，如果您希望察看所有数据，那么应该如何如何... ”
性能会提高吗？
如果数据量很小，显然性能不会有明显的提升，相反，性能会大大下降。因为数据库执行了不必要的查询和查询条件。
如果数据量很大，性能也不见得有明显提升，因为你总是要执行一个额外的count查询。当然，这要看实际情况了。
可以想像，分页查询对于性能的影响和数据量之间的关系应该是一个曲线，数据量小的时候会降低性能，数据量大的时候可能（根据不同的数据库）会提升性能。关键是通过测试，找到曲线的拐点。性能不是根据经验和感觉得到的，而是通过测试得到的
另外，如果一次全部取出数据，的确会造成空间性能的影响，但是，现在内存很便宜...
分页查询的负面影响
对于一个架构良好的web应用，将pageNo和PageSize在各个类之间传递实在是不爽，这两个数据明显属于表现层。
分页查询还会明显提高编程复杂度。
奇怪的现象：为什么没有一个大型数据库直接提供分页查询？Oracle的RowNo不是用于分页的，SQLServer的Top更不是。
结论
ExtremeTable、DisplayTag、JSF DataTable都提供了简单的分页方式，那就是在结果集合中分页。使用非常方便，而且使得逻辑清晰，大大提高了工作效率。绝大多数情况下，可以直接使用这种方式。
如果通过测试，发现上述方式影响了性能，那么考虑使用分页查询。
对于用户量很大的应用，因为内存的原因，也可以考虑分页查询。但是，我个人更推荐缓存方式：同样的查询放在一个缓存中...
采用合理的设计，屏蔽开发人员处理分页逻辑。比如，将分页逻辑和count查询放在父类，开发人员负责组合查询条件。具体看设计模式吧。
欢迎大家讨论！！！

评论

38 楼 kimfly 2006-10-27  

对记录缓存存在疑问，这样的策略好像不太适合实际情况。用户数据更改后一直看不到变化。
现在存在的另一个问题是如果用户修改记录后分页的页数变了，如何定位当前页呢。

37 楼 it_weigang 2006-10-27  

     

36 楼 dwangel 2006-10-26  

cats_tiger 写道

引用

除了session里面不放大量东西这个常识以外

从来也不会把数据忘session中缓存的，缓存方案很多，这个最烂。

引用

count的影响确实不大。重建索引就可以。200万的一个表中，没建索引之前，count需要20多秒，建了索引，0.n秒就可以了。

关键问题就在这里，你不能保证每个查询都走索引，尤其是你不能保证每个人都能做到这样。不走索引，任何查询都慢。
还有你说分页没有什么值得讨论的，我也不同意。任何好的思路都有使用范围，我就是希望能够证实我所总结的这个范围是正确的。

正因为不是每个人查询都能够做到充分利用索引，所以分页查询里就涉及到对数据的分析和掌握。
那么，数据库不直接提供分页功能也就比较合理了。
因为，提供了分页，却不能保证性能。
要性能就要用户自行调优，这就是用户应用层面的东西了。

35 楼 smilelee74 2006-10-26  

如果用户输入一个查询条件得到10万条数据，你不分页难道等死啊
我们来看看结果。
分页，只返回10条记录，用户感觉很爽，这么快就得到答案了。
不分页，返回10万条记录，用户可以重启系统了。。。

34 楼 叶子 2006-10-25  

刚特意插了102w记录(mssql)

翻页到1000000记录时候的分页存储过程执行时间 29XX ms

count 102w记录的  是15XX ms

字段就三个
id,name,pass

count和分页条件都是空,排序空.
id主键.name索引.

33 楼 抛出异常的爱 2006-10-25  

人身攻击的不要.....

分页有传统方式
传统方式有以下好处:
1好用
2稳定
3教的快
4对于海量数据(我见过10W数据的程序)可达到目的


新的想法有什么好处还不很清楚
但没人尝试怎么才会进步呢?

32 楼 cats_tiger 2006-10-25  

引用

除了session里面不放大量东西这个常识以外

从来也不会把数据忘session中缓存的，缓存方案很多，这个最烂。

引用

count的影响确实不大。重建索引就可以。200万的一个表中，没建索引之前，count需要20多秒，建了索引，0.n秒就可以了。

关键问题就在这里，你不能保证每个查询都走索引，尤其是你不能保证每个人都能做到这样。不走索引，任何查询都慢。
还有你说分页没有什么值得讨论的，我也不同意。任何好的思路都有使用范围，我就是希望能够证实我所总结的这个范围是正确的。

31 楼 kabbesy 2006-10-25  

Lucas Lee 写道

你的疑问很好。

BTW,我不使用缓存的原因，并不仅是因为性能的考虑，而主要是:
1)你将几万甚至几十万的记录缓存在session里，(应该是放在这里吧?),你不觉得session里放大量东西会严重影响性能么？这个应该算是常识吧。
2)，你什么时候更新缓存？用户想看到自己或其他用户添加、更改的内容，你用什么逻辑去更新？是否要考虑线程安全性？深入点，你会发现实际所有使用缓存的方法都会有类似的问题，所以特别是在数据库应用中，我会对数据内容缓存(非配置数据)持非常谨慎的态度。

而对于性能来说，我的一个主要观点是：
(我的假设，对一般情况而言)如果用户发现数据非常大，在一般情况下，不会依次查看每页记录，而会更改查询条件或排序方式，使结果更容易读。如果我的假设成立，那么显然，将所有记录都load到缓存里的方法，将有大部分的内容是不会用到的。所以，以前有些数据库查询的设计是，超过一千条的记录，只显示前一千条，和总记录数，这种设计，不是没有道理的，但显然数据库分页的方式比这种更好些，而且保持了类似的性能。

除了session里面不放大量东西这个常识以外
还应该有一个常识：缓存一切可以缓存的资源。

30 楼 together 2006-10-25  

absolute虽然这个方法每个jdbc驱动都有，但是实现的具体方法和效率是不一样的。
count的影响确实不大。重建索引就可以。200万的一个表中，没建索引之前，count需要20多秒，建了索引，0.n秒就可以了。

不过如果真是百八十万条以上的分页，还是先让用户输入查询条件更为合适。或者默认限定查询条件。

29 楼 叶子 2006-10-25  

together 写道

count查询对性能影响并不大，而且数据库本身可以缓存SQL的。

cats_tiger 写道

PS.很多用户要求根据任意列排序（就像C/S方式），这个需求和分页结合起来也很麻烦。如果直接采用Taglib提供的功能就简单多了。

这个实现起来也很easy，并没有什么难的。传几个参数而已。按任意列排序和模糊查询都非常容易实现的

另：分页，是一个不需要讨论的话题。就像为什么要OO，为什么要有软件工程一样。

count影响不大?

mssql里 无聊把23w ip库的记录insert到100w

count下,时间很恐怖的,比top翻最后一页所花时间都多.

28 楼 LucasLee 2006-10-25  

together 写道

叶子 写道

这个count可以在条件不变情况下缓存,比如bbs就可以把某个板块的帖子数目存板块表里面
sql2005有朋友使用了,不过没听他们说过有分页函数(也有可能他们还没摸那么深).
不过不能不说,mssql在分页上确实有点混蛋:(

count缓存也是不太保险的，实现起来容易，但要保证数据的完全一致，有点困难。
这个倒是冤枉了mssql。虽然它没有像oracle/mysql提供内置的分页方法，但是用游标定位的方式，还是可以快速的实现分页的。在asp中，ADO提供了resultset的PageSize和AbsolutePage函数，也是采用游标定位。假如你要每页显示30条，定位到第2页，这样写就可以实现：rs.PageSize=30  rs.AbsolutePage=2。

传说sqlserver2005提供了内置分页功能，这是真的。2005中也像oracle一样有rownum，它的分页方式和oracle采用rownum分页语句很相似。

你说的这个游标方式是数据库通用的，等同于JDBC里的Result.absolute()方法，一样，也是通用的。
但这个性能比数据库专有的方法要慢。
有的时候可以结合两种方法来实现，比如对于SQLSERVER2000，
在SQL中使用top来限制一部分记录，比如你要显示100--120记录，则select top 120,然后再使用Result.absolute()。

27 楼 together 2006-10-25  

count查询对性能影响并不大，而且数据库本身可以缓存SQL的。

cats_tiger 写道

PS.很多用户要求根据任意列排序（就像C/S方式），这个需求和分页结合起来也很麻烦。如果直接采用Taglib提供的功能就简单多了。

这个实现起来也很easy，并没有什么难的。传几个参数而已。按任意列排序和模糊查询都非常容易实现的

另：分页，是一个不需要讨论的话题。就像为什么要OO，为什么要有软件工程一样。

26 楼 robbin 2006-10-25  

cats_tiger 写道

正是因为一个多余的count查询，所以才感觉分页查询不见得能够提高性能。当然，性能必须通过测试获得，凭感觉和经验是不行滴。我所接触的项目中，多数表可以预测一个大概的记录总数。如果记录总数<10k,我们就不采用分页查询了，而是利用extremeTable、DisplayTag或Tomahawk DataTable之类的taglib来分页。
PS.很多用户要求根据任意列排序（就像C/S方式），这个需求和分页结合起来也很麻烦。如果直接采用Taglib提供的功能就简单多了。
另外，用absolut定位光标的方式和用List.subList的方式不知道哪个更快。

引用

BTW:Microsoft从来都是针对最终消费者的体验好，针对程序员，就难说了。

MS还是很能体会程序员的辛苦的，看看.Net IDE，Java这么多IDE没有一个比得上。

Visual Studio在代码功能上和Eclipse相比，差的太遥远了。

http://www.cnblogs.com/dflying/archive/2006/10/20/535210.html

25 楼 cats_tiger 2006-10-25  

正是因为一个多余的count查询，所以才感觉分页查询不见得能够提高性能。当然，性能必须通过测试获得，凭感觉和经验是不行滴。我所接触的项目中，多数表可以预测一个大概的记录总数。如果记录总数<10k,我们就不采用分页查询了，而是利用extremeTable、DisplayTag或Tomahawk DataTable之类的taglib来分页。
PS.很多用户要求根据任意列排序（就像C/S方式），这个需求和分页结合起来也很麻烦。如果直接采用Taglib提供的功能就简单多了。
另外，用absolut定位光标的方式和用List.subList的方式不知道哪个更快。

引用

BTW:Microsoft从来都是针对最终消费者的体验好，针对程序员，就难说了。

MS还是很能体会程序员的辛苦的，看看.Net IDE，Java这么多IDE没有一个比得上。

24 楼 叶子 2006-10-25  

together 写道

叶子 写道

这个count可以在条件不变情况下缓存,比如bbs就可以把某个板块的帖子数目存板块表里面
sql2005有朋友使用了,不过没听他们说过有分页函数(也有可能他们还没摸那么深).
不过不能不说,mssql在分页上确实有点混蛋:(

count缓存也是不太保险的，实现起来容易，但要保证数据的完全一致，有点困难。
这个倒是冤枉了mssql。虽然它没有像oracle/mysql提供内置的分页方法，但是用游标定位的方式，还是可以快速的实现分页的。在asp中，ADO提供了resultset的PageSize和AbsolutePage函数，也是采用游标定位。假如你要每页显示30条，定位到第2页，这样写就可以实现：rs.PageSize=30  rs.AbsolutePage=2。

传说sqlserver2005提供了内置分页功能，这是真的。2005中也像oracle一样有rownum，它的分页方式和oracle采用rownum分页语句很相似。

是不太保险,需要自己考虑有数据更改情况下更新.

^_^,偶就是写asp出家的,对这个很熟悉.游标的性能,也就那么回事吧.

AbsolutePosition,AbsolutePage对ac是速度最快的,不过对mssql是噩梦.

另扯一句,asp里缓存count,对ac AbsolutePosition,对mssql用top ,之后再getrows,速度是最快滴.

23 楼 together 2006-10-25  

叶子 写道

这个count可以在条件不变情况下缓存,比如bbs就可以把某个板块的帖子数目存板块表里面
sql2005有朋友使用了,不过没听他们说过有分页函数(也有可能他们还没摸那么深).
不过不能不说,mssql在分页上确实有点混蛋:(

count缓存也是不太保险的，实现起来容易，但要保证数据的完全一致，有点困难。
这个倒是冤枉了mssql。虽然它没有像oracle/mysql提供内置的分页方法，但是用游标定位的方式，还是可以快速的实现分页的。在asp中，ADO提供了resultset的PageSize和AbsolutePage函数，也是采用游标定位。假如你要每页显示30条，定位到第2页，这样写就可以实现：rs.PageSize=30  rs.AbsolutePage=2。

传说sqlserver2005提供了内置分页功能，这是真的。2005中也像oracle一样有rownum，它的分页方式和oracle采用rownum分页语句很相似。

22 楼 叶子 2006-10-25  

更正:

刚google了下,mssql2005确实有ROW_NUMBER来解决分页问题的.

demo语句:

select threadid from (select threadid, ROW_NUMBER() OVER (order by stickydate) as Pos from cs_threads) as T where T.Pos > 100000 and T.Pos < 100030

21 楼 叶子 2006-10-25  

cats_tiger 写道

引用 写道

分页很简单啊，没有多运行什么所谓的查询条件，只是当查询出等于pageSize的数量时暂停查询而已啊，已经查出来的数据会放在数据库缓存中，等再次查询时，也可以提高数据库缓存命中率。不能理解性能如何大大下降啊。

我所说的“额外的查询”是指那个count查询，如果不加查询条件，count是很快的，如果加查询条件并且不走索引，count查询就很慢了。多数分页查询，都要执行一个count查询吧，它不会影响性能吗？

这个count可以在条件不变情况下缓存,比如bbs就可以把某个板块的帖子数目存板块表里面

sql2005有朋友使用了,不过没听他们说过有分页函数(也有可能他们还没摸那么深).

不过不能不说,mssql在分页上确实有点混蛋:(

20 楼 robbin 2006-10-25  

cats_tiger 写道

引用

这并不奇怪。
只能说楼主没有仔细想过数据库查询的基本方法。
一个简单的两个表的inner join，如果要分页，就需要首先得到整个结果集的数量。而仅仅通过top, limit限定范围，则只需要达到限制的数量就可以了。 top 和 limit实现，要比分页简单多了。
所以数据库设计者，是将分页交给用户来作处理而已。
不实现分页不是说分页没有用。

还是很奇怪呀，分页的确比limit复杂，正因为如此，数据库设计者才不应该把这个工作交给用户。SQLServer压根就没有为分页查询提供可能的技术，Oracle好歹还有一个ROW_NUM,SQLServer的top...唉！这种情况显然与MS的一贯做法不同，MS向来重视客户体验。
实际上，对于数据库设计者来说，提供一个分页功能实在是太简单了。

传言SQL Server 2005提供了分页SQL，不知道是不是真的。

SQL Server可以使用sp_系统存储过程来移动游标实现数据库端分页。

BTW:Microsoft从来都是针对最终消费者的体验好，针对程序员，就难说了。

19 楼 cats_tiger 2006-10-25  

引用

这并不奇怪。
只能说楼主没有仔细想过数据库查询的基本方法。
一个简单的两个表的inner join，如果要分页，就需要首先得到整个结果集的数量。而仅仅通过top, limit限定范围，则只需要达到限制的数量就可以了。 top 和 limit实现，要比分页简单多了。
所以数据库设计者，是将分页交给用户来作处理而已。
不实现分页不是说分页没有用。

还是很奇怪呀，分页的确比limit复杂，正因为如此，数据库设计者才不应该把这个工作交给用户。SQLServer压根就没有为分页查询提供可能的技术，Oracle好歹还有一个ROW_NUM,SQLServer的top...唉！这种情况显然与MS的一贯做法不同，MS向来重视客户体验。
实际上，对于数据库设计者来说，提供一个分页功能实在是太简单了。