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的输出列

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