explain都不懂？搞什么数据库优化，快进来学习了

文章目录

一、 前言

EXPLAIN 想必用过mysql的小伙伴都听过， EXPLAIN 可以说是MySQL的性能分析神器。
利用

MySQL EXPLAIN

工具 我们可以生成 MySQL的

执行计划

，我们可以了解到查询语句的访问路径、索引使用情况、关联方式等信息，从而确定瓶颈所在，进而使用适当的优化方法进行调整，提高查询性能和执行效率。
EXPLAIN 语法：

EXPLAINSELECT column_name(s)FROM table_name WHERE condition

其中，column_name(s)为要查询的字段名称；table_name为查询的表名；condition为查询条件。

结果会显示出查询表的访问方式、关联方式、扫描类型、使用的索引以及查询语句的执行次数等信息。

二、MySQL EXPLAIN实战

为了展示MySQL EXPLAIN的实际应用，下面我们通过一个简单的例子来演示如何使用EXPLAIN命令分析MySQL查询语句的执行计划和性能。

假设我们有一个测试表hotel，其字段包括hotel_id,hotel_name,address,price等。我们要查询hotel表中所有地址为“北京”的酒店名称和价格，并且按照价格从低到高排序。

MySQL查询语句如下：

SELECT hotel_name, price FROM hotel WHERE address ="北京"ORDERBY price ASC;

使用EXPLAIN命令分析如下：

EXPLAINSELECT hotel_name, price FROM hotel WHERE address ="北京"ORDERBY price ASC;

分析结果如下：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type |table| partitions |type| possible_keys |key| key_len | ref  |rows| filtered | Extra                                              |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+|1|SIMPLE| hotel |NULL| ref  | address       | address |90| const |16|100.00|Usingindex condition;Using filesort              |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+

解析查询结果：
序号字段名含义1id为查询语句执行的序号，如果查询语句存在子查询，会对应多个id2select_type该条查询的类型3table涉及到的表名4partitions涉及到的分区信息5type查询中使用的访问方法6possible_keys查询中可能使用到的索引7key实际使用的索引8key_len实际使用的索引长度9ref连接条件或者索引关联类型10rows返回的行数11filtered使用了WHERE条件时，过滤器选出行的百分比12Extra附加信息，包括使用的索引等信息
通过分析EXPLAIN结果，我们可以了解到该查询使用的是索引访问方式，使用的索引名称为address，采用了filesort算法进行排序，并且查询的行数为16，查询结果占用单表的100%。我们可以根据这些信息来进一步优化该查询语句，提高数据库的查询性能和执行效率。

三、mysql EXPLAIN输出结果详解

3.1 id详解

该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。

3.2 select_type

查询类型，有如下几种取值：

3.3 table

表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名

3.4 partitions

当前查询匹配记录的分区。对于未分区的表，返回null

3.5 type

连接类型，有如下几种取值，

性能由好到差进行排序

如下：

1. system：该表只有一行（相当于系统表），system是const类型的特例
2. const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
3. eq_ref：当使用了索引的全部组成部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型，性能仅次于system及const。

-- 多表关联查询，单行匹配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.columnAND ref_table.key_column_part2=1;

1. ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行，性能也是不错的。

-- 根据索引（非主键，非唯一索引），匹配到多行SELECT*FROM ref_table WHERE key_column=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.columnAND ref_table.key_column_part2=1;

1. fulltext：全文索引
2. ref_or_null：该类型类似于ref，但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询

SELECT*FROM ref_table
  WHERE key_column=expr OR key_column ISNULL;

1. index_merge：此类型表示使用了索引合并优化，表示一个查询里面用到了多个索引

8.unique_subquery：该类型和eq_ref类似，但是使用了IN查询，且子查询是主键或者唯一索引。例如：

valueIN(SELECT primary_key FROM single_table WHERE some_expr)

1. index_subquery：和unique_subquery类似，只是子查询使用的是非唯一索引

valueIN(SELECT key_column FROM single_table WHERE some_expr)

1. range：范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。

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

1. index：全索引扫描，和ALL类似，只不过index是全盘扫描了索引的数据。当查询仅使用索引中的一部分列时，可使用此类型。有两种场景会触发：

如果索引是查询的覆盖索引，并且索引查询的数据就可以满足查询中所需的所有数据，则只扫描索引树。此时，explain的Extra 列的结果是Using index。index通常比ALL快，因为索引的大小通常小于表数据。
按索引的顺序来查找数据行，执行了全表扫描。此时，explain的Extra列的结果不会出现Uses index。

1. ALL：全表扫描，性能最差。

