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