MySQL执行计划解析

MySql 执行计划 Explain
MySQL
发布日期:   2021-05-25
文章字数:   3.3k
阅读时长:   13 分

MySQL执行计划解析

EXPLAIN 是常用于分析SQL语句并对SQL进行优化的利器，它提供具体执行语句的相关信息。

EXPLAIN 可以为 SELECT 语句中使用到的每张表输出一行信息 。它按照 MySQL 在处理 SELECT 语句时的读取顺序来列出各张表。

EXPLAIN 输出列如下：

Column Meaning
id SELECT 标识符
select_type SELECT 类型
table 引用的表名
partitions 匹配的分区
type 连接类型
possible_keys 可选的索引
key 实际选择的索引
key_len 实际所选 key 的长度
ref 与索引比较的列
rows 扫描行数
filtered 按表条件过滤的行百分比
Extra 附加信息

id

id 列的编号是 SELECT 的序列号，有几个 SELECT 就有几个 id，并且id的顺序是按 select 出现的顺序增长的。**id 值越大执行优先级越高，id 值相同则从上往下执行**，id 值为 NULL 则最后执行。

select_type

表示查询类型是简单查询还是复杂查询。常见 SELECT 类型如下：

select_type Value Meaning
SIMPLE 简单查询，没有使用union或者子查询
PRIMARY 对于包含union、union all或者子查询的一个大查询来说，它是由几个小查询构成的，而最左边的那个查询就是primary类型
UNION 对于包含union、union all的一个大查询来说，它是由几个小查询构成的，除了最左边的那个查询之外其余的就是union类型
UNION RESULT 临时表用于union的去重操作，如果对该临时表进行查询，则对应于UNION RESULT
SUBQUERY 如果包含子查询的查询语句不能够转为对应的semi-join[1]的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化[2]的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
MATERIALIZED 如果是子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED

注[1]：

semi-join子查询，是指当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。另外，右节点的表一条记录也不会返回。半连接通常使用IN 或 EXISTS 作为连接条件

注[2]：

将子查询结果集中的记录保存到临时表的过程称之为物化（Materialize）。存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

还有一些其他value值，详见官网

table

表示输出行所引用的表名，特殊情况如下：

  • <union*M*,*N*>：该行指的是 id 值为 M 和 N 的并集。
  • <derived*N*>：当 FROM 子句中有子查询时，表示当前查询依赖于 id=N 的查询结果，于是先执行 id=N 的查询。
  • <subquery*N*>：表示改行对应于id=N的物化子查询的临时表记录的结果

type

单表查询的性能对比：system > const > eq_ref > ref > range > index > ALL。一般来说，得保证查询达到 range 级别，最好达到 ref 级别。

system

该表只有一行。是 const 连接类型的特例。

const

该表最多只有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。const 表非常快，因为它们只能读取一次。

当主键索引（PRIMARY KEY ）或唯一索引（UNIQUE KEY）与常量值比较时使用 const 类型。如下：

SELECT * FROM tbl_name WHERE primary_key = 1;

SELECT * FROM tbl_name WHERE primary_key_part1 = 1 AND primary_key_part2 = 2;

SELECT * FROM tbl_name WHERE unique_key = '001';

SELECT * FROM tbl_name WHERE unique_key_part1 = '001' AND unique_key_part2 = '002';

SELECT * FROM ref_table,other_table
  WHERE ref_table.unique_key_column = other_table.unique_key_column
  AND other_table.unique_key_column = '001';

eq_ref

对于 other_table 中的每行，仅从 ref_table 中读取唯一一行。eq_ref 类型用于主键索引（PRIMARY KEY ）或 NOT NULL 的唯一索引（UNIQUE KEY），且索引被表连接所使用时。除了 system 和 const 类型之外，这是最好的连接类型。select_type=SIMPLE 简单查询类型不会出现这种类型。

例子：

SELECT * FROM ref_table,other_table
  WHERE ref_table.unique_key_column = other_table.column;
  
SELECT * FROM ref_table,other_table
  WHERE ref_table.unique_key_column_part1 = other_table.column
  AND ref_table.unique_key_column_part2 = 1;

ref

