分析查詢語句：EXPLAIN

使用格式

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

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);

table

不論我們的查詢語句有多複雜，裡邊兒 包含了多少個表 ，到最後也是需要對每個表進行 單表通路 的，是以MySQL規定EXPLAIN語句輸出的每條記錄都對應着某個單表的通路方法，
該條記錄的table列代表着該表的表名（有時不是真實的表名字，可能是簡稱）

代碼案例

# 查詢的每一行記錄都對應着一個單表
EXPLAIN SELECT * FROM s1;

# s1:驅動表  s2:被驅動表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

id

id如果相同，可以認為時一組，從上往下順序執行。
在所有組中，id值越大，優先級越高，越先執行。
id每個号碼表示一趟獨立的查詢，一個sql的查詢趟數越少越好

代碼案例

# 在一個大的查詢語句中每個SELECT關鍵字都對應一個唯一的id
SELECT * FROM s1 WHERE key1 = 'a';

SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';

SELECT * FROM s1 
WHERE key1 IN (SELECT key3 FROM s2);

SELECT * FROM s1 UNION SELECT * FROM s2;

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
# 查詢優化器可能對涉及子查詢的查詢語句進行重寫,轉變為多表查詢的操作，是以這裡id是1，不是2
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
 
# Union去重
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

select_type

MySQL為每一個SELECT關鍵字代表的小查詢都定義了一個稱之為select_type的屬性，意思是我們隻要知道了某個小查詢的select_type屬性，
就知道了這個小查詢在整個大查詢中扮演了一個什麼角色

代碼案例

# 查詢語句中不包含`UNION`或者子查詢的查詢都算作是`SIMPLE`類型
EXPLAIN SELECT * FROM s1;
 
# 連接配接查詢也算是`SIMPLE`類型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 
# 對于包含`UNION`或者`UNION ALL`或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個
# 查詢的`select_type`值就是`PRIMARY`
 
# 對于包含`UNION`或者`UNION ALL`的大查詢來說，它是由幾個小查詢組成的，其中除了最左邊的那個小查詢
# 以外，其餘的小查詢的`select_type`值就是`UNION`
 
# `MySQL`選擇使用臨時表來完成`UNION`查詢的去重工作，針對該臨時表的查詢的`select_type`就是`UNION RESULT`
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
 
# 子查詢：
# 如果包含子查詢的查詢語句不能夠轉為對應的`semi-join`的形式，并且該子查詢是不相關子查詢。
# 該子查詢的第一個`SELECT`關鍵字代表的那個查詢的`select_type`就是`SUBQUERY`
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 
# 如果包含子查詢的查詢語句不能夠轉為對應的`semi-join`的形式，并且該子查詢是相關子查詢，
# 則該子查詢的第一個`SELECT`關鍵字代表的那個查詢的`select_type`就是`DEPENDENT SUBQUERY`
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
# 注意的是，select_type為`DEPENDENT SUBQUERY`的查詢可能會被執行多次。
 
# 在包含`UNION`或者`UNION ALL`的大查詢中，如果各個小查詢都依賴于外層查詢的話，那除了
# 最左邊的那個小查詢之外，其餘的小查詢的`select_type`的值就是`DEPENDENT UNION`。
EXPLAIN SELECT * FROM s1 
WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
 
# 對于包含`派生表`的查詢，該派生表對應的子查詢的`select_type`就是`DERIVED`
EXPLAIN SELECT * FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;
 
# 當查詢優化器在執行包含子查詢的語句時，選擇将子查詢物化之後與外層查詢進行連接配接查詢時，
# 該子查詢對應的`select_type`屬性就是`MATERIALIZED`
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);  # 子查詢被轉為了物化表

partitions

檢視分區

代碼案例

-- 建立分區表，
-- 按照id分區，id<100 p0分區，其他p1分區
CREATE TABLE user_partitions (id INT auto_increment,
NAME VARCHAR(12),PRIMARY KEY(id))
PARTITION BY RANGE(id)(
PARTITION p0 VALUES less than(100),
PARTITION p1 VALUES less than MAXVALUE
);

DESC SELECT * FROM user_partitions WHERE id>200;

# 檢視執行計劃，partitions是p1

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

代碼案例

# 針對單表的通路方法
 
# 當表中`隻有一條記錄`并且該表使用的存儲引擎的統計資料是精确的，比如MyISAM、Memory，那麼對該表的通路方法就是`system`。
CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);

EXPLAIN SELECT * FROM t;

# 換成InnoDB
CREATE TABLE tt(i INT) ENGINE=INNODB;
INSERT INTO tt VALUES(1);
EXPLAIN SELECT * FROM tt;

# 當我們根據主鍵或者唯一二級索引列與常數進行等值比對時，對單表的通路方法就是`const`
EXPLAIN SELECT * FROM s1 WHERE id = 10005;

EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

