Mysql 如何选择合适的索引

  1. mysql> EXPLAIN select * from employees where name > 'a';

image.png

如果用 name 索引需要遍历 name 字段联合索引树，然后还需要根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高，可以用覆盖索引优化，这样只需要遍历 name 字段的联合索引树就能拿到所有结果，如下：

  1. mysql> EXPLAIN select name,age,position from employees where name > 'a';

image.png

  1. mysql> EXPLAIN select * from employees where name > 'zzz' ;

image.png

对于上面这两种 name>'a' 和 name>'zzz' 的执行结果，mysql 最终是否选择走索引或者一张表涉及多个索引，mysql 最
终如何选择索引，我们可以用 trace工具 来一查究竟，开启 trace 工具会影响 mysql 性能，所以只能临时分析 sql 使用，用
完之后立即关闭

  1. set session optimizer_trace="enabled=on", end_markers_in_json=on; ‐‐开启trace
  1. select * from employees where name > 'a' order by position;
  2. SELECT * FROM information_schema.OPTIMIZER_TRACE;
  1. {
  2.   "steps": [
  3.     {
  4.       "join_preparation": {  ‐‐ 第一阶段：SQL准备阶段
  5.         "select#": 1,
  6.         "steps": [
  7.           {
  8.             "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
  9.           }
  10.         ] /* steps */
  11.       } /* join_preparation */
  12.     },
  13.     {
  14.       "join_optimization": {  ‐‐ 第二阶段：SQL优化阶段
  15.         "select#": 1,
  16.         "steps": [
  17.           {
  18.             "condition_processing": {  ‐‐ 条件处理
  19.               "condition": "WHERE",
  20.               "original_condition": "(`employees`.`name` > 'a')",
  21.               "steps": [
  22.                 {
  23.                   "transformation": "equality_propagation",
  24.                   "resulting_condition": "(`employees`.`name` > 'a')"
  25.                 },
  26.                 {
  27.                   "transformation": "constant_propagation",
  28.                   "resulting_condition": "(`employees`.`name` > 'a')"
  29.                 },
  30.                 {
  31.                   "transformation": "trivial_condition_removal",
  32.                   "resulting_condition": "(`employees`.`name` > 'a')"
  33.                 }
  34.               ] /* steps */
  35.             } /* condition_processing */
  36.           },
  37.           {
  38.             "substitute_generated_columns": {
  39.             } /* substitute_generated_columns */
  40.           },
  41.           {
  42.             "table_dependencies": [  ‐‐ 表依赖详情
  43.               {
  44.                 "table": "`employees`",
  45.                 "row_may_be_null": false,
  46.                 "map_bit": 0,
  47.                 "depends_on_map_bits": [
  48.                 ] /* depends_on_map_bits */
  49.               }
  50.             ] /* table_dependencies */
  51.           },
  52.           {
  53.             "ref_optimizer_key_uses": [
  54.             ] /* ref_optimizer_key_uses */
  55.           },
  56.           {
  57.             "rows_estimation": [  ‐‐ 预估表的访问成本
  58.               {
  59.                 "table": "`employees`",
  60.                 "range_analysis": {
  61.                   "table_scan": {  ‐‐ 全表扫描情况
  62.                     "rows": 3, ‐‐ 扫描行数
  63.                     "cost": 3.7 ‐‐ 查询成本
  64.                   } /* table_scan */,
  65.                   "potential_range_indexes": [ ‐‐ 查询可能使用的索引
  66.                     {
  67.                       "index": "PRIMARY", ‐‐ 主键索引
  68.                       "usable": false,
  69.                       "cause": "not_applicable"
  70.                     },
  71.                     {
  72.                       "index": "idx_name_age_position", ‐‐ 辅助索引
  73.                       "usable": true,
  74.                       "key_parts": [
  75.                         "name",
  76.                         "age",
  77.                         "position",
  78.                         "id"
  79.                       ] /* key_parts */
  80.                     },
  81.                     {
  82.                       "index": "idx_age",
  83.                       "usable": false,
  84.                       "cause": "not_applicable"
  85.                     }
  86.                   ] /* potential_range_indexes */,
  87.                   "setup_range_conditions": [
  88.                   ] /* setup_range_conditions */,
  89.                   "group_index_range": {
  90.                     "chosen": false,
  91.                     "cause": "not_group_by_or_distinct"
  92.                   } /* group_index_range */,
  93.                   "analyzing_range_alternatives": { ‐‐ 分析各个索引使用成本
  94.                     "range_scan_alternatives": [
  95.                       {
  96.                         "index": "idx_name_age_position",
  97.                         "ranges": [  ‐‐ 索引使用范围
  98.                           "a < name"
  99.                         ] /* ranges */,
  100.                         "index_dives_for_eq_ranges": true,
  101.                         "rowid_ordered": false,  ‐‐ 使用该索引获取的记录是否按照主键排序
  102.                         "using_mrr": false,
  103.                         "index_only": false, ‐‐ 是否使用覆盖索引
  104.                         "rows": 3,  ‐‐ 索引扫描行数
  105.                         "cost": 4.61, ‐‐ 索引使用成本
  106.                         "chosen": false, ‐‐ 是否选择该索引
  107.                         "cause": "cost"
  108.                       }
  109.                     ] /* range_scan_alternatives */,
  110.                     "analyzing_roworder_intersect": {
  111.                       "usable": false,
  112.                       "cause": "too_few_roworder_scans"
  113.                     } /* analyzing_roworder_intersect */
  114.                   } /* analyzing_range_alternatives */
  115.                 } /* range_analysis */
  116.               }
  117.             ] /* rows_estimation */
  118.           },
  119.           {
  120.             "considered_execution_plans": [
  121.               {
  122.                 "plan_prefix": [
  123.                 ] /* plan_prefix */,
  124.                 "table": "`employees`",
  125.                 "best_access_path": {  ‐‐ 最优访问路径
  126.                   "considered_access_paths": [
  127.                     {
  128.                       "rows_to_scan": 3,
  129.                       "access_type": "scan",  ‐‐ 访问类型：为 scan，全表扫描
  130.                       "resulting_rows": 3,
  131.                       "cost": 1.6,
  132.                       "chosen": true, ‐‐ 确定选择
  133.                       "use_tmp_table": true  
  134.                     }
  135.                   ] /* considered_access_paths */
  136.                 } /* best_access_path */,
  137.                 "condition_filtering_pct": 100,
  138.                 "rows_for_plan": 3,
  139.                 "cost_for_plan": 1.6,
  140.                 "sort_cost": 3,
  141.                 "new_cost_for_plan": 4.6,
  142.                 "chosen": true
  143.               }
  144.             ] /* considered_execution_plans */
  145.           },
  146.           {
  147.             "attaching_conditions_to_tables": {
  148.               "original_condition": "(`employees`.`name` > 'a')",
  149.               "attached_conditions_computation": [
  150.               ] /* attached_conditions_computation */,
  151.               "attached_conditions_summary": [
  152.                 {
  153.                   "table": "`employees`",
  154.                   "attached": "(`employees`.`name` > 'a')"
  155.                 }
  156.               ] /* attached_conditions_summary */
  157.             } /* attaching_conditions_to_tables */
  158.           },
  159.           {
  160.             "clause_processing": {
  161.               "clause": "ORDER BY",
  162.               "original_clause": "`employees`.`position`",
  163.               "items": [
  164.                 {
  165.                   "item": "`employees`.`position`"
  166.                 }
  167.               ] /* items */,
  168.               "resulting_clause_is_simple": true,
  169.               "resulting_clause": "`employees`.`position`"
  170.             } /* clause_processing */
  171.           },
  172.           {
  173.             "reconsidering_access_paths_for_index_ordering": {
  174.               "clause": "ORDER BY",
  175.               "steps": [
  176.               ] /* steps */,
  177.               "index_order_summary": {
  178.                 "table": "`employees`",
  179.                 "index_provides_order": false,
  180.                 "order_direction": "undefined",
  181.                 "index": "unknown",
  182.                 "plan_changed": false
  183.               } /* index_order_summary */
  184.             } /* reconsidering_access_paths_for_index_ordering */
  185.           },
  186.           {
  187.             "refine_plan": [
  188.               {
  189.                 "table": "`employees`"
  190.               }
  191.             ] /* refine_plan */
  192.           }
  193.         ] /* steps */
  194.       } /* join_optimization */
  195.     },
  196.     {
  197.       "join_execution": { ‐‐ 第三阶段：SQL执行阶段
  198.         "select#": 1,
  199.         "steps": [
  200.           {
  201.             "filesort_information": [
  202.               {
  203.                 "direction": "asc",
  204.                 "table": "`employees`",
  205.                 "field": "position"
  206.               }
  207.             ] /* filesort_information */,
  208.             "filesort_priority_queue_optimization": {
  209.               "usable": false,
  210.               "cause": "not applicable (no LIMIT)"
  211.             } /* filesort_priority_queue_optimization */,
  212.             "filesort_execution": [
  213.             ] /* filesort_execution */,
  214.             "filesort_summary": {
  215.               "rows": 3,
  216.               "examined_rows": 3,
  217.               "number_of_tmp_files": 0,
  218.               "sort_buffer_size": 200704,
  219.               "sort_mode": "<sort_key, packed_additional_fields>"
  220.             } /* filesort_summary */
  221.           }
  222.         ] /* steps */
  223.       } /* join_execution */
  224.     }
  225.   ] /* steps */
  226. }

