SQL 本身并不難學(xué)，編寫查詢語句也很容易，但是想要編寫出能夠高效運(yùn)行的查詢語句卻有一定的難度。

[[373751]] 

圖片來自 Pexels

查詢優(yōu)化是一個(gè)復(fù)雜的工程，涉及從硬件到參數(shù)配置、不同數(shù)據(jù)庫的解析器、優(yōu)化器實(shí)現(xiàn)、SQL 語句的執(zhí)行順序、索引以及統(tǒng)計(jì)信息的采集等，甚至應(yīng)用程序和系統(tǒng)的整體架構(gòu)。

本文介紹幾個(gè)關(guān)鍵法則，可以幫助我們編寫高效的 SQL 查詢;尤其是對于初學(xué)者而言，這些法則至少可以避免我們寫出性能很差的查詢語句。

以下法則適用于各種關(guān)系型數(shù)據(jù)庫，包括但不限于：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。

法則一：只返回需要的結(jié)果

一定要為查詢語句指定 WHERE 條件，過濾掉不需要的數(shù)據(jù)行。

通常來說，OLTP 系統(tǒng)每次只需要從大量數(shù)據(jù)中返回很少的幾條記錄;指定查詢條件可以幫助我們通過索引返回結(jié)果，而不是全表掃描。

絕大多數(shù)情況下使用索引時(shí)的性能更好，因?yàn)樗饕?B-樹、B+樹、B*樹)執(zhí)行的是二進(jìn)制搜索，具有對數(shù)時(shí)間復(fù)雜度，而不是線性時(shí)間復(fù)雜度。

以下是 MySQL 聚簇索引的示意圖：

 

舉例來說，假設(shè)每個(gè)索引分支節(jié)點(diǎn)可以存儲(chǔ) 100 個(gè)記錄，100 萬(1003)條記錄只需要 3 層 B- 樹即可完成索引。

通過索引查找數(shù)據(jù)時(shí)需要讀取 3 次索引數(shù)據(jù)(每次磁盤 IO 讀取整個(gè)分支節(jié)點(diǎn))，加上 1 次磁盤 IO 讀取數(shù)據(jù)即可得到查詢結(jié)果。

相反，如果采用全表掃描，需要執(zhí)行的磁盤 IO 次數(shù)可能高出幾個(gè)數(shù)量級(jí)。當(dāng)數(shù)據(jù)量增加到 1 億(1004)時(shí)，B- 樹索引只需要再增加 1 次索引 IO 即可;而全表掃描則需要再增加幾個(gè)數(shù)量級(jí)的 IO。

同理，我們應(yīng)該避免使用 SELECT * FROM， 因?yàn)樗硎静樵儽碇械乃凶侄巍?/p>

這種寫法通常導(dǎo)致數(shù)據(jù)庫需要讀取更多的數(shù)據(jù)，同時(shí)網(wǎng)絡(luò)也需要傳輸更多的數(shù)據(jù)，從而導(dǎo)致性能的下降。

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

如果缺少合適的索引，即使指定了查詢條件也不會(huì)通過索引查找數(shù)據(jù)。因此，我們首先需要確保創(chuàng)建了相應(yīng)的索引。

一般來說，以下字段需要?jiǎng)?chuàng)建索引：

• 經(jīng)常出現(xiàn)在 WHERE 條件中的字段建立索引可以避免全表掃描。
• 將 ORDER BY 排序的字段加入到索引中，可以避免額外的排序操作。
• 多表連接查詢的關(guān)聯(lián)字段建立索引，可以提高連接查詢的性能。
• 將 GROUP BY 分組操作字段加入到索引中，可以利用索引完成分組。

即使創(chuàng)建了合適的索引，如果 SQL 語句寫的有問題，數(shù)據(jù)庫也不會(huì)使用索引。

導(dǎo)致索引失效的常見問題包括：

• 在 WHERE 子句中對索引字段進(jìn)行表達(dá)式運(yùn)算或者使用函數(shù)都會(huì)導(dǎo)致索引失效，這種情況還包括字段的數(shù)據(jù)類型不匹配，例如字符串和整數(shù)進(jìn)行比較。
• 使用 LIKE 匹配時(shí)，如果通配符出現(xiàn)在左側(cè)無法使用索引。對于大型文本數(shù)據(jù)的模糊匹配，應(yīng)該考慮數(shù)據(jù)庫提供的全文檢索功能，甚至專門的全文搜索引擎(Elasticsearch 等)。
• 如果 WHERE 條件中的字段上創(chuàng)建了索引，盡量設(shè)置為 NOT NULL;不是所有數(shù)據(jù)庫使用 IS [NOT] NULL 判斷時(shí)都可以利用索引。

