SQL2005中50万的数据，下面这个分页SQL语句执行的效率为何这么低？

Question

在视图View_Product表中有大约50万的纪录，现在要求对该表进行分页显示，代码如下：
SELECT *
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, 
     *
      FROM view_product  WHERE isPassAudit='true' and pagerRank0>0 ) AS D
    WHERE isPassAudit='true' and pagerRank0>0  AND rownum BETWEEN 159985 AND 160000 ORDER BY pagerrank0 DESC,releaseTime desc其中记录集是数据库中第159985到160000条的纪录，我已经对视图View_product中建立了pagerrank0,releasetime的联合降序索引。
但这个SQL语句的执行效率还是比较低，并且跟所访问的纪录范围有关，越是后面的纪录，执行的速度越慢。
我通过set staticstic io on　查看了执行的信息，显示如下：
(16 行受影响)
表 'View_Product'。扫描计数 1，逻辑读取 518171 次，物理读取 1140 次，预读 47800 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
并且发现要读取的纪录越往后，逻辑读取的次数越多。
但是同样的SQL语句，在第二次进行运行时，运行的效率大幅度提高，显示的信息如下：
(16 行受影响)
表 'View_Product'。扫描计数 1，逻辑读取 490452 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。请大家帮忙看下，看主要的问题所在

Answer 1

外面的isPassAudit='true' and pagerRank0>0 没必要

Answer 2

外面的ORDER BY pagerrank0 DESC,releaseTime desc 感觉你是要 order by rownum 其他的就不好优化了
第二次是因为系统缓存了执行计划 会比第一次快

Answer 3

好像以前见过,第二个那个isPassAudit='true' and pagerRank0>0应该没必要了,
在前面的查询表中已经指定了,
加了索引还这么慢????

Answer 4

SELECT * FROM 
(
  SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, * FROM view_product  WHERE isPassAudit='true' and pagerRank0>0 
) AS D
WHERE rownum BETWEEN 159985 AND 160000 
ORDER BY pagerrank0 DESC , releaseTime desc
需要对pagerrank0,releaseTime加索引.

Answer 5

表 'View_Product'。扫描计数 1，逻辑读取 518171 次，物理读取 1140 次，预读 47800 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 看这个应该是没加索引。
你在查询条件字段加索引试试。

Answer 6

SELECT *
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, 
     *
      FROM view_product  WHERE isPassAudit='true' and pagerRank0>0 ) AS D
    WHERE rownum BETWEEN 159985 AND 160000 ORDER BY rownum 
应该和你的意思差不多吧

Answer 7

对 pagerrank0,releasetime 建立索引，只是避免了排序工作，在查询时一样用 index scan。
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, *
      FROM view_product) AS D
WHERE isPassAudit='true' and pagerRank0>0  AND rownum BETWEEN 159985 AND 160000 如果纯粹从语句优化的角度，应该将子查询的结果放入中间表，在表上为 rownum 建聚集索引。

Answer 8

我刚才看了下SQL所占的内存，尽然要1G，操作系统刚启动时，SQL只占用了80M，但我的SQL分页查询第一次执行后SQL所占内存要到1G，而且第一次查询速度很慢。第二次再查询时，无论我查询哪个范围的纪录，速度都很快，我想可能是SQL把整个数据库都放入到内存的原因吧。现在请各位看下如何提高第一次查询的速度，我把查询的执行计划和所显示的消息信息给大家看下：
执行计划：
显示的信息：

Answer 9

是将 SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum,* FROM view_product 子查询的结果放入的缓存。
如果可能应该将其放入临时表，在表上对 rownum 列键聚集索引，可以大大提升查询速度。
但是，如果你不打算反复执行这样的查询，就没有必要这么做了。

Answer 10

你可以从比较第一和第二次的执行计划中看到，有多少物理读、预读，还有前后的内存使用情况的变化。第二次执行快的原因主要在缓存上。在视图上建立索引，只是避免了排序工作，在 where 子句中定义的条件是不会让优化器使用索引的，在查询时一样用表扫描。 如果可能的话，在创建视图时就加入标示列，这样便不需要再使用子查询，可能会大大提高查询速度。

Answer 11

我的意思是，既然你建立了视图，为什么不在视图定义中加入分页所需的标示列？然后在视图上建索引。这样查询就可以直接引用索引视图了。也可以通过 where 子句中定义的条件使用索引加速查询了。

Answer 12

50万的记录第1次查询应该更快 ，当然第2次都是接近0s完成。注意：SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, * 
      FROM view_product 这个子查询是全表全字段查询 ，因为要分页，不用临时表的话全表查询无法避免，优化的空间就在全字段上，我推测你占用1个G的内存字段不会少，先查询出主键记录，然后和主表做关联查询。自己先测试一下吧。如果第1次的查询还在5s以上就联系我吧。

Answer 13

 估计是你的索引没起到作用吧，
因为视图和索引一起的话，索引会有小几率失效的。
如果能把视图去掉用原sql的话，可以试下 看看

Answer 14

  FROM view_product  WHERE isPassAudit='true' and pagerRank0>0 ) AS D
    WHERE rownum BETWEEN 159985 AND 160000 AND  isPassAudit='true' and pagerRank0>0  ORDER BY pagerrank0 数据效果最高的那个条件要放到左边,对sql来说,oracle是从右到左

Answer 15

-- pagerrank0，releaseTime两个字段要求聚集索引覆盖
SELECT b.rownum,a.* 
   FROM view_product a,
(SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, 
     pagerrank0,releaseTime
      FROM view_product  WHERE isPassAudit='true' and pagerRank0>0 ) AS b
    WHERE b.pagerrank0=a.rownum and b.releaseTime=a.releaseTime 
AND b.rownum BETWEEN 159985 AND 160000 
ORDER BY b.rownum

Answer 16

运行时提示错误：
消息 207，级别 16，状态 1，第 6 行
列名 'rownum' 无效。

Answer 17

SELECT b.rownum,a.* 
  FROM view_product a, 
(SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, 
    pagerrank0,releaseTime 
      FROM view_product  WHERE isPassAudit='true' and pagerRank0>0 )  b 
    WHERE b.pagerrank0=a.rownum and b.releaseTime=a.releaseTime 
AND b.rownum BETWEEN 159985 AND 160000 
ORDER BY b.rownum 

Answer 18

没有库无法测试 大意了 ，你先测试一下 ，如果SELECT ROW_NUMBER() OVER(ORDER BY pagerrank0 DESC,releaseTime desc) AS rownum, 
    pagerrank0,releaseTime 
      FROM view_product  WHERE isPassAudit='true' and pagerRank0>0  生成临时表 ，在rownum上聚集的话 会更好，如前面那位仁兄说的。

Answer 19

当查前几页的时候.很快.而很后的页的时候.会很慢.很多分页都存在这个问题..
调整一下你的业务逻辑..
就是你是否需要查这么后的记录..
像google啥的..一般我们输入了关键字查找后.我们只会关注前几页.或者前20页吧.
后面的.我们基本不感兴趣...

Answer 20

你的这个view_product 视图复杂吗 有优化的空间吗

Answer 21

确实是这样，但我现在的SQL中存在排序问题，而且刚才测试的情况是这个排序尽然要对整个表做一次扫描。这是性能的瓶颈所在