结论：全表扫描的成本低于索引扫描，所以 mysql 最终选择全表扫描 

  1. select * from employees where name > 'zzz' order by position;
  2. SELECT * FROM information_schema.OPTIMIZER_TRACE;
  4. 查看 trace 字段可知索引扫描的成本低于全表扫描，所以 mysql 最终选择索引扫描
  1. set session optimizer_trace="enabled=off"; ‐‐ 关闭trace

Order by 与 Group by 优化

  • Case 1：

    1. EXPLAIN select * from employees where name = 'LiLei' and position = 'dev' order by age;

    image.png
    利用最左前缀法则：中间字段不能断，因此查询用到了 name 索引，从 key_len=74 也能看出，age 索引列用在 排序 过程中，因为 Extra字段里没有 using filesort

  • Case 2：

    1. EXPLAIN select * from employees where name = 'LiLei' order by position;

    image.png
    从explain的执行结果来看：key_len=74，查询使用了 name 索引，由于用了 position 进行排序，跳过了 age，出现了 Using filesort

  • Case 3：

    1. EXPLAIN select * from employees where name = 'LiLei' order by age, position;

    image.png
    查找只用到索引 name，age 和 position 用于排序，无 Using filesort

  • Case 4：

    1. EXPLAIN select * from employees where name = 'LiLei' order by position, age;

    image.png
    和 Case 3 中 explain 的执行结果一样，但是出现了 Using filesort，因为索引的创建顺序为 name, age, position，但是排序的时候age 和 position 颠倒位置了

  • Case 5：

    1. EXPLAIN select * from employees where name = 'LiLei' and age = 18 order by position, age;

    image.png
    与Case 4对比，在Extra中并未出现 Using filesort，因为 age 为常量，在排序中被优化，所以索引未颠倒， 不会出现 Using filesort

  • Case 6：

    1. EXPLAIN select * from employees where name = 'LiLei' order by age ASC, position DESC;

    image.png
    虽然排序的字段列与索引顺序一样，且 order by 默认升序，这里 position desc 变成了降序，导致与索引的排序方式不同，从而产生 Using filesort。Mysql8 以上版本有降序索引可以支持该种查询方式

  • Case 7：

    1. EXPLAIN select * from employees where name in ('LiLei', 'HanMeiMei') order by age, position;

    image.png
    对于排序来说，多个相等条件也是范围查询

  • Case 8：

    1. EXPLAIN select * from employees where name > 'a' order by name;

    image.png
    可以用覆盖索引优化

    1. EXPLAIN select name, age, position from employees where name > 'a' order by name;

    image.png

