mysql性能优化工具explain介绍

Explain工具介绍

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

Explain分析示例

参考官方文档：MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

示例表：

DROP TABLE IF EXISTS `actor`;
CREATE TABLE IF NOT EXISTS `actor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES
(1, 'a', '2022-03-08 16:11:00'),
(2, 'b', '2022-03-08 16:11:12'),
(3, 'c', '2022-03-08 16:12:03');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
  `id` int(11) NOT NULL,
  `film_id` int(11) NOT NULL,
  `actor_id` int(11) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) 
VALUES (1,1,1),(2,1,2),(3,2,1);

在查询中的每个表会输出一行，如果有两个表通过 join 连接查询，那么会输出两行

explain 两个变种

1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

如果是关联查询的话查询表和关联表关联的行数的大概的百分比

explain extended select * from film where id = 1;

show warnings;

2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

explain中的列

1. id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

id列越大执行优先级越高，id列不一定唯一，id相同则从上往下执行，id为NULL最后执行。

2. select_type列

select_type 表示对应行是简单还是复杂的查询。

1）simple：简单查询。查询不包含子查询和union

explain select * from film where id = 2;

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 (select 1 from actor where id = 1) 
from (select * from film where id = 1) der;

• select 1 from actor where id = 1 当我们不关心数据的值只关心语句返回多少行是可以使用select 1的方式，1可以替换为任意字符串、数字、字符。查询结果有多少行就会输出多少行1。这样写数据库引擎就不会去检索数据表里一条条具体的记录和每条记录中每个字段的值然后再放到内存中
• select 1 from actor where id = 1在select后面，因此是一个子查询(SUBQUERY)

• select * from film where id = 1在from后面是一个衍生查询，因为他是需要通过知执行语句才能确定结果(DERIVED)
• 最外层的查询是Primary，对应的table是<derived3>。这个<derived3>就是通过from后面的查询语句查询出的衍生表，3就是衍生查询的执行计划id

set session optimizer_switch='derived_merge=on'; #还原默认配置

5）union：在 union 中的第二个和随后的 select

explain select 1 union all select 1;

3. table列

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。

当有 union 时，UNION RESULT 的 table 列的值为<union1, 2>，1和2表示参与 union 的 select 行id。

4. type列

这一列表示