大家好，我是 方圓。這篇主要介紹對慢 SQL 優(yōu)化的一些手段，而在講解具體的優(yōu)化措施之前，我想先對 EXPLAIN 進(jìn)行介紹，它是我們在分析查詢時必要的操作，理解了它輸出結(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)（二）：索引 是本文的前置知識，歡迎大家閱讀。原文收錄在我的 Github: enthusiasm 中，歡迎Star和獲取原文。

1. Explain 詳解

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

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

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ū)間為單點掃描區(qū)間的訪問

• ref_or_null: 相比于 ref 多掃描了一些值為 NULL 的二級索引列

• range: 使用索引執(zhí)行查詢時，對應(yīng)的掃描區(qū)間為 若干個單點掃描區(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ū)動表中找不到匹配紀(jì)錄時，對應(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

當(dāng)訪問方法是 const、ref、ref_or_null、eq_ref、unique_subquery 和 index_subquery 其中之一時，ref 列展示的是與索引列進(jìn)行 等值匹配 的東西是啥：

• 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(chǎn)’ and key1 like ‘%b’;

  索引條件下推會將 key1 所有條件判斷完而不是只判斷完 key1 > ‘a(chǎn)’ 就去回表。

• 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)部臨時表

2. 優(yōu)化考慮點

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

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

減少掃描行數(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ù)行 的情況，通常可以通過 增加單獨的匯總表 進(jìn)行優(yōu)化，當(dāng)然這需要在應(yīng)用層增加相應(yīng)的邏輯對匯總表的數(shù)據(jù)進(jìn)行維護(hù)。

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

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

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

切分處理

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

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

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

阿里巴巴開發(fā)手冊中提到過一點，聯(lián)表查詢時聯(lián)表的數(shù)量不超過 3 個。如果聯(lián)表過多，我們需要將其拆成多個查詢或多個單表查詢（單表查詢的 緩存效率會更高），查詢被分解后，查詢間的鎖競爭會減少。除此之外，聯(lián)表查詢還需要注意以下兩點：

• 確保 ON 或者 USING 子句中的列上有索引

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

IN() 條件與 OR 條件

一般情況下我們認(rèn)為 IN() 完全等價于多個 OR 條件，但是在 MySQL 中這兩者是有區(qū)別的。MySQL 在處理 IN() 條件時，會將列表中的數(shù)據(jù)先進(jìn)行排序，然后通過二分查找的方式來確定列表中的值是否滿足條件，這是一個時間復(fù)雜度為 O(logn) 的操作，如果等價地轉(zhuǎn)換成 OR 查詢，它的時間復(fù)雜度為 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)，而是使用了表達(dá)式或者函數(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 列進(jìn)行排序的，所以用不到索引

• 如果排序列是某個聯(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;
  

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

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

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

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

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

重復(fù)索引指的是在相同的列上按照相同順序創(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ù)情況下都不需要冗余索引，我們應(yīng)該盡量擴(kuò)展已有的索引而不是創(chuàng)建新的索引。

是否存在索引合并

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

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

• 當(dāng)查詢優(yōu)化器需要對多個索引合并時，通常意味著需要一個包含所有相關(guān)列的聯(lián)合索引，而不是多個獨立的單列索引

• 當(dāng)優(yōu)化器需要對多個索引做合并操作時，通常需要在算法的緩存、排序和合并操作上耗費(fèi)大量 CPU 和內(nèi)存資源，尤其是當(dāng)其中有些索引列值的選擇性不高且需要合并掃描返回的大量數(shù)據(jù)時

• 優(yōu)化器不會將這些操作算在查詢成本中，這會使得查詢的成本被“低估”，導(dǎo)致執(zhí)行計劃還不如進(jìn)行全表掃描

通常來說，我們需要考慮 重建索引 或者 使用 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ū)間，達(dá)不到減少掃描記錄的數(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 篩選出的主鍵值 進(jìn)行排序，這樣就能夠繼續(xù)使用 Union 索引合并了。

優(yōu)化 COUNT()

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

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

優(yōu)化 UNION 查詢

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

優(yōu)化 OFFSET

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

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

select * from specific_table
where id <= 180
limit 20;

不過，這種情況也有不足，它沒有辦法指定頁碼進(jìn)行查詢，比如說我現(xiàn)在想看第 5 頁的數(shù)據(jù)，我們沒辦法計算對應(yīng)頁具體的 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 的處理拿到應(yīng)用層去做。

OPTIMIZE TABLE

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

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

OPTIMIZE TABLE specific_table;

-- Table does not support optimize, doing recreate + analyze instead

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

alter table specific_table engine=InnoDB;

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

show table status from specific_db like specific_table;

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

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

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

check table specific_table;

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

repair table specific_table;

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

巨人的肩膀

• 《高性能MySQL 第四版》：第七、八章

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

• MySQL：optimizer_switch

• 8.9.4 Index Hints

• mysql進(jìn)階：optimize table命令文章來源地址http://www.zghlxwxcb.cn/news/detail-672882.html

到了這里，關(guān)于高性能MySQL實戰(zhàn)（三）：性能優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！