“Python 之父” 推薦的國內(nèi)唯一 Python 入門書《零基礎(chǔ)輕松學(xué) Python》作者。 每天分享 Python 干貨珍手，從數(shù)據(jù)分析办铡、人工智能等技術(shù)文章辞做、到 Python 工具資源、熱點資訊寡具、學(xué)習(xí)資料等凭豪，還有小白原創(chuàng)系列文章

文章目錄

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

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

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

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

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

總結(jié)

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

查詢優(yōu)化是一個復(fù)雜的工程，涉及從硬件到參數(shù)配置撰豺、不同數(shù)據(jù)庫的解析器粪般、優(yōu)化器實現(xiàn)、SQL 語句的執(zhí)行順序污桦、索引以及統(tǒng)計信息的采集等亩歹，甚至應(yīng)用程序和系統(tǒng)的整體架構(gòu)。本文介紹幾個關(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ù)情況下使用索引時的性能更好狱从，因為索引（B-樹蛀柴、B+樹、B*樹）執(zhí)行的是二進制搜索矫夯，具有對數(shù)時間復(fù)雜度鸽疾，而不是線性時間復(fù)雜度。以下是 MySQL 聚簇索引的示意圖：

Clustered index

舉例來說训貌，假設(shè)每個索引分支節(jié)點可以存儲 100 個記錄制肮，100 萬（1003）條記錄只需要 3 層 B-樹即可完成索引冒窍。通過索引查找數(shù)據(jù)時需要讀取 3 次索引數(shù)據(jù)（每次磁盤 IO 讀取整個分支節(jié)點），加上 1 次磁盤 IO 讀取數(shù)據(jù)即可得到查詢結(jié)果豺鼻。

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

同理附帽，我們應(yīng)該避免使用 SELECT * FROM， 因為它表示查詢表中的所有字段井誉。這種寫法通常導(dǎo)致數(shù)據(jù)庫需要讀取更多的數(shù)據(jù)蕉扮，同時網(wǎng)絡(luò)也需要傳輸更多的數(shù)據(jù)，從而導(dǎo)致性能的下降颗圣。

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

如果缺少合適的索引喳钟，即使指定了查詢條件也不會通過索引查找數(shù)據(jù)。因此在岂，我們首先需要確保創(chuàng)建了相應(yīng)的索引奔则。一般來說，以下字段需要創(chuàng)建索引：

經(jīng)常出現(xiàn)在 WHERE 條件中的字段建立索引可以避免全表掃描蔽午；

將 ORDER BY 排序的字段加入到索引中应狱，可以避免額外的排序操作；

多表連接查詢的關(guān)聯(lián)字段建立索引祠丝，可以提高連接查詢的性能疾呻；

將 GROUP BY 分組操作字段加入到索引中，可以利用索引完成分組写半。

即使創(chuàng)建了合適的索引岸蜗，如果 SQL 語句寫的有問題，數(shù)據(jù)庫也不會使用索引叠蝇。導(dǎo)致索引失效的常見問題包括：

在 WHERE 子句中對索引字段進行表達式運算或者使用函數(shù)都會導(dǎo)致索引失效璃岳，這種情況還包括字段的數(shù)據(jù)類型不匹配，例如字符串和整數(shù)進行比較悔捶；

使用 LIKE 匹配時铃慷，如果通配符出現(xiàn)在左側(cè)無法使用索引。對于大型文本數(shù)據(jù)的模糊匹配蜕该，應(yīng)該考慮數(shù)據(jù)庫提供的全文檢索功能犁柜，甚至專門的全文搜索引擎（Elasticsearch 等）；

如果 WHERE 條件中的字段上創(chuàng)建了索引堂淡，盡量設(shè)置為 NOT NULL馋缅；不是所有數(shù)據(jù)庫使用 IS [NOT] NULL 判斷時都可以利用索引扒腕。

執(zhí)行計劃（execution plan，也叫查詢計劃或者解釋計劃）是數(shù)據(jù)庫執(zhí)行 SQL 語句的具體步驟萤悴，例如通過索引還是全表掃描訪問表中的數(shù)據(jù)瘾腰，連接查詢的實現(xiàn)方式和連接的順序等。如果 SQL 語句性能不夠理想覆履，我們首先應(yīng)該查看它的執(zhí)行計劃蹋盆，通過執(zhí)行計劃（EXPLAIN）確保查詢使用了正確的索引。

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

