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

-------------------------------------------------------------------------------------------------正文---------------------------------------------------------------------------------------------------------------
问题发生于对即将上线的SQL进行review时，实际问题的截图隐去部分生产环境信息，试验环境构造于测试数据库
所有操作都是基于MySQL-5.6.26下进行的，补充实验在MySQL-5.7.9-GA环境下进行了简单验证

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

出问题的SQL语句（同类型的问题构造）

问题描述：开发在测试环境的表上面建立了 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：请无视中间那个失误...
表结构

部分测试结果