分析查詢陳述句：EXPLAIN

一、概述

使用mysqldumpslow工具定位到慢查詢陳述句之后，可以使用explain或describe工具做針對性的分析查詢陳述句，

MySQL種有專門負責優化SELECT陳述句的優化器模塊：通過計算分析系統中收集到的統計資訊，為客戶端請求的Query提供他認為最優的執行計劃，

這個執行計劃展示了接下來具體執行查詢的方式，比如多表連接的順序是什么，對于每個表采用什么訪問方法來具體執行查詢等等，MySQL提供了EXPLAIN陳述句來幫助我們查看某個查詢陳述句的具體執行計劃，看懂EXPLAIN陳述句的各個輸出項，可以有針對性的提升我們查詢陳述句的性能，

二、基本語法

explain  查詢陳述句;   select、insert、update、delete都可使用

例如：explain select * from user limit 10;

explain陳述句輸出的各列作用如下：

三、資料準備

執行以下代碼，為分析explain做準備

#創建表
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_string1(n INT) 
	RETURNS VARCHAR(255) #該函式會回傳一個字串
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 ;

SET GLOBAL log_bin_trust_function_creators=1; 

#創建存盤程序：
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_string1(6),
    (min_num + 30 * i + 5),
    rand_string1(6),
    rand_string1(10),
    rand_string1(5),
    rand_string1(10),
    rand_string1(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 s2 VALUES(
        (min_num + i),
		rand_string1(6),
		(min_num + 30 * i + 5),
		rand_string1(6),
		rand_string1(10),
		rand_string1(5),
		rand_string1(10),
		rand_string1(10));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //
DELIMITER ;

#呼叫存盤程序
CALL insert_s1(10001,10000);

CALL insert_s2(10001,10000);

SELECT COUNT(*) FROM s1;

SELECT COUNT(*) FROM s2;

四、explain各列詳解

1、table：表名

查詢的每一個行記錄對應著一個單表

explain select * from s1;
explain select * from s1 inner join s2;

 2、id：在一個大的查詢陳述句中，每一個select關鍵字都對應一個唯一的id

explain select * from s1where key1 = 'a';

 總結：

id如果相同，可以認為是一組，從上往下執行

在所有組種，id越大，優先級越高，越先執行

每一個id號碼表示一趟獨立的查詢，一個SQL查詢的趟數越少越好，

3、select_type：select關鍵字對應的那個查詢的型別，確定小查詢在大查詢種扮演什么角色

①查詢陳述句中不包含UNION或者子查詢的查詢都算是SIMPLE型別

explain select * from s1;

 ②連接查詢也是SIMPLE

explain select * from s1 inner join s2;

 ③對于包含UNION、UNION ALL的大查詢來說，它是由幾個小查詢組成的，其中最左邊的查詢的select_type值就是PRIMARY，其余的小查詢的select_type值為UNION

④MySQL選擇使用臨時表來完成UNION查詢的去重作業，針對該臨時表的查詢的select_type的值是UNION RESULT

 UNION 具有去重操作，UNION ALL沒有去重操作，所以UNION會有一個臨時表

 ⑤如果包含子查詢的查詢陳述句不能轉化為多表聯查的形式，并且該子查詢不是相關子查詢，該子查詢的第一個select關鍵字的查詢的select_type就是SUBQUERY

⑥如果包含子查詢的查詢陳述句不能轉化為多表聯查的形式，并且該子查詢是相關子查詢，該子查詢的第一個select關鍵字的查詢的select_type就是DEPENDENT SUBQUERY

⑦在包含`UNION`或者`UNION ALL`的大查詢中，如果各個小查詢都依賴于外層查詢的話，那除了最左邊的那個小查詢之外，其余的小查詢的`select type `的值就是`DEPENDENT UNION

 ⑧對于包含派生表的查詢，該派生表對應的子查詢的select_type就是DERIVED

 ⑨當查詢優化器在執行包含子查詢的陳述句時，選擇將子查詢物化之后與外層查詢進行連接查詢時，該子查詢對應的`select type`屬性就是‘MATERIALIZED~

 4、type

執行計劃的一條記錄就代表著MySQL對某個表的執行查詢時的訪問方法，又稱°訪問型別”，其中的type列就表明了這個訪問方法是啥，是較為重要的一個指標，比如，看到type列的值是ref，表明MysQL即將使用ref訪問方法來執行對s1表的查詢，

完整的訪問方法如下: system,const,eq_ref,ref, fulltext,ref_or_null , index_merge ,unique_subquery , index_subquery , range , index,ALL，

①system

當表中只有一條記錄并且該表使用的存盤引擎統計的書資料是精確的，比如MyISAM、Memory,那么對該表的訪問方法就是system

②const

當我們根據主鍵或唯一的二級索引列與常數進行等值匹配時，對單表的訪問方法就是const

explain select * from s1 where id = 10005;
explain select * from s1 where key2 = 10066;

 ③eq_ref

在連接查詢時，如果被驅動表是通過主鍵或者唯一二級索引列等值匹配的方式進行訪問的（如果該主鍵或者唯一二級索引是聯合索引的話，所有的索引列都必須進行等值比較)，則對該被驅動表的訪問方法就是`eg ref`

explain select * from s1 inner join s2 on s1.id = s2.id;

 ④ref

當通過普通的二級索引列與常量進行等值匹配時來查詢某個表，那么對該表的訪問方法就可能是`ref'

explain select * from s1 where key1 = 'a';

 ⑤unique_subquery

unique subquery`是針對在一些包含`IN`子查詢的查詢陳述句中，如果查詢優化器決定將`IN`子查詢
轉換為`ExISTS`子查詢，而且子查詢可以使用到主鍵進行等值匹配的話，那么該子查詢執行計劃的'type '列的值就是`unique_ subquery

 ⑥如果使用索引獲取某些范圍區間的記錄，那么就可能用到range訪問方法

 ⑦index

當我們可以使用索引覆寫，但需要掃描全部的索引記錄時，該表的訪問方法就是index

 ⑧all：全表掃描

 5、possiable_keys和key

possiable_keys串列示在某個查詢陳述句中，對某個表執行單表查詢時可能用到的索引有哪些，

key表示實際用到的索引有哪些

6、key_len

實際使用到的索引的長度（位元組數），檢查是否充分利用了索引，值越大越好（和自己比），主要針對聯合索引，

先更新到這里，會持續更新~~~~

標籤：MySQL

上一篇：【Mysql】復合主鍵的索引

下一篇：返回列表