526互联

MySQL学习(11)使用EXPLAN查看执行计划

发布时间 2023-11-06 17:19:36作者: 哪过晓得

前言

 

MySQL查询优化起生成的执行计划是什么，可以通过EXPLAIN命令查看。

MySQL执行计划组成

执行计划

在SELECT、DELETE、INSERT、REPLACE以及UPDATE语句前面加上EXPLAIN，可以通过记录的形式输出这条语句的执行计划。

EXPLAIN SELECT * FROM single_table;

 

image-20231017121313467

列名描述
id 每个SELECT关键字对应一个唯一的id
select_type SELECT关键字对应的查询类型
table 表名
paritions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际使用的索引
key_len 实际使用的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 针对预估的需要读取的记录条数，经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

 

id

查询语句每一个SELECT都有一个唯一的id值，一般在查询包含子查询或者UNION时，会出现多个SELECT，也会有多个id。

  • 只有一个SELECT：

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

 

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

  • 包含两个SELECT的子查询：

EXPLAIN SELECT * FROM single_table WHERE key1 IN (SELECT key1 FROM single_table2) OR key3 = 'a';

 

EXPLAIN SELECT * FROM single_table WHERE key1 IN (SELECT key1 FROM single_table2) OR key3 = 'a';

在查询的执行计划中，每个SELECT对应一个id，同一个SELECT下的id相同。查询优化器会对子查询进行重写，转换为连接查询（半连接）。可以根据执行计划查看查询优化器是否对查询进行优化重写。

EXPLAIN SELECT * FROM single_table WHERE key2 IN (SELECT key2 FROM single_table2 WHERE key1 = 'a');

 

EXPLAIN SELECT * FROM single_table WHERE key2 IN (SELECT key2 FROM single_table2 WHERE key1 = 'a');

对于使用UNION的查询语句：

EXPLAIN SELECT * FROM single_table UNION SELECT * FROM single_table2;

 

EXPLAIN SELECT * FROM single_table UNION SELECT * FROM single_table2;

这个执行计划说明了，这条语句在执行时，UNION子句为了把id为1的查询和id为2的查询的结果集合并并去重，在内部创建了一个名为<union1,2>的临时表，id为NULL，这个临时表是为了合并两个查询的结构集而创建的。

与UNION相比，UNION ALL不需要去重。

EXPLAIN SELECT * FROM single_table UNION ALL SELECT * FROM single_table2;

 

EXPLAIN SELECT * FROM single_table UNION ALL SELECT * FROM single_table2;

MySQL 5.6以及以前的版本，UNION ALL可能也会用到临时表。

select_type

执行计划中每条记录对应一个表，都有一个查询类型，就是select_type。

select_type取值如下：

名称描述
SIMPLE Simple SELECT (not using UNION or subqueries)
PRIMARY Outermost SELECT
UNION Second or later SELECT statement in a UNION
UNION RESULT Result of a UNION
SUBQUERY First SELECT in subquery
DEPENDENT SUBQUERY First SELECT in subquery, dependent on outer query
DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
DERIVED Derived table
MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

  • SIMPLE

查询语句中不包含UNION或者子查询的查询都是SIMPLE类型。

EXPLAIN SELECT * FROM single_table;

 

image-20231017121313467

连接查询满足这个条件也属于SIMPLE类型。

EXPLAIN SELECT * FROM single_table INNER JOIN single_table2;

 

EXPLAIN SELECT * FROM single_table INNER JOIN single_table2;

  • PRIMARY

查询语句包含UNION、UNION ALL或者子查询的查询是由几个小查询组成，其中最左边的小查询就是PRIMARY。

EXPLAIN SELECT * FROM single_table UNION SELECT * FROM single_table2;

 

EXPLAIN SELECT * FROM single_table UNION SELECT * FROM single_table2;

  • UNION

查询语句包含UNION、UNION ALL或者子查询的查询是由几个小查询组成，从左到右，第二个以及以后的小查询就是UNION类型，如上条语句执行结果第二条记录。

  • UNION RESULT

查询语句中包含UNION的查询，建立的用来合并和去重结果集的临时表就是UNION RESULT类，如上条语句执行结果第三条记录。

  • SUBQUERY

查询语句满足这些条件：1️⃣不能够转为对应的半连接形式；2️⃣子查询是不相关子查询3️⃣查询优化器决定采用将该子查询物化的方案来执行该子查询。这个子查询的第一个SELECT关键词代表的查询的select_type为SUBQUERY。

EXPLAIN SELECT * FROM single_table WHERE key1 IN (SELECT key1 FROM single_table2) OR key3 = 'a';

 

EXPLAIN SELECT * FROM single_table WHERE key1 IN (SELECT key1 FROM single_table2) OR key3 = 'a';

