SET optimizer_trace="enabled=on"; // 打开 optimizer_trace SELECT * FROM order_info where uid = 5837661orderbyidasclimit1 SELECT * FROM information_schema.OPTIMIZER_TRACE; // 查看执行计划表 SET optimizer_trace="enabled=off"; // 关闭 optimizer_trace
MySQL 优化器首先会计算出全表扫描的成本,然后选出该 SQL 可能涉及到的所有索引并且计算索引的成本,然后选出所有成本最小的那个来执行,来看下 optimizer trace 给出的关键信息
这个选择表示由于排序的原因再进行了一次索引选择优化,由于我们的 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 都还没解决,所以在官方修复前我们要尽量避免这种写法,如果一定要用这种写法,怎么办呢,主要有两种方案
使用 force index 来强制使用指定的索引,如下:
select * from order_info forceindex(idx_uid_stat) where uid = 5837661orderbyidasclimit1
这种写法虽然可以,但不够优雅,如果这个索引被废弃了咋办?于是有了第二种比较优雅的方案
使用 order by (id+0) 方案,如下
select * from order_info where uid = 5837661orderby (id+0) asclimit1
这种方案也可以让优化器选择正确的索引,更推荐!为什么这个 trick 可以呢,因为此 SQL 虽然是按 id 排序的,但在 id 上作了加法这样耗时的操作(虽然只是加个无用的 0,但足以骗过优化器),优化器认为此时基于全表扫描会更耗性能,于是会选择基于成本大小的方式来选择索引