【MySQL优化(七)】MySQL Explain详解

满满和米兜

已于 2022-11-14 12:34:19 修改

阅读量391

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库开发数据库架构数据库

于 2022-03-21 23:10:15 首次发布

本文链接：https://blog.csdn.net/haolinbird/article/details/123644005

版权

MySQL 专栏收录该内容

11 篇文章 4 订阅

订阅专栏

序

Explain为MySQL自带的查询计划分析器，通过 Explain 可以分析一条SQL语句的执行过程，详细了解其各字段的含义有助于我们优化SQL语句。

注意：如果from中包含子查询，仍会执行该子查询，将结果放入临时表中

Explain字段详解

示例：
在这里插入图片描述

id

ID列编号是select的序号, 每个select对应一个ID。
通过ID列可以看出SQL执行的顺序，排序规则为数值大的优先执行，数值相同的，从上往下执行，ID为NULL最后执行。
若同时存在ID不同和ID相同的情况，则先按照排序值从大到小，相同ID的时候则从上往下执行。例如：ID为11123，则先执行3->2 再从上往下执行111。

select_type

查询类型，表示对应行是简单查询还是复杂查询，包含以下类型：

类型	说明
SIMPLE	简单查询，不包含子查询和union
PRIMARY	复查查询中最外层的select
UNION	在 union 中的第二个和随后的 select
DEPENDENT UNION	跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
UNION RESULT	从union表获取结果的select
SUBQUERY	在select或者where列表中包含子查询（不在from子句中）
DEPENDENT SUBQUERY	subquery的子查询要受到外部表查询的影响
DERIVED	from子句中出现的子查询，也叫做派生类。MySQL会将结果存放在一个临时表。
UNCACHEABLE SUBQUERY	表示使用子查询的结果不能被缓存
UNCACHEABLE UNION	表示union的查询结果不能被缓存：sql语句未验证

table

表示当前行访问的表

如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
当有DERIVED时，derivenN 表示当前查询依赖id=N的查询，需要先执行id=N的查询。
当有UNION时，UNION RESULT 的 table 列的值为，1和2表示参与union的select 行id。

type

表示访问或关联类型，指MySQL决定如何查找表中的数据行。
从最优到最差依次为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 。
基本要求为至少达到range级，以下依次解释每个级别

NULL
表示在优化阶段分析语句即能拿到结果，执行阶段不用再访问表或者索引
system
system是const的特例，表里只有一条元组匹配时为system，平时不会出现。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
const
MySQL能对查询的某部分进行优化并将其转化成一个常量，最终这个表至多有一个匹配行。
eq_ref
primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
ref
相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
ref_or_null
对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式
index_merge
在查询过程中需要多个索引组合使用
unique_subquery
该连接类型类似与index_subquery，使用的是唯一索引
index_subquery
利用索引来关联子查询，不再扫描全表
range
表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描。
范围扫描通常出现在 in()，between，>，<，>=，IS NULL，BETWEEN，LIKE，or IN() 等操作中。使用一个索引来检索给定范围的行。
index
扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
ALL
即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

possible_keys

显示查询可能使用哪些索引来查找，但不一定被查询实际使用。
可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。

key

显示MySQL实际采用哪个索引来优化对该表的访问，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。
如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

key_len

MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。
key_len计算规则如下：

字符串

char(n)：如果是UTF-8, 3n 字节长度
varchar(n)：如果是UTF-8, 长度是3n + 2 字节，加的2字节用来存储字符串长度。

数值类型

tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节

时间类型

date：3字节
timestamp：4字节
datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL。
注意：索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：userinfo.id）

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好，注意这个不是结果集里的行数。

Extra

这一列展示的是额外信息。常见的重要值如下：

类型	说明
Using Index	表示当前的查询是使用覆盖索引，如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
Using where	使用 where 语句来处理结果，查询的列未被索引覆盖
Using index condition	查询的列不完全被索引覆盖，where条件中是一个前导列的范围
Using temporary	MySQL需要创建一张临时表来处理查询，完成后将临时表删除。出现这种情况一般是要进行优化的，首先是想到用索引来优化
Using filesort	说明MySQL无法利用索引进行排序，只能利用排序算法进行排序，将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的
using join buffer	使用连接缓存
impossible where	where语句的结果总是false