神奇的 SQL 之效能優化 → 讓 SQL 飛起來

青石路發表於2021-01-05

開心一刻

  一天,一個男人去未婚妻家玩,晚上臨走時下起了大雨

  未婚妻勸他留下來過夜,說完便去準備被褥,準備就緒後發現未婚夫不見了

  過了好久,全身淋的像只落湯雞的未婚夫回來了

  未婚妻吃驚的問:“你跑哪去了”

  未婚夫上氣不接下氣的回答:“我,我回家拿睡衣去了”

寫在前面

  在像 Web 服務這樣需要快速響應的應用場景中,SQL 的效能直接決定了系統是否可以使用;特別在一些中小型應用中,SQL 效能更是決定服務能否快速響應的唯一標準

  嚴格地優化查詢效能時,必須要了解所使用資料庫的功能特點,此外,查詢速度慢並不只是因為 SQL 語句本身,還可能是因為記憶體分配不佳、檔案結構不合理、刷髒頁等其他原因

  因此本文即將介紹的優化 SQL 的方法不能解決所有的效能問題,但是卻能處理很多因 SQL 寫法不合理而產生的效能問題

  下文將盡量介紹一些不依賴具體資料庫實現,使 SQL 執行速度更快、消耗記憶體更少的優化技巧,只需調整 SQL 語句就能實現的通用的優化 Tips

  說句很重要的心裡話:祝大家在 2021 年,健康好運,平安幸福!

環境準備

  下文所講的內容是從 SQL 層面展開的,而不是針對某種特性的資料庫,也就是說,下文的內容基本上適用於任何關係型資料庫

  但是,關係型資料庫那麼多,逐一來演示示例了,顯然不太現實;我們以常用的 MySQL 來進行示例的演示

  MySQL 版本: 5.7.30-log ,儲存引擎: InnoDB 

  準備兩張表: tbl_customer 和 tbl_recharge_record 

神奇的 SQL 之效能優化 → 讓 SQL 飛起來
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:銀聯,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());
View Code

  後文中, EXPLAIN 談的比較多,具體可檢視:神奇的 SQL 之 MySQL 執行計劃 → EXPLAIN,讓我們瞭解 SQL 的執行過程!

使用高效的查詢

  針對某一個查詢,有時候會有多種 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 使用起來確實簡單,也非常好理解;我們來看下它的執行計劃

神奇的 SQL 之效能優化 → 讓 SQL 飛起來

    我們再來看看 EXISTS 的執行計劃:

神奇的 SQL 之效能優化 → 讓 SQL 飛起來

    可以看到,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 改如何寫?

神奇的 SQL 之效能優化 → 讓 SQL 飛起來

    這種寫法能充分利用索引;而且,因為沒有了子查詢,所以資料庫也不會生成中間表;所以,查詢效率是不錯的

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

避免排序

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

  但是,除了 ORDER BY 顯示的排序,資料庫內部還有很多運算在暗中進行排序;會進行排序的代表性的運算有下面這些

  

  如果只在記憶體中進行排序,那麼還好;但是如果因記憶體不足而需要在硬碟上排序,那麼效能就會急劇下降

  因此,儘量避免(或減少)無謂的排序,能夠大大提高查詢效率

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

    SQL 中有 UNION  、 INTERSECT  、 EXCEPT 三個集合運算子,分表代表這集合運算的 並集、交集、差集

    預設情況下,這些運算子會為了排除掉重複資料而進行排序

神奇的 SQL 之效能優化 → 讓 SQL 飛起來

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

    如果我們不在乎結果中是否有重複資料,或者事先知道不會有重複資料,可以使用 UNION ALL 代替 UNION 

神奇的 SQL 之效能優化 → 讓 SQL 飛起來

    可以看到,執行計劃中沒有排序運算了

    對於 INTERSECT 和 EXCEPT 也是一樣的,加上 ALL 可選項後就不會進行排序了

    加上 ALL 可選項是一個非常有效的優化手段,但各個資料庫對它的實現情況卻是參差不齊,如下圖所示

    

    注意:Oracle 使用 MINUS 代替 EXCEPT ;MySQL 壓根就沒有實現 INTERSECT 和 EXCEPT 運算

  使用 EXISTS 代替 DISTINCT

    為了排除重複資料, 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 

    會發現執行計劃中有個 Using temporary ,表示用到了排序運算

神奇的 SQL 之效能優化 → 讓 SQL 飛起來

    我們使用 EXISTS 來進行優化

神奇的 SQL 之效能優化 → 讓 SQL 飛起來

    可以看到,已經規避了排序運算

  在極值函式中使用索引

    SQL 語言裡有兩個極值函式: MAX 和 MIN ,使用這兩個函式時都會進行排序

    例如: SELECT MAX(recharge_amount) FROM tbl_recharge_record 

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

    但是如果引數欄位上建有索引,則只需要掃描索引,不需要掃描整張表

    例如: SELECT MAX(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、儘量減少使用中間表

參考

  《SQL進階教程》

相關文章