揭秘SQL Server 2000中的Bookmark Lookup

Matthewl

Bookmark Lookup是什么呢？在SQL Server2000中的联机丛书中是这样说的：“Bookmark Lookup逻辑运算符和物理运算符使用书签（行 ID 或聚集键）在表或聚集索引内查找相应的行。Argument 列包含书签标志，用于在表或聚集索引内查找行。Argument 列还包含要查找的行所在的表或聚集索引的名称。如果 WITH PREFETCH 子句出现在 Argument 列内，则表示查询处理器已决定在表或聚集索引内查找书签时最好使用异步预提取（预读）。”看了这样的解释，还是不明不白。后来通过查找资料，终于明白了Bookmark Lookup是什么了，什么时候会发生Bookmark Lookup，他对查询的性能有什么样的影响，并如何避免Bookup Lookup。现与大家共享。如果有什么错误，也请不吝赐教。如无特殊说明，本文中的Sql Server均指Sql Server 2000。
要弄清楚Bookmark Lookup，需从Sql Server索引和表的存储架构入手。Sql Server的表使用如下两种方法组织其数据页：
聚集表：聚集表就是具有聚集索引的表，它基于聚集索引键按顺序存储数据行，索引按B树索引结构实现。B树基于聚集索引键值对行进行快速检索。每级索引的页链接在双向链表中，但使用键值在各级间进行导航。数据行本身构成聚集索引的最低级别。
堆集：堆集是没有聚集索引的表，不按任何特殊顺序存储数据行。数据页不在链表内链接。非聚集索引有一个与聚集索引中相似的B树索引结构，但是他对数据行的顺序不起作用，其最低行包含非聚集索引的键值，每个键值项都有指针指向包含该键值的数据行。对于堆集，该指针是指向行的指针，对于聚集表，则是聚集索引键。该指针叫做行定位器。
SQL Server 的数据文件中有一类是IAM，即索引分配映射表，它存储有关表和索引所使用的扩展盘区信息。一个堆集在sysindexes内有一行，其indid=0。FirstIAM列指向指向表的数据页集合的IAM链。服务器使用IAM页查找数据页集合内的页。通过扫描IAM页，可以对堆集进行表扫描或串行读，以找到这个堆集的页的扩展盘区。所以，对于没有任何索引的堆集，不管做什么样的查询，服务器都必须对对表进行一次扫描。哪怕只返回一行，其IO数都是一样的，即表的行数。
某个表和视图的聚集索引在sysindexes内有一行，其indid=1。root列指向聚集索引B树的顶端。服务器使用B树查找数据页。SQL Server沿着聚集索引浏览以找到聚集索引键对应的行。为找到键的范围，SQL Server浏览索引以找到这个范围的起始值，然后用向前或向后页扫描数据页。为找到数据页链的页首，SQL Server从索引的根节点开始沿着最左边的指针进行扫描。所以，如果用聚集索引查找数据，如果只返回一行，那么其IO数，就是B树的顶端到键值所在数据行的深度，简记为D。如果返回多行，则需要再加上符合条件的页数，简记为P。总的IO数为D+P。
某个表或视图的非聚集索引在索引在sysindexes内也有一行，其indid值从2到250，root列指向非聚集索引B树的顶端。SQL Server在查找数据时，服务器先使用和使用聚集索引相同的查找方法找到该索引的行定位器——Bookmark，然后通过行定位器来找到所需要的数据，这种通过行定位器查找数据的方式就是Bookmark Lookup。如果索引所在的表是堆集，那么Sql Server使用行指针来找到数据。所以，这种情况下，返回1行的IO数是找到行定位器为止的B树的深度D+1。而如果返回多行，则IO数为D+所有妈祖条件的索引页的页数P+返回行数H。如果索引所在的表是聚集，那么Sql Server使用聚集索引的键来找到数据。所以，这种情况下，返回1行的IO数是找到行定位其为止的B树的深度D+找到聚集索引的键的B树的深度D1。返回多行的IO数则为D+P+H*D1。
在基于非聚集索引查找数据时，还有另外一种情形，那就是如果放回的数据列就包含于索引的键值中，或者包含于索引的键值+聚集索引的键值中，那么就不会发生Bookup Lookup，因为找到索引项，就已经找到所需的数据了，没有必要再到数据行去找了。这种情况，叫做索引覆盖。
好了，现在我们以实例说明。
有一个这样的表：
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有没有建立聚集索引。
从这里，我们可以得出一些有趣的结论：
在一个聚集表上使用非聚集索引进行查询，其性能低于在堆集上使用非聚集索引进行查询。
查询性能比较：
返回行数较多：索引覆盖>聚集索引>表扫描>堆集的非聚集索引>聚集的非聚集索引
返回行数较少：索引覆盖=聚集索引>堆集的非聚集索引>聚集的非聚集索引>表扫描
所以，了解表的存储结构对于我们编写高效率的查询和建立高效率的索引有非常重要的意义。
参考文章：Bookmark Lookups

http://www.google.com/logos/Logo_25wht.gif	输入您的搜索字词  提交搜索表单
	[size=-1]Web [size=-1]yahong111.iyunv.com