你們一般都是怎麼進行SQL調優的？MySQL在執行時是如何選擇索引的？

紀莫發表於2021-03-01
MySql索引

前言

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

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

explain 查詢SQL執行計劃

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

舉例?：

建立如下資料表

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 '學生名與成績的聯合索引',
  KEY `idx_create_time` (`create_time`) USING BTREE COMMENT '建立時間的索引'
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 COMMENT='學生科目成績表';

在上面的學生科目成績表中，我建立了兩個索引，一個是name和score的聯合索引，一個是create_time的索引。

然後向test_score表中插入10w條資料，通過儲存過程的方式新增：

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

自定義函式：convertSubject內容如下：

-- 自定義函式
delimiter $$
DROP FUNCTION IF EXISTS convertSubject $$
create function `convertSubject`(xis int) returns VARCHAR(50)
begin
		CASE xis
		WHEN 1 THEN RETURN '數學';
		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 ;

執行儲存過程插入資料：

call insert_test_score();

資料插入成功後，我們檢視一個查詢SQL語句的執行計劃：

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

執行結果：
SQL執行計劃
這裡解釋一下，Explain執行計劃結果每個欄位的含義：

id

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

  1. id相同

如果只有一條SQL的單表查詢（不含中間表）的情況，那麼id就是1，如：
SQL執行計劃
如果存在中間表查詢的SQL，這時候會有多條執行結果，但是id值相同，這個時候代表的執行順序是由上到下的順序。

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值不同時，值越大優先順序越高，也就是說，值越大的越先執行。
EXPLAIN select `name`,score from test_score t1 where id = (SELECT id from test_score t2 where t2.id =1);

在這裡插入圖片描述

select_type

select_type代表的是查詢型別，主要是區別於簡單查詢、聯合查詢、子查詢等查詢型別。
主要有以下幾種型別：

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

table

資料輸入行所引用的表名（若表名有別名，則展示別名）。

Type

主要是顯示連線型別，有如下幾種：

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

possible_keys

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

key

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

key_len

表示索引中使用的位元組數，該列計算查詢中使用的索引的長度在不損失精度的情況下，長度越短越好。如果鍵是NULL,則長度為NULL。該欄位顯示為索引欄位的最大可能長度，並非實際使用長度。

ref

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

rows

根據表統計資訊以及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數。

filtered

表示返回結果的行數佔需讀取行數的百分比 Filtered列的值越大越好 Filtered列的值依賴於統計資訊。

Extra

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

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

通過對Explain執行計劃結果的瞭解，我們就可以在分析自己寫的SQL時應該做哪些優化，這些操作是必須掌握的一些技能，還有就是要了解一下哪些情況下會造成索引失效，例如：對欄位進行計算後查詢或者是like '%***'，欄位型別隱式轉換等等。
還有就是儘量避免回表，能用覆蓋索引完成查詢最好，用到檔案排序時，儘量避免產生filesort等等。

MySQL在執行時是如何選擇索引的？

通過上面我們對Explain執行計劃的各個欄位內容的瞭解，我們知道了，MySQL在執行的SQL的時候，最終的一個執行計劃是什麼樣的。

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

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

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

我們下面來慢慢分析。

查詢成本

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

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

  • IO成本是指，MySQL讀取資料的時候會將資料從磁碟讀取到記憶體中，讀取資料的單位是資料頁，每一頁為16KB，所以讀取資料頁的成本常數記做1（1頁的成本為1）。
  • CPU成本是指，查詢資料是否滿足查詢條件或排序條件的CPU的執行成本。預設情況下，檢測記錄成本常數記錄為0.2（這裡是指檢測每一行資料的成本）。

基於執行成本，我們來看一下，全表掃描的成本，還是基於上面的我們已經建立好的test_score表為例，通過如下SQL，查詢出MySQL本身為每個表維護的統計資料。

SHOW TABLE STATUS LIKE 'test_score'

輸出結果：
MySQL維護的表統計資料

  • 通過這個結果我們可以看到，test_score表中總行數是99869行。我們之前不是插入了10w行嗎？怎麼少了100多行資料？其實這裡的總行數只是MySQL的一個估算值，但是這個估算值並不影響我們計算成本，我們上面說了，單條記錄的CPU成本常數是0.2，那麼99869*0.2=19974左右。
  • 表中的資料總長度是6832128位元組，InnoDB每一頁的資料是16KB，資料總長度是417頁，因此，IO成本就是417*1=417左右。

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

統計成本

接下來，我們以一個SQL為例來說明一下，執行成本統計情況。
還是以上面我們已經建立好的資料表test_score為例。

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

上面這條SQL的執行計劃結果是全表掃描：
18點的執行計劃
但是隻要我們把create_tieme的引數從18點改為17點，執行計劃顯示就會走索引了，並且走的是create_time的索引，而不是name欄位的聯合索引。

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

執行計劃：
17點的執行計劃
通過上面的例子我們可以看到，同樣的查詢欄位，傳入不同的值，有的就會走索引，有的確不走索引，並且MySQL選擇的索引，也並不是根據where後面的查詢欄位的順序來選擇的。

產生這樣結果的原因，就是MySQL基於成本來選擇了最優的計劃來執行了SQL。那麼MySQL到底是怎麼制定執行計劃以及做出選擇計劃的依據是什麼呢？

MySQL執行計劃的選擇過程。

在MySQL5.6及之後的版本中，我們可以通過optimizer_trace功能來檢視優化器生成執行計劃的整個過程。通過這個功能，我們可以瞭解MySQL每個計劃的成本，然後來進一步對查詢進行優化。

optimizer_trace功能，預設是關閉的。可通過如下程式碼開啟後，再執行具體的SQL，然後通過information_schema.OPTIMIZER_TRACE 表檢視執行計劃，最後記得手動再關閉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";

執行後的結果是個大JSON，內容比較多，所以我就只擷取了關鍵部分。

我們先來看走idx_name_score索引時執行計劃：
idx_name_score執行計劃成本
我們看到，走idx_name_score索引，要掃描資料21474行，成本（cost）是：25770。

再來看走idx_create_time時的執行計劃：
idx_create_time執行計劃成本
這裡看到，走idx_create_time時，掃描資料6805行，成本（cost）是8167，比走idx_name_score索引成本要低。所以MySQL最終選擇的是走idx_create_time的執行計劃。

最後再來將create_time的引數改為18點，然後看一下全表掃描的執行時間。

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

全表掃描執行計劃成本
我們看到全表掃描要檢索資料99869行，執行成本（cost）是20391。這個20391正好是我們上面在統計全表掃描時計算出來的成本。

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

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

其實，MySQL的執行計劃選擇，也並不是百分百準確的，有的時候，他計算出來的成本並不一定準確，所以我們可以強制讓某條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';

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

總結

最後總結一下，我把Explain的各欄位值的相關資訊，做了一個表格。
explain欄位解釋表格

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

最後的最後

用上面的例子的test_score表，來分享一道我面試中遇到的SQL題。
SQL題
還是科目成績表，根據這張表，請用一條SQL寫出來，每科成績最高的同學。返回的欄位要有同學的名稱，科目和成績。

相關文章