寫在前面

在像 Web 服務(wù)這樣需要快速響應的應用場景中，SQL 的性能直接決定了系統(tǒng)是否可以使用；特別在一些中小型應用中，SQL 性能更是決定服務(wù)能否快速響應的唯一標準

嚴格地優(yōu)化查詢性能時，必須要了解所使用數(shù)據(jù)庫的功能特點，此外，查詢速度慢并不只是因為 SQL 語句本身，還可能是因為內(nèi)存分配不佳、文件結(jié)構(gòu)不合理、刷臟頁等其他原因

因此本文即將介紹的優(yōu)化 SQL 的方法不能解決所有的性能問題，但是卻能處理很多因 SQL 寫法不合理而產(chǎn)生的性能問題

下文將盡量介紹一些不依賴具體數(shù)據(jù)庫實現(xiàn)，使 SQL 執(zhí)行速度更快、消耗內(nèi)存更少的優(yōu)化技巧，只需調(diào)整 SQL 語句就能實現(xiàn)的通用的優(yōu)化Tip

環(huán)境準備

下文所講的內(nèi)容是從 SQL 層面展開的，而不是針對某種特性的數(shù)據(jù)庫，也就是說，下文的內(nèi)容基本上適用于任何關(guān)系型數(shù)據(jù)庫

但是，關(guān)系型數(shù)據(jù)庫那么多，逐一來演示示例了，顯然不太現(xiàn)實；我們以常用的 MySQL 來進行示例的演示

MySQL 版本： 5.7.30-log ，存儲引擎： InnoDB

準備兩張表： tbl_customer 和 tbl_recharge_record

 

使用高效的查詢

針對某一個查詢，有時候會有多種 SQL 實現(xiàn)，例如 IN、EXISTS、連接之間的互相轉(zhuǎn)換

從理論上來講，得到相同結(jié)果的不同 SQL 語句應該有相同的性能，但遺憾的是，查詢優(yōu)化器生成的執(zhí)行計劃很大程度上要受到外部結(jié)構(gòu)的影響

因此，如果想優(yōu)化查詢性能，必須知道如何寫 SQL 語句才能使優(yōu)化器生成更高效的執(zhí)行計劃

使用 EXISTS 代替 IN

關(guān)于 IN，相信大家都比較熟悉，使用方便，也容易理解；雖說 IN 使用方便，但它卻存在性能瓶頸

如果 IN 的參數(shù)是 1,2,3 這樣的數(shù)值列表，一般還不需要特別注意，但如果參數(shù)是子查詢，那么就需要注意了

在大多時候， [NOT] IN 和 [NOT] EXISTS 返回的結(jié)果是相同的，但是兩者用于子查詢時，EXISTS 的速度會更快一些

假設(shè)我們要查詢有充值記錄的顧客信息，SQL 該怎么寫？

相信大家第一時間想到的是 IN： SELECT * FROM tbl_customer WHERE ID IN (SELECT customer_id FROM tbl_recharge_record);

IN 使用起來確實簡單，也非常好理解；我們來看下它的執(zhí)行計劃

 

我們再來看看 EXISTS 的執(zhí)行計劃：

 

可以看到，IN 的執(zhí)行計劃中新產(chǎn)生了一張臨時表： <subquery2> ，這會導致效率變慢

通常來講，EXISTS 比 IN 更快的原因有兩個

1、如果連接列（customer_id）上建立了索引，那么查詢 tbl_recharge_record 時可以通過索引查詢，而不是全表查詢

2、使用 EXISTS，一旦查到一行數(shù)據(jù)滿足條件就會終止查詢，不用像使用 IN 時一樣進行掃描全表（NOT EXISTS 也一樣）

當 IN 的參數(shù)是子查詢時，數(shù)據(jù)庫首先會執(zhí)行子查詢，然后將結(jié)果存儲在一張臨時表里（內(nèi)聯(lián)視圖），然后掃描整個視圖，很多情況下這種做法非常耗費資源

使用 EXISTS 的話，數(shù)據(jù)庫不會生成臨時表

但是從代碼的可讀性上來看，IN 要比 EXISTS 好，使用 IN 時的代碼看起來更加一目了然，易于理解

因此，如果確信使用 IN 也能快速獲取結(jié)果，就沒有必要非得改成 EXISTS 了

其實有很多數(shù)據(jù)庫也嘗試著改善了 IN 的性能

Oracle 數(shù)據(jù)庫中，如果我們在有索引的列上使用 IN， 也會先掃描索引

