詳解SQL Server的聚焦過濾索引

詳解SQL Server的聚焦過濾索引。本站提示廣大學習愛好者：（詳解SQL Server的聚焦過濾索引）文章只能為提供參考，不一定能成為您想要的結果。以下是詳解SQL Server的聚焦過濾索引正文

---

前言

這一節我們還是持續講講索引知識，後面我們聚集索引、非聚集索引以及掩蓋索引等，在這其中還有一個過濾索引，經過索引過濾我們也能進步查詢功能，冗長的內容，深化的了解。

過濾索引，在查詢條件上創立非聚集索引（1）

過濾索引是SQL 2008的新特性，被使用在表中的局部行，所以應用過濾索引可以進步查詢，絕對於全表掃描它能增加索引維護和索引存儲的代價。當我們在索引上使用WHERE條件時就是過濾索引。也就是滿足如下格式：

CREATE NONCLUSTERED INDEX <index name>
ON <table> (<columns>)
WHERE <criteria>;
GO

上面我們來看一個復雜的查詢

USE AdventureWorks2012
GO
SELECT SalesOrderDetailID, UnitPrice
FROM Sales.SalesOrderDetail
WHERE UnitPrice > 2000
GO

上述列中未樹立任何索引，當然除了SalesOrderDetailID默許創立的聚集索引，這種狀況下我們可以猜測到其執行的查詢方案必定是主鍵創立的聚集索引掃描，如下

上述我們曾經說過此時未在查詢條件上創立索引，所以此時必定走的是主鍵創立的聚集索引，接上去我們首先在UnitPrice列上創立非聚集索引來進步查詢功能，

CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_UnitPrice
ON Sales.SalesOrderDetail(UnitPrice)

此時我們再來比擬二者查詢開支

USE AdventureWorks2012
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT SalesOrderDetailID, UnitPrice
FROM AdventureWorks2012.Sales.SalesOrderDetail WITH(INDEX([PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]))
WHERE UnitPrice > 2000
GO
SELECT SalesOrderDetailID, UnitPrice
FROM Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_UnitPrice]))
WHERE UnitPrice > 2000

此時在查詢條件上樹立了非聚集索引之後，查詢開支提升的十分分明，提升到達了90%以上，由於非聚集索引也會援用了主鍵創立的聚集索引，所以這個時分不會招致Bookmark Lookup或許Key Lookup查找。接上去我們我們再添加一個帶有條件的非聚集索引即過濾索引

CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPrice
ON Sales.SalesOrderDetail(UnitPrice)
WHERE UnitPrice > 1000

此時我們再來看看創立了過濾索引之後和之前非聚集索引功能開支差別：

USE AdventureWorks2012
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SELECT SalesOrderDetailID, UnitPrice
FROM AdventureWorks2012.Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_UnitPrice]))
WHERE UnitPrice > 2000
SELECT SalesOrderDetailID, UnitPrice
FROM Sales.SalesOrderDetail WITH(INDEX([idxwhere_SalesOrderDetail_UnitPrice]))
WHERE UnitPrice > 2000

此時我們知道創立的非聚集過濾索引與傳統創立的非聚集索引相比，我們的查詢接近增加了一半。

獨一過濾索引

獨一過濾索引關於一切列必需獨一且不為空（只允許一個NULL存在）也是十分好的處理方案，所以此時在創立獨一過濾索引時需求將NULL值除外，比方如下：

CREATE UNIQUE NONCLUSTERED INDEX uq_fix_Customers_Email
ON Customers(Email)
WHERE Email IS NOT NULL
GO

過濾索引結合INCLUDE

當我們再添加一個額定列時，運用默許主鍵創立的聚集索引時，此時會走聚集索引掃描，然後我們在查詢條件上創立一個過濾索引，我們強迫運用這個過濾索引時，此時由於添加額定列，會招致需求前往到基表中再去獲取數據，所以也就形成了Key Lookup查找，如下：

USE AdventureWorks2012
GO
SELECT SalesOrderDetailID, UnitPrice, UnitPriceDiscount
FROM Sales.SalesOrderDetail
WHERE UnitPrice > 2000
GO

此時我們需求用INCLUDE來包括額定列。

CREATE NONCLUSTERED INDEX [idx_SalesOrderDetail_UnitPrice] ON Sales.SalesOrderDetail(UnitPrice) INCLUDE(UnitPriceDiscount)

我們再創立一個過濾索引同時包括額定列

CREATE NONCLUSTERED INDEX [idxwhere_SalesOrderDetail_UnitPrice] ON Sales.SalesOrderDetail(UnitPrice) INCLUDE(UnitPriceDiscount)
WHERE UnitPrice > 2000

接上去再來執行比擬添加過濾索引和未添加過濾索引同時都包括了額定列的功能查詢差別。

SELECT SalesOrderDetailID, UnitPrice, UnitPriceDiscount
FROM AdventureWorks2012.Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_UnitPrice]))
WHERE UnitPrice > 2000 
SELECT SalesOrderDetailID, UnitPrice, UnitPriceDiscount
FROM Sales.SalesOrderDetail WITH(INDEX([idxwhere_SalesOrderDetail_UnitPrice]))
WHERE UnitPrice > 2000

此時功能用INCLUDE來包括額定列功能也失掉了一定的改善。

過濾索引，在主鍵上創立非聚集索引（2）

在第一個案列中，我們可以直接在查詢列上創立非聚集索引，由於其類型是數字類型，要是查詢條件是字符類型呢？首選如今我們先創立一個測試表

USE TSQL2012
GO
CREATE TABLE dbo.TestData 
(
  RowID    integer IDENTITY NOT NULL, 
  SomeValue  VARCHAR(max) NOT NULL,   
  StartDate  date NOT NULL,
  CONSTRAINT PK_Data_RowID
    PRIMARY KEY CLUSTERED (RowID)
);

添加10萬條測試數據

USE TSQL2012
GO
INSERT dbo.TestData WITH (TABLOCKX)
  (SomeValue, StartDate)
SELECT
  CAST(N.n AS VARCHAR(max)) + 'JeffckyWang',
  DATEADD(DAY, (N.n - 1) % 31, '20140101')
FROM dbo.Nums AS N
WHERE 
  N.n >= 1 
  AND N.n < 100001;

假如我們需求獲取表TestData中SomeValue = 'JeffckyWang'，此時我們想要在SomeValue上創立一個非聚集索引然後停止過濾，如下

USE TSQL2012
GO
CREATE NONCLUSTERED INDEX idx_noncls_somevalue
ON dbo.TestData(SomeValue)
WHERE SomeValue = 'JeffckyWang'

更新

SQL Server對創立索引大小無限制，最大是900字節，上述直接寫的VARCHAR(MAX)，所以會出錯，切記，切記。

此時我們在主鍵上創立非聚集索引，我們在主鍵RowID上創立一個過濾索引且SomeValue = 'JeffckyWang'，然後前往數據，如下：

CREATE NONCLUSTERED INDEX idxwhere_noncls_somevalue
ON dbo.TestData(RowID)
WHERE SomeValue = 'JeffckyWang'

上面我們來比照樹立過濾索引前後查詢方案後果：

USE TSQL2012
GO
SELECT RowID, SomeValue, StartDate 
FROM dbo.TestData WITH(INDEX([idx_pk_rowid]))
WHERE SomeValue = 'JeffckyWang'
SELECT RowID, SomeValue, StartDate 
FROM dbo.TestData WITH(INDEX([idxwhere_noncls_somevalue]))
WHERE SomeValue = 'JeffckyWang'

然後結合之前所學，移除Key Lookup，對創立的過濾索引停止INCLUDE。

CREATE NONCLUSTERED INDEX [idxwhere_noncls_somevalue] ON dbo.TestData(RowID) INCLUDE(SomeValue,StartDate) 
WHERE SomeValue = 'JeffckyWang'

從這裡看出，無論是對查詢條件創立過濾索引還是對主鍵創立過濾索引，我們都可以經過結合之前所學來進步查詢功能。

我們從掃尾就不斷在講創立過濾索引，那麼創立過濾索引優點的條件究竟是什麼？

（1）只能經過非聚集索引停止創立。

（2）假如在視圖上創立過濾索引，此視圖必需是耐久化視圖。

（3）不能在全文索引上創立過濾索引。

過濾索引的優點

（1）增加索引維護本錢：關於增、刪、改等操作不需求代價沒有那麼昂貴，由於一個過濾索引的重建不需求耗時太多時間。

（2）增加存儲本錢：過濾索引的存儲占用空間很小。

（3）更准確的統計：經過在WHERE條件上創立過濾索引比全表統計後果愈加准確。

（4）優化查詢功能：經過查詢方案可以看出其高效性。

講到這裡為止，不斷陳說的是過濾索引的益處和優點，曾經將其捧上天了，其實其缺陷也是不言而喻。

過濾索引缺陷

最大的缺陷則是查詢條件的限制。其查詢條件僅限於

<filter_predicate> ::=  
  <conjunct> [ AND <conjunct> ] 
<conjunct> ::= 
  <disjunct> | <comparison>  
<disjunct> ::= 
    column_name IN (constant ,...n)

過濾條件僅限於AND、|、IN。比擬條件僅限於 { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }，所以如下應用LIKE不行

CREATE NONCLUSTERED INDEX [idxwhere_noncls_somevalue] ON dbo.TestData(RowID) INCLUDE(SomeValue,StartDate) 
WHERE SomeValue LIKE 'JeffckyWang%'

如下可以

USE AdventureWorks2012
GO
CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ModifiedDate
ON Sales.SalesOrderDetail(ModifiedDate)
WHERE ModifiedDate >= '2008-01-01' AND ModifiedDate <= '2008-01-07'
GO

如下卻不行

CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ModifiedDate
ON Sales.SalesOrderDetail(ModifiedDate)
WHERE ModifiedDate = GETDATE()
GO

變量對過濾索引影響

上述我們創立過濾索引在查詢條件上直接定義的字符串，如下：

CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPrice
ON Sales.SalesOrderDetail(UnitPrice)
WHERE UnitPrice > 1000

假如定義的是變量，應用變量來停止比擬會如何呢？首先我們創立一個過濾索引

CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ProductID 
ON Sales.SalesOrderDetail (ProductID)
WHERE ProductID = 870

應用變量來和查詢條件比擬，強迫運用過濾索引（默許狀況下走聚集索引）

USE AdventureWorks2012
GO
DECLARE @ProductID INT 
SET @ProductID = 870 
SELECT ProductID 
FROM Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_ProductID]))
WHERE ProductID = @ProductID

檢查查詢執行方案後果卻出錯了，此時我們需求添加OPTION重新編譯，如下：

USE AdventureWorks2012
GO
DECLARE @ProductID INT 
SET @ProductID = 870 
SELECT ProductID 
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
OPTION(RECOMPILE)

上述應用變量來查詢最後經過OPTION重新編譯在SQL Server 2012中測試好使，至於其他版本未知，參考材料【The Pains of Filtered Indexes】。

總結

本節我們學習了經過過濾索引來進步查詢功能，同時也給出了其不同的場景以及其運用優點和分明的缺陷。冗長的內容，深化的了解，我們下節再會，good night。

以上就是本文的全部內容，希望本文的內容對大家的學習或許任務能帶來一定的協助，假如有疑問大家可以留言交流，同時也希望多多支持！