SQL优化思想1-2-3（参考文链接)

上期回顾

      小余买鱼与数据库优化【1】——诊断与改进 

      小余买鱼与数据库优化【2】——需求与设计

      小余买鱼与数据库优化【3】——资源的利用

       小余买鱼与数据库优化【4】——真正的需求

      小余买鱼与数据库优化【5】——买鱼买出方法论

1.2 方法论应用案例

好吧，不说故事了，和大家分享点工作中的数据库优化经典案例吧（啥，你说啥？你终于听到高级的了？哦，说案例就高级，讲故事就低级啊。。。。）

某电信营运商生产系统出现故障，短信平台产生大量积压，维护人员跟踪发现，原因是后台短信平台进程调用数据库中某个过程包，而该过程包原先执行返回结果给后台进程在10秒以内，现在不知是何种原因过程包返回时间居然长达1分钟，所以导致短信后台程序处理缓慢许多，最终造成短信积压。情况紧急，需要立即着手调查，该怎么处理呢？

亲爱的读者，如何处理你心中有数吗？如果你读懂《小余买鱼》，你就应该很有信心来解决这个故障，优化这个系统，成为大家心目中的英雄！ 不信？让我接着往下讲吧。

该如何优化这个系统呢？好吧，祭出秘密武器：优化方法论！该方法论由买鱼故事演化而来，旗下有两大精髓，又称作八字要领：一套流程、两大 法宝！（啥，叫我别吹牛了，讲实在的？还不实在啊，好吧，接下来让你好好看看实在不实在….）

忘记前面总结出的方法论的读者，请自觉翻回上小节再仔细阅读三遍。

根据总结的“一套流程”，我们自然想到，优化的这套流程主要分为诊断和改进优化两个环节，由于现在还不明确问题出在该数据库包的哪个模块？因此进入诊断环节是必要的，然后根据诊断定位出来的具体问题，再进行具体的优化。

1.2.1一套流程

 1.2.1.1诊断

模拟后台调用该包的时候传入的参数手动执行该包，然后开启ORACLE的TRACE跟踪工具工具，该过程包执行完毕后关闭跟踪，具体步骤大致如下：

1.用10046 trace 工具开始跟踪

alter session set events '10046 trace name context  forever,level 12';

2.执行你的数据库包

 exec pkg_test(‘abc’);

3.执行包完毕后结束跟踪

alter session set events '10046 trace name context off';

4.10046 trace 工具跟踪完毕后会输出分析结果，类似如下：

E:\admin\ora10\udump\ora10_ora_4832.trc

5.可格式化后进行分析，类似如下：

tkprof  E:\admin\ora10\udump\ora10_ora_4832.trc  d:\10046.txt  sys=no sort=prsela,exeela,fchela

6.然后分析10046.txt的文件，这里响应时间从大到小展现该包所有SQL语句，即可有如下收获：

• 该过程包总共执行了多少SQL语句，具体内容是什么，分别开销了多少时长

• 哪些是开销时长最长的语句。（由于有排序过，所以最长的一眼可看出，在最前端）

  咦，这和《小余买鱼1》中的诊断方法有差别吗？还真是过程细化和找细化项主要矛盾两个动作啊。

接下来分析10046.txt文件发现，原来慢的SQL是类似如下的两条非常简单的SQL，分别占用30秒和20秒，其他所有SQL单条执行都只零点几秒。

语句1（SQL1耗时30秒）

Select count(*) from t1;

语句2（SQL2耗时20秒）

Select  distinct t1.col1,t1.col2,t2.col3,t2.col4  
from t1 ,t2  
where t1.id=t2.id and t1.name=’cc’ 
order by t1.col5;

其他SQL语句（合计才消耗0.5秒）

----SQL3    (0.03秒)
----SQL4    (0.028秒)
-- --SQLn…….(0.001秒)
--略去

1.2.1.2 改进优化（首 次优化）

通过分析可以知道,SQL1和SQL2是重点需要改进的SQL，首先分析SQL1，该如何改进优化呢？

