這篇主要介紹對慢 SQL 優(yōu)化的一些手段，而在講解具體的優(yōu)化措施之前，我想先對 EXPLAIN 進行介紹，它是我們在分析查詢時必要的操作，理解了它輸出結(jié)果的內(nèi)容更有利于我們優(yōu)化 SQL。為了方便大家的閱讀，在下文中規(guī)定類似 key1 的表示二級索引，key_part1 表示聯(lián)合索引的第一部分，unique_key1 則表示唯一二級索引，primary_key 表示主鍵索引。高性能MySQL實戰(zhàn)（一）：表結(jié)構(gòu) 和高性能MySQL實戰(zhàn)（二）：索引是本文的前置知識，歡迎大家閱讀。

一、Explain 詳解

Explain 是我們在對慢 SQL 進行優(yōu)化前常用語句，它能分析具體的查詢計劃，從而讓我們有目的地去進行優(yōu)化。本節(jié)則主要是讓大家看懂 Explain 查詢結(jié)果的每一列是干啥用的，我們先簡要的來看一下各個列的作用：

列名	描述
id	在一個大的查詢語句中，每個 SELECT 關(guān)鍵字都對應一個唯一的 id。在連接查詢中，記錄的 id 值都是相同的；在多個 SELECT 關(guān)鍵字的查詢中，查詢優(yōu)化器可能會對子查詢進行優(yōu)化，使得多條 SELECT 記錄的 id 值相同
select_type	查詢類型
table	表名
partitions	匹配的分區(qū)信息
type	針對單表的訪問方法
possible_keys	可能用到的索引
key	實際使用的索引
key_len	實際使用的索引長度
ref	當使用索引列等值查詢時，與索引列進行等值匹配的對象信息
rows	預估的需要讀取的記錄條數(shù)
filtered	針對預估的需要讀取的記錄，經(jīng)過搜索條件過濾后剩余記錄條數(shù)的百分比。在單表查詢中沒什么意義，在連表查詢中可以計算出在驅(qū)動表執(zhí)行完查詢后，還需要對被驅(qū)動表執(zhí)行多少次查詢
Extra	額外的備注信息

其中大部分列在描述信息中已經(jīng)解釋的足夠清楚，下面我們主要對一些必要的列進行詳述：

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ù)進行等值比較時，這個 const 訪問才有效
ref: 通過二級索引與常數(shù)進行等值比較，形成的掃描區(qū)間為單點掃描區(qū)間的訪問
ref_or_null: 相比于 ref 多掃描了一些值為 NULL 的二級索引列
range: 使用索引執(zhí)行查詢時，對應的掃描區(qū)間為若干個單點掃描區(qū)間或者范圍掃描區(qū)間的訪問
index: 使用覆蓋索引，并掃描全部二級索引的訪問。另外，當通過全表掃描對使用 InnoDB 引擎的表執(zhí)行查詢時，如果添加了ORDER BY主鍵的語句，那么該語句在執(zhí)行時也會被認為是 index 訪問
fulltext: 全文索引訪問
all: 全表掃描
eq_ref: 執(zhí)行連接查詢時，如果被驅(qū)動表是通過主鍵或者不允許為 NULL 的唯一二級索引等值匹配的方式進行訪問

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

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

1.3 ref

當訪問方法是 const、ref、ref_or_null、eq_ref、unique_subquery 和 index_subquery 其中之一時，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: 當查詢中有 min 或 max 聚合函數(shù)時，但是沒有記錄符合 WHERE 條件
Using Index: 使用了覆蓋索引
Using Index condition: 在執(zhí)行查詢語句時使用了索引條件下推特性

索引條件下推：它是針對二級索引查詢條件做的優(yōu)化，在對二級索引條件進行判斷時，會將所有該索引相關(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)存或者磁盤中進行排序
Using temporary: 查詢時使用到了內(nèi)部臨時表

二、優(yōu)化考慮點

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

