使用 ORDER BY 從不同表中選擇 TOP 1 時如何設置索引視圖

我正在努力在以下場景中設置索引視圖，以便在沒有兩次聚集索引掃描的情況下執行以下查詢。每當我為此查詢創建索引視圖然後使用它時，它似乎忽略了我放在它上面的任何索引：

   -- +++ THE QUERY THAT I WANT TO IMPROVE PERFORMANCE-WISE +++

   SELECT TOP 1 *
   FROM    dbo.TB_test1 t1
           INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1
   ORDER BY t1.somethingelse1
          ,t2.somethingelse2;


   GO

表設置如下：

• 兩張桌子
• 它們通過上面的查詢通過內部連接連接
• 並通過上面的查詢按第一個表中的列排序，然後從第二個表中的列排序；僅選擇 TOP 1
• （在下面的腳本中還有一些行來生成測試數據，以防萬一它有助於重現問題）

-- +++ TABLE SETUP +++

CREATE TABLE [dbo].[TB_test1]
   (
    [PK_ID1] [INT] IDENTITY(1, 1)  NOT NULL
   ,[something1] VARCHAR(40) NOT NULL
   ,[somethingelse1] BIGINT NOT NULL
       CONSTRAINT [PK_TB_test1] PRIMARY KEY CLUSTERED ( [PK_ID1] ASC )
   );

GO

create TABLE [dbo].[TB_test2]
   (
    [PK_ID2] [INT] IDENTITY(1, 1)  NOT NULL
   ,[FK_ID1] [INT] NOT NULL
   ,[something2] VARCHAR(40) NOT NULL
   ,[somethingelse2] BIGINT NOT NULL
       CONSTRAINT [PK_TB_test2] PRIMARY KEY CLUSTERED ( [PK_ID2] ASC )
   );

GO

ALTER TABLE [dbo].[TB_test2]  WITH CHECK ADD  CONSTRAINT [FK_TB_Test1] FOREIGN KEY([FK_ID1])
REFERENCES [dbo].[TB_test1] ([PK_ID1])
GO

ALTER TABLE [dbo].[TB_test2] CHECK CONSTRAINT [FK_TB_Test1]

GO


-- +++ TABLE DATA GENERATION +++

-- this might not be the quickest way, but it's only to set up test data

INSERT INTO dbo.TB_test1
       ( something1, somethingelse1 )
VALUES  ( CONVERT(VARCHAR(40), NEWID())  -- something1 - varchar(40)
         ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse1 - bigint
         )

GO 100000

RAISERROR( 'Finished setting up dbo.TB_test1', 0, 1) WITH NOWAIT    

GO    

INSERT INTO dbo.TB_test2
       ( FK_ID1, something2, somethingelse2 )
VALUES  ( ISNULL(ABS(CHECKSUM(NewId())) % ((SELECT MAX(PK_ID1) FROM dbo.TB_test1) - 1), 0) + 1 -- FK_ID1 - int
         ,CONVERT(VARCHAR(40), NEWID())  -- something2 - varchar(40)
         ,ISNULL(ABS(CHECKSUM(NewId())) % 92233720368547758078, 1)   -- somethingelse2 - bigint
         )

GO 100000

RAISERROR( 'Finished setting up dbo.TB_test2', 0, 1) WITH NOWAIT          

GO

索引視圖可能應該定義如下，生成的 TOP 1 查詢如下。但是我需要什麼索引才能使這個查詢比沒有索引視圖的查詢執行得更好？

   CREATE VIEW VI_test
   WITH SCHEMABINDING
   AS
       SELECT  t1.PK_ID1
              ,t1.something1
              ,t1.somethingelse1
              ,t2.PK_ID2
              ,t2.FK_ID1
              ,t2.something2
              ,t2.somethingelse2
       FROM    dbo.TB_test1 t1
               INNER JOIN dbo.TB_test2 t2 ON t1.PK_ID1 = t2.FK_ID1


   GO


   SELECT TOP 1 * FROM dbo.VI_test ORDER BY somethingelse1,somethingelse2


   GO

