深入理解mysql-第十二章 mysql查询优化-Explain 详解（下）

叁歲伎倆 2023-01-24 02:07 52阅读 0赞

我们前面两章详解了Explain的各个属性，我们看到的都是mysql已经生成的执行计划，那这个执行计划的是如何生成的？我们能看到一些过程指标数据吗？实际mysql贴心为我们提供了执行计划的各项成本评估指标的以及优化器生成执行计划的整个过程的方法。

# 一、查看执行计划计算的成本数据 #

我们上边介绍的`EXPLAIN`语句输出中缺少了一个衡量执行计划好坏的重要属性 -成本。那如何查看具体的成本呢？`MySQL也`贴心的为我们提供了一种查看某个执行计划花费的成本的方式-Json格式的执行计划。我们只需要在`EXPLAIN`单词和真正的查询语句中间加上`FORMAT=JSON。`

mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G
    *************************** 1. row ***************************
    
    EXPLAIN: {
      "query_block": {
        "select_id": 1,     # 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1
        "cost_info": {
          "query_cost": "3197.16"   # 整个查询的执行成本预计为3197.16
        },
        "nested_loop": [    # 几个表之间采用嵌套循环连接算法执行
        
        # 以下是参与嵌套循环连接算法的各个表的信息
          {
            "table": {
              "table_name": "s1",   # s1表是驱动表
              "access_type": "ALL",     # 访问方法为ALL，意味着使用全表扫描访问
              "possible_keys": [    # 可能使用的索引
                "idx_key1"
              ],
              "rows_examined_per_scan": 9688,   # 查询一次s1表大致需要扫描9688条记录
              "rows_produced_per_join": 968,    # 驱动表s1的扇出是968
              "filtered": "10.00",  # condition filtering代表的百分比
              "cost_info": {
                "read_cost": "1840.84",     # 稍后解释
                "eval_cost": "193.76",      # 稍后解释
                "prefix_cost": "2034.60",   # 单次查询s1表总共的成本
                "data_read_per_join": "1M"  # 读取的数据量
              },
              "used_columns": [     # 执行查询中涉及到的列
                "id",
                "key1",
                "key2",
                "key3",
                "key_part1",
                "key_part2",
                "key_part3",
                "common_field"
              ],
              
              # 对s1表访问时针对单表查询的条件
              "attached_condition": "((`xiaohaizi`.`s1`.`common_field` = 'a') and (`xiaohaizi`.`s1`.`key1` is not null))"
            }
          },
          {
            "table": {
              "table_name": "s2",   # s2表是被驱动表
              "access_type": "ref",     # 访问方法为ref，意味着使用索引等值匹配的方式访问
              "possible_keys": [    # 可能使用的索引
                "idx_key2"
              ],
              "key": "idx_key2",    # 实际使用的索引
              "used_key_parts": [   # 使用到的索引列
                "key2"
              ],
              "key_length": "5",    # key_len
              "ref": [      # 与key2列进行等值匹配的对象
                "xiaohaizi.s1.key1"
              ],
              "rows_examined_per_scan": 1,  # 查询一次s2表大致需要扫描1条记录
              "rows_produced_per_join": 968,    # 被驱动表s2的扇出是968（由于后边没有多余的表进行连接，所以这个值也没啥用）
              "filtered": "100.00",     # condition filtering代表的百分比
              
              # s2表使用索引进行查询的搜索条件
              "index_condition": "(`xiaohaizi`.`s1`.`key1` = `xiaohaizi`.`s2`.`key2`)",
              "cost_info": {
                "read_cost": "968.80",      # 稍后解释
                "eval_cost": "193.76",      # 稍后解释
                "prefix_cost": "3197.16",   # 单次查询s1、多次查询s2表总共的成本
                "data_read_per_join": "1M"  # 读取的数据量
              },
              "used_columns": [     # 执行查询中涉及到的列
                "id",
                "key1",
                "key2",
                "key3",
                "key_part1",
                "key_part2",
                "key_part3",
                "common_field"
              ]
            }
          }
        ]
      }
    }
    1 row in set, 2 warnings (0.00 sec)

# 二、查询语句重写后的语句 #

在我们使用`EXPLAIN`语句查看了某个查询的执行计划后，紧接着还可以使用`SHOW WARNINGS`语句查看与这个查询的执行计划有关的一些扩展信息，其中`Message`字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
    +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
    |  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL              | 9954 |    90.00 | Using where |
    |  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s2.key1 |    1 |   100.00 | Using index |
    +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------------+
    2 rows in set, 1 warning (0.00 sec)
    
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Note
       Code: 1003
    Message: /* select#1 */ select `xiaohaizi`.`s1`.`key1` AS `key1`,`xiaohaizi`.`s2`.`key1` AS `key1` from `xiaohaizi`.`s1` join `xiaohaizi`.`s2` where ((`xiaohaizi`.`s1`.`key1` = `xiaohaizi`.`s2`.`key1`) and (`xiaohaizi`.`s2`.`common_field` is not null))
    1 row in set (0.00 sec)

