Mysql之explain详解(超级全面)
概念
使用 explain 关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。
分析你的查询语句 或是 表结构瓶颈。
explain能干嘛?
- 获取表是否顺序读取
- 知道数据读取操作的操作类型
- 知道哪些索引可以使用
- 知道哪些索引实际被使用
- 表之间的引用
- 每张表有多少行被优化器查询
如何使用
EXPLAIN + SQL语句
例子:
EXPLAIN SELECT
*
FROM
test t
LEFT JOIN test1 t1 ON t.uuid = t1.uuid
Left join test2 t2 on t.uuid = t2.uuid
WHERE
t.uuid = "uuid1";
一般会输出如下东西:
输出字段解释
id(表的读取顺序)
select 查询序号,包含一组数字,表示查询中执行select子句或操作表的顺序
一般情况有三种情况
- id相同,执行顺序由上至下
- id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越被优先执行
- id有相同也有不同,同时存在
id相同,执行顺序由上至下
表结构
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
KEY `nk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t2` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`new_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
UNIQUE KEY `uk_new_id` (`new_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t3` (
`id` bigint NOT NULL,
`name` varchar(255) DEFAULT NULL,
`uuid` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划
EXPLAIN SELECT
*
FROM
t1
LEFT JOIN t2 ON t1.uuid = t2.uuid
LEFT JOIN t3 ON t2.uuid = t3.uuid
WHERE
t1.uuid = "uuid1";
输出如下:
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越被优先执行
表结构:
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
KEY `nk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t2` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`new_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
UNIQUE KEY `uk_new_id` (`new_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t3` (
`id` bigint NOT NULL,
`name` varchar(255) DEFAULT NULL,
`uuid` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划:
EXPLAIN SELECT
*
FROM
t1
WHERE
id = (
SELECT
id
FROM
t2
WHERE
id = (SELECT id FROM t3 WHERE id = 1)
);
输出结果
id有相同也有不同,同时存在
这个时候是,id相同的为一组,从上往下顺序执行;在所有组中,id值越大,越优先执行
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
KEY `nk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t2` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`new_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
UNIQUE KEY `uk_new_id` (`new_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `t3` (
`id` bigint NOT NULL,
`name` varchar(255) DEFAULT NULL,
`uuid` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
EXPLAIN SELECT
t2.*
FROM
(
SELECT
*
FROM
t1
WHERE
`name` IS NOT NULL
) s1,
t2
WHERE
s1.id = (SELECT id FROM t3 WHERE id = 1);
输出的结果为
select_type(数据读取操作的操作类型)
查询类型,一般有下面几种类型
1. SIMPLE: 简单的select查询,查询中不包含子查询或者union
2. PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记
3. SUBQUERY:在select或者where列表中包含子查询
4. DERIVED:在from列表中包含子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里
5. UNION:若第二个select出现在union之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层的select将被标记为DERIVED
6. UNION RESULT:从UNION表获取的SELECT
什么时候会出现 UNION RESULT ?
两个表union就会出现?
例子:
EXPLAIN
SELECT id, uuid, `name`, age FROM t1
UNION
SELECT id, uuid, `name`, age FROM t2;
输出结果:
table
显示这一行的数据是关于那张表的
type (很重要)
一般有以下值
system
表只有一行记录(等于系统表),并且要是MyISAM引擎,这是const类型的特例,平时不会出现,这个也可以忽略不计
const
表示通过索引一次就找到了,const用于比较primary key 或者 union 索引。因为只匹配一行数据,所以很快,如将主键至于where列表中,MySQL就能将查询转换为一个常量。
一般情况要有以下条件才会发生
- 命中主键(primary key)或者唯一(unique)索引;
- 被连接的部分是一个常量(const)值;
以下例子就会发生
表结构
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
KEY `nk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划
EXPLAIN SELECT id, uuid, `name`, age FROM t1 where id = 1;
输出结果
eq_ref 刚好匹配一条记录
唯一性索引扫描,对于每一个索引键,表只有一条记录与之匹配。常见于主键和唯一索引扫描。
刚好只有一条
eq_ref发生条件,对于前表的扫描,后表只有一行被扫描。
- join 查询
- 命中主键 和 唯一性非空索引
- 等值连接
例子:
// t1
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
// t2表有一个new_id非空索引
CREATE TABLE `t2` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`new_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
UNIQUE KEY `uk_new_id` (`new_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划
EXPLAIN SELECT
*
FROM
t1
JOIN t2 ON t1.id = t2.new_id;
输出结果:
ref 匹配单个值所有的行
非唯一性索引扫描,返回匹配某个单独值所有行
本质上也是一种索引访问,他返回所有匹配某个匹配单独值的行,而然它可能会找到索格符合的条件,所以他应该属于查找和扫描的混合体
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
KEY `nk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划
EXPLAIN SELECT
*
FROM
t1
WHERE `name` = "xiaohua";
输出结果
fulltext
全文索引
ref_or_null
与ref相似,但包括NULL
index_merge
表示出现了索引合并优化(包括交集,并集以及交集之间的并集),但不包括跨表和全文索引。这个比较复杂,目前的理解是合并单表的范围索引扫描(如果成本估算比普通的range要更优的话)
unique_subquery
在in子查询中,就是value in (select…)把形如select unique_key_column的子查询替换。PS:所以不一定in子句中使用子查询就是低效的!
index_subquery
同上,但把形如”select non_unique_key_column“的子查询替换
range
只检索给定范围的行,使用一个索引来进行选择。key列显示使用了那个索引
一般就是在你的where语句中出现了between、>、< 、in等的查询,这种范围扫描比扫描全表要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
例子:
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
KEY `nk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划
EXPLAIN SELECT
*
FROM
t1
WHERE id > 1;
输出结果
index
FULL index scan ,扫描全部索引,这通常比ALL快,因为索引文件通常比数据文件小,index和ALL都是读全表,但是index是从索引中读取,而ALL使用硬盘中读取。
CREATE TABLE `t1` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
KEY `nk_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划
EXPLAIN SELECT
`name`
FROM
t1;
输出结果
ALL
扫描全表,最坏的结果
从最好到最坏依次是:
system ——> const ——> eq_ref ——> ref ——> fulltext ——> ref_or_null ——> index_merge ——> unique_subquery ——> index_subquery ——> range ——> index ——> ALL
一般来说
SQL优化至少要达到range级别,最好是达到ref级别
possible_keys(可能用到的索引)和 key(实际使用的索引)
possible_keys
显示可能应用在这张表中的索引,一个或者多个。
查询设计及到的字段若存在索引,则该索引将被列出,但不一定被查询使用。
key
实际使用的索引。如果为NULL,则没有使用索引
查询中弱使用可覆盖索引,则该索引仅出现在key列表中
例子:
表结构
CREATE TABLE `t2` (
`id` bigint NOT NULL,
`uuid` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`new_id` bigint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uuid` (`uuid`) USING BTREE,
UNIQUE KEY `uk_new_id` (`new_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
执行计划
EXPLAIN SELECT
t2.uuid
FROM
t2;
查询的字段是索引,key列也会出现被查询的值,因为这样也能扫描索引。
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越长越好。
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
ref(显示哪些索引被实际使用)
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或者常量被用于查找索引列上的值。
在联合索引时,如果索引被充分利用就是一个常量。
row(每张表有多少行被优化器查询)
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需读取的行数
一般来说,建立索引后扫的行数会大大降低
Extra
using filesort(重要)
糟糕的
这个值说明mysql会对数据使用一个外部的索引排序,而不是按照表内索引顺序的进行读取。
MySQL中无法利用索引完成的排序成为“文件排序”。
using temporary(重要)
糟糕的,比using filesort更坏
使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表,常见于排序Order by 和分组查询 group by。
using index(重要)
好的
表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现 using where,表名索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
注意:
覆盖索引,又称为索引覆盖
理解一:
查询select的数据列只用从索引中就能取得,不必读取数据行,MySQL可以利用索引返回select中的字段,而不必要根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解二:
索引是高效找到行的一个方法,但是一般数据能使用索引找到一个列的数据,因此不必读取整个行,毕竟索引叶子节点储存了他们索引的数据;当能通过索引就可以获得想要的数据,那就不需要读取行了,一个索引包含了(或者覆盖了)满足查询结果的数据就叫索引覆盖。
但是需要注意的是,如果要使用索引覆盖,一定要select列出只取出所需要的列,不能 select *
但是如果将所有字段一起做索引会导致索引文件过大,查询性能下降。
using where (表示使用的where过滤)
using join buffer(表示使用了链接缓存)
impossible where
where 子句的值总是false,不能用来获取任何元素
select tables optimized away(了解)
在没有group by子句的情况下,基于索引优化min/max操作或者对于MyISAM储存优化COUNT(*)操作,不必等到执行阶段再计算,查询执行计划生成的阶段即完成优化。
distinct(了解)
优化distinct操作,在找到第一匹配的元组后立即停止找同样值的动作。
本文参考链接
https://blog.csdn.net/qq_41583016/article/details/112555323