MySQL应该是我们平时用得最多的一个关系型数据库了吧,毕竟开源免费而且功能强大。但是如果想知道一条SQL语句具体是怎么执行的,总不能每次都看源码吧,而官方就提供了一个工具——EXPLAIN语句,可以查看一条SQL的具体执行计划。
其实EXPLAIN我平时用得也不少,但EXPLAIN的输出信息实在比较丰富,所以每次都是根据输出结果然后上网定向查,过后就又忘了。这一次终于下定决心要把EXPLAIN的所有情况做一个总结。
本文介绍的MySQL版本
5.7
EXPLAIN的使用场景与功能
- EXPLAIN后面如果跟的是SQL语句,则只适用于DML,官方文档直接说明:SELECT, DELETE, INSERT, REPLACE和 UPDATE 语句。也就是说你想用EXPLAIN去查看MySQL是怎么CREATE或者DROP一张表的,这是不行滴。
- EXPLAIN可以用来输出一条SQL的具体执行计划,包括多个表之间的连接顺序,如何连接以及索引的使用等等。
- EXPLAIN也可以用于显示某个连接中执行语句的执行计划,格式类似于EXPLAIN FOR CONNECTION connection_id。
- 针对SELECT语句,EXPLAIN可以输出更多的执行计划信息,方法是在EXPLAIN后执行SHOW WARNINGS。
- EXPLAIN可以输出查询语句中有关分区表的信息,分区简单而言,就是通过一定的规则将单表做切分,然后可以以不同的形式存储于不同的物理区域,因为跟本文关联不是很大,所以不做重点介绍。
- EXPLAIN后面紧跟表名的话跟DESCRIBE的作用相同。
根据我自己的使用经验,我们平时使用EXPLAIN最多的用途就是查看某条SQL有没有用到索引、用了哪个索引、是否有文件排序、表的关联顺序是否和预期一致等等和SQL执行速度相关的情况,主要就是用来解决慢SQL。
EXPLIAN的输出信息
由于情况类似,这里以SELECT语句为例,EXPLAIN会根据SELECT语句中涉及到的每个表(包括临时表)都输出一行信息。每一行都包含很多列,下面重点介绍每一列的含义,毕竟这最常用。
输出列介绍
下面的表格列出了所有EXPLAIN语句输出的列,后面会详细说明每一列的情况。
列名 | 含义 |
---|---|
id | SELECT标识符 |
select_type | SELECT类型 |
table | 本行信息对应的表名称 |
partitions | 语句匹配到的分区 |
type | 连接类型 |
possible_keys | 有可能用到的索引 |
key | 实际选择的索引 |
key_len | 被选择的索引长度 |
ref | 索引比较列 |
rows | 扫描行数的估计值 |
filtered | 按表条件过滤的行数所占总数的百分比 |
Extra | 扩展信息 |
id
这是MySQL执行查表操作的标识符,除了引用其他表并集结果的情况下会为null,其实一般都是有值的,而且一般是大于0的整数。id的作用就是表明了每个表之间的查询顺序,两条规则:
- id越大,越先被执行
- id相同,靠前的先执行
select_type
下面的表格列出了select_type的所有可能值。
可取值 | 含义 |
---|---|
SIMPLE | 简单SELECT(不使用 UNION或子查询) |
PRIMARY | 最外层的SELECT |
UNION | UNION语句中第二个或者再之后的SELECT语句 |
DEPENDENT UNION | 跟UNION类似,区别在于当前语句依赖于外部查询 |
UNION RESULT | UNION语句的结果 |
SUBQUERY | 子查询中第一个SELECT语句 |
DEPENDENT SUBQUERY | 跟SUBQUERY类似,区别在于当前查询依赖于外部查询 |
DERIVED | 派生表 |
MATERIALIZED | 物化子查询 |
UNCACHEABLE SUBQUERY | 子查询,其结果无法缓存,必须针对外部查询的每一行重新进行评估 |
UNCACHEABLE UNION | UNION语句中第二个或者再之后的SELECT语句,结果无法缓存(参考UNCACHEABKE SUBQUERY) |
这里DEPENDENT SUBQUERY和UNCACHEABLE SUBQUERY是有区别的,DEPENDENT SUBQUERY针对外部查询中的每个值重新评估(重复值评估一次),而UNCACHEABLE SUBQUERY会针对外部的每个值都评估一次(重复值重复评估)。
- SIMPLE
- PRIMARY & UNION & UNION RESULT
- DEPENDENT UNION & UNCACHEABLE SUBQUERY
- SUBQUERY
- DEPENDENT SUBQUERY
- DERIVED
- MATERIALIZED
这是一种对子查询的优化,不必针对每个外层循环执行子查询
- UNCACHEABLE UNION
table
输出当前行所引用的表的名称,也有以下其他的情况:
- <unionm,n style="box-sizing: border-box;">:即id为M和N的查询结果的并集</unionm,n>
- <derivedn style="box-sizing: border-box;">:id为N的查询结果的派生表,比如一个FROM子句中的子查询</derivedn>
- <subqueryn style="box-sizing: border-box;">:id为N的物化子查询结果,可以认为是一个内存临时表,用于加快查询速度的</subqueryn>
partitions
一般为null,毕竟MySQL的分区功能比较少用,如果有值,代表当前查询匹配到的分区。
type
连接类型,用于表示多个表之间是怎么连接的,可以取的值需要详细介绍,不同取值差异巨大。
- system
该表只有一行(即系统表)。这是const联接类型的特例 ,一般在业务开发中不太常见。
- const
该表最多有一个匹配行,本次查询开始的时候就已经获取到这个唯一值了,所以后续的所有查询都将本次查询到的内容视为常量,常见的就是主键或者唯一索引的等值比较。
// emp_no是主键
explain select * from employees where emp_no = 10021;
- eq_ref
之前查询到的结果中每一行在当前查询中最多匹配一行,是除了system和 const类型之外最好的联接类型。常见的就是:主键或者唯一索引 = {之前查询的某个列值}
// emp_no是employees表的主键
explain select * from employees, dept_manager where employees.emp_no = dept_manager.emp_no;
- ref
与eq_ref类似,但是之前查询到的结果中每一行在当前查询中可能匹配到多行(索引匹配)。常见的就是:非唯一索引 = {之前查询的某个列值}
// dept_no是dept_manager表的普通索引explain select * from departments, dept_manager where departments.dept_no = dept_manager.dept_no;
- fulltext
使用fulltext类型的索引进行连接,这里就不做具体展开了,可以认为和ref类似,但是索引类型是fulltext。
- ref_or_null
就是ref加上一个null值查询,需要索引所在列允许值为null。
- index_merge
使用了索引合并的优化,其实就是MySQL用到了多个索引,最常见的就是两个索引=值的or连接查询
// 这里dept_no和emp_no都有各自的索引explain select * from dept_manager where dept_no = "d009" or emp_no = 110183;
- unique_subquery
官方介绍说是唯一索引的查找条件中用到了子查询,替代某些eq_ref的IN查询。但是我按照官方例子试了还是eq_ref。
- index_subquery
这个就是普通索引的查找条件用了子查询,替代某些ref的IN查询。同样,我也试不出来
- range
索引在某个范围内的查询。
- index
与ALL其实差不多,只是MySQL扫描了索引树,有两种情况
- 覆盖索引,通过扫描索引就拿到了数据,不用回表,Extra会显示Using index。
- 通过扫描索引来扫描全表,Extra不会显示Using index。
- ALL
全表扫描,最烂的一种情况,要避免。
possible_keys
可能用到的索引,注意仅仅是可能,如果这一列没有值,那你要紧张一下了。。
key
实际使用到的索引。通常情况下是possible_keys中的某一个,但是也有特殊情况,比如MySQL根据where条件列出了所有可能用到的索引,但是实际查询的列被另一个索引完全覆盖(覆盖索引),这个时候key就有可能是这个覆盖索引,因为虽然这个索引并不能用来过滤,但根据这个索引全部扫描一次更快(不用回表)。
某些情况下先ANALYZE TABLE table_name会影响索引的选择,因为统计信息也是MySQL选择索引的重要参考条件。
key_len
使用到的索引的长度,有时候不用使用全部索引长度就可以过滤完成,由于索引存储格式的原因,允许为null的那些列对应的索引会长一个单位(相比那些not null的列)。
ref
显示哪些列或者常量被用于索引的比较。如果值是func,那么代表索引的比较条件是一个函数结果,可以通过SHOW WARNINGS查看更具体的结果。
rows
查询需要扫描的行数,对于InnoDB来说,是个估计值。
filtered
表示按表条件过滤的表行的估计百分比,最大值为100,这表示未过滤任何行。所以该值越小表明当前过滤条件越有效。
Extra(不知道为啥就这个字段是大写开头)
包含一些执行计划的扩展信息,包含以下这些可选值,常见的有详细分析。
- const row not found
查询空表的情况,但实际我没试出来
- Deleting all rows
MyISAM引擎快速删除所有表格记录时会显示这个值
- Distinct
使用了distinct
- FirstMatch(
tbl_name
)
当前面表的查询策略使用了半联接FirstMatch联接快捷方式策略
- Full scan on NULL key
子查询优化无法利用到索引时的一种备选方案
- Impossible HAVING
HAVING子句中的条件永远不满足,结果一定是空
- Impossible WHERE
WHERE子句中的条件永远不满足,结果一定是空
- Impossible WHERE noticed after reading const tables
MySQL已经读取了所有 const(和 system)表,并判断该WHERE子句始终为false。
- LooseScan(
m
..n
)
使用了半连接的LooseScan策略。m 和 n是索引部分的编号。
- No matching min/max row
使用MIN和MAX函数的时候发现没有满足条件的记录
- no matching row in const table
对于具有联接的查询,存在一个空表或没有满足唯一索引条件的行的表。
- No matching rows after partition pruning
对于DELETE或 UPDATE,在分区修剪后,优化器未发现任何要删除或更新的内容。类似于Impossible WHERE for SELECT语句。
- No tables used
没有FROM子句
- Not exists
对LEFT JOIN 的一种优化,在找到符合LEFT JOIN条件的一行后,不再检查更多行是否满足条件。
这个例子中dept_manager.emp_no定义为not null
- Plan isn't ready yet
使用EXPLAIN FOR CONNECTION才会出现的值,当优化器未完成为在指定连接中执行的语句创建执行计划时,就会出现此值。
- Range checked for each record (index map:
N
)
MySQL找不到很好的索引来使用,但是发现在知道先前表中的列值之后可能会使用某些索引。
- Scanned
N
databases
查询INFORMATION_SCHEMA中的表时,显示具体查询了多少目录,可取0、1或者all。
- Select tables optimized away
要查询的信息是现成的,不要遍历索引或者表,比较有代表性的例子就是查询引擎是MyISAM的某个表的总记录数,因为MyISAM引擎是记录这个数据的,所以直接获取即可。
-
Skip_open_table
,Open_frm_only
,Open_full_table
查询INFORMATION_SCHEMA才会出现的值
Skip_open_table:不需要打开表文件,通过扫描数据库目录就可以了。
Open_frm_only:只需要打开数据库表的.frm文件。
Open_full_table:未优化,.frm、.MYD和 .MYI文件都必须被打开。
Start temporary
,End temporary
这表明临时表用于半联接重复淘汰策略,不常见。
- unique row not found
对于诸如SELECT … FROM tbl_name的查询,没有行满足表上的UNIQUE索引或PRIMARY KEY的条件。
- Using filesort
需要额外进行一次排序及查找,应该尽量避免。实际上MySQL会按照WHERE条件找出所有排序键和行记录指针,然后按照排序键进行一次排序,然后再根据指针查出所有记录。
这是一种比较常见的情况,order by后面的列没有建立索引,而where条件后的列建立了索引,优化器最终选择了按照emp_no查询记录,此时的逻辑就是:
按照emp_no 小于 20000查出所有birth_date及对应记录的指针;
根据birth_date排序;
根据指针查出所有记录后返回。
Using index
简单来说就是不需要回表,查询的数据直接根据索引就能拿到,不需要查询真正的数据行。
上面的查询因为dept_name上有索引,所以这里直接根据索引就能拿到需要查询的数据。还有一种特殊情况,看下面的索引
由于索引的结构设计,key是索引列的值,value是聚簇索引(一般就是主键),所以查询列带上聚簇索引包含的列依然可以Using index。
- Using index condition
这里涉及一个概念,即索引条件下推(ICP),5.6版本后提供的新特性。where中关于索引的过滤条件下推到存储引擎减少不必要的网络IO,有两个重要的特点:
- 单表单索引
- 聚集索引无效
下面的例子我是为了测试特地加了first_name和last_name的联合索引。
- Using index for group-by
与Using index类似,表示MySQL找到了一个索引,该索引可用于检索GROUP BY或 DISTINCT查询的所有列,但是不需要对实际表进行任何额外的磁盘访问。
-
Using join buffer (Block Nested Loop)
,Using join buffer (Batched Key Access)
本次查询之前的查询结果被缓冲起来,然后本次查询是与上一次的连接是通过读取缓冲区的数据来执行的,Block Nested Loop和Batched Key Access是两种不同的算法。
- Using MRR
使用了多范围读取优化。MRR的目的是为了减少磁盘的随机IO(存储引擎先按照聚集索引排序再从磁盘获取数据),而且如果不是想获取数据的所有列,MRR是不具有优势的。二级索引在物理存储上是不连续的,所以如果没有MRR,随机IO将会很明显(大多数情况)。
-
Using sort_union(...)
,Using union(...)
,Using intersect(...)
索引合并优化时用到的优化算法,比如下面这个,两个索引的结果是通过union连接的。
Using temporary
为了执行该查询,MySQL需要创建一个临时表来保存结果。常见的就是GROUP BY和 ORDER BY子句后面跟着不同的列。
Using where
用WHERE子句作过滤,限制行记录范围去匹配下一个表或最终发送到客户端的行记录。
Using where with pushed condition
适用于NDB集群,类似于索引下推,将where条件下推到数据节点来避免一些无谓的网络消耗。
Zero limit
含有一个LIMIT 0子句,选不到任何记录。
写在最后
其中重要的几个就是 key、type 、rows、extra,其中key为null时,说明没有使用到索引,需要调整索引。type为ALL的地方,需要进行优化,一般需要达到ref、eq_ref级别,范围查找需要达到range。extra有Using filesort、Using temporary 的一定需要优化,根据rows可以直观看出优化结果。