面試官：不會sql優化？出門右轉順便帶上門，謝謝

上古偽神發表於2021-03-06
面試SQL優化

導讀

作為一個後端程式設計師，資料庫這個東西是繞不開的，特別是寫sql的能力，如果您參加過多次面試，那麼一定會從面試覆盤中發現面試官總是會考察到sql優化這個東西。

我在之前的多次面試中最常遇到的一個問題的sql優化，不論是大廠還是小廠。但我之前沒有詳細去了解過這些東西啊，我就瞎雞兒吹了，畢竟我也幹過兩三年的crud，sql還是寫過不少的，也遇到過一些特別長的sql，執行時間特別長的sql，所以以前經常犧牲午睡時間給客戶出報表還是有點效果的，mmp的。

我是這樣說的：Sql優化，首先就是看sql的執行計劃，然後按照執行計劃對應的執行修改，比如該建索引建索引，然後就balabala....

其實老子之前壓根就沒學過怎麼看執行計劃，純靠這麼些年寫sql的經驗瞎雞兒吹。這也是上個月開始看MySqL，才學會看，那今天就現學現賣，給您們展示兩下子。

SQL執行計劃（以MySQL為例）

1、如何檢視sql的執行計劃

在需要執行的查詢SQL前新增一個關鍵字“EXPLAIN”

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

從上圖可以看到，執行計劃共有12欄位，先來簡便看每一列的作用：

欄位解釋idselect查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序select_type查詢型別table訪問到的表partitions匹配的分割槽type訪問型別possible_keys有可能會使用到的索引key實際使用到的索引，如果為NULL，則沒有使用索引key_len索引中使用的位元組數，可通過該列計算查詢中使用的索引長度ref顯示索引的哪一列被使用了rows估算找到所需資料需讀取的行數filtered查詢的錶行佔表的百分比extra包含不適合在其他列展示但異常重要的資訊，比如是使用索引排序還是檔案排序

2、EXPLAIN中的列

（1）id

1、標識select所屬的行，sql語句中有多少個select就有多少個id，並且id的順序是按照select出現的順序增長的

2、id越大，越先執行

3、id相同，從上往下執行

4、id為NUll的最後執行

例如：（1）id相同，從上往下依次執行

explain 
select * from student,class,class_student;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（2）id不同，id越大執行優先順序越高

explain 
select s.id,s.name,(select 1 from class) from student s;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（3）id相同又不同，id越大越先執行，id相同從上往下執行

explain 
select id,name from student where id <5 union select s.id,s.
name from student s,class_student cs where cs.stu_id = s.id and cs.class_id = 4;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

(2) select_type

顯示對應行是簡單還是複雜select，SIMPLE值表明沒有子查詢或Union，如果有子查詢，那麼最外層標記為Primary

（1）SUBQUERY 包含在select欄位中的子查詢，不在From語句中 例如：

explain 
select id,name,(select class_id from class_student) from student;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（2）DERIVED 包含在From中的子查詢，MySQL會遞迴執行並將結果放在一個臨時表中，成為派生表，從子查詢中派生出來的。

（3）UNION 在UNION中的第二個和隨後的select被標記為UNION。第一個select被標記為外查詢來執行，如果UNION被From子句中的子查詢包含，那麼它的第一個Select會被標記為DERIVED。

explain 
     select id,name from student where id>5
     union
     select id,name from student where id>15;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

UNION被包含在From子句中的示例：

explain
     select * from (
     select id,name from student where id>15
     union
     select id,name from student where id<4
     ) a;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（4）UNION RESULT 用來從UNION的匿名臨時表檢索結果的select被標記為UNION RESULT。上例中可以看到

（5）DEPENDENT select依賴於外層查詢中發現的資料。

explain
     select s.id,s.name,(select class_id from class_student cs where cs.stu_id = s.id) from student s;
面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（6）UNCACHEABLE select中的某些特性阻止結果被快取在一個Item_cache中。

(3) table

顯示當前行的資料來自於哪一張表

（4）type

訪問型別，結果值從好到壞依次是：NULL>system>const>eq_ref>ref>range>index>ALL

一般來說，保證查詢至少能到達range級別，最好能達到ref。

（1）system 表中只有一行資料（系統表）

（2）const 通過索引一次就能找到的資料，比如primary key 和union key，主鍵在where條件中，就能將查詢轉換成一個常量。比如：student表中id是主鍵

explain
   select * from student where id = 1;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（3）eq_ref 唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描。

explain
   select * from student s left join (select * from class_student where stu_id > 10)cs on cs.stu_id = s.id where cs.class_id = 4;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（4）ref 非唯一性索引掃描，返回匹配某個單獨值得所有行 比如：student表中的age是一個普通索引

explain 
select * from student where age = 20;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（5）range

1、只檢索給定範圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引

2、一般就是where條件中出現“>”、“<”、“between”、“in”等條件

3、這種範圍掃描索引掃描比全表掃描要好，因為它只需要開始於索引的某一點，而結束語另一點，不用掃描全部索引。

例如：

explain
select * from student where id > 5;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

(6) All 全表掃描，不使用任何條件或索引。比如：student表中score欄位是沒有設定索引的。

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（5）possible_keys

可能會使用到的key

（6）key

實際使用到的key

（7）key_len

索引中使用的位元組數，可通過該列計算出使用的是哪些列，長度越短越好。顯示的是索引欄位的最大可能長度，並非實際使用長度，根據表定義計算而來，不是通過表內檢索而來。

需要注意的是：1、char欄位一個字元在utf8編碼下最多佔3個位元組，可變長欄位需要額外的兩個位元組記錄長度，外加需要存入一個null值，一個null是一個位元組 2、複合索引有最左字首的特性，如果複合索引能全部使用上，則是複合索引欄位的索引長度之和，這也可以用來判定複合索引是否部分使用，還是全部使用。

比如：student表的id是int型別，四個位元組，所以key_len是4

explain
select * from student where id > 5;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

name欄位型別是varchar(20)，所以ken_len=20*3+2+1=63

EXPLAIN
  select * from student where name = '張三';

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

（8）ref

顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或者常量被用於查詢索引列上的值。

explain
select * from student s,class_student cs where cs.stu_id=s.id and cs.class_id = 4;

　　

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

從第二行可知，使用了student表中的主鍵查詢，ken_len為4 ref為test.cs.stu_id表明使用了cs表中的stu_id欄位。

(9) rows

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

（10）extra

值描述Using filesort說明mysql會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成的排序操作稱為"檔案排序"Using temporary使了用臨時表儲存中間結果,MySQL在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by。Using index表示相應的select操作中使用了覆蓋索引(Covering Index)，避免訪問了表的資料行，效率不錯！  如果同時出現using where，表明索引被用來執行索引鍵值的查詢;   如果沒有同時出現using where，表明索引用來讀取資料而非執行查詢動作Using where使用了where條件Using join buffer使用了連線快取impossible wherewhere子句的值總是false,不能用來獲取任何元素distinct一單mysql找到了與形相聯合匹配的行,就不在搜尋了

注意：當在Extra列出現了Using filesort時候，就說明可以建立相應的索引進行排序優化查詢了。

下面是一個完整的思維導圖：

面試官：不會sql優化？出門右轉順便帶上門，謝謝

 

===============================

我是Liusy，一個喜歡健身的程式設計師。

獲取更多幹貨以及最新訊息，請關注公眾號：上古偽神

如果對您有幫助，點個關注就是對我最大的支援！！！

結尾小驚喜：公眾號回覆“MySQL”，送您一個MySQL的整體思維導圖。

相關文章