ITPub博客

首页 > 数据库 > MySQL > MySQL之SQL分析三部曲实际案例(六)--file sort与key_len

MySQL之SQL分析三部曲实际案例(六)--file sort与key_len

MySQL 作者:streetboy85 时间:2015-11-13 11:21:38 0 删除 编辑
-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------
问题发生于对即将上线的SQL进行review时,实际问题的截图隐去部分生产环境信息,试验环境构造于测试数据库
所有操作都是基于MySQL-5.6.26下进行的,补充实验在MySQL-5.7.9-GA环境下进行了简单验证

背景:开发人员在测试环境测试SQL时发现文件排序没有走索引
问题分析过程:下文详细描述
问题聚焦:优化器为什么没有走索引去排序,以及key_len的延伸

出问题的SQL语句(同类型的问题构造)

点击(此处)折叠或打开

  1. select * from t_order_main 
  2. where outer_order_id >= '1' and outer_order_id <= '3'
  3. and pay_time>='2014-11-11 00:00:00' and pay_time<='2014-11-11 23:59:59'
  4. order by order_id;

问题描述:开发在测试环境的表上面建立了outer_order_id, pay_time, order_id的联合索引,希望解决explain里面出现的file sort的问题,
但是加了索引以后,发现explain的输出结果中还是存在filesort,结果如下图
索引

执行计划

可以看到虽然有索引可以同时覆盖到选择条件和排序列, 但是MySQL的优化器没有选择那个联合索引,而是选择了两个where条件的联合索引
既然要看优化器的一些信息,那么就去trace里面找找吧~一组使用正常的优化器逻辑,一组使用force来指定索引,截取部分信息截图
正常的优化器选择逻辑,可以看到优化器估计的cost是2.2

强制指定索引,

索引的代价是一样的

但是在排序的时候, 这个联合索引的最后一列没有用上


从这两个对比里面很容易看出来,优化器认为走索引去排序并不好,而且三列的联合索引和两列的联合索引都是一样的cost,自然也就不会去使用相对体积更大的三列联合索引了。
结论:MySQL对比两种索引策略的cost以后,认为使用索引去排序没有必要,所以选择了体积相对比较小的两列联合索引,
这种情况多发生于最终结果集比较小的时候,排序的操作就可以完全放在内存,而不用读索引,然后再回表取数据,所以虽然联合索引有排序列,但是MySQL并不会去使用。

延伸:有一个很有意思的现象,这两个索引的结构不一样,但是key len却是一样的,原因
对比一下强制索引和默认索引的explain,有一个比较有意思的现象:两个执行计划的key len都是103~但是这两个索引的结构是不一样的;


引用前辈的总结(出处同下面的key len计算方式):key len表明了在这次查询中,所用到的索引的长度,所用到的,意味着,如果索引的某些列没有用到,那就不会计算在这个长度里面;
这段介绍也证实了在之前,虽然索引结构不一样,但是都只用到了一部分的索引列,那么这意味着可以通过key_len来判断实际执行中用到了多少列。
问题就变成了,这个key len是怎么算出来的?
索引对应的几个列的结构



数据结构对应的key len计算方式,一部分引用现有的资料(出处http://imysql.com/2015/10/20/mysql-faq-key-len-in-explain.shtml)

补充一个用到的datetime的属性,计算key_len的时候为5(如果带上了小数精度,这个长度会变化,从5-8不等)
那么开始计算一下len:
outer_order_id = 32x3(UTF-8)+2(变长)=98
pay_time = 5
可以看到截图中的key_len刚好是98+5=103,说明这个执行计划使用了outer_order_id 和pay_time~
可是,执行计划里面真的用了pay_time?
在trace里面
我们可以看到在计算索引的代价的时候,列出了使用索引的选择条件和索引

是的,在分析索引代价的时候,没有pay_time,这说明索引中的pay_time并没有被用来优化where条件里面的逻辑,
但是key_len里面确确实实把pay_time的这一部分加上了,那么他用到哪里去了?
看看最前面截图的表结构,然后为explain增加一点额外的输出,看看分区表的分区信息

很明显可以看到,这个查询指向了一个单独的分区p0,真相大白~本次查询用到了pay_time,但是不是用来优化where条件的筛选,而是用在了分区条件的判断上!
作为对比,去掉pay_time的条件看看,

长度也变成了98, 分区也从p0变成了所有~

-------------------------------------------------------------------------------------------------结论---------------------------------------------------------------------------------------------------------------
MySQL会有一套Cost计算模型来判断多种索引的代价,file sort的出现,并非代表着这个语句的效率不好,因为结果集并不大的时候,纯内存的排序并不会有太高的开销,
通过走索引避免排序,然后再用随机读的方式回表反而会消耗更多的时间(随机读的开销很高
explain的key_len会反应本次查询使用的索引的列的情况,不仅是使用在where条件里面的列,也包括判断分区条件使用到的索引列。

-------------------------------------------------------------------------------------------------附录---------------------------------------------------------------------------------------------------------------
附上其他类型的key_len的实验,请对照截图信息食用~测试环境MySQL-5.7.9-GA,本质上没什么区别
PS:请无视中间那个失误...
表结构

部分测试结果

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17233243/viewspace-1836308/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2015-11-13

  • 博文量
    1
  • 访问量
    3304