平台人生
作者平台人生2017-06-15 14:09
软件开发工程师, 平台人生

一个容易被忽略的SQL调优技巧 --- order by字段到底要不要加入索引

字数 3507阅读 22794评论 6赞 19

作者:邱峙


对于SQL调优,要调就调到极致,小编并不是处女座,而是因为在一个并发量很大的业务系统中,对于频繁执行的单条SQL性能的提升,可能对整体数据库的性能提升都有很大的意义。
但是遇到order by字段后面的字段,特别是当这个字段不在过滤条件中时,小编就会心里打鼓,是加到索引里面呢,还是不加到索引里面呢,加进去会不会没有起到提升性能的作用,反而让索引变得更加复杂,给系统带来不必要的额外负担,“偷鸡不成蚀把米”,开个玩笑。但是如果直接忽略掉这个问题,很可能这个提升系统性能的机会就被错过了。
所以今天小编就和大家探讨一下,面对order by字段后面的条件,特别是这个条件不在过滤条件中时,到底要不要加入索引中,对于SQL调优这笔账,索引中加入order by字段,是赚了还是赔了❓

Part 1

空话不多说,先来一个小实验,热一下身。通过多次复制dba_objects中的数据,生成测试表T1,大约1000万行数据。做一个简单的查询,查询T1表中object_id最小的10行数据,select * from (select * from T1 order by object_id) where rownum<=10,耗时‘Elapsed: 00:00:35.92’,执行计划如下:
微信图片_20170615135444.png
执行计划中可以看到,先作了一个全表扫,取到了结果集11M行(可以粗略理解为11百万行,这个测试表T1行数为11943842)。然后作了一个排序,截取最小的10条记录,最后返回结果。下面我们在object_id字段上建一个索引I_T1_ORDER3,作一个比较。
耗时从刚才的35秒,直接降到了 ‘Elapsed: 00:00:00.01’,提升性能的效果非常明显。索引和执行计划如下:
微信图片_20170615135651.png
微信图片_20170615135738.png
从执行计划中可以看到,优化器直接从索引中找到了最小的10条记录,然后回表取得结果集返回。相比上一个执行计划,省去了全表扫描,省去了排序,所以执行时间和系统资源消耗都大大减少。
在这里作一个简单的分析,首先索引和数据不同,是按照有序的排列存储的,当结果集要求按照顺序取得一部分数据时,索引的功效会体现的非常明显,本次查询就是要取得object_id最小的10条记录。其次,建立索引系统只需要消耗一次资源完成排序过程,而如果没有索引,执行不同的语句可能每次都要经历排序的过程,会消耗更多的系统资源。从这个实验看,在order by字段建索引是非常划算的,而且order by字段并不一定非要加入到where条件中也可以生效。

这里小编要和大家分享一个自己踩到的“坑”,就是小编起初在建了索引I_T1_ORDER3后,这条查询语句的执行计划并不选择索引,增加了hint提示也不选择,小编都有点怀疑人生了,明显使用索引会好,为什么优化器偏偏不选择索引呢,而且是加了hint也不走。在修改object_id列为非空属性(NOT NULL)后,优化器才选择了这个索引。小编这里是这么理解的,如果这一列存在NULL值,NULL值是没有大小这一说法的,而且不会被保存在索引中。如果优化器无法确定该列没有NULL值,为了保证结果集的准确性,宁愿选择更慢的全表扫描,也不会选择走可能存在NULL的索引,即使用户指定了hint也不会选择(这里的几句话有点绕,大家耐心读一下)。从这一点来看,开发Oracle优化器的小伙伴是非常靠谱的。

Part 2

