SQLServer分页控件优化：高效分页算法详解

两个分页算法，第一个分页算法只适用于SQL Server ，第二个分页算法Access也能用。
速度还是比较快的，基本上几十毫秒就可以搞定。
至于是不是写成存储过程的形式，看个人的习惯了，
我是在程序里面组合sql语句，并且封装到了分页控件里面，这样就方便多了，尤其是在多查询条件和算法升级的时候。

上两篇随笔：
我的分页控件（未完，待续）——控件件介绍及思路

我自己写的一个分页控件（源码和演示代码）PostBack分页版 for vs2003、SQL Server

关于分页的误区
&＃160;&＃160;&＃160;&＃160;误区1：分页的时候，只有使用存储过程，效率才高。
&＃160;&＃160;&＃160;&＃160;误区2：忽略了索引的作用。

上两篇好像介绍的不太详细，这里详细说明一下分页控件里使用的分页算法，也就是SQL语句。

分页一般分为四种情况

1、单字段排序，排序字段没有重复值。
2、单字段排序，排序字段有重复值。
3、多字段排序，最后一个排序字段没有重复值。
4、多字段排序，最后一个排序字段有重复值。

其中第2、4 情况都可以再加一个排序字段（比如说主键），就可以转换成第三种情况。

所以分页针对1、3两种情况设置了两种分页算法。

1、单字段排序，排序字段没有重复值。

&＃160;&＃160;&＃160;&＃160;公式：

&＃160;1declare&＃160;@col&＃160;int
&＃160;2
&＃160;3select&＃160;top&＃160;{PageSize&＃160;*&＃160;(PageIndex-1)+1}&＃160;@col&＃160;=&＃160;[排序字段]&＃160;
&＃160;4from&＃160;[表名|视图名]&＃160;
&＃160;5[&＃160;where&＃160;查询条件&＃160;]&＃160;
&＃160;6order&＃160;by&＃160;[排序字段]&＃160;asc|desc
&＃160;7
&＃160;8select&＃160;top&＃160;PageSize&＃160;需要显示的字段&＃160;
&＃160;9from&＃160;[表名|视图名]&＃160;
10where&＃160;[排序字段]&＃160;>=&＃160;@col
11[&＃160;and&＃160;查询条件&＃160;]
12order&＃160;by&＃160;[排序字段]&＃160;asc|desc

&＃160;&＃160;&＃160;&＃160;以NorthWind 数据库里的 Products 表为例，假设一页显示10条数据，CategoryID = 3 为查询条件，按照ProductID 倒序，如果想显示第二页的数据，那么SQL语句就是
&＃160;&＃160;&＃160;&＃160;

declare&＃160;@col&＃160;int

select&＃160;top&＃160;11&＃160;@col&＃160;=&＃160;ProductID&＃160;from&＃160;Products&＃160;where&＃160;CategoryID&＃160;=&＃160;3&＃160;order&＃160;by&＃160;ProductID&＃160;desc&＃160;&＃160;&＃160;&＃160;

select&＃160;top&＃160;10&＃160;*&＃160;from&＃160;Products&＃160;where&＃160;ProductID&＃160;>=&＃160;@col&＃160;and&＃160;CategoryID&＃160;=&＃160;3&＃160;order&＃160;by&＃160;ProductID&＃160;desc&＃160;&＃160;&＃160;&＃160;

&＃160;&＃160; 说明：
&＃160;&＃160;&＃160;&＃160;第一行的定义，要根据字段类型来修改，看是比较麻烦，但是这个麻烦交给分页控件就可以了，使用者，只要设置分页控件的属性就可以了。
&＃160;&＃160;&＃160;&＃160;第五行和第十一行，如果需要加查询条件的话就可以在这里添加。

&＃160;&＃160;&＃160; 第三行是一个“定位”，这个可以算是SQL Server 所特有的吧，也是SQL Server 很宽容的地方。以Products 表的例子，执行完第一条select 语句之后， @col 里面记录的是 在CategoryID = 3&＃160;的记录里面，按照ProductID 倒序，排行在11位的记录的值。
&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;第一个select 语句定位以后，第二个select 语句就可以根据这个“位置”继续向下查找数据了。

