EXPLAIN
用于获取查询执行计划(即 MySQL 如何执行查询的解释)
当EXPLAIN
与可解释语句(SELECT
, DELETE
, INSERT
, REPLACE
,和 UPDATE
)一起使用时,MySQL 会显示来自优化器的有关语句执行计划的信息。也就是说,MySQL 解释了它将如何处理语句,包括有关如何连接表和按什么顺序连接的信息。
EXPLAIN 输出列
列 | 说明 |
---|---|
id | 该SELECT标识符 |
select_type | 该SELECT类型 |
table | 输出行的表 |
partitions | 匹配的分区 |
type | 联接类型 |
possible_keys | 可供选择的可能索引 |
key | 实际选择的索引 |
key_len | 所选密钥的长度 |
ref | 与索引比较的列 |
rows | 估计要检查的行数 |
filtered | 按表条件过滤的行百分比 |
Extra | 附加信息 |
一、 id
SELECT识别符。这是SELECT的查询序列号,即:SQL执行的顺序的标识。
1. id相同时,执行顺序由上往下
EXPLAIN SELECT
fa_exam_user.*
FROM
fa_exam,
fa_exam_user
WHERE
fa_exam_user.exam_id = fa_exam.id
AND fa_exam.`name` = '数学考试';
如上例所示,当id相同时SQL执行的顺序为由上至下,先查询fa_exam,再查询fa_exam_user。
2. id不同时,id值越大优先级越高,越先被执行
EXPLAIN SELECT
*
FROM
fa_exam_user
WHERE
exam_id = ( SELECT id FROM fa_exam WHERE `name` = '数学考试' );
一般子查询,id的序号会递增。如上例所示,当id由小到大,id越大优先级越高,所以先查询fa_exam,再查询fa_exam_user。
3. id相同又不同时,id相同的可以认为是一组,由上往下顺序执行;在所有组中,id值越大优先级越高,越先被执行
EXPLAIN SELECT
fa_exam_user.*
FROM
fa_exam,
fa_exam_user
WHERE
fa_exam_user.exam_id = fa_exam.id
AND fa_exam.`name` = '数学考试'
AND fa_exam_user.admin_id = (
SELECT id FROM fa_admin WHERE `real_name` = '哈哈'
);
如上例所示, id既有相同又有不同时,id相同的可以认为是一组,组里执行顺序从上往下;在所有组中,id值越大优先级越高,越先被执行。因此,先查询的是fa_admin,然后查询fa_exam_user,最后查询fa_exam。
二、select_type
表示查询中select的类型,可以是下表中的任何一种
列 | 说明 |
---|---|
SIMPLE | 简单SELECT(不使用UNION或子查询) |
PRIMARY | 最外层的SELECT,子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY |
UNION | UNION中的第二个或之后的SELECT语句 |
DEPENDENT UNION | UNION中的第二个或之后的SELECT语句,取决于外层的查询 |
UNION RESULT | UNION的结果,UNION语句中第二个SELECT或之后的SELECT语句 |
SUBQUERY | 子查询中的第一个SELECT子,结果不依赖于外层的查询 |
DEPENDENT SUBQUERY | 子查询中的第一个SELECT,结果依赖于外部查询 |
DERIVED | 派生表(Derived table),派生表的SELECT, FROM子句的子查询 |
MATERIALIZED | 物化子查询 |
UNCACHEABLE SUBQUERY | 不能缓存其结果的子查询,必须为外层查询的每一行重新求值 |
UNCACHEABLE UNION | UNION中属于非缓存子查询的第二个或之后的选择(参考UNCACHEABLE SUBQUERY),一个子查询的结果不能被缓存,必须重新评估外链接的第一行 |
三、table
输出行所引用的表的名称。这也可以是以下值之一:
-
<unionM,N>: 行是指具有 和id值的行 的 M并集 N。
-
<derivedN>:该行是指用于与该行的派生表结果id的值 N。例如,派生表可能来自FROM子句中的子查询 。
-
<subqueryN>:该行是指与物化子查询该行的结果id 的值N。
四、partitions
partitions,查询将匹配记录的分区。该值NULL用于非分区表。
五、type
type,表的连接类型。常用的类型有:system、const、eq_ref、 ref、range、index、ALL(从左到右,性能从好到差)。不同类型的说明,该type列 EXPLAIN输出介绍如何连接表。在 JSON 格式的输出中,这些作为access_type属性的值被找到。
下面的描述了连接类型,从最好的类型到最差的类型:
1、system
system 表示该表只有一行(基本相当于系统表)。这是const连接类型的一个特例 。
2、const
const 表示该表最多有一个匹配行,在查询开始时读取。因为只有一行,所以该行中该列的值可以被优化器的其余部分视为常量。 const表非常快,因为它们只被读取一次。
const用于将主键或惟一索引的所有部分与常量值进行比较。
在以下查询中,tbl_name可以作为const表使用:
EXPLAIN SELECT * FROM fa_exam WHERE id = 4;
使用唯一索引查询:
EXPLAIN SELECT * FROM fa_exam WHERE exam_no = 'E202106101155392510';
如果查询的不是唯一索引,则typa为ALL。
3、eq_ref
eq_ref 对于前面表中的每个行组合,从该表中读取一行。除了 system和const类型之外,这是最好的连接类型。当连接使用索引的所有部分并且索引是一个 PRIMARY KEY或UNIQUE NOT NULL索引时使用它。eq_ref可用于使用=运算符进行比较的索引列 。比较值可以是常量或表达式,该表达式使用在此表之前读取的表中的列。
在以下示例中,MySQL 可以使用 eq_ref连接来处理 fa_exam_user ,以为只有一条数据满足fa_exam_user.id = fa_exam.id:
如果把查询条件改成fa_exam_user.exam_id = fa_exam.id,SQL语句如下:
EXPLAIN SELECT fa_exam_user.* FROM fa_exam, fa_exam_user WHERE fa_exam_user.exam_id = fa_exam.id AND fa_exam.name
LIKE '%数学%';
因为满足fa_exam_user.exam_id = fa_exam.id条件的数据是多条,所以是ref类型。
4、ref
ref 对于先前表中的每个行组合,从该表中读取具有匹配索引值的所有行。ref如果联接仅使用键的最左前缀或键不是 aPRIMARY KEY或 UNIQUE索引(换句话说,如果联接无法根据键值选择单行),则使用。如果使用的键只匹配几行,这是一个很好的连接类型。ref可用于使用=or<=> 运算符进行比较的索引列 。在以下示例中,MySQL 可以使用 ref连接来处理 ref_table:
5、range
range 使用索引返回一个范围中的行(索引范围扫描)。输出行中的key列表明使用了哪个索引。key_len包含所使用的key使用的字节数。对于这种类型,ref列是NULL。
range类型常用于<>,>,>=,<,<=,is NULL, <=>, BETWEEN, LIKE, or IN()等操作符:
EXPLAIN SELECT * FROM fa_exam WHERE id <= 28;
EXPLAIN SELECT * FROM fa_exam WHERE id IN (27,28);
如果查询的不是索引,则类型不是range:
注意!!最少也应该要使用索引到range级别!
6、index
index 该index连接类型与ALL相同,只是index类型只扫描索引树。这有两种方式:
-
如果索引是查询的覆盖索引,可以满足表中所有需要的数据,则只扫描索引树。在这种情况下,该Extra列显示 Using index。仅索引扫描通常比ALL索引的大小通常小于表数据的大小要快 。
-
使用从索引中读取来执行全表扫描以按索引顺序查找数据行。 Uses index不会出现在 Extra列中。
当查询只使用属于单个索引的列时,MySQL可以使用这种连接类型。
7、ALL
ALL 全表扫描。如果表是第一个未被标记为const的表,这通常是不好的,在所有其他情况下通常是非常糟糕的。通常,可以通过添加索引来避免使用ALL,这些索引支持基于常值从表中进行行检索,或基于以前表中的列值进行行检索。
8、其他
-
fulltext连接是使用FULLTEXT 索引执行的。
-
ref_or_null这种连接类型类似于 ref,但另外,MySQL 会额外搜索包含NULL值的行。这种连接类型优化最常用于解析子查询。在以下示例中,MySQL 可以使用 ref_or_null连接来处理ref_table:SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL。
-
index_merge 索引合并。表示查询使用了两个或者以上的索引数量,常见于and或者or查询匹配上了多个不同索引的字段。
-
unique_subquery这种类型替代 了以下形式的eq_ref一些 IN子查询:value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery 只是一个索引查找函数,完全替换子查询以提高效率。
-
index_subquery这种联接类型类似于 unique_subquery. 它取代了IN子查询,但它适用于以下形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
六、possible_keys
possible_keys,表示MySQ查询时可能使用的索引。注意,此列完全独立于EXPLAIN输出中显示的表的顺序。这意味着possible_keys中的一些键在实际生成的表顺序中可能不可用。
如果该列为NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句来检查查询是否引用了某些列(或适合创建索引的列)从而提高查询的性能。如果是,可以创建一个适当的索引并再次使用EXPLAIN检查查询。
查看表的索引,SHOW INDEX FROM tbl_name。
七、keys
key,表示MySQL实际使用的键(索引)。key可以命名一个不存在于possible_keys值中的索引。如果没有一个possible_keys索引适合查找行,但是查询选择的所有列都是其他一些索引的列,就会发生这种情况。也就是说,已命名的索引涵盖所选的列,因此尽管它不用于确定要检索哪些行,但索引扫描比数据行扫描更有效。
对于InnoDB来说,即使查询也选择了主键,辅助索引也可能涵盖所选列,因为InnoDB会在每个辅助索引中存储主键值。如果key是NULL, MySQL找不到索引来更有效地执行查询。
要强制MySQL使用或忽略列中列出的索引,可以在查询中使用force index、use index或ignore index。
对于MyISAM表,运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表,myisamchk -analyze也做同样的工作。
八、key_len
key_len,表示索引的使用的字节数,可通过该列计算查询中使用的索引的长度(显示的值为索引字段的最大可能长度,并非实际使用长度,key_len是根据表定义计算而得,不是通过表内检索出的)不损失精确性的情况下,长度越短越好 。
九、ref
ref,列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
ref,列显示哪些列或常量与键列中指定的索引进行比较,以从表中选择行。
如果值是func,则使用的值是某个函数的结果。要查看哪个函数,请在EXPLAIN后面使用SHOW WARNINGS来查看扩展的EXPLAIN输出。函数实际上可能是一个运算符,比如算术运算符。
十、rows
rows,表示MySQL认为在执行查询时必须检查的行数。对于InnoDB表,这个数字是一个估计值,可能并不总是准确的。
十一、filtered
filtered,按表条件过滤的行百分比
filtered,表示由表条件筛选的行数的估计百分比。最大值是100,这意味着没有对rows进行过滤。从100开始减小的值表示过滤的数量在增加。
Rows显示所检查的估计行数,而行×过滤显示与下表连接的行数。例如,如果rows为1000,而filtered为50.00(50%),则连接到下表的行数为1000 × 50% = 500。
十二、Extra
Extra,包含了MySQL解析查询的详细信息,主要有以下几种情况:
Using index:
重要。使用了覆盖索引,避免访问数据行,仅使用索引树中的信息从表中检索列信息,而不必执行额外的查找来读取实际的行。当查询只使用属于单个索引的列时,可以使用此策略。这个是非常好的。
对于具有用户定义的聚集索引的InnoDB表,即使Extra列中没有Using index,也可以使用该索引。如果type是index, key是PRIMARY,就会出现这种情况。
Using where:
不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary:
重要,需要优化。表示MySQL需要使用临时表来存储结果集,常见于排序和分组查询,常见 group by ; order by
Using filesort:
重要,需要优化。文件内排序,当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”,速度会很慢。
Using join buffer:
改值强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能。
Impossible where:
这个值强调了where语句会导致没有符合条件的行(通过收集统计信息不可能存在结果)。
Select tables optimized away:
这个值意味着仅通过使用索引,优化器可能仅从聚合函数结果中返回一行
No tables used:
Query语句中使用from dual 或不含任何from子句
-- explain select now() from dual;
注:
学习笔记,欢迎指正。
参考资料:《MySQL 5.7 Reference Manual 》https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
网友评论