mysql 索引 limit_【业务学习】关于MySQL order by limit 走错索引的探讨

Grape

描述

今天在跑脚本的时候发现了几条慢查询，根据之前的经验实属不应该，后来经过查找资料和分析出来结果，在这里简单记录一下。

首先，我的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是可以走这个索引的，但是他没有，他选择了主键索引。

分析

看到这是个慢查询，我起手一个explain，结果如下：

看到这个结果我肯定不服啊，为什么是走的主键索引，因此开始了百度谷歌之旅。

刚开始我找到了一个自认为比较正确的方法，在某度上找了一篇文章说orderby之前有范围查找的会走orderby之后的索引，反之走orderby之前的索引，我试了一下，哎，不错，我把范围查询改成了等值查询，是走了我的索引了，但是我看了一眼行数，一脸懵逼，为什么这么多行？这不是我想要的

然后我profiling(大家可以自行百度)查了下时间，发现Creating sort index这哥们占用了九成的时间，这时候我敏锐的察觉到了这个排序有问题，(该吃饭了)不行，继续查！

继续查，上某哥，哎你别说，某哥大法还是好，终于找到了一个大佬的分析,具体是什么原因呢？

首次，我强制走我的联合索引看下情况：

看到上图会发现有个差别就是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，这样可能会导致一些问题。

以上分析若有差错，还望不吝指教！感谢。

参考文章：