為什麼添加計算列會阻止謂詞下推？

我有一個奇怪的情況，我不太明白。

我有一張這樣的桌子：

CREATE TABLE dbo.cc_demo
    (
        id INT IDENTITY PRIMARY KEY,
        up_action INT,
        down_action INT,
        last_action_date DATETIME
    );

INSERT dbo.cc_demo ( up_action, down_action, last_action_date )
SELECT TOP 5000000
      nums.num % 500000, nums.num % 500000, DATEADD(MINUTE, nums.num, GETDATE())
FROM
      (   SELECT     ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )) AS num
          FROM       master..spt_values AS sv
          CROSS JOIN master..spt_values AS sv2 ) AS nums;

如果我執行這個查詢，計劃只是一個正常的聚集索引掃描。

SELECT *
FROM   dbo.cc_demo AS cd
WHERE  cd.last_action_date >= '20270601'
AND    cd.last_action_date < '20270901';

但是，如果我添加一個計算列並為其編制索引，我的計劃就會發生最壞的情況。

ALTER TABLE dbo.cc_demo
   ADD total_actions AS up_action + down_action;

CREATE INDEX ix_total_actions ON dbo.cc_demo (total_actions);

現在它掃描聚集索引，然後過濾掉東西！

它要求一個索引，這會將計劃更改回我所期望的，但是為什麼我必須添加這個呢？

CREATE NONCLUSTERED INDEX [WORLDSTAR]
   ON dbo.cc_demo ( last_action_date )
   INCLUDE ( up_action, down_action, total_actions );

這似乎不合邏輯。

表現：

預計算列：

Table 'cc_demo'. Scan count 1, logical reads 17990, 

SQL Server Execution Times:
  CPU time = 234 ms,  elapsed time = 224 ms.

後計算列 + 索引：

Table 'cc_demo'. Scan count 1, logical reads 17990

SQL Server Execution Times:
  CPU time = 594 ms,  elapsed time = 591 ms.

這是細分：

• 當我添加計算列時，計劃不會改變
• 當我索引計算列時，它確實
• 我還不太擔心性能
• 我只是好奇為什麼在我索引計算列後計劃會這樣改變

我只是好奇為什麼在我索引計算列後計劃會這樣改變

真正重要的不是索引計算列。任何額外的非覆蓋索引都可以產生相同的效果（只要表格佔用一頁以上）。僅存在一個聚集索引（或完全覆蓋的非聚集索引）時，優化器可以生成一個TRIVIAL計劃，過濾條件完全下推。

當需要做出重要的訪問方法選擇時，查詢會進行基於成本的優化。如您所料，基於成本的優化包含更複雜和更強大的索引匹配規則，但是由於計算列擴展和投影導致的多個計算標量的存在可以防止謂詞（過濾器）被推到執行計劃中，當計算列不是PERSISTED。

SQL Server 嘗試在編譯過程的早期將謂詞（過濾器）推送到邏輯查詢樹的盡可能遠的位置，但是當涉及非持久計算列時，它可能會非常保守。在基於成本的優化過程中，未提前下推的過濾器不太可能進一步下推。

簡化展示

DROP TABLE IF EXISTS #CC;

CREATE TABLE #CC
(
   id integer IDENTITY PRIMARY KEY,
   c0 integer NULL,
   c1 integer NULL,
   c2 integer NULL,
   c3 AS c1 + c2
);

-- Just enough rows to fill more than one page
-- (so not all data access is trivial)
INSERT #CC 
   (c0, c1, c2)
SELECT 
   SV.number, SV.number, SV.number
FROM master.dbo.spt_values AS SV
WHERE 
   SV.[type] = N'P'
   AND SV.number BETWEEN 1 AND 324;

-- Trivial plan (only a clustered index to choose from)
SELECT * FROM #CC AS C WHERE C.c0 >= 1;

-- Add a non-covering index *not* on the computed column
CREATE INDEX ic2 ON #CC (c2);

-- Filter not pushed
SELECT * FROM #CC AS C WHERE C.c0 >= 1;

解決方法

在您的範例中，有幾種方法可以解決此限制：

1. 不要投影total_actions柱子。如果不需要該列，則推導其值的計算不會妨礙謂詞推送。
2. 製作計算列PERSISTED。這允許優化器考慮從基表中讀取持久值的計劃，從而避免計算。
3. 使用提示指定聚集索引，例如WITH (INDEX(1))。這允許一個簡單的計劃，因為它消除了訪問方法選擇的問題。
4. (INDEX(ix_total_actions))使用提示指定計算列的索引。雖然沒有覆蓋（所以不是簡單的計劃），這個提示還允許列來自儲存而不是被計算。索引不包括last_action_date，因此謂詞應用於 Key Lookup。因此，這個計劃可能效率很低。
5. …ETC。

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