MySQL（十四）分析查詢語句Explain 七千字總結

Tod4發表於2023-04-14
MySqlAI

分析查詢語句：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的輸出列

image-20230413142058178

列名 描述
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

image-20230413152527504

EXPLAIN SELECT * FROM s1 INNER JOIN s2

image-20230413152550172

上面的兩個SQL都只有一個select所以只有一個id

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

子查詢有兩個select，所以對應兩個id1和2

image-20230413153710803

?查詢最佳化器可能會對涉及子查詢的查詢語句進行重寫：

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

image-20230413154055995

最佳化器在看到子查詢後判斷能夠變為多表連線以降低複雜度（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')

image-20230413154648592

?UNION去重

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

image-20230413155716716

union由於去重操作會使用到中間表，所以會有一個table<union, 1, 2>

但是我這裡的臨時表也有id = 3，看康師傅影片是沒有的，是版本的問題嗎？也就是還對中間表進行了select

​ 如果使用的是UNION ALL不進行去重，則是：

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

image-20230413160443889

小結：

  • id如果相同，則會被認為是同一組查詢，會按照從上往下的順序執行
  • 如果不同，則id越大的優先順序越高，越先執行
  • id的號碼錶示一趟獨立的查詢，一個sql的查詢趟數越少越好
2 select_type

​ 一個大的查詢裡面可以包含多個select關鍵字，每個select關鍵字代表一個小的查詢語句，而每個小的查詢中都包含著若干的表進行連線操作，而每一張表都對應著EXPLAIN查詢計劃的一條記錄，對於在同一個select關鍵字的表來說，他們的id是相同的。

​ select_type：SELECT關鍵字對應查詢的型別，即我們只要知道了某個小查詢的select_type屬性，就能知道這個小查詢在大查詢中扮演的角色、起到的作用

常見的select_type：

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

  • UNION、PRIMARY、UNION RESULT：對於包含UNION和UNION ALL的語句，它是由幾個小的查詢組成的，除了最左邊的查詢的select_type是PRIMARY，其餘的均為UNION，而針對臨時表的select則是UNION RESULT

    image-20230413155716716

  • SUBQUERY：如果包含子查詢的查詢語句不能夠轉化為semi-join的方式（即最佳化器將子查詢最佳化為表連線），並且子查詢不是相關子查詢(即用到了外表的子查詢)，則該子查詢的第一個select關鍵字代表的那個查詢的select_type就是SUBQUERY

    explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a'

    image-20230413191457400

    首先這個子查詢不是相關子查詢，那麼這個sql能不能最佳化成表連線的sql呢？

    select * from s1 INNER JOIN s2 on s1.key1 = s2.key1

    答案是不能，這兩個sql是不同的：比如s1表中有一個key1值，s2表有兩個重複的key1值，則第一條語句由於是in，所以只會匹配一次，而第二條sql是等於號，所以這種情況下會匹配兩次，從而二個sql得到的結果是完全不同的，因此這個sql會用到兩個select，也就出現兩個id了，一個select為Primary，子查詢的select為subquery。

  • DEPENDENT SUBQUERY：如果包含子查詢的查詢語句不能夠轉化為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'

    image-20230413192644328

    select_type 為 DEPENDENT SUBQUERY 的查詢可能會被執行多次

  • DEPENDENT UNION：在包含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')

    image-20230413193342501

    第二個子查詢UNION加上了DEPENDENT 好理解，因為用到了外表

    但是，為什麼第一個子查詢沒有用到外表，也是DEPENDENT SUBQUERY呢？

    這是由於最佳化器對於in的改動：

    ​ where exists (s1.key1 = s2.key1 ...)，這樣就變為了相關子查詢，至於為啥這麼做完全不知道了。。

  • DERIVED：派生表對應子查詢的select_type為DERIVED

    EXPLAIN SELECT * FROM (SELECT key1, count(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1

    image-20230413194529416

    <drived2>即為id為2的派生表

  • MATERIALIZED（物化）：當查詢最佳化器在執行包含子查詢語句的時候，選擇將子查詢之後與外層查詢進行連線時，該子查詢對應的select_type就是MATERIALIZED

    EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2)

    image-20230413195516194

    SELECT key1 FROM s2的結果是一個個的記錄然後與外表進行連線，則這些記錄就可以被稱作是物化表，查詢方式為MATERIALIZED

    而外層select直接將子查詢成的物化表看做普通的表，查詢方式為SIMPLE

    這個和上面的非相關子查詢有點像，後面新增了一個or key3 = 'a'，非相關子查詢就變成物化表了？？？

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

    image-20230413191457400

3 table

​ table，即表名

  • 查詢出來的每一行記錄都對應著一個單表

    EXPLAIN SELECT * FROM s1

    image-20230413152527504

    EXPLAIN SELECT * FROM s1, s2

    image-20230413152550172

    可以看到兩個記錄的id是一樣的，因為屬於同一個大的查詢語句（只有一個select）

    並且s2排在s1的前面，所以s2是驅動表，s1是被驅動表（並不能根據sql語句判斷，因為sql的順序有可能被最佳化器最佳化修改）

4 partitions
  • 代表分割槽表中的命中情況，非分割槽表，該值為NULL，一般情況下我們查詢語句執行計劃的partitions列的值也都是NULL

​ image-20230413200812010

5 type ?

​ 執行計劃的一條記錄就代表著MySQL對某個表的執行查詢時的訪問方法，又稱訪問型別，即這裡的type。比如，type是ref，表名mysql將使用ref方法對改行記錄的表進行查詢。

​ 完整的訪問方法如下：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all，越靠前代表效率越高

​ SQL效能最佳化的目標：至少要達到range級別，要求是ref級別，最好是const級別。

  • system：當表中只有一條記錄，並且該表使用的儲存引擎的統計資料是精確的，比如MyISAM、Memory，那麼對該表的訪問方法就是system

    CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);

