失眠网 > MySQL系列-高级-性能分析工具-EXPLAIN

MySQL系列-高级-性能分析工具-EXPLAIN

时间：2024-01-17 16:05:01

1. EXPLAIN概述1.1 官网介绍1.2 EXPLAIN 基本语法2. 基于函数和存储过程插入数据2.1 创建表2.2 创建函数和过程2.2.1 创建函数2.3 创建存储过程插入数据1. 存储过程insert_table_s12. 存储过程insert_table_s23. EXPLAIN 各返回值作用3.1 table与id作用3.2 select_type1. simple查询2. PRIMARY，UNION和UNION RESULT3. SUBQUERY 和 DEPENDENT SUBQUERY4. DEPENDENT UNION5. DERIVED 和 MATERIALIZED3.3 partitions3.4 type1. system2. const3. eq_ref4. ref5. fulltext6. ref_or_null7. index_merge8. unique_subquery9. index_subquery10. range11. index12. ALL3.5. possible_keys和key3.6. key_len3.7. ref3.8. rows3.9. filtered3.10. Extra3.6 小结

本文参考了尚硅谷的MySQL课程，康师傅主讲，很优秀啊

1. EXPLAIN概述

1.1 官网介绍

/doc/refman/5.7/en/explain-output.html

/doc/refman/8.0/en/explain-output.html

Optimizing Queries with EXPLAIN
The EXPLAIN statement provides information about how MySQL executes statements:
EXPLAIN语句提供了MySQL如何执行语句的信息:

EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
EXPLAIN与SELECT、DELETE、INSERT、REPLACE和UPDATE语句一起工作。

When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about using EXPLAIN to obtain execution plan information, see Section 8.8.2, “EXPLAIN Output Format”.
当EXPLAIN与可解释语句一起使用时，MySQL将显示来自优化器的关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括关于表如何连接以及以何种顺序连接的信息。有关使用EXPLAIN获取执行计划信息的信息，请查看8.8.2, “EXPLAIN Output Format”.

When EXPLAIN is used with FOR CONNECTION connection_id rather than an explainable statement, it displays the execution plan for the statement executing in the named connection. See Section 8.8.4, “Obtaining Execution Plan Information for a Named Connection”.
当EXPLAIN与FOR CONNECTION connection_id而不是可解释语句一起使用时，它将显示在指定连接中执行的语句的执行计划。参见第8.8.4节“Obtaining Execution Plan Information for a Named Connection”。

For SELECT statements, EXPLAIN produces additional execution plan information that can be displayed using SHOW WARNINGS. See Section 8.8.3, “Extended EXPLAIN Output Format”.
对于SELECT语句，EXPLAIN生成可以使用SHOW WARNINGS显示的附加执行计划信息。参见8.8.3节“Extended EXPLAIN Output Format”。

EXPLAIN is useful for examining queries involving partitioned tables. See Section 24.3.5, “Obtaining Information About Partitions”.
EXPLAIN对于检查涉及分区表的查询非常有用。参见第24.3.5节“Obtaining Information About Partitions”。

The FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSON format displays the information in JSON format.
FORMAT选项可用于选择输出格式。TRADITIONAL以表格格式显示输出。如果没有FORMAT选项，这是默认值。JSON格式以JSON格式显示信息。

1.2 EXPLAIN 基本语法

如果我们想看看某个查询的执行计划，可以使用EXPLAIN， EXPLAIN或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options

或者

DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：

2. 基于函数和存储过程插入数据

2.1 创建表

创建表s1：

CREATE TABLE s1 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)) ENGINE=INNODB CHARSET=utf8;

创建S2表

CREATE TABLE s2 (id INT AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),INDEX idx_key1 (key1),UNIQUE INDEX idx_key2 (key2),INDEX idx_key3 (key3),INDEX idx_key_part(key_part1, key_part2, key_part3)) ENGINE=INNODB CHARSET=utf8;

2.2 创建函数和过程

2.2.1 创建函数

创建函数 rand_string2 用生产随机字符串

DELIMITER //CREATE DEFINER = `root` @`localhost` FUNCTION rand_string2 ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci #该函数会返回一个字符串BEGINDECLAREchars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';DECLAREreturn_str VARCHAR ( 255 ) DEFAULT '';DECLAREi INT DEFAULT 0;WHILEi < n DOSET return_str = CONCAT(return_str,SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));SET i = i + 1;END WHILE;RETURN return_str;END // DELIMITER;

创建函数，假如报错

设置参数 log_bin_trust_function_creators，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1;

2.3 创建存储过程插入数据

1. 存储过程insert_table_s1

创建存储过程insert_table_s1 用于向s1表中插入数据：

