1.1 select_type

• SIMPLE: 查詢語句中不包含 UNION 或者子查詢的查詢
• PRIMARY: 對于包含 UNION、UNION ALL 或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊查詢的 select_type 是 PRIMARY
• UNION: 對于包含 UNION 和 UNION ALL 的大查詢來說，它是由幾個小查詢組成的，其中除了最左邊的那個小查詢以外，其余小查詢的 select_type 都是 UNION
• UNION RESULT: MySQL 選擇使用臨時表來完成 UNION 查詢的去重，針對該臨時表的查詢的 select_type 是 UNION RESULT
• DEPENDENT UNION: UNION 查詢相關(guān)的類型
• SUBQUERY, DEPENDENT SUBQUERY, MATERIALIZED: 子查詢相關(guān)的類型
• DERIVED: 在包含派生表的查詢中，以物化派生表的方式執(zhí)行的查詢

1.2 type

• const: 通過主鍵或唯一二級索引與常數(shù)的等值比較來定位一條記錄，如果是聯(lián)合索引，則只有在索引列的每一個列都與常數(shù)進(jìn)行等值比較時，這個 const 訪問才有效
• ref: 通過二級索引與常數(shù)進(jìn)行等值比較，形成的掃描區(qū)間為單點(diǎn)掃描區(qū)間的訪問
• ref_or_null: 相比于 ref 多掃描了一些值為 NULL 的二級索引列
• range: 使用索引執(zhí)行查詢時，對應(yīng)的掃描區(qū)間為若干個單點(diǎn)掃描區(qū)間或者范圍掃描區(qū)間的訪問
• index: 使用覆蓋索引，并掃描全部二級索引的訪問。另外，當(dāng)通過全表掃描對使用 InnoDB 引擎的表執(zhí)行查詢時，如果添加了ORDER BY主鍵 的語句，那么該語句在執(zhí)行時也會被認(rèn)為是 index 訪問
• fulltext: 全文索引訪問
• all: 全表掃描
• eq_ref: 執(zhí)行連接查詢時，如果被驅(qū)動表是通過主鍵或者不允許為 NULL 的唯一二級索引等值匹配的方式進(jìn)行訪問

在外連接中，ON 語句是專門為 “驅(qū)動表中的記錄在被驅(qū)動表中找不到匹配記錄時，對應(yīng)的被驅(qū)動表記錄的?各個字段使用 NULL 來填充” 場景提出的；在內(nèi)連接中，ON 和 WHERE 的作用一致

• unique_subquery: 針對的是一些包含 IN 子查詢的查詢語句，如果查詢優(yōu)化器決定將 IN 子查詢轉(zhuǎn)換成 EXISTS 子查詢，而且子查詢在轉(zhuǎn)換之后可以使用主鍵或者為允許為 NULL 的唯一二級索引進(jìn)行等值匹配
• index_subquery: 與 unique_subquery 類似，只不過在訪問時使用的是普通二級索引
• index_merge: 存在索引合并
• system: 當(dāng)表中只有一條記錄并且使用的存儲引擎的統(tǒng)計數(shù)據(jù)是精確的（如 MyISAM 和 MEMORY）

1.3 ref

• const: 表示是一個常數(shù)
• func: 表示是一個函數(shù)
• DBName.TableName.columnName: 表示某個數(shù)據(jù)庫某個表中的某個列

1.4 Extra

• No Table used: 查詢語句中沒有 FROM 子句
• Impossible WHERE: 查詢語句中的 WHERE 條件始終為 FALSE
• No matching min/max row: 當(dāng)查詢中有 min 或 max 聚合函數(shù)時，但是沒有記錄符合 WHERE 條件
• Using Index: 使用了覆蓋索引

• Using Index condition: 在執(zhí)行查詢語句時使用了索引條件下推特性

索引條件下推：它是針對 二級索引 查詢條件做的優(yōu)化，在對二級索引條件進(jìn)行判斷時，會將所有該索引相關(guān)列的條件都判斷完成后，符合條件再執(zhí)行回表操作，不符合條件則不再執(zhí)行回表，這樣做減少了回表操作的次數(shù)，從而減少了 I/O。 

