MySQL索引-Explain详解

作者: 垓下 | 来源:发表于2021-06-22 17:19 被阅读0次

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