深度探索:Clustered Index Scan vs Table Scan .

有帖子http://topic.csdn.net/u/20080704/11/40975992-fcce-4807-bd65-b65cc0b39e09.html
提到索引扫描的问题，其中关tb上只建有聚集索引时，select * from tb到底是走聚集索引还是走iam，
还是有些疑虑，所以做了以下的测试，有经验的同学可以发表下意见。


环境: SQL 2005+SP2 ON Winxp SP3

1.
use tempdb
go

create table tb(a int primary key,b char(5000) default('bbbbb'))
go

declare @i int,@v int
set @i=1
while @i<=400
begin
reval:
	set @v=abs(checksum(newid())%1000)
	if exists(select * from tb where a=@v) 
		goto reval
	insert into tb(a) values (@v)
	set @i=@i+1
end
go

2.找出IAM、Root、IAM指向的第一页的页面位置

select [first],[root],firstIAM,* from sysindexes where id=object_id('tb')

我这里的值分别如下图:


--
转换成十进制後，IAM页是1:90;IAM指向的第1个数据页是1:77;索引根页是1:110

3.计算分别从IAM开始扫描和从ROOT开始扫描所应该得出的结果
[3.1] 从ROOT页开始扫描(Clustered Index Scan)
DBCC TRACEON(3604) --
DBCC PAGE(tempdb,1,110,3) --查看索引根页的内容
     结果如下图所示(图未截完)　


记好ChildPageID的值，稍後要按row的顺序依次检查ChildPageID的内容(数据页)
dbcc page(tempdb,1,434,1)
结果如下图，转换成十进制之后，a的值是7，即select * from tb order by a 返回的
第1行应该是7.

如次再往下检查下一个ChildPageID的内容(1:434)
并把检查a的结果记下来。


[3.2] 从IAM指向的第一页开始扫描(Table Scan)
DBCC TRACEON(3604)
DBCC PAGE(tempdb,1,90,3)--先查看iam页的内容，我的结果如下图


依次检查下面的Slot 0~Slot 7所指数据页的内容，就是mssql扫描的顺序

1:77  a的值是355
1:105 a的值是209
1:121 a的值是210
1:174 a的值是958
......

4.验证
[4.1]

	SELECT * FROM TB WITH (NOLOCK)
/*	
a           b
----------- -----------
355         bbbbb      
209         bbbbb      
210         bbbbb      
958         bbbbb      
662         bbbbb      
490         bbbbb      
190         bbbbb      
101         bbbbb      
302         bbbbb      
51          bbbbb      
935         bbbbb      
630         bbbbb  
*/

a的顺序符合[3.2]所记录的值吧,说明此时走的是iam，即Table Scan !

[4.2]

	SELECT * FROM TB 
/*
a           b
----------- ------------
7           bbbbb       
8           bbbbb       
9           bbbbb       
11          bbbbb       
13          bbbbb       
14          bbbbb       
15          bbbbb       
16          bbbbb       
17          bbbbb       
20          bbbbb       
21          bbbbb       
25          bbbbb       
26          bbbbb       
32          bbbbb       
33          bbbbb      

*/

可以见到a的值是有排序的，走的是聚集索引，结果应该是跟上面[3.1]记录的是一样.

5.结论

老实说，我被自己搞糊涂了。
我原本以为当聚集索引中的碎片很多时，SELECT * FROM tb 是会走iam扫描的，结果却不是，走的是索引扫描。
我用DBCC SHOWCONTIG(tb)查看索引碎片的值是98%,按理说不会去走index的，生成的执行计划
也是标了Ordered : FALSE(如下图)，但我试了很多次，结果看起来都是按a排序的。

然后我就试着加上一些查询提示，WITH INDEX啊什么的，当试到WITH (NOLOCK)时，结果终於不一样
验证后发现with (NOLOCK)就是走的iam扫描.

打开set statistics io on ，
发现select * from tb with (nolock)比select * from tb 少读取1个io，应该是略过了根索引页。




