463.com解析SQL Server聚焦移除

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

比较移除Bookmark Lookup等两种方式差异

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

到这里我们应该发现了,唯一的区别在于我们创建非聚集索引时的顺序和查询条件不同就会导致索引扫描和索引查找的转换,那么到底什么时候才会执行索引查找呢?我们可以进行如下一般性总结:

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

抛出Bookmark Lookup、RID Lookup、Key Lookup问题

更新

USE TSQL2012GOSELECT orderid, shipaddress, shipregionFROM Sales.OrdersWHERE shipaddress = '深圳' GO

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

总结

CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_UnitPriceON Sales.SalesOrderDetail(UnitPrice)
USE TSQL2012GOCREATE NONCLUSTERED INDEX [ix_noncls_include] ON [TSQL2012].[Sales].[Orders] ( shipcity) INCLUDE (shipaddress, shipregion, orderid)

总结

既然有如上两种方式,我们应该有所取舍,二者谁的性能更好呢?我们接下来比较上述二者的开销差异。

CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPriceON Sales.SalesOrderDetail(UnitPrice)WHERE UnitPrice  1000

一说到这三者,如果对索引研究不深的童鞋估计是懵逼的,什么玩意,我们姑且将上面三者翻译为:标签查找、行ID查找、键查找。标签查找和键查找是一个意思,在SQL
2005之前叫Key
Lookup。怎么解释,如何定义呢?首先我们不看定义,直接看下面一步一步解析,如果你实在忍不住,请看园友的见解,解释还是非常到位。我们简短的说明下此三者概念。

过滤索引结合INCLUDE

前言

变量对过滤索引影响

CREATE NONCLUSTERED INDEX idx_noncls_cover_exceptorderidON Sales.Orders(shipcity,shipaddress,shipregion)CREATE NONCLUSTERED INDEX idx_noncls_include_exceptorderidON Sales.Orders(shipcity) INCLUDE(shipaddress,shipregion)
USE AdventureWorks2012GOCREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ModifiedDateON Sales.SalesOrderDetail(ModifiedDate)WHERE ModifiedDate = '2008-01-01' AND ModifiedDate = '2008-01-07'GO

我们对查询条件以及检索列创建非聚集索引。

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

索引查找的一般性结论:如果条件中包含WHERE或者ON的话,查询条件必须是位于索引集合列中首位,此时索引查找将会被使用。

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

USE TSQL2012GOSELECT orderid, shipaddress, shipregionFROM Sales.OrdersWHERE shipcity = '深圳'
USE TSQL2012GOCREATE NONCLUSTERED INDEX idx_noncls_somevalueON dbo.TestData(SomeValue)WHERE SomeValue = 'JeffckyWang'

解决Bookmark Lookup、RID Lookup、Key Lookup问题

CREATE NONCLUSTERED INDEX idx_SalesOrderDetail_ProductID ON Sales.SalesOrderDetail (ProductID)WHERE ProductID = 870

覆盖索引与默认聚集索引性能开销比较

CREATE UNIQUE NONCLUSTERED INDEX uq_fix_Customers_EmailON Customers(Email)WHERE Email IS NOT NULLGO

由上知,非聚集索引列不需要包含创建了聚集索引的列,那么事实到底是怎样的呢?

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

此时我们穿插一点内容,上述我们创建了覆盖索引,我们来比较下覆盖索引和默认情况下聚集索引查找的性能开销。

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

创建非聚集索引覆盖索引

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

我们再执行上述查询

CREATE NONCLUSTERED INDEX idxwhere_SalesOrderDetail_UnitPriceON Sales.SalesOrderDetail(UnitPrice)WHERE UnitPrice  1000

这个不用多讲,没添加任何索引,执行查询计划是全表扫描。接下来我们创建在orderid上创建聚集索引如下:

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

此时我们对检索列创建了非聚集索引,此时将不会再到数据页中获取数据,而是从索引中直接返回,所以到这里我们算是移除了Key
Lookup。但是此时触发另外一个问题,执行查询计划走的却是索引扫描,索引到底是什么呢?我们打个比方,一个索引相当于是数据库中一个本书开始的索引,我们需要快速从书中查找到我们所需要的数据,这个时候书就是我们所说的表。索引扫描意味着要读取表中的所有行,然后返回满足条件的所有数据,当执行索引扫描时,所有行上叶子节点上的所有都会被扫描,这也就意味着索引上的所有行都会被检索一遍而不是直接检索表,和表扫描对比的话,表扫描是直接读取表中数据,所以表扫描和索引扫描还是有一点点不同,而索引查找则是依赖于索引页数据来定位满足条件的所有行,索引查找仅仅只影响满足条件以及页上包含这些满足条件的行,所以说索引查找更加高效。

CREATE NONCLUSTERED INDEX index nameON table (columns)WHERE criteria;GO

我们从上所知,二者开销一样,并未有什么区别,当然相信我们更倾向于的是将第二种方式作为解决方案。到这里算是基本结束了,但是还有一个小问题,我们在之前已经创建了orderid的聚集索引,后面在解决方案中我们也添加了orderid的非聚集索引,难道非得添加吗,我们去掉试试看。

