鋒利的SQL2014：聯結演算法，sql2014聯結演算法

鋒利的SQL2014：聯結演算法，sql2014聯結演算法
 

在Microsoft SQLServer Management Studio中執行查詢時，如果選定工具列中的按鈕，可以看到為查詢產生的執行計畫。執行計畫以圖形方式顯示了SQL Server查詢最佳化工具選擇的資料檢索方法，如表掃描、排序、雜湊匹配等。對於聯結查詢，SQL Server會根據聯結表之間的資料、索引等情況，選擇使用巢狀迴圈聯結、合并聯結或雜湊聯結。

7.7.1 巢狀迴圈聯結

巢狀迴圈聯結也稱為“嵌套迭代”，它將一個聯結輸入用作外部輸入表（顯示為圖形執行計畫中的頂端輸入），將另一個聯結輸入用作內部（底端）輸入表。外部迴圈逐行處理外部輸入表。內部迴圈會針對每個外部行執行，在內部輸入表中搜尋匹配行。簡單地講，就是掃描其中的一個聯結表，並為該表中的每一行在另一個聯結表中搜尋匹配行。

如果外部輸入較小（不到10行）而內部輸入較大且預先建立了索引，則巢狀迴圈聯結尤其有效。在許多小事務中（如那些隻影響較小的一組行的事務），索引巢狀迴圈聯結優於合并聯結和雜湊聯結。但在大型查詢中，巢狀迴圈聯結通常不是最佳選擇。

例如，下面的查詢由於Sales.Customer表行數只有1行，而Sales.SalesOrderHeader資料量較大，因此將使用巢狀迴圈聯結，產生的執行計畫7-11所示。

USE AdventureWorks2014;

GO

SELECT *

FROM Sales.Customer  

  INNER JOINSales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID

WHERE Customer.CustomerID = 1;

圖7-11 使用嵌套迴圈的執行計畫

在該計劃中存在兩個嵌套迴圈，其中只有左邊的嵌套迴圈符用於Sales.Customer 與Sales.SalesOrderHeader 的聯結，而右邊的嵌套迴圈是用於Sales.SalesOrderHeader的索引尋找與物理行定位（鍵尋找）之間的聯結。執行計畫右上方的Sales.Customer表被作為外部輸入，在叢集索引中尋找客戶。對於每個客戶，嵌套迴圈運算將對SalesOrderHeader.CustomerID 列上的IX_SalesOrderHeader_CustomerID索引執行一次尋找，然後再跟一個鍵尋找來定位要訪問的資料行。

7.7.2 合并聯結

合并聯結要求兩個輸入都在合并列上排序，合并列由聯結謂詞的等效（ON）子句定義。由於每個輸入都已排序，因此合并聯結將從每個輸入擷取一行並將其進行比較。例如，對於內聯結操作，如果行相等則返回。如果行不相等，則廢棄值較小的行並從該輸入獲得另一行。這一過程將重複進行，直到處理完所有的行為止。

合并聯結操作可以是常規操作，也可以是多對多操作。多對多合并聯結使用暫存資料表儲存行。如果每個輸入中有重複值，則在處理其中一個輸入中的每個重複項時，另一個輸入必須重繞到重複項的開始位置。

合并聯結本身的速度很快，但是如果合并列上未建立索引，選擇合并聯結有可能會非常費時，因為它首先要對列進行排序操作。然而，如果資料量很大且能夠從索引中獲得預排序的所需資料，則合并聯結通常是最快的可用聯結演算法。

例如，下面的查詢語句將擷取訂單的詳細資料，由於SalesOrderHeader和SalesOrderDetail在合并列SalesOrderID上都具有叢集索引，已經將列進行了排序，所以查詢最佳化工具會選擇合并聯結。7-12所示。

USE AdventureWorks2014;

GO

SELECT *

FROM Sales.SalesOrderHeader  

  INNER JOINSales.SalesOrderDetail 

    ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID;

圖7-12 使用合并聯結的執行計畫

7.7.3 雜湊聯結

雜湊聯結可以有效處理未排序的大型非索引輸入。因此，它對處理複雜查詢的中間結果很有用。查詢的中間結果是未經索引的，而且通常不會為查詢計劃中的下一個操作進行適當的排序。並且，查詢最佳化工具只估計中間結果的大小。而對於複雜查詢，估計可能有很大的誤差，因此如果中間結果比預期的大得多，則處理中間結果的演算法不僅必須有效而且必須適度弱化。再像合并聯結那樣嚴格要求具備排序列，對於中間結果而言是不現實的，排序成本的付出可能要遠遠大於資料的直接檢索成本。

選擇雜湊聯結的兩種情況：一是沒有為聯結建立合適的索引，二是中間結果比較大。

雜湊聯結有兩種輸入：產生輸入和探測輸入。查詢最佳化工具會選擇二者中較小的那個作為產生輸入，對聯結列值應用雜湊函數，將產生輸入中的行分配到雜湊桶中。雜湊桶是一種存放所訪問資料位元置的結構，有了它，進行資料檢索時，可以避免不必要的表掃描。

為了驗證無索引情況下的雜湊聯結使用，首先使用下面的語句建立Sales.Customer和Sales.SalesOrderHeader表的副本。

USE AdventureWorks2014;

GO

SELECT TOP 10 *

INTO MyCustomer

FROM Sales.Customer

ORDER BY CustomerID;

 

SELECT TOP 100 *

INTO MySalesOrderHeader

FROM Sales.SalesOrderHeader

