Explain 关键字
使用 Explain 关键字可以模拟 SQL 优化器的执行计划,有助于分析查询语句的结构和性能平静,便于我们后续做 SQL 优化
测试之前先打开对衍生表的合并,Mysql 5.7 之后默认打开
// 关闭对衍生表的合并
set session optimizer_switch = 'derived_merge=off';
// 打开对衍生表的合并(默认配置)
set session optimizer_switch = 'derived_merge=on';
一、id
查询语句中每出现一个 select 关键字,Mysql 就会为它分配一个唯一的 id,它们的执行顺序遵循如下规则,id 值越大执行的优先级越高,id 值相同则从上往下顺序执行,id 值为 NULL 的之后执行
有一个比较特殊的是 id 为 NULL 的情况union 是要结果进行去重的,内部会创建一个临时表,把查询 1 和查询 2 的结果集都合并到该临时表中,利用唯一键进行去重,这种情况下 id 就为 NULL
二、select_type
表示查询的类型,比较常见的有 SIMPLE、SUBQUERY、PRIMARY、DERIVED
1、SIMPLE
简单查询,不包括子查询或 union (all)
2、SUBQUERY
包含在 select 子句中的子查询(不在 from 子句中的子查询)
3、PRIMARY
对于包含 union 或子查询的大查询来说,它是由几个小查询组成的,其中最左边的那个查询的 select_type 就是 PRIMARYunionunion all
4、DERIVED
包含在 from 子句中的子查询,Mysql 会将结果集存放在一张临时表中,也称为衍生表(派生表)
三、table
这一列表示 explain 的一行正在访问哪个表
当 from 子句中有子查询时,table 列是
当有 union 时,UNION RESULT 的 table 列的值为
四、type
type 显示的是访问类型,是一个很重要的指标,结果值从好到差依次是: null > system > const > eq_ref > ref > range > index > all,一般来说,至少保证查询达到 range 级别,最好达到 ref 级别
1、NULL
Myql 能够在优化阶段分解查询语句,在执行阶段不需要访问表或者索引
2、system、const
Mysql 使用主键索引或者唯一的二级索引与常数进行比较时 type 会出现 system、const
如果从某一张表中(表中可能有多条记录)只能查询到一条记录,此时出现的是 const,如果查询的表中只有一条记录,那么 type 为 system
system 可以看做是 const 的一个特例,当表中有多条数据通过主键或唯一索引只能找出一条数据时 type 为 const,当表中只能有一条数据,通过主键或唯一索引查找时 type 为 system
为 employee 创建唯一索引,然后使用唯一索引进行等值查询
create unique index idx_age on employee(age);
3、eq_ref
可以这么认为,假设存在 A 表和 B 表,如果 A 表连接 B 表,并且 B 表使用了主键(或唯一索引)作为 on 后面的连接条件,那么对于 A 表中的每一行来说,B 表中最多只有一行满足 JOIN 条件,这种连接查询是相当快的,B 表的 type 为 eq_ref
假设有 employee、department 表,通过下面的例子进行演示
employee 表索引如下(只有主键索引 id)department 表索引如下(存在主键索引 id,唯一索引 idx_name)
通过 department 表的主键 id 或者 B 表的唯一索引作为 on 的连接条件,执行计划显示 department 表的 type 为 eq_ref
通过上面的案例可以得出,employee 表是全表扫描,而 department 是查询效率较高的 eq_ref,如果条件允许的话,最好是数据量小的表在 left join 左边(小表驱动大表)
再看一个如果 department 表不使用 id 或者唯一索引进行连接,B 表的情况又是什么样子呢?
department 表的 type 变成了 ALL,也就是全表扫描了
4、ref
相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀(前缀索引),索引要和某个值相比较,可能会找到多个符合条件的行
把 department 表中的唯一索引删除,为 department 建议一个普通索引
简单查询
连接查询
5、range
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中,使用一个索引(主键索引、唯一索引、普通索引)来检索给定范围的行
主键索引的情况
普通索引
6、index
扫描某个二级索引,这种扫描不会从索引树根节点开始快速查找,而是直接对二级索引的叶子节点遍历和扫描,速度还是比较慢的,这种查询一般为使用覆盖索引,由于二级索引 <= 聚集索引,所以这种通常比 ALL 快一些
7、ALL
即全表扫描,扫描聚集索引的所有叶子节点.通常情况下这需要增加索引来进行优化了
五、possible_key、key
possible_key : 这一列显示可能使用哪些索引来查找,possible_key 列中的值不是越多越好,可能使用的索引越多,Mysql 查询优化器计算查询成本时就要花费更多时间,如果可以的话尽量删除那些用不到的索引
key : 这一列显示 Mysql 实际采用哪个索引来优化对该表的访问
有些时候 possible_keys 显示为 NULL,而 key 有值
先为 name、age 字段创建一个联合索引 idx_name_age
然后再查询 name 字段的值,possible_key 为 NULL,而 key 却用到了索引,产生这种情况的原因是,搜索 name 字段的时候并没有用到索引,在聚集索引和联合索引上都有 name 字段的值,遍历联合索引拿到所有的 name 成本更低,所以 Mysql 查询优化器选择了扫描联合索引的叶子节点获取数据
六、key_len
这一列显示了 Mysql 在索引里使用的字节数通过这个值可以算出具体使用了索引中的哪些列
key_len计算规则如下
- 字符串
char(n) 和 varchar(n),5.0.3 以后版本中, n 均代表字符数,而不是字节数,如果是 utf-8,一个数字或字母占 1 个字节,一个汉字占3个字节
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节,加的 2 字节用来存储字符串长度,因为 varchar 是变长字符串数值类型
- 数值类型
tinyint : 1 字节
smallint : 2 字节
int : 4 字节
bigint : 8 字节
- 时间类型
date : 3 字节
timestamp : 4 字节
datetime : 8 字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时, Mysql 会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引
下面就具体计算一下索引的字节数
表结构如下
索引如下
employee 表中有一个主键索引 id,还有一个联合索引 idx_name_age_gender
编码格式为 utf-8,name 字段为 varchar(20),age 字段为 int 类型,gender 字段为 int 类型
只使用联合索引的 name 字段查询,套用公式 3n + 2 = 3 * 20 + 2 = 62
使用使用联合索引的 name、age 字段,套用公式 62 + 4 = 66
使用使用联合索引的 name 、age、gender 字段,套用公式 66 + 4 = 70
七、ref
当使用索引列等值匹配的条件去执行查询时,ref 列显示的就是与索引列做等值匹配的对象,常见的有 : const(常量)、字段名(例 : summer.name),如果不是等值匹配,则显示为 NULL
八、rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时,执行计划的 rows 列就代表预计需要扫描的行数;如果使用索引来执行查询时,执行计划里的 rows 就代表预计扫描的索引记录行数,注意 rows 只是一个预估值,不是结果集里面的行数
九、Extra
employee 表中索引如下
1、Using where
全表扫描时, Mysql 服务层使用 where 条件进行过滤数据
使用索引访问数据时,where 子句中除了有索引字段,还包含其它字段(例如 name 是联合索引的字段,department_id 没有任何索引)
2、Using index
使用了覆盖索引
3、Using index condition
发生了索引下推,,第一个条件 name = 'xiaomaomao' 可以使用索引,但是由于没有 age 字段,索引 gender 字段也不能使用索引
Mysql 利用第一个字段 name 作为索引在联合索引的叶子节点找到合适的数据 data,然后再利用 gender 字段的值与 data 中的 gender 进行比较,符合的就返回,虽然 gender 字段没有用上索引,但是它起到了过滤的作用
4、Using temporary
用临时表保存中间结果,常用语 group by 操作中,一般这种情况是需要优化的
5、Using filesort
Mysql 有两种方式可以生成有序的结果,通过索引树或者是将符合条件的数据先拿出来之后再进行额外的排序,当 Extra 中出现了 Using filesort 时说明使用了后者,需要注意的是,虽然叫 filesort,但是并不代表就是用了磁盘文件来进行排序,当内存足够时,它会在内存中完成额外的排序,当内存不够时,它才会使用磁盘文件进行排序,当出现了额外的排序,可以通过添加索引来改进性能,用索引来为查询的结果排序