Explain的四种格式与查看优化器重写SQL

关联博文：认真学习MySQL中的explain分析SQL

【1】explain四种输出格式

explain可以输出四种格式：传统格式、json格式、tree格式以及可视化输出。用户可以根据需要选择适用于自己的格式。

① 传统格式

传统格式即默认格式，输出是一个表格形式，概要说明查询计划。

explain select s1.key1,s2.key1 from s1 left join s2 on s1.key1=s2.key1 
where s2.common_field is not null;

② json格式

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

json格式：在explain单词和真正的查询语句中间加上 format=json

explain format=json select s1.key1,s2.key1 from s1 left join s2 on s1.key1=s2.key1 
where s2.common_field is not null;

我们可以将json拿出来，进行格式化：

{
    "query_block": {
        "select_id": 1,
        "cost_info": {
            "query_cost": "4130.67"
        },
        "nested_loop": [
            {
                "table": {
                    "table_name": "s2",
                    "access_type": "ALL",
                    "possible_keys": [
                        "idx_key1"
                    ],
                    "rows_examined_per_scan": 9895,
                    "rows_produced_per_join": 8905,
                    "filtered": "90.00",
                    "cost_info": {
                        "read_cost": "123.20",
                        "eval_cost": "890.55",
                        "prefix_cost": "1013.75",
                        "data_read_per_join": "15M"
                    },
                    "used_columns": [
                        "key1",
                        "common_field"
                    ],
                    "attached_condition": "((`testindex`.`s2`.`common_field` is not null) and (`testindex`.`s2`.`key1` is not null))"
                }
            },
            {
                "table": {
                    "table_name": "s1",
                    "access_type": "ref",
                    "possible_keys": [
                        "idx_key1"
                    ],
                    "key": "idx_key1",
                    "used_key_parts": [
                        "key1"
                    ],
                    "key_length": "303",
                    "ref": [
                        "testindex.s2.key1"
                    ],
                    "rows_examined_per_scan": 1,
                    "rows_produced_per_join": 8905,
                    "filtered": "100.00",
                    "using_index": true,
                    "cost_info": {
                        "read_cost": "2226.37",
                        "eval_cost": "890.55",
                        "prefix_cost": "4130.67",
                        "data_read_per_join": "15M"
                    },
                    "used_columns": [
                        "key1"
                    ]
                }
            }
        ]
    }
}

Explain的column与json的对应关系（来源于MySQL5.7文档）

关于成本信息的说明

如下是s2的成本信息：

"cost_info": {
                  "read_cost": "123.20",
                  "eval_cost": "890.55",
                  "prefix_cost": "1013.75",
                  "data_read_per_join": "15M"
              },

read_cost是由下边这两部分组成的：

• IO成本；
• 检测rows x (1 - filter)条记录的CPU成本

rows和filter都是我们前面学习执行计划的输出列，在json格式的执行计划中，rows相当于 rows_examined_per_scan，filtered名不变。

eval_cost是这样计算的：检测rows x filter条记录的成本。

prefix_cost就是单独查询s1表的成本，也就是：read_cost + eval_cost

data_read_per_join表示在此次查询中需要读取的数据量。

对于s1的 cost_info部分是这样的：

"cost_info": {
                  "read_cost": "2226.37",
                  "eval_cost": "890.55",
                  "prefix_cost": "4130.67",
                  "data_read_per_join": "15M"
              },

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

123.20 + 890.55 +2226.37 +890.55 = 4130.67

③ TREE格式

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

explain format=tree select s1.key1,s2.key1 from s1 
left join s2 on s1.key1=s2.key1  
where s2.common_field is not null;

-> Nested loop inner join  (cost=4130.67 rows=8905)
    -> Filter: ((s2.common_field is not null) and (s2.key1 is not null))  (cost=1013.75 rows=8905)
        -> Table scan on s2  (cost=1013.75 rows=9895)
    -> Index lookup on s1 using idx_key1 (key1=s2.key1)  (cost=0.25 rows=1)

④ 可视化输出

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

样例如下：

【2】show warnings的使用

在我们使用explain语句查看了某个查询的执行计划后，紧接着还可以使用show warnings语句查看与这个查询的执行计划有关的一些扩展信息。

explain select s1.key1,s2.key1 
from s1 
left join s2 
on s1.key1=s2.key1  
where s2.common_field is not null;

show WARNINGS;

我们看看show WARNINGS;的打印：

可以看到show WARNINGS;展示出来的信息有三个字段，分别是level、code、message。我们最常见的就是code为1003的信息，当code为1003时，message字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。

比如我们上边的查询本来是一个左外连接查询，但是有一个s2.common_field is not null的条件，这就会导致查询优化器把左外连接查询优化为内连接查询，从show warnings的message字段也可以看出来，原本的left join变成了join。

/* select#1 */ 
select `testindex`.`s1`.`key1` AS `key1`,`testindex`.`s2`.`key1` AS `key1` 
from `testindex`.`s1` 
join `testindex`.`s2` 
where ((`testindex`.`s1`.`key1` = `testindex`.`s2`.`key1`) 
and (`testindex`.`s2`.`common_field` is not null))

我们还可以看到将ON条件优化为了where查询条件。