前言

提示

本文基于 MySQK 8.0.32 版本编写，理论上其他版本应该也是类似的。

使用如下语句对sql语句进行执行计划查询

EXPLAIN  SELECT * FROM user WHERE id IN (SELECT user_id FROM role_user);

返回结果

+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
| id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                                                                             | 
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
|  1 | SIMPLE      | user      | NULL       | ALL   | PRIMARY       | NULL               | NULL    | NULL |    3 |   100.00 | NULL                                                                              | 
|  1 | SIMPLE      | role_user | NULL       | index | NULL          | index_role_user_id | 8       | NULL |    3 |    33.33 | Using where; Using index; FirstMatch(user); Using join buffer (Block Nested Loop) | 
+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+

以下是Explain关键字的列含义：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id
select_type	SELECT关键字对应查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能使用到的索引
key	实际使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询的时候，与索引列进行等值匹配的对象信息(索引的哪一列被引用了)
rows	预估需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

敲黑板，接下来我们来详细分析一下每一个字段的含义

一、id

该语句的唯一标识。如果explain的结果包括多个id值，则数字越大越先执行；而对于相同id的行，则表示从上往下依次执行。

二、select_type

select_type字段表示查询的类型，有如下几种：

select_type	描述
SIMPLE	简单查询，没有子查询或者UNION
PRIMARY	主查询，即最外层的查询
SUBQUERY	子查询，即SELECT语句中的子查询
DEPENDENT SUBQUERY	依赖于外部查询的子查询
UNION	UNION查询，即SELECT语句中的UNION.如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。
DERIVED	派生表，即SELECT语句中的FROM子句中的子查询
INDEPENDENT SUBQUERY	不依赖于外部查询的子查询
MATERIALIZED	预处理查询，即SELECT语句中的FROM子句中的子查询
SCALAR	嵌套查询，即SELECT语句中的SELECT子句中的子查询
INDEPENDENT UNION	不依赖于外部查询的UNION查询
INDEPENDENT DERIVED	不依赖于外部查询的派生表
INDEPENDENT DERIVED UNION	不依赖于外部查询的派生表和UNION查询
INDEPENDENT DERIVED UNION ALL	不依赖于外部查询的派生表和UNION查询，并且UNION ALL
INDEPENDENT DERIVED UNION DISTINCT	不依赖于外部查询的派生表和UNION查询，并且UNION DISTINCT
INDEPENDENT DERIVED UNION SEMI JOIN	不依赖于外部查询的派生表和UNION查询，并且UNION SEMI JOIN
INDEPENDENT DERIVED UNION DISTINCT SEMI JOIN	不依赖于外部查询的派生表和UNION查询，并且UNION DISTINCT SEMI JOIN
INDEPENDENT DERIVED UNION ALL SEMI JOIN	不依赖于外部查询的派生表和UNION查询，并且UNION ALL SEMI JOIN
INDEPENDENT DERIVED UNION ALL DISTINCT	不依赖于外部查询的派生表和UNION查询，并且UNION ALL DISTINCT
INDEPENDENT DERIVED UNION ALL DISTINCT SEMI JOIN	不依赖于外部查询的派生表和UNION查询，并且UNION ALL DISTINCT SEMI JOIN

三、table

表示当前这一行 SQL 正在访问哪张表，如果SQL定义了别名，则展示表的别名

四、partitions

当前查询匹配记录的分区。对于未分区的表，返回null

五、type

六、possible_keys

七、key

八、key_len

rows

filtered

Extra

参考

1.MySQL EXPLAIN 完全解读

2.EXPLAIN中ref、rows、filtered、Extra字段的剖析

3.explain-output

测试数据创建

1.创建表结构

CREATE TABLE table_1 (
	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


CREATE TABLE table_2 (
	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.创建存储函数

-- 函数返回随机字符串
DELIMITER //

CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4
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为1

SELECT @@log_bin_trust_function_creators variable;

SET GLOBAL log_bin_trust_function_creators = 1;

3.存储过程

向table_1、table_2表添加数据的存储过程

DELIMITER //
CREATE PROCEDURE insert_table_1 (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 table_1 VALUES(
		(min_num + i),
		rand_string(6),
		(min_num + 30* i + 5),
		rand_string(6),
		rand_string(10),
		rand_string(5),
		rand_string(10),
		rand_string(10)
	);
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER;



DELIMITER //
CREATE PROCEDURE insert_table_2 (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 table_1 VALUES(
		(min_num + i),
		rand_string(6),
		(min_num + 30* i + 5),
		rand_string(6),
		rand_string(10),
		rand_string(5),
		rand_string(10),
		rand_string(10)
	);
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER;

4.执行存储过程添加数据

CALL insert_table_1(10001, 10000);
CALL insert_table_2(10001, 10000);