bug 触发条件如下:
表结构
- 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 时出现了错误:
最开始的时候保存访问计划变量(quick);
- SQL_SELECT::test_quick_select
在不修改 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
来源: http://mysql.taobao.org/monthly/2015/11/10/