神奇的 SQL 之性能優(yōu)化，讓 SQL 飛起來

寫在前面

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

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

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

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

說句很重要的心里話：祝大家在 2021 年，健康好運，平安幸福！

環(huán)境準備

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

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

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

準備兩張表：tbl_customer?和?tbl_recharge_record

DROP?TABLE?IF?EXISTS?tbl_customer;
CREATE?TABLE?tbl_customer?(
??id?INT(11)?UNSIGNED?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??name?VARCHAR(50)?NOT?NULL?COMMENT?'顧客姓名',
??age?TINYINT(3)?NOT?NULL?COMMENT?'年齡',
??id_card?CHAR(18)?NOT?NULL?COMMENT?'身份證',
??phone_number?CHAR(11)?NOT?NULL?COMMENT?'手機號碼',
??PRIMARY?KEY?(id)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COMMENT='顧客表';

INSERT?INTO?tbl_customer(name,?age,id_card,phone_number)?VALUES
('張三',19,'430682198109129210','15174480311'),
('李四',21,'430682198109129211','15174480312'),
('王五',22,'430682198109129212','15174480313'),
('六一',23,'430682198109129213','15174480314'),
('六二',25,'430682198109129214','15174480315'),
('六三',27,'430682198109129215','15174480316'),
('六四',29,'430682198109129216','15174480317');

DROP?TABLE?IF?EXISTS?tbl_recharge_record;
CREATE?TABLE?tbl_recharge_record?(
??id?INT(11)?UNSIGNED?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??customer_id?INT(11)?NOT?NULL?COMMENT?'顧客ID',
??recharge_type?TINYINT(2)?NOT?NULL?COMMENT?'充值方式?1:支付寶,?2:微信,3:QQ,4:京東,5:銀聯(lián),6:信用卡,7:其他',
??recharge_amount?DECIMAL(15,2)?NOT?NULL?COMMENT?'充值金額,?單位元',
??recharge_time?DATETIME?NOT?NULL?COMMENT?'充值時間',
??remark?VARCHAR(500)?NOT?NULL?DEFAULT?'remark'?COMMENT?'備注',
??PRIMARY?KEY?(id),
??KEY?idx_c_id(customer_id)
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COMMENT='顧客充值記錄表';

INSERT?INTO?tbl_recharge_record(customer_id,recharge_type,recharge_amount,recharge_time)?VALUES
(1,1,10000,NOW()),
(2,2,20000,NOW()),
(1,2,10000,NOW()),
(1,3,10000,NOW()),
(2,7,20000,NOW()),
(3,3,15000,NOW()),
(4,1,10000,NOW()),
(5,1,10000,NOW()),
(6,1,10000,NOW()),
(7,1,10000,NOW()),
(7,1,10000,NOW()),
(6,1,10000,NOW()),
(5,1,10000,NOW()),
(4,1,10000,NOW()),
(3,1,10000,NOW()),
(2,1,10000,NOW()),
(1,1,10000,NOW()),
(2,1,10000,NOW()),
(3,1,10000,NOW()),
(2,1,10000,NOW()),
(3,1,10000,NOW()),
(4,1,10000,NOW()),
(2,1,10000,NOW()),
(2,1,10000,NOW()),
(1,1,10000,NOW());

后文中，?EXPLAIN?談的比較多，具體可查看：

神奇的 SQL 之 MySQL 執(zhí)行計劃 → EXPLAIN，讓我們了解 SQL 的執(zhí)行過程！

cnblogs.com/youzhibing/p/11909681.html

使用高效的查詢

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

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

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

使用 EXISTS 代替 IN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

關于 EXISTS，更多詳情可查看：

神奇的 SQL 之謂詞 → 難理解的 EXISTS

cnblogs.com/youzhibing/p/11385136.html

使用連接代替 IN

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

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

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

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

避免排序

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

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

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

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

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

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

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

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

如果我們不在乎結果中是否有重復數(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

那么查出來的結果會有很多重復記錄，我們改進 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í)行結果

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

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

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

有效利用索引

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

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

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

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

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

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

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

使用索引

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

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

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

減少臨時表

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

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

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

2、很多時候（特別是聚合時），臨時表沒有繼承原表的索引結構

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

靈活使用 HAVING 子句

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

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

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

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

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

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

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

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

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

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

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

先進行連接再進行聚合

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

合理地使用視圖

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

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

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

總結

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

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

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

小結下文中的 Tips

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

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

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

4、盡量減少使用臨時表

參考

《SQL進階教程》

來源：cnblogs.com/youzhibing/p/11909821.html

版權申明：內(nèi)容來源網(wǎng)絡，版權歸原創(chuàng)者所有。除非無法確認，我們都會標明作者及出處，如有侵權煩請告知，我們會立即刪除并表示歉意。謝謝!

---

感謝閱讀