这个查询语句外层查询的select_type为PRIMARY，子查询的select_type为SUBQUERY。由于select_type为SUBQUERY的子查询会被物化，所以该子查询只需要执行一遍。

  • DEPENDENT SUBQUERY

查询语句满足这些条件：1️⃣包含子查询的查询语句不能转为半连接；2️⃣该子查询被转换为相关子查询。则该子查询的第一个SELECT关键字代表的查询的select_type就是DEPENDENT SUBQUERY。

EXPLAIN SELECT * FROM single_table AS s1 WHERE key1 IN (SELECT key1 FROM single_table2 AS s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

 

EXPLAIN SELECT * FROM single_table AS s1 WHERE key1 IN (SELECT key1 FROM single_table2 AS s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

select_type为DEPENDENT SUBQUERY的子查询可能会被执行多次。

  • DEPEMENT UNION

查询语句满足这些条件：1️⃣包含UNION或者UNION ALL；2️⃣每个小查询都依赖于外层查询。则子查询中第二个以及后面的小查询的select_type是DEPEMENT UNION。

EXPLAIN SELECT * FROM single_table WHERE key1 IN (SELECT key1 FROM single_table2 WHERE key1 = 'a' UNION SELECT key1 FROM single_table WHERE key1 = 'b');

 

image-20231105233110363

第二行记录为子查询第一个小查询，select_type为DEPENDENT SUBQUERY，第三行记录为子查询第二个小查询，select_type为DEPEMENT UNION。

  • DERIVED

查询语句满足这些条件：1️⃣查询包含派生表；2️⃣以物化派生表的方式执行查询。则派生表对应的子查询的select_type为DERIVED。

EXPLAIN SELECT * FROM (SELECT key1, count(*) AS c FROM single_table GROUP BY key1) AS derived_s1 WHERE c > 1;

 

image-20231105234522825

id 为 2 的记录就代表子查询的执行方式 ，它的select_type是DERlVED， 说明该子查询是以物化的方式执行的。id为1的记录代表外层查询，它的table列显示的是<derived2>，表示该查询是针对将派生表物化之后的表进行查询的。

  • MATERIALIZED

查询语句满足这些条件：1️⃣查询包含子查询；2️⃣子查询物化后与外层查询进行连接查询。则该子查询的select_type为MATERIALIZED。

EXPLAIN SELECT * FROM single_table WHERE key1 IN (SELECT key1 FROM single_table2);

 

image-20231105234545407

第三行记录的select_type是MATERIALIZED，查询优化器是把子查询先转换成物化表。执行计划的前两条记录的id值都为1，说明这两条记录对应的表进行的是连接查询。第二条记录的table列的值是<subquery2>，说明该表其实就是执行计划中id为2对应的子查询执行之后产生的物化表 ；然后再将single_table和该物化表进行连接查询。

  • UNCACHEABLE SUBQUERY

查询语句满足这些条件：1️⃣查询包含子查询；2️⃣其结果无法缓存，必须为外部查询的每一行重新计算。则子查询的select_type为UNCACHEABLE SUBQUERY。出现的的情况极少。

  • UNCACHEABLE UNION

查询语句满足这些条件：1️⃣查询包含UNION子查询；2️⃣其结果无法缓存。则子查询第二个及后续的select_type为UNCACHEABLE UNION。出现的的情况极少。

table

EXPLAIN语句输出的每条记录的table列代表单表访问的表名。

  • 单表查询：

EXPLAIN SELECT * FROM single_table;

 

EXPLAIN SELECT * FROM single_table;

  • 连接查询：

EXPLAIN SELECT * FROM single_table INNER JOIN single_table2;

 

EXPLAIN SELECT * FROM single_table INNER JOIN single_table2;

在连接查询的执行计划中，每个表都对应一条记录，这些记录的id是相同的；出现在前面的是驱动表，出现在后面的是被驱动表。

paritions

指分区信息。数据库优化有分库、分表、分区；这里的分区值表的分区信息，若无则为NULL。

type

EXPLAIN语句输出的每条记录都对应着某个单表的访问信息，每条记录的type列的值表示对表的访问方法。完整的访问方法有system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL等。

  • system

查询满足这些条件时：1️⃣查询的表使用的存储引擎的统计数据是精确的，比如MyISAM、MEMORY；2️⃣表中只有一条记录。则对该表的访问方法是system。

# 创建存储引擎为MyISAM的表t_myisam。
CREATE TABLE t_myisam (
  i int
) Engine=MyISAM;
# 向表t_myisam插入一条数据，此时表t_myisam中只有一条数据。
INSERT INTO t_myisam VALUES(1);
# 使用EXPLAIN语句查看执行计划
EXPLAIN SELECT * FROM t_myisam;

 

image-20231106110220426

若表仅有一条记录，但存储引擎为InnoDB，访问方法为ALL。

  • const

查询满足这些条件时：1️⃣查询条件为根据主键或唯一二级索引列与常数进行等值匹配。则对该表的访问方法是const。

EXPLAIN SELECT * FROM single_table WHERE id = 1;

 

image-20231106110346857

  • eq_ref

查询满足这些条件时：1️⃣查询包含连接查询；2️⃣驱动表是通过主键或者NOT NULL的唯一耳机所以你列等值匹配（如果是联合索引，需要所有的索引列进行等值比较）。则对被驱动表的访问方法是eq_ref。

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

 

image-20231106111306242

  • ref

查询满足这些条件时：1️⃣查询条件为根据普通二级索引列与常量进行等值匹配。则对该表的访问方法就是ref。另外在链接查询中，被驱动表中的某个普通的二级索引列与驱动表中的某个列进行等值匹配，被驱动表的访问方法也是ref。

EXPLAIN SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 ON s1.key1 = s2.key1;

 

image-20231106112918146

  • fulltext

查询使用到全文索引，访问方法就是fulltext。

CREATE TABLE fulltext_table (
    id INT AUTO_INCREMENT,
    content TEXT NOT NULL,
    tag VARCHAR(255),
    PRIMARY KEY (id),
    FULLTEXT INDEX content_tag_fulltext(content,tag)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

EXPLAIN SELECT * FROM fulltext_table WHERE MATCH(content,tag) AGAINST('xxx xxx');

 

image-20231106165744757

MySQL 5.6之前版本，只有MyISAM存储引擎支持全文索引，5.6之后，InnoDB存储引擎也支持全文索引。另外，只有字段的数据类型为 CHAR、VARCHAR、TEXT 及其系列才可以建全文索引。

  • ref_or_null

查询满足这些条件时：对普通二级索引进行等值匹配且索引列的值可以是NULl值，对该表的访问方法可能是ref_or_null。

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

 

image-20231106115907383

  • index_merge

查询满足这些条件时：1️⃣使用Intersection、Union或Sort-Union这三种索引合并之一时，对该表的访问方法是index_merge。

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

 

image-20231106120534860

  • unique_subquery

查询满足这些条件时：1️⃣查询包含IN子查询；2️⃣查询优化器将IN子查询转换为EXISTS子查询；3️⃣子查询在转换后可以使用主键或者NOT NULL唯一二级索引进行等值匹配。则子查询的访问方法是unique_subquery。

EXPLAIN SELECT * FROM single_table AS s1 WHERE common_field IN (SELECT id FROM single_table AS s2 WHERE s1.common_field = s2.common_field) OR key3 = 'a';

 

image-20231106121653687

  • index_subquery

查询满足这些条件时：1️⃣查询包含IN子查询；2️⃣查询优化器将IN子查询转换为EXISTS子查询；3️⃣子查询在转换后可以使用普通二级索引进行等值匹配。则子查询的访问方法是unique_subquery。

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

 

image-20231106122009112

  • range

查询满足这些条件时：根据索引获取某些单点扫描区间的记录或某些范围扫描区间的记录，则对该表的访问方法是range。

单点扫描区间：

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

 

image-20231106115219552

范围扫描区间：

EXPLAIN SELECT * FROM single_table WHERE key1 > 'd' AND key1 < 'f';

 

image-20231106115259827

  • index

查询满足这些条件：1️⃣查询列表满足索引覆盖；2️⃣需要扫描全部索引记录。则对该表的访问方法是index。

EXPLAIN SELECT key_part2, key_part3 FROM single_table WHERE key_part3 = 'a';

 

image-20231106114634708

对于InnoDB存储引起来说，在执行全标扫描并且需要对主键进行排序，此时的访问方法也是index。

EXPLAIN SELECT * FROM single_table ORDER BY id;

 

image-20231106114444143

  • ALL

查询条件不满足索引使用条件，执行全表扫描，则对该表的访问方法是ALL。

EXPLAIN SELECT * FROM single_table;

 

image-20231106113155976

index和ALL的区别在于，index是扫描所有的二级索引，不需要回表；ALL是扫描所有的聚簇索引。

possible_keys

表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些。

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

 

image-20231106122458561

不过有一点比较特别，就是在使用index访问方法查询某个表时，possible_keys列是空的，而key列展示的是实际使用到的索引。

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

 

image-20231106122722840

注意，possible_keys不是越多越好，查询优化器计算成本花费的时间会受影响。

key

表示在某个查询语句中，对某个表执行单表查询时实际用到的索引有哪些。

key_len

MySQL为边界条件中包含的列都维护了一个key_len值，它的计算方式：

  1. 该列的实际数据最多占用的存储空间长度。1️⃣对于固定长度的列来说，该列实际数据最多占用的存储空间长度就是固定的。2️⃣对于变长类型的列，该值为一个字符最多占用的字节数乘以该类型最多可 以存储的字符数的积。

  2. 如果该列可以存储NULL值，则key_len值在原先基础上再加1字节。

  3. 对于使用变长类型的列来说，会有2字节的空间来存储该变列的实际数据占用的存储空间长度。key_len值在原先基础上加2字节。

key_len存在的意义主要是为了让我们在使用联合索引执行查询时，能知道优化器具体使用了涉及多少个列的搜索条件来充当形成扫描区间的边界条件。

ref

ref表示的是与索引列进行等值匹配的是什么，可能是一个常数或者某个列。

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

 

image-20231106142748433

ref列的值为const，表示与索引列进行等值匹配的是常数。

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

 

image-20231106142937246

当与索引列进行等值匹配的是一个列，ref列的值会使用完整的列名，也就是数据库名、表名和列名。

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

 

image-20231106143342785

这种情况表示索引列进行等值匹配的对象是一个函数。

rows

表示预估扫描的记录条数。全表扫描则表示预估表记录数量，索引查询时表示预估扫描的索引记录数量。

EXPLAIN SELECT * FROM single_table WHERE key1 > 'x';

 

image-20231106143627159

filtered

表示条件过滤后的记录数量占扫描记录总数的百分比。

  • 如果使用全表扫描的方式执行单表查询，那么计算驱动表扇出时需要估计出满足全部搜索条件的记录到底有多少条。

  • 如果使用索引来执行单表扫描，那么计算驱动表扇出时需要估计出现在满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少。

Extra

  • No tables used

查询语句中没有FROM子句。

EXPLAIN SELECT 1;

 

image-20231106150159694

  • lmpossible WHERE

查询语句的WHERE子句永远为FALSE。

EXPLAIN SELECT * FROM single_table WHERE 1 != 1;

 

image-20231106150323453

  • No matching min/max row

查询列表有MIN或MAX聚集函数，但是没有记录符合WHERE子句中的搜索条件。

EXPLAIN SELECT MIN(key1) FROM single_table WHERE key1 = 'abc';

 

image-20231106150456742

  • Using index

查询列表覆盖索引时。

EXPLAIN SELECT key1 FROM single_table WHERE key1 = 'v';

 

image-20231106150731363

  • Using index condition

搜索条件出现了索引列，但是不能有效的缩小扫描区间。

EXPLAIN SELECT * FROM single_table WHERE key1 > 'v' AND key1 LIKE '%a';

 

image-20231106151419656

  • Using where

搜索条件需要在server层判断，在存储引擎，也就是索引中无法完成过滤。

EXPLAIN SELECT * FROM single_table WHERE key1 = 'a' AND common_field LIKE 'a%';

 

image-20231106152250833

  • Using join buffer(Block Nested Loop)

在执行连接查询中，当被驱动表不能有效地利用所以能加快访问速度时，MySQL会分配一块连接缓冲区（Join Buffer）的内存块来加快查询速度；也就是使用基于块的嵌套循环算法来执行连接查询。

EXPLAIN SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 ON s1.common_field = s2.common_field;

 

image-20231106152634859

  • Using intersect(...)、 Using union(...)和 Using sort_union(...)

使用索引合并的方式执行查询。

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

 

image-20231106120534860

  • Zero limit

当LIMIT子句的参数为0时，意思就是不打算读取任何记录。

EXPLAIN SELECT * FROM single_table LIMIT 0;

 

image-20231106152905661

  • Using filesort

查询中使用到文件排序的方式。

EXPLAIN SELECT * FROM single_table ORDER BY common_field;

 

image-20231106153256297

  • Using temporary

在执行包含DISTINCT、GROUP BY、UNION等子句时，查询中使用到了内部的临时表。

EXPLAIN SELECT DISTINCT common_field FROM single_table;

 

image-20231106155139824

执行计划中出现Using temporary不是好事，建立于维护临时表需要耗费很多性能成本，最好能使用索引代替临时表。

  • Start temporary, End temporary

子查询转换为半连接时，当执行策略为Duplicate Weedout，也就是通过建立临时表来为外层查询中的记录进行去重时，驱动表查询执行计划Extra显示Start temporary，被驱动表End temporary。

  • LooseScan

子查询转为半连接时，采用的是LooseScan执行策略，则驱动表执行计划的Extra就是LooseScan

可参考子查询转换为半连接的方式。

  • FirstMatch(tbl_name)

在将IN子查询转为半连接时，如果采用的是FirstMatch执行策略，则被驱动表执行计划的Extra列就显示FirstMatch(tbl_name)。

EXPLAIN SELECT * FROM single_table AS s1 WHERE common_field IN (SELECT key1 FROM single_table2 AS s2 WHERE s1.key3 = s2.key3);

 

image-20231106145448817

  

学习《MySQL是怎样运行的》小孩子4919