第二个sql改成如下这个也一样: SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY art_ordertime) AS rows,* FROM article) t WHERE rows >1000000 and rows <1000100也就是两个sql的区别就是 select * 和 select art_id的区别
select * from (select ROW_NUMBER() over(order by Art_OrderTime) as rows,* from Article) t where rows>1000000 and rows<10000100 你为什么不直接这样查呢切入正题 你的第二个语句,进行了两次表扫描加排序,而且数量级是100W级别的 SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Art_OrderTime) AS rows,* FROM (select TOP 1000100 * from article order by Art_OrderTime) t) s WHERE rows >1000000
这个还是分析执行计划吧,光从语句上看,是不是得出结论ROW_NUMBER做分页效率高?
我个人理解是这样:第一条语句,最内层的子查询“select ROW_NUMBER() over(order by Art_OrderTime) as rows,Art_Id from Article”在获取所有Art_Id后,按照ROW_NUMBER的结果进行筛选,到最后与原表JOIN时(即最外层的select * from article where art_id in),记录数已经被大大减少了,所以只输出了“where rows>1000000 and rows<1000100”这个范围内仅仅100条记录的所有字段。第二条语句,内层的“SELECT ROW_NUMBER() OVER(ORDER BY Art_OrderTime) AS rows,* FROM (select TOP 1000100 * from article order by Art_OrderTime) t”,是用未经筛选的Art_ID与原表JOIN,相当于把表中所有记录的所有字段都读取出来,开销可想而知。然后,再按照ROW_NUMBER的结果进行筛选,虽然逻辑上是正确的,但耗费了大量的IO。另外,我比较好奇,为啥会是Index Scan,IX_OrderTime这个索引中包含Art_Id列么?
本帖最后由 DBA_Huangzj 于 2015-03-12 17:10:00 编辑
第二个执行计划的sql语句跟上面的不一样,再发第一个执行计划看看。
前三张图是第一个的 后面的是第二个的我感觉是列数的原因你试下,这个语句的速度,句式和第二一列,但只取了主要列SELECT art_id FROM (SELECT ROW_NUMBER() OVER(ORDER BY Art_OrderTime) AS rows,art_id FROM (select TOP 1000100 art_id,Art_OrderTime from article order by Art_OrderTime) t) s WHERE rows >1000000
前三张图是第一个的 后面的是第二个的我感觉是列数的原因你试下,这个语句的速度,句式和第二一列,但只取了主要列SELECT art_id FROM (SELECT ROW_NUMBER() OVER(ORDER BY Art_OrderTime) AS rows,art_id FROM (select TOP 1000100 art_id,Art_OrderTime from article order by Art_OrderTime) t) s WHERE rows >1000000这个语句是会快点 但如果把Art_Id换成Art_Code 一样很慢 Art_Code建索引了也 而且是唯一的 唯一的不同是不是主键
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY art_ordertime) AS rows,* FROM article) t
WHERE rows >1000000 and rows <1000100也就是两个sql的区别就是 select * 和 select art_id的区别
(select ROW_NUMBER() over(order by Art_OrderTime) as rows,* from Article) t
where rows>1000000 and rows<10000100
你为什么不直接这样查呢切入正题
你的第二个语句,进行了两次表扫描加排序,而且数量级是100W级别的
SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Art_OrderTime) AS rows,* FROM
(select TOP 1000100 *
from article order by Art_OrderTime) t) s WHERE rows >1000000
(SELECT ROW_NUMBER() OVER(ORDER BY Art_OrderTime) AS rows,art_id FROM
(select TOP 1000100 art_id,Art_OrderTime
from article order by Art_OrderTime) t) s WHERE rows >1000000
(SELECT ROW_NUMBER() OVER(ORDER BY Art_OrderTime) AS rows,art_id FROM
(select TOP 1000100 art_id,Art_OrderTime
from article order by Art_OrderTime) t) s WHERE rows >1000000这个语句是会快点 但如果把Art_Id换成Art_Code 一样很慢 Art_Code建索引了也 而且是唯一的 唯一的不同是不是主键
总觉得第二个语句是取出1000100行数据进行排序,
select TOP 1000100 * from article order by Art_OrderTime而第一个语句只是取出一列Art_Id 来参与排序,再取100行数据。
所以第一个处理的数据比较少。