ORDER BY CustomerID;

執行下面的查詢，可以看到7-13所示的執行計畫。

SELECT *

FROM MyCustomer

  INNER JOINMySalesOrderHeader

    ONMyCustomer.CustomerID = MySalesOrderHeader.CustomerID;

圖7-13 使用雜湊聯結的執行計畫

下面再來看一個比較有趣的樣本。下面的查詢語句中僅選擇了Sales.Customer中CustomerID = 1的行與Sales.SalesOrderHeader進行聯結，由於聯結行數很小，所產生中間結果的資料量也比較小，因此，可以看到查詢最佳化工具為語句使用了巢狀迴圈聯結。7-14所示。

USE AdventureWorks2014;

GO

SELECT *

FROM Sales.Customer  

  INNER JOINSales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID

WHERE Customer.CustomerID = 1;    

圖7-14 資料量較小時使用巢狀迴圈聯結

 同樣是上面的聯結，去除掉WHERE篩選條件後資料量明顯增大，執行該語句會發現查詢最佳化工具使用了雜湊聯結方式。7-15所示。

SELECT *

FROM Sales.Customer  

  INNER JOINSales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID;  

圖7-15 資料量較大時使用雜湊聯結

7.7.4 使用聯結提示強制聯結策略

聯結提示用於指定查詢最佳化工具在兩個表之間強制執行聯結策略，提示符包括LOOP JOIN、MERGE JOIN和HASH JOIN，分別用於嵌套迴圈、雜湊和合并聯結。如果指定了多個聯結提示，則最佳化器從允許的聯結策略中選擇開銷最少的聯結策略。此外，也可以使用OPTION子句指定聯結策略。但是這種方式會影響查詢中的所有聯結，通常用於舊式聯結文法。

1．為每個聯結指定單獨的聯結策略

可以在FROM子句中使用LOOP JOIN、MERGE JOIN和HASH JOIN提示符為每個聯結單獨指定聯結策略。例如，下面的查詢語句指定使用巢狀迴圈聯結。

USE AdventureWorks2014;

GO

SELECT *

FROM Sales.Customer  

  INNER LOOPJOIN Sales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID;

又如，下面的查詢語句指定使用合并聯結。

USE AdventureWorks2014;

GO

SELECT *

FROM Sales.Customer  

  INNERMERGE JOIN Sales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID;

在多表聯結中使用聯結提示時，會影響聯結的執行順序。在前面介紹了，在不影響返回結果正確的情況下，查詢最佳化工具會按照效率優先的原則，選擇首先執行的聯結。例如，下面語句的執行計畫7-16所示，可以看到首先執行的是Sales.SalesOrderHeader與Sales.SalesOrderDetail的聯結，然後將聯結結果再與Sales.Customer進行聯結。

USE AdventureWorks2014;

GO

SELECT *

FROM Sales.Customer  

  INNER JOINSales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID

  INNER JOINSales.SalesOrderDetail

    ON SalesOrderHeader.SalesOrderID =SalesOrderDetail.SalesOrderID; 

圖7-16 未使用聯結提示的執行計畫

下面的語句為Sales.Customer和Sales.SalesOrderHeader指定了合并聯結提示，並且這個提示僅對這兩個表起作用，與Sales.SalesOrderDetail的聯結策略仍舊由查詢最佳化工具決定。由於明確指定了Sales.Customer與Sales.SalesOrderHeader使用合并聯結，最佳化器會先執行該聯結，而不是先執行Sales.SalesOrderHeader與Sales.SalesOrderDetail的聯結。否則，就會造成Sales.Customer與Sales.SalesOrderHeader和Sales.SalesOrderDetail的聯結結果再執行合并聯結。圖7-17是該語句的執行計畫。

SELECT *

FROM Sales.Customer  

  INNERMERGE JOIN Sales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID

  INNER JOINSales.SalesOrderDetail

    ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID; 

圖7-17 使用聯結提示後的執行計畫

如果希望Sales.Customer與Sales.SalesOrderHeader和Sales.SalesOrderDetail的聯結結果執行合并聯結，則應當使用嵌套聯結的方式實現，參考下面的語句：

SELECT *

FROM Sales.Customer  

  INNERMERGE JOIN (Sales.SalesOrderHeader

                   INNER JOIN Sales.SalesOrderDetail

                      ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID)

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID;

2．為全部聯結指定統一的聯結策略

當使用舊式聯結文法時，應當使用OPTION子句指定聯結策略，但是，這種策略會影響語句中的全部聯結，無法為每個聯結單獨指定不同的聯結策略，如：

SELECT *

FROM Sales.Customer, Sales.SalesOrderHeader,Sales.SalesOrderDetail

WHERE Customer.CustomerID =SalesOrderHeader.CustomerID

  AND SalesOrderHeader.SalesOrderID= SalesOrderDetail.SalesOrderID

OPTION (MERGE JOIN); 

該語句的執行計畫7-18所示，可以看到三個表之間全部使用了合并聯結策略。

圖7-18 為全部聯結使用統一聯結策略的執行計畫

在ANSI SQL:1992規範中，也可以使用OPTION子句，它同樣也是影響語句中的全部聯結，如：

SELECT *

FROM Sales.Customer  

  INNER JOINSales.SalesOrderHeader

    ONCustomer.CustomerID = SalesOrderHeader.CustomerID

  INNER JOINSales.SalesOrderDetail

    ONSalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID

OPTION (MERGE JOIN);