再深入Explain

---

MySQL学习系列

---

• EXPLAIN 输出成本

前面我们已经对 MySQL 查询优化器如何计算成本有了比较深刻的了解。 但是 EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。 不过 MySQL 已经为我们提供了一种查看某个执行计划花费的成本的方式：在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON。这样我们就可以得到一个 json 格式的执行计划， 里边包含该计划花费的成本， 比如这样：

explain format=json SELECT * FROM order_exp WHERE order_no IN ('DD00_6S',
'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND
expire_time<= '2021-03-22 18:35:09' AND insert_time> expire_time AND order_note
LIKE '%7 排 1%' AND order_status = 0\G

mysql> explain format=json SELECT * FROM order_exp WHERE order_no IN ('DD00_6S',
    -> 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND
    -> expire_time<= '2021-03-22 18:35:09' AND insert_time> expire_time AND order_note
    -> LIKE '%7  1%' AND order_status = 0\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,     # 整个查询语句只有 1 个 SELECT 关键字， 该关键字对应的 id 号为 1
    "cost_info": {
      "query_cost": "55.61"     # 整个查询的执行成本预计为 55.61
    },
    "table": {
      "table_name": "order_exp",
      "access_type": "range",
      "possible_keys": [
        "idx_order_no",
        "idx_expire_time"
      ],
      "key": "idx_expire_time",
      "used_key_parts": [
        "expire_time"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 39,
      "rows_produced_per_join": 0,
      "filtered": "0.13",
      "index_condition": "((`test`.`order_exp`.`expire_time` > '2021-03-22 18:28:28') 
      and (`test`.`order_exp`.`expire_time` <= '2021-03-22 18:35:09'))",
      "cost_info": {
        "read_cost": "55.60",
        "eval_cost": "0.01",
        "prefix_cost": "55.61",        # 单独查询表的成本， 也就是： read_cost + eval_cost
        "data_read_per_join": "24"     # 和连接查询相关的数据量， 单位字节， 这里无用
      },
      "used_columns": [
        "id",
        "order_no",
        "order_note",
        "insert_time",
        "expire_duration",
        "expire_time",
        "order_status"
      ],
      "attached_condition": "((`test`.`order_exp`.`order_status` = 0) and (`test`.`order_exp`.`order_no` in
       ('DD00_6S','DD00_9S','DD00_10S')) and (`test`.`order_exp`.`insert_time` > `test`.`order_exp`.`expire_time`) 
       and (`test`.`order_exp`.`order_note` like '%7  1%'))"
    }
  }
}
1 row in set, 1 warning (0.02 sec)

---

• Optimizer Trace

对于 MySQL5.6 之前的版本来说， 只能通过 EXPLAIN 语句查看到最后优化器决定使用的执行计划， 却无法知道它为什么做这个决策。 我们可能有这样的疑问：“我就觉得使用其他的执行方案比 EXPLAIN 输出的这种方案强， 凭什么优化器做的决定和我想的不一样呢？ 为什么 MySQL 一定要全文扫描， 不用索引呢？ ”

在 MySQL 5.6 以及之后的版本中， MySQL 提出了一个 optimizer trace 的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程， 这个功能的开启与关闭由系统变量 optimizer_trace 决定：

mysql> SHOW VARIABLES LIKE 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.01 sec)

可以看到 enabled 值为 off， 表明这个功能默认是关闭的。 one_line 的值是控制输出格式的， 如果为 on 那么所有输出都将在一行中展示， 我们就保持其默认值为off。
如果想打开这个功能， 必须首先把 enabled 的值改为 on， 就像这样：

SET optimizer_trace="enabled=on";

然后我们就可以输入我们想要查看优化过程的查询语句， 当该查询语句执行完成后， 就可以到 information_schema 数据库下的 OPTIMIZER_TRACE 表中查看完整的优化过程。 这个 OPTIMIZER_TRACE 表有 4 个列， 分别是：
QUERY： 表示我们的查询语句。
TRACE： 表示优化过程的 JSON 格式文本。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE： 由于优化过程可能会输出很多，如果超过某个限制时， 多余的文本将不会被显示， 这个字段展示了被忽略的文本字节数。
INSUFFICIENT_PRIVILEGES： 表示是否没有权限查看优化过程， 默认值是 0，只有某些特殊情况下才会是 1， 我们暂时不关心这个字段的值。当停止查看语句的优化过程时， 把 optimizer trace 功能关闭

