MySQL-Explain详解

Explain工具介绍

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

Explain分析示例

EXPLAIN SELECT * FROM shop_order;

image.png

① id列的编号是查询的序号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type

查询类型
① SIMPLE：简单查询，不使用 UNION或子查询
② PRIMARY：最外层查询
③ UNION：位于UNION中第二个及其以后的子查询
④ DEPENDENT UNION：首先满足UNION的条件，即位于UNION中第二个及其以后的子查询，同时依赖外部的查询
⑤ UNION RESULT：用来从匿名临时表里检索结果的select被标记为UNION RESULT
⑥ SUBQUERY：包含在 select 中的子查询（不在 from 子句中）
⑦ DEPENDENT SUBQUERY：和DEPENDENT UNION相对UNION一样
⑧ DERIVED：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

table

table.png

① 表示 explain 的一行正在访问哪个表。
② 当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
③ 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

partitions

① 当前查询匹配记录的分区。对于未分区的表，返回null

type

关联类型或访问类型，优先级system > const > eq_ref > ref > range > index > ALL

type.png

① system：system是const的特例，表里只有一行匹配时为system。
② const：该表最多有一个匹配行，在查询开始时读取。因为只有一行，所以这一行中列的值可以被优化器的其余部分视为常量，用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。

eq_ref.png

③ eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了

ref.png

④ ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

range.png

⑤ range：检索给定范围内的行，通常出现在 in(), between ,> ,<, >= 等操作中。

index.png

⑥ index：与ALL相同只是扫描了索引树。
1、如果索引是查询的覆盖索引并且可以用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，该Extra列显示Using index
2、使用从索引中读取以按索引顺序查找数据行来执行全表扫描。 Uses index没有出现在 Extra列中。

all.png

⑦ ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。

possible_keys

① 可能使用哪些索引来查找，如果此列是NULL，则没有相关索引。在这种情况下，可以通过检查WHERE 子句来检查它是否引用了适合索引的某些列或列，从而提高查询的性能

① 该列指示 MySQL 实际决定使用的键（索引），如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

① 显示了mysql在索引里使用的字节数
② key_len的计算规则：
1、如果列可以为空，则在数值类型占用字节的基础上加1，例如：int型不能为空key_len为4，可以为空key_len为5；

2.如果列是字符型，则还需要考虑字符集，如某列的定义是char(10)，且是utf8，不能为空，则key_len为10 * 3 ,可以为空则key_len为 10 * 3+1；如果列是变长的，则在数据列所占字节的基数上再加2，如varchar(10)，不能为空，则key_len为10 * 3 + 2 ，可以为空则key_len为10*3+2+1

3、时间类型：date：3字节；timestamp：4字节；datetime：8字节。数值类型：tinyint：1字节；smallint：2字节；int：4字节；bigint：8字节

① 该ref列显示将哪些列或常量与列中指定的索引进行比较以 key从表中选择行

rows

① 是mysql估计要读取并检测的行数，注意这个不是结果集里的行数

filtered

① 指示按表条件过滤的表行的估计百分比。最大值为 100，这意味着没有过滤行。从 100 开始减小的值表示过滤量增加。 rows显示检查的估计行数，rows× filtered显示与下表连接的行数。例如，如果 rows是 1000 并且 filtered是 50.00 (50%)，则要与下表连接的行数是 1000 × 50% = 500

Extra

① 包含有关 MySQL 如何解析查询的附加信息，

image.png

② Using index：仅使用索引树中的信息从表中检索列信息，而无需执行额外的查找来读取实际行

image.png

③ Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

image.png

④ Using index condition：叫作Index Condition Pushdown Optimization (索引下推优化)，ICP是MySQL使用索引从表中检索行的一种优化。如果没有ICP，存储引擎将遍历索引以定位表中的行，并将它们返回给MySQL服务器，服务器将判断行的WHERE条件。在启用ICP的情况下，如果可以只使用索引中的列来计算WHERE条件的一部分，MySQL服务器就会将WHERE条件的这一部分推到存储引擎中。然后，存储引擎通过使用索引条目来评估推入的索引条件，只有当满足该条件时，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。