EXPLAIN SELECT * FROM t

    image-20230413202000688

    儲存引擎的統計資料是精確的，意思是例如MyISAM儲存儲存引擎有記錄的記錄的個數

    system是效能最高的情況

    而如果再新增一條記錄，會變為all，而InnoDB即使一條資料也是all

    image-20230413202320906

    於此同時，INNODB訪問count()的資料也是all的

    CREATE TABLE tt(i INT) ENGINE=INNODB;
INSERT INTO tt VALUES(1);
EXPLAIN SELECT count(*) FROM tt

    image-20230413205132920

  • const：當根據主鍵或者唯一的二級索引與常數進行等值匹配的時候，對單表的訪問就是const，表示常數級別

    EXPLAIN SELECT * FROM s1 WHERE id = 10005;
EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

    image-20230413210639838

    如果是key3，則為all

    EXPLAIN SELECT * FROM s1 WHERE key3 = 1006;

    image-20230413210652051

    這裡其實牽扯到隱式轉換導致索引失效的問題：由於key3是varchar型別的，但是這裡是數字進而進行了函式轉換，進而索引失效只能all查詢了

  • eq_ref：在連線查詢的時候，如果被驅動表是透過主鍵或者唯一的二級索引等值匹配的方式進行訪問的（如果主鍵或者唯一的二級索引是聯合索引，則要求索引的每一列進行聯合匹配），則對於該被驅動表的訪問方式就是eq_ref

    EXPLAIN SELECT * from s1 INNER JOIN s2 WHERE s1.key2 = s2.key2

    image-20230413211429876

    key2是帶有唯一約束的二級索引，因此被驅動表s2的訪問方式為eq_ref

    其中ref表示查詢的值已經被指定：即透過all方式查詢的s1表指定的

  • ref：當透過普通的二級索引與常量進行等值匹配來查詢某個表，對該表的訪問方式可能是ref

    EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq';

    image-20230413212234596

    這裡key3就是沒有唯一約束的普通索引，可以看到用到了索引key3，因此type為ref

  • ref_or_null：當透過普通的二級索引與常量進行等值匹配來查詢某個表，當該值也可能是null值時，那麼對該 表的訪問方式可能就是ref_not_null

    EXPLAIN SELECT * FROM s1 WHERE key3 = 'CUTLVwqweqweq' OR key3 IS NULL;

    image-20230413212613015

  • index_merge：單表訪問在某些情況下可以使用Intersection、Union、Sort-Union這三種索引合併的方式來執行查詢

    EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key2 = 123131

    image-20230413213029544

    key1和key2均為索引列，一個Select關鍵字只能使用一個索引，所以這裡使用了合併索引為一個虛擬索引的辦法，相當於掃描兩個索引樹取出主鍵並取並集再回表的操作

    但是，如果是AND的情況，只會使用一個索引（這裡是唯一的二級索引。故而是const）

    EXPLAIN SELECT * FROM s1 WHERE key1 = 'rCLXEg' AND key2 = 10036

    image-20230413213831202

  • 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'

    image-20230413214650160

  • range：如果使用索引獲取某些範圍區間的記錄，就可能使用到range方法

    EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c')

    image-20230413214919640

    非索引列則為all

  • index：當可以使用索引覆蓋，並且需要掃描全部的索引記錄，該表的訪問方法就是index

    EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a'

    image-20230413215536216

    可以看到在key裡面還是用到了聯合索引的，儘管根據最左字首原則，只有檢索條件為key_part1才能用到索引，這裡是因為檢索條件和select返回列都是和聯合索引相關的列，所以使用了聯合索引掃描了全部的索引記錄，因為這樣就不需要再回表找其他的列了（查的列都在索引上）

    不需要回表就能查詢到所需要的資料，稱作索引覆蓋

    這時候再新增一個其他的列：

    EXPLAIN SELECT key1, key_part2 FROM s1 WHERE key_part3 = 'a'

    結果為ALL，因為聯合索引列上沒有key1的資訊，需要回表去查key1

  • all：全表掃描