SET optimizer_trace="enabled=off";

注意： 开启 trace 会影响 mysql 性能， 所以只能临时分析 sql 使用， 用完之后立即关闭 。

现在我们有一个搜索条件比较多的查询语句， 它的执行计划如下：

explain SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S',
'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time>
'2021-03-22 18:35:09' AND order_note LIKE '%7 排 1%';

mysql> explain SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time> '2021-03-22 18:35:09' AND order_note LIKE '%7  1%';
+----+-------------+-----------+------------+-------+-----------------------------------------------+--------------+---------+------+------+----------+------------------------------------+
| id | select_type | table     | partitions | type  | possible_keys                                 | key          | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-----------+------------+-------+-----------------------------------------------+--------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | order_exp | NULL       | range | u_idx_day_status,idx_order_no,idx_expire_time | idx_order_no | 152     | NULL |   58 |     2.78 | Using index condition; Using where |
+----+-------------+-----------+------------+-------+-----------------------------------------------+--------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到该查询可能使用到的索引有 3 个u_idx_day_status,idx_order_no,idx_expire_time， 那么为什么优化器最终选择了idx_order_no 而不选择其他的索引或者直接全表扫描呢？ 这时候就可以通过otpimzer trace 功能来查看优化器的具体工作过程：
#记得开启 optimizer trace 功能

SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S',
'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time>
'2021-03-22 18:35:09' AND order_note LIKE '%7 排 1%';

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
# 分析的查询语句是什么
QUERY: SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S',
'DD00_10S') AND expire_time> '2021-03-22 18:28:28' AND insert_time>
'2021-03-22 18:35:09' AND order_note LIKE '%7  1%'
# 优化的具体过程
TRACE: {
  "steps": [
    {
      "join_preparation": {    # prepare 阶段
        "select#": 1,
        "steps": [
          {
            "IN_uses_bisection": true
          },
          # 类似于查询优化器将查询语句重写后的语句
          {
            "expanded_query": "/* select#1 */ select `order_exp`.`id` AS `id`,`order_exp`.`order_no` AS
             `order_no`,`order_exp`.`order_note` AS `order_note`,`order_exp`.`insert_time` AS
              `insert_time`,`order_exp`.`expire_duration` AS `expire_duration`,`order_exp`.`expire_time` AS
               `expire_time`,`order_exp`.`order_status` AS `order_status` from `order_exp` where
                ((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`order_exp`.`expire_time` >
                 '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
                 and (`order_exp`.`order_note` like '%7  1%'))"
          }
        ]
      }
    },
    # optimize 阶段
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
             # 处理搜索条件
            "condition_processing": {
              "condition": "WHERE",
              # 原始搜索条件
              "original_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and
               (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
               and (`order_exp`.`order_note` like '%7  1%'))",
              "steps": [
                {
                # 等值传递转换
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) 
                  and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
                  and (`order_exp`.`order_note` like '%7  1%'))"
                },
                {
                 # 常量传递转换
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) 
                  and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
                  and (`order_exp`.`order_note` like '%7  1%'))"
                },
                {
                # 去除没用的条件
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) 
                  and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
                  and (`order_exp`.`order_note` like '%7  1%'))"
                }
              ]
            }
          },
          {
          	# 替换虚拟生成列
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
            # 表的依赖信息
              {
                "table": "`order_exp`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
            ]
          },
          {
          	# 预估不同单表访问方法的访问成本
            "rows_estimation": [
              {
                "table": "`order_exp`",
                "range_analysis": {
                  # 全表扫描的行数以及成本
                  "table_scan": {
                    "rows": 10668,
                    "cost": 2232.7
                  },
                  # 分析可能使用的索引
                  "potential_range_indexes": [
                    {
                      # 主键
                      "index": "PRIMARY",
                      "usable": false,
                      # 主键不可用
                      "cause": "not_applicable"
                    },
                    {
                      # u_idx_day_status 可能被使用	
                      "index": "u_idx_day_status",
                      "usable": true,
                      "key_parts": [
                        "insert_time",
                        "order_status",
                        "expire_time"
                      ]
                    },
                    {
                      # idx_order_no 可能被使用	
                      "index": "idx_order_no",
                      "usable": true,
                      "key_parts": [
                        "order_no",
                        "id"
                      ]
                    },
                    {
                      # idx_expire_time 可能被使用	
                      "index": "idx_expire_time",
                      "usable": true,
                      "key_parts": [
                        "expire_time",
                        "id"
                      ]
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                   # 分析各种可能使用的索引的成本
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                      	# 使用 u_idx_day_status 的成本分析
                        "index": "u_idx_day_status",
                        # 使用 u_idx_day_status 的范围区间
                        "ranges": [
                          "0x99a92d28c9 < insert_time"
                        ],
                        # 是否使用 index dive
                        "index_dives_for_eq_ranges": true,
                        # 使用该索引获取的记录是否按照主键排序
                        "rowid_ordered": false,
                        # 是否使用 mrr
                        "using_mrr": false,
                        # 是否是索引覆盖访问
                        "index_only": false,
                        # 使用该索引获取的记录条数
                        "rows": 5334,
                        # 使用该索引的成本
                        "cost": 6401.8,
                        # 是否选择该索引
                        "chosen": false,
                        # 因为成本太大所以不选择该索引
                        "cause": "cost"
                      },
                      {
                        "index": "idx_order_no",
                        "ranges": [
                          "DD00_10S <= order_no <= DD00_10S",
                          "DD00_6S <= order_no <= DD00_6S",
                          "DD00_9S <= order_no <= DD00_9S"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 58,
                        "cost": 72.61,
                        "chosen": true
                      },
                      {
                        "index": "idx_expire_time",
                        "ranges": [
                          "0x99a92d271c < expire_time"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 5334,
                        "cost": 6401.8,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    # 分析使用索引合并的成本
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  },
                  # 对于上述单表查询 s1 最优的访问方法
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_order_no",
                      "rows": 58,
                      "ranges": [
                        "DD00_10S <= order_no <= DD00_10S",
                        "DD00_6S <= order_no <= DD00_6S",
                        "DD00_9S <= order_no <= DD00_9S"
                      ]
                    },
                    "rows_for_plan": 58,
                    "cost_for_plan": 72.61,
                    "chosen": true
                  }
                }
              }
            ]
          },
          {
            # 分析各种可能的执行计划,负责对比各可行计划的开销， 并选择相对最优的执行计划。
			#（对多表查询这可能有很多种不同的方案， 单表查询的方案上边已经分析过了， 直接选取idx_order_no 即可）
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`order_exp`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 58,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_order_no"
                      },
                      "resulting_rows": 58,
                      "cost": 84.21,
                      "chosen": true
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 58,
                "cost_for_plan": 84.21,
                "chosen": true
              }
            ]
          },
          {
            # 尝试给查询添加一些其他的查询条件,增加主要是为了便于 ICP（索引条件下推）
            "attaching_conditions_to_tables": {
              "original_condition": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) 
              and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') 
              and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
              and (`order_exp`.`order_note` like '%7  1%'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`order_exp`",
                  "attached": "((`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) 
                  and (`order_exp`.`expire_time` > '2021-03-22 18:28:28') 
                  and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
                  and (`order_exp`.`order_note` like '%7  1%'))"
                }
              ]
            }
          },
          {
            # 再稍稍的改进一下执行计划
            "refine_plan": [
              {
                "table": "`order_exp`",
                "pushed_index_condition": "(`order_exp`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S'))",
                "table_condition_attached": "((`order_exp`.`expire_time` > '2021-03-22 18:28:28') 
                and (`order_exp`.`insert_time` > '2021-03-22 18:35:09') 
                and (`order_exp`.`order_note` like '%7  1%'))"
              }
            ]
          }
        ]
      }
    },
    {
       # execute 阶段
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
# 因优化过程文本太多而丢弃的文本字节大小， 值为 0 时表示并没有丢弃
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
# 权限字段
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)

优化过程大致分为了三个阶段：

• prepare 阶段
• optimize 阶段
• execute 阶段
  我们所说的基于成本的优化主要集中在 optimize 阶段， 对于单表查询来说，我们主要关注 optimize 阶段的"rows_estimation"这个过程， 这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说， 我们更多需要关注"considered_execution_plans"这个过程， 这个过程里会写明各种不同的连接方式所对应的成本。 反正优化器最终会选择成本最低的那种方案来作为最终的执行计划， 也就是我们使用 EXPLAIN 语句所展现出的那种方案。如果对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解， 就可以尝试使用 optimizer trace 功能来详细了解每一种执行方案对应的成本。

---