不許不會的 SQL 優化極簡法則-SQL-PHP中文網

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

不許不會的 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中文網其他相關文章！