优化 Oracle 数据库分页查询:让查询更高效

一、Oracle数据库的分页操作

对于Oracle数据库的分页查询语句一般采用如下的方式。

二、Oracle数据库的分页操作的性能分析和调优

按照分页操作的SQL语句的复杂度，可以将分页操作的种类分为只包含排序操作的分页、包含过滤操作和排序操作的单表查询分页、包含过滤操作和排序操作的单分区表查询分页、包含过滤操作和排序操作的表关联查询分页等几种类型。

1.只包含排序操作的分页

一般地，分页操作的SQL语句带有排序操作，即：order by。例如：在客户访问记录表中查询最新访问的30个客户，这就需要按照客户的访问时间进行降序排序。

对于这类分页操作的调优，需要利用索引是有序的特性，为order by中的排序列设置索引。

如果是对多个列进行排序，需要为所有的排序列创建一个复合索引，创建时需要注意复合索引中的各列的顺序需要与order by中指定的各排序列的顺序一致，且还需注意索引是升序还是降序，以下示例1展示了该类SQL语句的调优。

【示例1】

对名为user的用户信息表执行分页查询，查询最近访问但积分最低的前10位用户，分页语句如下所示。

user表中的记录数为300万左右。

为排序列创建一个复合索引，usercore列在前，lasttime列在后，且将lasttime列的索引设置为降序，即：

创建索引后，该语句的真实执行计划中的核心内容如下图所示。

通过以上的执行计划可以知：

（1）该SQL语句执行时访问User表时采用了INDEX FULL SCAN（索引快速全扫）的访问方式（因为未指定过滤条件，所以只能采用INDEX FULL SCAN的访问方式）；

（2）执行INDEX FULL SCAN时访问10条记录后，已经满足分页要求，扫描终止，该SQL语句执行完毕。即上图执行计划中的COUNT STOPKEY（COUNT STOPKEY一旦获取到满足分页条件所需的记录后即停止SQL语句的执行）操作；

（3）执行计划中没有诸如SORT ORDER BY、SORT ORDER BY ROWNUM之类的排序操作，说明以上SQL语句执行时利用了索引消除了排序操作。

综上所述可知，该SQL的执行计划是正确的，高效的。

此外，对于只有1个列的降序排序操作，也可以不将该列的索引设置为降序索引，而是创建一般的升序索引，在查询执行时使用index_desc提示器，使得执行对该索引的扫描时采用降序扫描的方式。

如果创建符合索引时，排序列的顺序与order by中的排序列顺序不一致，在查询执行时，排序列的索引将仅用于通过索引访问记录，之后对获取的结果集再执行一次额外的排序操作，执行计划中将出现SORT ORDER BY或SORT ORDER BY ROWNUM之类的排序操作提示，说明并未利用索引消除排序操作。

如果创建的索引的顺序与实际排序的顺序不一致，例如：降序排序，升序索引，执行时也未使用index_desc提示器，则在查询执行时，排序列的索引也将仅用于通过索引访问记录，之后也要对获取的结果集再执行一次额外的排序操作。

2. 包含过滤条件和排序操作的分页

对于包含过滤条件和排序的分页，除了为排序列创建索引外，还需要为过滤条件创建索引。根据过滤条件的不同，又分为等值过滤和非等值过滤。

Ⅰ.等值过滤

如果分页语句中包含等值过滤和排序，此时需要为等值过滤列和排序列创建复合索引，该复合索引的顺序为等值过滤列在前，排序列在后，即：

(等值过滤列1,等值过滤列2,...,排序列1,排序列2,...)

以下示例2展示了该类SQL语句的调优。

【示例2】

对名为user的用户信息表执行分页查询，查询最近访问但积分最低的前10位类别为C类用户，分页语句如下所示。

为等值过滤列，排序列创建一个复合索引，按userclass，userscore，lasttime的顺序创建，即：

创建索引后，该语句的真实执行计划中的核心内容如下图所示。

通过以上的执行计划可以知：

