深入剖析-关于分页语句的性能优化

分页语句是数据库开发和应用场景比较常见的需求，即按照特定的where条件进行过滤，然后在按照一个或者多个条件进行排序（如果不进行排序无法确执行时候无法返回相同的结果），最后取其中的前十行或者几十行。

一般分页语句消耗资源的地方有两点：

1、返回where条件过滤的结果集；

2、是对这个结果集进行排序，如果表过大同时对返回的结果集排序势必导致性能严重下降，针对分页语句性能低下的原因。

优化分页语句的核心思想：

1、创建效率高的索引返回尽量少的结果集排序；

2、因为索引是有序的，直接让数据库读取有序索引数据避免进行排序。

下面就针对不同的分页语句场景做如何优化。

1正确的分页语句框架

分页场景一：针对分页语句的优化

首先我们要确定正确的分页语句框架，如果不按照正确的分页语句框架编写，会严重影响oracle选择正确的执行计划，正确的分页语句框架如下：

select * from

      ( select   * from

           (   select  a.*,rownum rn from 

                 (    写好的sql语句  )  a 

           )   where rownum<=m

      ) where   rn>=n;

针对正确的分页语句和错误的分页语句会产生不同的执行计划，举例如下：

SQL>  create table t as select * from dba_objects;

Table created.

SQL> select count(*) from t;

COUNT(*)

----------

497070

我们要写好的sql语句，如下：

select * from t where object_id<1000  返回前10行 

针对这个sql语句，如果T表比较大的话，全表扫描就会非常消耗资源，我们针对object_id列创建索引即可。object_id列选择性非常高，对1000列进行排序性能也很高。创建如下索引：

create index t_idx_id on t(object_id);

然后再套用正确的分页语句框架，去执行高级执行计划：

Set linesize 200 pagesize 200

alter session set statistics_level=all;

select * from

      ( select * from

           ( select  a.*,rownum rn from 

                 ( select * from t where object_id<1000 ) a 

           ) where rownum<=10

      ) where rn>=0;

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

采用正确的分页语句框架执行计划走的是T_IDX_ID索引，分页语句显示10行，执行计划中A-ROWS是10行。我们再看看采用其他错误分页语句显示的高级执行计划：

从错误的执行语句框架中我们可以看到，只要不是正确分页语句框架，oracle都会扫描<1000行最后显示10行数据。

针对上述语句的优化方案我们需要注意2点：

1、采用正确的分页语句框架；

2、针对where条件创建选择性高、效率高、索引返回少的结果集。

2order by 分页

分页场景二： select * from t order by object_id  基于某列排序再分页

因为索引是排序的，我们可以利用索引的排序功能。在排序的分页语句中如果我们让分页语句直接按照升序或者降序扫描索引，这样的话就避免了全表扫描再排序的这种消耗资源操作。但是我们不确定object_id列是否有非空约束，由于索引是不存空值的，为了能够保证可能为空的object_id列也存在索引中，我们要在索引中添加一个组合列的常量索引，创建索引语句如下：

create index T_IDX_ID0 on t (object_id,0);

执行计划如下：

注：如果有些优化器没走索引可以在sql语句中增加一个索引的hint。

3where等值条件过滤order by分页

分页场景三： select * from t where owner=’SYS’ order by object_id  有where条件过滤，然后基于某列排序再分页

这类分页语句我们要如何创建索引？ 因为oracle对这类语句执行过程是先过滤where条件再排序，所以我们创建一个组合索引，给予OWNER,OBJECT_ID列组合（不能颠倒）

create index T_IDX_owner_ID on t (owner,object_id);

以此类推，where owner='SYS' order by object_id, object_name  这类基于 owner,object_id,object_name列建组合索引。

4where不等值条件过滤order by分页

分页场景四： select * from t where where object_id<100000 order by owner 语句中的where条件是非等值，然后order by 其他列

这种情况我们就不能按照【分页场景三】进行优化，这类语句我们要分两种情况：

第一种where条件过滤后的结果集比较少，我们就采用【分页场景一】进行优化直接创建效率高的索引。

第二种where条件过滤后结果集比较多，这种我们就要 order by列在前，不等值列在后创建组合索引。

create index T_IDX_owner_ID on t (owner,object_id);

执行计划如下：

注：以上两种情况没有明显的分界线，特别是针对反对结果集比较适中的情况，还要综合比较两种创建索引方法谁的执行效率更高而采用哪种方案。

5多表关联的分页语句

分页场景五：多表关联的分页如何优化select * from a,b where a.id=b.id  order by a.id;

这类分页语句的优化思想是：既然是多表关联的分页语句，一定是走嵌套循环，不能走hash连接，最后要order by 某个表，一定是 order by的那个表做驱动表，同时驱动表的 order by列必须有索引。

6无法优化的分页语句

无法优化的分页场景：但是如果是这种需求select * from a,b where a.id=b.id  order by a.xx，b.xxx  这种需要对两个表排序情况下就无解了（为什么会搞基于两个表排序的需求，淘宝京东的商品排序大多数是只按照一种属性排序，如按照销量排序，按照价格排序，综合排序），这种情况需要干掉一个 order by 的列。

如果分页语句中有distinct， group by等需要把表数据全部扫描之后再去排序分页的，这种就无法用专门分页语句进行优化了。

7总结

以上几种分页场景基本包含了目前主要的分页语句的需求和实现，不同的分页语句有一种或者几种优化方案。首先根据【优化场景一】的内容，先选择标准的分页语句框架，然后判断whete条件过滤后的结果集条数是多还是少。如果返回结果集少，则创建效率高的索引；如果返回结果集非常多，则考虑【分页场景二，三，四】，为分页语句创建一个排序过滤好的索引直接返回结果。对于【无法优化的分页场景】，就要考虑其他手段了，比如说调整分页需求，增加where过滤条件，对大表进行分区和瘦身等其他优化方案。

关注本公众号，回复：prelection，你可以找到本文的相关视频文档。

相关阅读：

SQL之美 - 分页查询的排序问题

让SQL成为一种生活方式：认识分页查询

如何优化Oracle使用DBlink的SQL语句

Linux大内存页Oracle数据库优化

Library Cache优化与SQL游标

资源下载

关注公众号：数据和云（OraNews）回复关键字获取

‘2017DTC’，2017DTC大会PPT

‘DBALIFE’，“DBA的一天”海报

‘DBA04’，DBA手记4经典篇章电子书

‘INTERNALS’，Oracle RAC PPT

‘122ARCH’，Oracle 12.2体系结构图

‘2017OOW’，Oracle OpenWorld资料

‘PRELECTION’，大讲堂讲师课程资料