关于sqlserver 分页问题

以前看网上不少人的说ROW_NUMBER在处理分页的时候效率很高，代替了TOP

上午你说过之后我又做了查看和分析，看了一些资料。做了如下分析：

 

1、  ROW_NUMBER的原理决定了他和 双TOP主键方式是要取同样的page，都是按顺序扫描，知道最后所需要的。所以两者差距并不大，

 

2、  如果翻页多了，在后面的页，ROW_NUMBER 还是对整个表扫描，但是 双TOP就可能逆序来扫描。就会比ROW_NUMBER更优

 

3、  我们平时用的最普遍的ROW_NUMBER 写法是错误的。并没有达到我们想要的结果

 

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int

SELECT @START_ROW = 1, @MAX_ROWS = 25;

WITH PAGED AS (

SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, *

FROM TRANS_TABLE(NOLOCK)

)

SELECT *

FROM PAGED

WHERE ROWNUM BETWEEN@START_ROW AND @START_ROW + @MAX_ROWS - 1

 

常用的那个方法执行计划经常是表扫描，因为附带了其它的多余的列

改用取主键值，然后JOIN才会更高效的利用索引

 

DECLARE @START_ROW int, @MAX_ROWS int, @TOT_ROW_CNT int

SELECT @START_ROW = 1, @MAX_ROWS = 25;

WITH PAGED AS (

SELECT ROW_NUMBER() OVER(ORDER BY MyDate, MYID) AS rowNum, MYID

FROM TRANS_TABLE(NOLOCK)

)

SELECT TT.*

FROM PAGED PGD

INNER JOIN TRANS_TABLE TT

ON PGD.MYID = TT.MYID

WHERE ROWNUM BETWEEN @START_ROW AND @START_ROW + @MAX_ROWS - 1

ORDER BY MyDate, MYID

 

 

4、  然后说下TOP，通常TOP是有三种写法（假设id列上有聚集索引）

--1

SELECT TOP 10 * FROM tb WHERE id NOT IN(SELECT TOP 20 id FROM tb ORDER BY id)  ORDER BY id

--2

SELECT * FROM(SELECT TOP 10 * FROM (SELECT TOP 20 id FROM tb ORDER BY id)a  ORDER BY id DESC)b ORDER BY id

--3

SELECT TOP 10 * FROM tb WHERE id>(

                           SELECT ISNULL(max(id),0) FROM

                               (SELECT TOP 20 idFROM tb ORDER BY id) t

                        )

 

其中2最差，因为要反复排序，1次之，3最优，开销仅是在查找id，然后就使用INDEX SEEK

 

5、  另外一个疑问，如果数据量大了，先对总页数进行判断，然后从数据中间开始，

如果需要的的页在前面 就用正双向TOP，如果在后面就用反向TOP取最小值id

就是把上面的3改成

SELECT TOP 10 *FROM Stock WHERE id>(

                           SELECT ISNULL(min(id),0) FROM

                               (SELECT TOP 20/*这里的在实际环境中需要计算得到*/ id FROM Stock ORDER BY id desc) t

                        )

 

如果是存储过程把 SELECT ISNULL(max(id),0) FROM

                               (SELECT TOP 20 idFROM tb ORDER BY id) t

这一段分出来写效果会更好。