MySQL索引-Explain详解

2021-06-22  本文已影响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

输出行所引用的表的名称。这也可以是以下值之一：

四、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类型只扫描索引树。这有两种方式：

当查询只使用属于单个索引的列时，MySQL可以使用这种连接类型。

7、ALL

ALL 全表扫描。如果表是第一个未被标记为const的表，这通常是不好的，在所有其他情况下通常是非常糟糕的。通常，可以通过添加索引来避免使用ALL，这些索引支持基于常值从表中进行行检索，或基于以前表中的列值进行行检索。

8、其他

六、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

上一篇下一篇

猜你喜欢

热点阅读