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子句或操作表的顺序
一般情况有三种情况

  1. id相同,执行顺序由上至下
  2. id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越被优先执行
  3. 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就能将查询转换为一个常量。
一般情况要有以下条件才会发生

  1. 命中主键(primary key)或者唯一(unique)索引;
  2. 被连接的部分是一个常量(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发生条件,对于前表的扫描,后表只有一行被扫描。

  1. join 查询
  2. 命中主键 和 唯一性非空索引
  3. 等值连接
    例子:
// 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

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值