MySQL的EXPLAIN

MySQL的EXPALIN是优化查询语句必不可少的工具，用户通过它可以获得查询计划的相关信息，查看优化器的选择。

EXPLAIN生成的查询计划中显示优化器计划如何执行查询，包括“SELECT”、“INSERT”、“REPLACE”、“UPDATE”和“DELECE”语句。执行EXPLAIN不会修改执行语句涉及的数据，也不会返回执行语句的结果集。例如：

MySQL localhost:3350 ssl world SQL > EXPLAIN SELECT * FROM world.city where id=100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100
Extra: NULL
1 row in set, 1 warning (0.0036 sec)
Note (code 1003): /* select#1 */ select '100' AS `ID`,'Paraná' AS `Name`,'ARG' AS `CountryCode`,'Entre Rios' AS `District`,'207041' AS `Population` from `world`.`city` where true



• id：表示检查语句的编号

• select_type：查询中使用的“SELECT”类型

• SIMPLE：表示查询没有使用“UNION”或子查询

• 其他的值表示使用了“UNION”或子查询

• table：输出行的表名

• partitions：执行查询时，优化器需要查验的分区

• type：索引或JOIN的比较类型

• possible_keys：查询使用的相关索引

• key：优化器选择的索引

• key_len：用于搜索索引的最左边列的大小(以字节为单位)。

• ref：列或const与索引比较

• rows：查询将返回优化器预测的估计行数

• filtered：表的条件过滤的行的百分比，最大值是100，意味着没有对行进行过滤。从100开始递减的值表示过滤量在增加。rows显示检查的估计行数，Rows × filtered显示与下表连接的行数。例如，如果rows为1000，filtered为50.00(50%)，则要与下表连接的行数为1000 × 50% = 500。

• Extra：优化器或存储引擎提供的每次查询的附加信息。

type的值表示优化器在访问数据行时，比较的类型，包括如下：

• ALL：全表扫描

• index：使用索引扫描

• const：在查询开始时将主键或唯一键与常量匹配

• eq_ref：匹配单个引用值(由ref列标识)与其相等

• ref：匹配一个或多个引用值与其相等

• range：匹配索引（键）支持范围内的行

EXPLAIN输出的最后的信息是一个警告，当用户执行一个“SELECT”语句的查询计划时，会产生一个Note级别的事件，描述查询重写和优化操作。使用“SHOW WARNINGS”可以显示更详细的信息，每条消息都提供有关优化器的计划的扩展信息，并显示了重新改写的版本，用以表示优化后的查询。例如：

MySQL localhost:3350 ssl world SQL > SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: * select#1 */ select '100' AS `ID`,'Paraná' AS `Name`,'ARG' AS `CountryCode`,'Entre Rios' AS `District`,'207041' AS `Population` from `world`.`city` where true
1 row in set (0.0004 sec)



之前的例子中，查询使用了索引，让我们再看一个使用全表扫描的例子：

