oracle

 

排序分页，排名次在咱们系统中应用的非常多，当表的数据量大的时候，排序分页，排名次就成为了SQL性能的瓶颈，生产库对这类SQL多有预警！

 

最近对海量数据排序分页，排名次做了深入研究，终于找到一个很好的方法，表的数据量对这种方法的性能影响不大，只与记录所在的页数有关，页数越大，性能稍有降低，第一个分页最快。

 

对数据量为10000000的表在开发库上做了测试，每页显示20条记录，第1000页用了0.187秒，用我们平常用的方法执行了125.238秒 ，比较了下结果和顺序都是一样的。

 

 

新方法sql如下：

 

SELECT/*+ leading(t1) use_nl(t1 t2) */

t1.rn, --名次

t2.*

FROM  kfj_posters_info t2,

       (SELECT/*+ index(t IDX_KFJ_POSTERS_INFO_NUM_DATE)*/

         rownum rn,

         ROWID  rd

        FROM  kfj_posters_info t

        WHERE t.posters_url ISNOTNULL

        AND   rownum <= 20000

        ORDER  BYpseters_vote_num DESC,

                 date_created     DESC) t1

WHERE  t2.rowid= t1.rd

AND   t1.rn > 19980

ORDER  BY t1.rn ASC;

 

说下这个sql的思路：

 

1，  通过pseters_vote_num，date_created两个列的降序索引IDX_KFJ_POSTERS_INFO_NUM_DATE取出当前页所有记录的 rowid （因为索引中已排好序，这一步非常快）;

2，  通过rowid 取出当前页表的数据（通过rowid 访问表是最快的方法） ;

3，  为了保证结果正确，最后再对当前页的数据做一次排序。

4，  为了保证执行计划的正确，这个sql的两个hint是必须要加的。如果执行计划不正确可能导致结果不对或很慢。

 

 

新方法的优缺点：

 

优点：性能很好，只对当前页的记录排序，消耗的cpu，I/O,内存资源很少

 

缺点：依赖索引，对执行要求很高，如果执行计划不正确，可能影响结果的正确性，所以必须使用hint去限制sql走正确的执行计划。

 

 

 

常规方法如下

SELECT *

FROM   (SELECTrownum rn,

              t1.*

        FROM   (SELECT *

               FROM   kfj_posters_info t

               WHERE  t.posters_url ISNOTNULL

               ORDER  BY pseters_vote_num DESC,

                         date_created     DESC) t1

        WHERE  rownum <= 20000

        ) t2

WHERE  t2.rn> 19980

         

 

常规方法的优缺点：

 

优点：执行计划的正确与否不影响结果

 

缺点：性能很差。对太多没有必要的数据做order by ，每查一个分页都要对全表做order by，消耗太多cpu，I/O,内存资源 。

 

 

 

 

=》 如果大家需要对大数据量做排序分页，排名次操作，请尝试新方法，谢谢！

 

另外介绍两个可以排名次的函数

 

dense_rank() over() -- 数值相同的算做一个名次，下个数值的名次直接加 1

 

rank() over()  --数值相同的算做一个名次，下个数值的名次不是加1，

 

具体区别大家通过下面sql在开发环境运行下就可以明白了

 

 

SELECT/*+ leading(t1) use_nl(t1 t2) */

t1.rn,

t1.dense_rank_,   -- dense_rank() over(ORDER BY t.pseters_vote_num DESC)

t1.rank_,  -- rank() over(ORDER BY t.pseters_vote_num DESC)

 t2.pseters_vote_num,

t2.date_created,

t2.*

FROM  kfj_posters_info t2,

       (

        SELECT/*+ index(t IDX_KFJ_POSTERS_INFO_NUM_DATE)*/

         dense_rank() over(ORDERBY t.pseters_vote_num DESC) dense_rank_ ,

          rank() over(ORDERBYt.pseters_vote_num DESC)  rank_,

          rownum rn,

          ROWID rd

        FROM  kfj_posters_info t

        WHERE  t.posters_urlISNOTNULL

        AND   rownum <= 20

        ORDER  BYpseters_vote_num DESC

        ) t1

WHERE  t2.rowid= t1.rd

AND   t1.rn > 0

ORDER  BY rn ASC;

 

结果如下：