如何在 SQL Server 中提示多對多連接？

我有 3 個“大”表，它們連接在一對列（兩者int）上。

• 表 1 有約 2 億行
• 表 2 有約 150 萬行
• Table3 有大約 600 萬行

每個表在 , 上都有一個聚集索引Key1，Key2然後是一列。Key1具有低基數並且非常偏斜。它總是在WHERE子句中引用。Key2條款中從未提及WHERE。每個連接都是多對多的。

問題在於基數估計。每個連接的輸出估計變得更小而不是更大。當實際結果達到數百萬時，這導致最終估計為低數百。

我有什麼辦法可以讓行政長官做出更好的估計嗎？

SELECT 1
FROM Table1 t1
    JOIN Table2 t2
      ON t1.Key1 = t2.Key1
         AND t1.Key2 = t2.Key2
    JOIN Table3 t3
      ON t1.Key1 = t3.Key1
         AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

我嘗試過的解決方案：

• 在 上創建多列統計資訊Key1，Key2
• 創建大量過濾後的統計資訊Key1（這很有幫助，但我最終會在數據庫中獲得數千個使用者創建的統計資訊。）

屏蔽的執行計劃（抱歉屏蔽不好）

在我正在查看的情況下，結果有 900 萬行。新的 CE 估計有 180 行；舊版 CE 估計有 6100 行。

這是一個可重現的範例：

DROP TABLE IF EXISTS #Table1, #Table2, #Table3;
CREATE TABLE #Table1 (Key1 INT NOT NULL, Key2 INT NOT NULL, T1Key3 INT NOT NULL, CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED (Key1, Key2, T1Key3));
CREATE TABLE #Table2 (Key1 INT NOT NULL, Key2 INT NOT NULL, T2Key3 INT NOT NULL, CONSTRAINT pk_t2 PRIMARY KEY CLUSTERED (Key1, Key2, T2Key3));
CREATE TABLE #Table3 (Key1 INT NOT NULL, Key2 INT NOT NULL, T3Key3 INT NOT NULL, CONSTRAINT pk_t3 PRIMARY KEY CLUSTERED (Key1, Key2, T3Key3));

-- Table1 
WITH Numbers
    AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
        FROM master..spt_values t1
             CROSS JOIN master..spt_values t2),
    DataSize (Key1, NumberOfRows)
    AS (SELECT 1, 2000 UNION
        SELECT 2, 10000 UNION
        SELECT 3, 25000 UNION
        SELECT 4, 50000 UNION
        SELECT 5, 200000)
INSERT INTO #Table1
SELECT Key1
    , Key2 = ROW_NUMBER() OVER (PARTITION BY Key1, T1Key3 ORDER BY Number)
    , T1Key3
FROM DataSize
    CROSS APPLY (SELECT TOP(NumberOfRows) 
                        Number
                      , T1Key3 = Number%(Key1*Key1) + 1 
                 FROM Numbers
                 ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smaller number of distinct third Key)
WITH Numbers
    AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
        FROM master..spt_values t1
             CROSS JOIN master..spt_values t2)
INSERT INTO #Table2
SELECT DISTINCT 
      Key1
    , Key2
    , T2Key3
FROM #Table1
    CROSS APPLY (SELECT TOP (Key1*10) 
                        T2Key3 = Number
                 FROM Numbers
                 ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smallest number of distinct third Key)
WITH Numbers
    AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
        FROM master..spt_values t1
             CROSS JOIN master..spt_values t2)
INSERT INTO #Table3
SELECT DISTINCT 
      Key1
    , Key2
    , T3Key3
FROM #Table1
    CROSS APPLY (SELECT TOP (Key1) 
                        T3Key3 = Number
                 FROM Numbers
                 ORDER BY Number) size;


DROP TABLE IF EXISTS #a;
SELECT col = 1 
INTO #a
FROM #Table1 t1
    JOIN #Table2 t2
      ON t1.Key1 = t2.Key1
         AND t1.Key2 = t2.Key2
WHERE t1.Key1 = 1;

DROP TABLE IF EXISTS #b;
SELECT col = 1 
INTO #b
FROM #Table1 t1
    JOIN #Table2 t2
      ON t1.Key1 = t2.Key1
         AND t1.Key2 = t2.Key2
    JOIN #Table3 t3
      ON t1.Key1 = t3.Key1
         AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

需要明確的是，優化器已經知道這是一個多對多連接。如果您強制合併連接並查看估計的計劃，您可以看到連接運算符的屬性，它告訴您連接是否可以是多對多的。您需要在這裡解決的問題是提高基數估計值，大概是這樣您就可以為您遺漏的查詢部分獲得更有效的查詢計劃。

我要嘗試的第一件事是將連接的結果Object3放入Object5臨時表中。對於您發布的計劃，它只是 51393 行上的一列，因此它幾乎不應該佔用 tempdb 中的任何空間。您可以在臨時表上收集完整的統計數據，僅此一項就足以獲得足夠準確的最終基數估計。收集完整的統計數據Object1也可能有所幫助。當您從右到左遍歷計劃時，基數估計通常會變得更糟。

如果這不起作用，ENABLE_QUERY_OPTIMIZER_HOTFIXES如果您尚未在數據庫或伺服器級別啟用查詢提示，則可以嘗試查詢提示。Microsoft 將影響計劃的 SQL Server 2016 性能修復鎖定在該設置後面。其中一些與基數估計有關，因此也許您會很幸運，其中一個修復程序將對您的查詢有所幫助。您還可以嘗試使用帶有FORCE_LEGACY_CARDINALITY_ESTIMATION查詢提示的舊基數估計器。某些數據集可能會使用舊版 CE 獲得更好的估計。

作為最後的手段，您可以使用 Adam Machanic 的MANY()函式通過您喜歡的任何因素手動增加基數估計。我在另一個答案中談論它，但看起來連結已經死了。如果您有興趣，我可以嘗試探勘一些東西。

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