问题描述

  1. 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;
  3. insert into t1 select null,null,null,null,null;
  4. insert into t1 select null,null,null,null,null from t1;
  5. insert into t1 select null,null,null,null,null from t1;
  6. insert into t1 select null,null,null,null,null from t1;
  7. insert into t1 select null,null,null,null,null from t1;
  8. insert into t1 select null,null,null,null,null from t1;
  10. update t1 set a=id/2, b=id/4, c=6-id/8, v=repeat('a',1000);
  12. explain select id from t1 where a<3 and b in (1, 13) and c>=3 order by c desc limit 2;
  13. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
  14. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
  15. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
  16. |  1 | SIMPLE      | t1    | index | iabc,ic       | iabc | 15      | NULL |   32 | Using where; Using index; Using filesort |
  17. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
  19. 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;
  20. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
  21. | id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
  22. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
  23. |  1 | SIMPLE      | t1    | range | iabc          | iabc | 5       | NULL |    3 | Using where; Using index; Using filesort |
  24. +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+

从SELECT语句中可以看出，同样的语句，使用同样的INDEX，但使用了FORCE INDEX之后选择的执行计划不一样。当然如果数据量大的话，实际的执行性能也会差别很大。使用RANGE scan显然要优于INDEX scan的全扫描。

另外此bug引发的另一个问题是，由于使用了LIMIT语句，导致选择的INDEX不是最优的INDEX。

问题分析：

使用如下命令打开Optimizer trace

  1. SET OPTIMIZER_TRACE_MAX_MEM_SIZE=268435456(i.e. 256M);
  2. SET optimizer_trace="enabled=on";

执行上面的查询语句，可以看到optimizer trace的输出结果如下，请注意里面重点部位的注释（以’//’开头部分)：

  1. "considered_execution_plans": [\
  2.               {\
  3.                 "plan_prefix": [\
  4.                 ],\
  5.                 "table": "`t1`",\
  6.                 "best_access_path": {\
  7.                   "considered_access_paths": [\
  8.                     {\
  9.                       "access_type": "range",\  
  10.                       // 这里我们可以看到，优化器通过代价选择的最佳访问方式是RANGE scan
  11.                       "rows": 3,\
  12.                       "cost": 2.2146,\
  13.                       "chosen": true\
  14.                     }\
  15.                   ]\
  16.                 },\
  17.                 "cost_for_plan": 2.2146,\
  18.                 "rows_for_plan": 3,\
  19.                 "chosen": true\
  20.               }\
  21.             ]\
  22. 但是接下来我们可以看到：
  23.        "refine_plan": [\
  24.               {\
  25.                 "table": "`t1`",\
  27.                 "access_type": "index_scan"\ //这里强制使用了INDEX scan
  28.               }\
  29.             ]\

这里显示的是optimizer在执行优化器的第四个阶段，PLAN REFINEMENT的时候，最后选择了INDEX scan。所以我们可以大致确定错误发生的地方。另外有问题的query带有LIMIT，因此基本可以确定问题发生在了make_join_select函数中。

make_join_select函数中有下面一段逻辑：

  1. if (!tab->const_keys.is_clear_all() &&               // 有依赖于常量的索引条件表达式
  2.                    i == join->const_tables &&        // 是第一个非常量表
  3.                    (join->unit->select_limit_cnt <
  4.                     tab->position->records_read) &&  
  5.                     // 有Limit条件且需要返回的行数比估计扫描的行数少
  6.                    !(join->select_options & OPTION_FOUND_ROWS))  // 没有SQL_CALC_FOUND_ROWS
  7.             recheck_reason= LOW_LIMIT;  // 这里MySQL开始对Limit语句进行优化
  8.             ...
  9. // 检查是否有RANGE scan可以使用
  10. if ((recheck_reason != DONT_RECHECK) &&
  11.                 sel->test_quick_select(thd, usable_keys,
  12.                                        used_tables & ~tab->table->map,
  13.                                        (join->select_options &
  14.                                         OPTION_FOUND_ROWS ?
  15.                                         HA_POS_ERROR :
  16.                                         join->unit->select_limit_cnt),
  17.                                        false,   // don't force quick range
  18.                                        interesting_order) < 0)
  19.             {
  20. 这里usable_keys是描述可以用来对ORDER BY列进行索引排序的可能的所有索引的MAP。上面的函数会查找这些可用的索引是否可以进行更高效RANGE
  21. 扫描。但是通过问题query的条件表达式，这里没有找到对应的RANGE扫描，所以最后的执行计划输出只是使用了一个COVERING index.

问题解决

解决方式是需要将原来已经选好的RANGE scan与用来进行排序的索引扫描代价进行比较，比较哪种扫描方式对于增加ORDER BY操作后的代价更低，进而选择一个代价最优的扫描方式。下面是一个相关的patch。

  1. if (!tab->const_keys.is_clear_all() &&               // 有依赖于常量的索引条件表达式
  2.                    i == join->const_tables &&        // 是第一个非常量表
  3.                    (join->unit->select_limit_cnt <
  4.                     tab->position->records_read) &&  // 有Limit条件且需要返回的行数比估计的扫描的行数少
  5.                    !(join->select_options & OPTION_FOUND_ROWS))  // 没有SQL_CALC_FOUND_ROWS
  6.             recheck_reason= LOW_LIMIT;  //这里MySQL会去对Limit语句进行优化
  7.             ...
  8. +              if (recheck_reason != DONT_RECHECK)
  9.                {
  10. -                recheck_reason= DONT_RECHECK;
  11. +                int best_key= -1;
  12. +                ha_rows select_limit= join->unit->select_limit_cnt;
  13. +
  14. +                // 对所有可用的INDEX计算排序代价，选择一个代价最优的INDEX
  15.                  // 注意：这里的usable_keys包含所有可用索引，而不只是原来版本中只包含可以用来排序的索引
  16. +                test_if_cheaper_ordering(tab, join->order, tab->table,
  17. +                                         usable_keys, -1, select_limit,
  18. +                                         &best_key, &read_direction,
  19. +                                         &select_limit);
  20.                  // 如果没有找到任何可用的INDEX，那就默认使用原来的扫描方式
  21. +                if (best_key < 0)
  22. +                  recheck_reason= DONT_RECHECK; // No usable keys
  23. +                else
  24. +                {
  25. +                  // 找到一个最优的INDEX，我们只需要设置可用的INDEX，接下来查看一下是否有RANGE scan即可
  26. +                  usable_keys.clear_all();
  27. +                  usable_keys.set_bit(best_key);
  28. +                  interesting_order= (read_direction == -1 ? ORDER::ORDER_DESC :
  29. +                                      ORDER::ORDER_ASC);
  30. +                }
  31.                }
  32.              }
  33. if ((recheck_reason != DONT_RECHECK) &&
  34.                 sel->test_quick_select(thd, usable_keys,
  35.                                        used_tables & ~tab->table->map,
  36.                                        (join->select_options &
  37.                                         OPTION_FOUND_ROWS ?
  38.                                         HA_POS_ERROR :
  39.                                         join->unit->select_limit_cnt),
  40.                                        false,   // don't force quick range
  41.                                        interesting_order) < 0)
  42.             {
  43.             ...

可以看到最终效果是：

  1. EXPLAIN SELECT id FROM t1 WHERE a<3 AND b IN (1, 13) AND c>=3 ORDER BY c DESC LIMIT 2;
  2. id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
  3. 1    SIMPLE        t1        range    iabc,ic            iabc    5    NULL    4    Using index condition; Using filesort