MySQL分析查询语句Explain 七千字总结

分析查询语句：EXPLAIN

---

1概述

​ 定位了查询慢的SQL之后，就可以使用EXPLAIN或者DESCRIBE工具做针对性的分析查询。两者使用方法相同，并且分析结果也是相同的。

​ MySQL中有专门负责SQL语句优化的优化器模块，主要功能是计算分析系统中收集到的统计信息，为客户端请求的Query提供它最优的执行计划（它认为的最优数据检索方案毕竟是自动分析成的，所以不一定是DBA或者开发人员认为的最优方案）

​ 这个执行计划展示了接下来进行具体查询的方式，比如多表连接的顺序、对每个表采用什么方式进行具体的查询等等，MySQL提供的EXPLAIN语句可以用来查询某个查询语句的具体执行计划，根据EXPLAIN语句的输出项，可以有针对性地提升查询SQL的性能。

能查到什么？

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以被使用
• 哪些索引实际被使用
• 表之间的引用关系
• 每张表有多少行被优化器查询

版本区别

• MySQL5.6.3之前只能使用EXPLAIN SELECT，之后可以使用EXPLAIN SELECT， UPDATE， DELETE
• 5.7之前的版本，想要查看partitions（分区）和filtered需要使用EXPLAIN partitions、filtered，而5.7之后直接默认显示

数据准备

创建表

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


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

创建存储函数

-- 函数返回随机字符串
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;

存储过程

​ 向s1、s2表添加数据的存储过程

DELIMITER //
CREATE PROCEDURE insert_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_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_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 s1 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;

执行存储过程添加数据

CALL insert_s1(10001, 10000);
CALL insert_s2(10001, 10000);

Explain的输出列

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

1 id

​ id，在一个大的查询语句中每个SELECT关键字都对应着一个唯一的id，所以有几个select关键字就会有几个id：

EXPLAIN SELECT * FROM s1

EXPLAIN SELECT * FROM s1 INNER JOIN s2

上面的两个SQL都只有一个select所以只有一个id

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

子查询有两个select，所以对应两个id1和2

🌟查询优化器可能会对涉及子查询的查询语句进行重写：

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

优化器在看到子查询后判断能够变为多表连接以降低复杂度（O(n^2) -> O(n)）:

​ SELECT * FROM s1, s2 ON s1.key1 = s2.key2 WHERE s2.common_field = 'a'

重写后的sql变成了一个select，所以查询结果仍然是一个id

​ 但是如果s2查的是key1，就会变成下面这样：

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

🌟UNION去重

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

union由于去重操作会使用到中间表，所以会有一个table<union, 1, 2>

但是我这里的临时表也有id = 3，看康师傅视频是没有的，是版本的问题吗？也就是还对中间表进行了select

​ 如果使用的是UNION ALL不进行去重，则是：

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

小结：

• id如果相同，则会被认为是同一组查询，会按照从上往下的顺序执行
• 如果不同，则id越大的优先级越高，越先执行
• id的号码表示一趟独立的查询，一个sql的查询趟数越少越好

2 select_type

​ 一个大的查询里面可以包含多个select关键字，每个select关键字代表一个小的查询语句，而每个小的查询中都包含着若干的表进行连接操作，而每一张表都对应着EXPLAIN查询计划的一条记录，对于在同一个select关键字的表来说，他们的id是相同的。

​ select_type：SELECT关键字对应查询的类型，即我们只要知道了某个小查询的select_type属性，就能知道这个小查询在大查询中扮演的角色、起到的作用

常见的select_type：

• SIMPLE：不包含UNION或者子查询的查询都算是SIMPLE类型