问题描述
今天早早地来到了公司，按照惯例打开阿里云DMS控制台，在sql分析中有红色的慢sql的警告，如下图：
于是赶紧点进入去看了下具体的慢sql记录，有部分执行次数比较多的sql，如下所示（隐去了真实的表名）：
SELECT
a.*
FROM
`some_table` a
WHERE
a.estimate_deliver_date >= '2021-04-01 00:00:00.0'
AND a.estimate_deliver_date < '2021-04-09 23:59:59.0'
ORDER BY
a.id DESC
LIMIT 0, 10;
复制代码语句看起来很简单，也没什么特别的，但是执行的查询时间差不多在6s左右，表大概有500多W行。
大致情况就是这样，下面进入排查问题的环节。
问题排查
首先怀疑是estimate_deliver_date字段没有建立索引，导致查询走了All类型的全表扫描，于是看了下表的索引信息：
PRIMARY KEY (`id`)
KEY `idx_statement_date` (`statement_date`) USING BTREE
KEY `idx_estimate_deliver_date` (`estimate_deliver_date`) USING BTREE
......
复制代码可以看到存在包含estimate_deliver_date字段的索引idx_estimate_deliver_date，我们查询条件是estimate_deliver_date，按道理是会走索引的。
有没有走idx_estimate_deliver_date索引，通过explain一看便知。mysql提供了explain用来分析sql语句的执行计划，执行计划中我们主要关注以下几个关键指标：
- select_type：查询类型，有sample、delived等
- type：扫描的类型，有ALL、index、range、ref等
- possible_keys：可能会用到的索引
- key：最终mysql优化器选择的索引
- key_len：mysql优化器选择的索引中用到的字段的长度总和
- rows：本次查询将扫描的行数，这是一个预估值
- extra：额外条件，比如using where、using index、using filesort等
我们使用explain分析该语句，得到如下的执行计划：
可以看出，type的值是index，说明没有走全表扫描，但是走的全索引扫描，效率也是比较低的；接下来possible_keys显示的是idx_estimate_deliver_date，但是key却显示的是PRIMARY，说明查询走的主键索引。
既然没有走idx_estimate_deliver_date索引，那么我们可以指定查询强制走此索引，通过FORCE INDEX(idx_estimate_deliver_date)，现在查询语句变成了这样：
SELECT
a.*
FROM
`some_table` a
FORCE INDEX(idx_estimate_deliver_date)
WHERE
a.estimate_deliver_date >= '2021-04-01 00:00:00.0'
AND a.estimate_deliver_date < '2021-04-09 23:59:59.0'
ORDER BY
a.id DESC
LIMIT 0, 10;
复制代码这次语句执行的飞快，毫秒级别就执行完了，如下图所示：
我们来看看语句的执行计划的结果是什么：
通过上图可以看出，加上了FORCE INDEX(idx_estimate_deliver_date)后，查询的确走了idx_estimate_deliver_date索引，并且执行速度也是得到了很大的提升。
问题找到了，总结下来大概就是：MySQL优化器认为在上述的查询语句的情况下，走主键索引能够更快的找到数据，并且如果走联合索引需要扫描索引后进行排序，增加额外的排序开销，而主键索引天生有序，所以优化器综合考虑，走了主键索引。
简单介绍mysql索引的选择原理
mysql优化器索引选择的准则
首先要知道，选择索引是MySQL优化器的工作。
而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。
当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。
我们再来对比一下刚才两个explain的执行结果：
走了主键索引的查询语句，rows预估行数227，而强制走联合索引行数是357，并且Extra信息中，显示需要Using filesort进行额外的排序。所以在不加强制索引的情况下，优化器选择了主键索引，因为它觉得主键索引扫描行数少，而且不需要额外的排序操作，主键索引天生有序。
预估行数是怎么估计出来的
这里不做具体的介绍，有一个算法来进行估算，具体可以查询相应的资料进行了解。
索引要考虑order by字段吗
order by中的字段是要考虑的，如果索引中没有考虑order by中的字段，那么可能mysql server层拿到引擎层的查询结果后，要再自己进行一遍排序，这时就会出现额外的性能开销，可以从执行计划的extra中的using filesort体现出。具体的关于order by的优化请参考官方文档 dev.mysql.com/doc/refman/…
更改limit可以解决问题吗
先回答问题，是会影响的，这里我们不妨做一个实验，我们将limit的数量提高到20，那么这个语句就变成了：
SELECT
a.*
FROM
`some_table` a
FORCE INDEX(idx_estimate_deliver_date)
WHERE
a.estimate_deliver_date >= '2021-04-01 00:00:00.0'
AND a.estimate_deliver_date < '2021-04-09 23:59:59.0'
ORDER BY
a.id DESC
LIMIT 0, 20;
复制代码这个修改过后的语句对应的explain执行计划是：
从执行计划可以看到，这时查询走了idx_estimate_deliver_date索引。至于为什么呢，我猜测是这时走主键索引的rows预估行数超过了走idx_estimate_deliver_date索引的预估行数，并且结合其他条件，最终宁愿走额外的排序，也要走idx_estimate_deliver_date索引。
解决方法
知道了Mysql为何选择了另外的索引的原因后，所有也有了优化的思路，主要包括下面两个方面：
- 强制走索引
- 干扰优化器选择索引
强制走索引：force index
正如之前写的一样，我们直接使用force index来强制查询走索引，这样避免优化器选错索引导致出现查询效率问题。
SELECT
a.*
FROM
`some_table` a
FORCE INDEX(idx_estimate_deliver_date)
WHERE
a.estimate_deliver_date >= '2021-04-01 00:00:00.0'
AND a.estimate_deliver_date < '2021-04-09 23:59:59.0'
ORDER BY
a.id DESC
LIMIT 0, 10;
复制代码这种方式是最快能解决问题的。虽然通过force index解决了优化器选错索引的问题，但是也是有缺点的：比如硬编码进代码中，高耦合，不易维护，万一后面索引名称变化后，代码要进行相应的调整。所以不建议使用这种方式。
干扰优化器选择索引：增大limit
这种方式是增大limit，让扫描行数rows增加，使得优化器选择目标索引：
SELECT
a.*
FROM
`some_table` a
WHERE
a.estimate_deliver_date >= '2021-04-01 00:00:00.0'
AND a.estimate_deliver_date < '2021-04-09 23:59:59.0'
ORDER BY
a.id DESC
LIMIT 0, 100;
复制代码这种方式虽然可以走索引，但是总感觉哪里不对，而且也不一定会走到目标索引，即使走了目标索引，最后还要在业务代码中过滤掉不用的数据，不是白白的浪费计算资源吗？
干扰优化器选择索引：增加联合索引
SELECT
a.*
FROM
`some_table` a
WHERE
a.estimate_deliver_date >= '2021-04-01 00:00:00.0'
AND a.estimate_deliver_date < '2021-04-09 23:59:59.0'
ORDER BY
a.id DESC
LIMIT 0, 10;
复制代码目前这个查询sql只建立了estimate_deliver_date字段的idx_estimate_deliver_date索引，导致还需要额外的排序来对id进行desc排序，这种情况我们可以建立一个联合索引：
KEY `idx_estimate_deliver_date_id` (`estimate_deliver_date`,`id`) USING BTREE
复制代码这时我们来看看执行计划的结果：
从执行计划中可以看出，这时走了idx_estimate_deliver_date_id索引，并且扫描行数rows比走主键索引的时候要少。
这样也有一定的弊端，比如数据表的数据量比较大的话，建立索引非常耗时，如果无限制的用索引解决问题，可能会带来新的问题。表中的索引不宜过多。
干扰优化器选择索引：使用子查询
当然，除了上面的几个办法，还有一种方法就是使用子查询，子查询sql如下：
SELECT
a.*
FROM
`some_table` a
WHERE
a.id in (
SELECT
b.id
FROM (
SELECT
c.id
FROM
`some_table` c
WHERE
c.estimate_deliver_date >= '2021-04-01 00:00:00.0'
AND c.estimate_deliver_date < '2021-04-09 23:59:59.0'
ORDER BY
c.id DESC LIMIT 0, 10
) b)
ORDER BY
a.id DESC;
复制代码其他解决方法
SQL优化是个很大且繁琐的工程，我们还有非常多的办法能够解决这句慢查询问题，这里就不一一展开了。