[SQL SERVER][Memo]篩選索引

SQL2008 在索引類型中新增了一個名為篩選索引的新成員，線上叢書說明篩選索引是最佳化的非叢集索引，

特別適合從已定義的資料子集進行查詢篩選。

簡單來說就是在建立非叢集索引時可加上簡單的 WHERE 子句來提高選擇性，

竟而提升查詢效能、降低索引儲存和維護成本..等，不多說，馬上來實測看看效果如何。

 

參考線上叢書情境如下:

當資料行中的值大部分都是 NULL 且查詢只會從非 NULL 值進行選取時(疏鬆資料行)，

您可以針對非 NULL 的資料列建立篩選索引。

所產生的索引比在相同的索引鍵資料行上定義的全資料表非叢集索引還小，維護成本也比較低。

 

CANCEL_REASON資料行資料內容如下:

is null筆數:190748。is not null筆數:9252。

 

查詢 Statement:

SELECT t1.BILL_NO,t1.CANCEL_REASON,t1.CANCEL_DATE FROM dbo.BILL_t t1 

WHERE t1.CANCEL_REASON IS NOT NULL

 

建立非叢集索引和篩選索引

-- Creating a non clustered index on CANCEL_REASON column

CREATE NONCLUSTERED INDEX nidx_CANCEL_REASON

ON BILL_t(CANCEL_REASON) 

INCLUDE(BILL_NO, CANCEL_DATE) --Including remaining columns in the index

GO

-- Creating a non clustered Filetered index on CANCEL_REASON column

CREATE NONCLUSTERED INDEX fnidx_CANCEL_REASON

ON BILL_t(CANCEL_REASON)

INCLUDE(BILL_NO, CANCEL_DATE) --Including remaining columns in the index

WHERE CANCEL_REASON IS NOT NULL --filter criteria for the index

GO

 

查看索引相關資訊

SELECT t2.index_id, t2.name, t2.type_desc, t1.reserved_page_count, 

t1.used_page_count,t1.row_count,t2.filter_definition 

FROM sys.dm_db_partition_stats t1

INNER JOIN sys.indexes t2 ON t1.object_id = t2.object_id AND t1.index_id = t2.index_id

WHERE t1.object_id = OBJECT_ID('BILL_t')

可以看到篩選索引儲存成本比一般非叢集索引低很多，相對在維護索引統計資料也很省時。

 

使用一般非叢集執行計畫、IO和經過時間

使用一般非叢集索引整體執行計畫成本:0.0845703。

 

 

使用篩選索引執行計畫、IO和經過時間

先清除相關Buffer。

 

 

使用一般非叢集索引整體執行計畫成本:0.0164222。

 

結果比較表

 

整體執行計畫成本相差約5倍。