&＃160;&＃160;&＃160;&＃160;虽然例子里面使用了ProductID（主键）来排序，但是并不是说这个算法只能用主键来排序，哪个字段都可以，但是要符合第一种情况，就是“只有一个排序字段，且排序字段里的记录没有重复值”！


3、多字段排序，最后一个排序字段没有重复值。

&＃160;&＃160;&＃160;&＃160;如果 Products 表想要用 UnitPrice 字段来排序怎么办呢？上面的算法是不适合的，我们需要使用另一种算法，这个和颠颠倒倒法有些类似，但是我做了一些优化。

&＃160;&＃160;&＃160;&＃160;公式：

select&＃160;[需要显示的字段]&＃160;from&＃160;[表名|视图名]&＃160;where&＃160;[主键字段]&＃160;in&＃160;

&＃160;&＃160;&＃160;&＃160;(&＃160;select&＃160;top&＃160;PageSize&＃160;[主键字段]&＃160;from&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;(select&＃160;top&＃160;{PageSize&＃160;*&＃160;PageIndex}&＃160;[主键字段]&＃160;,&＃160;[排序字段]&＃160;from&＃160;&＃160;&＃160;&＃160;&＃160;--有几个排序字段就写几个字段

&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[表名|视图名]&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[&＃160;where&＃160;查询条件&＃160;]&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;order&＃160;by&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[排序字段1]&＃160;asc|desc&＃160;,
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[排序字段2]&＃160;desc|asc,&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[主键字段]&＃160;asc|desc&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;)&＃160;as&＃160;aa&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;order&＃160;by&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[排序字段1]&＃160;desc|asc,&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;--如果上面是倒序，那么这里就是正序，下同
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[排序字段2]&＃160;asc|desc&＃160;,
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;[主键字段]&＃160;desc|asc&＃160;

&＃160;&＃160;&＃160;&＃160;)&＃160;

order&＃160;by&＃160;
&＃160;&＃160;&＃160;&＃160;[排序字段1]&＃160;asc|desc,&＃160;&＃160;&＃160;&＃160;--如果上面是倒序，那么这里就是正序，所谓颠颠倒倒嘛。
&＃160;&＃160;&＃160;&＃160;[排序字段2]&＃160;desc|asc,
&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;[主键字段]&＃160;asc|desc

&＃160;&＃160;&＃160;&＃160;以NorthWind 数据库里的 Products 表为例，假设一页显示10条数据，CategoryID = 3 为查询条件，按照UnitPrice 倒序，由于UnitPrice 字段可能有重复值，所以加上一个排序字段——ProductID&＃160;，即按照 UnitPrice&＃160; desc,ProductID&＃160; 来排序。&＃160;如果想显示第二页的数据，那么SQL语句就是

select&＃160;*&＃160;from&＃160;Products&＃160;where&＃160;ProductID&＃160;in&＃160;
&＃160;&＃160;&＃160;&＃160;(&＃160;select&＃160;top&＃160;10&＃160;ProductID&＃160;from&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;(select&＃160;top&＃160;20&＃160;ProductID&＃160;,&＃160;UnitPrice&＃160;from&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;Products&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;where&＃160;CategoryID&＃160;=&＃160;3
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;order&＃160;by&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;UnitPrice&＃160;desc&＃160;,
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;ProductID&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;)&＃160;as&＃160;aa&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;order&＃160;by&＃160;&＃160;&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;UnitPrice&＃160;asc,&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;--如果上面是倒序，那么这里就是正序，下同
&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;&＃160;ProductID&＃160;desc&＃160;
&＃160;&＃160;&＃160;&＃160;)&＃160;
order&＃160;by&＃160;
&＃160;&＃160;&＃160;&＃160;UnitPrice&＃160;desc,&＃160;&＃160;&＃160;&＃160;--如果上面是倒序，那么这里就是正序，所谓颠颠倒倒嘛。
&＃160;&＃160;&＃160;&＃160;ProductID&＃160;