比如我们上边的查询本来是一个左（外）连接查询，但是有一个`s2.common_field IS NOT NULL`的条件，着就会导致查询优化器把左（外）连接查询优化为内连接查询，从`SHOW WARNINGS`的`Message`字段也可以看出来，原本的`LEFT JOIN`已经变成了`JOIN`。

但是大家一定要注意，我们说`Message`字段展示的信息类似于查询优化器将我们的查询语句重写后的语句，并不是等价于，也就是说`Message`字段展示的信息并不是标准的查询语句，在很多情况下并不能直接拿来运行。

# 三、查看优化器生成执行计划的过程 #

对于`MySQL 5.6`以及之前的版本来说，查询优化器就像是一个黑盒子一样，你只能通过`EXPLAIN`语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。在`MySQL 5.6`以及之后的版本中，`MySQL`提出了一个`optimizer trace`的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程。

功能的开启与关闭由系统变量`optimizer_trace`决定（默认是关闭的）。当我们打开这个功能，我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到`information_schema`数据库下的`OPTIMIZER_TRACE`表中查看完整的优化过程。具体的流程：

# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
    SET optimizer_trace="enabled=on";
    
    # 2. 这里输入你自己的查询语句
    SELECT ...; 
    
    # 3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
    SELECT * FROM information_schema.OPTIMIZER_TRACE;
    
    # 4. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步
    ...
    
    # 5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭
    SET optimizer_trace="enabled=off";

举例说明一下：

SET optimizer_trace="enabled=on";
    
    SELECT * FROM s1 WHERE 
        key1 > 'z' AND 
        key2 < 1000000 AND 
        key3 IN ('a', 'b', 'c') AND 
        common_field = 'abc';
        
    SELECT * FROM information_schema.OPTIMIZER_TRACE\G

输出：

