MySQL--EXPLAIN关键字
EXPLAIN关键字是什么
EXPLAIN关键字用于查看MySQL执行计划
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而指定MySQL是如何处理你的SQL语句的,分析你的查询语句或是表结构的性能瓶颈
EXPLAIN关键字能做什么
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
EXPLAIN关键字如何使用
EXPLAIN + SQL语句
eg:
EXPLAIN SELECT * FROM test
执行结果如下:
结果所展示的字段又分别是什么含义呢?
执行结果各字段解释
id — 表的读取顺序
SELECT 查询的序列号,包含一组数字,表示查询中执行SELECT子句或操作表的顺序
存在有三种情况:
1.id相同,执行顺序由上至下
2.id不同,如果是子查询,id的序列号会递增,id值越大优先级越高,越先被执行
如图,当id不同时,id值越大优先级越高,则执行顺序为 3 --> 2 --> 1
3.相同不同,同时存在
(这个例句我用MySQL5.7执行结果是ID相同,尚硅谷课程阳哥用的是5.5,故直接截取视频图片记录)
id如果相同,可以认为是一组,从上往下顺序执行;
在所有组中,id值越大,优先级越高,越先执行
select_type — 数据读取操作的操作类型
- SIMPLE: 简单的 SELECT 查询,查询中不包含子查询或者 UNIOIN
- PRIMARY: 查询中若包含任何复杂的子部分,最外层查询被标记为 PRIMARY
- SUBQUERY: 在 SELECT 或 WHERE 列表包含了子查询
- DERIVED: 在 FROM列 表中包含的子查询被标记为 DERIVED(衍生),MySQL 会递归执行这些子查询,把结果放在临时表里
- UNION: 若第二个 SELECT 出现在 UNION 之后,则被标记为 UNION;若 UNION包含在 FROM 子查询中,外层 SELECT 将被标记为DERIVED
- UNION RESULT: 从 UNION表获取结果的 SELECT
table
显示这一行的数据是关于哪张表的
type — 访问类型
type显示的访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > range > index > ALL
常用的只要记住:
system > const > eq_ref > ref > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref
- system: 表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个可以忽略
- const: 表示通过索引一次就找到了,const用于比较 PRIMARY KEY 或者 UNIQUE 索引。因为只匹配一行数据,所以很快
- eq_ref: 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- ref: 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
- range: 只检索给定范围的行,使用一个索引来选择行。KEY 列 显示使用了哪个索引,一般就是在你的 WHERE 语句中出现了 BETWEEN、< 、>、IN 等的查询,这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语零一点,不用扫描全部索引
- index: Full Index Scan,index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快,因为索引文件通常比数据文件小。(也就是说虽然 ALL 和 index 都是读全表,但 index 是从索引中读取的,而 all 是从硬盘中读的)
- ALL: Full Table Scan,将遍历全表以找到匹配的行
一般来说,得保证查询至少达到range级别,最好能达到ref
possible_keys — 可能用到的索引
显示可能应用在这张表中的索引,一个或多个。
查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
key — 实际使用的索引
表示实际使用到的索引。如果为 NULL,则没有使用索引。
查询中若使用了覆盖索引,则该索引仅出现在 key 列表中
key_len — 索引中使用的字节数
表示索引中使用的字节数,可通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越短越好。
key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表定义计算而得,不是通过表内检索出的
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
rows
表示根据表统计信息及索引选用情况,大致估算出扎到所需的记录所需要读取的行数
filtered
表示返回结果的行数占需读取行数的百分比
Extra
表示包含不适合在其他列中显示但十分重要的额外信息
有以下类型:
-
Using filesort: 说明 MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引进行读取。MySQL 中无法利用索引完成的排序操作称为:“文件排序”
-
Using temporary: 使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 ORDER BY 和分组查询 GROUP BY
-
using index: 表示相应的 SELECT 操作中使用了覆盖索引(Covering Index),避免了访问表的数据行,效果不错!
如果同时出现 using where,表明索引被用来执行索引键值的查找; 如果没有同时出现 using where,表明索引用来读取数据而非执行查找动作 覆盖索引(Covering Index),一说为索引覆盖。 *理解方式一:就是 SELECT 的数据列只用从索引中就能够取得,不必读取数据行,MySQL 可以利用索引返回 SELECT 列表中的字段, 而不必根据索引再次读取数据文件,换句话说查询列要被所见的索引覆盖 理解方式二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。 比较索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引 注意:如果要使用覆盖索引,一定要注意 SELECT 列表中只取出需要的列,不可 SELECT * FOM, 因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降
-
using where: 表明使用了 WHERE 过滤
-
using join buffer: 使用了链接缓存(需要调整缓存空间进行优化)
-
impossible where: where子句的值总是 false,不能用来获取任何元组
-
select tables optimized away: 在没有 GROUP BY 子句的情况下,基于索引优化 MIN/MAX操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
-
distinct: 优化 DISTINCT 操作,再找到第一匹配的元组后即停止找相同值的操作
-
Using index condition: 当使用范围查询导致索引失效时出现
资料来源于尚硅谷课程,记录学习的知识,后期有自己的理解与见解后再进行补充