如何改进优化？根据小余买鱼的经验，我们知道要先去理解需求，这条SQL背后的需求是什么？看上去并不难猜测，就为了查询T1表的记录数，毕竟Select count(*) from t1;这条语句太简单了。

开始介入分析，通过查看该SQL的执行计划可以知道，该表T1是进行全表扫描。（查看执行计划和10046 TRACE一样，是一个非常重要的优化工具）

该表记录目前有5千万，每次都对全表进行扫描仅为了获取该表的记录。我们的需求是为了得到记录数，是否一定要对全表进行扫描呢？就好比小余要去买鱼，是否一定要去20里外的沃尔玛超市呢？

就好比小余需要具备了解自己住宅周围是否有地方买鱼这个生活经验一样，这个案例我们也需要多了解点相关知识，啥知识？这里需要读者对ORACLE索引有比较深刻的理解。

通过全扫描数据表可以获取到该表有多少记录的信息，如果对该表存放序列值的非空字段SEQ_ID上建一个索引，全扫描该索引，一样可以获取到该表有多少记录的信息。看来，获取表记录数可以有两种方法了。选那种方法呢？大家知道，索引的大小比表大小小的多，在更大的范围内遍历更快速还是在小的多的范围内遍历更快速呢？好比买鱼是到远方的沃尔玛还是楼下的农贸商场买鱼那种更快一样，答案毋庸置疑。

接下来我在T1表的SEQ_ID这个非空字段上建立一个索引，Select count(*) from t1;的语句执行计划从全表扫描转换为索引全扫描。由于该表字段很多，而这个SEQ_ID字段又仅占10个字节，索引大小仅为表大小的三十分之一。从大范围找答案改为小范围找答案后，意味这我们达成同样的目的，少做了不少事情。如此下来该SQL执行速度当然会有大幅度提升，果然，最终执行速度从原来的30秒变为1秒左右。

看来学习优化还真是和学习买鱼一个道理，顺利优化了SQL1就等于完成了工作的一半，继续努力。

接下来进行SQL2的调优，和优化SQL1时一样首先开始查看分析SQL2语句的执行计划，发现SQL2的执行计划也是全表扫描，这里t1.name=的取值为cc的返回仅仅10条记录，而T1表记录都在5千万左右，T2表在200万左右，需要全扫这么大的两个表而获取仅有的10记录吗？

这里又要再次利用到索引的原理，SQL1是利用到了索引一般比表小的多的特点，现在又是要利用啥呢？哦，利用索引的快速定位原理。假如我们在name列建了一个索引，而现在是利用了索引的快速检索原理。索引有个最大的特点是有序排列，当表记录检索到dc等以d打头的记录后，ORACLE就停止遍历了！为啥，因为索引是有序的，当出现d打头的记录后，绝对后面不可能再出现c打头的记录了，因为我们是查询=cc的值，当然停住了。随时停止检索相比遍历全表，明显是少做事和不做事，效率可以意料会提升不少。

那SQL2如何优化，哦，好简单，就是在name列建一个索引就好了。索引在这条SQL中因为可以让应用少做事和不做事，最终到了速度大幅度提升，果然，优化后的执行速度从原来的20秒缩减为1秒。

到此优化完毕，短息后台进程由原来的每次执行1分钟多变为2秒多，速度提升了30多倍，积压情况大大缓解，系统运行恢复正常。

应该说这次优化总体是很成功的，客户也非常满意。不过我个人心中还是有少许疑惑之处，什么疑问呢？

1.  SQL1（Select count(*) from t1）为什么要统计条数，得到条数的真正目的是什么？

2.  SQL2中的distinct 取唯一值是为啥，难道表有重复记录？distinct 可是需要排序的。

3.  SQL2 中的 order by t1.col5; 排序是T1表的col5字段，展现字段又没有这个字段，真的需要这个排序吗？

未完待续...

小余买鱼与数据库优化【7】——方法论应用案例（下）

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列