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

1. Explain 詳解

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

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

1.1 select_type

SIMPLE: 查詢語句中 不包含 UNION 或者子查詢 的查詢PRIMARY: 對于包含 UNION、UNION ALL 或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊查詢的 select_type 是 PRIMARYUNION: 對于包含 UNION 和 UNION ALL 的大查詢來說，它是由幾個小查詢組成的，其中除了最左邊的那個小查詢以外，其余小查詢的 select_type 都是 UNIONUNION RESULT: MySQL 選擇使用臨時表來完成 UNION 查詢的去重，針對該臨時表的查詢的 select_type 是 UNION RESULTDEPENDENT UNION: UNION 查詢相關的類型SUBQUERY, DEPENDENT SUBQUERY, MATERIALIZED: 子查詢相關的類型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 條件始終為 FALSENo matching min/max row: 當查詢中有 min 或 max 聚合函數(shù)時，但是沒有記錄符合 WHERE 條件Using Index: 使用了覆蓋索引Using Index condition: 在執(zhí)行查詢語句時使用了索引條件下推特性索引條件下推：它是針對 二級索引 查詢條件做的優(yōu)化，在對二級索引條件進行判斷時，會將所有該索引相關列的條件都判斷完成后，符合條件再執(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)部臨時表

2. 優(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é)查詢

阿里巴巴開發(fā)手冊中提到過一點，聯(lián)表查詢時聯(lián)表的數(shù)量不超過 3 個。如果聯(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_table where 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 orderby key_part1, key_part2 desc;在 MySQL 8.0 版本，可以支持 ASC 和 DESC 混用使用索引如果排序列包含非同一索引的列，則無法使用到索引，如下 SQL 所示select id, key1, key2 from specific_table orderby key1, key2;因為它們非同一索引，在 key1 相同的情況下，是不會按照 key2 列進行排序的，所以用不到索引如果排序列是某個聯(lián)合索引的索引列，但是這些排序列在聯(lián)合索引中并不連續(xù)，那么也無法使用到索引。如下 SQL 所示，因為該聯(lián)合索引在按照 key_part1 排序后是沒有再按照 key_part3 進行排序的，所以無法使用索引select key_part1, key_part3 from specific_table orderby key_part1, key_part3;如果排序列不是以單獨列名的形式出現(xiàn)在 ORDER BY 語句中，則無法使用索引。如下 SQL 所示，在排序時使用了函數(shù)，所以無法使用索引select id, key1, key2 from specific_table orderby 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)化器需要對多個索引合并時，通常意味著需要一個包含所有相關列的聯(lián)合索引，而不是多個獨立的單列索引當優(yōu)化器需要對多個索引做合并操作時，通常需要在算法的緩存、排序和合并操作上耗費大量 CPU 和內(nèi)存資源，尤其是當其中有些索引列值的選擇性不高且需要合并掃描返回的大量數(shù)據(jù)時優(yōu)化器不會將這些操作算在查詢成本中，這會使得查詢的成本被“低估”，導致執(zhí)行計劃還不如進行全表掃描

通常來說，我們需要考慮 重建索引 或者 使用 UNION 改寫查詢。除此之外，可以通過修改 optimizer_switch 參數(shù)來關閉索引合并功能，如下 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 索引合并的基礎上添加了排序操作：將 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 關鍵字，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_table where id <= 180 limit 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;

巨人的肩膀

《高性能MySQL 第四版》：第七、八章《MySQL 是怎樣運行的》：第七、十、十一、十四、十五章MySQL：optimizer_switch8.9.4 Index Hintsmysql進階：optimize table命令

作者：京東物流 王奕龍

來源：京東云開發(fā)者社區(qū) 自猿其說Tech 轉(zhuǎn)載請注明來源