6 possible_key 和 key

​ 在EXPLAIN語句輸出的執行計劃中，possible_key 表示在單表查詢中可能會用到的索引，一般查詢涉及到的欄位上存在索引，則該索引就將被列出，但不一定被查詢使用。

​ key則表示經過查詢最佳化器計算使用不同索引的查詢成本之後，最終確定使用的索引。

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

image-20230414101530039

key1和key3均為普通的二級索引，但是key3是等值匹配因此耗費的成本較低，所以最終選擇使用索引key3

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

而如果這裡改成OR，則會演變成之前講的 index_merge 合併索引，即將兩個索引樹的主鍵提取取並集，然後統一到聚簇索引中執行一次回表操作

image-20230414101830623

EXPLAIN SELECT key1, key3 FROM s1 WHERE key1 > 'z' OR key3 = 'a'

再擴充套件一下，即使查詢列可以使用覆蓋索引（即查詢列的值都可以在索引樹中找到），仍然需要進行一次回表操作，因此兩個查詢的執行計劃是相同的：

image-20230414102257106

7 index_len ?（聯合索引分析）

​ 實際使用到的索引的長度（即位元組數），用來檢視是否充分利用了索引，index_len的值越大越好

這裡的越大越好是跟自己進行的比較，因為主要是針對的聯合索引，因為利用聯合索引的長度越大，查詢需要讀入的資料頁就越少，效率也就越高

EXPLAIN SELECT * FROM s1 WHERE id = 10005

image-20230414103528727

為什麼是4：因為id列是int型所以真實資料佔4個位元組，同時行格式中主鍵非空因此不需要NULL值列表，定長不需要變長欄位長度列表，故而是4

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

image-20230414104516060

