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

最新推荐文章于 2022-07-23 16:19:40 发布

iqifenxia

最新推荐文章于 2022-07-23 16:19:40 发布

阅读量158

点赞数

文章标签： https xml c#

原文链接：https://www.fgba.net/forum-53-1.html

版权

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

今天跟大家分享一个小案例，之前为一个公司做项目终结验收的一个护航工作，我的任务就是每天监控并抓几个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排序区已经无法满足存储数据需求，走了QQ账号买号平台外存，这步骤也是要尽量避免，而目前这个步骤对于Oracle而言也是迫不得已，因为第4步走全表，根据用户的业务需求确实需要全表排序。注意此时的逻辑读为185，有内存排序。

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

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

Index created.

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