【开发日记】MySQL-Explain学习日记

Explain

前言

本文使用的MySQL数据库版本为8.0.28。

参考的官方文档链接如下：

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

作用

用来查看MySQL是如何执行语句的信息，使用这个关键字并不会执行这条SQL语句；

EXPLAIN适用于SELECT、DELETE、INSERT、、REREPLACE和UPDATE语句。

使用方式

EXPLAIN SQL语句

如：

EXPLAIN SELECT a,b FROM test_table

执行结果

1、id列

表示执行顺序；id值越大执行顺序越靠前，id值相同则按照自上而下的顺序执行。

2、rows列和filtered列

rows * filtered / 100 可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值小的表）

3、select_type列

类型	说明
SIMPLE	简单查询。查询不包含子查询和union。
PRIMARY	复杂查询中最外层的select。
SUBQUERY	包含在select中的子查询（不在from子句中）
DERIVED	包含在from子句中的子查询。

4、table列

表示行数据引用的表的名称。还有其他出现的情况可以参考：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_table

5、partitions列

版本5.7以前，该项是EXPLAIN PARTITIONS显示的选项，5.7以后成为了默认选项；该列显示的为分区表命中的分区情况；非分区表该字段为NULL；

6、type列

这一列表示访问类型，决定MySQL如何查找表中的行，查找数据行记录的大概范围。

常见的结果从优到差依次为：

system > const > eq_ref > ref > range > index > ALL

一般来说，需要保证起码达到range级别，最好达到ref；其他的不常见的结果值请查看官方说明文档，这里只列举常见的。

类型	说明
system	该表只有一行，这里是const类型的特例。
const	表示该表最多有一个匹配的行，通过索引一次就找到了；如果将主键放在WHERE列表中，MySQL可以将该查询转为一个常量。
eq_ref	使用唯一性索引，每一个索引值，表中只有一条数据与之匹配。
ref	相比eq_ref不使用唯一索引，而是使用普通索引或唯一性索引的部分前缀，索引要和某个值相比较，可能找到多个符合条件的行。
range	一般出现在范围性查询，比如大于小于等操作中。
index	表示全索引扫描。
ALL	表示全表扫描，性能最差。

7、key_len列

表示索引的长度。

计算规则如下：

类型	长度
char(n)	n字节长度
varchar(n)	如果是utf-8，则长度3n+2字节，两个字节存储字符串长度
tinyint	1字节
smallint	2字节
int	4字节
bigint	8字节
date	3字节
timestamp	4字节
datetime	8字节

另外：如果字段允许NULL，需要增加1字节记录是否为NULL。

8、ref列

表示查询条件是什么类型，比如查询条件为常量时就是const

9、filtered列

表示过滤比例，经过索引过滤后剩下多少满足；最大值为100，这意味着未对行进行筛选，将进行全表扫描；

10、Extra列

这列展示额外信息。

类型	说明
Using index	使用覆盖索引，表示使用索引树就可以查询到所有的结果，不需要回表查询。
Using where	使用where语句处理结果，并且查询的列未被索引覆盖。
Using index condition	查询的列不被索引覆盖，where条件中是一个前导列的范围。
Using temporay	表示使用了临时表。
Select tables optimized away	使用了聚合函数，比如max、min来访问存在索引的字段。

注意：

① 如果在查询结果集中既有唯一索引，也有二级索引，这时会使用二级索引；辅助索引占用磁盘链比较少。

② 如果SQL中使用了不等于的时候，大多数情况下无法使用索引，会进行全表扫描。

③ SQL中使用了IS NULL、IS NOT NULL大多数情况无法使用索引，会进行全表扫描。

④ SQL中使用LIKE模糊查询时，写法如%abc...无法使用索引，会进行全表扫描；百分号在后时会使用索引，如abc%；可以尝试使用覆盖索引，使被查询的列被索引覆盖。

⑤ 减少使用OR或IN使用频率，多数情况下无法使用索引。