PS:下面的一段文字和图片都是来自於Inside SQL Server 2005这本书，说的是聚集索引扫描会走iam，这样看来是有问题的。
even though the execution plan shows a clustered index scan, the activity is no different than a table scan, and throughout the book I will often refer to it simply as a table scan. As shown in the illustration, here SQL Server will also use the index's IAM pages to scan the data sequentially. The information box of the Clustered Index Scan operator tells you that the scan was not ordered, meaning that the access method did not rely on the linked list that maintains the logical order of the index. 

36 个解决方案

#1

一大早码这么多字，累死了。
照例沙发自己抢 !

#2

先顶后看

#3

引用 1 楼 Garnett_KG 的回复:

一大早码这么多字，累死了。 
照例沙发自己抢 ! 

:)
辛苦鸟～

#4

嘿嘿，终于验证了我的思路。

#5

我喜欢这种讨论，能进步很多。
由衷的楼谢楼主写了这么多。

#6

看看，，，

#7

有空我也再测测

#8

来过

#9

最终结论是什么？另外，测试表的数据量太小了。

#10

学习

#11

是的，表的数据量有些小，不过有控制1个数据页只能放下1行资料，所以尽管只有400行数据，也是占到400个datapage的。
如果有兴趣，可以insert 更多的资料再去试一下，不过我想这并没有多大影响。


结论……嗯，怎么说呢，
SELECT * FROM tb  -- 一定会走 ROOT-->中间层(如果有)-->叶子层，数据是按聚集的主键排序

SELECT * FROM tb WITH (NOLOCK) --->一定会从IAM 开始提扫描，数据是按物理存储的顺序读取，乱序。


至於为什么加上with (nolock)后会有这样的结果，我也不知道。
有经验的同学或是dba可以上来现身说法一下。

#12

不过有控制1个数据页只能放下1行资料，所以尽管只有400行数据，也是占到400个datapage的。 
--------
在哪儿控制的？

#13

哦，没看见char(5000)

#14

  学习.楼主辛苦

#15

看看
，，，，

#16

分享一下:
　有一个这样的表：

　　Employees (EmployeeID,EmployeeName,Sex,Birthday,PhotoFile, 　　EnterDate, ProvinceID, CityID, Address, PostCode, IDCardNo) 。
　　其中EmployeeID为主键，并且按他建立了一个聚集索引PK_EmployeeID，在EmployeeName，Birthday，EnterDate，PostCode，IDCardNo上分别建立了非聚集索引IX_EmployeeName，IX_Birthday，IX_EnterDate，IX_PostCode，IX_IDCardNo。

　　如果我们用这样的一个语句进行查询：
　　Select * from Employees where EmployeeID=’C054965’
　　Select EmployeeID from Employees where EmployeeName=’刘永红’
　　则不会发生Bookmark Lookup，而如果用下面的语句，则会发生Bookupmark Lookup：
　　Select Sex from Employees where EmployeeName=’刘永红’

　　对照上面的语句，我们再回过头来看看照联机丛书中的解释。

　　“Bookmark Lookup逻辑运算符和物理运算符使用书签（行 ID 或聚集键）在表或聚集索引内查找相应的行。”

　　对于语句 select Sex from Employees where EmployeeName=’刘永红’，服务器先在非聚集索引IX_EmployeeName上找到与“刘永红”对应的行定位器——“C054965”，然后根据这个值在聚集索引PK_EmployeeID上找到与“C054965”对应的数据行，并返回Sex——“男”这个值。而我们用select EmployeeID from Employees where EmployeeName=’刘永红’时，因为EmployeeID包含于聚集索引PK_EmployeeID的键值中，所以，不用再进行Bookmark Lookup，而可以直接返回了。

　　但是对于select Sex from Employees where EmployeeName=’刘永红’ 就不同了，因为Sex并没有包含在PK_EmployeeID的键值中，也没有包含在EmployeeName的键值中，所以必须根据行定位器——“C054965”来进一步查找。

　　如果我们去掉聚集索引PK_EmployeeID，那么，服务器在执行Select Sex from Employees where EmployeeName=’刘永红’的时候，先在非聚集索引IX_EmployeeName上找到与“刘永红”对应的行定位器——指向EmployeeName=‘刘永红’的对应的数据行的指针，然后返回该行的Sex——“男”。

