SQL 優(yōu)化極簡(jiǎn)法則,還有誰(shuí)不會(huì)?

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué),十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn),目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專(zhuān)欄作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net


文章目錄

    法則一:只返回需要的結(jié)果
    法則二:確保查詢(xún)使用了正確的索引
    法則三:盡量避免使用子查詢(xún)
    法則四:不要使用 OFFSET 實(shí)現(xiàn)分頁(yè)
    法則五:了解 SQL 子句的邏輯執(zhí)行順序
    總結(jié)

大家好!我是只談技術(shù)不剪發(fā)的 Tony 老師。SQL 作為關(guān)系型數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言,是 IT 從業(yè)人員必不可少的技能之一。SQL 本身并不難學(xué),編寫(xiě)查詢(xún)語(yǔ)句也很容易,但是想要編寫(xiě)出能夠高效運(yùn)行的查詢(xún)語(yǔ)句卻有一定的難度。

查詢(xún)優(yōu)化是一個(gè)復(fù)雜的工程,涉及從硬件到參數(shù)配置、不同數(shù)據(jù)庫(kù)的解析器、優(yōu)化器實(shí)現(xiàn)、SQL 語(yǔ)句的執(zhí)行順序、索引以及統(tǒng)計(jì)信息的采集等,甚至應(yīng)用程序和系統(tǒng)的整體架構(gòu)。本文介紹幾個(gè)關(guān)鍵法則,可以幫助我們編寫(xiě)高效的 SQL 查詢(xún);尤其是對(duì)于初學(xué)者而言,這些法則至少可以避免我們寫(xiě)出性能很差的查詢(xún)語(yǔ)句。

以下法則適用于各種關(guān)系型數(shù)據(jù)庫(kù),包括但不限于:MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等。如果覺(jué)得文章有用,歡迎評(píng)論??、點(diǎn)贊??、推薦??
法則一:只返回需要的結(jié)果

一定要為查詢(xún)語(yǔ)句指定 WHERE 條件,過(guò)濾掉不需要的數(shù)據(jù)行。通常來(lái)說(shuō),OLTP 系統(tǒng)每次只需要從大量數(shù)據(jù)中返回很少的幾條記錄;指定查詢(xún)條件可以幫助我們通過(guò)索引返回結(jié)果,而不是全表掃描。絕大多數(shù)情況下使用索引時(shí)的性能更好,因?yàn)樗饕˙-樹(shù)、B+樹(shù)、B*樹(shù))執(zhí)行的是二進(jìn)制搜索,具有對(duì)數(shù)時(shí)間復(fù)雜度,而不是線(xiàn)性時(shí)間復(fù)雜度。以下是 MySQL 聚簇索引的示意圖:

在這里插入圖片描述

舉例來(lái)說(shuō),假設(shè)每個(gè)索引分支節(jié)點(diǎn)可以存儲(chǔ) 100 個(gè)記錄,100 萬(wàn)(1003)條記錄只需要 3 層 B-樹(shù)即可完成索引。通過(guò)索引查找數(shù)據(jù)時(shí)需要讀取 3 次索引數(shù)據(jù)(每次磁盤(pán) IO 讀取整個(gè)分支節(jié)點(diǎn)),加上 1 次磁盤(pán) IO 讀取數(shù)據(jù)即可得到查詢(xún)結(jié)果。

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

同理,我們應(yīng)該避免使用 SELECT * FROM, 因?yàn)樗硎静樵?xún)表中的所有字段。這種寫(xiě)法通常導(dǎo)致數(shù)據(jù)庫(kù)需要讀取更多的數(shù)據(jù),同時(shí)網(wǎng)絡(luò)也需要傳輸更多的數(shù)據(jù),從而導(dǎo)致性能的下降。

??關(guān)于B-樹(shù)索引的原理以及利用索引優(yōu)化各種查詢(xún)條件、連接查詢(xún)、排序和分組以及 DML 語(yǔ)句的介紹,可以參考這篇文章。

