【业务学习】关于MySQL order by limit 走错索引的探讨
Grapemysql
描述
今天在跑脚本的时候发现了几条慢查询，根据以前的经验实属不该该，后来通过查找资料和分析出来结果，在这里简单记录一下。算法
首先，个人sql是这个样子：sql
select `id` from `xxx` force index(idx_d_t) where `date` = '2019-09-11' AND `time_flag` < '20190911220000' order by id asc;
索引是下边这个样子：优化
KEY `inx_t_d` (`date`,`time_flag`)；
按照我以前的理解这条sql是能够走这个索引的，可是他没有，他选择了主键索引。spa
分析
看到这是个慢查询，我起手一个explain，结果以下：指针
看到这个结果我确定不服啊，为何是走的主键索引，所以开始了百度谷歌之旅。
刚开始我找到了一个自认为比较正确的方法，在某度上找了一篇文章说orderby以前有范围查找的会走orderby以后的索引，反之走orderby以前的索引，我试了一下，哎，不错，我把范围查询改为了等值查询，是走了个人索引了，可是我看了一眼行数，一脸懵逼，为何这么多行？这不是我想要的code
而后我profiling(你们能够自行百度)查了下时间，发现Creating sort index这哥们占用了九成的时间，这时候我敏锐的察觉到了这个排序有问题，（该吃饭了）不行，继续查！
继续查，上某哥，哎你别说，某哥大法仍是好，终于找到了一个大佬的分析,具体是什么缘由呢？blog
首次，我强制走个人联合索引看下状况：排序
看到上图会发现有个差异就是Using filesort这玩意儿，这玩意儿是个什么东西呢？简单的说filesort 是经过相应的排序算法将取得的数据在内存中进行排序。俗话说有对比才有伤害，抓到敌人的小辫子就接近了胜利，咱们继续看。索引
fliesort有两种排序方式：
- 双路排序：首先根据相应的条件取出相应的排序字段和能够直接定位行数据的行指针信息，而后在 sort buffer 中进行排序。
- 单路排序：是一次性取出知足条件行的全部字段，而后在 sort buffer 中进行排序。
何时用到这两种呢？MySQL 主要经过比较所设定的系统参数 max_length_for_sort_data 的大小和 Query 语句所取出的字段类型大小总和来断定须要使用哪种排序算法。若是 max_length_for_sort_data 更大，则使用第二种优化后的算法，反之使用第一种算法。很显然应该尽量让 MySQL 选择使用第二种单路算法来进行排序。这样能够减小大量的随机 IO 操做，很大幅度地提升排序工做的效率。
上文分析的排序时间过长极可能就和这个有关系了，继续查资料分析，问题的关键就在于为何会filesort。
结论
在执行语句的时候，由于数据量较大，MySQL优化器认为走联合索引很差，默认选择了第一个更慢的执行计划，它的理由是走主键索引不须要内存排序，候选的 idx_d_t被淘汰。优化器认为主键索引不用排序比联合索引要好，因此致使了这种状况，
那咱们该怎么作，在这里我只列出个人解决方法，他认为主键更好，那么咱们就给他更好的，咱们更改idx_d_t这个索引，由date,time_flag改为，id,date,time_flag，这样就解决问题了。
如图：
最后总结一下，就是优化器会尽可能避免走file_sort，这样可能会致使一些问题。
以上分析如有差错，还望不吝指教！感谢。
参考文章：