以 MySQL 為例硝全，以下查詢返回月薪大于部門平均月薪的員工信息：

EXPLAINANALYZE

SELECTemp_id,?emp_name

FROMemployee?e

WHEREsalary?>?(

SELECTAVG(salary)

FROMemployee

WHEREdept_id?=?e.dept_id);

->?Filter:?(e.salary?>?(select#2))??(cost=2.75?rows=25)?(actual?time=0.232..4.401?rows=6?loops=1)

->Tablescanone??(cost=2.75rows=25)?(actualtime=0.099..0.190rows=25loops=1)

->Select#2?(subquery?in?condition;?dependent)

->Aggregate:avg(employee.salary)??(actualtime=0.147..0.149rows=1loops=25)

->Indexlookuponemployeeusingidx_emp_dept?(dept_id=e.dept_id)??(cost=1.12rows=5)?(actualtime=0.068..0.104rows=7loops=25)

從執(zhí)行計劃可以看出栖雾，MySQL 中采用的是類似 Nested Loop Join 實現(xiàn)方式；子查詢循環(huán)了 25 次柳沙，而實際上可以通過一次掃描計算并緩存每個部門的平均月薪。以下語句將該子查詢替換為等價的 JOIN 語句拌倍，實現(xiàn)了子查詢的展開（Subquery Unnest）：

EXPLAINANALYZE

SELECTe.emp_id,?e.emp_name

FROMemployee?e

JOIN(SELECTdept_id,AVG(salary)ASdept_average

FROMemployee

GROUPBYdept_id)?t

ONe.dept_id?=?t.dept_id

WHEREe.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é)果生成一個內(nèi)存臨時表；然后與 employee 表進行連接柱恤。通過實際執(zhí)行時間可以看出這種方式更快数初。

以上示例在 Oracle 和 SQL Server 中會自動執(zhí)行子查詢展開，兩種寫法效果相同梗顺；在 PostgreSQL 中與 MySQL 類似泡孩，第一個語句使用 Nested Loop Join，改寫為 JOIN 之后使用 Hash Join 實現(xiàn)寺谤，性能更好仑鸥。

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

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

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

分頁查詢

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

--?MySQL

SELECT*

FROMlarge_table

ORDERBYid

LIMIT10OFFSETN;

以上查詢隨著 OFFSET 的增加遮晚，速度會越來越慢性昭；因為即使我們只需要返回 10 條記錄，數(shù)據(jù)庫仍然需要訪問并且過濾掉 N（比如 1000000）行記錄县遣，即使通過索引也會涉及不必要的掃描操作巩梢。

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

--?MySQL

SELECT*

FROMlarge_table

WHEREid>?last_id

ORDERBYid

LIMIT10;

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

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

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

(6)SELECT[DISTINCT|ALL]?col1,?col2,?agg_func(col3)ASalias

(1)FROMt1JOINt2

(2)ON(join_conditions)

(3)WHEREwhere_conditions

(4)GROUPBYcol1,?col2

(5)HAVINGhaving_condition

(7)UNION[ALL]

...

(8)ORDERBYcol1ASC,col2DESC

(9)OFFSETmROWSFETCHNEXTnum_rowsROWSONLY;

也就是說忌警，SQL 并不是按照編寫順序先執(zhí)行 SELECT搁拙，然后再執(zhí)行 FROM 子句。從邏輯上講法绵，SQL 語句的執(zhí)行順序如下：

首先箕速，F(xiàn)ROM 和 JOIN 是 SQL 語句執(zhí)行的第一步。它們的邏輯結(jié)果是一個笛卡爾積朋譬，決定了接下來要操作的數(shù)據(jù)集盐茎。注意邏輯執(zhí)行順序并不代表物理執(zhí)行順序，實際上數(shù)據(jù)庫在獲取表中的數(shù)據(jù)之前會使用 ON 和 WHERE 過濾條件進行優(yōu)化訪問徙赢；

其次字柠，應(yīng)用 ON 條件對上一步的結(jié)果進行過濾并生成新的數(shù)據(jù)集；

然后狡赐，執(zhí)行 WHERE 子句對上一步的數(shù)據(jù)集再次進行過濾窑业。WHERE 和 ON 大多數(shù)情況下的效果相同，但是外連接查詢有所區(qū)別枕屉，我們將會在下文給出示例常柄；

接著，基于 GROUP BY 子句指定的表達式進行分組搀擂；同時西潘，對于每個分組計算聚合函數(shù) agg_func 的結(jié)果。經(jīng)過 GROUP BY 處理之后哨颂，數(shù)據(jù)集的結(jié)構(gòu)就發(fā)生了變化秸架，只保留了分組字段和聚合函數(shù)的結(jié)果；

如果存在 GROUP BY 子句咆蒿，可以利用 HAVING 針對分組后的結(jié)果進一步進行過濾东抹，通常是針對聚合函數(shù)的結(jié)果進行過濾；

接下來沃测，SELECT 可以指定要返回的列缭黔；如果指定了 DISTINCT 關(guān)鍵字，需要對結(jié)果集進行去重操作蒂破。另外還會為指定了 AS 的字段生成別名馏谨；

如果還有集合操作符（UNION、INTERSECT附迷、EXCEPT）和其他的 SELECT 語句惧互，執(zhí)行該查詢并且合并兩個結(jié)果集哎媚。對于集合操作中的多個 SELECT 語句，數(shù)據(jù)庫通澈袄埽可以支持并發(fā)執(zhí)行拨与；

然后，應(yīng)用 ORDER BY 子句對結(jié)果進行排序艾猜。如果存在 GROUP BY 子句或者 DISTINCT 關(guān)鍵字买喧，只能使用分組字段和聚合函數(shù)進行排序；否則匆赃，可以使用 FROM 和 JOIN 表中的任何字段排序淤毛；

最后，OFFSET 和 FETCH（LIMIT算柳、TOP）限定了最終返回的行數(shù)低淡。

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

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

--?錯誤示例

SELECTemp_nameASempname

FROMemployee

WHEREempname?='張飛';

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

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

--?GROUP?BY?錯誤示例

SELECTdept_id,?emp_name,AVG(salary)

FROMemployee

GROUPBYdept_id;

由于經(jīng)過 GROUP BY 處理之后結(jié)果集只保留了分組字段和聚合函數(shù)的結(jié)果稠鼻，示例中的 emp_name 字段已經(jīng)不存在冈止；從業(yè)務(wù)邏輯上來說，按照部門分組統(tǒng)計之后再顯示某個員工的姓名沒有意義候齿。如果需要同時顯示員工信息和所在部門的匯總熙暴，可以使用窗口函數(shù)。

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

還有一些邏輯問題可能不會直接導(dǎo)致查詢出錯，但是會返回不正確的結(jié)果灭必；例如外連接查詢中的 ON 和 WHERE 條件狞谱。以下是一個左外連接查詢的示例：

SELECTe.emp_name,?d.dept_name

FROMemployee?e

LEFTJOINdepartment?dON(e.dept_id?=?d.dept_id)

WHEREe.emp_name?='張飛';

emp_name|dept_name|

--------|---------|

張飛?????|行政管理部|

SELECTe.emp_name,?d.dept_name

FROMemployee?e

LEFTJOINdepartment?dON(e.dept_id?=?d.dept_idANDe.emp_name?='張飛');

emp_name|dept_name|

--------|---------|

劉備?????|???[NULL]|

關(guān)羽?????|???[NULL]|

張飛?????|行政管理部|

諸葛亮???|???[NULL]|

...

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

第二個查詢將所有的過濾條件都放在 ON 子句中跟衅，結(jié)果返回了所有的員工信息孵睬。這是因為左外連接會返回左表中的全部數(shù)據(jù)，即使 ON 子句中指定了員工姓名也不會生效与斤；而 WHERE 條件在邏輯上是對連接操作之后的結(jié)果進行過濾肪康。

總結(jié)

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

以上雾狈。

來源：blog.csdn.net/horses/article/details/105695431