為什麼更改聲明的連接列順序會引入排序？

我有兩個具有相同名稱、類型和索引鍵列的表。其中一個具有唯一的聚集索引，另一個具有非唯一的.

測試設置

設置腳本，包括一些真實的統計數據：

DROP TABLE IF EXISTS #left;
DROP TABLE IF EXISTS #right;

CREATE TABLE #left (
   a       char(4) NOT NULL,
   b       char(2) NOT NULL,
   c       varchar(13) NOT NULL,
   d       bit NOT NULL,
   e       char(4) NOT NULL,
   f       char(25) NULL,
   g       char(25) NOT NULL,
   h       char(25) NULL
   --- and a few other columns
);

CREATE UNIQUE CLUSTERED INDEX IX ON #left (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #left WITH ROWCOUNT=63800000, PAGECOUNT=186000;

CREATE TABLE #right (
   a       char(4) NOT NULL,
   b       char(2) NOT NULL,
   c       varchar(13) NOT NULL,
   d       bit NOT NULL,
   e       char(4) NOT NULL,
   f       char(25) NULL,
   g       char(25) NOT NULL,
   h       char(25) NULL
   --- and a few other columns
);

CREATE CLUSTERED INDEX IX ON #right (a, b, c, d, e, f, g, h)

UPDATE STATISTICS #right WITH ROWCOUNT=55700000, PAGECOUNT=128000;

複製品

當我在它們的集群鍵上加入這兩個表時，我期望一個一對多的 MERGE 連接，如下所示：

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
   l.a=r.a AND
   l.b=r.b AND
   l.c=r.c AND
   l.d=r.d AND
   l.e=r.e AND
   l.f=r.f AND
   l.g=r.g AND
   l.h=r.h
WHERE l.a='2018';

這是我想要的查詢計劃：

（不要介意警告，它們與虛假統計有關。）

但是，如果我更改連接中列的順序，如下所示：

SELECT *
FROM #left AS l
LEFT JOIN #right AS r ON
   l.c=r.c AND     -- used to be third
   l.a=r.a AND     -- used to be first
   l.b=r.b AND     -- used to be second
   l.d=r.d AND
   l.e=r.e AND
   l.f=r.f AND
   l.g=r.g AND
   l.h=r.h
WHERE l.a='2018';

… 有時候是這樣的：

Sort 運算符似乎根據聲明的連接順序對流進行排序，即c, a, b, d, e, f, g, h，它向我的查詢計劃添加了阻塞操作。

我看過的東西

• 我嘗試將列更改為NOT NULL，結果相同。
• 原始表是用 創建的ANSI_PADDING OFF，但創建它ANSI_PADDING ON不會影響這個計劃。
• 我嘗試了一個INNER JOIN而不是LEFT JOIN，沒有變化。
• 我在 2014 SP2 Enterprise 上發現了它，在 2017 Developer（目前 CU）上創建了一個複製品。
• 刪除前導索引列上的 WHERE 子句確實會產生好的計劃，但它會影響結果.. :)

最後，我們進入問題

• 這是故意的嗎？
• 我可以在不更改查詢的情況下消除排序嗎（這是供應商程式碼，所以我真的不想……）。我可以更改表和索引。

這是故意的嗎？

這是設計使然，是的。不幸的是，當 Microsoft 停用 Connect 回饋站點時，該斷言的最佳公共資源失去了，刪除了 SQL Server 團隊開發人員的許多有用評論。

無論如何，目前的優化器設計並沒有主動尋求避免不必要的排序本身。這在視窗函式等中最常遇到，但也可以在其他對排序敏感的運算符中看到，特別是對運算符之間的保留順序敏感。

儘管如此，優化器在避免不必要的排序方面非常好（在許多情況下），但這種結果通常是由於積極嘗試不同的排序組合之外的原因而發生的。從這個意義上說，與其說是“搜尋空間”的問題，不如說是正交優化器功能之間的複雜互動，這些互動已被證明可以以可接受的成本提高總體規劃質量。

例如，通常可以簡單地通過將排序要求（例如頂級ORDER BY）與現有索引匹配來避免排序。在您的情況下微不足道，這可能意味著添加ORDER BY l.a, l.b, l.c, l.d, l.e, l.f, l.g, l.h;，但這是一種過度簡化（並且不可接受，因為您不想更改查詢）。

更一般地，每個備忘錄組可以與所需的或期望的屬性相關聯，這些屬性可以包括輸入排序。當沒有明顯的理由強制執行特定命令時（例如，為了滿足ORDER BY，或確保來自對命令敏感的物理運算符的正確結果），則涉及“運氣”元素。我寫了更多關於它的細節，因為它與合併連接（在聯合或連接模式下）有關，在使用 Merge Join Concatenation 避免排序。其中大部分超出了產品支持的表面積，因此請將其視為資訊，並且可能會發生變化。

在您的特定情況下，是的，您可以按照 jadarnel27的建議調整索引以避免排序；儘管沒有什麼理由更喜歡這裡的合併連接。您還可以在不更改查詢的情況下使用計劃指南提示在散列或循環物理連接之間進行選擇OPTION(HASH JOIN, LOOP JOIN)，具體取決於您對數據的了解，以及最佳、最差和平均情況性能之間的權衡。

最後，出於好奇，請注意，可以使用簡單的 來避免排序ORDER BY l.b，但代價是可能效率較低的多對多合併連接b，並且具有復雜的殘差。我提到這主要是為了說明我之前提到的優化器功能之間的互動，以及頂級需求可以傳播的方式。

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