key2是int型別，佔4個位元組，並且具有唯一性約束但是可能為空，因此行格式中null值列表佔1個位元組，總共5個位元組

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

首先key1是varchar(100)，並且表是utf8mb3格式的，因此真實資料儲存佔(100 * 3) = 300個位元組，本身定長所以行格式的變長欄位長度列表佔2個位元組，NULL值列表佔1個位元組，共計303個位元組

同理下面的查詢一個為303，另一個是606，這時候才體現出key_len的作用：第二個sql比第一個sql利用聯合索引更加充分

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

​ ref表示當我們使用索引列等值查詢的時候，與索引列進行等值匹配的物件的資訊。

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

image-20230414110557413

key1是普通的二級索引，所以type是ref（唯一的二級索引是const），而等值的匹配型別是一個常量，因此ref列的值是const

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

image-20230414111603202

由於是表連線，所以只有一個select id，然後由於是主鍵進行的連線，所以對於第二個表的訪問方式type是eq_ref（普通索引則為ref），同時等值比較的是s1的列，因此ref為atguigu1.s2.id

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

image-20230414111724590

key1是普通的二級索引，因此type為ref，等值比較的型別是一個函式返回值，因此ref列的值為func

9 rows ?

​ rows：預估需要讀取的記錄條數，值越小越好

值越小表示在同一個資料頁中的可能性越大，IO的次數也就越少

10 filtered ?（結合rows分析）

​ filtered：表示某個表經過條件過濾之後，剩餘記錄條數的百分比，值越大越好

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

image-20230414112551508

如上表示經過條件過濾後，100%都是符合要求的

值越大越好的原因：假設條件過濾後是40條記錄，如果filtered是100%，則原來有40條，如果filtered是10%，則原來有400條，相比之下40條需要讀取的資料頁要少一些

​ 而如果執行的是索引的單表掃描，那麼計算的時候除了估計出滿足對應索引的搜尋條件，還應計算同時滿足其他條件的記錄是多少條

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'b';

image-20230414130019004

如上面的sql，rows303表示預估滿足索引列key1需要讀取的記錄數，而filtered表示加上common_field欄位後預估讀取佔全部的百分比

​ ? 對於單表查詢這個filtered列其實沒有太大作用，但是它在多表連線中驅動表對應的執行計劃記錄的filtered值，決定了被驅動表的執行次數。

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

image-20230414130633288

首先多表連線查詢所以為同一個select id，其次連線條件是普通的二級索引，所以驅動表的訪問型別type為all，被驅動表的訪問型別type為ref，最後s1表預估讀取的記錄數rows為10152，再經過條件過濾10152 * 10%和s2做等值匹配，因此1015就是s2表的執行次數

11 Extra?