DELIMITER //CREATE DEFINER = `root` @`localhost` PROCEDURE `insert_table_s1` (IN min_num INT ( 10 ),IN max_num INT ( 10 )) BEGINDECLAREi INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s1VALUES(( min_num + i ),rand_string2 ( 6 ),( min_num + 30 * i + 5 ),rand_string2 ( 6 ),rand_string2 ( 10 ),rand_string2 ( 5 ),rand_string2 ( 10 ),rand_string2 ( 10 ));UNTIL i = max_num END REPEAT;COMMIT;END // DELIMITER;

调用存储过程，插入数据

CALL insert_table_s1(10001,10000)

2. 存储过程insert_table_s2

创建存储过程insert_table_s2 用于向s2表中插入数据：

DELIMITER //CREATE DEFINER = `root` @`localhost` PROCEDURE `insert_table_s2` (IN min_num INT ( 10 ),IN max_num INT ( 10 )) BEGINDECLAREi INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO s2VALUES(( min_num + i ),rand_string2 ( 6 ),( min_num + 30 * i + 5 ),rand_string2 ( 6 ),rand_string2 ( 10 ),rand_string2 ( 5 ),rand_string2 ( 10 ),rand_string2 ( 10 ));UNTIL i = max_num END REPEAT;COMMIT;END // DELIMITER;

插入数据

CALL insert_table_s2(10001,10000)

3. EXPLAIN 各返回值作用

为了便于对照，把EXPLAIN的输出，再罗列如下：

EXPLAIN 语句输出的各个列的作用如下：

在navicat或mysql命令行输入如下语句：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

输出为：

这里的输出与官网所描述是一致的。

3.1 table与id作用

输入：

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

输出为：

可以看出，这里的table分别对应s1和s2，也就是无论查询语句有多复杂，语句中包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

输出为：

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');

输出为：

输入：

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

输出为：

输入：

EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

输出为：

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

DESC SELECT * FROM user_partitions WHERE id<80;

输出：

输入：

DESC SELECT * FROM user_partitions WHERE id>200;

输出为：

partitions的值为p1或p0，表示当前查询执行的分区

3.4 type

EXPLAIN输出的type列描述如何连接表。在json格式的输出中，这些是access_type属性的值。下面的列表描述了连接类型，从最好的类型到最差的类型:

system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL。

1. system

The table has only one row (= system table). This is a special case of the const join type.
system表示表只有一行(=系统表)。这是const连接类型的一个特殊情况

创建引擎为MyISAM的表：

CREATE TABLE t(i int) Engine=MyISAM;

插入一行数据：

INSERT INTO t VALUES(1);

输入：

EXPLAIN SELECT * FROM t;

输出：

再插入一条数据，并查看执行计划

INSERT INTO t VALUES(2);EXPLAIN SELECT * FROM t;

输出为：

2. const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.
const is used when you compare all parts of a PRIMARY KEY or UNIQUE index to constant values. In the following queries, tbl_name can be used as a const table:
该表最多有一个匹配行，因为只有一匹配行，所以这一行中的列的值可以被优化器的其余部分视为常量。Const表非常快。
当将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时，使用const。

输入：

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

输出：

id为主键，只匹配一行。

3. eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
对于前面表中的每一行的组合，从这个表中读取一行。除了system和const类型，这是最好的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时，将使用它。

eq_ref can be used for indexed columns that are compared using the = operator.
eq_ref 可用于使用=运算符比较的索引列

输入：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

输出：

从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问方法是 eq_ref，表明在访问s1表的时候可以通过主键的等值匹配来进行访问。

4. ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.
对于前几个表的每个行组合，从这个表中读取具有匹配索引值的所有行。如果连接只使用键的最左边的前缀，或者键不是PRIMARY key或UNIQUE索引(换句话说，如果连接不能根据键值选择单行)，则使用ref。如果所使用的键只匹配几行，则这是一个很好的连接类型。

ref can be used for indexed columns that are compared using the = or <=> operator.
ref 可用于使用=或<=>操作符进行比较的索引列。

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

输出：

5. fulltext

The join is performed using a FULLTEXT index.
连接是使用FULLTEXT索引执行的。

6. ref_or_null

This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries.
这个连接类型类似于ref，但是MySQL对包含NULL值的行进行额外的搜索。这种连接类型优化最常用于解析子查询。
In the following examples, MySQL can use a ref_or_null join to process ref_table:

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

输出：

7. index_merge

This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.
此连接类型指示使用索引合并优化。在本例中，输出行中的键列包含所使用的索引的列表，key_len包含所使用索引的最长键部分的列表。

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

输出：

从执行计划的 type 列的值是 index_merge 就可以看出，MySQL 打算使用索引合并的方式来执行

对 s1 表的查询。

8. unique_subquery

unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.
unique_subquery 只是一个索引查找函数，它取代子查询以提高效率。