（1）该SQL语句执行时访问User表时采用了INDEX RANGE SCAN（索引范围扫描）的访问方式，因为已经指定了过滤条件，且为过滤条件列设置了索引。

（2）执行INDEX RANGE SCAN时访问10条记录后，已经满足分页要求，扫描终止，该SQL语句执行完毕。即上图执行计划中的COUNT STOPKEY操作；

（3）执行计划中没有诸如SORT ORDER BY、SORT ORDER BY ROWNUM之类的排序操作，说明以上SQL语句执行时利用了索引消除了排序操作。

综上所述可知，该SQL的执行计划是正确的，高效的。

需要注意的是，创建复合索引时等值过滤条件列要放在排序列之前，如果将其放在排序列之后，即：按userscore，lasttime，userclass，的顺序创建上述分页语句的真实执行计划将变为如下图所示的。

通过以上的执行计划可以发现，对user表的访问方式由INDEX RANGE SCAN变为INDEX FULL SCAN方式。

过滤执行时为执行计划中id为7中对应的access和filter两种方式的结合，该方式说明在使用userclass列上索引访问user表时在执行索引扫描的同时又执行了对记录的过滤操作。具体操作为：扫描索引时，同时对每条索引按照过滤条件，如果符合过滤条件的记录达到分页操作指定的10条后，扫描停止。

这种边扫描边过滤的访问方式的执行效率较直接执行INDEX RANGE SCAN的访问方式稍低，因为这种方式的逻辑读多于INDEX RANGE SCAN方式。

Ⅱ.等值过滤和非等值过滤

如果分页语句中包含等值过滤非等值过滤和排序，创建复合索引时，需要将等值过滤列放在前部，排序列放在中部，非等值过滤列放在后部，即：

(等值过滤列1,等值过滤列2,...,排序列1,排序列2,...,非等值过滤列1,非等值过滤列2...)

同样地，如果分页语句中只有非等值过滤和排序，则需要将非等值过滤列放到复合索引的后部，以下示例2展示了该类SQL语句的调优。

【示例3】

对名为user的用户信息表执行分页查询，查询最近访问但积分最低的且注册时间大于18个月的前10位类别为C类用户，分页语句如下所示。

为等值过滤列，非等值过滤列，排序列创建一个复合索引，按userclass，userscore，lasttime，regtime的顺序创建，即：

创建索引后，该语句的真实执行计划中的核心内容如下图所示。

通过以上的执行计划可以知，该分页SQL查询执行时已经消除了排序操作，且对user的访问方式为INDEX RANGE SCAN（索引范围扫描），因为创建复合索引时将非等值过滤列regtime放到了排序列之后，所以执行INDEX RANGE SCAN时又进行了过滤操作，虽然这种方式的执行性能稍低于直接执行INDEX RANGE SCAN，但如果创建复合索引时将非等值过滤列regtime放到了排序列之后，该分页SQL查询的真实执行计划将变为如下图所示的形式。

该执行计划中出现了 SORT ORDER BY排序操作，且执行INDEX RANGE SCAN时扫描了6389条记录，说明查询执行时未利用索引是有序的特性，对查询结果结果集又执行了排序操作并只获取了前10条记录，显然，这种方式的执行效率和性能相比INDEX RANGE SCAN和过滤方式是较差的。

2. 包含过滤操作和排序操作的单分区表查询分页

上述介绍的通过创建适当的索引调优分页操作的方法同样适用于分区表的分页查询。

Oracle数据库的分区表的索引分为本地索引（Local Index）和全局（Global Index）索引两种类型。

本地索引（Local Index）的分区与表的分区完全相同，每个分区都有自己的本地索引分区。本地索引的维护由Oracle自动进行，通常本地索引的维护代价较低。

全局索引（Global Index）与表的分区无关，因此它只有一个分区。全局索引的维护和更新对于表的分区变化的响应速度可能较慢，但是在全局索引分区上可以执行跨分区的查询操作。

通过以上对两种索引的介绍可知，为分区表创建索引时需要在本地索引与全局索引中做出选择，选则原则如下。