此时我们在主键上创建非聚集索引,我们在主键RowID上创建一个过滤索引且SomeValue
= ‘JeffckyWang’,然后返回数据,如下:

FROM Sales.Orders WITH(INDEX([PK_Orders]))WHERE orderid11072goSELECT orderid, shipaddress, shipregionFROM Sales.Orders WITH(INDEX([idx_noncls_include_exceptorderid]))WHERE orderid11072GO
USE TSQL2012GOINSERT 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 NWHERE N.n = 1 AND N.n  100001;

结论:其实对于任何非聚集索引列都不需要包含创建了聚集索引的列,因为创建聚集索引的列是非聚集索引集合列的一部分,也就是说只要一个表上的列创建了聚集索引,那么非聚集索引集合列就包含了这个聚集索引。

USE AdventureWorks2012GODBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSSELECT SalesOrderDetailID, UnitPriceFROM AdventureWorks2012.Sales.SalesOrderDetail WITH(INDEX([idx_SalesOrderDetail_UnitPrice]))WHERE UnitPrice  2000SELECT SalesOrderDetailID, UnitPriceFROM Sales.SalesOrderDetail WITH(INDEX([idxwhere_SalesOrderDetail_UnitPrice]))WHERE UnitPrice  2000

Bookmark Lookup、RID Lookup、Key Lookup定义

过滤索引,在主键上创建非聚集索引

我们观察到对查询条件创建了非聚集索引,查询计划会使用非聚集索引查找返回结果,但是对于shipaddress,
shipcity,
shipregion并不是索引的一部分,此时查询引擎会返回到基表中得到这些数据再返回。这种行为就叫做Bookmark
Lookup或者Key
Lookup。下面我们就如本文标题一样问题出现来解决问题,移除Bookmark
Lookup或者Key Lookup。我们尝试用两种不同的方法来解决。

CREATE NONCLUSTERED INDEX [idxwhere_noncls_somevalue] ON dbo.TestData(RowID) INCLUDE(SomeValue,StartDate) WHERE SomeValue LIKE 'JeffckyWang%'

创建INCLUDE非聚集索引

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

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,同时也希望多多支持脚本之家!

过滤索引的优点

463.com,接着进行查询

过滤索引是SQL
2008的新特性,被应用在表中的部分行,所以利用过滤索引能够提高查询,相对于全表扫描它能减少索引维护和索引存储的代价。当我们在索引上应用WHERE条件时就是过滤索引。也就是满足如下格式:

CREATE NONCLUSTERED INDEX idx_nc_shipcity ON Sales.Orders(shipcity)

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

CREATE NONCLUSTERED INDEX idx_cls_cover ON Sales.Orders(shipcity,orderid,shipaddress,shipregion)

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

去除orderid比较二者开销差异:

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

CREATE CLUSTERED INDEX idx_cls_orderid ON Sales.Orders(orderid)

CREATE NONCLUSTERED INDEX [idxwhere_SalesOrderDetail_UnitPrice] ON Sales.SalesOrderDetail(UnitPrice) INCLUDE(UnitPriceDiscount)WHERE UnitPrice  2000

在查询中,我们对返回的列在查询条件上若建立了非聚集索引,此时将可能尝试使用非聚集索引查找,如果返回的列没有创建非聚集索引,此时会返回到数据页中去获取这些列的数据,即使表中存在聚集索引或者没有,都会返回到表中或者聚集索引中去获取数据。对于以上场景描述,如果表没有创建聚集索引则称为Bookmar
Lookup,如果表中没有聚集索引但是存在非聚集索引我们称为RID
Lookup。看到这里我们就会想法操作如此耗时,还要返回到基表中去获取数据,所以才有了我们本节来移除以上三者来提高查询性能。接下来我们一起来看看。

唯一过滤索引

通过上述覆盖索引与默认聚集索引的对比,我们能够有效的减少IO,这一点也是非常明确的,当然下面的INCLUDE索引对比也是另外一种好的方案。

filter_predicate ::= conjunct [ AND conjunct ] conjunct ::= disjunct | comparison disjunct ::= column_name IN (constant ,...n)

上述我们稍微讲解了下索引扫描和索引查找,而上述的问题是我们创建了非聚集索引,但是结果执行的查询计划是索引扫描,很是纳闷,对于刚学索引小白的我来说,不知该如何是好,以为是缓存的缘故,清除各种缓存均不好使。于是开始胡思乱想是不是检索列中数据有为NULL引起的,是不是检索列数据重复引起的,尝试了无数次,最终发现某一次居然好使。如下

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

CREATE NONCLUSTERED INDEX idx_all_cover ON Sales.Orders(shipaddress,orderid,shipcity,shipregion)

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

USE TSQL2012GOSELECT orderid, shipaddress, shipcity, shipregionFROM Sales.Orders WITH(INDEX(idx_all_cover))WHERE shipcity = '深圳'GOSELECT orderid, shipaddress, shipcity, shipregionFROM Sales.Orders WITH(INDEX(ix_noncls_include))WHERE shipcity = '深圳'GO

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

此时若我们将查询条件进行如下修改。

相关文章