SQL Server-聚焦过滤索引提高查询性能（十）

前言

这一节我们还是继续讲讲索引知识，前面我们讲了聚集索引、非聚集索引以及覆盖索引等，在这其中还有一个过滤索引，通过索引过滤我们也能提高查询性能，简短的内容，深入的理解，Always to review the basics。

过滤索引，在查询条件上创建非聚集索引（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。 

时间： 2024-08-01 09:16:34

SQL Server-聚焦过滤索引提高查询性能（十）的相关文章

详解SQL Server的聚焦过滤索引_MsSql

前言 这一节我们还是继续讲讲索引知识,前面我们聚集索引.非聚集索引以及覆盖索引等,在这其中还有一个过滤索引,通过索引过滤我们也能提高查询性能,简短的内容,深入的理解. 过滤索引,在查询条件上创建非聚集索引(1) 过滤索引是SQL 2008的新特性,被应用在表中的部分行,所以利用过滤索引能够提高查询,相对于全表扫描它能减少索引维护和索引存储的代价.当我们在索引上应用WHERE条件时就是过滤索引.也就是满足如下格式: CREATE NONCLUSTERED INDEX <index name> O

详解SQL Server的聚焦过滤索引

前言 这一节我们还是继续讲讲索引知识,前面我们聚集索引.非聚集索引以及覆盖索引等,在这其中还有一个过滤索引,通过索引过滤我们也能提高查询性能,简短的内容,深入的理解. 过滤索引,在查询条件上创建非聚集索引(1) 过滤索引是SQL 2008的新特性,被应用在表中的部分行,所以利用过滤索引能够提高查询,相对于全表扫描它能减少索引维护和索引存储的代价.当我们在索引上应用WHERE条件时就是过滤索引.也就是满足如下格式: CREATE NONCLUSTERED INDEX <index name> O

浅析SQL Server的聚焦使用索引和查询执行计划_MsSql

前言 上一篇<浅析SQL Server 聚焦索引对非聚集索引的影响>我们讲了聚集索引对非聚集索引的影响,对数据库一直在强调的性能优化,所以这一节我们统筹讲讲利用索引来看看查询执行计划是怎样的,简短的内容,深入的理解. 透过索引来看查询执行计划 我们首先来看看第一个例子 1.默认使用索引 USE TSQL2012 GO SELECT orderid FROM Sales.Orders SELECT * FROM Sales.Orders 上述我们看到第2个查询的所需要的开销是第1个查询开销的3倍

SQL Server创建复合索引时，复合索引列顺序对查询的性能影响

原文:SQL Server创建复合索引时,复合索引列顺序对查询的性能影响    说说复合索引 写索引的博客太多了,一直不想动手写,有一下两个原因: 一是觉得有炒剩饭的嫌疑,有兄弟曾说:索引吗,只要在查询条件上建索引就行了,真的可以这么暴力吗? 二来觉得,索引是个非常大的话题,很难概括出所有的情况,你不整出点新意来,倒是有抄袭照搬的嫌疑 既然写了,就写一点稍微不一样的东西出来, 好了,废话打住,开搞   搭建测试环境: 创建一张表,模拟实际业务中的一个表,往里面填入数据, 时间字段上,相对按照时间

浅析SQL Server的聚焦使用索引和查询执行计划

前言 上一篇<浅析SQL Server 聚焦索引对非聚集索引的影响>我们讲了聚集索引对非聚集索引的影响,对数据库一直在强调的性能优化,所以这一节我们统筹讲讲利用索引来看看查询执行计划是怎样的,简短的内容,深入的理解. 透过索引来看查询执行计划 我们首先来看看第一个例子 1.默认使用索引 USE TSQL2012 GO SELECT orderid FROM Sales.Orders SELECT * FROM Sales.Orders 上述我们看到第2个查询的所需要的开销是第1个查询开销的3倍

SQL Server 优化---为什么索引视图（物化视图）需要with(noexpand)强制查询提示

原文:SQL Server 优化---为什么索引视图(物化视图)需要with(noexpand)强制查询提示   本文出处:http://www.cnblogs.com/wy123/p/6694933.html    第一次通过索引视图优化SQL语句,以及遇到的一些问题,记录一下.   语句分析 最近开发递交过来一个查询统计的SQL,说是性能有问题,原本执行需要4-5秒钟,这个业务本身对性能要求又比较critical,期望是在1s之内在用尽各种办法之后(执行计划,统计信息,索引,改写SQL,临时

Sql Server优化之索引提示----我们为什么需要查询提示，Sql Server默认情况下优化策略选择的不足

原文:Sql Server优化之索引提示----我们为什么需要查询提示,Sql Server默认情况下优化策略选择的不足 环境: Sql Server2012 SP3企业版,Windows Server2008 标准版   问题由来: 最近在做DB优化的时候,发现一个存储过程有非常严重的性能问题, 由于整个SP整体逻辑是一个多表关联的复杂的查询,整体结构比较复杂的,通过的分析和尝试, 最后发现问题出在其中一个大表的查询上实现方式上, 因为这个大表上的意外的执行方式,导致其他表无法被驱动,其他表也

使用查询改写提高查询性能

性能 无需改变SQL查询就可以大幅提高查询性能. 你是否为等待你的查询返回结果而感到疲惫?你是否已经为增强索引和调优SQL而感到疲惫,但仍然不能提高查询性能?那么,你是否已经考虑创建物化视图?有了物化视图,那些过去需要数小时运行的报告可以在几分钟内完成.物化视图可以包括联接(join)和集合(aggregate),它提供了一种储存预计算结果的方法. 在执行一个查询时,优化器会判定访问物化视图或数据驻留的基础表是否更快一些.如果优化器判定查询物化视图是更好的解决方案,那么优化器会在一个被称为"查询

SQL Server基础之&lt;索引&gt;

原文:SQL Server基础之<索引>  索引用于快速找出在某个列中有某一特定值的行,不使用索引,数据库必须从第一条记录开始读完整个表,直到找出相关的行.表越大,查询数据所花费的时间越多,如果表中查询的列有一个索引,数据库能快速到达一个位置去搜索数据,本篇主要介绍与索引相关的内容,包括索引的含义和特点,索引的分类,索引的设计原则以及如何创建和删除索引. 一:索引的含义和特点  索引是一个单独的,存储在磁盘上的数据结构,它们包含则对数据表里所有记录的引用指针,使用索引用于快速找出在某个或多个列