&＃160;&＃160;&＃160;&＃160;说明：
&＃160;&＃160;&＃160;&＃160;1、这里查询条件加一次就可以了。
&＃160;&＃160;&＃160;&＃160;2、是不是看 asc|desc 倒来倒去的有点晕，恩，这就对了，颠颠倒倒嘛。
&＃160;&＃160;&＃160;&＃160;3、最主要的就是第三个select 语句，他要取从第一条数据到要显示的页的数据，可见越是后面的记录，top&＃160;n 就会越大，所以这里提取的数据就要做一个精简，只写排序需要的字段（主键字段和排序字段）。
&＃160;&＃160;&＃160;&＃160;4、第二个select 语句是去掉前面不需要的页里的数据，只保留要显示的页号里的数据。
&＃160;&＃160;&＃160;&＃160;5、第一个select 语句，用主键字段 in () 的方式提取其他需要的字段。
&＃160;&＃160;&＃160;&＃160;6、这种分页算法有一个小的bug，就是显示最后一页数据的时候，会多出来几条记录，不过这个bug已经在分页控件里面修正了，最后一页的分页算法，采用特殊的select语句。
&＃160;&＃160;&＃160;&＃160;7、效率，设置好索引，效率是没有问题的，上一篇随笔已经测试过了。
&＃160;&＃160;&＃160;&＃160;8、这种算法有一个“侵入性”，就是要求表必须有主键，而且不能是联合主键，引为要用 in 的方式查询数据。但是并没有要求主键自身必须能够排序。

测试效果
记录数：2523136条。
一页显示5条记录。

//分页算法1 单字段排序，且排序字段是聚集索引。
&＃160;&＃160;&＃160;//1000 页以内 15毫秒
&＃160;&＃160;&＃160;//10000页以内 30毫秒
&＃160;&＃160;&＃160;//50000页以内 100多毫秒
&＃160;&＃160;&＃160;//100000页以内 200多毫秒
&＃160;&＃160;&＃160;//最后几页 第一次跳转到 4秒多
&＃160;&＃160;&＃160;//最后几页 连续向前翻页 1秒156毫秒

&＃160;&＃160;&＃160;//页号大范围跳转的时候需要的时间比较长，但是也小于1秒，同时SQL Server 占用的内存有所增加 120M。最后几页时达到320M

===================================================================
以下是多排序字段的分页情况，排序字段是 UnitPrice,ProductID&＃160;&＃160;

&＃160;&＃160;&＃160;//分页算法2 无索引&＃160; 首页 8秒187毫秒&＃160;。&＃160;
&＃160;&＃160;&＃160;//10 页以内 2秒812毫秒
&＃160;&＃160;&＃160;//速度太慢下面的就不测试了

&＃160;&＃160;&＃160;//分页2 非聚集索引 UnitPrice&＃160; 首页 468毫秒
&＃160;&＃160;&＃160;//10 页以内 2秒671毫秒
&＃160;&＃160;&＃160;//速度太慢下面的就不测试了

&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;//分页算法2 非聚集索引 UnitPrice,ProductID&＃160; 首页 500毫秒
&＃160;&＃160;&＃160;//10 页以内 2秒796毫秒
&＃160;&＃160;&＃160;//100页以内 4秒796毫秒
&＃160;&＃160;&＃160;//速度太慢下面的就不测试了

&＃160;&＃160;&＃160;
&＃160;&＃160;&＃160;//分页算法2 非聚集索引 UnitPrice,ProductID desc&＃160; 首页 500毫秒
&＃160;&＃160;&＃160;//10 页以内 0-15毫秒
&＃160;&＃160;&＃160;//100页以内 15-46毫秒
&＃160;&＃160;&＃160;//1000页以内 31-62毫秒
&＃160;&＃160;&＃160;//10000页以内 100毫秒左右
&＃160;&＃160;&＃160;//50000页以内 400-500毫秒
&＃160;&＃160;&＃160;//100000页以内 900毫秒左右
&＃160;&＃160;&＃160;//最后几页 第一次跳转到 4秒421毫秒
&＃160;&＃160;&＃160;//最后几页 连续向前翻页 4秒375毫秒

&＃160;&＃160;&＃160;//页号大范围跳转的时候需要的时间比较长，但是也小于1秒，
&＃160;&＃160;&＃160;//这回SQL Server 占用的内存增加幅度不大 120M左右