问题描述
bug 触发条件如下：
优化器先选择了 where 条件中字段的索引，该索引过滤性较好；SQL 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化，最终因代价问题没有成功。
复现case
create table t1(id int auto_increment primary key, a int, b int, c int, v varchar(1000), key iabc(a,b,c), key ic(c)) engine = innodb;
insert into t1 select null,null,null,null,null;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;
insert into t1 select null,null,null,null,null from t1;
update t1 set a=id/2, b=id/4, c=6-id/8, v=repeat('a',1000);
explain select id from t1 where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | t1 | index | iabc,ic | iabc | 15 | NULL | 32 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
explain select id from t1 force index (iabc) where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| 1 | SIMPLE | t1 | range | iabc | iabc | 5 | NULL | 3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
从SELECT语句中可以看出，同样的语句，使用同样的INDEX，但使用了FORCE INDEX之后选择的执行计划不一样。当然如果数据量大的话，实际的执行性能也会差别很大。使用RANGE scan显然要优于INDEX scan的全扫描。
问题分析
MySQL下有个了解优化器工作过程的利器，其就是optimizer_trace。使用方法为：
SET OPTIMIZER_TRACE_MAX_MEM_SIZE=268435456;
SET optimizer_trace="enabled=on";
select id from t1 where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;
select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
MySQL5.6的输出：
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`id` AS `id` from `t1` where ((`t1`.`a` < 3) and (`t1`.`b` in (1,13)) and (`t1`.`c` >= 3)) order by `t1`.`c` limit 2"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`t1`.`a` < 3) and (`t1`.`b` in (1,13)) and (`t1`.`c` >= 3))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`a` < 3) and (`t1`.`b` in (1,13)) and (`t1`.`c` >= 3))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`a` < 3) and (`t1`.`b` in (1,13)) and (`t1`.`c` >= 3))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`a` < 3) and (`t1`.`b` in (1,13)) and (`t1`.`c` >= 3))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 32,
"cost": 9.5
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "iabc",
"usable": true,
"key_parts": [
"a",
"b",
"c",
"id"
] /* key_parts */
},
{
"index": "ic",
"usable": true,
"key_parts": [
"c",
"id"
] /* key_parts */
}
] /* potential_range_indices */,
"best_covering_index_scan": {
"index": "iabc",
"cost": 7.4718,
"chosen": true
} /* best_covering_index_scan */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "iabc",
"ranges": [
"NULL < a < 3"
] /* ranges */,
"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"
] /* ranges */,
"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 */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "iabc",
"rows": 3,
"ranges": [
"NULL < a < 3"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 3,
"cost_for_plan": 1.6146,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t1`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"rows": 3,
"cost": 2.2146,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2.2146,
"rows_for_plan": 3,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`t1`.`a` < 3) and (`t1`.`b` in (1,13)) and (`t1`.`c` >= 3))",
"attached_conditions_computation": [
{
"table": "`t1`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 2,
"row_estimate": 3,
"range_analysis": {
"table_scan": {
"rows": 32,
"cost": 40.4
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "iabc",
"usable": false,
"cause": "not_applicable"
},
{
"index": "ic",
"usable": true,
"key_parts": [
"c",
"id"
] /* key_parts */
}
] /* potential_range_indices */,
"best_covering_index_scan": {
"index": "iabc",
"cost": 7.4718,
"chosen": true
} /* best_covering_index_scan */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "ic",
"ranges": [
"3 <= c"
] /* ranges */,
"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 */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
} /* rechecking_index_usage */
}
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": "((`t1`.`a` < 3) and (`t1`.`b` in (1,13)) and (`t1`.`c` >= 3))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`t1`.`c`",
"items": [
{
"item": "`t1`.`c`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t1`.`c`"
} /* clause_processing */
},
{
"refine_plan": [
{
"table": "`t1`",
"access_type": "index_scan"
}
] /* refine_plan */
},
{
"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
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t1`",
"field": "c"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"limit": 2,
"rows_estimate": 936,
"row_size": 22,
"memory_available": 8388608,
"chosen": true
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 3,
"examined_rows": 32,
"number_of_tmp_files": 0,
"sort_buffer_size": 90,
"sort_mode": "<sort_key, additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
结合OPTIMIZER_TRACE的输出和MySQL5.6的代码，来复盘这个bug。由于MySQL优化器是个复杂的系统，这里不去解释每一行TRACE的输出，而是和该bug密切联系的部分，会做重点的分析。rows_estimation是用来计算一个表在不同的访问路径下（全表扫描、索引扫描、范围扫描等），数据库所要付出的代价。
table_scan:cost=io_cost+cpu_cost=(pages+1.1)+(rows*ROW_EVALUATE_COST+1 )=(1+1.1) + (32*0.2+1)=9.5
索引iabc作为覆盖索引，计算出代价为
cover_index:cost=io_cost+cpu_cost=(rows+base)/base+rows*ROW_EVALUATE_COST=7.4718
其中base的计算方式为 page_size/2/(length(iabc))
其中 length(iabc)=length(a)+length(b)+length(c)+length(id)+3
依次带入计算获得
cover_index:cost=7.4718
range:iabc:cost=io_cost+cpu_cost=(rows+base)/base + rows*ROW_EVALUATE_COST=(3+base)/base+3*0.2=1.6146
range:ic:cost=io_cost+cpu_cost=(rows+1)+rows*ROW_EVALUATE_COST=(17+1)+17*0.2=21.4
因此从代价角度来说，使用索引iabc，进行范围查询是代价最低的。现在看来一切正常
"rows_estimation": [
{
"table": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 32,
"cost": 9.5
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "iabc",
"usable": true,
"key_parts": [
"a",
"b",
"c",
"id"
] /* key_parts */
},
{
"index": "ic",
"usable": true,
"key_parts": [
"c",
"id"
] /* key_parts */
}
] /* potential_range_indices */,
"best_covering_index_scan": {
"index": "iabc",
"cost": 7.4718,
"chosen": true
} /* best_covering_index_scan */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "iabc",
"ranges": [
"NULL < a < 3"
] /* ranges */,
"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"
] /* ranges */,
"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 */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "iabc",
"rows": 3,
"ranges": [
"NULL < a < 3"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 3,
"cost_for_plan": 1.6146,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
]
下面进一步计算该方案的代价
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t1`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"rows": 3,
"cost": 2.2146,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2.2146,
"rows_for_plan": 3,
"chosen": true
}
]
由于iabc只使用了a列去取数据，而整个查询还有其他条件，需要进一步过滤，其计算代价方式为
cost_no_sort=range:iabc:cost+rows*ROW_EVALUATE_COST
cost_no_sort=1.6146+3*0.2=2.2146
问题原因
MySQL5.6遇到order by limit时，由于只考虑能够优化 order by的索引，而放弃了其他更好的索引，导致的bug，那么MySQL5.7的解决办法是进入low_limit后，不是只考虑优化order by的索引，而是考虑了全部索引。
索引ic的代价为
range:ic:cost=io_cost+cpu_cost=(rows+1)+rows*ROW_EVALUATE_COST=(17+1)+17*0.2=21.4
其甚至高出了cover:iabc:cost，因此在test_quick_select中放弃使用了range索引。
最后由make_join_select选择了一个index scan作为最终的执行计划。