对于 other_table 中的每行，从 ref_table 中读取所有匹配行。ref 类型用于普通索引或联合索引的最左前缀列（leftmost prefix of the key），即无法根据键值查询到唯一一行。

例子：

SELECT * FROM ref_table WHERE key_column = expr;

SELECT * FROM ref_table WHERE key_column_part1 = expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column = other_table.column;
  
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1 = other_table.column
  AND ref_table.key_column_part2 = 1;

range

使用索引进行范围查询时，例如：=, <>, >, >=, <, <=, <=>, IS NULL, BETWEEN, LIKE, IN()。

SELECT * FROM tbl_name
  WHERE key_column = 10;

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

索引扫描，类似于 ALL 全表扫描。以下情况发生：

  • 覆盖索引（covering index）。此时 Extra 列显示 Using index。覆盖索引扫描通常比全表扫描速度更快，因为其存储空间更小。例子：

    SELECT primary_key FROM tbl_name;

SELECT unique_key FROM tbl_name;

SELECT COUNT(primary_key) FROM tbl_name;

SELECT COUNT(unique_key) FROM tbl_name;

ALL

全表扫描。此时必须增加索引优化查询。

全表扫描发生的情况如下：

  • 小表，此时全表扫描比二级索引扫描再回表的速度要快；
  • ON 或 WHERE 子句没有可用的索引；
  • 查询的字段虽然使用了索引，但查询条件覆盖的范围太大以至于还不如全表扫描。优化方式详见：Section 8.2.1.1, “WHERE Clause Optimization”
  • 使用了区分度（cardinality）低的索引，索引扫描范围太大以至于还不如全表扫描。如果是统计不准，可以用 ANALYZE TABLE 语句优化：Section 13.7.2.1, “ANALYZE TABLE Syntax”

possible_keys

表示 MySQL 可选的索引。

如果此列为 NULL，表示 MySQL 没有可选的索引。此时，可以检查 WHERE 子句是否引用了某些适合建立索引的列，建立索引以提升查询性能。

key

表示 MySQL 实际选择的索引。

  • 如果此列为 NULL，表示 MySQL 没有找到可用于提高查询性能的索引。
  • 如果 possible_keys NOT NULL，但 key NULL，可能是因为表中数据不多，MySQL 认为索引对此查询帮助不大，选择了全表扫描。

如需强制 MySQL 使用或忽略 possible_keys 中列出的索引，可以在查询中使用 FORCE INDEX、USE INDEX 或 IGNORE INDEX。

key_len

表示 MySQL 实际选择的索引长度，单位为 Byte。如果该索引为联合索引，可用于判断 MySQL 实际使用了联合索引中的多少个字段。如果 key 列为 NULL，key_len 列也为 NULL。

key_len 计算规则如下：

  • 使用 NULL 需要额外增加 1 Byte 记录是否为 NULL。并且进行比较和计算时要对 NULL 值做特别的处理，因此尽可能把所有列定义为 NOT NULL。

  • 各个类型：

    • 整数类型

      • TINYINT 1 Byte
      • SMALLINT 2 Bytes
      • MEDIUMINT 3 Bytes
      • INT 4 Bytes
      • BIGINT 8 Bytes

    • 日期与时间类型

      • DATE 3 Bytes
      • TIMESTAMP 4 Bytes
      • DATETIME 8 Bytes

    • 字符串类型，实际字节存储长度取决于使用的字符集

      字符集（Character encoding） M L
      latin1 1 Char 1 Byte
      gbk 1 Char 2 Bytes
      utf8 1 Char 3 Bytes
      utf8mb4 1 Char 4 Bytes

      • CHAR(M)：如果字符集为 utf8，则长度为 3 * M Bytes(取最长情况)

      • VARCHAR(M)：如果字符集为 utf8，则长度为 3 * M Bytes + 1 or 2 Bytes。额外 1 or 2 Byte(s) 用于存储长度。

  • 创建索引的时候可以指定索引的长度，例如：alter table test add index idx_username (username(30));。长度 30 指的是字符的个数。

  • InnoDB 索引==最大长度为 767 Bytes==，引自官方文档：

    key_part:
    col_name [(length)] [ASC | DESC]

    index_type:
    USING {BTREE | HASH}

    Prefixes, defined by the length attribute, can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.

