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

优化器先选择了 where 条件中字段的索引，该索引过滤性较好； SQL 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化，最终因代价问题没有成功。 复现case

表结构

create table t1(

 id int auto_increment primary key,

 a int, b int, c int,

 key iabc (a, b, c),

 key ic (c)

) engine = innodb;

构造数据

insert into t1 select null,null,null,null;

insert into t1 select null,null,null,null from t1;

insert into t1 select null,null,null,null from t1;

insert into t1 select null,null,null,null from t1;

insert into t1 select null,null,null,null from t1;

insert into t1 select null,null,null,null from t1;

update t1 set a = id / 2, b = id / 4, c = 6 - id / 8;

触发SQL

mysql explain select id from t1 where a 3 and b in (1, 13) and c =3 order by c limit 2\G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: t1

 type: index

possible_keys: iabc,ic

 key: iabc

 key_len: 15

 ref: NULL

 rows: 32

 Extra: Using where; Using index; Using filesort

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

mysql explain select id from t1 force index(iabc) where a 3 and b in (1, 13) and c =3 order by c limit 2\G

*************************** 1. row ***************************

 id: 1

 select_type: SIMPLE

 table: t1

 type: range

possible_keys: iabc

 key: iabc

 key_len: 5

 ref: NULL

 rows: 3

 Extra: Using where; Using index; Using filesort

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

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

 "range_scan_alternatives": [

 "index": "iabc",

 "ranges": [

 "NULL a 3"

 "index_dives_for_eq_ranges": true,

 "rowid_ordered": false,

 "using_mrr": false,

 "index_only": true,

 "rows": 3,

 "cost": 1.6146,

 "chosen": true

 "index": "ic",

 "ranges": [

 "3 = c"

 "index_dives_for_eq_ranges": true,

 "rowid_ordered": false,

 "using_mrr": false,

 "index_only": false,

 "rows": 17,

 "cost": 21.41,

 "chosen": false,

 "cause": "cost"

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

 "considered_execution_plans": [

 "plan_prefix": [

 "table": "`t1`",

 "best_access_path": {

 "considered_access_paths": [

 "access_type": "range",

 "rows": 3,

 "cost": 2.2146,

 "chosen": true

 "cost_for_plan": 2.2146,

 "rows_for_plan": 3,

 "chosen": true

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

 {

 "clause_processing": {

 "clause": "ORDER BY",

 "original_clause": "`t1`.`c`",

 "items": [

 "item": "`t1`.`c`"

 "resulting_clause_is_simple": true,

 "resulting_clause": "`t1`.`c`"

 "refine_plan": [

 "table": "`t1`",

 "access_type": "index_scan"

 "reconsidering_access_paths_for_index_ordering": {

 "clause": "ORDER BY",

 "index_order_summary": {

 "table": "`t1`",

 "index_provides_order": false,

 "order_direction": "undefined",

 "index": "unknown",

 "plan_changed": false

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

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

第一阶段，优化器选择了索引 iabc，采用 range 访问； 第二阶段，优化器试图进一步优化执行计划，使用 order by 的列访问，并清空了第一阶段的结果； 第三阶段，优化器发现使用 order by 的列访问，代价比第一阶段的结果更大，但是第一阶段结果已经被清空了，无法还原，于是选择了代价较大的访问方式（index_scan），触发了bug。 我们在索引优化函数SQL_SELECT::test_quick_select 最开始的时候保存访问计划变量（quick）； 在索引没变的时候，还原这个变量； 在索引发生改变的时候，删除这个变量。

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

SQL_SELECT::test_quick_select 调用栈如下

 #0 SQL_SELECT::test_quick_select

 #1 make_join_select

 #2 JOIN::optimize

 #3 mysql_execute_select

 #4 mysql_select

 #5 mysql_explain_unit

 #6 explain_query_expression

 #7 execute_sqlcom_select

 #8 mysql_execute_command

 #9 mysql_parse

 #10 dispatch_command

 #11 do_command

【MySQL笔记】ORDER BY是如何工作的？ 每次业务功能中总有按操作时间排序，或者按其他字段排序的需求，一想到排序我们就会想到MySQL中的ORDER BY，那在使用的过程中，我们是否会存在不合理的使用导致查询的速度下降呢？
db匠 rds内核团队秘密研发的全自动卖萌机. 追加特效: 发数据库内核月报. 月报传送: http://mysql.taobao.org/monthly/