不許不會的 SQL 優化極簡法則

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

推薦（免費）：SQL教學

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

以下法則適用於各種關聯式資料庫，包括但不限於：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。如果覺得文章有用，歡迎留言、按讚、轉發朋友圈支持。

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

一定要為查詢語句指定 WHERE 條件，過濾掉不需要的資料行。通常來說，OLTP 系統每次只需要從大量資料中傳回很少的幾筆記錄；指定查詢條件可以幫助我們透過索引傳回結果，而不是全表掃描。絕大多數情況下使用索引時的效能較好，因為索引（B-樹、B 樹、B*樹）執行的是二元搜索，具有對數時間複雜度，而不是線性時間複雜度。以下是 MySQL 叢集索引的示意圖：舉例來說，假設每個索引分支節點可以儲存 100 個記錄，100 萬（1003）筆記錄只需要 3 層 B-樹即可完成索引。透過索引尋找資料時需要讀取 3 次索引資料（每次磁碟 IO 讀取整個分支節點），加上 1 次磁碟 IO 讀取資料即可取得查詢結果。純乾貨！ 15000 字語法手冊分享給你

相反，如果採用全表掃描，需要執行的磁碟 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 进行重写。搜索公众号 民工哥技术之路，回复“1024”，送你一份技术资源大礼包。

法则四：不要使用 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 字段已经不存在；从业务逻辑上来说，按照部门分组统计之后再显示某个员工的姓名没有意义。如果需要同时显示员工信息和所在部门的汇总，可以使用窗口函数。扩展：SQL 语法速成手册

还有一些逻辑问题可能不会直接导致查询出错，但是会返回不正确的结果；例如外连接查询中的 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 优化本质上是了解优化器的的工作原理，并且为此创建合适的索引和正确的语句；同时，当优化器不够智能的时候，手动让它智能。

以上是不許不會的 SQL 優化極簡法則的詳細內容。更多資訊請關注PHP中文網其他相關文章！