了解表假離線和相關子查詢之間的關係

我正在學習執行計劃，並且對錶假離線和相關子查詢之間的關係有疑問（我正在關注本教程，不要介意拼寫錯誤）。

我創建了下表：

CREATE TABLE student (
 ID INT IDENTITY(1, 1),
 CX_Name VARCHAR(50),
 CX_PhoneNum VARCHAR(50),
 CX_Address VARCHAR(MAX),
 CX_Credit INT
)

然後插入值：

INSERT INTO student
VALUES (
 'Alen',
 '9625788954',
 'London',
 500
) 
GO 100

INSERT INTO student
VALUES (
 'Frank',
 '962445785',
 'Germany',
 1400
)
GO 100

然後執行以下查詢，其中包括一個相關的子查詢：

SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
   SELECT AVG(CX_Credit)
   FROM student CX2
   WHERE CX1.ID = CX2.ID
)

執行計劃是：

本教程解釋（粗體是我的）：

SQL Server Engine 先從表中讀取數據，對數據進行排序，然後再將其劃分為段，然後創建一個臨時表來儲存數據組。

在解釋計劃的另一部分，SQL Server 引擎從 Table Spool 中讀取數據，然後使用 Stream Aggregate 運算符計算每個組的平均信用值。

最後一個 Table Spool 操作員將讀取分組數據並將其連接以檢索高於平均值的值。

三個 Table Spool 操作員將使用第一次創建的同一個臨時表。

我不明白加粗的句子，有兩種方式：

1. 必須為每個學生記錄重新執行相關子查詢。那麼分組在這裡有什麼作用呢？
2. 一個組以什麼方式“儲存”在表線軸內？

執行計劃如何運作

段假離線一次儲存一組的行。子樹每組執行一次。在每個組的處理結束時，假離線被截斷，並且對下一組行重複處理。

我在Partitioning and the Common Subexpression Spool中寫了完整的細節。

Segment Spool迭代器始終顯示為Segment迭代器的直接父級。計劃中顯示的兩個葉級表假離線迭代器是輔助假離線，它只重放主假離線保存的行。

Segment Spool懶惰地將行寫入其工作表，直到發出新組的開始信號。一旦Segment Spool在其工作表中有一個完整的組，就會將一行（不是整個組）返回給其父級（本範例中的頂級嵌套循環運算符）。

這一行中儲存的數據值並不重要；它們對最終結果沒有貢獻。關鍵是這一單行是在父嵌套循環迭代器的外部輸入上接收的。這導致迭代器每組執行一次其內部輸入。

你的例子

在您的範例中，分組是由以下相關性暗示的ID：

WHERE CX1.ID = CX2.ID

CX1.ID外部參考在哪裡。

給定原始查詢（缺少的 CX2 別名添加到 中AVG）：

SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
   SELECT AVG(CX2.CX_Credit)
   FROM student CX2
   WHERE CX1.ID = CX2.ID
)

是的，原則上，來自 CX1 的每一行都會計算出來自 CX2 的所有行的平均值，其中與外行中ID的目前ID值匹配。正是在這個意義上，群體才形成。

一般來說，以這種方式逐字執行查詢效率很低，並且會導致多次計算相同的平均值。這就是我們有優化器的原因；找到產生相同邏輯結果的等效物理計劃，但效率更高。在這種情況下，這意味著計算一次組平均值並通過重放假離線將其交叉連接到目前組中的行。

更重要的是，這裡的假離線解決了在流中還沒有看到的行上計算聚合的問題。考慮到最終計劃只訪問一次基表，儘管在原始查詢中有兩次引用它。將組中的行保存一次並重放它們會更有效，而不是每個外部行訪問一次基表。

例如，假設我們阻止優化器將查詢規範轉換為“分組應用”：

SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
   SELECT AVG(CX2.CX_Credit)
   FROM student CX2
   WHERE CX1.ID = CX2.ID
)
OPTION (QUERYRULEOFF GenGbApplySimple);

執行計劃現在有兩個表訪問：

如果我們進一步限制可用的優化技巧，我們會更接近原文的字面解釋：

SELECT ID, CX_Name, CX_Credit
FROM student CX1 
WHERE CX_Credit >= (
   SELECT AVG(CX2.CX_Credit)
   FROM student CX2
   WHERE CX1.ID = CX2.ID
)
OPTION 
(
   QUERYRULEOFF GenGbApplySimple, 
   LOOP JOIN, 
   FORCE ORDER, 
   NO_PERFORMANCE_SPOOL
);

---

您可能會發現更直覺的等效查詢規範是：

SELECT
   S1.ID,
   S1.CX_Name,
   S1.CX_Credit
FROM 
(
   SELECT 
       S.*, 
       avg_credit = AVG(S.CX_Credit) OVER (
           PARTITION BY S.ID)
   FROM dbo.student AS S
) AS S1
WHERE
   S1.CX_Credit >= S1.avg_credit;

給出的範例不是很有用，因為ID它實際上是獨一無二的。如果沒有某種約束，優化器無法保證這一點，因此它會防禦性地添加一個假離線。如果我們確保ID是唯一的：

CREATE UNIQUE INDEX i ON dbo.student (ID);

原始查詢生成一個沒有聚合的連接計劃（因為最多一行的聚合是多餘的）：

請嘗試以下我的部落格文章中的範例。您可以使用https://dbfiddle.uk/，它可以選擇每次都從 AdventureWorks 數據庫的新副本開始。

進一步閱讀

我的其他相關文章：

• 排名函式優化器轉換
• “Segment Top”查詢優化
• 段和序列項目迭代器

引用自：https://dba.stackexchange.com/questions/292746