这个 MySQL bug 99% 的人会踩坑！

1、公司产品太多了，怎么实现一次登录产品互通？
2、用了BigDecimal就不会资损？了解下BigDecimal这五个坑
3、一个依赖搞定 Spring Boot 反爬虫，防止接口盗刷！
4、千万不要把 Request 传递到异步线程里面！有坑！
5、不卷了！入职字节一周就果断跑了。

select * from order_info where uid = 5837661 order by id asc limit 1

CREATE TABLE `order_info` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`uid` int(11) unsigned,`order_status` tinyint(3) DEFAULT NULL,  ... 省略其它字段和索引  PRIMARY KEY (`id`),KEY `idx_uid_stat` (`uid`,`order_status`),) ENGINE=InnoDB DEFAULT CHARSET=utf8

explain select * from order_info where uid = 5837661 order by id asc limit 1

SET optimizer_trace="enabled=on";        // 打开 optimizer_traceSELECT * FROM order_info where uid = 5837661 order by id asc limit 1SELECT * FROM information_schema.OPTIMIZER_TRACE;    // 查看执行计划表SET optimizer_trace="enabled=off"; // 关闭 optimizer_trace

MySQL 优化器首先会计算出全表扫描的成本，然后选出该 SQL 可能涉及到的所有索引并且计算索引的成本，然后选出所有成本最小的那个来执行。

来看下 optimizer trace 给出的关键信息：

{"rows_estimation": [    {"table": "`rebate_order_info`","range_analysis": {"table_scan": {"rows": 21155996,"cost": 4.45e6    // 全表扫描成本        }      },      ..."analyzing_range_alternatives": {"range_scan_alternatives": [          {"index": "idx_uid_stat","ranges": ["5837661 <= uid <= 5837661"            ],"index_dives_for_eq_ranges": true,"rowid_ordered": false,"using_mrr": false,"index_only": false,"rows": 255918,"cost": 307103,            // 使用idx_uid_stat索引的成本"chosen": true            }          ],"chosen_range_access_summary": {    // 经过上面的各个成本比较后选择的最终结果"range_access_plan": {"type": "range_scan","index": "idx_uid_stat",  // 可以看到最终选择了idx_uid_stat这个索引来执行"rows": 255918,"ranges": ["58376617 <= uid <= 58376617"             ]         },"rows_for_plan": 255918,"cost_for_plan": 307103,"chosen": true         }         }      ...

可以看到，全表扫描的成本是 4.45e6，而选择索引 idx_uid_stat 的成本是 307103，远小于全表扫描的成本。而且从最终的选择结果（chosen_range_access_summary）来看，确实也是选择了 idx_uid_stat 这个索引。

但为啥从 explain 看到的选择是执行 PRIMARY 也就是全表扫描呢？难道这个执行计划有误？

仔细再看了一下这个执行计划，果然发现了猫腻。

执行计划中有一个 reconsidering_access_paths_for_index_ordering 选择引起了我的注意：

{"reconsidering_access_paths_for_index_ordering": {"clause": "ORDER BY","index_order_summary": {"table": "`rebate_order_info`","index_provides_order": true,"order_direction": "asc","index": "PRIMARY",    // 可以看到选择了主键索引"plan_changed": true,"access_type": "index_scan"        }    }}

这个选择表示：由于排序的原因再进行了一次索引选择优化。

由于我们的 SQL 使用了 id 排序（order by id asc limit 1），优化器最终选择了 PRIMARY 也就是全表扫描来执行。也就是说这个选择会无视之前的基于索引成本的选择。

为什么会有这样的一个选项呢？

主要原因如下：

The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.

从这段解释可以看出，主要原因是由于我们使用了 order by id asc 这种基于 id 的排序写法，优化器认为排序是个昂贵的操作，所以为了避免排序，并且它认为 limit n 的 n 如果很小的话即使使用全表扫描也能很快执行完，所以它选择了全表扫描。也就避免了 id 的排序（全表扫描其实就是基于 id 主键的聚簇索引的扫描，本身就是基于 id 排好序的）。

如果这个选择是对的那也罢了，然而实际上这个优化却是有 bug 的！实际选择 idx_uid_stat 执行会快得多（只要 28 ms）！

网上有不少人反馈这个问题，而且出现这个问题基本只与 SQL 中出现 order by id asc limit n 这种写法有关。如果 n 比较小很大概率会走全表扫描，如果 n 比较大则会选择正确的索引。

这个 bug 最早追溯到 2014 年，不少人都呼吁官方及时修正这个 bug。可能是实现比较困难，直到 MySQL 5.7，8.0 都还没解决，所以在官方修复前我们要尽量避免这种写法，如果一定要用这种写法，怎么办呢？

主要有两种方案

1. 使用 force index 来强制使用指定的索引。

如下：

select * from order_info force index(idx_uid_stat) where uid = 5837661 order by id asc limit 1

这种写法虽然可以，但不够优雅，如果这个索引被废弃了咋办？于是有了第二种比较优雅的方案。

2. 使用 order by (id+0) 方案。

如下：

select * from order_info where uid = 5837661 order by (id+0) asc limit 1

这种方案也可以让优化器选择正确的索引，更推荐！

为什么这个 trick 可以呢？因为此 SQL 虽然是按 id 排序的，但在 id 上作了加法这样耗时的操作（虽然只是加个无用的 0，但足以骗过优化器），优化器认为此时基于全表扫描会更耗性能，于是会选择基于成本大小的方式来选择索引。

往期热门文章：
1、线上MySQL的自增id用尽怎么办？被面试官干趴下了！
2、计算机专业会不会成为下一个土木？
3、xxl-job惊艳的设计，怎能叫人不爱
4、ArrayList#subList这四个坑，一不小心就中招
5、面试官：大量请求 Redis 不存在的数据，从而影响数据库，该如何解决？
6、MySQL 暴跌！
7、超越 Xshell！号称下一代 Terminal 终端神器，用完爱不释手！
8、IDEA 官宣全新默认 UI，太震撼了！！
9、让你直呼「卧槽」的 GitHub 项目！
10、Kafka又笨又重，为啥不选Redis？