MySQL localhost:3350 ssl world SQL > EXPLAIN SELECT * FROM world.city where name='beijing'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4035
filtered: 10
Extra: Using where
1 row in set, 1 warning (0.0012 sec)
Note (code 1003): * select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` where (`world`.`city`.`Name` = 'beijing')



在这个查询中，执行计划的“possible_keys”、“key”，及“key_len”显示为“NULL”，表示该查询无法使用索引去改善性能，“type”显示为“ALL”，表示查询使用了全表扫描，"rows"显示为“4035”，表示InnoDB查验了表的行数。

接下来我们再看另外一个查询，该查询使用了key进行检索。首先我们确认一下city表的定义，可以看到“CountryCode”定义为key：

CREATE TABLE `city` (
`ID` int NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 



执行EXPLAIN，查看执行计划：

MySQL localhost:3350 ssl world SQL > EXPLAIN SELECT * FROM city where countrycode='CHN'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 12
ref: const
rows: 363
filtered: 100
Extra: Using index condition
1 row in set, 1 warning (0.0010 sec)
Note (code 1003): * select#1 */ select `world`.`city`.`ID` AS `ID`,`world`.`city`.`Name` AS `Name`,`world`.`city`.`CountryCode` AS `CountryCode`,`world`.`city`.`District` AS `District`,`world`.`city`.`Population` AS `Population` from `world`.`city` where (`world`.`city`.`CountryCode` = 'CHN')



“type”显示为“ref”，表示使用一个参照值（或常量）与列值进行比较，与参照值进行匹配的是一个非唯一列（CountryCode），优化器查验的行数量为363，在“Extra”中显示“Using index condition”表示通过访问索引元组访问表，以便首先确定是否读取完整的表行。

当用户执行一个复杂的查询时，EXPLAIN可以输出多行信息，每一行使用一个id进行唯一标识。如果“SELECT”语句中包含UNION或子查询中包含“SELECT”语句，EXPLAIN的输出将包含两行，每行有一个单独的id。如果在一个“SELECT”语句中JOIN两张表，EXPLAIN的输出将包含两行，两行将使用相同的id。

让我们再看一个JOIN例子：

CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint DEFAULT NULL,
`Population` int NOT NULL DEFAULT '0',
`LifeExpectancy` decimal(3,1) DEFAULT NULL,
`GNP` decimal(10,2) DEFAULT NULL,
`GNPOld` decimal(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

我们需要将“country”表与“city”表进行JOIN

MySQL localhost:3350 ssl world SQL > EXPLAIN SELECT country.name,city.name FROM country JOIN city ON country.code = city.countrycode where country.code like '%CHN%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: country
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 239
filtered: 11.11
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: city
partitions: NULL
type: ref
possible_keys: CountryCode
key: CountryCode
key_len: 12
ref: world.country.Code
rows: 17
filtered: 100
Extra: NULL
2 rows in set, 1 warning (0.0008 sec)
Note (code 1003): * select#1 */ select `world`.`country`.`Name` AS `name`,`world`.`city`.`Name` AS `name` from `world`.`country` join `world`.`city` where ((`world`.`city`.`CountryCode` = `world`.`country`.`Code`) and (`world`.`country`.`Code` like '%CHN%'))

第一个操作是全表扫描并没有使用索引，并且在“Extra”中提示使用到“where”，第二个操作是“ref”，表示对于来自前一个表“country”的每个行组合，将从这个表中读取具有匹配索引值的所有行。

EXPLAIN的输出格式有三种，分别为“TRADITIONAL”、“JSON”，和“TREE”，默认为“TRADITIONAL”。使用JSON格式和“TREE”输出时，会额外显示预估的执行成本和行数。

JSON格式

MySQL localhost:3350 ssl world SQL > EXPLAIN FORMAT=JSON SELECT country.name,city.name FROM country JOIN city ON country.code = city.countrycode where city.name like '%Beijing%'\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "566.65"
},
"nested_loop": [
{
"table": {
"table_name": "city",
"access_type": "ALL",
"possible_keys": [
"CountryCode"
],
"rows_examined_per_scan": 4035,
"rows_produced_per_join": 448,
"filtered": "11.11",
"cost_info": {
"read_cost": "364.92",
"eval_cost": "44.83",
"prefix_cost": "409.75",
"data_read_per_join": "108K"
},
"used_columns": [
"Name",
"CountryCode"
],
"attached_condition": "(`world`.`city`.`Name` like '%Beijing%')"
}
},
{
"table": {
"table_name": "country",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"Code"
],
"key_length": "12",
"ref": [
"world.city.CountryCode"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 448,
"filtered": "100.00",
"cost_info": {
"read_cost": "112.07",
"eval_cost": "44.83",
"prefix_cost": "566.65",
"data_read_per_join": "423K"
},
"used_columns": [
"Code",
"Name"
]
}
}
]
}
}
1 row in set, 1 warning (0.0039 sec)
Note (code 1003): /* select#1 */ select `world`.`country`.`Name` AS `name`,`world`.`city`.`Name` AS `name` from `world`.`country` join `world`.`city` where ((`world`.`country`.`Code` = `world`.`city`.`CountryCode`) and (`world`.`city`.`Name` like '%Beijing%'))



TREE格式：

MySQL localhost:3350 ssl world SQL > EXPLAIN FORMAT=TREE SELECT country.name,city.name FROM country JOIN city ON country.code = city.countrycode where city.name like '%Beijing%'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=567 rows=448)
-> Filter: (city.`Name` like '%Beijing%') (cost=410 rows=448)
-> Table scan on city (cost=410 rows=4035)
-> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=0.25 rows=1)


1 row in set (0.0030 sec)



EXPLAIN除了提供执行计划，在8.0.18之后的版本还提供了EXPLAIN ANALYZE功能。该功能除了能够输出查询计划，测量查询，并且能够执行该查询（但不返回查询结果），例如：

MySQL localhost:3350 ssl world SQL > EXPLAIN ANALYZE SELECT country.name,city.name FROM country JOIN city ON country.code = city.countrycode where city.name like '%Beijing%'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=567 rows=448) (actual time=0.942..0.942 rows=0 loops=1)
-> Filter: (city.`Name` like '%Beijing%') (cost=410 rows=448) (actual time=0.941..0.941 rows=0 loops=1)
-> Table scan on city (cost=410 rows=4035) (actual time=0.0296..0.555 rows=4079 loops=1)
-> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=0.25 rows=1) (never executed)


1 row in set (0.0020 sec)



MySQL在8.0.18版本之后推出了“Hash Join”功能，通过“EXPLAIN ANALYZE”或“TREE”格式可以进行查看：

MySQL localhost:3350 ssl world SQL > EXPLAIN ANALYZE SELECT * FROM country JOIN city\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (no condition) (cost=96474 rows=964365) (actual time=0.555..127 rows=974881 loops=1)
-> Table scan on city (cost=1.74 rows=4035) (actual time=0.0196..1.6 rows=4079 loops=1)
-> Hash
-> Table scan on country (cost=25.7 rows=239) (actual time=0.188..0.32 rows=239 loops=1)


1 row in set (0.4648 sec)

以上内容是关于EXPLAIN的简介，感谢关注“MySQL解决方案工程师”