　　当然，如果我们执行select * from Employees where Sex=’男’，那么也不会发生Bookmark Lookup，而是直接的表扫描(Table Scan)了，不管表Employees有没有建立聚集索引。

　　从这里，我们可以得出一些有趣的结论：

　　在一个聚集表上使用非聚集索引进行查询，其性能低于在堆集上使用非聚集索引进行查询。

　　查询性能比较：
　　返回行数较多：索引覆盖>聚集索引>表扫描>堆集的非聚集索引>聚集的非聚集索引
　　返回行数较少：索引覆盖=聚集索引>堆集的非聚集索引>聚集的非聚集索引>表扫描
　　所以，了解表的存储结构对于我们编写高效率的查询和建立高效率的索引有非常重要的意义。 

#17

引用 15 楼 you_tube 的回复:

看看
，，，，

#18

up

#19

加上With (Nolock)后，意思是指告訴SQLServer不在乎讀到一些髒數據(未提交的數據)，所以用分配單元的掃描是更好的選擇。
通常情況下，在一個建有聚集索引的表上做Table Scan，速度是要慢於在堆表上的Table Scan。
當聚集索引的碎片很多時，在聚集表上做Table Scan速度要明顯快於Clustered index scan.
這個lz自己可以測試的到,將tb中的記錄數調到10000或是更多,然後再試。

另外，在SQL2000中，加上With (Nolock)或是With (Tablock)后，走的是Table Scan.
SQL2005則有所改變，當聚集表的數據量小於64個Data Page,則不管是否加with nolock,走的是Clustered index scan. 大於64個Data Page才會走Table scan.

#20

学习了

#21

看看 

#22

mark

#23

学习了

#24

在《Inside Microsoft SQL Server 2005 T-SQL Querying》一书中，作者讲解了Index Access Methods。
其中对于Unordered Clustered Index Scan的讲解确实是有不正确的地方，正如楼主所迷惑的。
此书出版不久，作者就在他的Blog上重新阐述了这个问题。

参考：
Quaere Verum - Clustered Index Scans - Part I 
http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html
Quaere Verum - Clustered Index Scans - Part II
http://www.sqlmag.com/Article/ArticleID/92887/sql_server_92887.html
Quaere Verum - Clustered Index Scans - Part III
http://www.sqlmag.com/Article/ArticleID/92888/sql_server_92888.html

#25

引用 19 楼 N_chow 的回复:

加上With (Nolock)后，意思是指告訴SQLServer不在乎讀到一些髒數據(未提交的數據)，所以用分配單元的掃描是更好的選擇。 
通常情況下，在一個建有聚集索引的表上做Table Scan，速度是要慢於在堆表上的Table Scan。 
當聚集索引的碎片很多時，在聚集表上做Table Scan速度要明顯快於Clustered index scan. 
這個lz自己可以測試的到,將tb中的記錄數調到10000或是更多,然後再試。 

另外，在SQL2000中，加上With (Nolock)或是Wi…

3Q.我再试下。

#26

引用 24 楼 shuiniu 的回复:

在《Inside Microsoft SQL Server 2005 T-SQL Querying》一书中，作者讲解了Index Access Methods。 
其中对于Unordered Clustered Index Scan的讲解确实是有不正确的地方，正如楼主所迷惑的。 
此书出版不久，作者就在他的Blog上重新阐述了这个问题。 

参考： 
Quaere Verum - Clustered Index Scans - Part I 
http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html 
Quaere Verum - Clustered Index …

谢谢提供资料.:)

#27

If you change the number of iteration to a small enough value (e.g., 100), the table size will be smaller than 64 pages and then SQL Server 2005 will perform an index order scan even when you specify the NOLOCK or TABLOCK hints.

#28

回来看看～

#29

强文，收藏一下。

#30

学习了。

#31

学习！

#32

强

#33

不懂,记录一下

#34

平时还真没有研究过这个问题 ，学习了

#35

嘿.占下位子

#36

强大，真的很强大，with（nolock）里面的学问真的不仅仅是忽略锁的问题，性能上确实有很大的提高，学习了。