优化总结：

  1. MySQL 支持两种方式的排序 filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。
  2. order by 满足两种情况会使用 Using index。
  • order by 语句使用索引最左前列。
  • 使用 where 子句与 order by 子句条件列组合满足索引最左前列。
  1. 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
  2. 如果 order by 的条件不在索引列上，就会产生 Using filesort。
  3. 能用覆盖索引尽量用覆盖索引
  4. group by 与 order by 很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上 order by null 禁止排序。注意，where 高于 having，能写在 where 中的限定条件就不要去 having 限定。

Using filesort 文件排序原理详解

filesort 文件排序方式

  • 单路排序：是一次性取出满足条件行的所有字段，然后在 sort buffer 中进行排序；用 trace 工具可以看到 sort_mode 信息里显示 <sort_key, additional_fields> 或者 <sort_key, packed_additional_fields>
  • 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用 trace 工具可以看到 sort_mode 信息里显示<sort_key, rowid>

MySQL 通过比较系统变量 max_length_for_sort_data （默认1024字节）的大小和需要查询的字段总大小来判断使用哪种排序模式。

  • 如果查询字段的总长度小于 max_length_for_sort_data 的值，那么使用单路排序模式
  • 如果查询字段的总长度大于 max_length_for_sort_data 的值，那么使用双路排序模式

