必會(huì)的 5 條 SQL 查詢優(yōu)化法則！

這篇文章，是對(duì)SQL常用查詢優(yōu)化法則的總結(jié)，值得細(xì)看

文章目錄

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

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

• 法則三：盡量避免使用子查詢

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

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

• 總結(jié)

SQL 作為關(guān)系型數(shù)據(jù)庫的標(biāo)準(zhǔn)語言，是分析師必不可少的技能之一。SQL 本身并不難學(xué)，編寫查詢語句也很容易，但是想要編寫出能夠高效運(yùn)行的查詢語句卻有一定的難度。

查詢優(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 查詢；尤其是對(duì)于初學(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*樹）執(zhí)行的是二進(jìn)制搜索，具有對(duì)數(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)樗硎静樵儽碇械乃凶侄巍＿@種寫法通常導(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 子句中對(duì)索引字段進(jìn)行表達(dá)式運(yùn)算或者使用函數(shù)都會(huì)導(dǎo)致索引失效，這種情況還包括字段的數(shù)據(jù)類型不匹配，例如字符串和整數(shù)進(jìn)行比較；

• 使用 LIKE 匹配時(shí)，如果通配符出現(xiàn)在左側(cè)無法使用索引。對(duì)于大型文本數(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??(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)，性能更好。

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

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

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

分頁查詢

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

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

(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;

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

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

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]|
...

SQL 常用腳本整理

MySQL 時(shí)間類型 datetime、bigint、timestamp，選哪個(gè)？

MySQL 大批量插入，如何過濾掉重復(fù)數(shù)據(jù)？

MySQL 主從復(fù)制解決了什么問題？出現(xiàn)同步延遲如何解決？

MySQL 定時(shí)備份數(shù)據(jù)庫（非常全）

干貨！常見的SQL面試題：經(jīng)典50例！

SQL：我能玩出這花樣兒？