如果我的表上的所有非聚集索引都是過濾索引怎麼辦？

我有一張經常使用的桌子。該表有 370,370 條記錄。

當我執行以下選擇時：

select  [ImageSize] , the_num = count(*)
from  [dbo].[ProductImages] 
group by [ImageSize] 
order by [ImageSize] 

我得到這個結果：

基本上，該表中只有 5 個不同的 imageSize 對象。

這是目前表定義：

IF OBJECT_ID('[dbo].[ProductImages]') IS NOT NULL 
DROP TABLE [dbo].[ProductImages] 
GO

CREATE TABLE [dbo].[ProductImages] ( 
[ProductImageID]  INT              IDENTITY(1,1)   NOT NULL,
[SeasonItemID]    VARCHAR(5)                       NOT NULL,
[Language]        SMALLINT                         NOT NULL,
[Tier1]           VARCHAR(10)                      NOT NULL,
[Tier2]           VARCHAR(10)                      NOT NULL,
[Gender]          CHAR(1)                          NOT NULL,
[SortOrder]       SMALLINT                         NOT NULL,
[ImageFormat]     VARCHAR(20)                      NOT NULL,
[ImageSize]       VARCHAR(10)                      NOT NULL,
[ImageWidth]      SMALLINT                         NOT NULL,
[ImageHeight]     SMALLINT                         NOT NULL,
[BodenDomainURL]  NVARCHAR(40)                     NOT NULL,
[BodenImageURL]   NVARCHAR(210)                    NOT NULL,
CONSTRAINT   [PK_ProductImages]  
PRIMARY KEY NONCLUSTERED ([SeasonItemID] asc, 
                         [Language] asc, [Tier1] asc, [Tier2] asc, [Gender] asc, 
                         [SortOrder] asc, [ImageFormat] asc, 
                         [ImageSize] asc))

GO

這是目前 inexes 的定義

• 1 個聚集索引
• 4 個非聚集索引
• 1 個過濾索引
• 和上面定義的非聚集主鍵

 CREATE CLUSTERED INDEX 
 [CIX_ProductImages__ProductImageID] 
            ON [dbo].[ProductImages] ([ProductImageID] asc)

 CREATE NONCLUSTERED INDEX
 [IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL] 
            ON [dbo].[ProductImages] 
     ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, 
     [ImageSize] asc, [ImageFormat] asc, 
     [BodenImageURL] asc)
            INCLUDE ([BodenDomainURL])

 CREATE NONCLUSTERED INDEX
 [IX_ProductImages_ImageFormat_ImageSize_INCL] 
     ON [dbo].[ProductImages] 
     ([ImageFormat] asc, [ImageSize] asc)
            INCLUDE ([SeasonItemID], [Language],
      [Tier1], [Tier2], [BodenDomainURL], 
     [BodenImageURL])


 CREATE NONCLUSTERED INDEX 
 [IX_ProductImages_Language_Tier1_Tier2_ImageFormat_INCL] 
     ON [dbo].[ProductImages] 
     ([Language] asc, [ImageSize] asc, [Tier1] asc, [Tier2] asc, 
     [ImageFormat] asc)
            INCLUDE ([ProductImageID], [BodenImageURL])

這是一個過濾索引：

   CREATE NONCLUSTERED INDEX 
   [IDX_ProdImages_GetProductListingPageDenormalisedData] 
       ON [dbo].[ProductImages] 
       ([SeasonItemID] asc, [Tier1] asc, [Tier2] asc, 
       [ImageFormat] asc, [ImageWidth] asc, [ImageHeight] asc)
              INCLUDE ([BodenImageURL])
              WHERE ([ImageWidth]=(195) 
       AND [ImageHeight]=(252) 
       AND [ImageFormat]='ProductMain')
            

我可以通過根據 imageSize 將上面的 4 個非聚集索引更改為 20 個過濾索引來獲得顯著收益嗎？

大多數查詢使用 ImageSize 作為參數之一。它甚至在聚集索引中。

我可以修改索引和表結構。

該表主要用於閱讀。一天只有 1 次寫入，通常不會影響很多記錄。

顯著的收益？不，我不應該這樣想。

SQL Server 的索引是 B 樹。這些具有等級性質。為了讀取一行，伺服器從索引的根節點開始，然後逐步通過一個或多個中間節點，然後通過葉節點讀取行的值。（細節在聚集索引和非聚集索引以及堆之間有所不同。）速度限制因素通常是索引中的級別數，因此，頁面數（每個索引節點是它自己的頁面）有被閱讀。這又由扇出控制，即高級頁面可以引用的低級頁面的數量，這本身由密鑰的總大小決定。讓我們做一些手波數學。

索引 IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL 定義為

Column          Definition     Effective bytes  Note
------------    -------------  ---------------  -----------------------------------
SeasonItemID    VARCHAR(5)                   5  Use the full size
Tier1           VARCHAR(10)                  5  Assume 50% of defined size
Tier2           VARCHAR(10)                  5  Assume 50% of defined size
ImageSize       VARCHAR(10)                  7  Minimum from given data
ImageFormat     VARCHAR(20)                 10  Assume 50% of defined size
BodenImageURL   NVARCHAR(210)              210  Assume 50% of defined size, 2 bytes per character.
                                         ====
Total                                      242 bytes

我將忽略包含的扇出列，因為它僅存在於葉節點中。由於每頁大約有 8060 個可用字節，因此每頁大約有 8060 / 242 = 33 行。所以一級索引最多可以引用 33 行，二級索引可以引用 33 * 33 = 1,089 行，三級索引可以引用 35,937 行，四級索引可以引用 1,185,921 行。考慮到每個圖片大小的 74,000 行和總共 370,000 行的計數，您將需要一個四級索引1用於過濾和非過濾索引。引用的頁數不會減少，因此執行時間也不會改善。

這些數字是一個下限，因為包含的列和聚集索引的列將在非聚集索引的葉頁中佔用更多空間。

但是，添加這些索引會帶來風險。一是優化器在使用它們時可能會很挑剔。另一個是引入新的大小（數據更改）將需要再定義五個索引（DDL 更改）。這是不受歡迎的維護成本，但如果不執行它，新大小的圖像實際上將被取消索引。附加對象會使模式更改變得複雜。如果手動編寫腳本，索引維護會稍微複雜一些。上層索引的成本將略微增加數據庫大小，並隨之增加備份和恢復時間。

1有關實際索引使用的值，請參閱 sys.dm_db_index_physical_stats.index_depth ( docs )。

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