MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误

bug 触发条件如下：

优化器先选择了 where 条件中字段的索引，该索引过滤性较好；

sql 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化，最终因代价问题没有成功。

表结构

构造数据

触发sql

使用 force index 可以选择过滤性好的索引

optimizer_trace 可以帮助分析这个问题。

select * from information_schema.optimizer_trace\g

range_scan_alternatives 计算 range_scan，各个索引的开销，从上面的结果可以看出，联合索引 iabc 开销较小，应该选择 iabc。

considered_execution_plans 表索引选择过程，access_type 是 range，rows_for_plan=3，到这里为止，执行计划还是符合预期的。

clause_processing 用于简化 order by，经过 clause_processing access_type 变成 index_scan（全索引扫描，过滤性较range差），此时出现了和预期不符的结果。

因此可以推测优化器试图优化 order by 时出现了错误：

第一阶段，优化器选择了索引 iabc，采用 range 访问；

第二阶段，优化器试图进一步优化执行计划，使用 order by 的列访问，并清空了第一阶段的结果；

第三阶段，优化器发现使用 order by 的列访问，代价比第一阶段的结果更大，但是第一阶段结果已经被清空了，无法还原，于是选择了代价较大的访问方式（index_scan），触发了bug。

我们在索引优化函数<code>sql_select::test_quick_select</code> 最开始的时候保存访问计划变量（quick）；

在索引没变的时候，还原这个变量；

在索引发生改变的时候，删除这个变量。

在不修改 mysql 源码的情况下，可以通过 force index 强制指定索引规避这个bug。

<code>sql_select::test_quick_select</code> 调用栈如下