在前文中我們已經(jīng)詳細介紹了 EXPLAIN 語句中的訪問類型（type），如果一個查詢的訪問類型并不是我們預期的，那么最簡單直接的解決辦法是為搜索條件列增加合適的索引。

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

在有些情況下，簡單地增加索引并不能解決問題，比如執(zhí)行如下 SQL：

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

這條 SQL 執(zhí)行完畢后可能只返回幾行數(shù)據(jù)，但是因為有 COUNT 聚合函數(shù)，需要掃描的數(shù)據(jù)可能會有成千上萬行，這取決于表中數(shù)據(jù)量總數(shù)。對于這種掃描大量數(shù)據(jù)卻只返回少數(shù)行的情況，通?？梢酝ㄟ^增加單獨的匯總表進行優(yōu)化，當然這需要在應用層增加相應的邏輯對匯總表的數(shù)據(jù)進行維護。

除此之外，還可以通過重寫復雜查詢的方式來優(yōu)化，下面我們對重寫查詢時需要考慮的方向進行介紹：

一個復雜查詢還是多個簡單查詢？

這是一個值得考慮的問題。將復雜查詢拆成多個簡單查詢，盡可能地減少數(shù)據(jù)庫的工作，并將一些處理邏輯拿到應用層處理，因為 MySQL 處理簡單查詢很高效，所以通常情況下這么做能夠提高效率。

切分處理

在實際工作中，對數(shù)據(jù)量較大的數(shù)據(jù)庫表進行結(jié)轉(zhuǎn)（或刪除）時通常會采用切分處理的方法，將一個大查詢分成小查詢，每個查詢的作用是一樣的，只不過操作的數(shù)據(jù)量不同，各個小查詢執(zhí)行完畢后，大查詢的任務也就處理完成了。

一次性結(jié)轉(zhuǎn)大量數(shù)據(jù)可能會鎖住很多數(shù)據(jù)、占滿整個事務日志、耗盡系統(tǒng)資源和阻塞很多小的查詢等，為了避免這種情況，通常在一次數(shù)據(jù)結(jié)轉(zhuǎn)任務中只操作一萬條左右數(shù)據(jù)，這樣對服務器影響最小，而且可以在每次結(jié)轉(zhuǎn)完成時，都暫停一會兒再去執(zhí)行下一次任務，這樣做可以將壓力分散到一個比較長的時間段中，大大降低對服務器的影響和減少持有鎖的時間。

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

如果聯(lián)表過多，我們需要將其拆成多個查詢或多個單表查詢（單表查詢的緩存效率會更高），查詢被分解后，查詢間的鎖競爭會減少。除此之外，聯(lián)表查詢還需要注意以下兩點：

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

IN() 條件與 OR 條件

一般情況下我們認為 IN() 完全等價于多個 OR 條件，但是在 MySQL 中這兩者是有區(qū)別的。MySQL 在處理 IN() 條件時，會將列表中的數(shù)據(jù)先進行排序，然后通過二分查找的方式來確定列表中的值是否滿足條件，這是一個時間復雜度為 O(logn) 的操作，如果等價地轉(zhuǎn)換成 OR 查詢，它的時間復雜度為 O(n)，所以在 IN() 條件中有大量取值時，MySQL 的處理速度會更快。

查詢時索引是否失效

