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

紀莫發表於2021-03-01

前言

過年回來的第二週了,終於有時間繼續總結知識了。這次來看一下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寫出來,每科成績最高的同學。返回的欄位要有同學的名稱,科目和成績。

相關文章