当前位置：Gxlcms > 数据库问题 > SQL Server-聚焦过滤索引提高查询性能（十）

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

时间：2021-07-01 10:21:17 帮助过：12人阅读

<table> (<columns>) WHERE <criteria>; GO

下面我们来看一个简单的查询

  1. <span style="color: #000000;">USE AdventureWorks2012
  2. GO
  3. SELECT SalesOrderDetailID, UnitPrice
  4. FROM Sales.SalesOrderDetail
  5. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span><span style="color: #000000;">
  6. GO</span>

上述列中未建立任何索引，当然除了SalesOrderDetailID默认创建的聚集索引，这种情况下我们能够猜想到其执行的查询计划必然是主键创建的聚集索引扫描，如下

技术分享

上述我们已经说过此时未在查询条件上创建索引，所以此时必然走的是主键创建的聚集索引，接下来我们首先在UnitPrice列上创建非聚集索引来提高查询性能， 

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_UnitPrice
  2. ON Sales.SalesOrderDetail(UnitPrice)</span>

此时我们再来比较二者查询开销

  1. <span style="color: #000000;">USE AdventureWorks2012
  2. GO
  3. DBCC FREEPROCCACHE
  4. DBCC DROPCLEANBUFFERS
  5. SELECT SalesOrderDetailID, UnitPrice
  6. FROM AdventureWorks2012.Sales.SalesOrderDetail WITH(INDEX([PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]))
  7. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span><span style="color: #000000;">
  8. GO
  9.  
  10. SELECT SalesOrderDetailID, UnitPrice
  11. FROM  Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_UnitPrice]))
  12. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span>

技术分享

此时在查询条件上建立了非聚集索引之后，查询开销提升的非常明显，提升达到了90%以上，因为非聚集索引也会引用了主键创建的聚集索引，所以这个时候不会导致Bookmark Lookup或者Key Lookup查找。接下来我们我们再添加一个带有条件的非聚集索引即过滤索引

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPrice
  2. ON Sales.SalesOrderDetail(UnitPrice)
  3. WHERE UnitPrice </span>> <span style="color: #800080;">1000</span>

此时我们再来看看创建了过滤索引之后和之前非聚集索引性能开销差异：

  1. <span style="color: #000000;">USE AdventureWorks2012
  2. GO
  3. DBCC FREEPROCCACHE
  4. DBCC DROPCLEANBUFFERS
  5. SELECT SalesOrderDetailID, UnitPrice
  6. FROM AdventureWorks2012.Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_UnitPrice]))
  7. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span><span style="color: #000000;">
  8.  
  9. SELECT SalesOrderDetailID, UnitPrice
  10. FROM  Sales.SalesOrderDetail WITH(INDEX([idxwhere_SalesOrderDetail_UnitPrice]))
  11. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span>

技术分享

此时我们知道创建的非聚集过滤索引与传统创建的非聚集索引相比，我们的查询接近减少了一半。

唯一过滤索引

唯一过滤索引对于所有列必须唯一且不为空（只允许一个NULL存在）也是非常好的解决方案，所以此时在创建唯一过滤索引时需要将NULL值除外，比如如下：

  1. <span style="color: #000000;">CREATE UNIQUE NONCLUSTERED INDEX uq_fix_Customers_Email
  2. ON Customers(Email)
  3. WHERE Email IS NOT NULL
  4. GO</span>

过滤索引结合INCLUDE

当我们再添加一个额外列时，使用默认主键创建的聚集索引时，此时会走聚集索引扫描，然后我们在查询条件上创建一个过滤索引，我们强制使用这个过滤索引时，此时由于添加额外列，会导致需要返回到基表中再去获取数据，所以也就造成了Key Lookup查找，如下：

  1. <span style="color: #000000;">USE AdventureWorks2012
  2. GO
  3. SELECT SalesOrderDetailID, UnitPrice, <span style="color: #ff0000;">UnitPriceDiscount</span>
  4. FROM Sales.SalesOrderDetail
  5. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span><span style="color: #000000;">
  6. GO</span>

技术分享 

此时我们需要用INCLUDE来包含额外列。

  1. CREATE NONCLUSTERED INDEX [idx_SalesOrderDetail_UnitPrice] ON Sales.SalesOrderDetail(UnitPrice) INCLUDE(UnitPriceDiscount)

我们再创建一个过滤索引同时包括额外列

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX [idxwhere_SalesOrderDetail_UnitPrice] ON Sales.SalesOrderDetail(UnitPrice) INCLUDE(UnitPriceDiscount)
  2. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span>

