[zt] 揭祕SQL Server 2000中的Bookmark Lookup

要弄清楚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有沒有建立聚集索引。

從這裡，我們可以得出一些有趣的結論：

在一個聚集表上使用非聚集索引進行查詢，其效能低於在堆集上使用非聚集索引進行查詢。

查詢效能比較：

返回行數較多：索引覆蓋>聚集索引>表掃描>堆集的非聚集索引>聚集的非聚集索引

返回行數較少：索引覆蓋=聚集索引>堆集的非聚集索引>聚集的非聚集索引>表掃描

所以，瞭解表的儲存結構對於我們編寫高效率的查詢和建立高效率的索引有非常重要的意義。 

 

轉載自   http://www.cnblogs.com/Yahong111/archive/2007/09/13/891641.html    

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-609154/，如需轉載，請註明出處，否則將追究法律責任。