前言
这一节我们还是继续讲讲索引知识，前面我们聚集索引、非聚集索引以及覆盖索引等，在这其中还有一个过滤索引，通过索引过滤我们也能提高查询性能，简短的内容，深入的理解。
过滤索引，在查询条件上创建非聚集索引（1）
过滤索引是SQL 2008的新特性，被应用在表中的部分行，所以利用过滤索引能够提高查询，相对于全表扫描它能减少索引维护和索引存储的代价。当我们在索引上应用WHERe条件时就是过滤索引。也就是满足如下格式：
CREATE NonCLUSTERED INDEXON (
) WHERe ; 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）优化查询性能：通过查询计划可以看出其高效性。
讲到这里为止，一直陈述的是过滤索引的好处和优点，已经将其捧上天了，其实其缺点也是显而易见。
过滤索引缺点
最大的缺点则是查询条件的限制。其查询条件仅限于
::= [ AND ] ::= | ::= 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。
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，同时也希望多多支持考高分网！
MsSql相关栏目本月热门文章
- 1【Linux驱动开发】设备树详解（二）设备树语法详解
- 2别跟客户扯细节
- 3Springboot+RabbitMQ+ACK机制(生产方确认(全局、局部)、消费方确认)、知识盲区
- 4【Java】对象处理流（ObjectOutputStream和ObjectInputStream）
- 5【分页】常见两种SpringBoot项目中分页技巧
- 6一文带你搞懂OAuth2.0
- 7我要写整个中文互联网界最牛逼的JVM系列教程 | 「JVM与Java体系架构」章节：虚拟机与Java虚拟机介绍
- 8【Spring Cloud】新闻头条微服务项目：FreeMarker模板引擎实现文章静态页面生成
- 9JavaSE - 封装、static成员和内部类
- 10树莓派mjpg-streamer实现监控及拍照功能调试
- 11用c++写一个蓝屏代码
- 12从JDK8源码中看ArrayList和LinkedList的区别
- 13idea 1、报错java: 找不到符号 符号: 变量 log 2、转换成Maven项目
- 14在openwrt使用C语言增加ubus接口（包含C uci操作）
- 15Spring 解决循环依赖
- 16SpringMVC——基于MVC架构的Spring框架
- 17Andy‘s First Dictionary C++ STL set应用
- 18动态内存管理
- 19我的创作纪念日
- 20Docker自定义镜像-Dockerfile
热门相关搜索路由器设置 木托盘 宝塔面板 儿童python教程 心情低落 朋友圈 vim 双一流学科 专升本 我的学校 日记学校 西点培训学校 汽修学校 情书 化妆学校 塔沟武校 异形模板 西南大学排名 最精辟人生短句 6步教你追回被骗的钱 南昌大学排名 清朝十二帝 北京印刷学院排名 北方工业大学排名 北京航空航天大学排名 首都经济贸易大学排名 中国传媒大学排名 首都师范大学排名 中国地质大学(北京)排名 北京信息科技大学排名 中央民族大学排名 北京舞蹈学院排名 北京电影学院排名 中国戏曲学院排名 河北政法职业学院排名 河北经贸大学排名 天津中德应用技术大学排名 天津医学高等专科学校排名 天津美术学院排名 天津音乐学院排名 天津工业大学排名 北京工业大学耿丹学院排名 北京警察学院排名 天津科技大学排名 北京邮电大学(宏福校区)排名 北京网络职业学院排名 北京大学医学部排名 河北科技大学排名 河北地质大学排名 河北体育学院排名名师互学网 版权所有 (c)2021-2022 ICP备案号：晋ICP备2021003244-6号
