由于最近需要做一些sql query性能提升的研究,是以研究了一下sql語句的解決過程。在園子裡看了下,大家寫了很多相關的文章,大家的側重點各有不同。本文是我在看了各種資料後手機總結的,會詳細的,一步一步的講述一個sql語句的各個關鍵字的解析過程,歡迎大家互相學習。
SQL語句的解析順序
簡單的說一個sql語句是按照如下的順序解析的:
- 1. FROM FROM後面的表辨別了這條語句要查詢的資料源。和一些子句如,(1-J1)笛卡爾積,(1-J2)ON過濾,(1-J3)添加外部列,所要應用的對象。FROM過程之後會生成一個虛拟表VT1。
- (1-J1)笛卡爾積 這個步驟會計算兩個相關聯表的笛卡爾積(CROSS JOIN) ,生成虛拟表VT1-J1。
- (1-J2)ON過濾 這個步驟基于虛拟表VT1-J1這一個虛拟表進行過濾,過濾出所有滿足ON 謂詞條件的列,生成虛拟表VT1-J2。
- (1-J3)添加外部行 如果使用了外連接配接,保留表中的不符合ON條件的列也會被加入到VT1-J2中,作為外部行,生成虛拟表VT1-J3。
- 2. WHERE 對VT1過程中生成的臨時表進行過濾,滿足where子句的列被插入到VT2表中。
- 3. GROUP BY 這個子句會把VT2中生成的表按照GROUP BY中的列進行分組。生成VT3表。
- 4. HAVING 這個子句對VT3表中的不同的組進行過濾,滿足HAVING條件的子句被加入到VT4表中。
- 5. SELECT 這個子句對SELECT子句中的元素進行處理,生成VT5表。
- (5-1)計算表達式 計算SELECT 子句中的表達式,生成VT5-1
- (5-2)DISTINCT 尋找VT5-1中的重複列,并删掉,生成VT5-2
- (5-3)TOP 從ORDER BY子句定義的結果中,篩選出符合條件的列。生成VT5-3表
- ORDER BY 從VT5-3中的表中,根據ORDER BY 子句的條件對結果進行排序,生成VC6表。
客戶,訂單的查詢例子
首先建立一個Customers表,插入如下資料:
customerid | city |
FISSA | Madrid |
FRNDO | Madrid |
KRLOS | Madrid |
MRPHS | Zion |
建立一個Orders表,插入如下資料:
orderid | customerid |
1 | FRNDO |
2 | FRNDO |
3 | KRLOS |
4 | KRLOS |
5 | KRLOS |
6 | MRPHS |
7 | NULL |
假如我們想要查詢來自Madrid的,訂單數小于3的客戶,并把他們的訂單數顯示出來,結果按照訂單數從小到大進行排序。
SELECT C.customerid, COUNT(O.orderid) AS numorders
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid
WHERE C.city = 'Madrid'
GROUP BY C.customerid
HAVING COUNT(O.orderid)
<
3
ORDER BY numorders
查詢結果為:
customerid | numorders |
FISSA | |
FRNDO | 2 |
下面我們會詳細的講述sql是怎樣計算出這個結果的:
FROM子句
FROM子句辨別了需要查詢的表,如果指定了表操作,會從左到右的處理,每一個基于一個或者兩個表的表操作都會傳回一個輸出表。左邊表的輸出結果會作為下一個表操作的輸入結果。例如,交表相關的操作有 (1-J1)笛卡爾積,(1-J2)ON過濾器,(1-J3)添加外部列。FROM句子生成虛拟表VT1。
Step 1-J1:執行笛卡爾積(CROSS JOIN)
笛卡爾積會把左右兩個表每一行所有可能的組合都列出來生成表VT1-J1,如果左表有m列,右表有n列,那麼笛卡爾積之後生成的VT1-J1表将會有m×n列。
Step 1-J1這個步驟等價于執行:
SELECT * from Customers C CROSS JOIN Orders O
執行結果為:(共有4×7列)
C.customerid | C.city | O.orderid | O.customerid |
FISSA | Madrid | 1 | FRNDO |
FISSA | Madrid | 2 | FRNDO |
FISSA | Madrid | 3 | KRLOS |
FISSA | Madrid | 4 | KRLOS |
FISSA | Madrid | 5 | KRLOS |
FISSA | Madrid | 6 | MRPHS |
FISSA | Madrid | 7 | NULL |
FRNDO | Madrid | 1 | FRNDO |
FRNDO | Madrid | 2 | FRNDO |
FRNDO | Madrid | 3 | KRLOS |
FRNDO | Madrid | 4 | KRLOS |
FRNDO | Madrid | 5 | KRLOS |
FRNDO | Madrid | 6 | MRPHS |
FRNDO | Madrid | 7 | NULL |
KRLOS | Madrid | 1 | FRNDO |
KRLOS | Madrid | 2 | FRNDO |
KRLOS | Madrid | 3 | KRLOS |
KRLOS | Madrid | 4 | KRLOS |
KRLOS | Madrid | 5 | KRLOS |
KRLOS | Madrid | 6 | MRPHS |
KRLOS | Madrid | 7 | NULL |
MRPHS | Zion | 1 | FRNDO |
MRPHS | Zion | 2 | FRNDO |
MRPHS | Zion | 3 | KRLOS |
MRPHS | Zion | 4 | KRLOS |
MRPHS | Zion | 5 | KRLOS |
MRPHS | Zion | 6 | MRPHS |
MRPHS | Zion | 7 | NULL |
Step 1-J2:應用ON過濾,(JOIN 條件)
ON過濾條件是sql的三個過濾條件(ON,WHERE,HAVING)中最先執行的,ON過濾條件應用于前一步生成的虛拟表(VT1-J1),滿足ON過濾條件的行會被加入到虛拟表VT1-J2中。在應用了ON 過濾之後,生成的VT1-J2表如下所示:
C.customerid | C.city | O.orderid | O.customerid |
FRNDO | Madrid | 1 | FRNDO |
FRNDO | Madrid | 2 | FRNDO |
KRLOS | Madrid | 3 | KRLOS |
KRLOS | Madrid | 4 | KRLOS |
KRLOS | Madrid | 5 | KRLOS |
MRPHS | Zion | 6 | MRPHS |
Step 1-J3:添加外部列
這個步驟隻會出現在使用了外連接配接的情況。對于外連接配接(LEFT,RIGHT, or FULL),你可以标記一個或者兩個表作為保留表。作為保留表意味着你希望這個表裡面的所有列都被傳回,即使它裡面的資料不滿足ON子句的過濾條件。LEFT OUTER JOIN 把左邊的表标記為保留表,RIGHTOUTER JOIN把右邊的表作為保留表,FULL OUTER JOIN把兩個表都标記為保留表.Step 1-J3為根據VT1-J2中的虛拟表,添加了保留表中不滿足ON 條件的列,在未保留表中沒有對應的列,是以标記為NULL。這個過程生成了虛拟表VT1-J3。
C.customerid | C.city | O.orderid | O.customerid |
FISSA | Madrid | NULL | NULL |
FRNDO | Madrid | 1 | FRNDO |
FRNDO | Madrid | 2 | FRNDO |
KRLOS | Madrid | 3 | KRLOS |
KRLOS | Madrid | 4 | KRLOS |
KRLOS | Madrid | 5 | KRLOS |
MRPHS | Zion | 6 | MRPHS |
如果FROM子句中有多個表操作運算,sql會按照從左到右的順序處理,左邊生成的臨時表結果作為右邊表的輸入表。
Step 2 WHERE 子句
WHERE過濾被應用到前一步生成的臨時表中,根據WHERE過濾條件生成臨時表VT2。
注意:由于資料現在還沒有被分組,是以現在你不能使用聚合運算-例如:你不能使用這樣的句子 WHERE orderdate = MAX(orderdate)。另外你也不能使用SELECT子句中建立的變量别名,因為現在還沒有處理SELECT子句-例如你不能寫這樣的句子:SELECT YEAR(orderdate) AS orderyear . . . WHERE orderyear > 2008.
應用這個過濾
WHERE C.city = 'Madrid'
這時生成的臨時表VT2的内容如下:
C.customerid | C.city | O.orderid | O.customerid |
FISSA | Madrid | NULL | NULL |
FRNDO | Madrid | 1 | FRNDO |
FRNDO | Madrid | 2 | FRNDO |
KRLOS | Madrid | 3 | KRLOS |
KRLOS | Madrid | 4 | KRLOS |
KRLOS | Madrid | 5 | KRLOS |
在這個例子中,你需要在ON子句中使用ON C.customerid = O.customerid過濾,沒有訂單的客戶在1-J2這一步中被過濾掉,但是在1-J3這一步中作為外部列又被加回來。但是,由于你隻想傳回來自Madrid的客戶,是以你需要在WHERE子句中過濾城市(WHERE C.city = ‘Madrid’),如果你放在ON過濾中,不屬于Madrid的客戶在添加外部列中會被添加回來。
關于ON 和 WHERE 的差別需要在這裡說明一下,ON 和WHERE 的主要差別在于 ON 實在添加外部列之前進行過濾,WHERE 是在之後。ON過濾掉的列會在1-J3中添加回來。如果你不需要添加外部列,那麼這兩個過濾是相同的。
Step 3 GROUP BY子句
這個子句會把前一步中生成的臨時表中的資料進行分組,每一行都會分到并且隻分到一個組裡,生成虛拟表VT3。VT3表中包含了VT2表中所有的資料,和分組辨別符。
這是生成的臨時表VT3的内容如下:
Groups C.customerid | C.customerid | C.city | O.orderid | O.customerid |
FISSA | FISSA | Madrid | NULL | NULL |
FRNDO | FRNDO | Madrid | 1 | FRNDO |
FRNDO | Madrid | 2 | FRNDO | |
KRLOS | Madrid | 3 | KRLOS | |
KRLOS | KRLOS | Madrid | 4 | KRLOS |
KRLOS | Madrid | 5 | KRLOS |
sql最終傳回的結果中,每一個分組必須隻能傳回一行(除非被過濾掉),是以當一個sql語句中使用了GROUP BY時,在GROUP BY後面處理的子句,如SELECT,HAVING子句等,隻能使用出現在GROUP BY後面的列,對于沒有出現GROUP BY後面的列必須使用聚合函數(如 MAX ,MIN,COUNT,AVG等),保證每一個GROUP隻傳回一行。
Step 4 HAVING子句
HAVING子句用來過濾前一步生成的臨時表,并且隻作用于分組後的資料,滿足HAVING條件的GROUP被添加到虛拟表VT4中。
當應用了這個過濾:
HAVING COUNT(O.orderid) < 3
之後,生成的VT4表内容如下:
Groups C.customerid | C.customerid | C.city | O.orderid | O.customerid |
FISSA | FISSA | Madrid | NULL | NULL |
FRNDO | FRNDO | Madrid | 1 | FRNDO |
FRNDO | Madrid | 2 | FRNDO |
需要注意的一點是,這裡面使用的是COUNT(O.orderid),而不是COUNT(*),由于這個查詢中添加了外部列,COUNT方法會忽略NULL的列,導緻出現了你不想要的結果。
Step 5 SELECT 子句
盡管出現在sql語句的最前面,SELECT在第五步的時候才被處理,SELECT子句傳回的表會最終傳回給調用者。這個子句包含三個子階段:(5-1)計算表達式,(5-2) 處理DISTINCT,(5-3)應用TOP過濾。
Step 5-1 計算表達式
SELECT子句中的表達式可以傳回或者操作前一步表中傳回的基本列。如果這個sql語句是一個聚合查詢,在Step 3之後,你隻能使用GROUP BY中的列,對不屬于GROUP集合中的列必須使用聚合運算。不屬于FROM表中基本列的必須為其起一個别名,如YEAR(orderdate) AS orderyear。
注意:在SELECT子句中建立的别名,不能在之前的Step中使用,即使在SELECT子句中也不能。原因是sql的很多操作是同時操作(all at once operation),至于什麼是all-at-once operation這裡就不再介紹了。是以,SELECT子句中建立的别名隻能在後面的子句中使用,如ORDER BY。例如:SELECT YEAR(orderdate) AS orderyear . . . ORDER BY orderyear。
在這個例子中:
SELECT C.customerid, COUNT(O.orderid) AS numorders
結果會得到一個虛拟表VT5-1:
C.customerid | numorders |
FIFSSA | |
FRNDO | 2 |
Step 5-2:應用DISTINCT子句
如果sql語句中使用了DISTINCT,sql會把重複列去掉,生成虛拟表VT5-2。
Step 5-3:應用TOP選項
TOP選項是T-SQL提供的一個功能,用來表示顯示多少行。基于ORDER BY子句定義的順序,指定個數的列會被查詢出來。這個過程生成虛拟表VT5-3。
正如上文提到的,這一步依賴于ORDER BY定義的順序來決定哪些列應該顯示在前面。如果你沒有指定結果的ORDER BY順序,也沒有使用WITH TIES子句 ,每一次的傳回結果可能會不一緻。
在我們的例子中,Step 5-3被省略了,因為我們沒有使用TOP關鍵字。
Step 6:ORDER BY子句
前一步傳回的虛拟表在這一步被排序,根據ORDER BY子句指定的順序,傳回遊标VC6。ORDER BY子句也是唯一一個可以使用SELECT子句建立的别名的地方。
注意:這一步和之前不同的地方在于,這一步傳回的結果是一個遊标,而不是表。sql是基于集合理論的,一個集合沒有對他的行定義順序,它隻是一個成員的邏輯集合,是以成員的順序并不重要。帶有ORDER BY子句的sql傳回一個按照特定序列組織每一行的對象。ANSI 把這樣的一個對象叫遊标。了解這一點對你了解sql很重要。