SQL 查詢並不是從 SELECT 開始的

削微寒發表於2021-07-15
SQL

原文地址：SQL queries don't start with SELECT

原文作者：Julia Evans（已授權）

譯者 & 校正：HelloGitHub-小熊熊 & 滷蛋

日常使用中寫 SQL 查詢命令都是以 SELECT 開始的（注意：本文僅探討 SELECT 查詢，不涵蓋 insert 或其他 SQL 命令）。

昨天我想到一個問題：可以用 WHERE、HAVING 或者其他方式來過濾視窗函式執行結果嗎？

經過一番探索，我得出的最終結論是否定的，因為視窗函式必須在 WHERE 和 GROUP BY 之後才能執行。但是，這也延伸到了一個更大的問題——SQL 查詢的執行順序是怎麼樣的呢？

你所不知道的 SQL 查詢執行順序

我專門查了一下文件 SQL 查詢執行順序如下：

如果不喜歡以上五彩斑斕的圖片形式，也可以看下面的文字：

  1. FROM/JOIN/ON
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT（視窗函式即在此步驟執行）
  6. ORDER BY
  7. LIMIT

上圖可以解答你的如下疑惑：

上圖是 SQL 查詢的語義說明。看懂這張圖，便能迅速判斷一個給定的 SQL 查詢將會返回什麼結果，也可以輕鬆解答如下疑問：

  • 可以對 GROUP BY 的結果進行 WHERE 篩選嗎？（不可以！因為 WHERE 在 GROUP BY 之前執行）
  • 可以對視窗函式的執行結果進行過濾嗎？（不可以！因為視窗函式在 SELECT 步驟執行，而這步是在 WHERE 和 GROUP BY 之後）
  • 可以對 GROUP BY 的結果再執行 ORDER BY 操作嗎? （可以！ORDER BY 基本上是最後一個步驟了，所以可以對任何操作的執行結果執行 ORDER BY)
  • LIMIT 執行在哪個步驟? （最後一步！）

雖然如此，但實際上資料庫引擎並非嚴格按照這個順序執行查詢，因為它們還會執行一系列的優化，以便提升查詢速度。

所以：

  • 當你想了解查詢語句的有效性，或是想搞明白為什麼會返回這樣一個查詢結果時，可以嘗試用該圖來解釋；

  • 但是，使用該圖是無法解釋查詢效能或索引相關問題的，它們會涉及到更多變數，因而也更為複雜。

一、最容易搞混的：列別名

比如：關聯姓和名，並對其進行分組。SQL 語法是允許這樣寫：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, count(*)
FROM table
GROUP BY full_name

上面的查詢看起來像是在 SELECT 之後執行 GROUP BY，但其實 GROUP BY是先執行的，因為 GROUP BY 引用了 SELECT 中的 alias。

資料庫引擎是可以將查詢重寫為：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, count(*)
FROM table
GROUP BY CONCAT(first_name, ' ', last_name)

接著，先執行 GROUP BY 中的語句，再進行 SELECT 操作，所以上面那麼寫是可行的。

此外，資料庫引擎肯定會執行一系列檢查，以確保在查詢開始執行之前，SELECT 和 GROUP BY 中的內容相匹配，因此在制定執行計劃之前，它必須將查詢語句當作一個整體來檢查。

二、查詢並非嚴格按照此順序執行（優化）

實際上，資料庫引擎並不是通過連線、過濾和分組來執行查詢，因為它實現了一系列優化來提升查詢速度，如重新排序（只要不影響最終返回結果）。

這裡列舉一個簡單的例子來說明查詢的執行順序是如何影響了查詢效能。

SELECT * FROM
owners LEFT JOIN cats ON owners.id = cats.owner
WHERE cats.name = 'mr darcy'

如果只需要查詢 3 個名為“mr darcy”的貓，那麼執行整個左連線並匹配這兩個表中的所有行是很慢的。相反，如果先對名為“mr darcy”的貓進行篩選再去執行連線，則要快得多。在這種情況下，先執行過濾不會改變查詢的結果！

實際上，資料庫引擎還實現了許多其他的優化，使得查詢語句以另外的順序來執行，這裡暫且不表。

三、不一樣的查詢語法

LINQ（C# 和 VB.NET 中的查詢語法）是按照FROM ... WHERE ... SELECT的順序來執行查詢。以下是 LINQ 查詢的示例：

var teenAgerStudent = from s in studentList
                      where s.Age > 12 && s.Age < 20
                      select s;

Pandas（Python 資料統計分析工具）也基本上是這樣工作的，儘管有時候不需要嚴格按照下面的順序來編寫程式碼，但這樣也不失為一種好習慣：

df = thing1.join(thing2)      # like a JOIN
df = df[df.created_at > 1000] # like a WHERE
df = df.groupby('something', num_yes = ('yes', 'sum')) # like a GROUP BY
df = df[df.num_yes > 2]       # like a HAVING, filtering on the result of a GROUP BY
df = df[['num_yes', 'something1', 'something']] # pick the columns I want to display, like a SELECT
df.sort_values('sometthing', ascending=True)[:30] # ORDER BY and LIMIT
df[:30]

這並不是因為 Pandas 的強制規定，只是按照 JOIN/WHERE/GROUP BY/HAVING 的順序來編寫程式碼更有助於理解底層邏輯。(值得一提的是，可以在 JOIN 之前先執行 WHERE 來提高效能，大多數資料庫引擎在實踐中也是這樣來執行的）

R 中的 dplyr（R 語言用來運算元據框的包）還允許採用不同的語法查詢不同的 SQL 資料庫，如：Postgres、MySQL 和 SQLite。

最後

當我發現 SQL 查詢語句的這種執行順序時，我其實是非常驚訝的。通過探究 SQL 查詢語句的執行順序，把我之前遇到的問題明明白白地搞清楚了。也希望本文能幫助到更多的人理解 SQL 的執行順序以及如何正確編寫 SQL 查詢語句。

關注 HelloGitHub 公眾號 第一時間收到更新。

還有更多開源專案的介紹和寶藏專案等待你的發現。

相關文章