mysql explain执行计划

一. 什么是执行计划

执行计划可以模拟优化器执行sql, 分析查询语句的性能瓶颈

# 在select 前面加上explain就可以查看执行计划
explain SELECT
	u.*,
	r.`name` 
FROM
	sys_user u
	JOIN user_role us ON us.user_id = u.id
	JOIN role r ON r.id = us.role_id
	where u.id < (select avg(id) from sys_user)

二. id(不重要)

id是执行sql的顺序, 越大越早执行, id相同, 从上往下执行, id为Null最后执行

三. select type(不重要)

select type表示是简单还是复杂的查询。

1. simple：简单查询。查询不包含子查询和union
2. primary：复杂查询中最外层的select
3. subquery：包含在select中的子查询（不在from子句中）
4. derived：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表。
5. union：在union关键字随后的selelct。

四. table

使用了哪个表, 有时不是真实的表名, 可能是临时表

五. type(重要)

常用的类型有： ALL、index、range、index_merge 、ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

一般需要达到range及以上

1. ALL 全表扫描
2. index 覆盖索引,扫描了全部的索引 并且select 查询的字段, 在索引里面就可以找到
3. range 范围查找 >, <, in, between
4. index_merge 使用了索引合并优化 就是where条件or的字段都是索引
5. ref 非唯一索引扫描
6. eq_ref 唯一索引扫描
7. const 常量 where条件指定了写死的值, 并且走了唯一索引或主键
8. system 表里只有一条记录, 并且是const级别, 是const的特例
9. NULL 执行阶段不用再访问表

六. possible_keys(不重要)

查询可能用到的列, 但并不一定用到

七. key(重要)

实际真正用到的索引, 如果为NULL, 表示没有用到索引

八. key_len(或许重要)

使用的索引长度, 可以根据长度算出组合索引用了哪些列

规则如下:

1. 允许为空 长度1

2. 字符串 UTF8 每个字符长度为3 UTF8MB4 每个字符4字节

   • char

     UTF8MB4编码的char(20) 允许为NULL 长度位: 20 * 4 + 1 = 81

   • varchar

     字符串保存长度再加2

     UTF8MB4编码的varchar(20), 允许为NULL 长度位: 20 * 4 + 2 + 1 = 83

3. 数字

   • tinyint: 1字节
   • smallint: 2字节
   • int: 4字节
   • bigint: 8字节

4. 时间

   • date: 3字节
   • timestamp: 4字节
   • datetime: 8字节

索引最大长度是768字节，当字符串过长时, 会截取前半段

九. ref(不重要)

一般为表关联的列

十. rows(不重要)

估算的结果集行数

十一. Extra(比较重要)

• Using index：使用覆盖索引
• Using index condition：查询的列不完全被索引覆盖 (索引下推)
• Using where：在查找使用索引的情况下, 需要回表去查询所需的数据
• using index & using where: 查找使用了索引, 并且需要的数据都在索引列中能找到, 所以不需要回表查询数据
• Using temporary：mysql需要创建一张临时表来处理查询 出现这种情况一般要进行优化, 常见于排序或分组查询
• Using filesort: 无法使用索引排序, 将使用外部排序, 数据较小时从内存排序, 否则需要在磁盘完成排序。
• select tables optimized away：使用聚合函数来处理索引的某个字段
• Using index for group-by: 使用group by或distinct时, 分组的字段在索引中
• Using join buffer: 表连接没有使用索引, 使用连接缓冲区来存储中间结果
• Distinct: 优化了distinct