# 在連接配接查詢時，如果被驅動表是通過主鍵或者唯一二級索引列等值比對的方式進行通路的
#（如果該主鍵或者唯一二級索引是聯合索引的話，所有的索引列都必須進行等值比較），則
# 對該被驅動表的通路方法就是`eq_ref`
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

# 當通過普通的二級索引列與常量進行等值比對時來查詢某個表，那麼對該表的通路方法就可能是`ref`
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

# 當對普通二級索引進行等值比對查詢，該索引列的值也可以是`NULL`值時，那麼對該表的通路方法就可能是`ref_or_null`
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

# 單表通路方法時在某些場景下可以使用`Intersection`、`Union`、`Sort-Union`這三種索引合并的方式來執行查詢
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

# `unique_subquery`是針對在一些包含`IN`子查詢的查詢語句中，如果查詢優化器決定将`IN`子查詢
# 轉換為`EXISTS`子查詢，而且子查詢可以使用到主鍵進行等值比對的話，那麼該子查詢執行計劃的`type`列的值就是`unique_subquery`
EXPLAIN SELECT * FROM s1 
WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';

# 如果使用索引擷取某些`範圍區間`的記錄，那麼就可能使用到`range`通路方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

# 同上
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';

# 當我們可以使用索引覆寫，但需要掃描全部的索引記錄時，該表的通路方法就是`index`
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

# 全表掃描
EXPLAIN SELECT * FROM s1;

possible_keys和key

在EXPLAIN語句輸出的執行計劃中，possible_keys清單示在某個查詢語句中，對某個表執行單表查詢時可能用到的索引有哪些。一般查詢涉及到的字段上若存在索引，
則該索引将被列出，但不一定被查詢使用。key清單示實際用到的索引有哪些，如果為NULL，則沒有使用索引

代碼案例

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

key_len

實際使用到的索引長度(即：位元組數)，幫你檢查是否充分的利用上了索引，值越大越好,主要針對于聯合索引，有一定的參考意義

代碼案例

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
 
EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';

ref

當使用索引列等值查詢時，與索引列進行等值比對的對象資訊。
比如隻是一個常數或者是某個列。

代碼案例

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
 
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

rows

預估的需要讀取的記錄條數 值越小越好

代碼案例

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';

filtered

某個表經過搜尋條件過濾後剩餘記錄條數的百分比,百分比越高越好。

代碼案例

# 如果使用的是索引執行的單表掃描，那麼計算時需要估計出滿足除使用
# 到對應索引的搜尋條件外的其他搜尋條件的記錄有多少條。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
 
# 對于單表查詢來說，這個filtered列的值沒什麼意義，我們`更關注在連接配接查詢
# 中驅動表對應的執行計劃記錄的filtered值`，它決定了被驅動表要執行的次數(即：rows * filtered)
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

Extra

Extra列是用來說明一些額外資訊的，包含不适合在其他列中顯示但十分重要的額外資訊。我們可以通過這些額外資訊來更準确的了解MySQL到底将如何執行給定的查詢語句。

No tables used：當查詢語句的沒有FROM子句時将會提示該額外資訊。

Impossible WHERE：查詢語句的WHERE子句永遠為FALSE時将會提示該額外資訊。

Using where：當我們使用全表掃描來執行對某個表的查詢，并且該語句的WHERE子句中有針對該表的搜尋條件時，在Extra列中會提示上述額外資訊。當使用索引通路來執行對某個表的查詢，
并且該語句的WHERE子句中有除了該索引包含的列之外的其他搜尋條件時，在Extra列中也會提示上述額外資訊。

No matching min/max row： 當查詢清單處有MIN或者MAX聚合函數，但是并沒有符合。WHERE子句中的搜尋條件的記錄時，将會提示該額外資訊。

Using index：當我們的查詢清單以及搜尋條件中隻包含屬于某個索引的列，也就是在可以使用覆寫索引（不需要回表，資料就在二級索引中）的情況下，在Extra列将會提示該額外資訊。

Using index condition：有些搜尋條件中雖然出現了索引列，但卻不能使用到索引。

Using where; Using join buffer (hash join)：在連接配接查詢執行過程中，當被驅動表不能有效的利用索引加快通路速度，MySQL一般會為其配置設定一塊名叫join buffer的記憶體塊
來加快查詢速度，也就是我們所講的基于塊的嵌套循環算法。

Using where; Not exists： 當我們使用左（外）連接配接時，如果WHERE子句中包含要求被驅動表的某個列等于NULL值的搜尋條件，而且那個列又是不允許存儲NULL值的，那麼在該表
的執行計劃的Extra列就會提示Not exists額外資訊。

Using union(idx_key1,idx_key3); Using where： 如果執行計劃的Extra列出現了Using intersect(...)提示，說明準備使用Intersect索引合并的方式執行查詢，括号中
的...表示需要進行索引合并的索引名稱；如果出現了Using union(...)提示，說明準備使用Union索引合并的方式執行查詢；出現了Using sort_union(...)提示，說明準備
使用Sort-Union索引合并的方式執行查詢。