執(zhí)行計(jì)劃(execution plan，也叫查詢計(jì)劃或者解釋計(jì)劃)是數(shù)據(jù)庫執(zhí)行 SQL 語句的具體步驟，例如通過索引還是全表掃描訪問表中的數(shù)據(jù)，連接查詢的實(shí)現(xiàn)方式和連接的順序等。

如果 SQL 語句性能不夠理想，我們首先應(yīng)該查看它的執(zhí)行計(jì)劃，通過執(zhí)行計(jì)劃(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) 

從執(zhí)行計(jì)劃可以看出，MySQL 中采用的是類似 Nested Loop Join 實(shí)現(xiàn)方式;子查詢循環(huán)了 25 次，而實(shí)際上可以通過一次掃描計(jì)算并緩存每個(gè)部門的平均月薪。

以下語句將該子查詢替換為等價(jià)的 JOIN 語句，實(shí)現(xiàn)了子查詢的展開(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)技術(shù)，將子查詢的結(jié)果生成一個(gè)內(nèi)存臨時(shí)表;然后與 employee 表進(jìn)行連接。通過實(shí)際執(zhí)行時(shí)間可以看出這種方式更快。

以上示例在 Oracle 和 SQL Server 中會(huì)自動(dòng)執(zhí)行子查詢展開，兩種寫法效果相同;在 PostgreSQL 中與 MySQL 類似，第一個(gè)語句使用 Nested Loop Join，改寫為 JOIN 之后使用 Hash Join 實(shí)現(xiàn)，性能更好。

另外，對于 IN 和 EXISTS 子查詢也可以得出類似的結(jié)論。由于不同數(shù)據(jù)庫的優(yōu)化器能力有所差異，我們應(yīng)該盡量避免使用子查詢，考慮使用 JOIN 進(jìn)行重寫。

法則四：不要使用 OFFSET 實(shí)現(xiàn)分頁

分頁查詢的原理就是先跳過指定的行數(shù)，再返回 Top-N 記錄。分頁查詢的示意圖如下：

 

數(shù)據(jù)庫一般支持 FETCH/LIMIT 以及 OFFSET 實(shí)現(xiàn) Top-N 排行榜和分頁查詢。當(dāng)表中的數(shù)據(jù)量很大時(shí)，這種方式的分頁查詢可能會(huì)導(dǎo)致性能問題。

以 MySQL 為例：

-- MySQL 
SELECT * 
  FROM large_table 
 ORDER BY id 
 LIMIT 10 OFFSET N; 

以上查詢隨著 OFFSET 的增加，速度會(huì)越來越慢;因?yàn)榧词刮覀冎恍枰祷?10 條記錄，數(shù)據(jù)庫仍然需要訪問并且過濾掉 N(比如 1000000)行記錄，即使通過索引也會(huì)涉及不必要的掃描操作。

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

-- MySQL 
SELECT * 
  FROM large_table 
 WHERE id > last_id 
 ORDER BY id 
 LIMIT 10; 

如果 id 字段上存在索引，這種分頁查詢的方式可以基本不受數(shù)據(jù)量的影響。

法則五：了解 SQL 子句的邏輯執(zhí)行順序

以下是 SQL 中各個(gè)子句的語法順序，前面括號(hào)內(nèi)的數(shù)字代表了它們的邏輯執(zhí)行順序：

