分析師必會的 5 條 SQL 查詢優(yōu)化法則 ！
文章目錄
法則一：只返回需要的結(jié)果 法則二：確保查詢使用了正確的索引 法則三：盡量避免使用子查詢 法則四：不要使用 OFFSET 實(shí)現(xiàn)分頁 法則五：了解 SQL 子句的邏輯執(zhí)行順序 總結(jié)
法則一：只返回需要的結(jié)果
Clustered index法則二：確保查詢使用了正確的索引
經(jīng)常出現(xiàn)在 WHERE 條件中的字段建立索引可以避免全表掃描； 將 ORDER BY 排序的字段加入到索引中，可以避免額外的排序操作； 多表連接查詢的關(guān)聯(lián)字段建立索引，可以提高連接查詢的性能； 將 GROUP BY 分組操作字段加入到索引中，可以利用索引完成分組。
在 WHERE 子句中對索引字段進(jìn)行表達(dá)式運(yùn)算或者使用函數(shù)都會導(dǎo)致索引失效，這種情況還包括字段的數(shù)據(jù)類型不匹配，例如字符串和整數(shù)進(jìn)行比較； 使用 LIKE 匹配時，如果通配符出現(xiàn)在左側(cè)無法使用索引。對于大型文本數(shù)據(jù)的模糊匹配，應(yīng)該考慮數(shù)據(jù)庫提供的全文檢索功能，甚至專門的全文搜索引擎（Elasticsearch 等）； 如果 WHERE 條件中的字段上創(chuàng)建了索引，盡量設(shè)置為 NOT NULL；不是所有數(shù)據(jù)庫使用 IS [NOT] NULL 判斷時都可以利用索引。
法則三：盡量避免使用子查詢
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)分頁
分頁查詢--?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;
首先，F(xiàn)ROM 和 JOIN 是 SQL 語句執(zhí)行的第一步。它們的邏輯結(jié)果是一個笛卡爾積，決定了接下來要操作的數(shù)據(jù)集。注意邏輯執(zhí)行順序并不代表物理執(zhí)行順序，實(shí)際上數(shù)據(jù)庫在獲取表中的數(shù)據(jù)之前會使用 ON 和 WHERE 過濾條件進(jìn)行優(yōu)化訪問； 其次，應(yīng)用 ON 條件對上一步的結(jié)果進(jìn)行過濾并生成新的數(shù)據(jù)集； 然后，執(zhí)行 WHERE 子句對上一步的數(shù)據(jù)集再次進(jìn)行過濾。WHERE 和 ON 大多數(shù)情況下的效果相同，但是外連接查詢有所區(qū)別，我們將會在下文給出示例； 接著，基于 GROUP BY 子句指定的表達(dá)式進(jìn)行分組；同時，對于每個分組計(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)行去重操作。另外還會為指定了 AS 的字段生成別名； 如果還有集合操作符（UNION、INTERSECT、EXCEPT）和其他的 SELECT 語句，執(zhí)行該查詢并且合并兩個結(jié)果集。對于集合操作中的多個 SELECT 語句，數(shù)據(jù)庫通常可以支持并發(fā)執(zhí)行； 然后，應(yīng)用 ORDER BY 子句對結(jié)果進(jìn)行排序。如果存在 GROUP BY 子句或者 DISTINCT 關(guān)鍵字，只能使用分組字段和聚合函數(shù)進(jìn)行排序；否則，可以使用 FROM 和 JOIN 表中的任何字段排序； 最后，OFFSET 和 FETCH（LIMIT、TOP）限定了最終返回的行數(shù)。
--?錯誤示例
SELECT?emp_name?AS?empname
??FROM?employee
?WHERE?empname?='張飛';
--?GROUP?BY?錯誤示例
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é)
轉(zhuǎn)自：SQL數(shù)據(jù)庫開發(fā) 點(diǎn)分享 點(diǎn)收藏 點(diǎn)點(diǎn)贊 點(diǎn)在看
評論
圖片
表情