一个分页查询优化的案例解析-谈谈SQL调优的基本思想

昨天下午完成公司安排的培训有点累，又跟同事忙里偷闲到哈尔滨的步行街走了走， 品尝了一根马迭尔冰棍，到松花江畔吹了吹江风，讲课的疲惫缓解了不少。配一张图吧，这是同事拍的，抓的角度很特别，让我们的技术文章多一点生活的趣味。

今天跟大家分享一个小案例，之前为一个公司做项目终结验收的一个护航工作，我的任务就是每天监控并抓几个TOPSQL 分析下看是否可以调优，而后直接将这些SQL调优意见发给对方的技术总监，他们确定该SQL对应的业务后决定是否调优处理。

对于SQL调优，我们要普及写基本知识，调优的一个基本原则就是“少拿数”，这三个字是之前听Oracle集群专家高斌老师的课程时听到的，或许之前也知道这个原则，但是那次优化学习这三个字深深刻在我的脑子里。也希望大家能够铭记于心，也就是SQL的执行要在解析阶段就确定尽量少拿数，在引擎阶段就做出好的决策，也就是有个优的执行计划，但是这个优还要依靠表数据分布的特点以及其他对象的存在，比如索引，约束，主键，也可能需要配合数据库参数调整。

由于不能全文发布该SQL（出于职业要求），我就模拟这个SQL的逻辑来分析，在分析的过程中我们一起学习调优的知识点以及对SQL逻辑的分析。

这是一个分页查询，它在TOPSQL中排第三位，主查询查询一个业务表，需要针对一个字段做排序，后取排序的前49行数据（49纯属满足测试rownum<50，没有实际意义），SQL语句如下:

select * from ( select a.object_name,a.owner,a.status,a.namespace from test_obj a order by created desc ) where rownum<50;

原始的执行计划如下：

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<50)

   3 - filter(ROWNUM<50)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        185  consistent gets

          0  physical reads

          0  redo size

       2299  bytes sent via SQL*Net to client

        556  bytes received via SQL*Net from client

          5  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

         49  rows processed

我们先分析下SQL逻辑，针对表test_obj查询四个字段，排序字段是created不在查询需求中，这个排序是降序排序，外围的分页查询需要取前10行数据。

从执行计划看，显示执行第四步全表扫描，而后通过第3步骤排序操作，通过第1步分页取数获得，我们看到在3，4步骤是耗资源的部分，尤其是第3步的sort oder by 操作优化器评估使用Temp空间，也就是服务器进程的PGA排序区已经无法满足存储数据需求，走了外存，这步骤也是要尽量避免，而目前这个步骤对于Oracle而言也是迫不得已，因为第4步走全表，根据用户的业务需求确实需要全表排序。注意此时的逻辑读为185，有内存排序。

对于这个操作，我应该知道索引的基础知识，就是索引是有序的，并且返回的数据量不大，这是典型缺乏索引的情况，而通过查询该字段系统没有创建索引，所以我建议再改字段创建desc索引.

SCOTT@orcl1>create index idx_test_obj_created on test_obj(created desc);

Index created.

我们再次执行该SQL ，看优化器是否使用该索引。 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<50)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         20  consistent gets

          0  physical reads

          0  redo size

       2305  bytes sent via SQL*Net to client

        556  bytes received via SQL*Net from client

          5  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         49  rows processed

此时的全表扫扫描消失了，Rows字段为49，也就是通过索引找到了前49行记录，然后通过步骤3回表，而步骤1没有消耗资源，因为用户所需要的数据前面已经全部获得。此时的逻辑读只有20个,注意此时步骤4的逻辑读为6个，回表找数又耗费了14个逻辑读，这里可以进一步优化。

其实这里还可以做一次针对该SQL的优化，因为走索引依然需要回表的IO需求，本案例是14个逻辑读，也就是回表依然需要逻辑读，如果能消除这个逻辑读，而直接从索引获得数据，走index full scan效率会更高，我们做如下测试，创建组合索引created字段放在前面。

组合索引

SCOTT@orcl1>create index idx_com_test_obj on test_obj(created desc,object_name,owner,status,namespace);

Index created.

再次观察执行计划和逻辑读

谓词和统计信息

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<50)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          6  consistent gets

          0  physical reads

          0  redo size

       2299  bytes sent via SQL*Net to client

        557  bytes received via SQL*Net from client

          5  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

         49  rows processed

从执行计划看步骤3走此时走index full scan此时消除了回表，只有6个逻辑读，由于数据都在索引中，这个组合索引类似于“瘦表”从而减少逻辑读。

总结：索引有序消除全表扫描，满足排序和分页要求，根据查询需求进一步通过组合索引消除索引回表的逻辑读开销，进一步提高SQL的执行效率。