Explain执⾏计划详解
作者：IT王⼩⼆
⼀条查询 SQL 语句为什么会执⾏很慢？执⾏的时候⾛了什么索引？多表查询的时候 SQL ⼤体执⾏流程是怎么样的？Explain 执⾏计划详解给各位客官⼀⼀道来。
⼀、⽰例MySQL安装⽅式和版本
⾸先需要 MySQL 安装的客官看这两篇，⼩⼆演⽰使⽤的是 Docker 的安装⽅式：
⼩⼆安装完的 MySQL 数据库版本为 5.7.36。
⼆、Explain⼯具简介
通常我们在⽣产环境会碰到查询速度⽐较慢的 SQL 语句，那么怎么知道 SQL 语句为什么执⾏慢呢？这个时候就需要⽤到 Explain 执⾏计划来分析我们的语句了。
通过使⽤ Explain 关键字可以模拟优化器执⾏ SQL 查询语句，执⾏会返回执⾏计划的信息，⽽不是执⾏这条SQL，当然如果 from 中包含⼦查询，仍会执⾏该⼦查询，将结果放⼊临时表中 。
mysql下载哪个版本好2022总的来说，通过 Explain 从⽽知道 MySQL 是如何处理你的 SQL 语句的，分析查询语句或是表结构的性能瓶颈。那么通过 Explain 执⾏计划可以知道：
表的读取顺序
数据读取操作的操作类型
哪些索引可能被使⽤
哪些索引实际被使⽤
表之间的引⽤
每张表估计有多少⾏会被执⾏
三、本⽂⽤到的⽰例表
CREATE TABLE`author`(
`id`int(11)NOT NULL,
`name`varchar(45)DEFAULT NULL,
`update_time`datetime DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO`author`(`id`,`name`,`update_time`)VALUES(1,'itwxe','2022-01-12 19:27:18'),(2,'admin','2022-01-12 19:27:18'),(3,'superAdmin','2022-0 1-12 19:27:18');
CREATE TABLE`blog`(
`id`int(11)NOT NULL AUTO_INCREMENT,
`title`varchar(10)DEFAULT NULL,
PRIMARY KEY(`id`),
KEY`idx_title`(`title`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO`blog`(`id`,`title`)VALUES(1,'blog1'),(2,'blog2'),(3,'blog3');
CREATE TABLE`author_blog`(
`id`int(11)NOT NULL,
`author_id`int(11)NOT NULL,
`blog_id`int(11)NOT NULL,
`remark`varchar(255)DEFAULT NULL,
PRIMARY KEY(`id`),
KEY`idx_author_blog_id`(`author_id`,`blog_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO`author_blog`(`id`,`author_id`,`blog_id`)VALUES(1,1,1),(2,2,1),(3,1,2);
四、Explain的变种
Explain 的语法⾮常简单，只需要在查询语句前⾯加⼀个 EXPLAIN 关键字即可。
explain select*from author where id =1;
如果你发现你的版本少了 partitions 或者 filtered 列字段信息的话，那是因为在 MySQL5.7 的某个⼩版本之前的版本是没有这两个字段的。rows * filtered/100 可以估算出将要和 explain 中前⼀个表进⾏连接的⾏数(前⼀个表指 explain 中的 id 值⽐当前表 id 值⼩的表)。
那么低版本想要看到这两个字段也是有办法的，这就是⼩⼆说的 Explain 的两个变种。
explain extended：会在低版本 MySQL explain 的基础上额外提供⼀些查询优化的信息 filtered 列。
explain partitions：会在低版本 MySQL explain 的基础上额外提供⼀个 partitions 字段，如果查询是基于分区表的话，会显⽰查询将访问的分区。
在 explain 紧随其后执⾏的 show warnings 命令可以得到优化之后的查询语句，从⽽看到优化器优化了什么东西，当然优化后的 SQL 并不⼀定符合 SQL的语法，但是 MySQL 可以识别并可以执⾏。
五、Explain中的列
1. id
id 列表⽰的编号是 select 的序列号，有⼏个 select 就有⼏个 id，并且 id 的顺序是按 select 出现的顺序增长的，id 列越⼤执⾏优先级越⾼，id 相同则从上往下执⾏，id 为 NULL 最后执⾏。
explain select*from author where id =(select author_id from author_blog where id =2);
可以看到⼦查询 id 为 2，所以先执⾏⼦查询再执⾏外层查询。
2. select_type
select_type 列表⽰对应⾏查询的类型。
1）SIMPLE：简单查询，不包含⼦查询和 union。
explain select*from author where id =1;
2）PRIMARY：复杂查询中最外层的 select。
3）SUBQUERY：包含在 select 中的⼦查询(不在 from ⼦句中)。
4）DERIVED：包含在 from ⼦句中的⼦查询。MySQL 会将结果存放在⼀个临时表中，也称为派⽣表(derived的英⽂含义)。
⽤这个例⼦来了解 PRIMARY、SUBQUERY 和 DERIVED 类型。
# 关闭 MySQL5.7 新特性对衍⽣表的合并优化
set session optimizer_switch ='derived_merge=off';
explain select(select1from blog where id =1)from(select1from author where id =1) tmp;
别问⼩⼆为啥写这种语句，⼀切的⼀切都是为了演⽰效果啊~~~
开启 MySQL5.7 新特性默认对衍⽣表的合并优化，就可以看到不⼀样的效果了。
set session optimizer_switch ='derived_merge=on';
5）UNION：在 union 中的第⼆个和随后的 select。
6）UNION RESULT：从 union 临时表检索结果的 select。
⽤这个例⼦来了解 UNION、UNION RESULT 类型。
explain select id from author where id =1union select id from blog where id =2;
3. table
table 列表⽰ explain 的⼀⾏正在访问哪个表。
当 from ⼦句中有⼦查询时，table 列是 <derivenN> 格式，表⽰当前查询依赖 id=N 的查询，于是先执⾏ id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表⽰参与 union 的 select ⾏ id。
4. type
type 列表⽰关联类型或访问类型，即 MySQL 决定如何查表中的⾏，查数据⾏记录的⼤概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL ，⼀般来说，得保证查询达到 range 级别，最好达到ref 级别。
除了上⾯⼏个拥有优先级的值之外，NULL 表⽰ MySQL 能够在优化阶段分解查询语句，在执⾏阶段⽤不着再访问表或索引。例如：在索引列中选取最⼩值，可以单独查索引来完成，不需要在执⾏时访问表。
explain select min(id)from author;
1）const、system：MySQL 能对查询的某部分进⾏优化并将其转化成⼀个常量(可以看 show warnings 的结果)。⽤于 primary key 或unique key 的所有列与常数⽐较时，所以表最多有⼀个匹配⾏，读取1次，速度⽐较快。system是const的特例，表⾥只有⼀条数据匹配时为system
explain select*from(select*from author where id =1) tmp;
show warnings;
2）eq_ref：primary key 或 unique key 索引的所有部分被连接使⽤ ，最多只会返回⼀条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
explain select*from author_blog left join blog on author_blog.blog_id = blog.id;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。