法則二:確保查詢(xún)使用了正確的索引

如果缺少合適的索引,即使指定了查詢(xún)條件也不會(huì)通過(guò)索引查找數(shù)據(jù)。因此,我們首先需要確保創(chuàng)建了相應(yīng)的索引。一般來(lái)說(shuō),以下字段

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

即使創(chuàng)建了合適的索引,如果 SQL 語(yǔ)句寫(xiě)的有問(wèn)題,數(shù)據(jù)庫(kù)也不會(huì)使用索引。導(dǎo)致索引失效的常見(jiàn)問(wèn)題包括:

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

執(zhí)行計(jì)劃(execution plan,也叫查詢(xún)計(jì)劃或者解釋計(jì)劃)是數(shù)據(jù)庫(kù)執(zhí)行 SQL 語(yǔ)句的具體步驟,例如通過(guò)索引還是全表掃描訪(fǎng)問(wèn)表中的數(shù)據(jù),連接查詢(xún)的實(shí)現(xiàn)方式和連接的順序等。如果 SQL 語(yǔ)句性能不夠理想,我們首先應(yīng)該查看它的執(zhí)行計(jì)劃,通過(guò)執(zhí)行計(jì)劃(EXPLAIN)確保查詢(xún)使用了正確的索引。

??關(guān)于各種主流數(shù)據(jù)庫(kù)中執(zhí)行計(jì)劃的查看和解釋?zhuān)梢詤⒖歼@篇文章和這篇文章。

法則三:盡量避免使用子查詢(xún)

以 MySQL 為例,以下查詢(xún)返回月薪大于部門(mén)平均月薪的員工信息:

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 中采用的是類(lèi)似 Nested Loop Join 實(shí)現(xiàn)方式;子查詢(xún)循環(huán)了 25 次,而實(shí)際上可以通過(guò)一次掃描計(jì)算并緩存每個(gè)部門(mén)的平均月薪。以下語(yǔ)句將該子查詢(xún)替換為等價(jià)的 JOIN 語(yǔ)句,實(shí)現(xiàn)了子查詢(xún)的展開(kāi)(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)

改寫(xiě)之后的查詢(xún)利用了物化(Materialization)技術(shù),將子查詢(xún)的結(jié)果生成一個(gè)內(nèi)存臨時(shí)表;然后與 employee 表進(jìn)行連接。通過(guò)實(shí)際執(zhí)行時(shí)間可以看出這種方式更快。

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

另外,對(duì)于 IN 和 EXISTS 子查詢(xún)也可以得出類(lèi)似的結(jié)論。由于不同數(shù)據(jù)庫(kù)的優(yōu)化器能力有所差異,我們應(yīng)該盡量避免使用子查詢(xún),考慮使用 JOIN 進(jìn)行重寫(xiě)。
法則四:不要使用 OFFSET 實(shí)現(xiàn)分頁(yè)

分頁(yè)查詢(xún)的原理就是先跳過(guò)指定的行數(shù),再返回 Top-N 記錄。分頁(yè)查詢(xún)的示意圖如下:

在這里插入圖片描述

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

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

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

對(duì)于以上分頁(yè)查詢(xún),更好的方法是記住上一次獲取到的最大 id,然后在下一次查詢(xún)中作為條件傳入:

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

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

??關(guān)于 Top-N 排行榜和分頁(yè)查詢(xún)的詳細(xì)介紹,可以參考這篇文章。

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