3.6 possible_keys

展示当前查询可以使用哪些索引，这一列的数据是在优化过程的早期创建的，因此有些索引可能对于后续优化过程是没用的。

3.7key

表示MySQL实际选择的索引

3.8 key_len

索引使用的字节数。由于存储格式，当字段允许为NULL时，key_len比不允许为空时大1字节。

key_len计算公式： https://www.cnblogs.com/gomysql/p/4004244.html

3.9 ref

表示将哪个字段或常量和key列所使用的字段进行比较。

如果ref是一个函数，则使用的值是函数的结果。要想查看是哪个函数，可在EXPLAIN语句之后紧跟一个SHOW WARNING语句。

3.10 rows

MySQL估算会扫描的行数，数值越小越好。

3.11 filtered

表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。

TIPS

• 在MySQL 5.7之前，想要显示此字段需使用explain extended命令；
• MySQL.5.7及更高版本，explain默认就会展示filtered

3.12 Extra

展示有关本次查询的附加信息，取值如下：

• Child of ‘table’ pushed join@1

此值只会在NDB Cluster下出现。

• const row not found

例如查询语句SELECT … FROM tbl_name，而表是空的

• Deleting all rows

对于DELETE语句，某些引擎（例如MyISAM）支持以一种简单而快速的方式删除所有的数据，如果使用了这种优化，则显示此值

• Distinct

查找distinct值，当找到第一个匹配的行后，将停止为当前行组合搜索更多行

• FirstMatch(tbl_name)

当前使用了半连接FirstMatch策略，详见 https://mariadb.com/kb/en/firstmatch-strategy/ ，翻译 https://www.cnblogs.com/abclife/p/10895624.html

• Full scan on NULL key

子查询中的一种优化方式，在无法通过索引访问null值的时候使用

• Impossible HAVING

HAVING子句始终为false，不会命中任何行

• Impossible WHERE

WHERE子句始终为false，不会命中任何行

• Impossible WHERE noticed after reading const tables

MySQL已经读取了所有const（或system）表，并发现WHERE子句始终为false

• LooseScan(m…n)

当前使用了半连接LooseScan策略，详见 https://mariadb.com/kb/en/loosescan-strategy/ ，翻译 http://www.javacoder.cn/?p=39

• No matching min/max row

没有任何能满足例如 SELECT MIN(…) FROM … WHERE condition 中的condition的行

• no matching row in const table

对于关联查询，存在一个空表，或者没有行能够满足唯一索引条件

• No matching rows after partition pruning

对于DELETE或UPDATE语句，优化器在partition pruning（分区修剪）之后，找不到要delete或update的内容

• No tables used

当此查询没有FROM子句或拥有FROM DUAL子句时出现。例如：explain select 1

• Not exists

MySQL能对LEFT JOIN优化，在找到符合LEFT JOIN的行后，不会为上一行组合中检查此表中的更多行。例如：

SELECT*FROM t1 LEFTJOIN t2 ON t1.id=t2.id
  WHERE t2.id ISNULL;

假设t2.id定义成了NOT NULL ，此时，MySQL会扫描t1，并使用t1.id的值查找t2中的行。 如果MySQL在t2中找到一个匹配的行，它会知道t2.id永远不会为NULL，并且不会扫描t2中具有相同id值的其余行。也就是说，对于t1中的每一行，MySQL只需要在t2中只执行一次查找，而不考虑在t2中实际匹配的行数。

在MySQL 8.0.17及更高版本中，如果出现此提示，还可表示形如 NOT IN (subquery) 或 NOT EXISTS (subquery) 的WHERE条件已经在内部转换为反连接。这将删除子查询并将其表放入最顶层的查询计划中，从而改进查询的开销。通过合并半连接和反联接，优化器可以更加自由地对执行计划中的表重新排序，在某些情况下，可让查询提速。你可以通过在EXPLAIN语句后紧跟一个SHOW WARNING语句，并分析结果中的Message列，从而查看何时对该查询执行了反联接转换。

Note

两表关联只返回主表的数据，并且只返回主表与子表没关联上的数据，这种连接就叫反连接

• Plan isn’t ready yet

使用了EXPLAIN FOR CONNECTION，当优化器尚未完成为在指定连接中为执行的语句创建执行计划时， 就会出现此值。

• Range checked for each record (index map: N)

MySQL没有找到合适的索引去使用，但是去检查是否可以使用range或index_merge来检索行时，会出现此提示。index map N索引的编号从1开始，按照与表的SHOW INDEX所示相同的顺序。 索引映射值N是指示哪些索引是候选的位掩码值。 例如0x19（二进制11001）的值意味着将考虑索引1、4和5。

