如何使用索引視圖對日期時間列進行過濾以提高查詢性能？

我有這張桌子：

CREATE TABLE transactions
(
   id                                    NUMERIC(20, 0)              NOT NULL PRIMARY KEY,
   amount                                NUMERIC(18, 2) DEFAULT NULL NULL,
   -- Some 100 columns
   customer_msisdn                       VARCHAR(255)   DEFAULT NULL              NULL,
   customer_email                        VARCHAR(255)   DEFAULT NULL              NULL,
   payment_date                          DATETIME2                   NOT NULL
);

CREATE NONCLUSTERED INDEX msisdn_idx ON transactions (customer_msisdn, payment_date, id);
CREATE NONCLUSTERED INDEX email_idx ON transactions (customer_email, payment_date, id);

我每月索引大約 100 萬行。非常頻繁地，我需要選擇過去 3 個月的每筆交易customer_msisdn或每筆交易customer_email，即 99% 的時間 50 - 1000 條記錄。

這是我的查詢以獲得更多見解：

SELECT t.*
FROM transactions t
        JOIN (SELECT t.id
              FROM transactions t
                       WITH (FORCESEEK)
              WHERE t.customer_email = :customerEmail
                AND t.payment_date >= :startDate
                AND t.payment_date < :endDate
              UNION
              SELECT t.id
              FROM transactions t
                       WITH (FORCESEEK)
              WHERE t.customer_msisdn = :customerMsisdn
                AND t.payment_date >= :startDate
                AND t.payment_date < :endDate) AS filtered_transactions
             ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

而且我覺得既然:endDate總是現在（如果不是，可以容忍錯誤）並且:startDate總是三個月前，我還有一些改進的空間。這就是我的想法：

創建一個帶有過濾器的索引視圖payment_date：

CREATE VIEW [dbo].transactions_iv
   WITH SCHEMABINDING AS
SELECT [t].id,
      -- All the rows
      [t].customer_msisdn,
      [t].customer_phone,
      [t].payment_date
FROM [dbo].[transactions] [t]
WHERE [t].payment_date >= DATEADD(MONTH, -3, CURRENT_TIMESTAMP);

和我的索引：

CREATE NONCLUSTERED INDEX msisdn_iv_idx ON transactions_iv (customer_msisdn, id);
CREATE NONCLUSTERED INDEX phone_iv_idx ON transactions_iv (customer_phone, id);

並從查詢中完全刪除AND t.payment_date >= :startDate AND t.payment_date < :endDate子句。查詢變為：

SELECT t.*
FROM transactions_iv t
        JOIN (SELECT t.id
              FROM transactions_iv t
                       WITH (FORCESEEK)
              WHERE t.customer_email = :customerEmail
              UNION
              SELECT t.id
              FROM transactions_iv t
                       WITH (FORCESEEK)
              WHERE t.customer_msisdn = :customerMsisdn) AS filtered_transactions
             ON t.id= filtered_transactions.id
ORDER BY t.payment_date;

由於該視圖僅包含最近 3 個月的交易，我假設索引也是如此。這個假設正確嗎？是否會僅更新索引以涵蓋最近 3 個月的記錄，並且我的性能會得到提升嗎？

---

另一種選擇是：

1. 創建另一個相同的表，
2. 用主表上的觸發器填充它
3. 使用 cron 作業，每晚刪除 3 個月以上的記錄。

此選項與前一個選項相比如何？

您的索引視圖無法按書面形式創建，因為它是不確定的。隨著時間的推移，行會從視圖中消失。

您目前的查詢可能會生成一個執行計劃，例如：

暫時擱置單獨的表和索引視圖的問題，試一下下面的小重寫（使用現有索引）：

SELECT t.*
FROM transactions t
JOIN 
(
   SELECT t.id, t.payment_date
   FROM transactions t
   WHERE
       t.customer_email = @customerEmail
       AND t.payment_date >= @startDate
       AND t.payment_date < @endDate
   UNION
   SELECT t.id, t.payment_date
   FROM transactions t
   WHERE 
       t.customer_msisdn = @customerMsisdn
       AND t.payment_date >= @startDate
       AND t.payment_date < @endDate
) AS filtered_transactions
   ON filtered_transactions.id = t.id
ORDER BY 
   filtered_transactions.payment_date;

請注意 中的額外列t.payment_date，UNION並且ORDER BY已更改為filtered_transactions.payment_date。這在語義上與您的查詢沒有什麼不同，但它會幫助優化器找到更好的計劃。

你應該得到一個相當有效的執行計劃，比如：

優化器確實應該選擇該計劃形狀（或者可能是並行版本），但如果不是，則可能需要一個或多個提示。一個極端的例子：

SELECT t.*
FROM
(
   SELECT t.id, t.payment_date
   FROM transactions t
       WITH (FORCESEEK(email_idx(customer_email,payment_date)))
   WHERE
       t.customer_email = @customerEmail
       AND t.payment_date >= @startDate
       AND t.payment_date < @endDate
   UNION
   SELECT t.id, t.payment_date
   FROM transactions t
       WITH (FORCESEEK(msisdn_idx(customer_msisdn,payment_date)))
   WHERE 
       t.customer_msisdn = @customerMsisdn
       AND t.payment_date >= @startDate
       AND t.payment_date < @endDate
) AS filtered_transactions
JOIN dbo.transactions AS t
   ON t.id = filtered_transactions.id
ORDER BY 
   filtered_transactions.payment_date
OPTION (FORCE ORDER, LOOP JOIN, MERGE UNION);

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