*************************** 1. row ***************************
    # 分析的查询语句是什么
    QUERY: SELECT * FROM s1 WHERE
        key1 > 'z' AND
        key2 < 1000000 AND
        key3 IN ('a', 'b', 'c') AND
        common_field = 'abc'
    
    # 优化的具体过程
    TRACE: {
      "steps": [
        {
          "join_preparation": {     # prepare阶段
            "select#": 1,
            "steps": [
              {
                "IN_uses_bisection": true
              },
              {
                "expanded_query": "/* select#1 */ select `s1`.`id` AS `id`,`s1`.`key1` AS `key1`,`s1`.`key2` AS `key2`,`s1`.`key3` AS `key3`,`s1`.`key_part1` AS `key_part1`,`s1`.`key_part2` AS `key_part2`,`s1`.`key_part3` AS `key_part3`,`s1`.`common_field` AS `common_field` from `s1` where ((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
              }
            ] /* steps */
          } /* join_preparation */
        },
        {
          "join_optimization": {    # optimize阶段
            "select#": 1,
            "steps": [
              {
                "condition_processing": {   # 处理搜索条件
                  "condition": "WHERE",
                  # 原始搜索条件
                  "original_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))",
                  "steps": [
                    {
                      # 等值传递转换
                      "transformation": "equality_propagation",
                      "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
                    },
                    {
                      # 常量传递转换    
                      "transformation": "constant_propagation",
                      "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
                    },
                    {
                      # 去除没用的条件
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
                    }
                  ] /* steps */
                } /* condition_processing */
              },
              {
                # 替换虚拟生成列
                "substitute_generated_columns": {
                } /* substitute_generated_columns */
              },
              {
                # 表的依赖信息
                "table_dependencies": [
                  {
                    "table": "`s1`",
                    "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": "`s1`",
                    "range_analysis": {
                      "table_scan": {   # 全表扫描的行数以及成本
                        "rows": 9688,
                        "cost": 2036.7
                      } /* table_scan */,
                      
                      # 分析可能使用的索引
                      "potential_range_indexes": [
                        {
                          "index": "PRIMARY",   # 主键不可用
                          "usable": false,
                          "cause": "not_applicable"
                        },
                        {
                          "index": "idx_key2",  # idx_key2可能被使用
                          "usable": true,
                          "key_parts": [
                            "key2"
                          ] /* key_parts */
                        },
                        {
                          "index": "idx_key1",  # idx_key1可能被使用
                          "usable": true,
                          "key_parts": [
                            "key1",
                            "id"
                          ] /* key_parts */
                        },
                        {
                          "index": "idx_key3",  # idx_key3可能被使用
                          "usable": true,
                          "key_parts": [
                            "key3",
                            "id"
                          ] /* key_parts */
                        },
                        {
                          "index": "idx_key_part",  # idx_keypart不可用
                          "usable": false,
                          "cause": "not_applicable"
                        }
                      ] /* 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": [
                          {
                            # 使用idx_key2的成本分析
                            "index": "idx_key2",
                            # 使用idx_key2的范围区间
                            "ranges": [
                              "NULL < key2 < 1000000"
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,   # 是否使用index dive
                            "rowid_ordered": false,     # 使用该索引获取的记录是否按照主键排序
                            "using_mrr": false,     # 是否使用mrr
                            "index_only": false,    # 是否是索引覆盖访问
                            "rows": 12,     # 使用该索引获取的记录条数
                            "cost": 15.41,  # 使用该索引的成本
                            "chosen": true  # 是否选择该索引
                          },
                          {
                            # 使用idx_key1的成本分析
                            "index": "idx_key1",
                            # 使用idx_key1的范围区间
                            "ranges": [
                              "z < key1"
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,   # 同上
                            "rowid_ordered": false,   # 同上
                            "using_mrr": false,   # 同上
                            "index_only": false,   # 同上
                            "rows": 266,   # 同上
                            "cost": 320.21,   # 同上
                            "chosen": false,   # 同上
                            "cause": "cost"   # 因为成本太大所以不选择该索引
                          },
                          {
                            # 使用idx_key3的成本分析
                            "index": "idx_key3",
                            # 使用idx_key3的范围区间
                            "ranges": [
                              "a <= key3 <= a",
                              "b <= key3 <= b",
                              "c <= key3 <= c"
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,   # 同上
                            "rowid_ordered": false,   # 同上
                            "using_mrr": false,   # 同上
                            "index_only": false,   # 同上
                            "rows": 21,   # 同上
                            "cost": 28.21,   # 同上
                            "chosen": false,   # 同上
                            "cause": "cost"   # 同上
                          }
                        ] /* range_scan_alternatives */,
                        
                        # 分析使用索引合并的成本
                        "analyzing_roworder_intersect": {
                          "usable": false,
                          "cause": "too_few_roworder_scans"
                        } /* analyzing_roworder_intersect */
                      } /* analyzing_range_alternatives */,
                      
                      # 对于上述单表查询s1最优的访问方法
                      "chosen_range_access_summary": {
                        "range_access_plan": {
                          "type": "range_scan",
                          "index": "idx_key2",
                          "rows": 12,
                          "ranges": [
                            "NULL < key2 < 1000000"
                          ] /* ranges */
                        } /* range_access_plan */,
                        "rows_for_plan": 12,
                        "cost_for_plan": 15.41,
                        "chosen": true
                      } /* chosen_range_access_summary */
                    } /* range_analysis */
                  }
                ] /* rows_estimation */
              },
              {
                
                # 分析各种可能的执行计划
                #（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取idx_key2就好）
                "considered_execution_plans": [
                  {
                    "plan_prefix": [
                    ] /* plan_prefix */,
                    "table": "`s1`",
                    "best_access_path": {
                      "considered_access_paths": [
                        {
                          "rows_to_scan": 12,
                          "access_type": "range",
                          "range_details": {
                            "used_index": "idx_key2"
                          } /* range_details */,
                          "resulting_rows": 12,
                          "cost": 17.81,
                          "chosen": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 12,
                    "cost_for_plan": 17.81,
                    "chosen": true
                  }
                ] /* considered_execution_plans */
              },
              {
                # 尝试给查询添加一些其他的查询条件
                "attaching_conditions_to_tables": {
                  "original_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))",
                  "attached_conditions_computation": [
                  ] /* attached_conditions_computation */,
                  "attached_conditions_summary": [
                    {
                      "table": "`s1`",
                      "attached": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
                    }
                  ] /* attached_conditions_summary */
                } /* attaching_conditions_to_tables */
              },
              {
                # 再稍稍的改进一下执行计划
                "refine_plan": [
                  {
                    "table": "`s1`",
                    "pushed_index_condition": "(`s1`.`key2` < 1000000)",
                    "table_condition_attached": "((`s1`.`key1` > 'z') and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
                  }
                ] /* refine_plan */
              }
            ] /* steps */
          } /* join_optimization */
        },
        {
          "join_execution": {    # execute阶段
            "select#": 1,
            "steps": [
            ] /* steps */
          } /* join_execution */
        }
      ] /* steps */
    }
    
    # 因优化过程文本太多而丢弃的文本字节大小，值为0时表示并没有丢弃
    MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
    
    # 权限字段
    INSUFFICIENT_PRIVILEGES: 0
    
    1 row in set (0.00 sec)

上面其实这只是优化器执行过程中的一小部分，`MySQL`可能会在之后的版本中添加更多的优化过程信息。不过杂乱之中其实还是蛮有规律的，优化过程大致分为了三个阶段：

*  `prepare`阶段
 *  `optimize`阶段
 *  `execute`阶段

我们所说的基于成本的优化主要集中在`optimize`阶段，对于单表查询来说，我们主要关注`optimize`阶段的`"rows_estimation"`这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们更多需要关注`"considered_execution_plans"`这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用`EXPLAIN`语句所展现出的那种方案。