网上有很多案例是关于 order by limit 选择错误索引的文章。但每个人遇到的情况可能不太相同，有些在新版本的 MySQL 下已经修复了，有些是优化器估计一时半会处理不了的。
案例
上面几个案例，有类似这样的查询 SELECT * FROM t1 where t1.f2>1 and t2.f2<100 order by t1.id，其中 id 是主键，条件里面有个 range 查询，就会造成优化器是选择主键，还是选择 filesort 问题，低版本下有些特殊情况就会选错索引，比如为了回避内存排序，选择了主键扫描，导致原本走范围过滤再排序，500ms 勉强可以结束的查询，5分钟不出结果。我测试了 MySQL 5.7 版本基本没有这个问题了。
但我下面这个案例却是很常见的索引选择错误导致查询时间放大，并且这种情况在生产环境很常见，MySQL 优化器一时半会也处理不了。
我们先看一下 account_detail 表统计信息里面表的行数是多少。
|
1 2 3 4 5 6 7 |
mysql> select TABLE_ROWS from information_schema.tables where table_name='account_detail'; +------------+ | TABLE_ROWS | +------------+ | 177957434 | +------------+ 1 row in set (0.00 sec) |
然后表上有一个 idx_supplier_state(supplier_id, state) 组合索引。
我们尝试执行下面的 SQL 拿到执行计划。
|
1 2 3 4 5 6 |
DESC SELECT detail.id, detail.account_serial, detail.order_no FROM account_detail `detail` WHERE(detail.supplier_id = 5511) ORDER BY detail.id desc; |
执行计划如下：
|
1 2 3 4 5 6 |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+--------+-----------+--------+-------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+--------+-----------+--------+-------------------------------------------+ | 1 | SIMPLE | detail | NULL | ref | idx_supplier_state | idx_supplier_state | 5 | const | 679656 | 11.11 | Using index condition; Using where; Using filesort | +----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+--------+-----------+--------+-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) |
执行计划就不用多说什么了，选择了 idx_supplier_state 索引，大概需要扫描 679656 行数据。
然后我们针对上面这个 SQL 稍微改变一下，加一个 limit 0, 15，只需要返回 15 行数据，并观察执行计划。
|
1 2 3 4 5 6 7 |
DESC SELECT detail.id, detail.account_serial, detail.order_no FROM account_detail `detail` WHERE(detail.supplier_id = 5511) ORDER BY detail.id desc LIMIT 0, 15; |
执行计划如下：
|
1 2 3 4 5 6 |
+----+-------------+--------+------------+-------+--------------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+--------------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | detail | NULL | index | idx_supplier_state | PRIMARY | 4 | NULL | 3927 | 0.04 | Using where | +----+-------------+--------+------------+-------+--------------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) |
可以明显看出，多加了一个过滤条件后执行计划变了。执行计划从 type:ref 变成了 type:index 索引扫描，索引从 idx_supplier_state 变成了 PRIMARY，总结就是多加了一个过滤条件后执行计划从原来的普通索引等值查询变成了主键顺序扫描。
打开 trace，看到了优化器对于排序的处理（这里省略了很多逻辑优化和索引选择），如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
{ "clause_processing": { "clause": "ORDER BY", "original_clause": "`account_detail`.`id` desc", "items": [ { "item": "`account_detail`.`id`" } ], "resulting_clause_is_simple": true, "resulting_clause": "`account_detail`.`id` desc" } }, { "refine_plan": [ { "table": "`account_detail`" } ] }, { // 到了order by id这边时，MySQL改变了执行计划，选择了PRIMARY主键索引 "reconsidering_access_paths_for_index_ordering": { "clause": "ORDER BY", "index_order_summary": { "table": "`account_detail`", "index_provides_order": true, "order_direction": "desc", "disabled_pushed_condition_on_old_index": true, "index": "PRIMARY", "plan_changed": true, "access_type": "index_scan" } } } |
关于 trace 更多的详细解释，可以去网上找资料。
优化器选择索引错误的原因？
MySQL 是优化器是根据成本来选择 SQL 执行路径的，当然成本是有自己的一套算法和套路评估出来的。从第一个执行计划我们可以看出，选择 idx_supplier_state 索引，需要扫描 679656 行数据，并且需要 filesort。第二个执行计划可以看出避免了 filesort 操作，并且扫描行数变成了 3927 行。从成本的角度考虑 MySQL 是正确的，选择主键扫描只需要扫描 3927 行数据，那么对比需要扫描 679656 行数据，自然选择了走主键扫描效率更高。
但是 order by id limit 的扫描行计算方式是存在问题的，当优化器走 idx_supplier_state 索引时，评估出 supplier_id=5511 大概需要扫描 679656 行数据，这个数据虽然不是 100% 精确，但也差不多就是 supplier_id=5511 的数据量，因为优化器是真实去扫描了 idx_supplier_state 索引，通过使用索引下探（index_dive）算法得到的 679656 这个值。
那么 order by id limit 是怎么计算出只需要扫描 3927 行就能出结果的结论呢？优化器是这么算的，拿统计信息里面的表行数 177957434 除以 679656 得到获取一行数据的成本，然后拿这个成本乘以 limit 的值就是优化器认定的走主键扫描需要的行数。优化器默认 supplier_id=5511 的数据是平均分布在表中（同样范围扫描，如 supplier_id>1000 and supplier_id<2000 也是默认为数据平均分布在表中，当 limit 成本小于 idx_supplier_state 索引时，也会放弃 idx_supplier_state 索引）。
|
1 2 3 4 5 6 7 |
mysql> select 177957434 / 679656 * 15; +-------------------------+ | 177957434 / 679656 * 15 | +-------------------------+ | 3927.5185 | +-------------------------+ 1 row in set (0.01 sec) |
如果你的 SQL 就是如此简单，那么优化器这个判断完全没有问题，只需要扫描大概 3927 行数据就可以得到结果，并且避免了 filesort。
但往往 SQL 的条件并不止 supplier_id=5511 这一个，比如加一个时间的过滤，SQL 如下：
|
1 2 3 4 5 6 7 8 |
SELECT detail.id, detail.account_serial, detail.order_no FROM account_detail `detail` WHERE(detail.supplier_id = 5511) AND((detail.account_time BETWEEN "2019-07-01" AND "2019-07-07")) ORDER BY detail.id desc LIMIT 0, 15; |
由于优化器并不会判断 account_time 条件，所以执行计划还是不会变的，会走主键扫描。但这个时候需要扫描的数据就不是优化器预估的 3927 行了，会随着时间范围的不同而不同，但优化器并不知道。本案例中 detail.account_time BETWEEN “2019-07-01” AND “2019-07-07” 的数据基本都在主键的右边，全表 177957434 行数据，所以可想而知，需要扫描多少数据才能扫描到符合条件的 15 行数据，这个成本就很大了。
当 limit offset 的值为多少时，MySQL 才会走 idx_supplier_state 索引呢？答案是 rows 大于走 idx_supplier_state 索引的 rows 时，也就是大于 679656。我们计算一下，
|
1 2 3 4 5 6 7 |
mysql> select 679656 / (177957434 / 679656); +-------------------------------+ | 679656 / (177957434 / 679656) | +-------------------------------+ | 2595.7459 | +-------------------------------+ 1 row in set (0.00 sec) |
可以看到，需要扫描 2595.74 行才与走 idx_supplier_state 索引的成本相同，我们扫描 2596 行应该就可以走 idx_supplier_state 索引了。
只需要把上面 SQL 的 limit 0,15 改成 limit 2581, 15 就表示扫描 2596 行数据了，看一下执行计划：
|
1 2 3 4 5 6 |
+----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | detail | NULL | ref | idx_supplier_state | idx_supplier_state | 5 | const | 679656 | 11.11 | Using index condition; Using where; Using filesort | +----+-------------+--------+------------+------+--------------------+--------------------+---------+-------+--------+----------+----------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) |
可以看到当成本大于走 idx_supplier_state 索引时，MySQL 还是会选择走索引了，走索引每次成本都是一样的，不会随着条件的变化而变化，但会随着 supplier_id=5511 的数据变大而变慢。
怎么解决这个问题呢？
这是 MySQL 针对 order by limit 做出的优化，可惜优化器目前还不够友好，期待后面能越做越好吧。
解决这个问题也很简单，要不就是给 supplier_id,account_time 加组合索引，这样根据索引查询的成本就可能低于主键扫描，但也不一定，要看具体这两个条件需要过滤的数据量。
或者，关闭 MySQL 对 order by limit 的优化，比如 force index 强制走某个索引，这样有一个不好的地方就是被强制使用的索引名称不能改变，或者删除，不然 SQL 就会报错了。
如果感觉使用 force index 不太友好，我们可以换个方式，通过干扰优化器给 order by id 改写成 order by id+0，这样优化器也不会走主键扫描。
