SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第二篇)

SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第二篇)

在看这篇文章的内容之前，请阁下先看第一篇的内容，因为没有第一篇的基础的话会看到一头雾水哦o(∩_∩)o

第一篇的地址：SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第一篇)

 ------------------------------开始----------------------------------------------------------------

索引上的数据检索方法

如果一张表上没有聚集索引，数据将会以随机的顺序存放在表格里。

以[SalesOrderDetail_test]为例子(这个表在第一篇里已经建立好了)。他的上面没有聚集索引，

只有一个在SalesOrderID上的非聚集索引。所以表格的每一行记录，不会按照任何顺序，而是随意地存放在Hash里 

 

这时候如果用户要找所有单价大于200的销售详细记录，要运行的语句会是

SET STATISTICS PROFILE ON
USE [AdventureWorks]
GO
SELECT 
[SalesOrderDetailID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test]
WHERE [UnitPrice]>200

由于表格在UnitPrice上没有索引，所以SQLSERVER不得不对这个表格从头到尾扫描一遍，把所有UnitPrice的值大于200的记录

一个一个挑出来，从执行计划里可以清楚地看出来SQLSERVER做了一个表扫描。（执行计划我就不贴出来了 ）

如果这个表格上有聚集索引，事情会怎样呢？还是以刚才那张表做例子，先给他在值是唯一的字段SalesOrderDetailID上

建立一个聚集索引，这样所有的数据都会按照聚集索引的顺序存储

USE [AdventureWorks]
GO
CREATE CLUSTERED INDEX SalesOrderDetail_test_CL ON [dbo].[SalesOrderDetail_test]([SalesOrderDetailID])
GO

可惜的是,查询条件UnitPrice上没有索引,所以SQLSERVER还是要把所有记录都扫描一遍

和刚才有区别的是:执行计划里的表扫描变成了聚集索引扫描.因为在有聚集索引的表格上数据是

直接存放在索引的最底层的,所以要扫描整个表格里的数据,就要把整个聚集索引扫描一遍。

在这里，聚集索引扫描就相当于一个表扫描。所要用的时间和资源与表扫描没有什么差别。

并不是说这里有了“Index”这个字样，就说明执行计划比表扫描的有多大进步。

当然反过来讲，如果看到“Table Scan”的字样，就说明这个表格上没有聚集索引

 

现在在UnitPrice上面建立一个非聚集索引，看看情况会有什么变化

USE [AdventureWorks]
GO
CREATE INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])
GO 

在非聚集索引里，会为每条记录存储一份非聚集索引索引键的值和一份聚集索引索引键的值

【在没有聚集索引的表格里，是RID值指向数据页面，有聚集索引的话指向聚集索引的键(在不使用include时)】

所以在这里，每条记录都会有一份[UnitPrice]和[SalesOrderDetailID]记录，按照[UnitPrice]的顺序存放

 

再跑刚才那个查询，你会看到这次SQLSERVER不用扫描整个表了。根据新建的索引，

他直接找到了符合记录的值执行计划：索引查找

 

但是，光用建立在[UnitPrice]上的索引不能告诉我们其他字段的值。如果在刚才那个查询里再增加几个字段返回，

SQLSERVER就要先在非聚集索引上找到所有[UnitPrice]大于200的记录，然后再根据[SalesOrderDetailID]的值

找到存储在聚集索引上的详细数据。这个过程可以称为“bookmark loolup” 书签查找（书签查找很难避免）

为什麽很难避免书签查找？阁下继续看下去就知道了

SET STATISTICS PROFILE ON
USE [AdventureWorks]
GO
SELECT [SalesOrderID],[SalesOrderDetailID],[UnitPrice]
FROM [dbo].[SalesOrderDetail_test] WITH (INDEX (salesorderdetail_test_ncl_price))
WHERE [UnitPrice]>200

在SQLSERVER2005以后，bookmark lookup的动作用一个嵌套循环来完成。所以在执行计划里，可以看到

SQLSERVER先seek了非聚集索引salesorderdetail_test_ncl_price，然后用clustered index seek把需要的行找出来。

这里的嵌套循环其实就是bookmark lookup

 

-----------------------------------总结--------------------------------------------------------
总结一下，在SQLSERVER里根据找寻目标的不同和方法不同，有下面几种情况
SQLSERVER寻找数据的方法
结构                                                   Scan                               Seek
堆（没有聚集索引的表格数据页）            Table Scan                          无
聚集索引                                      Clustered Index Scan          Clustered Index Seek
非聚集索引                                         Index Scan                     Index Seek

