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

qwer1030274531發表於2020-11-06
SQL

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

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

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

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

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

Clustered index

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

相反，如果採用全表掃描，需要執行的磁碟 IO 次數可能高出幾個數量級。當資料量增加到 1 億（100 4）時，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)123456789101112

從執行計劃可以看出，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)123456789101112131415

改寫之後的查詢利用了物化（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 為例： 

-- MySQLSELECT *
  FROM large_table ORDER BY id LIMIT 10 OFFSET N;12345

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

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

-- MySQLSELECT *
  FROM large_table WHERE id > last_id ORDER BY id LIMIT 10;123456

如果 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;12345678910

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

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

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

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

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

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

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

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

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

?如果使用了 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]|...123456789101112131415161718

第一個查詢在 ON 子句中指定了連線的條件，同時透過 WHERE 子句找出了“張飛”的資訊。

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

總結 /secondary/

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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30239065/viewspace-2732600/，如需轉載，請註明出處，否則將追究法律責任。

相關文章