如果不是按照索引的最左列開始查找，則無法使用索引
如果跳過了聯(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;

在查詢條件中略過了 key_part2，那么只能使用到索引的第一列，如果略過的是 key_part1 那么就無法使用到這個聯(lián)合索引了

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

   
   
    
    
     
     
    
    
    
    select * from specific_table where key1 * 2 > 4;

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

排序時索引是否失效

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

有如下 SQL，其中 key_part1 和 key_part2 是按順序的聯(lián)合索引，執(zhí)行時不能使用索引

   
   
    
    
     
     
     
     
    
    
    
    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;

因為它們非同一索引，在 key1 相同的情況下，是不會按照 key2 列進行排序的，所以用不到索引

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

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

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

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

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

當需要 Min() 和 Max() 操作時，索引列不為空可以讓它們更高效。比如要找到某一列的最小值，只需要查詢對應 B-Tree 索引的最左端記錄，查詢優(yōu)化器會將這個表達式看做一個常數(shù)對待，而且能夠在 ESPLAIN 結(jié)果的 Extra 列中發(fā)現(xiàn) “Select tables optimized away”。

重復索引和冗余索引

重復索引指的是在相同的列上按照相同順序創(chuàng)建的相同類型的索引，如下 SQL 所示：

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

它在 id 列上創(chuàng)建了兩個相同的索引，需要將其中的唯一索引移除。

冗余索引通常發(fā)生在為表添加新的索引時，比如在已有索引（column_a），再添加一個索引（column_a, column_b），這就是發(fā)生了冗余索引的情況，因為第二個聯(lián)合索引能夠發(fā)揮和單列索引一樣的作用。

大多數(shù)情況下都不需要冗余索引，我們應該盡量擴展已有的索引而不是創(chuàng)建新的索引。

是否存在索引合并

在多列上獨立地創(chuàng)建多個單列索引，大部分情況下并不能提高 MySQL 的查詢性能。

MySQL 中有一種 “索引合并” 的策略，它可以使用表中的多個單列索引來定位指定的數(shù)據(jù)行，并將掃描結(jié)果進行合并。索引合并的策略有時候非常不錯，但更多的時候，它說明了表中的索引建的比較糟糕：

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

通常來說，我們需要考慮重建索引或者使用 UNION改寫查詢。除此之外，可以通過修改 optimizer_switch 參數(shù)來關(guān)閉索引合并功能，如下 SQL：

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

還可以使用 IGNORE INDEX 語法讓優(yōu)化器來忽略到某些索引，從而避免優(yōu)化器使用包含該索引的索引合并執(zhí)行計劃：

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

除了在發(fā)生索引合并時考慮忽略索引，也需要在執(zhí)行查詢時因無法形成合適的掃描區(qū)間，達不到減少掃描記錄的數(shù)量的目的時，考慮忽略索引而使用全表掃描。

下面我們介紹三種索引合并的類型，讓大家對索引合并有一個更加充分的了解：它們分別是 Intersection 索引合并、Union 索引合并和 Sort-Union 索引合并。

Intersection 索引合并

我們看如下查詢：

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

我們都能清楚的是：在索引列值相同的情況下，二級索引記錄是按照主鍵值的大小排序的，那么可以將 key1 篩選出的主鍵值和 key2 篩選出的主鍵值取交集，根據(jù)結(jié)果再去執(zhí)行回表操作，這相比于分別對 key1 和 key2 篩選出的主鍵值都去做回表的開銷要低，這種情況使用的是 Intersection 索引合并策略。

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

我們將上述查詢條件更改成了范圍查詢條件，現(xiàn)在各索引篩選出的主鍵值是無序的，所以無法使用 Union 索引合并，而 Sort-Union 索引合并正是在 Union 索引合并的基礎(chǔ)上添加了排序操作：將 key1 篩選出的主鍵值和 key2 篩選出的主鍵值進行排序，這樣就能夠繼續(xù)使用 Union 索引合并了。

優(yōu)化 COUNT()

當我們需要統(tǒng)計有值的結(jié)果時，需要在 COUNT() 條件內(nèi)指定列名或 COUNT(0)；當我們需要統(tǒng)計所有的行數(shù)時，需要指定為 COUNT(*)，它會忽略所有列而直接統(tǒng)計所有行數(shù)。明白了這兩點之后，我們做數(shù)據(jù)統(tǒng)計能夠更清晰地傳達意圖。

通常來說，COUNT() 查詢需要掃描大量的數(shù)據(jù)行才能獲得精確的結(jié)果，所以比較難優(yōu)化。如果業(yè)務場景不要求完全精確，我們可以使用 EXPLAIN 估算的行數(shù) rows來代替；或者，我們?nèi)サ粢恍┎樵儣l件中的約束，刪除 DISTINCT 來避免排序操作，這些做法都可能使統(tǒng)計查詢性能提高。