未修改 max_length_for_sort_data 时：

  1. set session optimizer_trace="enabled=on", end_markers_in_json=on;
  2. select * from employees where name = 'LiLi' order by position;
  3. SELECT * FROM information_schema.OPTIMIZER_TRACE;
  1. "filesort_execution": [
  2.  ],
  3. "filesort_summary": { ‐‐文件排序信息
  4. "rows": 10000, ‐‐预计扫描行数
  5. "examined_rows": 10000, ‐‐参数排序的行
  6. "number_of_tmp_files": 3, ‐‐使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
  7. "sort_buffer_size": 262056,
  8. "sort_mode": "<sort_key, packed_additional_fields>" ‐‐排序方式，这里用的单路排序
  9. }

未修改 max_length_for_sort_data 之后：

  1. set max_length_for_sort_data = 10;
  2. select * from employees where name = 'LiLi' order by position;
  3. SELECT * FROM information_schema.OPTIMIZER_TRACE;
  1. "filesort_execution": [
  2.  ],
  3. "filesort_summary": { ‐‐文件排序信息
  4. "rows": 10000, ‐‐预计扫描行数
  5. "examined_rows": 10000, ‐‐参数排序的行
  6. "number_of_tmp_files": 2, ‐‐使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
  7. "sort_buffer_size":  262136,
  8. "sort_mode": "<sort_key, rowid>" ‐‐排序方式，这里用的双路排序
  9. }

单路排序的详细过程：

  1. 从索引name找到第一个满足 name = ‘LiLi’ 条件的主键 id
  2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
  3. 从索引name找到下一个满足 name = ‘LiLi’ 条件的主键 id
  4. 重复步骤 2、3 直到不满足 name = ‘LiLi’
  5. 对 sort_buffer 中的数据按照字段 position 进行排序
  6. 返回结果给客户端

双路排序的详细过程：

  1. 从索引 name 找到第一个满足 name = ‘LiLi’ 的主键id
  2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
  3. 从索引 name 取下一个满足 name = ‘LiLi’ 记录的主键 id
  4. 重复 3、4 直到不满足 name = ‘LiLi’
  5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
  6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。

如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。

所以，MySQL 通过 **max_length_for_sort_data** 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。

注意：如果全部使用 sort_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort_buffer（默认1024 KB），MySQL 很多参数设置都是做过优化的，不要轻易调整。