mysql explain 执行计划

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