前言

過年回來的第二周了，終于有時間繼續(xù)總結知識了。這次來看一下SQL調(diào)優(yōu)的知識，這類問題基本上面試的時候都會被問到，無論你的崗位是后端，運維，測試等等。
像本文標題中的兩個問題，就是我在實際面試過程中遇到的，所以這次就主要圍繞著這兩個問題來總結一下。

本文的重點在第二部分，請耐心看完！

explain 查詢SQL執(zhí)行計劃

我們在想知道一條SQL的執(zhí)行計劃時，是可以通過Explain關鍵字來模擬優(yōu)化器執(zhí)行SQL查詢語句，進而來分析SQL的語句。

舉例??：

創(chuàng)建如下數(shù)據(jù)表

CREATE TABLE `test_score` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '名稱',
  `subject` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '科目',
  `score` int(10) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name_score` (`name`,`score`) USING BTREE COMMENT '學生名與成績的聯(lián)合索引',
  KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '創(chuàng)建時間的索引'
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COMMENT='學生科目成績表';

在上面的學生科目成績表中，我創(chuàng)建了兩個索引，一個是name和score的聯(lián)合索引，一個是create_time的索引。

然后向test_score表中插入10w條數(shù)據(jù)，通過存儲過程的方式添加：

DROP PROCEDURE  IF EXISTS  insert_test_score;
CREATE DEFINER = `root` @`%` PROCEDURE `insert_test_score` ( ) 
 BEGIN
	DECLARE
		c_id INTEGER DEFAULT 1;
	WHILE
			c_id <= 100000 DO
			INSERT INTO test_score
		VALUES
			( c_id, concat( '李明明', c_id ), 
			convertSubject(RAND()*10),
			RAND()*100, 
			date_sub( NOW( ), INTERVAL c_id SECOND ) );
		
		SET c_id = c_id + 1;
		
	END WHILE;
END

自定義函數(shù)：convertSubject內(nèi)容如下：

-- 自定義函數(shù)
delimiter $$
DROP FUNCTION IF EXISTS convertSubject $$
create function `convertSubject`(xis int) returns VARCHAR(50)
begin
		CASE xis
		WHEN 1 THEN RETURN '數(shù)學';
		WHEN 2 THEN RETURN '語文';
		WHEN 3 THEN RETURN "英語";
		WHEN 4 THEN RETURN "物理";
		WHEN 5 THEN RETURN "化學";
		WHEN 6 THEN RETURN "生物";
		WHEN 7 THEN RETURN "政治";
		WHEN 8 THEN RETURN "歷史";
		WHEN 9 THEN RETURN "地理";
		ELSE  RETURN "體育";
		END CASE ;
end
$$
delimiter ;

執(zhí)行存儲過程插入數(shù)據(jù)：

call insert_test_score();

數(shù)據(jù)插入成功后，我們查看一個查詢SQL語句的執(zhí)行計劃：

EXPLAIN select * from test_score where name = '李明明5';

運行結果：

這里解釋一下，Explain執(zhí)行計劃結果每個字段的含義：

id

id代表的主要是SQL語句的執(zhí)行順序，主要分兩種情況：

1. id相同

如果只有一條SQL的單表查詢（不含中間表）的情況，那么id就是1，如：

如果存在中間表查詢的SQL，這時候會有多條執(zhí)行結果，但是id值相同，這個時候代表的執(zhí)行順序是由上到下的順序。

EXPLAIN select t2.name,t1.score 
from test_score t1 join subject_score t2 on t1.id = t2.id where t2.id = 1;

1. id不相同
   當id值不同時，值越大優(yōu)先級越高，也就是說，值越大的越先執(zhí)行。

EXPLAIN select `name`,score from test_score t1 where id = (SELECT id from test_score t2 where t2.id =1);

select_type

select_type代表的是查詢類型，主要是區(qū)別于簡單查詢、聯(lián)合查詢、子查詢等查詢類型。
主要有以下幾種類型：

1. simple：簡單查詢，指SQL中不包含子查詢或關聯(lián)查詢。
2. primary：若SQL中包含子查詢，那么最外層的查詢將被標記為primary。
3. subquery：執(zhí)行的SQL中，在select或where中包含了子查詢，那么子查詢將被標記為subquery。
4. derived：在from列表中包含的子查詢會被標記為derived，MySQL會先把這些derived的查詢結果放到臨時表中，然后再為下一步的查詢使用。
5. union：若第二個select出現(xiàn)在union之后，則會被標記為union，如果union包含在form子句的子查詢中，外層select被標記為derived。
6. union result：union 的結果。

table

數(shù)據(jù)輸入行所引用的表名（若表名有別名，則展示別名）。

Type

主要是顯示連接類型，有如下幾種：

1. system：表中僅有一行數(shù)據(jù)（一般系統(tǒng)表才展示此類型），這是const聯(lián)結類型的一個特例。
2. const：通過索引一次就找到，const用于比較primary
   key或者unique索引。因為只匹配一行數(shù)據(jù)，所以如果將主鍵置于where列表中，mysql能將該查詢轉換為一個常量。
3. eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于唯一索引或者主鍵掃描。
4. ref：非唯一索引掃描，返回匹配某個單獨值的所有行，可能會返回多行，本質上也可以歸為一直索引掃描，當使用二級索引時，一般都會是ref的連接類型。
5. range：檢索給定范圍的行，使用一個索引來選擇數(shù)據(jù)范圍。key列顯示使用了哪個索引，一般就是where語句中出現(xiàn)了between,in等范圍的查詢。這種范圍掃描索引掃描比全表掃描要好，因為它開始于索引的某一個點，而結束另一個點，不用全表掃描。
6. index：index與all區(qū)別為index類型只遍歷索引樹。通常比all快，因為索引文件通常比數(shù)據(jù)文件小很多。
7. all：通過全表掃描才找到匹配的數(shù)據(jù)。

possible_keys

指MySQL能使用哪個索引在該表中找到行，一般真正使用的索引都在possible_keys展示的索引中。

key

SQL在執(zhí)行的時候實際走的索引名稱，如果沒有走索引，那么此值為Null。

key_len

表示索引中使用的字節(jié)數(shù)，該列計算查詢中使用的索引的長度在不損失精度的情況下，長度越短越好。如果鍵是NULL,則長度為NULL。該字段顯示為索引字段的最大可能長度，并非實際使用長度。

ref

ref列顯示使用哪個列或常數(shù)與key一起從表中選擇行，如果值為const代表的是常數(shù)。

rows

根據(jù)表統(tǒng)計信息以及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)。

filtered

表示返回結果的行數(shù)占需讀取行數(shù)的百分比 Filtered列的值越大越好 Filtered列的值依賴于統(tǒng)計信息。

Extra

指不適合在其他列中顯示，但是十分重要的額外信息。
主要有如下幾個值：

1. Using filesort：說明mysql會對數(shù)據(jù)適用一個外部的索引排序。而不是按照表內(nèi)的索引順序進行讀取。MySQL中無法利用索引完成排序操作稱為“文件排序”。
2. Using temporary：使用了臨時表保存中間結果，mysql在查詢結果排序時使用臨時表。常見于排序order by和分組查詢group by。
3. Using index：表示相應的select操作用使用覆蓋索引，避免訪問了表的數(shù)據(jù)行。如果同時出現(xiàn)using where，表名索引被用來執(zhí)行索引鍵值的查找；如果沒有同時出現(xiàn)using where，表示索引用來讀取數(shù)據(jù)而非執(zhí)行查詢動作。
4. Using where ：表明使用where過濾。
5. Using join buffer：使用了連接緩存。
6. impossible where：where子句的值總是false，不能用來獲取任何元組。
7. select tables optimized away：在沒有group by子句的情況下，基于索引優(yōu)化Min、max操作或者對于MyISAM存儲引擎優(yōu)化count（*），不必等到執(zhí)行階段再進行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化。
8. distinct：優(yōu)化distinct操作，在找到第一匹配的元組后即停止找同樣值的動作。

通過對Explain執(zhí)行計劃結果的了解，我們就可以在分析自己寫的SQL時應該做哪些優(yōu)化，這些操作是必須掌握的一些技能，還有就是要了解一下哪些情況下會造成索引失效，例如：對字段進行計算后查詢或者是like '%***'，字段類型隱式轉換等等。
還有就是盡量避免回表，能用覆蓋索引完成查詢最好，用到文件排序時，盡量避免產(chǎn)生filesort等等。

MySQL在執(zhí)行時是如何選擇索引的？

通過上面我們對Explain執(zhí)行計劃的各個字段內(nèi)容的了解，我們知道了，MySQL在執(zhí)行的SQL的時候，最終的一個執(zhí)行計劃是什么樣的。

但是，不知道你們有沒有遇到過這種情況，自己寫了一個SQL，認為它會走某一個索引，結果卻是走的全表掃描，沒有走任何索引。還有就是，自己寫的SQL，認為應該走A索引，但是通過Explain查看后，結果是走的B索引。

這些情況，其實是MySQL的查詢優(yōu)化器在對你的SQL進行分析后最終用了成本最小的執(zhí)行計劃。這說明有的時候MySQL認為掃描全表比走索引的成本更小。

那么MySQL的查詢優(yōu)化器是如何對SQL進行優(yōu)化的呢？怎么就選出來了一個成本最低的策略呢。多個索引的時候，應該走哪個索引呢？

我們下面來慢慢分析。

查詢成本

MySQL在執(zhí)行查詢之前，會先對可能的方案做執(zhí)行計劃，然后在根據(jù)成本決定使用哪個執(zhí)行計劃。

這里說的成本是指：IO成本和CPU成本。

• IO成本是指，MySQL讀取數(shù)據(jù)的時候會將數(shù)據(jù)從磁盤讀取到內(nèi)存中，讀取數(shù)據(jù)的單位是數(shù)據(jù)頁，每一頁為16KB，所以讀取數(shù)據(jù)頁的成本常數(shù)記做1（1頁的成本為1）。
• CPU成本是指，查詢數(shù)據(jù)是否滿足查詢條件或排序條件的CPU的執(zhí)行成本。默認情況下，檢測記錄成本常數(shù)記錄為0.2（這里是指檢測每一行數(shù)據(jù)的成本）。

基于執(zhí)行成本，我們來看一下，全表掃描的成本，還是基于上面的我們已經(jīng)創(chuàng)建好的test_score表為例，通過如下SQL，查詢出MySQL本身為每個表維護的統(tǒng)計數(shù)據(jù)。

SHOW TABLE STATUS LIKE 'test_score'

輸出結果：

• 通過這個結果我們可以看到，test_score表中總行數(shù)是99869行。我們之前不是插入了10w行嗎？怎么少了100多行數(shù)據(jù)？其實這里的總行數(shù)只是MySQL的一個估算值，但是這個估算值并不影響我們計算成本，我們上面說了，單條記錄的CPU成本常數(shù)是0.2，那么99869*0.2=19974左右。
• 表中的數(shù)據(jù)總長度是6832128字節(jié)，InnoDB每一頁的數(shù)據(jù)是16KB，數(shù)據(jù)總長度是417頁，因此，IO成本就是417*1=417左右。

所以全表掃描的成本大概是19974+417=20391。

統(tǒng)計成本

接下來，我們以一個SQL為例來說明一下，執(zhí)行成本統(tǒng)計情況。
還是以上面我們已經(jīng)創(chuàng)建好的數(shù)據(jù)表test_score為例。

EXPLAIN select * from test_score 
where name > '李明明90017' and create_time < '2021-02-26 18:00:00';

上面這條SQL的執(zhí)行計劃結果是全表掃描：

但是只要我們把create_tieme的參數(shù)從18點改為17點，執(zhí)行計劃顯示就會走索引了，并且走的是create_time的索引，而不是name字段的聯(lián)合索引。

EXPLAIN select * from test_score 
where name > '李明明90017' and create_time < '2021-02-26 17:00:00';

執(zhí)行計劃：

通過上面的例子我們可以看到，同樣的查詢字段，傳入不同的值，有的就會走索引，有的確不走索引，并且MySQL選擇的索引，也并不是根據(jù)where后面的查詢字段的順序來選擇的。

產(chǎn)生這樣結果的原因，就是MySQL基于成本來選擇了最優(yōu)的計劃來執(zhí)行了SQL。那么MySQL到底是怎么制定執(zhí)行計劃以及做出選擇計劃的依據(jù)是什么呢？

MySQL執(zhí)行計劃的選擇過程。

在MySQL5.6及之后的版本中，我們可以通過optimizer_trace功能來查看優(yōu)化器生成執(zhí)行計劃的整個過程。通過這個功能，我們可以了解MySQL每個計劃的成本，然后來進一步對查詢進行優(yōu)化。

optimizer_trace功能，默認是關閉的。可通過如下代碼打開后，再執(zhí)行具體的SQL，然后通過information_schema.OPTIMIZER_TRACE 表查看執(zhí)行計劃，最后記得手動再關閉optimizer_trace功能。

SET optimizer_trace="enabled=on";
select * from test_score 
where name > '李明明90017' and create_time < '2021-02-26 17:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

執(zhí)行后的結果是個大JSON，內(nèi)容比較多，所以我就只截取了關鍵部分。

我們先來看走idx_name_score索引時執(zhí)行計劃：

我們看到，走idx_name_score索引，要掃描數(shù)據(jù)21474行，成本（cost）是：25770。

再來看走idx_create_time時的執(zhí)行計劃：

這里看到，走idx_create_time時，掃描數(shù)據(jù)6805行，成本（cost）是8167，比走idx_name_score索引成本要低。所以MySQL最終選擇的是走idx_create_time的執(zhí)行計劃。

最后再來將create_time的參數(shù)改為18點，然后看一下全表掃描的執(zhí)行時間。

SET optimizer_trace="enabled=on";
select * from test_score where name > '李明明90017' and create_time < '2021-02-26 18:00:00';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

我們看到全表掃描要檢索數(shù)據(jù)99869行，執(zhí)行成本（cost）是20391。這個20391正好是我們上面在統(tǒng)計全表掃描時計算出來的成本。

MySQL其實是將這三個執(zhí)行計劃成本進行比較，然后選取一個成本最小的，然后來執(zhí)行。

通過optimizer_trace功能，我們了解到了，MySQL是如何選擇執(zhí)行計劃的，從而了解到MySQL是如何選擇該使用哪個索引的。

其實，MySQL的執(zhí)行計劃選擇，也并不是百分百準確的，有的時候，他計算出來的成本并不一定準確，所以我們可以強制讓某條SQL使用指定的索引，還是拿上面的那條SQL舉例：

-- 全表掃描，耗時3.545s
select * from test_score  
where name > '李明明90017' and create_time < '2021-02-26 18:00:00';

-- 強制走索引，耗時3.088s
select * from test_score force index(idx_create_time) 
where name > '李明明90017' and create_time < '2021-02-26 18:00:00';

好了，這次內(nèi)容有點多，前面說Explain的部分有點太基礎了，大部分人都會看，但是后面的optimizer_trace相關的知識還是比較有意思的。

總結

最后總結一下，我把Explain的各字段值的相關信息，做了一個表格。

optimizer_trace相關的知識，這個就看個人興趣了，知道有這么個功能就可以了，用到機會不是太多。

最后的最后

用上面的例子的test_score表，來分享一道我面試中遇到的SQL題。

還是科目成績表，根據(jù)這張表，請用一條SQL寫出來，每科成績最高的同學。返回的字段要有同學的名稱，科目和成績。