Zero limit：當我們的LIMIT子句的參數為0時，表示壓根兒不打算從表中讀出任何記錄，将會提示該額外資訊。

Using filesort：如果某個查詢需要使用檔案排序的方式執行查詢，就會在執行計劃的Extra列中顯示Using filesort提示。

Using temporary：查詢，MySQL很有可能尋求通過建立内部的臨時表來執行查詢。如果查詢中使用到了内部的臨時表，在執行計劃的Extra列将會顯示Using temporary提示。

代碼案例

# 當查詢語句的沒有`FROM`子句時将會提示該額外資訊
EXPLAIN SELECT 1;

# 查詢語句的`WHERE`子句永遠為`FALSE`時将會提示該額外資訊
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

# 當我們使用全表掃描來執行對某個表的查詢，并且該語句的`WHERE`
# 子句中有針對該表的搜尋條件時，在`Extra`列中會提示上述額外資訊。
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';

# 當使用索引通路來執行對某個表的查詢，并且該語句的`WHERE`子句中
# 有除了該索引包含的列之外的其他搜尋條件時，在`Extra`列中也會提示上述額外資訊。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';

# 當查詢清單處有`MIN`或者`MAX`聚合函數，但是并沒有符合`WHERE`子句中
# 的搜尋條件的記錄時，将會提示該額外資訊
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'NlPros'; # NlPros 是 s1表中key1字段真實存在的資料

# select * from s1 limit 10;

# 當我們的查詢清單以及搜尋條件中隻包含屬于某個索引的列，也就是在可以
# 使用覆寫索引的情況下，在`Extra`列将會提示該額外資訊。比方說下邊這個查詢中隻
# 需要用到`idx_key1`而不需要回表操作：
EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';

# 有些搜尋條件中雖然出現了索引列，但卻不能使用到索引
# 看課件了解索引條件下推
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

# 在連接配接查詢執行過程中，當被驅動表不能有效的利用索引加快通路速度，MySQL一般會為
# 其配置設定一塊名叫`join buffer`的記憶體塊來加快查詢速度，也就是我們所講的`基于塊的嵌套循環算法`
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

# 當我們使用左（外）連接配接時，如果`WHERE`子句中包含要求被驅動表的某個列等于`NULL`值的搜尋條件，
# 而且那個列又是不允許存儲`NULL`值的，那麼在該表的執行計劃的Extra列就會提示`Not exists`額外資訊
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

# 如果執行計劃的`Extra`列出現了`Using intersect(...)`提示，說明準備使用`Intersect`索引
# 合并的方式執行查詢，括号中的`...`表示需要進行索引合并的索引名稱；
# 如果出現了`Using union(...)`提示，說明準備使用`Union`索引合并的方式執行查詢；
# 出現了`Using sort_union(...)`提示，說明準備使用`Sort-Union`索引合并的方式執行查詢。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

# 當我們的`LIMIT`子句的參數為`0`時，表示壓根兒不打算從表中讀出任何記錄，将會提示該額外資訊
EXPLAIN SELECT * FROM s1 LIMIT 0;

# 有一些情況下對結果集中的記錄進行排序是可以使用到索引的。
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

# 很多情況下排序操作無法使用到索引，隻能在記憶體中（記錄較少的時候）或者磁盤中（記錄較多的時候）
# 進行排序，MySQL把這種在記憶體中或者磁盤上進行排序的方式統稱為檔案排序（英文名：`filesort`）。

# 如果某個查詢需要使用檔案排序的方式執行查詢，就會在執行計劃的`Extra`列中顯示`Using filesort`提示
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

# 在許多查詢的執行過程中，MySQL可能會借助臨時表來完成一些功能，比如去重、排序之類的，比如我們
# 在執行許多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查詢過程中，如果不能有效利用索引來完成
# 查詢，MySQL很有可能尋求通過建立内部的臨時表來執行查詢。如果查詢中使用到了内部的臨時表，在執行
# 計劃的`Extra`列将會顯示`Using temporary`提示
EXPLAIN SELECT DISTINCT common_field FROM s1;

# EXPLAIN SELECT DISTINCT key1 FROM s1;

EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

# 執行計劃中出現`Using temporary`并不是一個好的征兆，因為建立與維護臨時表要付出很大成本的，是以
# 我們`最好能使用索引來替代掉使用臨時表`。比如：掃描指定的索引idx_key1即可
EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

# json格式的explain
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE s1.common_field = 'a';

EXPLAIN不考慮各種Cache
EXPLAIN不能顯示MySQL在執行查詢時所作的優化工作
EXPLAIN不會告訴你關于觸發器、存儲過程的資訊或使用者自定義函數對查詢的影響情況
部分統計資訊是估算的，并非精确值

分析查詢語句：EXPLAIN

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案