技術文章 » SQL 查詢的執行順序

SQL 查詢的執行順序

2023-09-04 12:00:42

by emanjusaka from ​ https://www.emanjusaka.top/archives/6 彼岸花開可奈何
本文歡迎分享與聚合，全文轉載請留下原文地址。

前言

瞭解 SQL 查詢的執行順序對我們解決一些問題很有幫助，有時我們可能會疑惑為什麼不能對分組的結果進行篩選這樣類似的問題？之前一直不是理解這個問題，在瞭解了SQL 查詢的執行順序之後這個問題也就迎刃而解。在我們對 SQL 查詢語句進行分析優化時，掌握執行順序也是有一定幫助的。

一、理論順序

上面是圖示 SQL 的執行順序，下面用列表列出：

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. CUBE | ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. LIMIT

上面所列出的執行順序能幫助我們解答一些問題：

  • 為啥不能對視窗函數的執行結果進行過濾？

    因為視窗函數在 SELECT 步驟執行，而這步是在 WHERE 和 GROUP BY 之後

  • 可以對分組的結果進行篩選嗎？

    不可以，因為 GROUP BY 在 WHERE 之後執行

  • 可以對分組後的結果進行排序嗎？

    可以，因為 ORDER BY 在 GROUP BY 之後。

二、程式碼範例

  • 學生表

  • 成績表

  • 查詢語句

    查詢來自天津且總成績高於70分，並且查詢他們的總成績，查詢結果按成績降序排列

    SELECT
	ss.student_id,sum(se.grade) as total,ss.city
FROM
	students ss
	LEFT JOIN score se ON ss.student_id = se.student_id 
WHERE
	ss.city = "天津"
GROUP BY ss.student_id
HAVING sum(se.grade) > 70
ORDER BY total DESC
LIMIT 10

  • 查詢結果

三、分析 SQL 執行過程

SQL 執行的每個操作都會產生一張虛擬表，只不過這些虛擬表對使用者是透明的，只有最後一步生成的虛擬表才會返回給使用者。

  1. 第一步執行的是對 FROM 字句前後的兩張表 students 和 score 進行笛卡爾積操作，生成虛擬表VT1。

  2. 應用 ON 過濾器

    在虛擬表 VT1 中執行過濾操作，過濾條件為：ss.student_id = se.student_id ​

    對於在 ON 過濾條件下的 NULL 值比較，此時的比較結果為 UNKNOWN，卻被視為 FALSE 來進行處理，即兩個 NULL 並不相同。但是在下面兩種情況下認為兩個 NULL 值的比較是相等的：

    • GROUP BY 子句把所有 NULL 值分到同一組
    • ORDER BY 子句中把所有 NULL 值排列在一起

    在產生虛擬表 VT2 時，會增加一個額外的列來表示 ON 過濾條件的返回值，返回值有 TRUE、FALSE、UNKNOWN。取出比較值為 TRUE 的記錄，產生虛擬表 VT2。

  3. 新增外部行

    這一步只有在連線型別為 OUTER JOIN 時才發生，如 LEFT OUTER JOIN、RIGHT OUTERJOIN、FULL OUTER JOIN。雖然在大多數時候我們可以省略 OUTER 關鍵字，但 OUTER 代表的就是外部行。LEFT OUTER JOIN 把左表記為保留表，RIGHT OUTER JOIN 把右表記為保留表，FULL OUTER JOIN 把左右表都記為保留表。新增外部行的工作就是在 VT2 表的基礎上新增保留表中被過濾條件過濾掉的資料，非保留表中的資料被賦予 NULL 值，最後生成虛擬表 VT3

  4. 應用 WHERE 過濾器

    對上一步驟產生的虛擬表 VT3 進行 WHERE 條件過濾，只有符合<where_condition>的記錄才會輸出到虛擬表 VT4 中

    在當前應用 WHERE 過濾器時，有兩種過濾是不被允許的：

    • 由於資料還沒有分組，因此現在還不能在 WHERE 過濾器中使用 where_condition=MIN(col)這類對統計的過濾
    • 由於沒有進行列的選取操作，因此在 SELECT 中使用列的別名也是不被允許的，如 SELECT city as c from students WHERE c = '天津' 是不允許出現的

  5. 分組

    在本步驟中根據指定的列對上個步驟中產生的虛擬表進行分組，最後得到虛擬表 VT5

  6. 應用 HAVING 過濾器

    在該步驟中對於上一步產生的虛擬表應用 HAVING 過濾器，HAVING 是對分組條件進行過濾的篩選器。生成虛擬表VT6。

  7. 處理 SELECT 列表

    在這一步中，將 SELECT 中指定的列從上一步產生的虛擬表中選出生成虛擬表 VT7。

  8. 應用 ORDER BY 字句

    根據 ORDER BY 子句中指定的列對上一步輸出的虛擬表進行排列，返回新的虛擬表 VT8。

    注意：在 MySQL 資料庫中，NULL 值在升序過程中總是首先被選出，即 NULL 值在 ORDER BY 子句中被視為最小值

  9. LIMIT 子句

    在該步驟中應用 LIMIT 子句，從上一步驟的虛擬表中選出從指定位置開始的指定行資料。對於沒有應用 ORDER BY 的 LIMIT 子句，結果同樣可能是無序的，因此 LIMIT 子句通常和 ORDER BY 子句一起使用。

四、注意

上面所討論的順序皆為理論上的執行順序，實際上資料庫引擎並不是通過連線、過濾和分組來執行查詢，因為它實現了一系列優化來提升查詢速度（不影響最終的返回結果）。資料庫引擎可能會為了提高查詢的速度把一些過濾條件進行提前，當然前提是不會對返回的結果造成影響。

SELECT
	ss.student_id,sum(se.grade) as total,ss.city
FROM
	students ss
	LEFT JOIN score se ON ss.student_id = se.student_id 
WHERE
	ss.city = "天津"

這個 sql 學生城市是天津的只有三個，如果在學生很多的情況下如果先做城市的篩選後再對兩張表做笛卡爾積可以很大程度的提升效能，並且也不會對返回的結果造成影響。這時實際上SQL的執行順序可能就與理論上的執行順序不一樣了。

參考資料

  1. SQL queries don't start with SELECT

本文原創，才疏學淺，如有紕漏，歡迎指正。尊貴的朋友，如果本文對您有所幫助，歡迎點贊，並期待您的反饋，以便於不斷優化。

原文地址： https://www.emanjusaka.top/archives/6

微信公眾號：emanjusaka的程式設計棧