I 概念
执行计划,简单的来说,是SQL在数据库中执行时情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字+SQL语句来查看SQL的执行计划。再配合 show warnings
查看优化器优化后的SQL以及警告信息。
测试数据
mysql官方提供了一份测试库数据放在了github上,所以我们利用它来学习(以下所有的sql都是基于这个employees数据库)
II 输出
EXPLAIN 输出列如下:
Column | 意义 |
---|---|
id | 该SELECT 标识符 |
select_type | SELECT 类型 |
table | 涉及的表,有别名则显示别名 |
partitions | 匹配的分区,当使用分区表时 |
type | 联接类型 |
possible_keys | 可供选择的可能索引 |
key | 实际选择的索引 |
key_len | 所选索引的长度 |
ref | 与索引比较的列 |
rows | 估计要检查的行数 |
filtered | 表按条件过滤的行百分比 |
Extra | 附加信息 |
一、id♥
查询的顺序,SQL执行顺序按照id大到小依次执行,同级(id相同)则从上至下。可以为null
当是union
结果时,这样的话table
列值为 <union
M,
N>
表示合并id为M和N的查询结果。
二、select_type♥
该表示查询的类型,具体查询类型如下表:
类型 | 说明 |
---|---|
SIMPLE | 简单查询(不使用union 和子查询) |
PRIMARY | 最外层的查询 |
UNION | union 后面的select |
DEPENDENT UNION | union 后面的select且依赖于外层查询(例如:在子查询中union且使用了外层查询的列用于过滤条件) |
UNION RESULT | union 的结果集 |
SUBQUERY | 子查询 |
DEPENDENT SUBQUERY | 子查询且依赖外层查询 |
DERIVED | 派生表(from select 或者 join select) |
MATERIALIZED | 物化子查询(物化是指将子查询结果生成为临时表) |
UNCACHEABLE SUBQUERY | 无法缓存的子查询 |
UNCACHEABLE UNION | union 后面的无法缓存子查询 |
SIMPLE
简单查询没有使用union
或者子查询
SELECT * FROM employees
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | 149711 | 100 | Using where |
PRIMARY
最外层的查询,可以有多个(在最外层的select中,每有一个join 或者笛卡尔积都会标记为PRIMARY
)且id相同
SELECT
e.*,
a.sum_salary
FROM
employees e
LEFT JOIN ( SELECT s.emp_no, sum( s.salary ) AS sum_salary FROM salaries s GROUP BY s.emp_no ) a ON e.emp_no = a.emp_no
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | e | ALL | 299423 | 100 | ||||||
1 | PRIMARY | ref | <auto_key0> | <auto_key0> | 4 | employees.e.emp_no | 10 | 100 | |||
2 | DERIVED | s | index | PRIMARY | PRIMARY | 7 | 2838462 | 100 | Using aggregate |
UNION
和UNION RESULT
union
关键字后面的select
标记成为UNION
,最终union的结果标记为UNION RESULT
SELECT
e.*
FROM
employees e
WHERE
e.emp_no = 10001 UNION
SELECT
e.*
FROM
employees e
WHERE
e.emp_no = 10002 UNION
SELECT
e.*
FROM
employees e
WHERE
e.emp_no = 10003
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | e | const | PRIMARY | PRIMARY | 4 | const | 1 | 100 | ||
2 | UNION | e | const | PRIMARY | PRIMARY | 4 | const | 1 | 100 | ||
3 | UNION | e | const | PRIMARY | PRIMARY | 4 | const | 1 | 100 | ||
UNION RESULT | <union1,2,3> | ALL | Using temporary |
DEPENDENT UNION
union
关键字后面的select
标记成为UNION
,如果它依赖外层查询条件则会标记为 DEPENDENT UNION
,Mysql会使用EXISTS
优化in 子查询
SELECT
*
FROM
employees e1
WHERE
e1.emp_no IN (
-- mysql in 优化
SELECT e2.emp_no FROM employees e2 WHERE e2.first_name = 'Matt'
UNION
SELECT e3.emp_no FROM employees e3 WHERE e3.first_name = 'Bezalel'
)
-- ------------------优化后等同于以下sql--------------------------------
SELECT
*
FROM
employees e1
WHERE
EXISTS (
SELECT e2.emp_no FROM employees e2 WHERE e2.first_name = 'Matt' and e1.emp_no=e1.emp_no
UNION
SELECT e3.emp_no FROM employees e3 WHERE e3.first_name = 'Bezalel' and e3.emp_no=e1.emp_no
)
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | e1 | ALL | 299423 | 100 | Using where | |||||
2 | DEPENDENT SUBQUERY | e2 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 10 | Using where | |
3 | DEPENDENT UNION | e3 | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 10 | Using where | |
UNION RESULT | <union2,3> | ALL | Using temporary |
SUBQUERY
子查询在select 或者 where中的 select(在不优化的情况下)
SELECT
d.*,
(SELECT COUNT(*) FROM departments ) as dept_num
FROM
departments d
WHERE
d.dept_no = (SELECT dept_no FROM dept_emp de WHERE de.emp_no=10001)
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | d | const | PRIMARY | PRIMARY | 12 | const | 1 | 100 | ||
3 | SUBQUERY | de | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100 | Using index | |
2 | SUBQUERY | departments | index | dept_name | 122 | 9 | 100 | Using aggregate; Using index |
DEPENDENT SUBQUERY
依赖子查询是在子查询基础上依赖于外部查询条件。
SELECT
e.*,
-- 子查询依赖与外层查询
( SELECT sum( s.salary ) FROM salaries s WHERE s.emp_no = e.emp_no ) AS 'sum'
FROM
employees e
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | e | ALL | 299423 | 100 | ||||||
2 | DEPENDENT SUBQUERY | s | ref | PRIMARY | PRIMARY | 4 | employees.e.emp_no | 10 | 100 | Using aggregate |
DERIVED
派生表,即通过查询得到一张临时表用于from中select or join 中 select
SELECT
e.*,
a.sum_salary
FROM
-- from select
( SELECT emp_no,COUNT(*) as total_titles FROM titles GROUP BY emp_no ) e
-- join select
LEFT JOIN ( SELECT s.emp_no, sum( s.salary ) AS sum_salary FROM salaries s GROUP BY s.emp_no ) a ON e.emp_no = a.emp_no
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | d | const | PRIMARY | PRIMARY | 12 | const | 1 | 100 | ||
3 | SUBQUERY | de | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100 | Using index | |
2 | SUBQUERY | departments | index | dept_name | 122 | 9 | 100 | Using aggregate; Using index |
MATERIALIZED
物化子查询,优化器使用物化来实现更高效的子查询处理。物化通过将子查询结果生成为临时表(通常在内存中)来加速查询执行。MySQL第一次需要子查询结果时,它会将该结果具体化到一个临时表中。任何后续需要结果时,MySQL 都会再次引用临时表。例如
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
当时用物化来优化子查询时,此时explain的select type 可能是SUBQUERY而在show WARNINGS 中的Message中会出现 materialize
和 materialized-subquery
表示当前子查询结果被物化。
三、table
涉及的表名或者自定义的别名(as xxx)。这也可以是以下值之一:
<union
M,
N>
: 来着id
为M结果union
id为N结束。<derived
N>
:来着id
为 *N
*派生表。例如,派生表可能来自FROM
(包含join)子句中的子查询 。<subquery
N>
:来着id
为*N
*的物化子查询。
四、partitions
记录将与查询匹配的分区。对于非分区表,该值为NULL。
五、type♥
该属性表示查询类型读取数据的方式,从最差到最好的类型如下:
类型 | 说明 |
---|---|
ALL | 全表扫描 |
index | 基于索引全表扫描 |
range | 范围查询索引 |
unique_subquery | 子查询结果是唯一 |
index_merge | 索引合并(使用多个索引查询后合并结果) |
ref_or_null | 类似于ref,只是额外扫描null值 |
fulltext | 使用全文索引 |
ref | 对于先前表,当前表中读取匹配索引值的所有行(可匹配出多行) |
eq_ref | 对于先前表,当前表索引值只匹配一条(唯一性索引) |
const | 唯一性索引并只匹配一条记录 |
system | 只有一行记录的表 |
NULL | 不用扫描表或索引 |
ALL
全表扫描,最差的情况,也就是要被挨骂的情况,例如使用组合索引没有符合最左匹配原则
SELECT * FROM titles WHERE title ='Senior Engineer'
titles
表中PRIMARY KEY
为 (emp_no
,title
,from_date
) title
字段位于中间无法通过索引去匹配,所以需要全表扫描。执行计划中 extra列Using where
表示对结果进行过滤。
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | titles | ALL | 433321 | 10 | Using where |
index
基于索引全表扫描,分以下两种情况:
-
如果索引是查询的覆盖索引(即索引值可以满足查询所有需要的数据),则只扫描索引树。在这种情况下,该
Extra
列显示Using index
。仅索引扫描通常比ALL
要快 ,因为索引的数据通常小于整个表的数据。SELECT dept_name FROM departments ORDER BY dept_name desc
dept_name
为唯一索引。我们只查询dept_name
即索引值符合查询所需要的列,当然不需要回表。id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE departments index dept_name 122 9 100 Using index -
先索引顺序查找数据所在行也就主键,然后利用查找的行进行全表扫描。
Uses index
不会出现在Extra
列中。SELECT * FROM titles WHERE emp_no= 10001 ORDER BY emp_no desc
PRIMARY KEY (
emp_no
,title
,from_date
),我们按emp_no
进行排序需要扫描索引(索引本身就序),而且我们查询所有字段是需要回表的。id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE titles index PRIMARY 159 433321 100 type=index
多数出现在利用索引排序。
range
范围扫描仅检索索引值在给定范围内的行。 执行计划的key指示使用哪个索引。 key_len包含使用的最长密钥部分。 对于此类型,ref列为NULL。使用=
(非唯一索引),<>
,>
,>=
,<
,<=
,IS NULL
,<=>
,BETWEEN
,LIKE
或IN()
运算符中的任何一个将键列与常量进行比较时,则可以使用range
。
-- emp_no 为主键
SELECT * FROM `employees` WHERE emp_no>10001 and emp_no<10012
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | 10 | 100 | Using where |
unique_subquery
这种类型替代 了以下形式的eq_ref
一些 IN
子查询:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery
只是一个索引查找函数,完全替换子查询以提高效率。
index_merge
表示使用了索引合并优化(同时使用多个索引最后合并结果)。在这种情况下,key
列包含所使用索引的列表,并key_len
包含所使用索引 的最长关键部分的列表。索引合并优化具体参考“官方文档”。
ref_or_null
类似于ref
,但是Mysql会额外搜索包含NULL的行记录
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
fulltext
表示使用FULLTEXT
索引执行的
ref
♥
对于先前表,从该表中读取匹配索引值的所有行(非唯一性索引)。ref表示仅使用键的最左前缀或键不是 PRIMARY KEY
或 UNIQUE
索引(换句话说就是对于前面的表无法根据索引值只匹配一行)。使用非唯一索引进行等值查询时也可ref。
-- 在titles emp_no只是主键的最左前缀
SELECT * FROM employees a,titles b
WHERE a.emp_no = b.emp_no
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | a | ALL | PRIMARY | 299423 | 100 | |||||
1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 4 | employees.a.emp_no | 1 | 100 |
eq_ref
对于前面的表该表只匹配一行(唯一性索引)。当匹配的索引是**PRIMARY KEY
或UNIQUE
** 且 not null索引时使用它。eq_ref
可用于使用=
运算符进行比较的索引列,比较值可以是常量也可以是表达式。
SELECT
*
FROM
employees a
-- dept_emp中emp_no为主键的前缀(可能匹配多个) 所以为ref
LEFT JOIN dept_emp b ON a.emp_no = b.emp_no
-- departments表中dept_no为主键 能够唯一匹配一行 所以为eq_ref
LEFT JOIN departments c ON b.dept_no = c.dept_no
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | a | ALL | 299423 | 100 | ||||||
1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 4 | employees.a.emp_no | 1 | 100 | ||
1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 12 | employees.b.dept_no | 1 | 100 |
const
表示该表最多匹配一行,const
标记哪些对**PRIMARY KEY
或UNIQUE
索引进行常量值**等值比较。
#格式
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;
#样例
SELECT * FROM departments a WHERE a.dept_name = 'Finance'
-- ---------上面等同于以下----------
SELECT a.dept_name,a.dept_no FROM departments a WHERE a.dept_name = 'Finance'
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | a | const | dept_name | dept_name | 122 | const | 1 | 100 | Using index |
注意:因为departments
只有两个字段:主键 dept_no和唯一索引 dept_name(那么select *
=select dept_name,dept_no
),而在innoDb中辅助索引(非聚集索引)其中存放了对应的聚集索引值(此处即dept_no值)所以不需要回表,其Extra
中显示Using index
。
system
表示该表只有一个行(系统表),这个是一个const
特例。
NULL
表示不需要索引或者不需要扫描表。例如过滤条件永远不成立的情况。
六、possible_keys
顾名思义,该属性给出了,该查询语句,可能走的索引,(如某些字段上索引的名字)这里提供的只是参考,而不是实际走的索引,也就导致会有possible_Keys不为null,key为空的现象。
七、key
实际使用的索引,其中就包括主键索引PRIMARY
,或者自建索引的名字。
八、key_len
表示决定使用索引的字节数。在不损失精确性的情况下,长度越短越好。
九、ref
显示哪些列(列名)或常量(显示const
)与key
列中指定的索引进行比较。
十、rows♥
扫描行数。也就是说需要扫描多少行才能获取需要的数据。通常情况下,rows越小,效率越高, 也就有大部分SQL优化,都是在减少这个值的大小(优化的指标)。在InnoDB中,这只是一个估计值并不一定是准确的。
十一、filtered
百分比值:表示将按条件筛选的行占rows的估计百分比。 最大值为100,这意味着不会对行进行过滤。 值从100开始减少表示过滤量增加。 rows显示已检查的估计行数,rows×filtered
显示将与下表连接的行数。 例如,如果rows
为1000且filtered
为50.00(50%),则使用下表连接的行数为1000×50%= 500。
十二、 Extra♥
这个属性非常重要,该属性中包括执行SQL时的真实情况信息,例如常见的Using index
(覆盖索引)、Using index condition
(索引下推)、using where
(服务层应用过滤条件)等等。
Child of ‘table’ pushed join@1
此表在连接中被引用为表的子节点,可以将其下推到NDB内核。 仅当启用了 pushed-down连接时,才适用于NDB群集。
const row not found
对于诸如SELECT ... FROM tbl_name之类的查询,该表为空。
Deleting all rows
对于DELETE,某些存储引擎(如MyISAM)支持一种处理程序方法,该方法以简单快捷的方式删除所有表行。 如果引擎使用此优化,则会显示此附加值。
Distinct
MySQL正在寻找不同的值,因此它在找到第一个匹配行后停止为当前行组合搜索更多行。
FirstMatch(tbl_name)
半连接FirstMatch连接快捷方式
Full scan on NULL key
当优化程序无法使用 index-lookup 访问时,子查询优化将作为回退策略发生。
Impossible HAVING
HAVING子句始终为false,无法查询任何行。
Impossible WHERE
WHERE子句始终为false,无法查询任何行。
Impossible WHERE noticed after reading const tables
MySQL已经读取了所有const
(和system
)表,并注意到WHERE子句始终为false。
LooseScan(m..n)
使用半连接LooseScan策略。 m和n是关键部件号。
No matching min/max row
没有行满足查询的条件,例如SELECT MIN(…)FROM … WHERE条件。
No matching rows after partition pruning
对于DELETE或UPDATE,优化器在分区修剪后未发现任何要删除或更新的内容。 它与SELECT语句的Impossible WHERE的含义相似。
No tables used
查询语句没有制定from子句,或者from dual子句。
对于INSERT或REPLACE语句,EXPLAIN在没有SELECT部分时显示该值。例如,它出现在EXPLAIN INSERT INTO VALUES(10)
中,因为它等同于EXPLAIN INSERT INTO SELECT 101 FROM DUAL
。
Not exists
MySQL能够对查询执行LEFT JOIN优化,并且在找到与LEFT JOIN条件匹配的行之后,不会检查此表中针对上一行组合的更多行。 以下是可以通过以下方式优化的查询类型的示例:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;
假设t2.id被定义为NOT NULL。 在这种情况下,MySQL扫描t1并使用t1.id的值在t2中查找行。 如果MySQL在t2中找到匹配的行,则它知道t2.id永远不能为NULL,并且不会扫描t2中具有相同id值的其余行。 换句话说,对于t1中的每一行,MySQL需要在t2中只进行一次查找,而不管t2中实际匹配多少行。
Plan isn’t ready yet
当优化器尚未完成为在命名连接中执行的语句创建执行计划时,EXPLAIN FOR CONNECTION会出现此值。 如果执行计划输出包含多行,则其中的任何一个或全部都可以具有此Extra值,具体取决于优化程序在确定完整执行计划时的进度。
Range checked for each record
MySQL发现没有好的索引可以使用,但发现在前面的表的列值已知后可能会使用某些索引。 对于上表中的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来检索行。 这不是很快,但比执行没有索引的连接更快。
Scanned N databases
这表示在处理INFORMATION_SCHEMA表的查询时服务器执行的目录扫描数。 N的值可以是0,1或all。
Select tables optimized away
使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询,例如MIN()\MAX(),这种也是比较好的结果之一
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
这表示临时表用于半连接Duplicate Weedout策略。
unique row not found
对于诸如SELECT ... FROM tbl_name之类的查询,没有行满足表上UNIQUE
索引或PRIMARY KEY
的条件。
Using filesort ♥
表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。 MySQL 中无法利用索引完成的排序操作称为“文件排序”。当出现此值时,需要尝试对排序字段建立索引。
-- emp_no 为主键
SELECT emp_no FROM employees WHERE emp_no> 10001 ORDER BY first_name desc
此时执行计划中type为range
而Extra
中Using where; Using filesort
,需要将查询结果放在内存中排序。
Using index ♥
覆盖索引的情况。表示仅使用索引树中的信息从表中检索列信息,而不必另外寻找读取实际行(不需要回表)。 当查询的字段仅使用属于单个索引的列时,可以使用此策略。
对于具有用户定义的聚簇索引的InnoDB表,即使Extra列中不存在使用索引,也可以使用该索引。 如果type是index并且key是PRIMARY,则会出现这种情况。
Using index condition ♥
使用索引下推,MySQL5.6提供的新特性,即将过滤条件应用到索引上,在存储引擎层通过索引就可以过滤掉不需要的数据。索引下推只能用于二级索引对于Innodb引擎来说。
Using index for group-by
与Using index table访问方法类似,Using group for group-by表示MySQL找到了一个索引,可用于检索GROUP BY或DISTINCT查询的所有列,而无需对实际表进行任何额外的磁盘访问。 此外,索引以最有效的方式使用,因此对于每个组,只读取少数索引条目。
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)
将联接中的表分成几部分读入连接缓冲区,然后从缓冲区中使用它们的行来与当前表执行连接。 (Block Nested Loop)表示使用块嵌套循环算法,(Batched Key Access)表示使用批量密钥访问算法。 也就是说,来自EXPLAIN输出前一行的表中的键将被缓冲,匹配的行将从连接缓冲区的行的表中批量提取。
Using MRR
使用多范围读取优化策略读取表
Using sort_union(…), Using union(…), Using intersect(…)
显示如何为index_merge连接类型合并索引扫描。
Using temporary ♥
表示 MySQL 使用临时表来保存查询结果。常见于排序 order by和分组查询group by
Using where ♥
表示对存储引擎返回的结果进行了过滤。此时改过滤操作是在服务层进行的!!!,所以需要注意看是否扫描不需要的数据。
Using where with pushed condition
此项仅适用于NDB表。 这意味着NDB Cluster正在使用条件下推优化来提高非索引列和常量之间直接比较的效率。
Zero limit
该查询具有LIMIT 0子句,无法选择任何行。