共计 3491 个字符，预计需要花费 9 分钟才能阅读完成。
最近收到一个 Sentry 报警，如下 SQL 查问超时了。
select * from order_info where uid = 5837661 order by id asc limit 1执行 show create table order_info 发现这个表其实是有加索引的：
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实践上执行上述 SQL 会命中 idx_uid_stat 这个索引，但理论执行 explain 查看
explain select * from order_info where uid = 5837661 order by id asc limit 1能够看到它的 possible_keys（此 SQL 可能波及到的索引）是 idx_uid_stat，但实际上（key）用的却是全表扫描。
咱们晓得 MySQL 是基于老本来抉择是基于全表扫描还是抉择某个索引来执行最终的执行打算的，所以看起来是全表扫描的老本小于基于 idx_uid_stat 索引执行的老本。
不过我的第一感觉很奇怪，这条 SQL 尽管是回表，但它的 limit 是 1。也就是说只抉择了满足 uid = 5837661 中的其中一条语句，就算回表也只回一条记录，这种老本简直能够忽略不计，优化器怎么会抉择全表扫描呢。
为了查看 MySQL 优化器为啥抉择了全表扫描，我关上了 optimizer_trace 来一探到底。
画外音：在 MySQL 5.6 及之后的版本中，咱们能够应用 optimizer trace 性能查看优化器生成执行打算的整个过程。
应用 optimizer_trace 的具体过程如下：
SET optimizer_trace="enabled=on"; // 关上 optimizer_trace
SELECT * FROM order_info where uid = 5837661 order by id asc limit 1
SELECT * FROM information_schema.OPTIMIZER_TRACE; // 查看执行计划表
SET optimizer_trace="enabled=off"; // 敞开 optimizer_traceMySQL 优化器首先会计算出全表扫描的老本，而后选出该 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，但足以骗过优化器），优化器认为此时基于全表扫描会更耗性能，于是会抉择基于老本大小的形式来抉择索引。