它似乎忽略了我放在上面的任何索引

除非您使用的是 SQL Server Enterprise Edition（或等效的試用版和開發版），否則您需要使用WITH (NOEXPAND)視圖引用才能使用它。事實上，即使您使用的是 Enterprise，也有充分的理由使用該提示。

如果沒有提示，查詢優化器（在企業版中）可能會在使用物化視圖或訪問基表之間做出基於成本的選擇。在視圖與基表一樣大的情況下，此計算可能有利於基表。

另一個有趣的地方是，在沒有NOEXPAND提示的情況下，視圖引用總是在優化開始之前擴展到基本查詢。隨著優化的進行，優化器可能會也可能不會將擴展定義匹配回物化視圖，這取決於之前的優化活動。您的簡單查詢幾乎肯定不是這種情況，但為了完整起見，我提到它。

因此，使用NOEXPAND表提示是您的主要選擇，但您也可以考慮僅實現基表鍵和視圖中排序所需的列。在組合鍵列上創建一個唯一的聚集索引，然後在排序列上創建一個單獨的非聚集索引。

這將減小物化視圖的大小，並限制為使視圖與基表保持同步而必須進行的自動更新次數。然後可以編寫查詢以從視圖中按所需順序獲取前 1 個鍵（理想情況下使用NOEXPAND），然後連接回基表以使用視圖中的鍵獲取任何剩餘的列。

另一種變體是將視圖聚集在排序列和表鍵上，然後編寫查詢以使用鍵從基表中手動獲取非視圖列。最適合您的選擇取決於更廣泛的背景。一個很好的決定方法是用真實的數據和工作量來測試它。

基本解決方案

CREATE VIEW VI_test
WITH SCHEMABINDING
AS
   SELECT
       t1.PK_ID1,
       t1.something1,
       t1.somethingelse1,
       t2.PK_ID2,
       t2.FK_ID1,
       t2.something2,
       t2.somethingelse2
   FROM dbo.TB_test1 t1
   INNER JOIN dbo.TB_test2 t2 
       ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Brute force unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
   (somethingelse1, somethingelse2, PK_ID1, PK_ID2);
GO
SELECT TOP (1) * 
FROM dbo.VI_test WITH (NOEXPAND)
ORDER BY somethingelse1,somethingelse2;

執行計劃：

使用非聚集索引

-- Minimal unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
   (PK_ID1, PK_ID2)
WITH (DROP_EXISTING = ON);
GO
-- Nonclustered index for ordering
CREATE NONCLUSTERED INDEX ix 
ON dbo.VI_test (somethingelse1, somethingelse2);

執行計劃：

此計劃中有一個查找，但它僅用於獲取單行。

最小索引視圖

ALTER VIEW VI_test
WITH SCHEMABINDING
AS
   SELECT
       t1.PK_ID1,
       t2.PK_ID2,
       t1.somethingelse1,
       t2.somethingelse2
   FROM dbo.TB_test1 t1
   INNER JOIN dbo.TB_test2 t2 
       ON t1.PK_ID1 = t2.FK_ID1;
GO
-- Unique clustered index
CREATE UNIQUE CLUSTERED INDEX cuq 
ON dbo.VI_test 
   (somethingelse1, somethingelse2, PK_ID1, PK_ID2);

詢問：

SELECT TOP (1)
   V.PK_ID1,
   TT1.something1,
   V.somethingelse1,
   V.PK_ID2,
   TT2.FK_ID1,
   TT2.something2,
   V.somethingelse2
FROM dbo.VI_test AS V WITH (NOEXPAND)
JOIN dbo.TB_test1 AS TT1 ON TT1.PK_ID1 = V.PK_ID1
JOIN dbo.TB_test2 AS TT2 ON TT2.PK_ID2 = V.PK_ID2
ORDER BY somethingelse1,somethingelse2;

執行計劃：

這顯示了正在檢索的表鍵（按順序從視圖聚集索引中獲取單行），然後在基表上進行兩次單行查找以獲取剩餘的列。

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