(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 并不是按照編寫順序先執(zhí)行 SELECT，然后再執(zhí)行 FROM 子句。

從邏輯上講，SQL 語句的執(zhí)行順序如下：

• 首先，F(xiàn)ROM 和 JOIN 是 SQL 語句執(zhí)行的第一步。它們的邏輯結(jié)果是一個(gè)笛卡爾積，決定了接下來要操作的數(shù)據(jù)集。

注意邏輯執(zhí)行順序并不代表物理執(zhí)行順序，實(shí)際上數(shù)據(jù)庫在獲取表中的數(shù)據(jù)之前會(huì)使用 ON 和 WHERE 過濾條件進(jìn)行優(yōu)化訪問。

• 其次，應(yīng)用 ON 條件對上一步的結(jié)果進(jìn)行過濾并生成新的數(shù)據(jù)集。
• 然后，執(zhí)行 WHERE 子句對上一步的數(shù)據(jù)集再次進(jìn)行過濾。WHERE 和 ON 大多數(shù)情況下的效果相同，但是外連接查詢有所區(qū)別，我們將會(huì)在下文給出示例。
• 接著，基于 GROUP BY 子句指定的表達(dá)式進(jìn)行分組;同時(shí)，對于每個(gè)分組計(jì)算聚合函數(shù) agg_func 的結(jié)果。經(jīng)過 GROUP BY 處理之后，數(shù)據(jù)集的結(jié)構(gòu)就發(fā)生了變化，只保留了分組字段和聚合函數(shù)的結(jié)果。
• 如果存在 GROUP BY 子句，可以利用 HAVING 針對分組后的結(jié)果進(jìn)一步進(jìn)行過濾，通常是針對聚合函數(shù)的結(jié)果進(jìn)行過濾。
• 接下來，SELECT 可以指定要返回的列;如果指定了 DISTINCT 關(guān)鍵字，需要對結(jié)果集進(jìn)行去重操作。另外還會(huì)為指定了 AS 的字段生成別名。
• 如果還有集合操作符(UNION、INTERSECT、EXCEPT)和其他的 SELECT 語句，執(zhí)行該查詢并且合并兩個(gè)結(jié)果集。對于集合操作中的多個(gè) SELECT 語句，數(shù)據(jù)庫通?？梢灾С植l(fā)執(zhí)行。
• 然后，應(yīng)用 ORDER BY 子句對結(jié)果進(jìn)行排序。如果存在 GROUP BY 子句或者 DISTINCT 關(guān)鍵字，只能使用分組字段和聚合函數(shù)進(jìn)行排序;否則，可以使用 FROM 和 JOIN 表中的任何字段排序。
• 最后，OFFSET 和 FETCH(LIMIT、TOP)限定了最終返回的行數(shù)。

了解 SQL 邏輯執(zhí)行順序可以幫助我們進(jìn)行 SQL 優(yōu)化。例如 WHERE 子句在 HAVING 子句之前執(zhí)行，因此我們應(yīng)該盡量使用 WHERE 進(jìn)行數(shù)據(jù)過濾，避免無謂的操作;除非業(yè)務(wù)需要針對聚合函數(shù)的結(jié)果進(jìn)行過濾。

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

-- 錯(cuò)誤示例 
SELECT emp_name AS empname 
  FROM employee 
 WHERE empname ='張飛'; 

該語句的錯(cuò)誤在于 WHERE 條件中引用了列別名;從上面的邏輯順序可以看出，執(zhí)行 WHERE 條件時(shí)還沒有執(zhí)行 SELECT 子句，也就沒有生成字段的別名。

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

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

由于經(jīng)過 GROUP BY 處理之后結(jié)果集只保留了分組字段和聚合函數(shù)的結(jié)果，示例中的 emp_name 字段已經(jīng)不存在。

從業(yè)務(wù)邏輯上來說，按照部門分組統(tǒng)計(jì)之后再顯示某個(gè)員工的姓名沒有意義。如果需要同時(shí)顯示員工信息和所在部門的匯總，可以使用窗口函數(shù)。

如果使用了 GROUP BY 分組，之后的 SELECT、ORDER BY 等只能引用分組字段或者聚合函數(shù);否則，可以引用 FROM 和 JOIN 表中的任何字段。

還有一些邏輯問題可能不會(huì)直接導(dǎo)致查詢出錯(cuò)，但是會(huì)返回不正確的結(jié)果;例如外連接查詢中的 ON 和 WHERE 條件。

以下是一個(gè)左外連接查詢的示例：

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]| 
關(guān)羽 | [NULL]| 
張飛 |行政管理部| 
諸葛亮 | [NULL]| 
... 

第一個(gè)查詢在 ON 子句中指定了連接的條件，同時(shí)通過 WHERE 子句找出了“張飛”的信息。

第二個(gè)查詢將所有的過濾條件都放在 ON 子句中，結(jié)果返回了所有的員工信息。

這是因?yàn)樽笸膺B接會(huì)返回左表中的全部數(shù)據(jù)，即使 ON 子句中指定了員工姓名也不會(huì)生效;而 WHERE 條件在邏輯上是對連接操作之后的結(jié)果進(jìn)行過濾。

總結(jié)

SQL 優(yōu)化本質(zhì)上是了解優(yōu)化器的的工作原理，并且為此創(chuàng)建合適的索引和正確的語句;同時(shí)，當(dāng)優(yōu)化器不夠智能的時(shí)候，手動(dòng)讓它智能。

作者：不剪發(fā)的Tony老師

編輯：陶家龍

出處：http://002ii.cn/YknfGU