持久計算列上的索引需要鍵查找來獲取計算表達式中的列

我在一個表上有一個持久計算列，它只是由連接列組成，例如

CREATE TABLE dbo.T 
(   
   ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
   A VARCHAR(20) NOT NULL,
   B VARCHAR(20) NOT NULL,
   C VARCHAR(20) NOT NULL,
   D DATE NULL,
   E VARCHAR(20) NULL,
   Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL 
);

這Comp不是唯一的，並且 D 是 的每個組合的有效起始日期A, B, C，因此我使用以下查詢來獲取每個組合的結束日期A, B, C（基本上是相同 Comp 值的下一個開始日期）：

SELECT  t1.ID,
       t1.Comp,
       t1.D,
       D2 = (  SELECT  TOP 1 t2.D
               FROM    dbo.T t2
               WHERE   t2.Comp = t1.Comp
               AND     t2.D > t1.D
               ORDER BY t2.D
           )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

然後我在計算列中添加了一個索引來幫助這個查詢（以及其他查詢）：

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;

然而，查詢計劃讓我感到驚訝。我會認為，因為我有一個 where 子句說明了這一點，D IS NOT NULL並且我正在排序Comp，並且沒有引用索引之外的任何列，所以計算列上的索引可用於掃描 t1 和 t2，但我看到了一個聚集索引掃描。

所以我強制使用這個索引來看看它是否產生了一個更好的計劃：

SELECT  t1.ID,
       t1.Comp,
       t1.D,
       D2 = (  SELECT  TOP 1 t2.D
               FROM    dbo.T t2
               WHERE   t2.Comp = t1.Comp
               AND     t2.D > t1.D
               ORDER BY t2.D
           )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

哪個給出了這個計劃

這表明正在使用 Key 查找，其詳細資訊是：

現在，根據 SQL-Server 文件：

如果在 CREATE TABLE 或 ALTER TABLE 語句中將該列標記為 PERSISTED，則可以在使用確定性但不精確的表達式定義的計算列上創建索引。這意味著數據庫引擎將計算值儲存在表中，並在計算列所依賴的任何其他列更新時更新它們。數據庫引擎在為列創建索引以及在查詢中引用索引時使用這些持久值。當數據庫引擎無法準確證明返回計算列表達式的函式（尤其是在 .NET Framework 中創建的 CLR 函式）是否具有確定性和精確性時，此選項使您能夠在計算列上創建索引。

因此，如果，正如文件所說*“數據庫引擎將計算值儲存在表中”*，並且該值也儲存在我的索引中，為什麼在未引用 A、B 和 C 時需要進行鍵查找來獲取它們查詢呢？我假設它們被用來計算 Comp，但是為什麼呢？另外，為什麼查詢可以在 上使用索引t2，但不能在 上使用t1？

SQL Fiddle 上的查詢和 DDL

注意我已經標記了 SQL Server 2008，因為這是我的主要問題所在的版本，但我在 2012 年也得到了相同的行為。

為什麼在查詢中根本沒有引用 A、B 和 C 時需要使用 Key Lookup 來獲取它們？我假設它們被用來計算 Comp，但是為什麼呢？

列在查詢計劃中A, B, and C 被引用——它們被 seek on 使用T2。

另外，為什麼查詢可以使用 t2 上的索引，而不能使用 t1 上的索引？

優化器決定掃描聚集索引比掃描過濾的非聚集索引然後執行查找以檢索列 A、B 和 C 的值更便宜。

解釋

真正的問題是為什麼優化器覺得需要為索引搜尋檢索 A、B 和 C。我們希望它Comp使用非聚集索引掃描讀取列，然後在同一索引（別名 T2）上執行查找以定位 Top 1 記錄。

查詢優化器在優化開始之前擴展計算列引用，以便有機會評估各種查詢計劃的成本。對於某些查詢，擴展計算列的定義允許優化器找到更有效的計劃。

當優化器遇到相關子查詢時，它會嘗試將其“展開”到它發現更容易推理的形式。如果它找不到更有效的簡化，它會求助於將相關子查詢重寫為應用（相關連接）：

碰巧的是，這種應用展開將邏輯查詢樹放入了一種不能很好地與項目規範化配合使用的形式（稍後階段，它看起來將通用表達式與計算列相匹配，等等）。

在您的情況下，查詢的編寫方式與優化器的內部細節互動，因此擴展表達式定義不匹配回計算列，並且您最終會得到一個引用列A, B, and C而不是計算列的查找，Comp. 這是根本原因。

解決方法

解決此副作用的一個想法是將查詢編寫為手動應用：

SELECT
   T1.ID,
   T1.Comp,
   T1.D,
   CA.D2
FROM dbo.T AS T1
CROSS APPLY
(  
   SELECT TOP (1)
       D2 = T2.D
   FROM dbo.T AS T2
   WHERE
       T2.Comp = T1.Comp
       AND T2.D > T1.D
   ORDER BY
       T2.D ASC
) AS CA
WHERE
   T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
   T1.Comp;

不幸的是，這個查詢也不會像我們希望的那樣使用過濾後的索引。apply 內列上的不等式測試Drejects NULLs，因此明顯冗餘的謂詞WHERE T1.D IS NOT NULL被優化掉。

如果沒有該顯式謂詞，過濾索引匹配邏輯將決定它不能使用過濾索引。有很多方法可以解決第二個副作用，但最簡單的可能是將交叉應用更改為外部應用（反映之前在相關子查詢上執行的優化器重寫的邏輯）：

SELECT
   T1.ID,
   T1.Comp,
   T1.D,
   CA.D2
FROM dbo.T AS T1
OUTER APPLY
(  
   SELECT TOP (1)
       D2 = T2.D
   FROM dbo.T AS T2
   WHERE
       T2.Comp = T1.Comp
       AND T2.D > T1.D
   ORDER BY
       T2.D ASC
) AS CA
WHERE
   T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
   T1.Comp;

現在優化器不需要使用 apply rewrite 本身（因此計算的列匹配按預期工作）並且謂詞也沒有被優化掉，所以過濾後的索引可以用於兩個數據訪問操作，並且 seek 使用Comp列兩邊：

這通常比INCLUDEd在過濾索引中添加 A、B 和 C 作為列更可取，因為它解決了問題的根本原因，並且不需要不必要地擴大索引。

持久化計算列

作為旁注，PERSISTED如果您不介意在CHECK約束中重複其定義，則不必將計算列標記為：

CREATE TABLE dbo.T 
(   
   ID integer IDENTITY(1, 1) NOT NULL,
   A varchar(20) NOT NULL,
   B varchar(20) NOT NULL,
   C varchar(20) NOT NULL,
   D date NULL,
   E varchar(20) NULL,
   Comp AS A + '-' + B + '-' + C,

   CONSTRAINT CK_T_Comp_NotNull
       CHECK (A + '-' + B + '-' + C IS NOT NULL),

   CONSTRAINT PK_T_ID 
       PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D) 
WHERE D IS NOT NULL;

PERSISTED僅當您想使用NOT NULL約束或在約束中直接引用Comp列（而不是重複其定義）時，才需要計算列CHECK。

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