输入：

EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

输出：

9. index_subquery

This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:
这种连接类型类似于unique_subquery。它替换了IN子查询，但它适用于以下形式的子查询中的非唯一索引:

输入：

EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';

输出：

10. range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.
只检索给定范围内的行，使用索引来选择这些行。输出行中的键列表示使用的是哪个索引。key_len包含所使用的最长的键部分。对于这种类型，ref列是NULL。
range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators:
range可以在使用=，<>，>，>=，<，<=，is NULL， <=>， BETWEEN, LIKE，或IN()操作符将键列与常量进行比较时使用:

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

输出：

11. index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:
索引连接类型与ALL相同，不同之处在于扫描索引树。发生的方式有两种:
If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned.
如果索引是查询的覆盖索引，并且可以用于满足表中所需的所有数据，则只扫描索引树。
A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.
使用从索引读取来执行全表扫描，以按索引顺序查找数据行。使用索引没有出现在额外列中
MySQL can use this join type when the query uses only columns that are part of a single index.
当查询只使用作为单个索引一部分的列时，MySQL可以使用这种连接类型。

输入：

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

输出：

12. ALL

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.
对前面表中的每个行组合执行全表扫描。如果表是第一个未标记为const的表，这通常是不好的，而在所有其他情况下通常是非常糟糕的。通常，可以通过添加索引来避免ALL，这些索引支持基于常量值或早期表中的列值从表中进行行检索。

输入：

EXPLAIN SELECT * FROM s1;

输出：

结果值从最好到最坏依次是：system > const > eq_ref > ref> fulltext > ref_or_null > index_merge >unique_subquery > index_subquery >range > index > ALL其中比较重要的几个提取出来（见上图中的红色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴开发手册要求）

3.5. possible_keys和key

possible_keys
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

keys
实际用到的索引，如果为NULL，则没有使用索引.
查询中若使用了覆盖索引，则该索引和查询的select字段重叠

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

输出：

3.6. key_len

key_len表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。
表示mysql当决定使用某个索引的时候，使用索引的最大长度，这个长度是由三个部分组成：
1、对于使用固定长度类型的索引，则他实际占用的大小就是他的固定值，若是utf8，varchar(100)，则他实际占用长度是100*3 = 300。
2、如果有null，需要加个null值列表。长度为1
3、对于变长字段，都会有两个字节来存储实际长度。

计算key_len注意事项：
索引字段的附加信息: 可以分为变长和定长数据类型讨论，当索引字段为定长数据类型时,如char，int，datetime,需要有是否为空的标记,这个标记占用1个字节(对于not null的字段来说,则不需要这1字节);对于变长数据类型,比如varchar,除了是否为空的标记外,还需要有长度信息,需要占用两个字节。
对于,char、varchar、blob、text等字符集来说，key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

输出：

输入：

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

输出为：

输入

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

输出为：

输入

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

输出为：

输入

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

输出为：

key_len的长度计算公式：

varchar(10)变长字段且允许NULL = 10 * ( character set：
utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

3.7. ref

列与索引的比较

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

输出：

当索引在等值匹配查询的时候，const,ref，ref_or_null，unique sub_query，index sub_query。这里可以看到ref现实的是const，意味着key1做匹配的时候，是个常数。

输入：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

输出：

而这里可以看到前面的是数据库加s1的id，后面是一个函数。

输入：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

输出：

而这里可以看到是一个函数。

3.8. rows

估计要检查的行数

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

输出：

3.9. filtered

Percentage of rows filtered by table condition
按表条件过滤的行百分比

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

输出：

输入：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 mon_field = 'a';

输出：

3.10. Extra

附加信息

输入：

EXPLAIN SELECT 1;

输出：

Impossible WHERE输入：

EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

输出为：

Using where输入

EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

输出为：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

输出为：

No matching min/max row输入

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

输出为：

Using index输入

EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

输出为：

Using index condition输入

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%b';

输出为：

Using join buffer (Block Nested Loop)输入

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON mon_field = mon_field;

输出为：

Not exists输入

EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

输出为：

Using intersect(…) 、 Using union(…) 和 Using sort_union(…)输入

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

输出为：

Zero limit输入

EXPLAIN SELECT * FROM s1 LIMIT 0;

输出为：

Using filesort 输入

EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

输出为：

EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

输出为：

Using temporary 输入

输入：

EXPLAIN SELECT DISTINCT common_field FROM s1;

输出为：

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

输出为：

EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

输出为：

从 Extra 的 Using index 的提示里我们可以看出，上述查询只需要扫描 idx_key1 索引就可以搞

定了，不再需要临时表了。

3.6 小结

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

部分统计信息是估算的，并非精确值

如果觉得《MySQL系列-高级-性能分析工具-EXPLAIN》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。