EXPLAIN四种输出格式&MySQL监控分析视图——sys schema

EXPLAIN四种输出格式

这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式：传统格式，JSON格式，TREE格式以及可视化输出。用户可以根据需要选择适用于自己的格式。

EXPLAIN四种输出格式

传统格式

传统格式简单明了，输出是一个表格形式，概要说明查询计划。

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE
s2.common_field IS NOT NULL;

JSON格式

第1种格式中介绍的EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性——成本。而JSON格式是四种格式里面输出信息最详尽的格式，里面包含了执行的成本信息。

• JSON格式：在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON 。

EXPLAIN FORMAT=JSON SELECT ....

• EXPLAIN的Column与JSON的对应关系:(来源于MySQL 5.7文档)

这样我们就可以得到一个json格式的执行计划，里面包含该计划花费的成本，比如这样：

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2  WHERE s1.common_field = 'a'\G

这种事JSON格式的

{
  "query_block": {
    "select_id": 1,
    # 花费的成本信息
    "cost_info": {
      "query_cost": "2102.20"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s1",
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 9895,
          "rows_produced_per_join": 989,
          "filtered": "10.00",
          "cost_info": {
            "read_cost": "914.80",
            "eval_cost": "98.95",
            "prefix_cost": "1013.75",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "((`atguigudb1`.`s1`.`common_field` = 'a') and (`atguigudb1`.`s1`.`key1` is not null))"
        }
      },
      {
        "table": {
          "table_name": "s2",
          "access_type": "eq_ref",
          "possible_keys": [
            "idx_key2"
          ],
          "key": "idx_key2",
          "used_key_parts": [
            "key2"
          ],
          "key_length": "5",
          "ref": [
            "atguigudb1.s1.key1"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 989,
          "filtered": "100.00",
          "index_condition": "(cast(`atguigudb1`.`s1`.`key1` as double) = cast(`atguigudb1`.`s2`.`key2` as double))",
          "cost_info": {
        	# IO成本+检测的rows * (1 - filter)条记录的CPU的成本
            "read_cost": "989.50",
            "eval_cost": "98.95",
            "prefix_cost": "2102.20",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}

• read_cost是由下边这两部分组成的：
  • IO成本
  • 检测rows × (1 - filter)条记录的 CPU 成本

小贴士： rows和filter都是我们前边介绍执行计划的输出列，在JSON格式的执行计划中，rows
相当于rows_examined_per_scan，filtered名称不变。

• eval_cost是这样计算的：

​ 检测rows * filter条记录的成本。

• prefix_cost就是单独查询s1表的成本，也就是:

​ read_cost + eval_cost

• data_read_per -join表示在此次查询中需要读取的数据量。

对于s2表的cost_info部分是这样是：

 "cost_info": {
            "read_cost": "914.80",
            "eval_cost": "98.95",
            "prefix_cost": "1013.75",
            "data_read_per_join": "1M"
          },

由于s2表是被驱动表，所以可能被读取多次，这里的read_cost和eval_cost是访问多次s2表后累
加起来的值，大家主要关注里边儿的prefix_cost的值代表的是整个连接查询预计的成本，也就是单
次查询s1表和多次查询s2表后的成本的和，也就是：

968.80 + 193.76 + 2034.60 = 3197.16

TREE格式

TREE格式是8.0.16版本之后引入的新格式，主要根据查询的各个部分之间的关系和各部分的执行顺序来描述如何查询。

mysql> EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2  WHERE s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=2102.20 rows=990)
    -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null))  (cost=1013.75 rows=990)
        -> Table scan on s1  (cost=1013.75 rows=9895)
    -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double))  (cost=1.00 rows=1)

1 row in set, 1 warning (0.00 sec)

可视化输出

可视化输出，可以通过MySQL Workbench可视化查看MysQL的执行计划。通过点击Workbench的放大镜图标，即可生成可视化的查询计划。