MySQL系列-高级-性能分析工具-EXPLAIN

发布于：2022-11-02 ⋅ 阅读:(762) ⋅ 点赞:(0)

MySQL系列-高级-性能分析工具-EXPLAIN

本文参考了尚硅谷的MySQL课程，康师傅主讲，很优秀啊

1. EXPLAIN概述

1.1 官网介绍

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
在这里插入图片描述

Optimizing Queries with EXPLAIN
The EXPLAIN statement provides information about how MySQL executes statements:
EXPLAIN语句提供了MySQL如何执行语句的信息:

EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
EXPLAIN与SELECT、DELETE、INSERT、REPLACE和UPDATE语句一起工作。

When EXPLAIN is used with an explainable statement, MySQL displays information from the optimizer about the statement execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. For information about using EXPLAIN to obtain execution plan information, see Section 8.8.2, “EXPLAIN Output Format”.
当EXPLAIN与可解释语句一起使用时，MySQL将显示来自优化器的关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括关于表如何连接以及以何种顺序连接的信息。有关使用EXPLAIN获取执行计划信息的信息，请查看8.8.2, “EXPLAIN Output Format”.

When EXPLAIN is used with FOR CONNECTION connection_id rather than an explainable statement, it displays the execution plan for the statement executing in the named connection. See Section 8.8.4, “Obtaining Execution Plan Information for a Named Connection”.
当EXPLAIN与FOR CONNECTION connection_id而不是可解释语句一起使用时，它将显示在指定连接中执行的语句的执行计划。参见第8.8.4节“Obtaining Execution Plan Information for a Named Connection”。

For SELECT statements, EXPLAIN produces additional execution plan information that can be displayed using SHOW WARNINGS. See Section 8.8.3, “Extended EXPLAIN Output Format”.
对于SELECT语句，EXPLAIN生成可以使用SHOW WARNINGS显示的附加执行计划信息。参见8.8.3节“Extended EXPLAIN Output Format”。

EXPLAIN is useful for examining queries involving partitioned tables. See Section 24.3.5, “Obtaining Information About Partitions”.
EXPLAIN对于检查涉及分区表的查询非常有用。参见第24.3.5节“Obtaining Information About Partitions”。

The FORMAT option can be used to select the output format. TRADITIONAL presents the output in tabular format. This is the default if no FORMAT option is present. JSON format displays the information in JSON format.
FORMAT选项可用于选择输出格式。TRADITIONAL以表格格式显示输出。如果没有FORMAT选项，这是默认值。JSON格式以JSON格式显示信息。

1.2 EXPLAIN 基本语法

如果我们想看看某个查询的执行计划，可以使用EXPLAIN， EXPLAIN或 DESCRIBE语句的语法形式如下：

EXPLAIN SELECT select_options

或者

DESCRIBE SELECT select_options

EXPLAIN 语句输出的各个列的作用如下：
在这里插入图片描述

2. 基于函数和存储过程插入数据

2.1 创建表

创建表s1：

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

创建S2表

CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2.2 创建函数和过程

2.2.1 创建函数

创建函数 rand_string2 用生产随机字符串

DELIMITER //
CREATE DEFINER = `root` @`localhost` FUNCTION rand_string2 ( n INT ) RETURNS VARCHAR ( 255 ) CHARSET utf8mb4 COLLATE utf8mb4_general_ci #该函数会返回一个字符串
BEGIN
	DECLARE
		chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE
		return_str VARCHAR ( 255 ) DEFAULT '';
	DECLARE
		i INT DEFAULT 0;
	WHILE
			i < n DO
			
			SET return_str = CONCAT(
				return_str,
			SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));
		
		SET i = i + 1;
		
	END WHILE;
	RETURN return_str;
	
END // 
DELIMITER;

创建函数，假如报错
在这里插入图片描述设置参数 log_bin_trust_function_creators，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1;

2.3 创建存储过程插入数据

1. 存储过程insert_table_s1

创建存储过程insert_table_s1 用于向s1表中插入数据：

DELIMITER //
CREATE DEFINER = `root` @`localhost` PROCEDURE `insert_table_s1` (
	IN min_num INT ( 10 ),
	IN max_num INT ( 10 )) 
	
BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;
	REPEAT
			
		SET i = i + 1;
		INSERT INTO s1
		VALUES
			(
				( min_num + i ),
				rand_string2 ( 6 ),
				( min_num + 30 * i + 5 ),
				rand_string2 ( 6 ),
				rand_string2 ( 10 ),
				rand_string2 ( 5 ),
				rand_string2 ( 10 ),
			rand_string2 ( 10 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;

END // 

DELIMITER;

调用存储过程，插入数据

CALL insert_table_s1(10001,10000)

2. 存储过程insert_table_s2

创建存储过程insert_table_s2 用于向s2表中插入数据：

DELIMITER //
CREATE DEFINER = `root` @`localhost` PROCEDURE `insert_table_s2` (
	IN min_num INT ( 10 ),
	IN max_num INT ( 10 )) 
	
BEGIN
	DECLARE
		i INT DEFAULT 0;
	
	SET autocommit = 0;
	REPEAT
			
		SET i = i + 1;
		INSERT INTO s2
		VALUES
			(
				( min_num + i ),
				rand_string2 ( 6 ),
				( min_num + 30 * i + 5 ),
				rand_string2 ( 6 ),
				rand_string2 ( 10 ),
				rand_string2 ( 5 ),
				rand_string2 ( 10 ),
			rand_string2 ( 10 ));
		UNTIL i = max_num 
	END REPEAT;
	COMMIT;

END // 

DELIMITER;

插入数据

CALL insert_table_s2(10001,10000)

3. EXPLAIN 操作

为了便于对照，把EXPLAIN的输出，再罗列如下：
EXPLAIN 语句输出的各个列的作用如下：
在这里插入图片描述
在navicat或mysql命令行输入如下语句：

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

输出为：
在这里插入图片描述

这里的输出与官网所描述是一致的。

3.1 table与id作用

输入：

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

输出为：
在这里插入图片描述

可以看出，这里的table分别对应s1和s2，也就是无论查询语句有多复杂，语句中包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名（有时不是真实的表名字，可能是简称）。

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

输出为：
在这里插入图片描述

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field= 'a');

输出为：
在这里插入图片描述
输入：

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

输出为：
在这里插入图片描述
输入：

EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

输出为：
在这里插入图片描述

id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3.2 select_type

select_type，可以是下表中所示的任何类型。json格式的EXPLAIN将select_type公开为query_block的属性，除非它是SIMPLE或PRIMARY。JSON名称(如果适用)也显示在表中。
在这里插入图片描述

1. simple查询

输入：

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

输出为：
在这里插入图片描述
join查询也是simple查询

2. PRIMARY，UNION和UNION RESULT

输入：

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

输出为：
在这里插入图片描述
s1表的select_type为PRIMARY，表示最外层查询
id为2的select_type为UNION，表示UNION中的第二个或后面的SELECT语句
第3行id为Null的，select_type为UNION RESULT，表示UNION的结果。

3. SUBQUERY 和 DEPENDENT SUBQUERY

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

输出为：
在这里插入图片描述
id为2的select_type为SUBQUERY，表示子查询中的第一个SELECT

输入：

 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

输出为：
在这里插入图片描述

id为2的select_type为DEPENDENT SUBQUERY，表示子查询中的第一个SELECT，依赖于外部查询

4. DEPENDENT UNION

输入：

EXPLAIN SELECT * FROM s1 WHERE 	key1 IN ( SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b' );

输出为：
在这里插入图片描述
id为3的select_type为DEPENDENT UNION，表示UNION中的第二个或更多的SELECT语句，依赖于外部查询

5. DERIVED 和 MATERIALIZED

输入：

EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS
derived_s1 where c > 1;

输出为：
在这里插入图片描述
id为2的select_type为DERIVED，表示派生表

输入：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2

输出为：
在这里插入图片描述
id为3的select_type为MATERIALIZED，表示物化子查询

6. partitions

输入：

输出为：

id为2的select_type为DERIVED，表示派生表

本文含有隐藏内容，请 开通VIP 后查看

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布