接下来再来执行比较添加过滤索引和未添加过滤索引同时都包括了额外列的性能查询差异。

  1. <span style="color: #000000;">SELECT SalesOrderDetailID, UnitPrice, UnitPriceDiscount
  2. FROM AdventureWorks2012.Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_UnitPrice]))
  3. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span><span style="color: #000000;">
  4.  
  5. SELECT SalesOrderDetailID, UnitPrice, UnitPriceDiscount
  6. FROM  Sales.SalesOrderDetail WITH(INDEX([idxwhere_SalesOrderDetail_UnitPrice]))
  7. WHERE UnitPrice </span>> <span style="color: #800080;">2000</span>

技术分享

此时性能用INCLUDE来包含额外列性能也得到了一定的改善。

过滤索引，在主键上创建非聚集索引（2）

 在第一个案列中，我们可以直接在查询列上创建非聚集索引，因为其类型是数字类型，要是查询条件是字符类型呢？首选现在我们先创建一个测试表

  1. <span style="color: #000000;">USE TSQL2012
  2. GO
  3. CREATE TABLE dbo.TestData 
  4. (
  5.     RowID       integer IDENTITY NOT NULL, 
  6.     SomeValue   VARCHAR(max) NOT NULL,      
  7.     StartDate   date NOT NULL,
  8.     CONSTRAINT PK_Data_RowID
  9.         PRIMARY KEY CLUSTERED (RowID)
  10. );</span>

