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

文章目錄

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

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

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

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

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

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

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)

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)

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

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

法則五：了解 SQL 子句的邏輯執(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;

1. 首先，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)化訪問(wèn)；
2. 其次，應(yīng)用 ON 條件對(duì)上一步的結(jié)果進(jìn)行過(guò)濾并生成新的數(shù)據(jù)集；
3. 然后，執(zhí)行 WHERE 子句對(duì)上一步的數(shù)據(jù)集再次進(jìn)行過(guò)濾。WHERE 和 ON 大多數(shù)情況下的效果相同，但是外連接查詢有所區(qū)別，我們將會(huì)在下文給出示例；
4. 接著，基于 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é)果；
5. 如果存在 GROUP BY 子句，可以利用 HAVING 針對(duì)分組后的結(jié)果進(jìn)一步進(jìn)行過(guò)濾，通常是針對(duì)聚合函數(shù)的結(jié)果進(jìn)行過(guò)濾；
6. 接下來(lái)，SELECT 可以指定要返回的列；如果指定了 DISTINCT 關(guān)鍵字，需要對(duì)結(jié)果集進(jìn)行去重操作。另外還會(huì)為指定了 AS 的字段生成別名；
7. 如果還有集合操作符（UNION、INTERSECT、EXCEPT）和其他的 SELECT 語(yǔ)句，執(zhí)行該查詢并且合并兩個(gè)結(jié)果集。對(duì)于集合操作中的多個(gè) SELECT 語(yǔ)句，數(shù)據(jù)庫(kù)通?？梢灾С植l(fā)執(zhí)行；
8. 然后，應(yīng)用 ORDER BY 子句對(duì)結(jié)果進(jìn)行排序。如果存在 GROUP BY 子句或者 DISTINCT 關(guān)鍵字，只能使用分組字段和聚合函數(shù)進(jìn)行排序；否則，可以使用 FROM 和 JOIN 表中的任何字段排序；
9. 最后，OFFSET 和 FETCH（LIMIT、TOP）限定了最終返回的行數(shù)。

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

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

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

總結(jié)

——End——
后臺(tái)回復(fù)關(guān)鍵字：1024，獲取一份精心整理的技術(shù)干貨
后臺(tái)回復(fù)關(guān)鍵字：進(jìn)群，帶你進(jìn)入高手如云的交流群。
推薦閱讀
國(guó)產(chǎn)數(shù)據(jù)庫(kù)建模工具，看到界面第一眼，良心了！
快手公司廁所裝坑位計(jì)時(shí)器，網(wǎng)友：再也不能帶薪拉屎了！
如何優(yōu)雅地給妹子優(yōu)化電腦？
最全MySQL面試題集合
有了這 4 款腳本工具，老板再也不怕我寫(xiě)爛SQL了


這是一個(gè)能學(xué)到技術(shù)的公眾號(hào)，歡迎關(guān)注
點(diǎn)擊「閱讀原文」了解SQL訓(xùn)練營(yíng)