举个例子，在字符集为 utf8 的情况下，n 最大只能为 (767 - 2 (存储长度)) / 3 = 765 / 3 = 255 个字符。因此当字符串过长时，MySQL 最多会将开头 255 个字符串截取出来作为索引。一个例子：

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `username` varchar(256) DEFAULT NULL,
  `password` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`(255)) USING BTREE,
  KEY `idx_password` (`password`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- key_len: 255 * 3 + 2 + 1 = 768 Bytes (额外增加 1 Byte 记录是否为 NULL)
mysql> explain select username from student where username = 'pete';
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | student | ref  | idx_username  | idx_username | 768     | const |    1 | Using where |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+-------------+

-- key_len: 1 * 3 + 2 + 1 = 6 Bytes
mysql> explain select password from student where password = 'pete';
+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | student | ref  | idx_password  | idx_password | 6       | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+--------------+---------+-------+------+--------------------------+

如果使用过长的索引，例如修改了字符串编码类型、增加联合索引列，则报错如下：

[Err] 1071 - Specified key was too long; max key length is 767 bytes

ref

ref 显示与 key 列（实际选择的索引）比较的内容，可选值：

  • 列名（例：t1.id）
  • const：常量值
  • func：值为某些函数的结果
  • NULL：范围查询（type=range）

简单来说，ref列展示的就是与索引列作等值匹配的值什么，比如只是一个常数或者是某个列。

rows

表示 MySQL 认为执行查询必须扫描的行数。

对于 InnoDB 表，此数字是估计值，可能并不总是准确。

当 prossible_keys 存在多个可选索引时，优化器会选择一个认为最优的执行方案，以最小的代价去执行语句。其中，这个扫描行数就是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的 IO 次数越少，消耗的 CPU 资源也越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

所以在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用执行 analyze table 重新统计信息。

在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

Extra

这一列显示的是额外信息。如果想要查询越快越好，需要特别留意 Extra 列是否出现以下情况：

Extra 缓冲区 大小配置 数据结构 备注
Using filesort sort_buffer sort_buffer_size 有序数组 使用了“外部排序”（全字段排序或 rowid 排序）
Using join buffer (Block Nested Loop) join_buffer join_buffer_size 无序数组 使用了“基于块的嵌套循环连接”算法（Block Nested-Loop Join（BNL））
Using temporary 临时表 小于 tmp_table_size 为内存临时表，否则为磁盘临时表（可以使用 SQL_BIG_RESULT 直接指定） 二维表结构（类似于 Map，Key-Value） 如果执行逻辑需要用到二维表特性，就会优先考虑使用临时表。例如：DISTINCT、GROUP BY、UNION

这三个数据结构都是用来存放 SELECT 语句执行过程中的中间数据，以辅助 SQL 语句的执行的。==这些情况通常都能通过索引优化==。

各种常见的重要值如下：

Using index

使用了覆盖索引。

Using where

使用 WHERE 条件过滤结果，但查询的列未被索引覆盖。

Using index condition

查询的列不完全被索引覆盖。

例如：索引下推优化（ICP）

查询 ICP 是否开启：SELECT @@GLOBAL.optimizer_switch，注意 index_condition_pushdown 标记：

Using temporary

MySQL 需要创建一张临时表来处理查询。通常发生于查询包含 DISTINCT、GROUP BY 或 ORDER BY 子句等需要数据去重的场景。出现这种情况一般是要进行优化的，首先想到的是用索引进行优化。

Using join buffer

使用 BNL 算法（分段处理,一块一块的加载到内存中）进行表连接。这种情况下一般考虑使用索引对被驱动表的表连接字段进行优化，以使用更高效的 NLJ（Index Nested-Loop Join）算法。

Using filesort

将用外部排序而不是索引排序，数据较少时从内存排序，否则需要在磁盘完成排序。这种情况下一般考虑使用索引进行优化。

参考

MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

最完整的Explain总结，SQL优化不再困难

文章作者: DestiNation
文章链接: https://li-zhenhua.github.io/2021/05/25/mysql-zhi-xing-ji-hua/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 DestiNation !
MySql 执行计划 Explain
  目录