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
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
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
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'
::=
[ AND ]
::=
| ::=
column_name IN (constant ,...n)
过滤条件仅限于AND、|、IN。比较条件仅限于 { IS | IS NOT | = | | != | > | >= | !> |
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
如下却不行
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】。
最新评论