注意：非聚集索引的情况：（如果表格没有聚集索引的话数据还是存放在堆结构的表数据页，只是在非聚集索引里，

会为每条记录存储一份非聚集索引索引键的值和一份聚集索引索引键的值 （在没有聚集索引的表格里，是RID值指向数据页面，

有聚集索引的话指向聚集索引的键） !!

为什麽不叫：NONCLUSTERED Index Scan？NONCLUSTERED Index Seek？

前面说了SQLSERVER只有两种索引：聚集索引和非聚集索引

下面这两句话是一样的都是建立非聚集索引：

CREATE NONCLUSTERED INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])
GO


CREATE  INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice])
GO

所以就没有必要叫NONCLUSTERED Index Scan和NONCLUSTERED Index Seek了

 

--------------------------------------------------再总结--------------------------------------------------------------

因为要说的太长 ，没有办法，我都不知道怎么排版了

如果在执行计划里看到这些动作，就应该能知道SQLSERVER正在对哪种对象在做什么样的操作。

表扫描表明正在处理的表格没有聚集索引，SQLSERVER正在扫描整张表。

聚集索引扫描表明SQLSERVER正在扫描一张有聚集索引的表，但是也是整张表扫描。

Index Scan表明SQLSERVER正在扫描一个非聚集索引。由于非聚集索引上一般只会有一小部分字段，

所以这里虽然也是扫描但是代价会比整表扫描会少很多，因为数据不存放在索引里面。

Clustered Index Seek和Index Seek说明SQL正在利用索引结果检索目标数据。如果结果集只占表格总数据量的一小部分

并且结果集的字段都包含在索引里，Seek会比Scan便宜很多，索引就起到了提高性能的作用，

否则就会引起bookmark lookup书签查找

 

-------------------------------------再再总结------------------------------------------------------

(1)where 后面（筛选的字段）：决定你建索引的时候要建的字段的范围，如果某个字段经常作为筛选字段那么可以在他上面建立索引

CREATE CLUSTERED INDEX SalesOrderDetail_test_CL ON [dbo].[SalesOrderDetail_test]([SalesOrderDetailID])

(2)where前面（返回的字段）：决定你的表数据的查找速度，根据聚集索引来返回其他字段的值，没有聚集索引的话到数据页面里去找字段值

(3)聚集索引扫描：where后面的字段是没有建立索引【包括非聚集索引（表格上要有非聚集索引）和聚集索引】的字段 

但是表格上有聚集索引 ，不管表格上有没有非聚集索引

(3)非聚集索引扫描：where后面的字段是没有建立索引（非聚集索引）的字段 ，但是表格上有非聚集索引但没有聚集索引

(4)表扫描：where后面的字段是没有建立索引的字段 并且表格上没有聚集索引和非聚集索引

(5)书签查找：where后面的字段建立了索引（不管是聚集索引还是非聚集索引），但是where前面返回的字段中有些没有建立索引

（不管是聚集索引还是非聚集索引）

 

个人意见：一般很少人会在建索引的时候在很多字段上建立索引不管是聚集索引还是非聚集索引，因为SQLSERVER维护这些索引

需要成本，需要付出代价

CREATE INDEX SalesOrderDetail_test_NCL_Price ON [dbo].[SalesOrderDetail_test]([UnitPrice]。。。N个表字段)
GO

可以把经常被返回的列放到索引的include里面去，在不增加索引键大小的情况下尽可能覆盖尽可能多的列

这样当遇到某些查询，没有用到组合索引的先导列，但又感觉不值得为其建立索引时，若此查询用到得字段被组合索引实现了索引覆盖

 

可以参考的文章 这两篇文章都是博客园里的园友写的：

听风听雨

SQL Server 索引中include的魅力（具有包含性列的索引）

Sql Server查询性能优化之创建合理的索引(下篇)

http://www.cnblogs.com/lzrabbit/archive/2012/07/03/2549558.html

 

 非聚集索引的存在必要性：

 个人觉得：如果可以为聚集索引指定包含性列，那么非聚集索引就没有什么用了。本人测试了一下

结果不能为聚集索引指定包含性列，应该是因为聚集索引把数据都存放到索引页

（上面提到在有聚集索引的表格上数据是直接存放在索引的最底层的）所以这些包含性索引列不能随意添加，

因为添加了会改变数据的存放顺序而非聚集索引就没有这个问题。

所以非聚集索引的存在还是很有必要的

 

 

 今晚继续写第三篇  o(∩_∩)o

第三篇地址：SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第三篇)