原文地址:SQL queries don‘t start with SELECT
原文作者:Julia Evans(已授權)
譯者 & 校正:HelloGitHub-小熊熊 & 滷蛋
日常使用中寫 SQL 查詢命令都是以 SELECT 開始的(注意:本文僅探討 SELECT 查詢,不涵蓋 insert 或其他 SQL 命令)。
昨天我想到一個問題:可以用 WHERE、HAVING 或者其他方式來過濾視窗函式執行結果嗎?
經過一番探索,我得出的最終結論是否定的,因為視窗函式必須在 WHERE 和 GROUP BY 之後才能執行。但是,這也延伸到了一個更大的問題——SQL 查詢的執行順序是怎麼樣的呢?
SQL 查詢執行順序
我專門查了一下文件 SQL 查詢執行順序如下:
如果不喜歡以上五彩斑斕的圖片形式,也可以看下面的文字:
FROM/JOIN/ON
WHERE
GROUP BY
HAVING
SELECT
(視窗函式即在此步驟執行)
ORDER BY
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 tableGROUP BY full_name
上面的查詢看起來像是在
SELECT
之後執行
GROUP BY
,但其實
GROUP BY
是先執行的,因為
GROUP BY
引用了
SELECT
中的
alias
。
資料庫引擎是可以將查詢重寫為:
SELECT CONCAT(first_name, ’ ‘, last_name) AS full_name, count(*)FROM tableGROUP BY CONCAT(first_name, ’ ‘, last_name)
接著,先執行
GROUP BY
中的語句,再進行
SELECT
操作,所以上面那麼寫是可行的。
此外,資料庫引擎肯定會執行一系列檢查,以確保在查詢開始執行之前,
SELECT
和
GROUP BY
中的內容相匹配,因此在制定執行計劃之前,它必須將查詢語句當作一個整體來檢查。
二、查詢並非嚴格按照此順序執行(最佳化)
實際上,資料庫引擎並不是透過連線、過濾和分組來執行查詢,因為它實現了一系列最佳化來提升查詢速度,如重新排序(只要不影響最終返回結果)。
這裡列舉一個簡單的例子來說明查詢的執行順序是如何影響了查詢效能。
SELECT * FROMowners LEFT JOIN cats ON owners。id = cats。ownerWHERE 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 JOINdf = df[df。created_at > 1000] # like a WHEREdf = df。groupby(’something‘, num_yes = (’yes‘, ’sum‘)) # like a GROUP BYdf = df[df。num_yes > 2] # like a HAVING, filtering on the result of a GROUP BYdf = df[[’num_yes‘, ’something1‘, ’something‘]] # pick the columns I want to display, like a SELECTdf。sort_values(’sometthing‘, ascending=True)[:30] # ORDER BY and LIMITdf[:30]
這並不是因為 Pandas 的強制規定,只是按照 JOIN/WHERE/GROUP BY/HAVING 的順序來編寫程式碼更有助於理解底層邏輯。(值得一提的是,可以在 JOIN 之前先執行 WHERE 來提高效能,大多數資料庫引擎在實踐中也是這樣來執行的)
R 中的 dplyr(R 語言用來操作資料框的包)還允許採用不同的語法查詢不同的 SQL 資料庫,如:Postgres、MySQL 和 SQLite。
最後
當我發現 SQL 查詢語句的這種執行順序時,我其實是非常驚訝的。透過探究 SQL 查詢語句的執行順序,把我之前遇到的問題搞清楚了。也希望本文能幫助到更多的人理解 SQL 的執行順序以及如何正確編寫 SQL 查詢語句。