示例：下面例子中，name是varchar类型，但是条件给出整数型，涉及到隐式转换。
图中t2也没有用到索引，是因为查询之前我将t2中name字段排序规则改为utf8_bin导致的链接字段排序规则不匹配。

explainselect a.*from t1 a leftjoin t2 b
on t1.name = t2.name
where t2.name =2;

结果：

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1SIMPLE    t2    NULLALL    idx_name    NULLNULLNULL911.11Usingwhere1SIMPLE    t1    NULLALL    idx_name    NULLNULLNULL511.11    Range checked for each record (index map: 0x8)

• Recursive

出现了递归查询。详见 “WITH (Common Table Expressions)”

• Rematerialize

用得很少，使用类似如下SQL时，会展示Rematerialize

SELECT...FROM
  t,
  LATERAL (derived table that refers to t)AS dt

• Scanned N databases

表示在处理INFORMATION_SCHEMA表的查询时，扫描了几个目录，N的取值可以是0，1或者all。详见 “Optimizing INFORMATION_SCHEMA Queries”

• Select tables optimized away

优化器确定：①最多返回1行；②要产生该行的数据，要读取一组确定的行，时会出现此提示。一般在用某些聚合函数访问存在索引的某个字段时，优化器会通过索引直接一次定位到所需要的数据行完成整个查询时展示，例如下面这条SQL。

explainselectmin(id)from t1;

• Skip_open_table， Open_frm_only， Open_full_table

这些值表示适用于INFORMATION_SCHEMA表查询的文件打开优化；

Skip_open_table：无需打开表文件，信息已经通过扫描数据字典获得
Open_frm_only：仅需要读取数据字典以获取表信息
Open_full_table：未优化的信息查找。表信息必须从数据字典以及表文件中读取

• Start temporary, End temporary

表示临时表使用Duplicate Weedout策略，详见 https://mariadb.com/kb/en/duplicateweedout-strategy/ ，翻译 https://www.cnblogs.com/abclife/p/10895531.html

• unique row not found

对于形如 SELECT … FROM tbl_name 的查询，但没有行能够满足唯一索引或主键查询的条件

• Using filesort

当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。数据较少时从内存排序，否则从磁盘排序。Explain不会显示的告诉客户端用哪种排序。官方解释：“MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行”

• Using index

仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。例如：

explainSELECT id FROM t

• Using index condition

表示先按条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行。通过这种方式，除非有必要，否则索引信息将可以延迟“下推”读取整个行的数据。详见 “Index Condition Pushdown Optimization” 。例如：

TIPS

MySQL分成了Server层和引擎层，下推指的是将请求交给引擎层处理。

理解这个功能，可创建所以INDEX (zipcode, lastname, firstname)，并分别用如下指令，

SET optimizer_switch ='index_condition_pushdown=off';SET optimizer_switch ='index_condition_pushdown=on';

开或者关闭索引条件下推，并对比：

explainSELECT*FROM people
  WHERE zipcode='95054'AND lastname LIKE'%etrunia%'AND address LIKE'%Main Street%';

的执行结果。

index condition pushdown从MySQL 5.6开始支持，是MySQL针对特定场景的优化机制，感兴趣的可以看下 https://blog.51cto.com/lee90/2060449

• Using index for group-by

数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。详见 “GROUP BY Optimization”

-- name字段有索引explainSELECT name FROM t1 groupby name
Usingindexfor skip scan

表示使用了Skip Scan。详见 Skip Scan Range Access Method

• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

使用Block Nested Loop或Batched Key Access算法提高join的性能。详见 https://www.cnblogs.com/chenpingzhao/p/6720531.html

• Using MRR

使用了Multi-Range Read优化策略。详见 “Multi-Range Read Optimization”

• Using sort_union(…), Using union(…), Using intersect(…)

这些指示索引扫描如何合并为index_merge连接类型。详见 “Index Merge Optimization” 。

• Using temporary

为了解决该查询，MySQL需要创建一个临时表来保存结果。如果查询包含不同列的GROUP BY和 ORDER BY子句，通常会发生这种情况。

-- name无索引explainSELECT name FROM t1 groupby name

• Using where

如果我们不是读取表的所有数据，或者不是仅仅通过索引就可以获取所有需要的数据，则会出现using where信息

explainSELECT*FROM t1 where id >5

• Using where with pushed condition

仅用于NDB

• Zero limit

该查询有一个limit 0子句，不能选择任何行

explainSELECT name FROM resource_template limit0