揭秘SQL Server 2000中的Bookmark Lookup–書籤尋找

來源:互聯網
上載者:User

           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有沒有建立叢集索引。

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

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

查詢效能比較:

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

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

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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.