（1）如果分页查询操作只访问一个分区，则可以为相应的列创建本地索引；

（2）如果分页查询操作需要访问多个分区（跨分区访问），则需要为相应的列创建全局索引，否则无法确保分页操作中排序操作的顺序也索引的顺序一致，从而无法利用索引的有序性消除分页中的排序操作；

（3）在分页查询操作中没有过滤条件的情景下，查询执行时将扫描分区表中的所有分区，如果该分页查询中的排序列就是创建分区表时的范围分区列，且范围分区中每个分区中的数据是递增的，此时可以为排序列创建本地索引。如果创建的分区是HASH分区或LIST分区，因为这两类分区是无序的，所以需要为排序列创建全局索引。

2. 表关联查询分页

对于多表关联排序的调优，需要遵循以下的原则。

（1）将表关联方式调整为嵌套循环关联，需要对哪个表排序，则应该该表作为驱动表，注意驱动表返回的数据顺序应与排序的顺序一致。如果采用外连接，则采用嵌套循环关联时，驱动表只能为主表；

（2）根据（1）可知，排序列只来自于嵌套循环关联的驱动表，即：只能对一个表排序，如果排序列即来自驱动表也来自被驱动表，则需要等表关联全部执行完成后，对结果集执行一次排序，导致排序操作无法消除；

（3）关联分页查询语句中不能出现max、min、avg之类的聚合函数，也不能出现去重（distinct）、分组（group by）、并（union/union all）等操作，这些操作会使得全部关联操作完成之后再进行分页，执行性能较低。

【示例4】

对 CD_U_MC_MODEL_REMINDER_ORGCUST（A）和CD_U_ MC MODEI_REMINDER（B）表执行关联，并进行分页操作，排序后获取前50条记录，SQL语句如下。

其中CD_U_MC_MODEL_REMINDER_ORGCUST表记录较多，达到3千万左右，该SQL语句执行较慢，平均执行用时16秒左右。

经对执行计划进行分析，语句中存在SORT ORDER BY ROWNUM排序操作，该SQL执行表表关联后，返回的结果集较大，达到300万条记录，对300万条进行排序，造成了较大的开销，且因为PGA空间有限，排序占用了临时表空间，同时造成了一定的I/O开销（出现direct path readtemp 和direct path write temp事件）。

对该SQL查询调优的首要就是消除排序，因排序列为CD_U_MC_MODEL_REMINDER_ORGCUST表，所以，为该表创建一个联合索引，按“(等值过滤列,关联列,排序列)”的顺序依次创建，需要注意的是，该SQL查询按CD_U_MC_MODEL_REMINDER_ORGCUST表的reach_date列降序排序，reach_date列需要创建降序索引，创建索引的语句如下。

创建索引后，该SQL语句的执行计划如下所示。

通过执行计划可以发现，排序操作已被消除，优化器选择了嵌套循环关联，并以排序列所属的CD_U_MC_MODEL_REMINDER_ORGCUST表作为嵌套循环关联的驱动表。

实际执行该SQL语句，查询用时降至0.6秒左右。

继续对该查询进行优化，通过进一步分析CD_U_MC_MODEL_REMINDER_ORGCUST表的数据分布，发现该表的记录按procod列的数据分布，整个表中procod列的值约有30个，每个值对应的记录数在100万-300万条左右，查询执行时对CD_U_MC_MODEL_REMINDER_ORGCUST表按procod的值进行了过滤。因此，我们可以将CD_U_MC_MODEL_REMINDER_ORGCUST表按procod的值进行分区，创建非HASH/LIST分区。此外，每次查询时procod只指定一个值，不存在跨分区查询，所以，为procod列创建本地索引即可。

经过以上第二次调优后，该SQL语句的执行计划如下所示。

通过以上的执行计划可以发现，对CD_U_MC_MODEL_REMINDER_ORGCUST表的访问只扫描了一个分区，该SQL语句的执行时间进一步降低至10毫秒左右。