MySQL的EXPLAIN

MySQLSE

发布于 2023-12-19 16:26:38

1560

发布于 2023-12-19 16:26:38

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)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-12-18，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql