前言
這一節(jié)我們還是繼續(xù)講講索引知識(shí)，前面我們聚集索引、非聚集索引以及覆蓋索引等，在這其中還有一個(gè)過濾索引，通過索引過濾我們也能提高查詢性能，簡(jiǎn)短的內(nèi)容，深入的理解。
過濾索引，在查詢條件上創(chuàng)建非聚集索引（1）
過濾索引是SQL 2008的新特性，被應(yīng)用在表中的部分行，所以利用過濾索引能夠提高查詢，相對(duì)于全表掃描它能減少索引維護(hù)和索引存儲(chǔ)的代價(jià)。當(dāng)我們?cè)谒饕蠎?yīng)用WHERE條件時(shí)就是過濾索引。也就是滿足如下格式：
CREATE NONCLUSTERED INDEX index name>
ON table> (columns>)
WHERE criteria>;
GO
下面我們來看一個(gè)簡(jiǎn)單的查詢
USE AdventureWorks2012
GO
SELECT SalesOrderDetailID, UnitPrice
FROM Sales.SalesOrderDetail
WHERE UnitPrice > 2000
GO
上述列中未建立任何索引，當(dāng)然除了SalesOrderDetailID默認(rèn)創(chuàng)建的聚集索引，這種情況下我們能夠猜想到其執(zhí)行的查詢計(jì)劃必然是主鍵創(chuàng)建的聚集索引掃描，如下
上述我們已經(jīng)說過此時(shí)未在查詢條件上創(chuàng)建索引，所以此時(shí)必然走的是主鍵創(chuàng)建的聚集索引，接下來我們首先在UnitPrice列上創(chuàng)建非聚集索引來提高查詢性能，
CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_UnitPrice
ON Sales.SalesOrderDetail(UnitPrice)
此時(shí)我們?cè)賮肀容^二者查詢開銷
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
此時(shí)在查詢條件上建立了非聚集索引之后，查詢開銷提升的非常明顯，提升達(dá)到了90%以上，因?yàn)榉蔷奂饕矔?huì)引用了主鍵創(chuàng)建的聚集索引，所以這個(gè)時(shí)候不會(huì)導(dǎo)致Bookmark Lookup或者Key Lookup查找。接下來我們我們?cè)偬砑右粋€(gè)帶有條件的非聚集索引即過濾索引
CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPrice
ON Sales.SalesOrderDetail(UnitPrice)
WHERE UnitPrice > 1000
此時(shí)我們?cè)賮砜纯磩?chuàng)建了過濾索引之后和之前非聚集索引性能開銷差異：
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
此時(shí)我們知道創(chuàng)建的非聚集過濾索引與傳統(tǒng)創(chuàng)建的非聚集索引相比，我們的查詢接近減少了一半。
唯一過濾索引
唯一過濾索引對(duì)于所有列必須唯一且不為空（只允許一個(gè)NULL存在）也是非常好的解決方案，所以此時(shí)在創(chuàng)建唯一過濾索引時(shí)需要將NULL值除外，比如如下：
CREATE UNIQUE NONCLUSTERED INDEX uq_fix_Customers_Email
ON Customers(Email)
WHERE Email IS NOT NULL
GO
過濾索引結(jié)合INCLUDE
當(dāng)我們?cè)偬砑右粋€(gè)額外列時(shí)，使用默認(rèn)主鍵創(chuàng)建的聚集索引時(shí)，此時(shí)會(huì)走聚集索引掃描，然后我們?cè)诓樵儣l件上創(chuàng)建一個(gè)過濾索引，我們強(qiáng)制使用這個(gè)過濾索引時(shí)，此時(shí)由于添加額外列，會(huì)導(dǎo)致需要返回到基表中再去獲取數(shù)據(jù)，所以也就造成了Key Lookup查找，如下：
USE AdventureWorks2012
GO
SELECT SalesOrderDetailID, UnitPrice, UnitPriceDiscount
FROM Sales.SalesOrderDetail
WHERE UnitPrice > 2000
GO
此時(shí)我們需要用INCLUDE來包含額外列。
CREATE NONCLUSTERED INDEX [idx_SalesOrderDetail_UnitPrice] ON Sales.SalesOrderDetail(UnitPrice) INCLUDE(UnitPriceDiscount)
我們?cè)賱?chuàng)建一個(gè)過濾索引同時(shí)包括額外列
CREATE NONCLUSTERED INDEX [idxwhere_SalesOrderDetail_UnitPrice] ON Sales.SalesOrderDetail(UnitPrice) INCLUDE(UnitPriceDiscount)
WHERE UnitPrice > 2000
接下來再來執(zhí)行比較添加過濾索引和未添加過濾索引同時(shí)都包括了額外列的性能查詢差異。
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
此時(shí)性能用INCLUDE來包含額外列性能也得到了一定的改善。
過濾索引，在主鍵上創(chuàng)建非聚集索引（2）
在第一個(gè)案列中，我們可以直接在查詢列上創(chuàng)建非聚集索引，因?yàn)槠漕愋褪菙?shù)字類型，要是查詢條件是字符類型呢？首選現(xiàn)在我們先創(chuàng)建一個(gè)測(cè)試表
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萬條測(cè)試數(shù)據(jù)
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'，此時(shí)我們想要在SomeValue上創(chuàng)建一個(gè)非聚集索引然后進(jìn)行過濾，如下
USE TSQL2012
GO
CREATE NONCLUSTERED INDEX idx_noncls_somevalue
ON dbo.TestData(SomeValue)
WHERE SomeValue = 'JeffckyWang'
更新
SQL Server對(duì)創(chuàng)建索引大小有限制，最大是900字節(jié)，上述直接寫的VARCHAR(MAX)，所以會(huì)出錯(cuò)，切記，切記。
此時(shí)我們?cè)谥麈I上創(chuàng)建非聚集索引，我們?cè)谥麈IRowID上創(chuàng)建一個(gè)過濾索引且SomeValue = 'JeffckyWang'，然后返回?cái)?shù)據(jù)，如下：
CREATE NONCLUSTERED INDEX idxwhere_noncls_somevalue
ON dbo.TestData(RowID)
WHERE SomeValue = 'JeffckyWang'
下面我們來對(duì)比建立過濾索引前后查詢計(jì)劃結(jié)果：
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'
然后結(jié)合之前所學(xué)，移除Key Lookup，對(duì)創(chuàng)建的過濾索引進(jìn)行INCLUDE。
CREATE NONCLUSTERED INDEX [idxwhere_noncls_somevalue] ON dbo.TestData(RowID) INCLUDE(SomeValue,StartDate)
WHERE SomeValue = 'JeffckyWang'
從這里看出，無論是對(duì)查詢條件創(chuàng)建過濾索引還是對(duì)主鍵創(chuàng)建過濾索引，我們都可以通過結(jié)合之前所學(xué)來提高查詢性能。
我們從開頭就一直在講創(chuàng)建過濾索引，那么創(chuàng)建過濾索引優(yōu)點(diǎn)的條件到底是什么？
（1）只能通過非聚集索引進(jìn)行創(chuàng)建。
（2）如果在視圖上創(chuàng)建過濾索引，此視圖必須是持久化視圖。
（3）不能在全文索引上創(chuàng)建過濾索引。
過濾索引的優(yōu)點(diǎn)
（1）減少索引維護(hù)成本：對(duì)于增、刪、改等操作不需要代價(jià)沒有那么昂貴，因?yàn)橐粋€(gè)過濾索引的重建不需要耗時(shí)太多時(shí)間。
（2）減少存儲(chǔ)成本：過濾索引的存儲(chǔ)占用空間很小。
（3）更精確的統(tǒng)計(jì)：通過在WHERE條件上創(chuàng)建過濾索引比全表統(tǒng)計(jì)結(jié)果更加精確。
（4）優(yōu)化查詢性能：通過查詢計(jì)劃可以看出其高效性。
講到這里為止，一直陳述的是過濾索引的好處和優(yōu)點(diǎn)，已經(jīng)將其捧上天了，其實(shí)其缺點(diǎn)也是顯而易見。
過濾索引缺點(diǎn)
最大的缺點(diǎn)則是查詢條件的限制。其查詢條件僅限于
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
變量對(duì)過濾索引影響
上述我們創(chuàng)建過濾索引在查詢條件上直接定義的字符串，如下：
CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPrice
ON Sales.SalesOrderDetail(UnitPrice)
WHERE UnitPrice > 1000
如果定義的是變量，利用變量來進(jìn)行比較會(huì)如何呢？首先我們創(chuàng)建一個(gè)過濾索引
CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ProductID
ON Sales.SalesOrderDetail (ProductID)
WHERE ProductID = 870
利用變量來和查詢條件比較，強(qiáng)制使用過濾索引（默認(rèn)情況下走聚集索引）
USE AdventureWorks2012
GO
DECLARE @ProductID INT
SET @ProductID = 870
SELECT ProductID
FROM Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_ProductID]))
WHERE ProductID = @ProductID
查看查詢執(zhí)行計(jì)劃結(jié)果卻出錯(cuò)了，此時(shí)我們需要添加OPTION重新編譯，如下：
USE AdventureWorks2012
GO
DECLARE @ProductID INT
SET @ProductID = 870
SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID
OPTION(RECOMPILE)
上述利用變量來查詢最后通過OPTION重新編譯在SQL Server 2012中測(cè)試好使，至于其他版本未知，參考資料【The Pains of Filtered Indexes】。
總結(jié)
本節(jié)我們學(xué)習(xí)了通過過濾索引來提高查詢性能，同時(shí)也給出了其不同的場(chǎng)景以及其使用優(yōu)點(diǎn)和明顯的缺點(diǎn)。簡(jiǎn)短的內(nèi)容，深入的理解，我們下節(jié)再會(huì)，good night。
以上就是本文的全部?jī)?nèi)容，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，同時(shí)也希望多多支持腳本之家！
您可能感興趣的文章:- SQL Server 2005通用分頁存儲(chǔ)過程及多表聯(lián)接應(yīng)用
- SQL設(shè)置SQL Server最大連接數(shù)及查詢語句
- 解析SQL Server聚焦移除（Bookmark Lookup、RID Lookup、Key Lookup）
- 淺述SQL Server的聚焦強(qiáng)制索引查詢條件和Columnstore Index
- 淺析SQL Server的分頁方式 ISNULL與COALESCE性能比較
- 詳解SQL Server中的數(shù)據(jù)類型
- 淺析SQL Server的聚焦使用索引和查詢執(zhí)行計(jì)劃
- 淺析SQL Server 聚焦索引對(duì)非聚集索引的影響
- 如何快速刪掉SQL Server登錄時(shí)登錄名下拉列表框中的選項(xiàng)
- 淺談SQL Server交叉聯(lián)接 內(nèi)部聯(lián)接
