MySQL索引优化实践（一）

MySQL如何选择合适的索引

1、SQL查询可能会走索引也可能不会走索引

（1）Case 1：

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

以上的结果可以看到，上面的sql未使用索引；

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

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

 （2）Casse 2：

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

 出现的以上情况是不是很奇怪呢？它会走索引。若将查询条件的 'zzz' 变为 'a'，就不会走索引了，这个是为什么呢？

以上的结果MySQL可能估算了一下， 大于 'zzz'的记录会很少或者没有，会比全表扫描的情况好很多，那它就会走索引了。

MySQL走不走索引与很多条件都有关系，数据量大小，磁盘分页大小等等都有关系，它会计算一个成本（cost），然后再去比较哪个成本比较小，选择成本小的方式。

2、使用 Trace 工具

我们可以使用 trace 工具来查看 mysql 为什么不会走索引；开启 trace 工具会影响 MySQL的性能，所以只能临时分析 SQL 使用，在使用完成之后应该立即关闭。

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; ‐‐开启trace，并且输出结果以json形式展示
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

 查看 trace 的结果

{
  "steps": [
    {
      "join_preparation": {  ----  第一阶段：SQL准备阶段
        "select#": 1,
        "steps": [
          {
            "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`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {  ---- 第二阶段：SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": { ---- 条件处理
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` > 'a')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` > 'a')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [   ---- 表依赖详情
              {
                "table": "`employees`",
                "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": "`employees`",
                "range_analysis": {
                  "table_scan": {   ---- 全表扫描的情况
                    "rows": 3,         ---- 全表扫描行数
                    "cost": 3.7        ---- 查询成本
                  } /* table_scan */,
                  "potential_range_indexes": [  ---- 查询可能使用到的索引
                    {
                      "index": "PRIMARY",        ---- 主键索引
                      "usable": false,           ---- 是否会使用该索引
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_position",  ---- 辅助索引
                      "usable": true,            ---- 是否会使用该索引        
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "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": "idx_name_age_position",
                        "ranges": [    
                          "a < name"    ---- 索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,   ---- 使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false,       ---- 是否使用覆盖索引
                        "rows": 3,                 ---- 索引扫描行数
                        "cost": 4.61,             ---- 索引使用成本
                        "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 */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": {      ‐‐-- 最优访问路径
                  "considered_access_paths": [   ‐‐-- 最终选择的访问路径
                    {
                      "rows_to_scan": 3,
                      "access_type": "scan",      ‐‐-- 访问类型：为scan，全表扫描
                      "resulting_rows": 3,
                      "cost": 1.6,                ---- 查询成本
                      "chosen": true,             ‐‐-- 确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 1.6,
                "sort_cost": 3,
                "new_cost_for_plan": 4.6,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'a')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'a')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`employees`.`position`",
              "items": [
                {
                  "item": "`employees`.`position`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`employees`.`position`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "steps": [
              ] /* steps */,
              "index_order_summary": {
                "table": "`employees`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "unknown",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {  ‐‐-- 第三阶段：SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`employees`",
                "field": "position"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "rows": 3,
              "examined_rows": 3,
              "number_of_tmp_files": 0,
              "sort_buffer_size": 200704,
              "sort_mode": "<sort_key, packed_additional_fields>"
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
以上结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描

 使用 trace 工具分析一下 zzz  条件的SQL：

mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描；

 关闭trace

mysql> set session optimizer_trace="enabled=off"; ‐‐关闭trace

常见SQL深入优化

1、order by 和 group by

（1）Case 1：

EXPLAIN select * from employees where  name = 'LiLei' and POSITION = 'dev' ORDER BY age;

 分析：利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出；age 也走了索引，age列用在排序的过程中，但是Extra字段里没有using filesort，所以它也是走了索引的； 

   进一步分析：联合索引在存储的时候，先比较第一个字段，第一个字段一样则再比较第二个字段，以上的 SQL 查询的 第一个字段的 name 值都是一样的，第二个字段就是排好序的，所以第二个字段排序的时候会走索引。

 （2）Case 2：

EXPLAIN select * from employees where  name = 'LiLei' ORDER BY position;

 分析：从结果来看， key_len = 74，查询使用了 name 索引； 由于用了 position 排序，跳过了 age 字段，出现了 Using filesort ， 所以 position 没有走索引。

   进一步分析：联合索引在存储的时候，先比较第一个字段，第一个字段一样则再比较第二个字段，第二个字段一样再去比较第三个字段；以上的 SQL 查询的 第一个字段的 name 值都是一样的，现在直接按照第三个字段 position 去排序，这个没有办法去走索引的，因为第 2 个字段可以为任何值，则第3个字段是乱序的，只能额外的去排序了。

（3）Case 3：

EXPLAIN select * from employees where  name = 'LiLei' and age = 1 ORDER BY age, position;

 分析：查找只用到索引name，age 和 position 用于排序，无Using filesort。

（4）Case 4：

EXPLAIN select * from employees where  name = 'LiLei' ORDER BY position,age;

 分析： 查询只用到 name 字段的索引， 排序没有走索引；

  进一步分析：联合索引在存储的时候，先比较第一个字段，第一个字段一样则再比较第二个字段，第二个字段一样再去比较第三个字段；以上的 SQL 查询的第一个字段的 name 值都是一样的，然后先要按照 position 排序，再按照 age 排序，这个和索引的存储的顺序是不符合的，所以需要去做额外的排序；

（5）Case 5：

EXPLAIN select * from employees where  name = 'LiLei' and age = 1 ORDER BY position,age;

 分析：联合索引在存储的时候，先比较第一个字段，第一个字段一样则再比较第二个字段，第二个字段一样再去比较第三个字段；以上的 SQL 查询的第一个字段的 name、age 值都是一样的，走索引查询的值都是一样的，先要按照 position 排序，再按照 age 排序，因为 age 都一样，所以相当于 age的排序 是没有用的，所以排序也走索引了，不需要额外的排序了， extra 中 无 Using filesort；

（6）Case 6：

EXPLAIN select * from employees where  name = 'LiLei' ORDER BY age asc, position desc;

 分析：联合索引在存储的时候，先比较第一个字段，第一个字段一样则再比较第二个字段，第二个字段一样再去比较第三个字段，按照升序去存储的；以上的 SQL 查询的第一个字段的 name 值都是一样的， 第二个字段升序，第三个字段降序；但是索引在存储的时候（name，age，position）是一个整体的，以这个整体去存储的，则在查询的时候所有字段降序 或 所有字段升序，这样才会去走索引。Mysql8以上版本有降序索引可以支持该种查询方式。

（7）Case 7：

EXPLAIN select * from employees where  name in ('LiLei', 'zhuge') ORDER BY age, position;

结论： in  有可能走索引，也有可能不走索引，可以使用 trace 工具查一下；一般来说 是主键索引会走，但是 in 里面的元素太多也不会走，如果是非主键所以一般都不会走。

分析：以上的SQL，不管 in 会不会走索引，排序都不会走索引的；联合索引在存储的时候，先比较第一个字段，第一个字段一样则再比较第二个字段，第二个字段一样再去比较第三个字段；第一个字段 name 有两个值，取出来的数据的第2、3字段都是乱序的，所以需要额外的排序的。

（8）Case 8：

EXPLAIN select * from employees where  name > 'a' ORDER BY name;

 可以使用覆盖索引优化

EXPLAIN select name,age,position from employees where  name > 'a' ORDER BY name;

 优化总结

1、MySQL支持两种方式的排序 filesort 和 index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

1) order by语句使用索引最左前列。

2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果 order by 的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引。

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上 order by null 禁止排序。注意，where 高于having，能写在where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解

filesort文件排序方式

（1）单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields>

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

选用那种排序方式？

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

（1）如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 单路排序模式；

（2）如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 双路排序模式。

针对那些没有办法将排序优化的走索引的SQL，我们就需要看一下 Using filesort 走的是单路排序还是双路排序了。

示例：

EXPLAIN select * from employees where name = 'zhuge' ORDER BY position;

 使用 trace 工具查询

 （1）max_length_for_sort_data 的默认值为 1024；

set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启trace
select * from employees where name = 'zhuge' order by position;
select * from information_schema.OPTIMIZER_TRACE;

trace的部分结果：

"join_execution": { ‐‐Sql执行阶段
    "select#": 1,
    "steps": [
        {
             "filesort_information": [
                 {
                     "direction": "asc",
                     "table": "`employees`",
                     "field": "position"
                 }
             ] /* filesort_information */,
             "filesort_priority_queue_optimization": {
                 "usable": false,
                 "cause": "not applicable (no LIMIT)"
             } /* filesort_priority_queue_optimization */,
             "filesort_execution": [
             ] /* filesort_execution */,
             "filesort_summary": { ‐‐文件排序信息
               "rows": 10000, ‐‐预计扫描行数
               "examined_rows": 10000, ‐‐参数排序的行
               "number_of_tmp_files": 3, ‐‐使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
               "sort_buffer_size": 262056, ‐‐排序缓存的大小
               "sort_mode": "<sort_key, packed_additional_fields>" ‐‐排序方式，这里用的单路排序
             } /* filesort_summary */
         }
     ] /* steps */
 } /* join_execution */

（2）将 max_length_for_sort_data 的值设置为 10；

set max_length_for_sort_data = 10; #employees表所有字段长度总和肯定大于10字节
set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启trace
select*from employees where name ='zhuge'orderby position;
select*from information_schema.OPTIMIZER_TRACE;

trace的部分结果：
"join_execution": {
    "select#": 1,
    "steps": [
        {
            "filesort_information": [
                {
                    "direction": "asc",
                    "table": "`employees`",
                    "field": "position"
                }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
                "usable": false,
                "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
                "rows": 10000,
                "examined_rows": 10000,
                "number_of_tmp_files": 2,
                "sort_buffer_size": 262136,
                "sort_mode": "<sort_key, rowid>" ‐‐排序方式，这里用的双路排序
            } /* filesort_summary */
        }
    ] /* steps */
} /* join_execution */

关闭 trace 

set session optimizer_trace="enabled=offf"; #关闭trace

我们先看单路排序的详细过程：

1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id；

2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中；

3. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id；

4. 重复步骤 2、3 直到不满足 name = ‘zhuge’；

5. 对 sort_buffer 中的数据按照字段 position 进行排序；

6. 返回结果给客户端；

我们再看下双路排序的详细过程：

1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id；

2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中；

3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id；

4. 重复 3、4 直到不满足 name = ‘zhuge’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(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。