sql教程 作為關系型數(shù)據(jù)庫的標準語言,是it從業(yè)人員必不可少的技能之一。sql 本身并不難學,編寫查詢語句也很容易,但是想要編寫出能夠高效運行的查詢語句卻有一定的難度。
推薦(免費):sql教程
查詢優(yōu)化是一個復雜的工程,涉及從硬件到參數(shù)配置、不同數(shù)據(jù)庫的解析器、優(yōu)化器實現(xiàn)、SQL 語句的執(zhí)行順序、索引以及統(tǒng)計信息的采集等,甚至應用程序和系統(tǒng)的整體架構。本文介紹幾個關鍵法則,可以幫助我們編寫高效的 SQL 查詢;尤其是對于初學者而言,這些法則至少可以避免我們寫出性能很差的查詢語句。
以下法則適用于各種關系型數(shù)據(jù)庫,包括但不限于:mysql、oracle、SQL Server、postgresql 以及 sqlite 等。如果覺得文章有用,歡迎評論、點贊、轉發(fā)朋友圈支持。
法則一:只返回需要的結果
一定要為查詢語句指定 WHERE 條件,過濾掉不需要的數(shù)據(jù)行。通常來說,OLTP 系統(tǒng)每次只需要從大量數(shù)據(jù)中返回很少的幾條記錄;指定查詢條件可以幫助我們通過索引返回結果,而不是全表掃描。絕大多數(shù)情況下使用索引時的性能更好,因為索引(B-樹、B+樹、B*樹)執(zhí)行的是二進制搜索,具有對數(shù)時間復雜度,而不是線性時間復雜度。以下是 MySQL 聚簇索引的示意圖:舉例來說,假設每個索引分支節(jié)點可以存儲 100 個記錄,100 萬(1003)條記錄只需要 3 層 B-樹即可完成索引。通過索引查找數(shù)據(jù)時需要讀取 3 次索引數(shù)據(jù)(每次磁盤 IO 讀取整個分支節(jié)點),加上 1 次磁盤 IO 讀取數(shù)據(jù)即可得到查詢結果。純干貨!15000 字語法手冊分享給你
相反,如果采用全表掃描,需要執(zhí)行的磁盤 IO 次數(shù)可能高出幾個數(shù)量級。當數(shù)據(jù)量增加到 1 億(1004)時,B-樹索引只需要再增加 1 次索引 IO 即可;而全表掃描則需要再增加幾個數(shù)量級的 IO。
同理,我們應該避免使用 select * FROM, 因為它表示查詢表中的所有字段。這種寫法通常導致數(shù)據(jù)庫需要讀取更多的數(shù)據(jù),同時網(wǎng)絡也需要傳輸更多的數(shù)據(jù),從而導致性能的下降。
法則二:確保查詢使用了正確的索引
如果缺少合適的索引,即使指定了查詢條件也不會通過索引查找數(shù)據(jù)。因此,我們首先需要確保創(chuàng)建了相應的索引。一般來說,以下字段需要創(chuàng)建索引:
- 經常出現(xiàn)在 WHERE 條件中的字段建立索引可以避免全表掃描;
- 將 ORDER BY 排序的字段加入到索引中,可以避免額外的排序操作;
- 多表連接查詢的關聯(lián)字段建立索引,可以提高連接查詢的性能;
- 將 GROUP BY 分組操作字段加入到索引中,可以利用索引完成分組。
即使創(chuàng)建了合適的索引,如果 SQL 語句寫的有問題,數(shù)據(jù)庫也不會使用索引。導致索引失效的常見問題包括:
- 在 WHERE 子句中對索引字段進行表達式運算或者使用函數(shù)都會導致索引失效,這種情況還包括字段的數(shù)據(jù)類型不匹配,例如字符串和整數(shù)進行比較;
- 使用 LIKE 匹配時,如果通配符出現(xiàn)在左側無法使用索引。對于大型文本數(shù)據(jù)的模糊匹配,應該考慮數(shù)據(jù)庫提供的全文檢索功能,甚至專門的全文搜索引擎(elasticsearch 等);
- 如果 WHERE 條件中的字段上創(chuàng)建了索引,盡量設置為 NOT NULL;不是所有數(shù)據(jù)庫使用 IS [NOT] NULL 判斷時都可以利用索引。
執(zhí)行計劃(execution plan,也叫查詢計劃或者解釋計劃)是數(shù)據(jù)庫執(zhí)行 SQL 語句的具體步驟,例如通過索引還是全表掃描訪問表中的數(shù)據(jù),連接查詢的實現(xiàn)方式和連接的順序等。如果 SQL 語句性能不夠理想,我們首先應該查看它的執(zhí)行計劃,通過執(zhí)行計劃(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í)行計劃可以看出,MySQL 中采用的是類似 Nested Loop Join 實現(xiàn)方式;子查詢循環(huán)了 25 次,而實際上可以通過一次掃描計算并緩存每個部門的平均月薪。以下語句將該子查詢替換為等價的 JOIN 語句,實現(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 <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)
改寫之后的查詢利用了物化(Materialization)技術,將子查詢的結果生成一個內存臨時表;然后與 employee 表進行連接。通過實際執(zhí)行時間可以看出這種方式更快。
以上示例在 Oracle 和 SQL Server 中會自動執(zhí)行子查詢展開,兩種寫法效果相同;在 PostgreSQL 中與 MySQL 類似,第一個語句使用 Nested Loop Join,改寫為 JOIN 之后使用 Hash Join 實現(xiàn),性能更好。
另外,對于 IN 和 EXISTS 子查詢也可以得出類似的結論。由于不同數(shù)據(jù)庫的優(yōu)化器能力有所差異,我們應該盡量避免使用子查詢,考慮使用 JOIN 進行重寫。搜索公眾號 民工哥技術之路,回復“1024”,送你一份技術資源大禮包。
法則四:不要使用 OFFSET 實現(xiàn)分頁
分頁查詢的原理就是先跳過指定的行數(shù),再返回 Top-N 記錄。分頁查詢的示意圖如下:數(shù)據(jù)庫一般支持 FETCH/LIMIT 以及 OFFSET 實現(xiàn) Top-N 排行榜和分頁查詢。當表中的數(shù)據(jù)量很大時,這種方式的分頁查詢可能會導致性能問題。以 MySQL 為例:
-- MySQL SELECT * FROM large_table ORDER BY id LIMIT 10 OFFSET N;
以上查詢隨著 OFFSET 的增加,速度會越來越慢;因為即使我們只需要返回 10 條記錄,數(shù)據(jù)庫仍然需要訪問并且過濾掉 N(比如 1000000)行記錄,即使通過索引也會涉及不必要的掃描操作。
對于以上分頁查詢,更好的方法是記住上一次獲取到的最大 id,然后在下一次查詢中作為條件傳入:
-- MySQL SELECT * FROM large_table WHERE id > last_id ORDER BY id LIMIT 10;
如果 id 字段上存在索引,這種分頁查詢的方式可以基本不受數(shù)據(jù)量的影響。
法則五:了解 SQL 子句的邏輯執(zhí)行順序
以下是 SQL 中各個子句的語法順序,前面括號內的數(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;
也就是說,SQL 并不是按照編寫順序先執(zhí)行 SELECT,然后再執(zhí)行 FROM 子句。從邏輯上講,SQL 語句的執(zhí)行順序如下:
- 首先,F(xiàn)ROM 和 JOIN 是 SQL 語句執(zhí)行的第一步。它們的邏輯結果是一個笛卡爾積,決定了接下來要操作的數(shù)據(jù)集。注意邏輯執(zhí)行順序并不代表物理執(zhí)行順序,實際上數(shù)據(jù)庫在獲取表中的數(shù)據(jù)之前會使用 ON 和 WHERE 過濾條件進行優(yōu)化訪問;
- 其次,應用 ON 條件對上一步的結果進行過濾并生成新的數(shù)據(jù)集;
- 然后,執(zhí)行 WHERE 子句對上一步的數(shù)據(jù)集再次進行過濾。WHERE 和 ON 大多數(shù)情況下的效果相同,但是外連接查詢有所區(qū)別,我們將會在下文給出示例;
- 接著,基于 GROUP BY 子句指定的表達式進行分組;同時,對于每個分組計算聚合函數(shù) agg_func 的結果。經過 GROUP BY 處理之后,數(shù)據(jù)集的結構就發(fā)生了變化,只保留了分組字段和聚合函數(shù)的結果;
- 如果存在 GROUP BY 子句,可以利用 HAVING 針對分組后的結果進一步進行過濾,通常是針對聚合函數(shù)的結果進行過濾;
- 接下來,SELECT 可以指定要返回的列;如果指定了 DISTINCT 關鍵字,需要對結果集進行去重操作。另外還會為指定了 AS 的字段生成別名;
- 如果還有集合操作符(UNION、INTERSECT、EXCEPT)和其他的 SELECT 語句,執(zhí)行該查詢并且合并兩個結果集。對于集合操作中的多個 SELECT 語句,數(shù)據(jù)庫通常可以支持并發(fā)執(zhí)行;
- 然后,應用 ORDER BY 子句對結果進行排序。如果存在 GROUP BY 子句或者 DISTINCT 關鍵字,只能使用分組字段和聚合函數(shù)進行排序;否則,可以使用 FROM 和 JOIN 表中的任何字段排序;
- 最后,OFFSET 和 FETCH(LIMIT、TOP)限定了最終返回的行數(shù)。
了解 SQL 邏輯執(zhí)行順序可以幫助我們進行 SQL 優(yōu)化。例如 WHERE 子句在 HAVING 子句之前執(zhí)行,因此我們應該盡量使用 WHERE 進行數(shù)據(jù)過濾,避免無謂的操作;除非業(yè)務需要針對聚合函數(shù)的結果進行過濾。
除此之外,理解SQL的邏輯執(zhí)行順序還可以幫助我們避免一些常見的錯誤,例如以下語句:
-- 錯誤示例 SELECT emp_name AS empname FROM employee WHERE empname ='張飛';
該語句的錯誤在于 WHERE 條件中引用了列別名;從上面的邏輯順序可以看出,執(zhí)行 WHERE 條件時還沒有執(zhí)行 SELECT 子句,也就沒有生成字段的別名。
另外一個需要注意的操作就是 GROUP BY,例如:
-- GROUP BY 錯誤示例 SELECT dept_id, emp_name, AVG(salary) FROM employee GROUP BY dept_id;
由于經過 GROUP BY 處理之后結果集只保留了分組字段和聚合函數(shù)的結果,示例中的 emp_name 字段已經不存在;從業(yè)務邏輯上來說,按照部門分組統(tǒng)計之后再顯示某個員工的姓名沒有意義。如果需要同時顯示員工信息和所在部門的匯總,可以使用窗口函數(shù)。擴展:SQL 語法速成手冊
還有一些邏輯問題可能不會直接導致查詢出錯,但是會返回不正確的結果;例如外連接查詢中的 ON 和 WHERE 條件。以下是一個左外連接查詢的示例:
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]| 關羽 | [NULL]| 張飛 |行政管理部| 諸葛亮 | [NULL]| ...
- 第一個查詢在 ON 子句中指定了連接的條件,同時通過 WHERE 子句找出了“張飛”的信息。
- 第二個查詢將所有的過濾條件都放在 ON 子句中,結果返回了所有的員工信息。這是因為左外連接會返回左表中的全部數(shù)據(jù),即使 ON 子句中指定了員工姓名也不會生效;而 WHERE 條件在邏輯上是對連接操作之后的結果進行過濾。
總結
SQL 優(yōu)化本質上是了解優(yōu)化器的的工作原理,并且為此創(chuàng)建合適的索引和正確的語句;同時,當優(yōu)化器不夠智能的時候,手動讓它智能。