MySQL执行计划

319 阅读9分钟

I 概念

执行计划,简单的来说,是SQL在数据库中执行时情况,通常用于SQL性能分析,优化等场景。在MySQL使用 explain 关键字+SQL语句来查看SQL的执行计划。再配合 show warnings查看优化器优化后的SQL以及警告信息。

测试数据

mysql官方提供了一份测试库数据放在了github上,所以我们利用它来学习(以下所有的sql都是基于这个employees数据库)

II 输出

EXPLAIN 输出列如下:

Column意义
idSELECT标识符
select_typeSELECT类型
table涉及的表,有别名则显示别名
partitions匹配的分区,当使用分区表时
type联接类型
possible_keys可供选择的可能索引
key实际选择的索引
key_len所选索引的长度
ref索引比较的列
rows估计要检查的行数
filtered表按条件过滤的行百分比
Extra附加信息

一、id♥

查询的顺序,SQL执行顺序按照id大到小依次执行,同级(id相同)则从上至下。可以为null当是union结果时,这样的话table列值为 <unionM,N>表示合并id为M和N的查询结果。

二、select_type♥

该表示查询的类型,具体查询类型如下表:

类型说明
SIMPLE简单查询(不使用union和子查询)
PRIMARY最外层的查询
UNIONunion后面的select
DEPENDENT UNIONunion后面的select且依赖于外层查询(例如:在子查询中union且使用了外层查询的列用于过滤条件)
UNION RESULTunion的结果集
SUBQUERY子查询
DEPENDENT SUBQUERY子查询且依赖外层查询
DERIVED派生表(from select 或者 join select)
MATERIALIZED物化子查询(物化是指将子查询结果生成为临时表
UNCACHEABLE SUBQUERY无法缓存的子查询
UNCACHEABLE UNIONunion后面的无法缓存子查询

SIMPLE

简单查询没有使用union或者子查询

SELECT * FROM employees 
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEemployeesrangePRIMARYPRIMARY4149711100Using 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
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYeALL299423100
1PRIMARYref<auto_key0><auto_key0>4employees.e.emp_no10100
2DERIVEDsindexPRIMARYPRIMARY72838462100Using aggregate

UNIONUNION 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
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYeconstPRIMARYPRIMARY4const1100
2UNIONeconstPRIMARYPRIMARY4const1100
3UNIONeconstPRIMARYPRIMARY4const1100
UNION RESULT<union1,2,3>ALLUsing 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
)
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYe1ALL299423100Using where
2DEPENDENT SUBQUERYe2eq_refPRIMARYPRIMARY4func110Using where
3DEPENDENT UNIONe3eq_refPRIMARYPRIMARY4func110Using where
UNION RESULT<union2,3>ALLUsing 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)
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYdconstPRIMARYPRIMARY12const1100
3SUBQUERYderefPRIMARYPRIMARY4const1100Using index
2SUBQUERYdepartmentsindexdept_name1229100Using 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
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYeALL299423100
2DEPENDENT SUBQUERYsrefPRIMARYPRIMARY4employees.e.emp_no10100Using 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
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYdconstPRIMARYPRIMARY12const1100
3SUBQUERYderefPRIMARYPRIMARY4const1100Using index
2SUBQUERYdepartmentsindexdept_name1229100Using 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中会出现 materializematerialized-subquery表示当前子查询结果被物化。

三、table

涉及的表名或者自定义的别名(as xxx)。这也可以是以下值之一:

  • <unionM,N>: 来着 idM结果 union id为N结束。
  • <derivedN>:来着id为 *N*派生表。例如,派生表可能来自FROM (包含join)子句中的子查询 。
  • <subqueryN>:来着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表示对结果进行过滤。

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEtitlesALL43332110Using where

index

基于索引全表扫描,分以下两种情况:

  • 如果索引是查询的覆盖索引(即索引值可以满足查询所有需要的数据),则只扫描索引树。在这种情况下,该Extra列显示 Using index。仅索引扫描通常比ALL要快 ,因为索引的数据通常小于整个表的数据。

    SELECT dept_name FROM departments  ORDER BY  dept_name desc
    

    dept_name 为唯一索引。我们只查询dept_name即索引值符合查询所需要的列,当然不需要回表。

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEdepartmentsindexdept_name1229100Using 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进行排序需要扫描索引(索引本身就序),而且我们查询所有字段是需要回表的。

    idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
    1SIMPLEtitlesindexPRIMARY159433321100

    type=index多数出现在利用索引排序。

range

范围扫描仅检索索引值在给定范围内的行。 执行计划的key指示使用哪个索引。 key_len包含使用的最长密钥部分。 对于此类型,ref列为NULL。使用=(非唯一索引),<>>>=<<=IS NULL<=>BETWEENLIKEIN()运算符中的任何一个将键列与常量进行比较时,则可以使用range

-- emp_no 为主键
SELECT * FROM `employees` WHERE emp_no>10001 and emp_no<10012
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEemployeesrangePRIMARYPRIMARY410100Using 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 KEYUNIQUE索引(换句话说就是对于前面的表无法根据索引值只匹配一行)。使用非唯一索引进行等值查询时也可ref。

-- 在titles emp_no只是主键的最左前缀
SELECT * FROM employees a,titles b 
  WHERE a.emp_no = b.emp_no
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEaALLPRIMARY299423100
1SIMPLEbrefPRIMARYPRIMARY4employees.a.emp_no1100

eq_ref

对于前面的表该表只匹配一行(唯一性索引)。当匹配的索引是**PRIMARY KEYUNIQUE** 且 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
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEaALL299423100
1SIMPLEbrefPRIMARYPRIMARY4employees.a.emp_no1100
1SIMPLEceq_refPRIMARYPRIMARY12employees.b.dept_no1100

const

表示该表最多匹配一行const标记哪些对**PRIMARY KEYUNIQUE索引进行常量值**等值比较。

#格式
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'
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEaconstdept_namedept_name122const1100Using 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为rangeExtraUsing 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子句,无法选择任何行。