SQL 優化極簡法則，還有誰不會？

文章目錄

大家好！我是隻談技術不剪髮的 Tony 老師。SQL 作爲關係型數據庫的標準語言，是 IT 從業人員必不可少的技能之一。SQL 本身並不難學，編寫查詢語句也很容易，但是想要編寫出能夠高效運行的查詢語句卻有一定的難度。

查詢優化是一個複雜的工程，涉及從硬件到參數配置、不同數據庫的解析器、優化器實現、SQL 語句的執行順序、索引以及統計信息的採集等，甚至應用程序和系統的整體架構。本文介紹幾個關鍵法則，可以幫助我們編寫高效的 SQL 查詢；尤其是對於初學者而言，這些法則至少可以避免我們寫出性能很差的查詢語句。

以下法則適用於各種關係型數據庫，包括但不限於：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。如果覺得文章有用，歡迎評論📝、點贊👍、推薦🎁

法則一：只返回需要的結果

一定要爲查詢語句指定 WHERE 條件，過濾掉不需要的數據行。通常來說，OLTP 系統每次只需要從大量數據中返回很少的幾條記錄；指定查詢條件可以幫助我們通過索引返回結果，而不是全表掃描。絕大多數情況下使用索引時的性能更好，因爲索引（B - 樹、B + 樹、B * 樹）執行的是二進制搜索，具有對數時間複雜度，而不是線性時間複雜度。以下是 MySQL 聚簇索引的示意圖：

舉例來說，假設每個索引分支節點可以存儲 100 個記錄，100 萬（1003）條記錄只需要 3 層 B - 樹即可完成索引。通過索引查找數據時需要讀取 3 次索引數據（每次磁盤 IO 讀取整個分支節點），加上 1 次磁盤 IO 讀取數據即可得到查詢結果。

相反，如果採用全表掃描，需要執行的磁盤 IO 次數可能高出幾個數量級。當數據量增加到 1 億（1004）時，B - 樹索引只需要再增加 1 次索引 IO 即可；而全表掃描則需要再增加幾個數量級的 IO。

同理，我們應該避免使用 SELECT * FROM，因爲它表示查詢表中的所有字段。這種寫法通常導致數據庫需要讀取更多的數據，同時網絡也需要傳輸更多的數據，從而導致性能的下降。

📝關於 B - 樹索引的原理以及利用索引優化各種查詢條件、連接查詢、排序和分組以及 DML 語句的介紹，可以參考這篇文章。

法則二：確保查詢使用了正確的索引

如果缺少合適的索引，即使指定了查詢條件也不會通過索引查找數據。因此，我們首先需要確保創建了相應的索引。一般來說，以下字段需要創建索引：

經常出現在 WHERE 條件中的字段建立索引可以避免全表掃描；
將 ORDER BY 排序的字段加入到索引中，可以避免額外的排序操作；
多表連接查詢的關聯字段建立索引，可以提高連接查詢的性能；
將 GROUP BY 分組操作字段加入到索引中，可以利用索引完成分組。

即使創建了合適的索引，如果 SQL 語句寫的有問題，數據庫也不會使用索引。導致索引失效的常見問題包括：

在 WHERE 子句中對索引字段進行表達式運算或者使用函數都會導致索引失效，這種情況還包括字段的數據類型不匹配，例如字符串和整數進行比較；
使用 LIKE 匹配時，如果通配符出現在左側無法使用索引。對於大型文本數據的模糊匹配，應該考慮數據庫提供的全文檢索功能，甚至專門的全文搜索引擎（Elasticsearch 等）；
如果 WHERE 條件中的字段上創建了索引，儘量設置爲 NOT NULL；不是所有數據庫使用 IS [NOT] NULL 判斷時都可以利用索引。

執行計劃（execution plan，也叫查詢計劃或者解釋計劃）是數據庫執行 SQL 語句的具體步驟，例如通過索引還是全表掃描訪問表中的數據，連接查詢的實現方式和連接的順序等。如果 SQL 語句性能不夠理想，我們首先應該查看它的執行計劃，通過執行計劃（EXPLAIN）確保查詢使用了正確的索引。

📝關於各種主流數據庫中執行計劃的查看和解釋，可以參考這篇文章和這篇文章。

法則三：儘量避免使用子查詢

以 MySQL 爲例，以下查詢返回月薪大於部門平均月薪的員工信息：

EXPLAIN ANALYZE
 SELECT emp_id, emp_name
   FROM employee e
   WHERE salary > (
     SELECT AVG(salary)
       FROM employee
       WHERE dept_id = e.dept_id);
