目录
一, 执行计划概念
二, Explain 用法
三, Explain 属性介绍
3.1 id 属性
3.2 select_type 属性
3.3 table 属性
3.4 type 属性
3.5 possible_keys 属性
3.6 key 属性
3.7 key_len 属性
3.8 ref 属性
3.9 rows 属性
3.10 Extra 属性
四, 案例学习
继上一篇博客《MySQL 的索引知识学习笔记》之后, 我再记录一篇 MySQL 执行计划方面的博客, 本博客是我在学习尚硅谷的学习教程后, 做的笔记, 当然我不是为了所谓宣传, 仅仅是学习记录的笔记. 本来可以不分享出来, 不过, 分享出来的笔记不仅可以给网上的学习者参考学习, 同时写在 csdn 比较方便, 可以支持图片上传, 也方便自己以后查找复习, 本博客 CSDN 版: 链接
附录: 我创建的数据库方面的专栏
SQL 调优方面的专栏
MySQL 知识方面的专栏
一, 执行计划概念
执行计划(Explain):explain 显示了 MySQL 如何使用索引来处理 select 语句以及连接表, 使用 Explain 关键字可以模拟 MySQL 优化器执行 SQL 查询语句, 从而知道 MySQL 是如何处理 SQL 语句的. 所以执行计划常用于 SQL 调优
二, Explain 用法
Explain 的用法:
Explain + SQL 语句
- MySQL> explain select * from sys_user;
- MySQL> use jeeplatform;
- Database changed
- MySQL> explain select * from sys_user;
- +----+-------------+----------+------+---------------+------+---------+------+--
- ----+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | r
- ows | Extra |
- +----+-------------+----------+------+---------------+------+---------+------+--
- ----+-------+
- | 1 | SIMPLE | sys_user | ALL | NULL | NULL | NULL | NULL |
- 4 | |
- +----+-------------+----------+------+---------------+------+---------+------+--
- ----+-------+
- 1 row in set (0.00 sec)
- MySQL>
Explain 的主要属性有 id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra, 如何对这些属性进行比较详细的介绍
三, Explain 属性介绍
执行计划 (Explain) 包括如下属性:
比较重要的属性有 id,type,key,rows,Extra
3.1 id 属性
id 属性其实就是显示了 SQL 的执行顺序, 下面分类解释
id 相同的情况
id 相同的情况, 执行顺序由上到下
本例子来自尚硅谷教程介绍, 从执行计划可以看出就是 t1>t3>t2, 因为 SQL 有 t1.other_column=''的条件, 所以先从 t1 开始执行, 然后是 t3, 接着是 t2
id 不同的情况
id 不同的情况, 根据 id 优先级来看, 优先级越高越先执行
id 相同不同同时存在
这种是比较特殊的情况, 不过还是按照优化先看, 然后 id 相同的情况, 还是按照从上到下的情况看
3.2 select_type 属性
select_type 就是查询的类型, 用来区别普通查询, 联合查询(union), 子查询等复杂查询, 属性分为:
1,Simple
简单 select 查询, 查询中不包括子查询, 联合查询等
2,PRIMARY
PRIMARY 可以理解为主查询, 当然是在包括子查询等情况下面, 最外面的查询被称之为 PRIMARY 查询
3,SUBQUERY
SUBQUERY 就是子查询, 一般用于 select 后面或者 where 条件后面
4,DERIVED
DERIVED 是衍生查询, 一般用于 from 后面查询返回一个衍生表
5,UNION
UNION 是联合查询, UNION 或者 UNION ALL 关键字后面的查询表查询都被标记为联合查询
6,UNION RESULT
union result 就是从 union 获取的查询结果
3.3 table 属性
table 属性, 很显然这个属性就是显示查询数据是关于哪张表的
3.4 type 属性
type 属性表示 MySQL 找到数据行采取的方式, 也称之为访问方式, 显示了查询使用了何种类型
访问类型排序, 从最好到最差的依次是(常用的):
system>const>eq_ref>ref>range>index>All
来自尚硅谷老师的归纳:
然后介绍一下这些级别
ststem
表只有一条记录的情况, 显示 type 为 system, 这种一般在系统表才会出现, 是 const 的一种特例
const
const 常量, 一般用于 where 条件后面, 用于比较主键索引或者唯一索引, 索引一次就可以找到一条记录, 一般返回一个常量
eq_ref
唯一性索引扫描, 一般是主键 Primary key 或者唯一索引(union index), 只返回一条记录与之匹配
ref
非唯一性索引扫描, 也是一种索引访问, 不过符合的记录有多条
range
索引返回扫描, 一般来说在 where 条件出现了 between 或者<,>,in 等符合或者关键字就是索引访问扫描, 不过建了组合索引, 有些情况是会导致索引失效的
index
index 也就是索引全扫描(full index scan),index 和 all 的区别是: index 扫描所有索引, all 扫描所有数据, 在 MySQL 中, 索引文件是比文件小, 所以 index 扫描性能是比 all 好的
All
all 也是全表扫描, 不建索引的情况, 经常出现全表扫描(full table scan)
备注: 要保证 sql 查询, 一般要达到 range, 或者 ref 级别
3.5 possible_keys 属性
用于显示理论上扫描可能用到的索引
3.6 key 属性
用于显示实践上用到的索引, 返回 null 表示没用到索引
3.7 key_len 属性
表示索引使用的字节数, key_len 显示的是索引字段最大的可能长度, 并非实际使用的长度
3.8 ref 属性
ref 属性用于显示哪些常用或者列被用于查找索引, 前提条件是走索引的
3.9 rows 属性
rows 数据是根据统计信息及索引选用情况, 大致估算出的记录数
3.10 Extra 属性
Extra 可以理解为拓展或者额外的属性, 包含了不在其它属性的信息, 但是十分重要的额外信息, 常用于分析定位性能问题, 比较重要的属性有 Using filesort,using temporary,using index
1,Using filesort
Extra 信息显示这个信息说明无法利用索引完成排序 order by, 而选择了文件排序, 这种情况是比较耗性能的, 所以要看看索引失效的原因
如图来自尚硅谷视频的例子, 这里就出现了 Extra 信息为 using filesort, 原因是 where 条件里用了 col1,order by 后面只用了 col3, 而复合索引是 3 个列都建的, 所以导致不能用到索引排序, 用了文件排序代替, 解决方法是在 order by 加上 col2
备注: 索引的两大作用: 排序和查询
2,using temporary
出现这个信息表示, 使用了临时表保存中间结果, 出现原因是在对查询结果排序时使用了临时表, 常见与 order by 和 group by 一起用的情况
上面例子, 就是 using temporary 的情况, 在 group by 后面加上 col2 列, 使索引能用得上
3,Using index
出现 using index 说明出现覆盖索引(Covering index), 是效率不错的! 如果 using where 也一起出现, 说明索引被用来查询, 只有一个说明只用来读取数据
覆盖索引(Covering index), 一说索引覆盖, 意思是查询时, 数据只要从索引获取就可以(从叶子节点获取), 不需要读取数据行
注意: 查询时, 要使用索引覆盖, 就要 select 的列表只取需要的列就可以, 不可以 select * , 如果将所有的字段一起做索引会导致索引文件过大, 影响查询性能
Using where
表示使用了 where 过滤条件
Using join buffer
表示使用了连接缓存, 也就是内外连接
Impossible where
where 子句的查询结果总是 false, 不能用来获取任何元组
Select table optimized away
Distinct
使用了 distinct 操作, 在查到对一条记录后, 就不查找相同的记录
四, 案例学习
复杂点的案例
来自视频教程的案例
尚硅谷教师的归纳
来源: https://www.cnblogs.com/mzq123/p/11523215.html