優(yōu)化 UNION 查詢

在我們使用 UNION 查詢時，如果不需要消除重復的行，一定要使用 UNION ALL，因為如果沒有 ALL 關(guān)鍵字，MySQL 會給臨時表加上 DISTINCT，這會對數(shù)據(jù)做去重，代價比較高。此外，我們可以將 WHERE、LIMIT 和 ORDER BY 語句應用到每個查詢中，這樣能夠讓 MySQL 對它們更好地進行優(yōu)化。

優(yōu)化 OFFSET

在分頁查詢中，OFFSET 會導致 MySQL 掃描大量不需要的行然后再拋棄掉，比如 LIMIT 1000, 20 這個表達式，它會查詢 1020 條數(shù)據(jù)然后將前 1000 條拋棄掉，這樣做的代價非常高。

我們可以通過采用書簽的方式記錄上次讀取數(shù)據(jù)的“位置”，那么下次查詢就能直接從該位置開始掃描，避免使用 OFFSET。比如說，每頁展示 20 條數(shù)據(jù)，我們記錄下來當前所在頁面的數(shù)據(jù) ID 值為 200，那么我們看下一頁的數(shù)據(jù)時，查詢 SQL 如下：

   
   
    
    
     
     
     
     
     
     
    
    
    
    select * from specific_tablewhere id <= 180limit 20;

不過，這種情況也有不足，它沒有辦法指定頁碼進行查詢，比如說我現(xiàn)在想看第 5 頁的數(shù)據(jù)，我們沒辦法計算對應頁具體的 ID 值范圍。除非我們能夠保證 ID 值是單調(diào)遞增且沒有刪除過數(shù)據(jù)的，這樣的話， ID 值是連續(xù)的，我們就能輕易的計算出第 5 頁的數(shù)據(jù)的 ID 值是從 120 開始的。這樣做的好處是無論翻頁到多么靠后，它的性能都很好。

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

我們通常使用 GROUP BY 做分組聚合查詢，如果還要對分組后的結(jié)果再次求和，可以使用 WITH ROLLUP 操作，但是更好的辦法還是將 WITH ROLLUP 的處理拿到應用層去做。

OPTIMIZE TABLE

如果我們刪除了很多數(shù)據(jù)，或者在插入數(shù)據(jù)時，不是按照主鍵的遞增順序插入的，很可能會因此產(chǎn)生很多內(nèi)存碎片，影響數(shù)據(jù)查詢的效率。這是因為在刪除數(shù)據(jù)時，MySQL 并不會立即將它們清除并整理空間，而是將它們標記為刪除，通過 OPTIMIZE TABLE 可以將空間進行整理，減少內(nèi)存碎片。

InnoDB 引擎并不支持 OPTIMIZE TABLE 操作，它會提示如下信息：

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

我們可以通過不做任何操作的 ALTER 命令來重建表達到以上目的：

   
   
    
    
     
     
    
    
    
    alter table specific_table engine=InnoDB;

執(zhí)行完成后，我們通過如下 SQL 查看執(zhí)行情況，如果 data_free 列為 0，說明我們空間碎片整理成功

   
   
    
    
     
     
    
    
    
    show table status from specific_db like specific_table;

不過，多數(shù)情況下不需要執(zhí)行該操作。

找到并修復損壞的表

可能因硬件問題、MySQL 本身的缺陷或者操作系統(tǒng)的問題導致索引損壞，當然這種問題非常少見，我們可以通過如下 SQL 來檢查大多數(shù)表和索引的錯誤：

   
   
    
    
     
     
    
    
    
    check table specific_table;

如果發(fā)現(xiàn)異常的話，可以通過如下 SQL 進行修復：

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

參考資料:

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

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

[3] MySQL：optimizer_switch

[4] 8.9.4 Index Hints

[5] mysql進階：optimize table命令

-end-

高性能MySQL實戰(zhàn)（三）：性能優(yōu)化