​ Extra用來說明一些不適合在其他列中展示但是十二分重要的額外資訊。透過這些額外資訊可以更準確地知道mysql導致是怎麼執行給定的查詢語句的。

  • no tables used：沒有from字句，即沒有用到表的情況

    EXPLAIN select 1

    image-20230414132310640

  • impossible where：where語句永遠為false的情況

    EXPLAIN select * FROM s1 WHERE 1 != 1

    image-20230414132438010

    這樣也沒有用到表，反正條件都不對

  • where：使用全表掃描來執行針對某個表的查詢，字句中有針對該表的搜尋條件，則在Extra中展現

    EXPLAIN select * FROM s1 WHERE common_field = 'a'

    image-20230414134955822

    common_field是一個不帶索引的普通欄位，因此type為all，Extra展現了語句是透過where執行的

  • no matching min/max row當查詢列表處有min或者max聚合函式，但是沒有符合where條件的記錄時，將會提示該額外資訊

     EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'adqwdqweqwe'

    image-20230414135835116

    而當where條件符合（或者根本沒有where條件的時候），則顯示Select tables optimized away，表示選擇最佳化後的表

    EXPLAIN SELECT MIN(key1) FROM s1

    image-20230414140049336

  • using index：當出現索引覆蓋，即查詢和檢索條件的列都在使用的索引裡面，也即是不需要回表操作的情況

    EXPLAIN SELECT key1 FROM s1 WHERE key1 = 'a'

    image-20230414140409229

    當出現主鍵的情況也是覆蓋索引

  • using index condition：即索引條件下推，考慮下面的sql查詢：

    EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 like '%a%'

    image-20230414141228852

    ​ 這條sql執行的正常順序應該是：首先使用idx_key1的索引樹，查詢key1 > z的所有主鍵值，這裡找到了385條記錄的主鍵，然後對這些主鍵進行回表操作，在聚簇索引中找到包含其他列的資料，然後判斷剩下的過濾條件進行返回。

    ​ 而索引條件下推針對特殊情況進行了最佳化：就是如果剩餘的過濾條件針對的是索引列，則不需要在回表後進行判斷，這樣就能夠減少回表的操作，但是rows仍為385

  • using join buffer：即基於塊的巢狀迴圈演算法：當被驅動表不能夠有效利用索引加快訪問速度，mysql就會為其在記憶體中分配一塊join buffer的記憶體塊來加快訪問的速度

    EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field

    image-20230414142120715

    common_field是一個沒有索引的列

  • not exists：在表連線的時候，當where條件中被驅動表的某個列等於null，而這個列又有非空約束的時候，Extra就會展現not exists

    EXPLAIN SELECT * FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.id IS NULL

    image-20230414142638304

    注意一定是被驅動表的列，如果是主驅動表出現這種情況，會直接顯示為impossible where，就不會再看被驅動表了

  • using union(index_merge)：or使用兩個索引的情況，即前面type講到的index_merge，這時候會將兩個索引樹查出的id取並集然後再回表在進行where條件過濾

    EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'

    image-20230414143026121

  • zero limit：limit為0的情況

  • file sort 檔案排序：

    • 有一些情況排序是能夠用到索引的：

      EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

      image-20230414144853029

      這個查詢利用idx_key1索引直接取出key1列的10條記錄（按照索引列排序的），然後再拿著記錄的主鍵值進行回表得到全部列的值。但是更多情況下的排序操作無法利用到索引，只能在記憶體中（記錄較少的情況）或者磁碟中進行排序，mysql把這種在記憶體或者磁碟中排序的方式統稱為檔案排序 file sort

      但是這裡有個地方很不理解，為什麼去掉limit或者limit較大的時候，就會變成檔案排序？

      EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 97;

      image-20230414145427834

      個人猜測：有一個地方需要注意，就是隨著limit的增大rows也在增大，尤其是在limit為95左右的時候突然增大了很多，這是不是因為：limit較小的時候，透過索引順序得到的主鍵值也比較集中，這時候回表操作也是順序查詢的級別，但是limit過大甚至沒有的時候，主鍵值就會特別分散（因為是按照key1索引列排序的，所以key1集中而主鍵值分散），因此這時候回表讀取操作相當於是隨機查詢的級別了，那這樣查詢最佳化器判斷成本後，還不如直接在記憶體或者磁碟中進行檔案排序。

    • 對於沒有索引的查詢，自然只能檔案排序了：

      EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;

      image-20230414150229592

  • using temporary：mysql在進行一些如去重、排序的功能的時候，如果不能夠有效地利用索引，就可能需要透過建立內部的臨時表來完成。

    EXPLAIN SELECT DISTINCT common_field FROM s1;

    image-20230414150617598

    執行計劃中出現臨時表不是一個很好的徵兆，因為建立和維護臨時表都需要很大的成本，應該儘量透過使用索引來替換臨時表

小結
  • Explain不考慮Cache（不考慮記錄的載入方式，只是考量sql語句）
  • Explain不能顯示mysql在執行查詢時做的最佳化工作
  • Explain不會顯示關於觸發器、儲存過程或使用者自定義函式對於查詢的影響
  • 部分資訊是估算的，並非精確的值
