寫在前面
在像 Web 服務這樣需要快速響應的應用場景中，SQL 的性能直接決定了系統是否可以使用；特別在一些中小型應用中，SQL 性能更是決定服務能否快速響應的唯一標準
嚴格地優化查詢性能時，必須要了解所使用資料庫的功能特點，此外，查詢速度慢並不只是因為 SQL 語句本身，還可能是因為內存分配不佳、文件結構不合理、刷髒頁等其他原因
因此本文即將介紹的優化 SQL 的方法不能解決所有的性能問題，但是卻能處理很多因 SQL 寫法不合理而產生的性能問題
下文將儘量介紹一些不依賴具體資料庫實現，使 SQL 執行速度更快、消耗內存更少的優化技巧，只需調整 SQL 語句就能實現的通用的優化Tip
環境準備
下文所講的內容是從 SQL 層面展開的，而不是針對某種特性的資料庫，也就是說，下文的內容基本上適用於任何關係型資料庫
但是，關係型資料庫那麼多，逐一來演示示例了，顯然不太現實；我們以常用的 MySQL 來進行示例的演示
MySQL 版本： 5.7.30-log ，存儲引擎： InnoDB
準備兩張表： tbl_customer 和 tbl_recharge_record
使用高效的查詢
針對某一個查詢，有時候會有多種 SQL 實現，例如 IN、EXISTS、連接之間的互相轉換
從理論上來講，得到相同結果的不同 SQL 語句應該有相同的性能，但遺憾的是，查詢優化器生成的執行計劃很大程度上要受到外部結構的影響
因此，如果想優化查詢性能，必須知道如何寫 SQL 語句才能使優化器生成更高效的執行計劃
使用 EXISTS 代替 IN
關於 IN，相信大家都比較熟悉，使用方便，也容易理解；雖說 IN 使用方便，但它卻存在性能瓶頸
如果 IN 的參數是 1,2,3這樣的數值列表，一般還不需要特別注意，但如果參數是子查詢，那麼就需要注意了
在大多時候， [NOT]IN 和 [NOT]EXISTS 返回的結果是相同的，但是兩者用於子查詢時，EXISTS 的速度會更快一些
假設我們要查詢有充值記錄的顧客信息，SQL 該怎麼寫？
相信大家第一時間想到的是 IN： SELECT*FROM tbl_customer WHERE ID IN (SELECT customer_id FROM tbl_recharge_record);
IN 使用起來確實簡單，也非常好理解；我們來看下它的執行計劃
我們再來看看 EXISTS 的執行計劃：
可以看到，IN 的執行計劃中新產生了一張臨時表： <subquery2> ，這會導致效率變慢
通常來講，EXISTS 比 IN 更快的原因有兩個
1、如果連接列（customer_id）上建立了索引，那麼查詢 tbl_recharge_record 時可以通過索引查詢，而不是全表查詢
2、使用 EXISTS，一旦查到一行數據滿足條件就會終止查詢，不用像使用 IN 時一樣進行掃描全表（NOT EXISTS 也一樣）
當 IN 的參數是子查詢時，資料庫首先會執行子查詢，然後將結果存儲在一張臨時表裡（內聯視圖），然後掃描整個視圖，很多情況下這種做法非常耗費資源
使用 EXISTS 的話，資料庫不會生成臨時表
但是從代碼的可讀性上來看，IN 要比 EXISTS 好，使用 IN 時的代碼看起來更加一目了然，易於理解
因此，如果確信使用 IN 也能快速獲取結果，就沒有必要非得改成 EXISTS 了
其實有很多資料庫也嘗試著改善了 IN 的性能
Oracle 資料庫中，如果我們在有索引的列上使用 IN， 也會先掃描索引
PostgreSQL 從版 本 7.4 起也改善了使用子查詢作為 IN 謂詞參數時的查詢速度
說不定在未來的某一天，無論在哪個關係型資料庫上，IN 都能具備與 EXISTS 一樣的性能
關於 EXISTS，更多詳情可查看：神奇的 SQL 之謂詞 → 難理解的 EXISTS
使用連接代替 IN
其實在平時工作當中，更多的是用連接代替 IN 來改善查詢性能，而非 EXISTS，不是說連接更好，而是 EXISTS 很難掌握
回到問題：查詢有充值記錄的顧客信息，如果用連接來實現，SQL 改如何寫？
這種寫法能充分利用索引；而且，因為沒有了子查詢，所以資料庫也不會生成中間表；所以，查詢效率是不錯的
至於 JOIN 與 EXISTS 相比哪個性能更好，不太好說；如果沒有索引，可能 EXISTS 會略勝一籌，有索引的話，兩者差不多
避免排序
說到 SQL 的排序，我們第一時間想到的肯定是： ORDERBY ，通過它，我們可以按指定的某些列來順序輸出結果
但是，除了 ORDERBY 顯示的排序，資料庫內部還有很多運算在暗中進行排序；會進行排序的代表性的運算有下面這些
如果只在內存中進行排序，那麼還好；但是如果因內存不足而需要在硬碟上排序，那麼性能就會急劇下降
因此，儘量避免（或減少）無謂的排序，能夠大大提高查詢效率
靈活使用集合運算符的 ALL 可選項
SQL 中有 UNION 、 INTERSECT 、 EXCEPT 三個集合運算符，分表代表這集合運算的 併集、交集、差集
默認情況下，這些運算符會為了排除掉重複數據而進行排序
Using temporary 表示進行了排序或分組，顯然這個 SQL 沒有進行分組，而是進行了排序運算
如果我們不在乎結果中是否有重複數據，或者事先知道不會有重複數據，可以使用 UNIONALL 代替 UNION
可以看到，執行計劃中沒有排序運算了
對於 INTERSECT 和 EXCEPT 也是一樣的，加上 ALL 可選項後就不會進行排序了
加上 ALL 可選項是一個非常有效的優化手段，但各個資料庫對它的實現情況卻是參差不齊，如下圖所示
注意：Oracle 使用 MINUS 代替 EXCEPT ；MySQL 壓根就沒有實現 INTERSECT 和 EXCEPT 運算
使用 EXISTS 代替 DISTINCT
為了排除重複數據， DISTINCT 也會進行排序
還記得用連接代替 IN 的案例嗎，如果不用 DISTINCT
SQL： SELECT tc.*FROM tbl_recharge_record trr LEFTJOIN tbl_customer tc on trr.customer_id = tc.id
那麼查出來的結果會有很多重複記錄，我們改進 SQL
SELECTDISTINCT tc.*FROM tbl_recharge_record trr LEFTJOIN tbl_customer tc on trr.customer_id = tc.id
會發現執行計劃中有個 Using temporary ，表示用到了排序運算
我們使用 EXISTS 來進行優化
可以看到，已經規避了排序運算
在極值函數中使用索引
SQL 語言裡有兩個極值函數： MAX 和 MIN ，使用這兩個函數時都會進行排序
例如： SELECTMAX(recharge_amount) FROM tbl_recharge_record
會進行全表掃描，並會進行隱式的排序，找出單筆充值最大的金額
但是如果參數欄位上建有索引，則只需要掃描索引，不需要掃描整張表
例如： SELECTMAX(customer_id) FROM tbl_recharge_record;
會通過索引： idx_c_id 進行掃描，找出充值記錄中最大的顧客ID
這種方法並不是去掉了排序這一過程，而是優化了排序前的查找速度，從而減弱排序對整體性能的影響
能寫在 WHERE 子句裡的條件不要寫在 HAVING 子句裡
我們來看兩個 SQL 以及其執行結果
從結果上來看，兩條 SQL 一樣；但是從性能上來看，第二條語句寫法效率更高，原因有兩個
減少排序的數據量
GROUP BY 子句聚合時會進行排序，如果事先通過 WHERE 子句篩選出一部分行，就能夠減輕排序的負擔
有效利用索引
WHERE 子句的條件裡可以使用索引
HAVING 子句是針對聚合後生成的視圖進行篩選的，但是很多時候聚合後的視圖都沒有繼承原表的索引結構
關於 HAVING，更多詳情可查看：神奇的 SQL 之 HAVING → 容易被輕視的主角
在 GROUP BY 子句和 ORDER BY 子句中使用索引
一般來說，GROUP BY 子句和 ORDER BY 子句都會進行排序
如果 GROUP BY 和 ORDER BY 的列有索引，那麼可以提高查詢效率
特別是在一些資料庫中，如果列上建立的是唯一索引，那麼排序過程本身都會被省略掉
使用索引
使用索引是最常用的 SQL 優化手段，這個大家都知道，怕就怕大家不知道：明明有索引，為什麼查詢還是這麼慢（為什麼索引沒用上）
關於索引未用到的情況，可查看：神奇的 SQL 之擦肩而過 → 真的用到索引了嗎，本文就不做過多闡述了
總之就是：查詢儘量往索引上靠，規避索引未用上的情況
減少臨時表
在 SQL 中，子查詢的結果會被看成一張新表（臨時表），這張新表與原始表一樣，可以通過 SQL 進行操作
但是，頻繁使用臨時表會帶來兩個問題
1、臨時表相當於原表數據的一份備份，會耗費內存資源
2、很多時候（特別是聚合時），臨時表沒有繼承原表的索引結構
因此，儘量減少臨時表的使用也是提升性能的一個重要方法
靈活使用 HAVING 子句
對聚合結果指定篩選條件時，使用 HAVING 子句是基本原則
但是如果對 HAVING 不熟，我們往往找出替代它的方式來實現，就像這樣
然而，對聚合結果指定篩選條件時不需要專門生成中間表，像下面這樣使用 HAVING 子句就可以
HAVING 子句和聚合操作是同時執行的，所以比起生成臨時表後再執行 WHERE 子句，效率會更高一些，而且代碼看起來也更簡潔
需要對多個欄位使用 IN 謂詞時，將它們匯總到一處
SQL-92 中加入了行與行比較的功能，這樣一來，比較謂詞 = 、< 、> 和 IN 謂詞的參數就不再只是標量值了，而應是值列表了
我們來看一個示例，多個欄位使用 IN 謂詞
這段代碼中用到了兩個子查詢，我們可以進行列匯總優化，把邏輯寫在一起
這樣一來，子查詢不用考慮關聯性，而且只執行一次就可以
還可以進一步簡化，在 IN 中寫多個欄位的組合
簡化後，不用擔心連接欄位時出現的類型轉換問題，也不會對欄位進行加工，因此可以使用索引
先進行連接再進行聚合
連接和聚合同時使用時，先進行連接操作可以避免產生中間表
合理地使用視圖
視圖是非常方便的工具，我們在日常工作中經常使用
但是，如果沒有經過深入思考就定義複雜的視圖，可能會帶來巨大的性能問題
特別是視圖的定義語句中包含以下運算的時候，SQL 會非常低效，執行速度也會變得非常慢
總結
文中雖然列舉了幾個要點，但其實優化的核心思想只有一個，那就是找出性能瓶頸所在，然後解決它
其實不只是資料庫和 SQL，計算機世界裡容易成為性能瓶頸的也是對硬碟，也就是文件系統的訪問（因此可以通過增加內存，或者使用訪問速度更快的硬碟等方法來提升性能）
不管是減少排序還是使用索引，亦或是避免臨時表的使用，其本質都是為了減少對硬碟的訪問
小結下文中的 Tips
1、參數是子查詢時，使用 EXISTS 或者 JOIN 代替 IN
2、在 SQL 中，很多運算都會暗中進行排序，儘量規避這些運算
3、SQL 的書寫，儘量往索引上靠，避免用不上索引的情況
4、儘量減少使用中間表