PostgreSQL 從版 本 7.4 起也改善了使用子查詢作為 IN 謂詞參數(shù)時的查詢速度

說不定在未來的某一天，無論在哪個關(guān)系型數(shù)據(jù)庫上，IN 都能具備與 EXISTS 一樣的性能

關(guān)于 EXISTS，更多詳情可查看：神奇的 SQL 之謂詞 → 難理解的 EXISTS

使用連接代替 IN

其實在平時工作當中，更多的是用連接代替 IN 來改善查詢性能，而非 EXISTS，不是說連接更好，而是 EXISTS 很難掌握

回到問題：查詢有充值記錄的顧客信息，如果用連接來實現(xiàn)，SQL 改如何寫？

 

這種寫法能充分利用索引；而且，因為沒有了子查詢，所以數(shù)據(jù)庫也不會生成中間表；所以，查詢效率是不錯的

至于 JOIN 與 EXISTS 相比哪個性能更好，不太好說；如果沒有索引，可能 EXISTS 會略勝一籌，有索引的話，兩者差不多

避免排序

說到 SQL 的排序，我們第一時間想到的肯定是： ORDER BY ，通過它，我們可以按指定的某些列來順序輸出結(jié)果

但是，除了 ORDER BY 顯示的排序，數(shù)據(jù)庫內(nèi)部還有很多運算在暗中進行排序；會進行排序的代表性的運算有下面這些

 

如果只在內(nèi)存中進行排序，那么還好；但是如果因內(nèi)存不足而需要在硬盤上排序，那么性能就會急劇下降

因此，盡量避免（或減少）無謂的排序，能夠大大提高查詢效率

靈活使用集合運算符的 ALL 可選項

SQL 中有 UNION 、 INTERSECT 、 EXCEPT 三個集合運算符，分表代表這集合運算的 并集、交集、差集

默認情況下，這些運算符會為了排除掉重復數(shù)據(jù)而進行排序

 

Using temporary 表示進行了排序或分組，顯然這個 SQL 沒有進行分組，而是進行了排序運算

如果我們不在乎結(jié)果中是否有重復數(shù)據(jù)，或者事先知道不會有重復數(shù)據(jù)，可以使用 UNION ALL 代替 UNION

 

可以看到，執(zhí)行計劃中沒有排序運算了

對于 INTERSECT 和 EXCEPT 也是一樣的，加上 ALL 可選項后就不會進行排序了

加上 ALL 可選項是一個非常有效的優(yōu)化手段，但各個數(shù)據(jù)庫對它的實現(xiàn)情況卻是參差不齊，如下圖所示

 

注意：Oracle 使用 MINUS 代替 EXCEPT ；MySQL 壓根就沒有實現(xiàn) INTERSECT 和 EXCEPT 運算

使用 EXISTS 代替 DISTINCT

為了排除重復數(shù)據(jù)， DISTINCT 也會進行排序

還記得用連接代替 IN 的案例嗎，如果不用 DISTINCT

SQL： SELECT tc.* FROM tbl_recharge_record trr LEFT JOIN tbl_customer tc on trr.customer_id = tc.id

那么查出來的結(jié)果會有很多重復記錄，我們改進 SQL

SELECT DISTINCT tc.* FROM tbl_recharge_record trr LEFT JOIN tbl_customer tc on trr.customer_id = tc.id

會發(fā)現(xiàn)執(zhí)行計劃中有個 Using temporary ，表示用到了排序運算

 

我們使用 EXISTS 來進行優(yōu)化

 

可以看到，已經(jīng)規(guī)避了排序運算

在極值函數(shù)中使用索引

SQL 語言里有兩個極值函數(shù)： MAX 和 MIN ，使用這兩個函數(shù)時都會進行排序

例如： SELECT MAX(recharge_amount) FROM tbl_recharge_record

會進行全表掃描，并會進行隱式的排序，找出單筆充值最大的金額

但是如果參數(shù)字段上建有索引，則只需要掃描索引，不需要掃描整張表

例如： SELECT MAX(customer_id) FROM tbl_recharge_record;

會通過索引： idx_c_id 進行掃描，找出充值記錄中最大的顧客ID

這種方法并不是去掉了排序這一過程，而是優(yōu)化了排序前的查找速度，從而減弱排序?qū)φw性能的影響

能寫在 WHERE 子句里的條件不要寫在 HAVING 子句里

我們來看兩個 SQL 以及其執(zhí)行結(jié)果

 

從結(jié)果上來看，兩條 SQL 一樣；但是從性能上來看，第二條語句寫法效率更高，原因有兩個

