AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2017-11-14 1122
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
简介：

前言

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







本文转自Jeffcky博客园博客，原文链接：http://www.cnblogs.com/CreateMyself/p/6129924.html，如需转载请自行联系原作者

文章标签：
云数据库 RDS SQL Server 版
索引
SQL
Go
存储
关键词：
SQL查询
SQL性能
SQL查询性能
SQL索引
云数据库RDS SQLServer版查询
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
嗯哼9925
目录
相关文章
猿java
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能？
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL，并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点，包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后，通过具体示例展示了索引如何提升查询速度，并提供了三层B+树的存储容量计算方法。通过这些技巧，可以帮助开发者有效提升数据库查询效率。
猿java
172 2
潇洒洒
|
21天前
|
SQL 数据库 UED
SQL性能提升秘籍：5步优化法与10个实战案例
在数据库管理和应用开发中，SQL查询的性能优化至关重要。高效的SQL查询不仅可以提高应用的响应速度，还能降低服务器负载，提升用户体验。本文将分享SQL优化的五大步骤和十个实战案例，帮助构建高效、稳定的数据库应用。
潇洒洒
37 3
潇洒洒
|
23天前
|
SQL IDE 数据库连接
IntelliJ IDEA处理大文件SQL：性能优势解析
在数据库开发和管理工作中，执行大型SQL文件是一个常见的任务。传统的数据库管理工具如Navicat在处理大型SQL文件时可能会遇到性能瓶颈。而IntelliJ IDEA，作为一个强大的集成开发环境，提供了一些高级功能，使其在执行大文件SQL时表现出色。本文将探讨IntelliJ IDEA在处理大文件SQL时的性能优势，并与Navicat进行比较。
潇洒洒
29 4
sunrr
|
1月前
|
SQL 存储 缓存
如何优化SQL查询性能？
【10月更文挑战第28天】如何优化SQL查询性能？
sunrr
100 10
技术自由圈/原疯狂创客圈
|
1月前
|
SQL 关系型数据库 MySQL
惊呆：where 1=1 可能严重影响性能，差了10多倍，快去排查你的 sql
老架构师尼恩在读者交流群中分享了关于MySQL中“where 1=1”条件的性能影响及其解决方案。该条件在动态SQL中常用，但可能在无真实条件时导致全表扫描，严重影响性能。尼恩建议通过其他条件或SQL子句命中索引，或使用MyBatis的`&lt;where&gt;`标签来避免性能问题。他还提供了详细的执行计划分析和优化建议，帮助大家在面试中展示深厚的技术功底，赢得面试官的青睐。更多内容可参考《尼恩Java面试宝典PDF》。
技术自由圈/原疯狂创客圈
28 2
最好zzz
|
21天前
|
SQL 缓存 监控
SQL性能提升指南：五大优化策略与十个实战案例
在数据库性能优化的世界里，SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载，提高应用响应速度，甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤，并结合十个实战案例，为你提供一份详尽的性能提升指南。
最好zzz
42 0
1100237741946300
|
2月前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中，慢SQL（即执行缓慢的SQL语句）不仅会影响查询性能，还可能对数据库的写入性能产生显著的不利影响
1100237741946300
122 5
何雨晨
|
2月前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差？其实可以这样优化
遇到SQL 子查询性能很差？其实可以这样优化
何雨晨
111 2
何雨晨
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
64 1
糜终
|
2月前
|
SQL 存储 数据挖掘
SQL Server 日期格式查询详解
SQL Server 日期格式查询详解
糜终
171 2

热门文章

最新文章

  • 1
    多台SQLServer数据实时同步，syncnavigator使用
  • 2
    阿里云服务器怎么搭建sql server（即 MS SQL）？
  • 3
    SQLServer CDC数据通过Kafka connect实时同步至分析型数据库 AnalyticDB For PostgreSQL及OSS
  • 4
    使用批处理文件命令行方式快速启动和停止IIS、SqlServer
  • 5
    [推荐] (SqlServer)批量清理指定数据库中所有数据
  • 6
    数据库SQL Server与C#中数据类型的对应关系
  • 7
    SQL Server 深入解析索引存储(堆)
  • 8
    SQLSERVER数据库分离作用及附加方法
  • 9
    第十二章——SQLServer统计信息（2）——非索引键上统计信息的影响
  • 10
    SQL Server 查询性能优化 相关文章
  • 1
    实时计算 Flink版产品使用合集之flink sql ROW_NUMBER()回退更新的机制，有相关文档介绍吗
    96
  • 2
    实时计算 Flink版产品使用合集之ClickHouse-JDBC 写入数据时，发现写入的目标表名称与 PreparedStatement 中 SQL 的表名不一致如何解决
    89
  • 3
    实时计算 Flink版产品使用合集之遇到SQL Server锁表问题如何解决
    52
  • 4
    SQL基础(1)，从三流Linux运维外包到秒杀阿里P7,
    70
  • 5
    实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
    260
  • 6
    实时计算 Flink版操作报错合集之Flink sql-client 针对kafka的protobuf格式数据建表，报错：java.lang.ClassNotFoundException 如何解决
    233
  • 7
    实时计算 Flink版产品使用合集之在使用Flink SQL向ClickHouse写入数据的过程中出现丢数据或重复数据的情况如何解决
    346
  • 8
    实时计算 Flink版产品使用合集之从MySQL到Flink 1.16.2 Flink-SQL的数据同步工作出现了一个异常如何解决
    138
  • 9
    实时计算 Flink版产品使用合集之将OceanBase的CDC数据导入到Flink SQL的任务的步骤是什么
    66
  • 10
    实时计算 Flink版产品使用合集之是否可以使用 DataStream API 或 Flink SQL 开发任务
    58

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用检索分析服务Elasticsearch版实现基础检索
  • 使用SQL语句管理索引
    • 下一篇
    阿里云对象存储OSS收费标准：存储、流量和请求等多个计费项详解