SQL優化這5個極簡法則，直接讓查詢原地起飛！

SQL 作為關係型資料庫的標準語言，是 IT 從業人員必不可少的技能之一。SQL 本身並不難學，編寫查詢語句也很容易，但是想要編寫出能夠高效運行的查詢語句卻有一定的難度。

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

以下法則適用於各種關係型資料庫，包括但不限於：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。

一：只返回需要的結果

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

                                                                                                                              Clustered index

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

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

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

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

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

• 經常出現在 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 欄位上存在索引，這種分頁查詢的方式可以基本不受數據量的影響。

五：瞭解 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 優化本質上是瞭解優化器的的工作原理，並且為此創建合適的索引和正確的語句；同時，當優化器不夠智能的時候，手動讓它智能。

作者丨不剪髮的Tony老師

本文來自博客園，作者：古道輕風，轉載請註明原文鏈接：https://www.cnblogs.com/88223100/p/The-Law-of-Minimalism-in-SQL-Optimization.html