关于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 PAGEDAS (

SELECT ROW_NUMBER() OVER(ORDERBY 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 PAGEDAS (

SELECT ROW_NUMBER() OVER(ORDERBY MyDate,MYID) AS rowNum,MYID

FROM TRANS_TABLE(NOLOCK)

)

SELECT TT.*

FROM PAGEDPGD

INNER JOINTRANS_TABLE TT

ON PGD.MYID= TT.MYID

WHERE ROWNUMBETWEEN @START_ROWAND @START_ROW + @MAX_ROWS - 1

ORDER BY MyDate, MYID

 

 

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

--1

SELECT TOP 10* FROM tb WHERE idNOT IN(SELECTTOP 20 id FROM tb ORDERBY id) ORDER BY id

--2

SELECT *FROM(SELECTTOP 10 * FROM (SELECTTOP 20 id FROM tb ORDERBY id)a ORDER BY id DESC)bORDER BY id

--3

SELECT TOP 10* FROM tb WHERE id>(

                           SELECTISNULL(max(id),0)FROM

                               (SELECTTOP 20 idFROMtb ORDER BY id) t

                        )

 

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

 

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

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

就是把上面的3改成

SELECT TOP 10*FROM Stock WHERE id>(

                           SELECTISNULL(min(id),0)FROM

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

                        )

 

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

                               (SELECTTOP 20 idFROMtb ORDER BY id) t

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