如下例子：

select * from specific_table where key1 > 'a' and key1 like '%b'; 

索引條件下推會將 key1 所有條件判斷完而不是只判斷完 key1 > 'a' 就去回表。

• Using join buffer(Block Nested Loop): 表示在執(zhí)行連接查詢時，被驅(qū)動表不能有效地利用索引加快訪問速度，而是使用內(nèi)存塊來加快查詢

• Using intersect(index_name, ...)、Using union(index_name, ...) 和 Using sort union(index_name, ...): 表示使用 Intersection 索引合并、Union 索引合并或 Sort-Union 索引合并執(zhí)行查詢（下文有介紹）

• Using filesort: 文件排序，排序無法使用到索引只能在內(nèi)存或者磁盤中進(jìn)行排序

• Using temporary: 查詢時使用到了內(nèi)部臨時表

基于訪問類型優(yōu)化

減少掃描行數(shù)的優(yōu)化

   
   

    
    

     
     

    
    
    
    
select name, count(name) from specific_table group by key1;
   
   
   
   

    
    

   
   

一個復(fù)雜查詢還是多個簡單查詢？

切分處理

優(yōu)化聯(lián)結(jié)查詢

• 確保 ON 或者 USING 子句中的列上有索引
• 確保任何 GROUP BY 和 ORDER BY 中的表達(dá)式只涉及一個表中的列，這樣 MySQL 才有可能使用索引來優(yōu)化這個查詢

IN() 條件與 OR 條件

查詢時索引是否失效

• 如果不是按照索引的最左列開始查找，則無法使用索引
• 如果跳過了聯(lián)合索引中的列，則無法使用索引或只能使用部分索引。有如下 SQL，其中 key_part1、key_part2 和 key_part3 是按順序的聯(lián)合索引

   
   

    
    

     
     

     
     

    
    
    
    
select key_part1, key_part2, key_part3 from specific_tablewhere key_part1 = 1 and key_part3 = 3;
   
   
   
   

    
    

   
   

• 如果查詢中有某列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查詢或排序。針對這種情況，如果范圍查詢列值的數(shù)量有限，那么可以通過 使用 OR 連接的多個等值匹配來替代范圍查詢
• 如果在搜索條件中列名不以列名的形式單獨(dú)出現(xiàn)，而是使用了表達(dá)式或者函數(shù)，那么無法使用索引，如下 SQL 所示，key1 列以 key1 * 2 的形式出現(xiàn)，不會使用到索引

   
   

    
    

     
     

    
    
    
    
select * from specific_table where key1 * 2 > 4;
   
   
   
   

    
    

   
   

• 如果針對變長字段使用 % 開頭的模糊查詢時，則不會使用索引。這個比較好理解，因為 MySQL 對字符串的排列是按照一個個字符排序的，在開頭使用 % 則無法完成比較只能使用全表掃描了

排序時索引是否失效

• 如果 ORDER BY 語句后面的列的順序沒有按照聯(lián)合索引的列順序給出，則無法使用索引
• 如果發(fā)生 ASC、DESC 混用，則無法使用索引

   
   

    
    

     
     

     
     

    
    
    
    
select key_part1, key_part2 from specific_table order by key_part1, key_part2 desc;
   
   

在 MySQL 8.0 版本，可以支持 ASC 和 DESC 混用使用索引

• 如果排序列包含非同一索引的列，則無法使用到索引，如下 SQL 所示
  

   
   

    
    

     
     

    
    
    
    
select id, key1, key2 from specific_table order by key1, key2;
   
   

• 如果排序列是某個聯(lián)合索引的索引列，但是這些排序列在聯(lián)合索引中并不連續(xù)，那么也無法使用到索引。如下 SQL 所示，因為該聯(lián)合索引在按照 key_part1 排序后是沒有再按照 key_part3 進(jìn)行排序的，所以無法使用索引
  

   
   

    
    

     
     

     
     

     
     

    
    
    
    
select key_part1, key_part3 from specific_table order by key_part1, key_part3;
   
   

