文档章节

order by 索引

开源中国-不入流码农
 开源中国-不入流码农
发布于 2017/09/04 19:32
字数 2508
阅读 19
收藏 1

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

字数 3507阅读 1275评论 1赞 2

作者:邱峙

对于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字段需要是非空的属性,否则会无效。
好了,今天的分享就到这里,大家可以关注我们的专栏。

本文转载自:http://www.talkwithtrend.com/Article/178469

下一篇: 删表
开源中国-不入流码农
粉丝 2
博文 36
码字总数 3897
作品 0
杭州
程序员
私信 提问
mysql 索引优化order by 语句

MySql创建索引时支持ASC或DESC排序。 下面举例 创建表时同时创建索引降序排序(sname 字段上普通索引降序) create table tbl1 ( id int unique, sname varchar(50), index tbl1indexsname(s...

君千殇520
2017/11/06
0
0
mysql使用索引优化order排序

个人原创地址:http://www.phpthinking.com/archives/433 在数据库中我们一般都会对一些字段进行索引操作,这样可以提升数据的查询速度,同时提高数据库的性能像order by ,group by前都需要索...

PHP开发学习门户
2014/09/25
0
0
Mysql 在 order by 时索引的使用机制

今天在这里用最简单粗暴的实例方式的方法来验证下这个让同志们抓不着节奏的 order by 和 索引之间的关系 条件:1300w 条数据(呃,公司测试数据而已,不要在意) order by 中的所有字段都包含...

big_cat
2016/04/26
107
0
联合索引详解

MySQL 联合索引详解 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持...

语博兄
2016/07/21
17
0
mysql索引提高优化order by语句用法介绍

在数据库中我们一般都会对一些字段进行索引操作,这样可以提升数据的查询速度,同时提高数据库的性能像order by ,group by前都需要索引哦。 先我们要注意一下 1>mysql一次查询只能使用一个索...

kongjun
2016/04/08
133
0

没有更多内容

加载失败,请刷新页面

加载更多

ngrok 外网映射工具

ngrok介绍 许多刚学java web的同学(包括我自己)肯定都非常好奇,如何在外网上访问自己做的项目,跟我们本地访问tomcat有什么区别? 今天就向大家介绍一个非常强大的外网映射工具:ngrok.ngrok可以...

edison_kwok
32分钟前
2
0
Spark Streaming的优化之路——从Receiver到Direct模式

          作者:个推数据研发工程师 学长 1 业务背景 随着大数据的快速发展,业务场景越来越复杂,离线式的批处理框架MapReduce已经不能满足业务,大量的场景需要实时的数据处理结果来...

个推
今天
4
0
壮丽70年·奋斗新时代|蒸妙集团熏蒸中会阴熏蒸的神奇好处

聚结相合之处为会。会阴居两阴间,为督、任、冲三脉的起点,三脉背出两阴之间,会聚阴部,因名会阴。会阴,经穴名。出《针灸甲乙经》。会阴别名屏翳、下极、金门。属任脉。在会阴部,男性当阴...

公益传承
今天
2
0
pentaho-kettle-8.2.0.0-R源码开发环境搭建

1.从Kettle官网下载源码,本文使用的是pentaho-kettle-8.2.0.0-R 下载地址:https://codeload.github.com/pentaho/pentaho-kettle/zip/8.2.0.0-R 2.打开eclipse,选择一个新的工作空间,然后设...

gq_2010
今天
1
0
lua web快速开发指南(7) - 高效的接口调用 - httpc库

httpc库基于cf框架都内部实现的socket编写的http client库. httpc库内置SSL支持, 在不使用代理的情况下就可以请求第三方接口. httpc支持header、args、body、timeout请求设置, 完美支持各种h...

水果糖的小铺子
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部