以下是 SQL 中各個(gè)子句的語(yǔ)法順序,前面括號(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;

也就是說(shuō),SQL 并不是按照編寫(xiě)順序先執(zhí)行 SELECT,然后再執(zhí)行 FROM 子句。從邏輯上講,SQL 語(yǔ)句的執(zhí)行順序如下:

首先,F(xiàn)ROM 和 JOIN 是 SQL 語(yǔ)句執(zhí)行的第一步。它們的邏輯結(jié)果是一個(gè)笛卡爾積,決定了接下來(lái)要操作的數(shù)據(jù)集。注意邏輯執(zhí)行順序并不代表物理執(zhí)行順序,實(shí)際上數(shù)據(jù)庫(kù)在獲取表中的數(shù)據(jù)之前會(huì)使用 ON 和 WHERE 過(guò)濾條件進(jìn)行優(yōu)化訪(fǎng)問(wèn);
其次,應(yīng)用 ON 條件對(duì)上一步的結(jié)果進(jìn)行過(guò)濾并生成新的數(shù)據(jù)集;
然后,執(zhí)行 WHERE 子句對(duì)上一步的數(shù)據(jù)集再次進(jìn)行過(guò)濾。WHERE 和 ON 大多數(shù)情況下的效果相同,但是外連接查詢(xún)有所區(qū)別,我們將會(huì)在下文給出示例;
接著,基于 GROUP BY 子句指定的表達(dá)式進(jìn)行分組;同時(shí),對(duì)于每個(gè)分組計(jì)算聚合函數(shù) agg_func 的結(jié)果。經(jīng)過(guò) GROUP BY 處理之后,數(shù)據(jù)集的結(jié)構(gòu)就發(fā)生了變化,只保留了分組字段和聚合函數(shù)的結(jié)果;
如果存在 GROUP BY 子句,可以利用 HAVING 針對(duì)分組后的結(jié)果進(jìn)一步進(jìn)行過(guò)濾,通常是針對(duì)聚合函數(shù)的結(jié)果進(jìn)行過(guò)濾;
接下來(lái),SELECT 可以指定要返回的列;如果指定了 DISTINCT 關(guān)鍵字,需要對(duì)結(jié)果集進(jìn)行去重操作。另外還會(huì)為指定了 AS 的字段生成別名;
如果還有集合操作符(UNION、INTERSECT、EXCEPT)和其他的 SELECT 語(yǔ)句,執(zhí)行該查詢(xún)并且合并兩個(gè)結(jié)果集。對(duì)于集合操作中的多個(gè) SELECT 語(yǔ)句,數(shù)據(jù)庫(kù)通??梢灾С植l(fā)執(zhí)行;
然后,應(yīng)用 ORDER BY 子句對(duì)結(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ù)過(guò)濾,避免無(wú)謂的操作;除非業(yè)務(wù)需要針對(duì)聚合函數(shù)的結(jié)果進(jìn)行過(guò)濾。

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

– 錯(cuò)誤示例
SELECT emp_name AS empname
FROM employee
WHERE empname =‘張飛’;

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

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

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

由于經(jīng)過(guò) GROUP BY 處理之后結(jié)果集只保留了分組字段和聚合函數(shù)的結(jié)果,示例中的 emp_name 字段已經(jīng)不存在;從業(yè)務(wù)邏輯上來(lái)說(shuō),按照部門(mén)分組統(tǒng)計(jì)之后再顯示某個(gè)員工的姓名沒(méi)有意義。如果需要同時(shí)顯示員工信息和所在部門(mén)的匯總,可以使用窗口函數(shù)。

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

還有一些邏輯問(wèn)題可能不會(huì)直接導(dǎo)致查詢(xún)出錯(cuò),但是會(huì)返回不正確的結(jié)果;例如外連接查詢(xún)中的 ON 和 WHERE 條件。以下是一個(gè)左外連接查詢(xún)的示例:

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

第一個(gè)查詢(xún)?cè)?ON 子句中指定了連接的條件,同時(shí)通過(guò) WHERE 子句找出了“張飛”的信息。

第二個(gè)查詢(xún)將所有的過(guò)濾條件都放在 ON 子句中,結(jié)果返回了所有的員工信息。這是因?yàn)樽笸膺B接會(huì)返回左表中的全部數(shù)據(jù),即使 ON 子句中指定了員工姓名也不會(huì)生效;而 WHERE 條件在邏輯上是對(duì)連接操作之后的結(jié)果進(jìn)行過(guò)濾。
總結(jié)

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