上面的实验中order by字段加入索引的作用非常明显。可是在实际生产环境中,能有这么简单的SQL来给DBA调优的机会并不多,实际生产中的SQL往往要更复杂一些。下面我们就把测试变得复杂一点,复制测试表T1,生成测试表T2,查询object_type类似INDEX中object_id最小的10条记录,select * from (select * from T2 where object_type like '%INDEX%' order by object_id) where rownum<=10。
这条语句比第一个实验中多了过滤条件,但是使用了like方法。按通常的经验建索引首先会考虑where条件后的字段,但是在使用like的过滤条件上建立索引,效果可能并不好。可是如果这条语句是业务系统中执行频率非常高的语句呢,我们还是硬着头皮优化一下吧。先看一下没有索引的情况。
微信图片_20170615135945.png
执行时间“Elapsed: 00:00:08.75”,接近9s,从执行计划中看到,先是全表扫描过滤出了1597K条(1597K约163万条)记录,然后作了个排序,返回object_id最小的10条记录。
这样的执行效率在生产系统中是不能接受的,但是在like列上建索引效果可能并不好,本着敬业的精神,还是试一下吧。在仅有的两个条件 object_type和object_id上建一个复合索引I_T2_ORDER2,并
加入hint提示,结果如下:
微信图片_20170615140027.png
微信图片_20170615140030.png
执行时间“Elapsed: 00:00:17.25”,比刚才9秒还多花了8秒。从执行计划中可以看到,先是在索引I_T2_ORDER2中定位到1597K条记录,然后回表取得1597K记录的结果集,再排序取到object_id最小的10条记录。与上一个执行计划相比,反而增加了一个读索引的步骤,所以系统资源消耗更多,执行时间也更长,而且虽然order by字段加入到索引中,并没有省去排序的步骤。在这里这个索引建的就有点亏了。
“理想很丰满,现实很骨感”,看来SQL变得复杂以后,order by字段在索引里面果然不灵了,这招不好使。不要着急,咱们分析一下,为什么不好使了。大家都知道索引是树状结构,现在I_T2_ORDER2索引中有两个字段,这个索引结构大概是这个样子的,如下图。
微信图片_20170615140106.png
大家可以看到,对应INDEX节点下面的object_id“3,9,13”是有序的, INDEX PARTITION节点也类似。但是把INDEX节点和INDEX PARTITION节点对应的object_id放到一起,“3,9,13…2,15,17”,就变得无序了,所以优化器虽然使用了索引,但不得不再做一遍排序,order by索引的功效并没有发挥出来。
看到这里是不是有点灰心了,这条语句没法优化了。看下本文的标题,换个角度想一下,说不定这条语句还有救。与测试表T1一样,在object_id上建一个索引I_T2_ORDER3试一下。
微信图片_20170615140141.png
微信图片_20170615140144.png
执行时间从17s,直接变为“Elapsed: 00:00:00.01”,从执行计划可以看到,优化器通过索引过滤了817条记录后得到了想要的10条结果,之后回表取得结果返回。与上面的执行计划相比,时间消耗和资源消耗都大大减少。
这里我们简单分析一下,索引I_T2_ORDER3是按照object_id有序排列的,当优化器按序处理到817条记录时,就已经得到了想要的object_type类似INDEX,object_id最小的10条记录,然后回表取到结果并返回,省去了全表扫描以及排序的消耗,所以效率大大提升。索引结构如下图。
微信图片_20170615140227.png
执行时间和系统消耗,都大大减少,那么到这里我们是不是可以交差了。再看一下我们文章的开头,“对于SQL调优,要调就调到极致”, “对于频繁执行的单条SQL性能的提升,对整体数据库的性能提升都有很大的意义”。我们再想一下还可不可以更优。小编在这里又建了一个索引I_T2_ORDER4,再执行这条查询语句。
微信图片_20170615140358.png
微信图片_20170615140403.png
执行时间“Elapsed: 00:00:00.01”,从执行计划中可以看到,优化器通过索引直接定位到了想要的10条记录,回表取得10条记录并返回。最终结果只有10条记录,优化器也只处理了10条记录,几乎没有任何的资源浪费。I_T2_ORDER4索引的结构图如下,可以看到,过滤条件已经在索引中存储了,所以优化器可以在索引中直接定位到最终的10条记录。
微信图片_20170615140458.png
到这里,从建索引的角度出发,小编认为这条SQL的优化可以交差了。

Part 3

最后小编想说的是,遇到类似order by字段是否加入索引的问题,或者其他一些大家犹豫的问题,可以大胆的尝试,并打开思路,从不同的角度考虑,多做测试,不要错过任何一个提升性能的机会。
对于order by字段加入索引本身这个问题,如果最终的结果集是以order by字段为条件筛选的,将order by字段加入索引,并放在索引中正确的位置,会有明显的性能提升。不过这里要注意小编前面提到的那个坑,order by字段需要是非空的属性,否则会无效。
好了,今天的分享就到这里,大家可以关注我们的专栏。

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

19

添加新评论6 条评论

#powx技术经理, ZWT
2019-05-13 18:14
棒,学习一下!!!!
#powx技术经理, ZWT
2019-05-13 18:14
棒,学习一下!!!!
#呆霸天软件开发工程师, 西安科技
2019-01-30 15:20
很nice
#Oone数据库开发工程师, 深圳
2018-04-18 11:29
很实用,很受教
#Jeanson软件开发工程师, 中科信息
2018-01-09 10:36
很有用的尝试
#desert_xu数据库运维工程师, 杭州xxxx有限公司
2017-08-31 14:34
文章不错,第二个复合索引,前缀字段为object_type 选择率不好呀,所以cbo 不选择走索引。
Ctrl+Enter 发表

本文隶属于专栏

关于TWT  使用指南  社区专家合作  厂商入驻社区  企业招聘  投诉建议  版权与免责声明  联系我们
© 2019  talkwithtrend — talk with trend,talk with technologist 京ICP备09031017号-30