Explain的進一步使用
Explain的四種輸出格式

​ Explain的四種輸出格式：傳統格式、Json格式、Tree格式、視覺化格式

1 傳統格式

​ 即上面一直在使用的EXPLAIN語句，概要說明查詢計劃

2 JSON格式

​ 傳統的EXPLAIN語句的輸出缺少了一個衡量執行計劃好壞的重要屬性--成本。JSON格式是四種格式裡面資訊最詳盡的格式，包含了執行的成本資訊。 接下來對比一下傳統和JSON格式的EXPLAIN：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'

image-20230414152305899

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'

{
  "query_block": {
    "select_id": 1, // 原來的id
    "cost_info": {
      "query_cost": "1394.77" // 查詢成本
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s1", // table
          "access_type": "ALL", // type
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 10152, // rows
          "rows_produced_per_join": 1015, // rows * filtered
          "filtered": "10.00",
          "cost_info": {
            "read_cost": "937.93",
            "eval_cost": "101.52",
            "prefix_cost": "1039.45", // read + eval
            "data_read_per_join": "1M" // 讀取的資料量
          },
          "used_columns": [ // 查詢欄位
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "((`atguigudb1`.`s1`.`common_field` = 'a') and (`atguigudb1`.`s1`.`key1` is not null))" // 查詢條件
        }
      },
      {
        "table": {
          "table_name": "s2",
          "access_type": "eq_ref",
          "possible_keys": [
            "idx_key2"
          ],
          "key": "idx_key2",
          "used_key_parts": [
            "key2"
          ],
          "key_length": "5",
          "ref": [
            "atguigudb1.s1.key1"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1015,
          "filtered": "100.00",
          "index_condition": "(cast(`atguigudb1`.`s1`.`key1` as double) = cast(`atguigudb1`.`s2`.`key2` as double))",
          "cost_info": {
            "read_cost": "253.80",
            "eval_cost": "101.52",
            "prefix_cost": "1394.77",
            "data_read_per_join": "1M"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      }
    ]
  }
}
  • read_cost：由兩部分組成：
    • IO成本
    • rows * (1 - filtered)條記錄的CPU成本
  • eval_cost： rows * filtered
3 Tree格式

​ Tree格式是8.0.16版本之後引入的新格式，主要根據各個部分之間的關係和各個部分的執行順序來描述如何查詢。

EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'

-> Nested loop inner join  (cost=1394.77 rows=1015)
    -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null))  (cost=1039.45 rows=1015)
        -> Table scan on s1  (cost=1039.45 rows=10152)
    -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double))  (cost=0.25 rows=1)
4 視覺化輸出

​ 需要安裝MySQL workbench

image-20230414153738475

Show Warnings的使用

​ 當我們使用Explain語句檢視了某個查詢語句的執行計劃之後，緊接著還可以使用Show warnings來檢視與這個查詢計劃有關的一些擴充套件資訊，比如：

EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;

image-20230414154744913

​ 正常來說，我們使用s2 left join s1，那麼s2應該是驅動表，s1是被驅動表，但是可以看到執行計劃中實際上是反著的，這是由於最佳化器在判斷兩個表作為驅動表的執行成本的時候對sql進行的最佳化（where語句是針對的s2），使用show warnings可以看到這種最佳化：

mysql> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `atguigudb1`.`s1`.`key1` AS `key1`,`atguigudb1`.`s2`.`key1` AS `key1` from `atguigudb1`.`s1` join `atguigudb1`.`s2` where ((`atguigudb1`.`s1`.`key1` = `atguigudb1`.`s2`.`key1`) and (`atguigudb1`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)

​ 看著挺彆扭，即下面：

select s1.key1, s2.key1
from s1 join s2
where s1.key1 = s2.key1 and s2.common_field is not null;

相關文章