減少排序的數(shù)據(jù)量

GROUP BY 子句聚合時會進行排序，如果事先通過 WHERE 子句篩選出一部分行，就能夠減輕排序的負擔

有效利用索引

WHERE 子句的條件里可以使用索引

HAVING 子句是針對聚合后生成的視圖進行篩選的，但是很多時候聚合后的視圖都沒有繼承原表的索引結(jié)構(gòu)

關(guān)于 HAVING，更多詳情可查看：神奇的 SQL 之 HAVING → 容易被輕視的主角

在 GROUP BY 子句和 ORDER BY 子句中使用索引

一般來說，GROUP BY 子句和 ORDER BY 子句都會進行排序

如果 GROUP BY 和 ORDER BY 的列有索引，那么可以提高查詢效率

特別是在一些數(shù)據(jù)庫中，如果列上建立的是唯一索引，那么排序過程本身都會被省略掉

使用索引

使用索引是最常用的 SQL 優(yōu)化手段，這個大家都知道，怕就怕大家不知道：明明有索引，為什么查詢還是這么慢（為什么索引沒用上）

關(guān)于索引未用到的情況，可查看：神奇的 SQL 之擦肩而過 → 真的用到索引了嗎，本文就不做過多闡述了

總之就是：查詢盡量往索引上靠，規(guī)避索引未用上的情況

減少臨時表

在 SQL 中，子查詢的結(jié)果會被看成一張新表（臨時表），這張新表與原始表一樣，可以通過 SQL 進行操作

但是，頻繁使用臨時表會帶來兩個問題

1、臨時表相當于原表數(shù)據(jù)的一份備份，會耗費內(nèi)存資源

2、很多時候（特別是聚合時），臨時表沒有繼承原表的索引結(jié)構(gòu)

因此，盡量減少臨時表的使用也是提升性能的一個重要方法

靈活使用 HAVING 子句

對聚合結(jié)果指定篩選條件時，使用 HAVING 子句是基本原則

但是如果對 HAVING 不熟，我們往往找出替代它的方式來實現(xiàn)，就像這樣

 

然而，對聚合結(jié)果指定篩選條件時不需要專門生成中間表，像下面這樣使用 HAVING 子句就可以

 

HAVING 子句和聚合操作是同時執(zhí)行的，所以比起生成臨時表后再執(zhí)行 WHERE 子句，效率會更高一些，而且代碼看起來也更簡潔

需要對多個字段使用 IN 謂詞時，將它們匯總到一處

SQL-92 中加入了行與行比較的功能，這樣一來，比較謂詞 = 、< 、> 和 IN 謂詞的參數(shù)就不再只是標量值了，而應是值列表了

我們來看一個示例，多個字段使用 IN 謂詞

 

這段代碼中用到了兩個子查詢，我們可以進行列匯總優(yōu)化，把邏輯寫在一起

 

這樣一來，子查詢不用考慮關(guān)聯(lián)性，而且只執(zhí)行一次就可以

還可以進一步簡化，在 IN 中寫多個字段的組合

 

簡化后，不用擔心連接字段時出現(xiàn)的類型轉(zhuǎn)換問題，也不會對字段進行加工，因此可以使用索引

先進行連接再進行聚合

連接和聚合同時使用時，先進行連接操作可以避免產(chǎn)生中間表

合理地使用視圖

視圖是非常方便的工具，我們在日常工作中經(jīng)常使用

但是，如果沒有經(jīng)過深入思考就定義復雜的視圖，可能會帶來巨大的性能問題

特別是視圖的定義語句中包含以下運算的時候，SQL 會非常低效，執(zhí)行速度也會變得非常慢

 

總結(jié)

文中雖然列舉了幾個要點，但其實優(yōu)化的核心思想只有一個，那就是找出性能瓶頸所在，然后解決它

其實不只是數(shù)據(jù)庫和 SQL，計算機世界里容易成為性能瓶頸的也是對硬盤，也就是文件系統(tǒng)的訪問（因此可以通過增加內(nèi)存，或者使用訪問速度更快的硬盤等方法來提升性能）

不管是減少排序還是使用索引，亦或是避免臨時表的使用，其本質(zhì)都是為了減少對硬盤的訪問

小結(jié)下文中的 Tips

1、參數(shù)是子查詢時，使用 EXISTS 或者 JOIN 代替 IN

2、在 SQL 中，很多運算都會暗中進行排序，盡量規(guī)避這些運算

3、SQL 的書寫，盡量往索引上靠，避免用不上索引的情況

4、盡量減少使用中間表