MySQL的EXPLAIN

数据运维 2023-12-18 大树 手机阅读

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解决方案工程师”