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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/501849.html

標籤：其他

上一篇：Hive存盤格式之RCFile詳解，RCFile的過去現在和未來

下一篇：my2sql工具之快速入門