- 优化器先选择了 where 条件中字段的索引,该索引过滤性较好;
- SQL 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化,最终因代价问题没有成功。
表结构
构造数据
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 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
ref: NULL
Extra: Using where; Using index; Using filesort
optimizer_trace 可以帮助分析这个问题。
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
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
}
]
},
"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。
- 我们在索引优化函数
SQL_SELECT::test_quick_select
最开始的时候保存访问计划变量(quick); - 在索引没变的时候,还原这个变量;
- 在索引发生改变的时候,删除这个变量。
在不修改 mysql 源码的情况下,可以通过 force index 强制指定索引规避这个bug。
#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
#11 do_command