索引多個有效日期列的最佳方法

我有一個表，主要用於表示實體之間的關係（即主要由外鍵組成）。這些關係隨時間而變化，因此該表有一個 StartDate 和一個 EndDate 列。我現在需要添加另一個維度的開始日期和結束日期，這意味著可以使用兩個不同的日期“鏡頭”查看關係（使用兩個日期進行查詢，@Date1 和 @Date2），因此架構將如下所示：

MyJoinTable:

| Id | Entity1Id | Entity2Id | StartDate1 |  EndDate1  | StartDate2 |  EndDate2  |
|----|-----------|-----------|------------|------------|------------|------------|
|  1 | A         | B         | 1753-01-01 | 2018-09-01 | 1753-01-01 | 2025-01-01 |
|  2 | A         | B         | 2018-09-01 | 2018-10-01 | 1753-01-01 | 2018-11-01 |
|  3 | A         | C         | 2018-09-01 | 2018-10-01 | 2018-11-01 | 2025-01-01 |
|  4 | A         | B         | 2018-10-01 | 2025-01-01 | 1753-01-01 | 2018-11-01 |
|  5 | A         | D         | 2018-10-01 | 2025-10-01 | 2018-11-01 | 2025-01-01 |

查詢將主要連接到該表，例如：

SELECT e1.Field, e2.Field
FROM Entity1 e1
INNER JOIN MyJoinTable jt ON jt.Entity1Id = e1.Id
   AND StartDate1 <= @Date1 AND EndDate1 > @Date1
   AND StartDate2 <= @Date2 and EndDate2 > @Date2
INNER JOIN Entity2 e2 ON e2.Id = jt.Entity2Id

我的問題是：

1. 索引此連接表的最佳方法是什麼？

• Entity1Id 上的索引
• Entity2Id 上的索引
• 所有四個日期列的綜合索引？（開始日期 1、結束日期 1、開始日期 2、結束日期 2）

2. 約束數據庫的最佳方法是什麼，以便我只為任何@Date1、@Date2 組合返回一個關係行？
3. 您對更好的數據模型有什麼建議嗎？

介紹

我沒有時間進行廣泛的測試，但可以建議從哪裡開始。

如果您以更對稱的方式重寫查詢，以強調兩個實體都連接到 的二維橫截面MyJoinTable：

SELECT E1.Field, E2.Field
FROM MyJoinTable JT
JOIN Entity1 E1 ON E1.Id = JT.Entity1Id
JOIN Entity2 E2 ON E2.Id = JT.Entity2Id
WHERE
   StartDate1 <= @date1 AND EndDate1 > @date1 AND
   StartDate2 <= @date2 AND EndDate2 > @date2

您將看到執行它的一種相當有效的方法是首先提取該橫截面，然後將實體連接到它。以下等效查詢將用於說明目的（但不要使用它！）：

-- A sample to demostrate the desired order or execution:
SELECT E1.Field, E2.Field
FROM
-- 1. Calculate the cross-section:
(   SELECT JT.Entity1Id, JT.Entity2Id
   FROM MyJoinTable JT
   WHERE 
       StartDate1 <= @date1 AND EndDate1 > @date1 AND
       StartDate2 <= @date2 AND EndDate2 > @date2
) SECT
-- 2. Join the entity tables:
JOIN Entity1 E1 ON E1.Id = SECT.Entity1Id
JOIN Entity2 E2 ON E2.Id = SECT.Entity2Id

更重要的是你說只有

為任何@Date1、@Date2 組合返回一個關係行。

出於這個原因，加入之前的實體MyJoinTable會減少到所需的橫截面，@date1並且@date2效率低下，因為每個實體可能有很多記錄，因此結果將包含太多行。實體最好在最後加入，使用它們的自然鍵欄位，Id我假設它是聚集索引。因此，下面的解決方案提出了計算這個橫截面的不同方法，對應於SECT上面範例的子查詢——

方案一：最簡單的

讓我們嘗試為原始查詢添加有用的索引。由於MSSQL的複合索引是分層的，因此它們在優化間隔比較方面毫無用處，因此我們可以對給定結構做的最好的事情是索引日期欄位之一，但確保涵蓋表中所需的所有其他欄位：

CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable ( StartDate1 )
INCLUDE (Entity1Id, Entity2Id, StartDate2, EndDate1, EndDate2 )

然後查詢將按以下順序執行：

1. 使用索引SD1，執行索引查找MyJoinTable 以查找記錄，並在 剩余謂詞中按、 StartDate1和 過濾它們StartDate2``EndDate1``EndDate2
2. 使用它們的自然鍵連接實體表Id。

這種方法效率不高，因為四個日期謂詞中只有一個被索引完全優化，結果謂詞是無聊的數據研磨器。

方案二：設置交點

獲得二維JOIN 橫截面的另一種對稱方法是通過INTERSECT四個日期謂詞的結果：

SELECT Entity1Id, Entity2Id
FROM
(   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
   WHERE @date1 >= StartDate1
   INTERSECT
   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
   WHERE @date2 >= StartDate2
   INTERSECT
   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
   WHERE @date1 <  EndDate1
   INTERSECT
   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
   WHERE @date2 <  EndDate2 )
SECT

有了合適的指標，

CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable (StartDate1)
   INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX SD2 ON MyJoinTable (StartDate2)
   INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX ED1 ON MyJoinTable (EndDate1  )
   INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX ED2 ON MyJoinTable (EndDate2  )
   INCLUDE (Id, Entity1Id, Entity2Id)

此查詢的計劃僅包括乾淨的索引查找（沒有剩余謂詞）和雜湊匹配操作。

觀察到查詢和索引中的實體鍵呈現出四重冗餘，可以通過分別加入實體來刪除這些冗餘——以更複雜的執行計劃為代價：

SELECT JT.Entity1Id, JT.Entity2Id
FROM
(   SELECT Id FROM MyJoinTable WHERE @date1 >= StartDate1
   INTERSECT
   SELECT Id FROM MyJoinTable WHERE @date2 >= StartDate2
   INTERSECT
   SELECT Id FROM MyJoinTable WHERE @date1 <  EndDate1
   INTERSECT
   SELECT Id FROM MyJoinTable WHERE @date2 <  EndDate2 )
SECT
JOIN MyJoinTable JT ON JT.Id = SECT.Id

使用這些索引：

CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable (StartDate1) INCLUDE (Id)
CREATE NONCLUSTERED INDEX SD2 ON MyJoinTable (StartDate2) INCLUDE (Id)
CREATE NONCLUSTERED INDEX ED1 ON MyJoinTable (EndDate1  ) INCLUDE (Id)
CREATE NONCLUSTERED INDEX ED2 ON MyJoinTable (EndDate2  ) INCLUDE (Id)

但是，由於在任何一種情況下，四個日期謂詞中的每一個（僅將日期限制在一端）都不能充分減少數據量，因此雜湊匹配可能不得不將 數據溢出 到tempdb. 如果是這樣，則此方法不適合您的環境。

解決方案三：妥協

現在我們可以合併解決方案 I 和 II，以便提出一個不需要太多 RAM 並且同時相當快的計劃：

SELECT Entity1Id, Entity2Id FROM
(   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
   WHERE @date1 >= StartDate1 AND @date1 < EndDate1
   INTERSECT
   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
   WHERE @date2 >= StartDate2 AND @date2 < EndDate2  )
SECT

現在，日期約束在丟棄數據方面更有效，因為它們在兩端綁定了日期。使用以下索引：

CREATE NONCLUSTERED INDEX SE1 ON MyJoinTable (StartDate1)
   INCLUDE (EndDate1, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX SE2 ON MyJoinTable (StartDate2)
   INCLUDE (EndDate2, Entity1Id, Entity2Id)

每個約束都使用帶有殘差謂詞的索引查找，這比解決方案 I 中的單個索引查找更好，並且比解決方案 II 佔用更少的 RAM。該計劃顯示了三個並行化機會：一個用於每個日期約束，一個用於INTERSECT操作。

這種方法的非冗餘版本是：

SELECT JT.Entity1Id, JT.Entity2Id FROM
(   SELECT Id FROM MyJoinTable
   WHERE @date1 >= StartDate1 AND @date1 < EndDate1
   INTERSECT
   SELECT Id FROM MyJoinTable
   WHERE @date2 >= StartDate2 AND @date2 < EndDate2  )
SECT
JOIN MyJoinTable JT ON JT.Id = SECT.Id

帶索引

CREATE NONCLUSTERED INDEX SE1 ON MyJoinTable (StartDate1)
   INCLUDE (EndDate1, Id)
CREATE NONCLUSTERED INDEX SE2 ON MyJoinTable (StartDate2)
   INCLUDE (EndDate2, Id)

儘管它應該更好地處理您的數據，其中SECT子查詢最多返回一行，但在我對隨機生成的數據的粗略測試中，它的效率較低，因為伺服器使用雜湊匹配而不是與該單行的嵌套循環連接。一定要在你身邊試試。

方案四：優化結構

可以以引入更複雜的結構為代價優化您的查詢，該結構需要額外的維護並減慢MyJoinTable. 如果您願意付出代價，請將日期範圍儲存為天數：

CREATE TABLE MyJoinTable
(   Id         INT IDENTITY(1,1),
   Entity1Id  INT,
   Entity2Id  INT,
   Range1     INT, -- reference to Ranges.Id 
   Range2     INT  -- reference to Ranges.Id
)

CREATE TABLE Ranges
(   Id    INT,
   Date  Date
)

並查詢關係：

SELECT E1.Field, E2.Field
FROM MyJoinTable JT
JOIN Entity1 E1  ON E1.Id = JT.Entity1Id
JOIN Entity2 E2  ON E2.Id = JT.Entity2Id
JOIN Ranges  R1  ON R1.Id = JT.Range1
JOIN Ranges  R2  ON R2.Id = JT.Range2
WHERE
   R1.Date = @date1 AND
   R2.Date = @date2

您需要進行一些測試才能確定最佳指數，但我認為以下應該可行：

CREATE NONCLUSTERED INDEX RD ON Ranges      ( Date   ) INCLUDE ( id )
CREATE NONCLUSTERED INDEX RR ON MyJoinTable ( Range1, Range2 )
-- optionally: INCLUDE (Entity1Id, Entity2Id)

索引RD將確保快速找到與指定日期對應的範圍，索引RR將幫助找到與這些範圍匹配的記錄。但是您必須設計一種方法來填充Ranges表格並使其與 保持同步MyJoinTable，因為手動這樣做是不可能的。

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