添加10万条测试数据

  1. <span style="color: #000000;">USE TSQL2012
  2. GO
  3. INSERT dbo.TestData WITH (TABLOCKX)
  4.     (SomeValue, StartDate)
  5. SELECT
  6.     CAST(N.n AS VARCHAR(max)) </span>+ <span style="color: #800000;">‘</span><span style="color: #800000;">JeffckyWang</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  7.     DATEADD(DAY, (N.n </span>- <span style="color: #800080;">1</span>) % <span style="color: #800080;">31</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">20140101</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
  8. FROM dbo.Nums AS N
  9. WHERE 
  10.     N.n </span>>= <span style="color: #800080;">1</span><span style="color: #000000;"> 
  11.     AND N.n </span>< <span style="color: #800080;">100001</span>;

如果我们需要获取表TestData中SomeValue = ‘JeffckyWang‘，此时我们想要在SomeValue上创建一个非聚集索引然后进行过滤，如下

  1. <span style="color: #000000;">USE TSQL2012
  2. GO
  3. CREATE NONCLUSTERED INDEX idx_noncls_somevalue
  4. ON dbo.TestData(SomeValue)
  5. WHERE SomeValue </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">JeffckyWang</span><span style="color: #800000;">‘</span>

技术分享

SQL Server 2012报错无法对字符类型创建非聚集索引，此时我们则需要在主键上创建非聚集索引，我们在主键RowID上创建一个过滤索引且SomeValue = ‘JeffckyWang‘，然后返回数据，如下：

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX idxwhere_noncls_somevalue
  2. ON dbo.TestData(RowID)
  3. WHERE SomeValue </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">JeffckyWang</span><span style="color: #800000;">‘</span>

下面我们来对比建立过滤索引前后查询计划结果：

  1. <span style="color: #000000;">USE TSQL2012
  2. GO
  3. SELECT RowID, SomeValue, StartDate 
  4. FROM dbo.TestData WITH(INDEX([idx_pk_rowid]))
  5. WHERE SomeValue </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">JeffckyWang</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  6. SELECT RowID, SomeValue, StartDate 
  7. FROM dbo.TestData WITH(INDEX([idxwhere_noncls_somevalue]))
  8. WHERE SomeValue </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">JeffckyWang</span><span style="color: #800000;">‘</span>

技术分享

然后结合之前所学，移除Key Lookup，对创建的过滤索引进行INCLUDE。

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX [idxwhere_noncls_somevalue] ON dbo.TestData(RowID) INCLUDE(SomeValue,StartDate) 
  2. WHERE SomeValue </span>= <span style="color: #800000;">‘</span><span style="color: #800000;">JeffckyWang</span><span style="color: #800000;">‘</span>

技术分享

从这里看出，无论是对查询条件创建过滤索引还是对主键创建过滤索引，我们都可以通过结合之前所学来提高查询性能。

我们从开头就一直在讲创建过滤索引，那么创建过滤索引优点的条件到底是什么？ 

（1）只能通过非聚集索引进行创建。

（2）如果在视图上创建过滤索引，此视图必须是持久化视图。

（3）不能在全文索引上创建过滤索引。

过滤索引的优点 

（1）减少索引维护成本：对于增、删、改等操作不需要代价没有那么昂贵，因为一个过滤索引的重建不需要耗时太多时间。

（2）减少存储成本：过滤索引的存储占用空间很小。

（3）更精确的统计：通过在WHERE条件上创建过滤索引比全表统计结果更加精确。

（4）优化查询性能：通过查询计划可以看出其高效性。

讲到这里为止，一直陈述的是过滤索引的好处和优点，已经将其捧上天了，其实其缺点也是显而易见。

过滤索引缺点

最大的缺点则是查询条件的限制。其查询条件仅限于

  1. <filter_predicate> ::=   
  2.     <conjunct> [ AND <conjunct><span style="color: #000000;"> ]  
  3.   
  4. </span><conjunct> ::=  
  5.     <disjunct> | <comparison>  
  6.   
  7. <disjunct> ::=<span style="color: #000000;">  
  8.         column_name IN (constant ,...n)  </span>

过滤条件仅限于AND、|、IN。比较条件仅限于 { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }，所以如下利用LIKE不行

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX [idxwhere_noncls_somevalue] ON dbo.TestData(RowID) INCLUDE(SomeValue,StartDate) 
  2. WHERE SomeValue LIKE </span><span style="color: #800000;">‘</span><span style="color: #800000;">JeffckyWang%</span><span style="color: #800000;">‘</span>

技术分享

如下可以

  1. <span style="color: #000000;">USE AdventureWorks2012
  2. GO
  3. CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ModifiedDate
  4. ON Sales.SalesOrderDetail(ModifiedDate)
  5. WHERE ModifiedDate </span>>= <span style="color: #800000;">‘</span><span style="color: #800000;">2008-01-01</span><span style="color: #800000;">‘</span> AND ModifiedDate <= <span style="color: #800000;">‘</span><span style="color: #800000;">2008-01-07</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  6. GO</span>

如下却不行

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ModifiedDate
  2. ON Sales.SalesOrderDetail(ModifiedDate)
  3. WHERE ModifiedDate </span>=<span style="color: #000000;"> GETDATE()
  4. GO</span>

技术分享

变量对过滤索引影响

上述我们创建过滤索引在查询条件上直接定义的字符串，如下：

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPrice
  2. ON Sales.SalesOrderDetail(UnitPrice)
  3. WHERE UnitPrice </span>> <span style="color: #800080;">1000</span>

如果定义的是变量，利用变量来进行比较会如何呢？首先我们创建一个过滤索引

  1. <span style="color: #000000;">CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ProductID 
  2. ON Sales.SalesOrderDetail (ProductID)
  3. WHERE ProductID </span>= <span style="color: #800080;">870</span>

利用变量来和查询条件比较，强制使用过滤索引（默认情况下走聚集索引）

  1. <span style="color: #000000;">USE AdventureWorks2012
  2. GO
  3. DECLARE @ProductID INT 
  4. SET @ProductID </span>= <span style="color: #800080;">870</span><span style="color: #000000;">  
  5. SELECT ProductID 
  6. FROM Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_ProductID]))
  7. WHERE ProductID </span>= @ProductID

技术分享

查看查询执行计划结果却出错了，此时我们需要添加OPTION重新编译，如下：

  1. <span style="color: #000000;">USE AdventureWorks2012
  2. GO
  3. DECLARE @ProductID INT 
  4. SET @ProductID </span>= <span style="color: #800080;">870</span><span style="color: #000000;">  
  5. SELECT ProductID 
  6. FROM Sales.SalesOrderDetail
  7. WHERE ProductID </span>=<span style="color: #000000;"> @ProductID
  8. <span style="color: #ff0000;">OPTION(RECOMPILE)</span></span>

技术分享

上述利用变量来查询最后通过OPTION重新编译在SQL Server 2012中测试好使，至于其他版本未知，参考资料【The Pains of Filtered Indexes】。

总结 

本节我们学习了通过过滤索引来提高查询性能，同时也给出了其不同的场景以及其使用优点和明显的缺点。简短的内容，深入的理解，我们下节再会，good night。 

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

标签：查找   结合   server   其他   lte   art   定义   技术分享   默认   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！