-> Filter: (e.salary > (select #2))  (cost=2.75 rows=25) (actual time=0.232..4.401 rows=6 loops=1)
    -> Table scan on e  (cost=2.75 rows=25) (actual time=0.099..0.190 rows=25 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Aggregate: avg(employee.salary)  (actual time=0.147..0.149 rows=1 loops=25)
            -> Index lookup on employee using idx_emp_dept (dept_id=e.dept_id)  (cost=1.12 rows=5) (actual time=0.068..0.104 rows=7 loops=25)

從執行計劃可以看出，MySQL 中採用的是類似 Nested Loop Join 實現方式；子查詢循環了 25 次，而實際上可以通過一次掃描計算並緩存每個部門的平均月薪。以下語句將該子查詢替換爲等價的 JOIN 語句，實現了子查詢的展開（Subquery Unnest）：

EXPLAIN ANALYZE
 SELECT e.emp_id, e.emp_name
   FROM employee e
   JOIN (SELECT dept_id, AVG(salary) AS dept_average
           FROM employee
          GROUP BY dept_id) t
     ON e.dept_id = t.dept_id
  WHERE e.salary > t.dept_average;
-> Nested loop inner join  (actual time=0.722..2.354 rows=6 loops=1)
    -> Table scan on e  (cost=2.75 rows=25) (actual time=0.096..0.205 rows=25 loops=1)
    -> Filter: (e.salary > t.dept_average)  (actual time=0.068..0.076 rows=0 loops=25)
        -> Index lookup on t using <auto_key0> (dept_id=e.dept_id)  (actual time=0.011..0.015 rows=1 loops=25)
            -> Materialize  (actual time=0.048..0.057 rows=1 loops=25)
                -> Group aggregate: avg(employee.salary)  (actual time=0.228..0.510 rows=5 loops=1)
                    -> Index scan on employee using idx_emp_dept  (cost=2.75 rows=25) (actual time=0.181..0.348 rows=25 loops=1)

改寫之後的查詢利用了物化（Materialization）技術，將子查詢的結果生成一個內存臨時表；然後與 employee 表進行連接。通過實際執行時間可以看出這種方式更快。

以上示例在 Oracle 和 SQL Server 中會自動執行子查詢展開，兩種寫法效果相同；在 PostgreSQL 中與 MySQL 類似，第一個語句使用 Nested Loop Join，改寫爲 JOIN 之後使用 Hash Join 實現，性能更好。

另外，對於 IN 和 EXISTS 子查詢也可以得出類似的結論。由於不同數據庫的優化器能力有所差異，我們應該儘量避免使用子查詢，考慮使用 JOIN 進行重寫。

法則四：不要使用 OFFSET 實現分頁

分頁查詢的原理就是先跳過指定的行數，再返回 Top-N 記錄。分頁查詢的示意圖如下：

數據庫一般支持 FETCH/LIMIT 以及 OFFSET 實現 Top-N 排行榜和分頁查詢。當表中的數據量很大時，這種方式的分頁查詢可能會導致性能問題。以 MySQL 爲例：

-- MySQL
SELECT *
  FROM large_table
 ORDER BY id
 LIMIT 10 OFFSET N;

以上查詢隨着 OFFSET 的增加，速度會越來越慢；因爲即使我們只需要返回 10 條記錄，數據庫仍然需要訪問並且過濾掉 N（比如 1000000）行記錄，即使通過索引也會涉及不必要的掃描操作。

對於以上分頁查詢，更好的方法是記住上一次獲取到的最大 id，然後在下一次查詢中作爲條件傳入：

-- MySQL
SELECT *
  FROM large_table
 WHERE id > last_id
 ORDER BY id
 LIMIT 10;

如果 id 字段上存在索引，這種分頁查詢的方式可以基本不受數據量的影響。

📝關於 Top-N 排行榜和分頁查詢的詳細介紹，可以參考這篇文章。

法則五：瞭解 SQL 子句的邏輯執行順序

以下是 SQL 中各個子句的語法順序，前面括號內的數字代表了它們的邏輯執行順序：

(6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
(1)  FROM t1 JOIN t2
(2)    ON (join_conditions)
(3) WHERE where_conditions
(4) GROUP BY col1, col2
(5)HAVING having_condition
(7) UNION [ALL]
   ...
(8) ORDER BY col1 ASC,col2 DESC
(9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

也就是說，SQL 並不是按照編寫順序先執行 SELECT，然後再執行 FROM 子句。從邏輯上講，SQL 語句的執行順序如下：

首先，FROM 和 JOIN 是 SQL 語句執行的第一步。它們的邏輯結果是一個笛卡爾積，決定了接下來要操作的數據集。注意邏輯執行順序並不代表物理執行順序，實際上數據庫在獲取表中的數據之前會使用 ON 和 WHERE 過濾條件進行優化訪問；
其次，應用 ON 條件對上一步的結果進行過濾並生成新的數據集；
然後，執行 WHERE 子句對上一步的數據集再次進行過濾。WHERE 和 ON 大多數情況下的效果相同，但是外連接查詢有所區別，我們將會在下文給出示例；
接着，基於 GROUP BY 子句指定的表達式進行分組；同時，對於每個分組計算聚合函數 agg_func 的結果。經過 GROUP BY 處理之後，數據集的結構就發生了變化，只保留了分組字段和聚合函數的結果；
如果存在 GROUP BY 子句，可以利用 HAVING 針對分組後的結果進一步進行過濾，通常是針對聚合函數的結果進行過濾；
接下來，SELECT 可以指定要返回的列；如果指定了 DISTINCT 關鍵字，需要對結果集進行去重操作。另外還會爲指定了 AS 的字段生成別名；
如果還有集合操作符（UNION、INTERSECT、EXCEPT）和其他的 SELECT 語句，執行該查詢並且合併兩個結果集。對於集合操作中的多個 SELECT 語句，數據庫通常可以支持併發執行；
然後，應用 ORDER BY 子句對結果進行排序。如果存在 GROUP BY 子句或者 DISTINCT 關鍵字，只能使用分組字段和聚合函數進行排序；否則，可以使用 FROM 和 JOIN 表中的任何字段排序；
最後，OFFSET 和 FETCH（LIMIT、TOP）限定了最終返回的行數。

瞭解 SQL 邏輯執行順序可以幫助我們進行 SQL 優化。例如 WHERE 子句在 HAVING 子句之前執行，因此我們應該儘量使用 WHERE 進行數據過濾，避免無謂的操作；除非業務需要針對聚合函數的結果進行過濾。

除此之外，理解 SQL 的邏輯執行順序還可以幫助我們避免一些常見的錯誤，例如以下語句：

-- 錯誤示例
SELECT emp_name AS empname
  FROM employee
 WHERE empname ='張飛';

該語句的錯誤在於 WHERE 條件中引用了列別名；從上面的邏輯順序可以看出，執行 WHERE 條件時還沒有執行 SELECT 子句，也就沒有生成字段的別名。

另外一個需要注意的操作就是 GROUP BY，例如：

-- GROUP BY 錯誤示例
SELECT dept_id, emp_name, AVG(salary)
  FROM employee
 GROUP BY dept_id;

由於經過 GROUP BY 處理之後結果集只保留了分組字段和聚合函數的結果，示例中的 emp_name 字段已經不存在；從業務邏輯上來說，按照部門分組統計之後再顯示某個員工的姓名沒有意義。如果需要同時顯示員工信息和所在部門的彙總，可以使用窗口函數。

📝如果使用了 GROUP BY 分組，之後的 SELECT、ORDER BY 等只能引用分組字段或者聚合函數；否則，可以引用 FROM 和 JOIN 表中的任何字段。

還有一些邏輯問題可能不會直接導致查詢出錯，但是會返回不正確的結果；例如外連接查詢中的 ON 和 WHERE 條件。以下是一個左外連接查詢的示例：

SELECT e.emp_name, d.dept_name
  FROM employee e
  LEFT JOIN department d ON (e.dept_id = d.dept_id)
 WHERE e.emp_name ='張飛';
emp_name|dept_name|
--------|---------|
張飛     |行政管理部|

SELECT e.emp_name, d.dept_name
  FROM employee e
  LEFT JOIN department d ON (e.dept_id = d.dept_id AND e.emp_name ='張飛');
emp_name|dept_name|
--------|---------|
劉備     |   [NULL]|
關羽     |   [NULL]|
張飛     |行政管理部|
諸葛亮   |   [NULL]|
...

第一個查詢在 ON 子句中指定了連接的條件，同時通過 WHERE 子句找出了 “張飛” 的信息。

第二個查詢將所有的過濾條件都放在 ON 子句中，結果返回了所有的員工信息。這是因爲左外連接會返回左表中的全部數據，即使 ON 子句中指定了員工姓名也不會生效；而 WHERE 條件在邏輯上是對連接操作之後的結果進行過濾。

總結

SQL 優化本質上是瞭解優化器的的工作原理，並且爲此創建合適的索引和正確的語句；同時，當優化器不夠智能的時候，手動讓它智能。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://blog.csdn.net/horses/article/details/105695431