【七千字】教你如何用MySQL分析查詢語句Explain
分析查詢語句：EXPLAIN
1概述
定位了查詢慢的SQL之后，就可以使用EXPLAIN或者DESCRIBE工具做針對(duì)性的分析查詢。兩者使用方法相同，并且分析結(jié)果也是相同的。
MySQL中有專門負(fù)責(zé)SQL語句優(yōu)化的優(yōu)化器模塊，主要功能是計(jì)算分析系統(tǒng)中收集到的統(tǒng)計(jì)信息，為客戶端請(qǐng)求的Query提供它最優(yōu)的執(zhí)行計(jì)劃（它認(rèn)為的最優(yōu)數(shù)據(jù)檢索方案畢竟是自動(dòng)分析成的，所以不一定是DBA或者開發(fā)人員認(rèn)為的最優(yōu)方案）
這個(gè)執(zhí)行計(jì)劃展示了接下來進(jìn)行具體查詢的方式，比如多表連接的順序、對(duì)每個(gè)表采用什么方式進(jìn)行具體的查詢等等，MySQL提供的EXPLAIN語句可以用來查詢某個(gè)查詢語句的具體執(zhí)行計(jì)劃，根據(jù)EXPLAIN語句的輸出項(xiàng)，可以有針對(duì)性地提升查詢SQL的性能。
能查到什么？
表的讀取順序
數(shù)據(jù)讀取操作的操作類型
哪些索引可以被使用
哪些索引實(shí)際被使用
表之間的引用關(guān)系
每張表有多少行被優(yōu)化器查詢
版本區(qū)別
MySQL5.6.3之前只能使用EXPLAIN SELECT，之后可以使用EXPLAIN SELECT， UPDATE， DELETE
5.7之前的版本，想要查看partitions（分區(qū)）和filtered需要使用EXPLAIN partitions、filtered，而5.7之后直接默認(rèn)顯示
數(shù)據(jù)準(zhǔn)備
創(chuàng)建表
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=utf8CREATE 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
創(chuàng)建存儲(chǔ)函數(shù)
-- 函數(shù)返回隨機(jī)字符串
DELIMITER //CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8mb4BEGIN
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 ;
首先要確保相信函數(shù)的變量
log_bin_trust_function_creators為1
SELECT @@log_bin_trust_function_creators variable;SET GLOBAL log_bin_trust_function_creators = 1;
存儲(chǔ)過程
向s1、s2表添加數(shù)據(jù)的存儲(chǔ)過程
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;
執(zhí)行存儲(chǔ)過程添加數(shù)據(jù)
CALL insert_s1(10001, 10000);CALL insert_s2(10001, 10000);
Explain的輸出列
列名
描述
id
在一個(gè)大的查詢語句中每個(gè)SELECT關(guān)鍵字都對(duì)應(yīng)著一個(gè)唯一的id
select_type
SELECT關(guān)鍵字對(duì)應(yīng)查詢的類型
table
表名
partitions
匹配的分區(qū)信息
type
針對(duì)單表的訪問方法
possible_keys
可能使用到的索引
key
實(shí)際使用的索引
key_len
實(shí)際使用到的索引長(zhǎng)度
ref
當(dāng)使用索引列等值查詢的時(shí)候，與索引列進(jìn)行等值匹配的對(duì)象信息
rows
預(yù)估需要讀取的記錄條數(shù)
filtered
某個(gè)表經(jīng)過搜索條件過濾后剩余記錄條數(shù)的百分比
Extra
一些額外的信息
1 id
id，在一個(gè)大的查詢語句中每個(gè)SELECT關(guān)鍵字都對(duì)應(yīng)著一個(gè)唯一的id，所以有幾個(gè)select關(guān)鍵字就會(huì)有幾個(gè)id：
EXPLAIN SELECT * FROM s1
EXPLAIN SELECT * FROM s1 INNER JOIN s2
上面的兩個(gè)SQL都只有一個(gè)select所以只有一個(gè)id
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'
子查詢有兩個(gè)select，所以對(duì)應(yīng)兩個(gè)id1和2
查詢優(yōu)化器可能會(huì)對(duì)涉及子查詢的查詢語句進(jìn)行重寫：
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a')
優(yōu)化器在看到子查詢后判斷能夠變?yōu)槎啾磉B接以降低復(fù)雜度（O(n^2) -> O(n)）:
SELECT * FROM s1, s2 ON s1.key1 = s2.key2 WHERE s2.common_field = 'a'
重寫后的sql變成了一個(gè)select，所以查詢結(jié)果仍然是一個(gè)id
但是如果s2查的是key1，就會(huì)變成下面這樣：
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE common_field = 'a')
UNION去重
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
union由于去重操作會(huì)使用到中間表，所以會(huì)有一個(gè)table<union, 1, 2>
但是我這里的臨時(shí)表也有id = 3，看康師傅視頻是沒有的，是版本的問題嗎？也就是還對(duì)中間表進(jìn)行了select
如果使用的是UNION ALL不進(jìn)行去重，則是：
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
小結(jié)：
id如果相同，則會(huì)被認(rèn)為是同一組查詢，會(huì)按照從上往下的順序執(zhí)行
如果不同，則id越大的優(yōu)先級(jí)越高，越先執(zhí)行
id的號(hào)碼表示一趟獨(dú)立的查詢，一個(gè)sql的查詢趟數(shù)越少越好
2 select_type
?一個(gè)大的查詢里面可以包含多個(gè)select關(guān)鍵字，每個(gè)select關(guān)鍵字代表一個(gè)小的查詢語句，而每個(gè)小的查詢中都包含著若干的表進(jìn)行連接操作，而每一張表都對(duì)應(yīng)著EXPLAIN查詢計(jì)劃的一條記錄，對(duì)于在同一個(gè)select關(guān)鍵字的表來說，他們的id是相同的。
select_type：SELECT關(guān)鍵字對(duì)應(yīng)查詢的類型，即我們只要知道了某個(gè)小查詢的select_type屬性，就能知道這個(gè)小查詢?cè)诖蟛樵冎邪缪莸慕巧⑵鸬降淖饔?/strong>
常見的select_type：
SIMPLE：不包含UNION或者子查詢的查詢都算是SIMPLE類型
UNION、PRIMARY、UNION RESULT：對(duì)于包含UNION和UNION ALL的語句，它是由幾個(gè)小的查詢組成的，除了最左邊的查詢的select_type是PRIMARY，其余的均為UNION，而針對(duì)臨時(shí)表的select則是UNION RESULT
SUBQUERY：如果包含子查詢的查詢語句不能夠轉(zhuǎn)化為semi-join的方式（即優(yōu)化器將子查詢優(yōu)化為表連接），并且子查詢不是相關(guān)子查詢(即用到了外表的子查詢)，則該子查詢的第一個(gè)select關(guān)鍵字代表的那個(gè)查詢的select_type就是SUBQUERY
explain?select?*?from?s1?where?key1?in?(select?key1?from?s2)?or?key3?=?'a'
首先這個(gè)子查詢不是相關(guān)子查詢，那么這個(gè)sql能不能優(yōu)化成表連接的sql呢？
select?*?from?s1?INNER?JOIN?s2?on?s1.key1?=?s2.key1
答案是不能，這兩個(gè)sql是不同的：比如s1表中有一個(gè)key1值，s2表有兩個(gè)重復(fù)的key1值，則第一條語句由于是in，所以只會(huì)匹配一次，而第二條sql是等于號(hào)，所以這種情況下會(huì)匹配兩次，從而二個(gè)sql得到的結(jié)果是完全不同的，因此這個(gè)sql會(huì)用到兩個(gè)select，也就出現(xiàn)兩個(gè)id了，一個(gè)select為Primary，子查詢的select為subquery。
DEPENDENT SUBQUERY：如果包含子查詢的查詢語句不能夠轉(zhuǎn)化為semi-join的方式，但是子查詢涉及到了外表，也就是為相關(guān)子查詢，那么該子查詢的第一個(gè)select關(guān)鍵字代表的那個(gè)查詢的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 的查詢可能會(huì)被執(zhí)行多次
DEPENDENT UNION：在包含UNION和UNION ALL的大查詢中，如果各個(gè)小查詢都依賴于外層查詢的話，那除了最左邊的小查詢之外，其余查詢的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')
第二個(gè)子查詢UNION加上了DEPENDENT 好理解，因?yàn)橛玫搅送獗?/p>
但是，為什么第一個(gè)子查詢沒有用到外表，也是DEPENDENT SUBQUERY呢？
這是由于優(yōu)化器對(duì)于in的改動(dòng)：
where?exists?(s1.key1 = s2.key1 ...)，這樣就變?yōu)榱讼嚓P(guān)子查詢，至于為啥這么做完全不知道了。。
DERIVED：派生表對(duì)應(yīng)子查詢的select_type為DERIVED
EXPLAIN?SELECT?*?FROM?(SELECT?key1, count(*)?AS?c?FROM?s1?GROUP?BY?key1)?AS?derived_s1?WHERE?c >?1
<drived2>即為id為2的派生表
MATERIALIZED（物化）：當(dāng)查詢優(yōu)化器在執(zhí)行包含子查詢語句的時(shí)候，選擇將子查詢之后與外層查詢進(jìn)行連接時(shí)，該子查詢對(duì)應(yīng)的select_type就是MATERIALIZED
EXPLAIN?SELECT?*?FROM?s1?WHERE?key1?IN?(SELECT?key1?FROM?s2)
SELECT key1 FROM s2的結(jié)果是一個(gè)個(gè)的記錄然后與外表進(jìn)行連接，則這些記錄就可以被稱作是物化表，查詢方式為MATERIALIZED
而外層select直接將子查詢成的物化表看做普通的表，查詢方式為SIMPLE
這個(gè)和上面的非相關(guān)子查詢有點(diǎn)像，后面添加了一個(gè)or key3 = 'a'，非相關(guān)子查詢就變成物化表了？？？
EXPLAIN?SELECT?*?FROM?s1?WHERE?key1?IN?(SELECT?key1?FROM?s2)?or?key3?=?'a'
3 table
table，即表名
查詢出來的每一行記錄都對(duì)應(yīng)著一個(gè)單表
EXPLAIN?SELECT?*?FROM?s1
EXPLAIN?SELECT?*?FROM?s1, s2
可以看到兩個(gè)記錄的id是一樣的，因?yàn)閷儆谕粋€(gè)大的查詢語句（只有一個(gè)select）
并且s2排在s1的前面，所以s2是驅(qū)動(dòng)表，s1是被驅(qū)動(dòng)表（并不能根據(jù)sql語句判斷，因?yàn)閟ql的順序有可能被優(yōu)化器優(yōu)化修改）
4 partitions
代表分區(qū)表中的命中情況，非分區(qū)表，該值為NULL，一般情況下我們查詢語句執(zhí)行計(jì)劃的partitions列的值也都是NULL
5 type
執(zhí)行計(jì)劃的一條記錄就代表著MySQL對(duì)某個(gè)表的執(zhí)行查詢時(shí)的訪問方法，又稱訪問類型，即這里的type。比如，type是ref，表名mysql將使用ref方法對(duì)改行記錄的表進(jìn)行查詢。
完整的訪問方法如下：system?>?const?>?eq_ref?>?ref?> fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >?range?>?index?> all，越靠前代表效率越高
?SQL性能優(yōu)化的目標(biāo)：至少要達(dá)到range級(jí)別，要求是ref級(jí)別，最好是const級(jí)別。
system：當(dāng)表中只有一條記錄，并且該表使用的存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)是精確的，比如MyISAM、Memory，那么對(duì)該表的訪問方法就是system
CREATE?TABLE?t(i?INT) ENGINE=MYISAM;?INSERT?INTO?t?VALUES(1); EXPLAIN?SELECT?*?FROM?t
存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)是精確的，意思是例如MyISAM存儲(chǔ)存儲(chǔ)引擎有記錄的記錄的個(gè)數(shù)
system是性能最高的情況
而如果再添加一條記錄，會(huì)變?yōu)閍ll，而InnoDB即使一條數(shù)據(jù)也是all
于此同時(shí)，INNODB訪問count()的數(shù)據(jù)也是all的
CREATE?TABLE?tt(i?INT) ENGINE=INNODB;?INSERT?INTO?tt?VALUES(1); EXPLAIN?SELECT?count(*)?FROM?tt
const：當(dāng)根據(jù)主鍵或者唯一的二級(jí)索引與常數(shù)進(jìn)行等值匹配的時(shí)候，對(duì)單表的訪問就是const，表示常數(shù)級(jí)別
EXPLAIN?SELECT?*?FROM?s1?WHERE?id?=?10005; EXPLAIN?SELECT?*?FROM?s1?WHERE?key2?=?10066;
如果是key3，則為all
EXPLAIN?SELECT?*?FROM?s1?WHERE?key3?=?1006;
這里其實(shí)牽扯到隱式轉(zhuǎn)換導(dǎo)致索引失效的問題：由于key3是varchar類型的，但是這里是數(shù)字進(jìn)而進(jìn)行了函數(shù)轉(zhuǎn)換，進(jìn)而索引失效只能all查詢了
eq_ref：在連接查詢的時(shí)候，如果被驅(qū)動(dòng)表是通過主鍵或者唯一的二級(jí)索引等值匹配的方式進(jìn)行訪問的（如果主鍵或者唯一的二級(jí)索引是聯(lián)合索引，則要求索引的每一列進(jìn)行聯(lián)合匹配），則對(duì)于該被驅(qū)動(dòng)表的訪問方式就是eq_ref
EXPLAIN?SELECT?*?from?s1?INNER?JOIN?s2?WHERE?s1.key2?=?s2.key2
key2是帶有唯一約束的二級(jí)索引，因此被驅(qū)動(dòng)表s2的訪問方式為eq_ref
其中ref表示查詢的值已經(jīng)被指定：即通過all方式查詢的s1表指定的
ref：當(dāng)通過普通的二級(jí)索引與常量進(jìn)行等值匹配來查詢某個(gè)表，對(duì)該表的訪問方式可能是ref
EXPLAIN?SELECT?*?FROM?s1?WHERE?key3?=?'CUTLVwqweqweq';
這里key3就是沒有唯一約束的普通索引，可以看到用到了索引key3，因此type為ref
ref_or_null：當(dāng)通過普通的二級(jí)索引與常量進(jìn)行等值匹配來查詢某個(gè)表，當(dāng)該值也可能是null值時(shí)，那么對(duì)該 表的訪問方式可能就是ref_not_null
EXPLAIN SELECT * FROM s1?WHERE?key3?=?'CUTLVwqweqweq'?OR key3 IS NULL;
index_merge：?jiǎn)伪碓L問在某些情況下可以使用Intersection、Union、Sort-Union這三種索引合并的方式來執(zhí)行查詢
EXPLAIN?SELECT?*?FROM?s1?WHERE?key1?=?'a'?OR?key2?=?123131
key1和key2均為索引列，一個(gè)Select關(guān)鍵字只能使用一個(gè)索引，所以這里使用了合并索引為一個(gè)虛擬索引的辦法，相當(dāng)于掃描兩個(gè)索引樹取出主鍵并取并集再回表的操作
但是，如果是AND的情況，只會(huì)使用一個(gè)索引（這里是唯一的二級(jí)索引。故而是const）
EXPLAIN?SELECT?*?FROM?s1?WHERE?key1?=?'rCLXEg'?AND?key2?=?10036
unique_subquery：是針對(duì)一些包含IN子查詢的查詢語句中，如果查詢優(yōu)化器決定將In子查詢語句變?yōu)镋XISTS子查詢，并且子查詢可以使用到主鍵的等值匹配的話，那么子查詢的type就是unique_subquery
EXPLAIN?SELECT?*?FROM?s1?WHERE?key2?IN?(SELECT?id?FROM?s2?WHERE?s1.key1?=?s2.key1)?OR?key3?=?'a'
range：如果使用索引獲取某些范圍區(qū)間的記錄，就可能使用到range方法
EXPLAIN?SELECT?*?FROM?s1?WHERE?key1?IN?('a',?'b',?'c')
非索引列則為all
index：當(dāng)可以使用索引覆蓋，并且需要掃描全部的索引記錄，該表的訪問方法就是index
EXPLAIN?SELECT?key_part2?FROM?s1?WHERE?key_part3?=?'a'
可以看到在key里面還是用到了聯(lián)合索引的，盡管根據(jù)最左前綴原則，只有檢索條件為key_part1才能用到索引，這里是因?yàn)?strong>檢索條件和select返回列都是和聯(lián)合索引相關(guān)的列，所以使用了聯(lián)合索引掃描了全部的索引記錄，因?yàn)檫@樣就不需要再回表找其他的列了（查的列都在索引上）
不需要回表就能查找到所需要的數(shù)據(jù)，稱作索引覆蓋
這時(shí)候再添加一個(gè)其他的列：
EXPLAIN?SELECT?key1, key_part2?FROM?s1?WHERE?key_part3?=?'a'
結(jié)果為ALL，因?yàn)槁?lián)合索引列上沒有key1的信息，需要回表去查key1
all：全表掃描
6 possible_key 和 key
在EXPLAIN語句輸出的執(zhí)行計(jì)劃中，possible_key?表示在單表查詢中可能會(huì)用到的索引，一般查詢涉及到的字段上存在索引，則該索引就將被列出，但不一定被查詢使用。
?key則表示經(jīng)過查詢優(yōu)化器計(jì)算使用不同索引的查詢成本之后，最終確定使用的索引。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a'
key1和key3均為普通的二級(jí)索引，但是key3是等值匹配因此耗費(fèi)的成本較低，所以最終選擇使用索引key3
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' OR key3 = 'a'
而如果這里改成OR，則會(huì)演變成之前講的?index_merge 合并索引，即將兩個(gè)索引樹的主鍵提取取并集，然后統(tǒng)一到聚簇索引中執(zhí)行一次回表操作
EXPLAIN SELECT key1, key3 FROM s1 WHERE key1 > 'z' OR key3 = 'a'
再擴(kuò)展一下，即使查詢列可以使用覆蓋索引（即查詢列的值都可以在索引樹中找到），仍然需要進(jìn)行一次回表操作，因此兩個(gè)查詢的執(zhí)行計(jì)劃是相同的：
7 index_len （聯(lián)合索引分析）
?實(shí)際使用到的索引的長(zhǎng)度（即字節(jié)數(shù)），用來查看是否充分利用了索引，index_len的值越大越好
這里的越大越好是跟自己進(jìn)行的比較，因?yàn)橹饕轻槍?duì)的聯(lián)合索引，因?yàn)槔寐?lián)合索引的長(zhǎng)度越大，查詢需要讀入的數(shù)據(jù)頁就越少，效率也就越高
EXPLAIN SELECT * FROM s1 WHERE id = 10005
為什么是4：因?yàn)閕d列是int型所以真實(shí)數(shù)據(jù)占4個(gè)字節(jié)，同時(shí)行格式中主鍵非空因此不需要NULL值列表，定長(zhǎng)不需要變長(zhǎng)字段長(zhǎng)度列表，故而是4
EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
key2是int類型，占4個(gè)字節(jié)，并且具有唯一性約束但是可能為空，因此行格式中null值列表占1個(gè)字節(jié)，總共5個(gè)字節(jié)
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
首先key1是varchar(100)，并且表是utf8mb3格式的，因此真實(shí)數(shù)據(jù)存儲(chǔ)占(100 * 3) = 300個(gè)字節(jié)，本身定長(zhǎng)所以行格式的變長(zhǎng)字段長(zhǎng)度列表占2個(gè)字節(jié)，NULL值列表占1個(gè)字節(jié)，共計(jì)303個(gè)字節(jié)
同理下面的查詢一個(gè)為303，另一個(gè)是606，這時(shí)候才體現(xiàn)出key_len的作用：第二個(gè)sql比第一個(gè)sql利用聯(lián)合索引更加充分
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
8 ref
?ref表示當(dāng)我們使用索引列等值查詢的時(shí)候，與索引列進(jìn)行等值匹配的對(duì)象的信息。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
key1是普通的二級(jí)索引，所以type是ref（唯一的二級(jí)索引是const），而等值的匹配類型是一個(gè)常量，因此ref列的值是const
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
由于是表連接，所以只有一個(gè)select id，然后由于是主鍵進(jìn)行的連接，所以對(duì)于第二個(gè)表的訪問方式type是eq_ref（普通索引則為ref），同時(shí)等值比較的是s1的列，因此ref為atguigu1.s2.id
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
key1是普通的二級(jí)索引，因此type為ref，等值比較的類型是一個(gè)函數(shù)返回值，因此ref列的值為func
9 rows
?rows：預(yù)估需要讀取的記錄條數(shù)，值越小越好
值越小表示在同一個(gè)數(shù)據(jù)頁中的可能性越大，IO的次數(shù)也就越少
10 filtered （結(jié)合rows分析）
?filtered：表示某個(gè)表經(jīng)過條件過濾之后，剩余記錄條數(shù)的百分比，值越大越好
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
如上表示經(jīng)過條件過濾后，100%都是符合要求的
值越大越好的原因：假設(shè)條件過濾后是40條記錄，如果filtered是100%，則原來有40條，如果filtered是10%，則原來有400條，相比之下40條需要讀取的數(shù)據(jù)頁要少一些
?而如果執(zhí)行的是索引的單表掃描，那么計(jì)算的時(shí)候除了估計(jì)出滿足對(duì)應(yīng)索引的搜索條件，還應(yīng)計(jì)算同時(shí)滿足其他條件的記錄是多少條
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'b';
如上面的sql，rows303表示預(yù)估滿足索引列key1需要讀取的記錄數(shù)，而filtered表示加上common_field字段后預(yù)估讀取占全部的百分比
?對(duì)于單表查詢這個(gè)filtered列其實(shí)沒有太大作用，但是它在多表連接中驅(qū)動(dòng)表對(duì)應(yīng)的執(zhí)行計(jì)劃記錄的filtered值，決定了被驅(qū)動(dòng)表的執(zhí)行次數(shù)。
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
首先多表連接查詢所以為同一個(gè)select id，其次連接條件是普通的二級(jí)索引，所以驅(qū)動(dòng)表的訪問類型type為all，被驅(qū)動(dòng)表的訪問類型type為ref，最后s1表預(yù)估讀取的記錄數(shù)rows為10152，再經(jīng)過條件過濾10152 * 10%和s2做等值匹配，因此1015就是s2表的執(zhí)行次數(shù)
11 Extra
Extra用來說明一些不適合在其他列中展示但是十二分重要的額外信息。通過這些額外信息可以更準(zhǔn)確地知道m(xù)ysql導(dǎo)致是怎么執(zhí)行給定的查詢語句的。
no tables used：沒有from字句，即沒有用到表的情況
EXPLAIN?select?1
impossible where：where語句永遠(yuǎn)為false的情況
EXPLAIN?select?*?FROM?s1?WHERE?1?!=?1
這樣也沒有用到表，反正條件都不對(duì)
where：使用全表掃描來執(zhí)行針對(duì)某個(gè)表的查詢，字句中有針對(duì)該表的搜索條件，則在Extra中展現(xiàn)
EXPLAIN?select?*?FROM?s1?WHERE?common_field?=?'a'
common_field是一個(gè)不帶索引的普通字段，因此type為all，Extra展現(xiàn)了語句是通過where執(zhí)行的
no matching min/max row當(dāng)查詢列表處有min或者max聚合函數(shù)，但是沒有符合where條件的記錄時(shí)，將會(huì)提示該額外信息
?EXPLAIN?SELECT?MIN(key1)?FROM?s1?WHERE?key1?=?'adqwdqweqwe'
而當(dāng)where條件符合（或者根本沒有where條件的時(shí)候），則顯示Select tables optimized away，表示選擇優(yōu)化后的表
EXPLAIN?SELECT?MIN(key1)?FROM?s1
using index：當(dāng)出現(xiàn)索引覆蓋，即查詢和檢索條件的列都在使用的索引里面，也即是不需要回表操作的情況
EXPLAIN?SELECT?key1?FROM?s1?WHERE?key1?=?'a'
當(dāng)出現(xiàn)主鍵的情況也是覆蓋索引
using index condition：即索引條件下推，考慮下面的sql查詢：
EXPLAIN?SELECT?*?FROM?s1?WHERE?key1?>?'z'?AND?key1?like?'%a%'
這條sql執(zhí)行的正常順序應(yīng)該是：首先使用idx_key1的索引樹，查詢key1 > z的所有主鍵值，這里找到了385條記錄的主鍵，然后對(duì)這些主鍵進(jìn)行回表操作，在聚簇索引中找到包含其他列的數(shù)據(jù)，然后判斷剩下的過濾條件進(jìn)行返回。
而索引條件下推針對(duì)特殊情況進(jìn)行了優(yōu)化：就是如果剩余的過濾條件針對(duì)的是索引列，則不需要在回表后進(jìn)行判斷，這樣就能夠減少回表的操作，但是rows仍為385
using join buffer：即基于塊的嵌套循環(huán)算法：當(dāng)被驅(qū)動(dòng)表不能夠有效利用索引加快訪問速度，mysql就會(huì)為其在內(nèi)存中分配一塊join buffer的內(nèi)存塊來加快訪問的速度
EXPLAIN?SELECT?*?FROM?s1?INNER?JOIN?s2?ON?s1.common_field?=?s2.common_field
common_field是一個(gè)沒有索引的列
not exists：在表連接的時(shí)候，當(dāng)where條件中被驅(qū)動(dòng)表的某個(gè)列等于null，而這個(gè)列又有非空約束的時(shí)候，Extra就會(huì)展現(xiàn)not exists
EXPLAIN?SELECT?*?FROM?s1?LEFT?JOIN?s2?on?s1.key1?=?s2.key1?WHERE?s2.id?IS?NULL
注意一定是被驅(qū)動(dòng)表的列，如果是主驅(qū)動(dòng)表出現(xiàn)這種情況，會(huì)直接顯示為impossible where，就不會(huì)再看被驅(qū)動(dòng)表了
using union(index_merge)：or使用兩個(gè)索引的情況，即前面type講到的index_merge，這時(shí)候會(huì)將兩個(gè)索引樹查出的id取并集然后再回表在進(jìn)行where條件過濾
EXPLAIN?SELECT?*?FROM?s1?WHERE?key1?=?'a'?OR?key3?=?'a'
zero limit：limit為0的情況
file sort 文件排序：
有一些情況排序是能夠用到索引的：
EXPLAIN?SELECT?*?FROM?s1?ORDER?BY?key1 LIMIT?10;
這個(gè)查詢利用idx_key1索引直接取出key1列的10條記錄（按照索引列排序的），然后再拿著記錄的主鍵值進(jìn)行回表得到全部列的值。但是更多情況下的排序操作無法利用到索引，只能在內(nèi)存中（記錄較少的情況）或者磁盤中進(jìn)行排序，mysql把這種在內(nèi)存或者磁盤中排序的方式統(tǒng)稱為文件排序 file sort
但是這里有個(gè)地方很不理解，為什么去掉limit或者limit較大的時(shí)候，就會(huì)變成文件排序？
EXPLAIN?SELECT?*?FROM?s1?ORDER?BY?key1 LIMIT?97;
個(gè)人猜測(cè)：有一個(gè)地方需要注意，就是隨著limit的增大rows也在增大，尤其是在limit為95左右的時(shí)候突然增大了很多，這是不是因?yàn)椋簂imit較小的時(shí)候，通過索引順序得到的主鍵值也比較集中，這時(shí)候回表操作也是順序查詢的級(jí)別，但是limit過大甚至沒有的時(shí)候，主鍵值就會(huì)特別分散（因?yàn)槭前凑誯ey1索引列排序的，所以key1集中而主鍵值分散），因此這時(shí)候回表讀取操作相當(dāng)于是隨機(jī)查找的級(jí)別了，那這樣查詢優(yōu)化器判斷成本后，還不如直接在內(nèi)存或者磁盤中進(jìn)行文件排序。
對(duì)于沒有索引的查詢，自然只能文件排序了：
EXPLAIN?SELECT?*?FROM?s1?ORDER?BY?common_field LIMIT?10;
using temporary：mysql在進(jìn)行一些如去重、排序的功能的時(shí)候，如果不能夠有效地利用索引，就可能需要通過建立內(nèi)部的臨時(shí)表來完成。
EXPLAIN?SELECT?DISTINCT?common_field?FROM?s1;
執(zhí)行計(jì)劃中出現(xiàn)臨時(shí)表不是一個(gè)很好的征兆，因?yàn)榻⒑途S護(hù)臨時(shí)表都需要很大的成本，應(yīng)該盡量通過使用索引來替換臨時(shí)表
小結(jié)
Explain不考慮Cache（不考慮記錄的加載方式，只是考量sql語句）
Explain不能顯示mysql在執(zhí)行查詢時(shí)做的優(yōu)化工作
Explain不會(huì)顯示關(guān)于觸發(fā)器、存儲(chǔ)過程或用戶自定義函數(shù)對(duì)于查詢的影響
部分信息是估算的，并非精確的值
Explain的進(jìn)一步使用
Explain的四種輸出格式
Explain的四種輸出格式：傳統(tǒng)格式、Json格式、Tree格式、可視化格式
1 傳統(tǒng)格式
即上面一直在使用的EXPLAIN語句，概要說明查詢計(jì)劃
2 JSON格式
?傳統(tǒng)的EXPLAIN語句的輸出缺少了一個(gè)衡量執(zhí)行計(jì)劃好壞的重要屬性--成本。JSON格式是四種格式里面信息最詳盡的格式，包含了執(zhí)行的成本信息。 接下來對(duì)比一下傳統(tǒng)和JSON格式的EXPLAIN：
EXPLAIN SELECT * FROM s1 INNER JOIN s2 on s1.key1 = s2.key2 WHERE s1.common_field = 'a'
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" // 讀取的數(shù)據(jù)量
? ? ? ? ?}, ? ? ? ? ?"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版本之后引入的新格式，主要根據(jù)各個(gè)部分之間的關(guān)系和各個(gè)部分的執(zhí)行順序來描述如何查詢。
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
Show Warnings的使用
當(dāng)我們使用Explain語句查看了某個(gè)查詢語句的執(zhí)行計(jì)劃之后，緊接著還可以使用Show warnings來查看與這個(gè)查詢計(jì)劃有關(guān)的一些擴(kuò)展信息，比如：
EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 on s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
正常來說，我們使用s2 left join s1，那么s2應(yīng)該是驅(qū)動(dòng)表，s1是被驅(qū)動(dòng)表，但是可以看到執(zhí)行計(jì)劃中實(shí)際上是反著的，這是由于優(yōu)化器在判斷兩個(gè)表作為驅(qū)動(dòng)表的執(zhí)行成本的時(shí)候?qū)ql進(jìn)行的優(yōu)化（where語句是針對(duì)的s2），使用show warnings可以看到這種優(yōu)化：
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)
SQL 復(fù)制 全屏
看著挺別扭，即下面：
select s1.key1, s2.key1from s1 join s2where s1.key1 = s2.key1 and s2.common_field is not null;