• 如果排序列不是以單獨(dú)列名的形式出現(xiàn)在 ORDER BY 語句中，則無法使用索引。如下 SQL 所示，在排序時使用了函數(shù)，所以無法使用索引

   
   

    
    

     
     

    
    
    
    
select id, key1, key2 from specific_table order by upper(key1)
   
   

索引列不為空的優(yōu)化

重復(fù)索引和冗余索引

   
   

    
    

     
     

     
     

     
     

     
     

    
    
    
    
create table specific_table (    id int not null primary key,    unique key(id))engine=InnoDB;
   
   

是否存在索引合并

• 當(dāng)查詢優(yōu)化器需要對多個索引合并時，通常意味著需要一個包含所有相關(guān)列的聯(lián)合索引，而不是多個獨(dú)立的單列索引
• 當(dāng)優(yōu)化器需要對多個索引做合并操作時，通常需要在算法的緩存、排序和合并操作上耗費(fèi)大量 CPU 和內(nèi)存資源，尤其是當(dāng)其中有些索引列值的選擇性不高且需要合并掃描返回的大量數(shù)據(jù)時
• 優(yōu)化器不會將這些操作算在查詢成本中，這會使得查詢的成本被“低估”，導(dǎo)致執(zhí)行計劃還不如進(jìn)行全表掃描

   
   

    
    

     
     

     
     

     
     

     
     

    
    
    
    
SELECT @@optimizer_switch;
-- 改成 index_merge=off set optimizer_switch = 'index_merge=off, ...';
   
   
   
   

    
   
   

   
   

    
    

     
     

     
     

    
    
    
    
select * from specific_table ignore index(index_name)where column_name = #{value};
   
   
   
   

    
   
   

Intersection 索引合并

select * from specific_table where key1 = 'a' and key2 = 'b';

   
   

    Union 索引合并
   
   

   
   

    
    

     
     

    
    
    
    
select * from specific_table where key1 = 'a' or key2 = 'b';
   
   

將 key1 篩選出的主鍵值和 key2 篩選出的主鍵值取并集，再根據(jù)結(jié)果去做回表操作，這種做法被稱為 Union 索引合并，它可能相比于直接做全表掃描的開銷要低。需要注意的是：Union 索引合并要求二級索引篩選出的主鍵值是有序的，如果主鍵值無序則需要考慮 Sort-Union 索引合并。

Sort-Union 索引合并

   
   

    
    

     
     

    
    
    
    
select * from specific_table where key1 < 'a' or key2 > 'b';
   
   
   
   

    
   
   

優(yōu)化 COUNT()

優(yōu)化 UNION 查詢

優(yōu)化 OFFSET

   
   

    
    

     
     

     
     

     
     

    
    
    
    
select * from specific_tablewhere id <= 180limit 20;
   
   
   
   

    
   
   

使用 WITH ROLLUP 優(yōu)化 GROUP BY

OPTIMIZE TABLE

   
   

    
    

     
     

     
     

     
     

    
    
    
    
OPTIMIZE TABLE specific_table;
-- Table does not support optimize, doing recreate + analyze instead
   
   
   
   

    
   
   

   
   

    
    

     
     

    
    
    
    
alter table specific_table engine=InnoDB;
   
   
   
   

    
   
   

   
   

    
    

     
     

    
    
    
    
show table status from specific_db like specific_table;
   
   
   
   

    
   
   

找到并修復(fù)損壞的表

   
   

    
    

     
     

    
    
    
    
check table specific_table;
   
   
   
   

    
   
   

   
   

    
    

     
     

     
     

     
     

     
     

    
    
    
    
repair table specific_table;
-- 如果存儲引擎不支持上述操作的話，也可通過表重建來完成alter table specific_table engine=InnoDB;
   
   
   
   

    
   
   

參考資料:

[1]《高性能MySQL 第四版》：第七、八章

[2] 《MySQL 是怎樣運(yùn)行的》：第七、十、十一、十四、十五章

[3] MySQL：optimizer_switch

[4] 8.9.4 Index Hints

[5] mysql進(jìn)階：optimize table命令

-end-