Sql Server查詢效能最佳化之不可小覷的書籤尋找介紹

小小程式猿SQL Server認知的成長
1.沒畢業或工作沒多久，只知道有資料庫、SQL這麼個東東，渾然分不清SQL和Sql Server Oracle、MySql的關係，通常認為SQL就是SQL Server
2.工作好幾年了，也寫過不少SQL，卻渾然不知道索引為何物，只知道資料庫有索引這麼個東西，分不清叢集索引和非叢集索引，只知道查詢慢了建個索引查詢就快了，到頭來索引也建了不少，查詢也確實快了，偶然問之：汝建之索引為何類型？答曰：。。。
3.終於受到刺激開始奮發圖強，買書，gg查資料終於知道原來索引分為叢集索引和非叢集索引，頓時淚流滿面，嗚呼哀哉，吾終知索引為何物也。
4.再進一步學習之亦知叢集索引為物理索引、非叢集索引為邏輯索引，叢集索引為資料的儲存順序，非叢集索引是邏輯索引既對叢集索引的索引
5.再往後學會了查看執行計畫，通過查詢計劃終於對查詢過程有了大概瞭解，也知道了叢集索引掃描和表掃描沒有用到索引，看到叢集索引、索引尋找高興的眉飛色舞，看到RID、鍵尋找暗自竊喜，瞧，鍵尋找肯定就是關鍵字尋找了，用著索引呢，效率肯定高，於是每次寫完sql都要觀看下其執行計畫，表掃描的乾貨統統不要，俺只要索引尋找、鍵尋找。
6.自信滿滿的過著悠哉的小日子，突然有一天迷茫了，為嘛俺明明在這個欄位上建立了索引，它她妹的老給我顯示叢集索引掃描的，難道查詢最佳化工具發燒了，實際執行下，發現實際的執行計畫還是表掃描，這下徹底迷惑了，興許是查詢最佳化工具顯示的有問題吧。
7.繼續深入學習終發現，資料庫這潭水太深了，瞭解的太片面了，想想從猿到人的進化過程吧，恩恩，現在就是一個靈智初開的程式猿，向著偉大的程式員奮勇前進
恩恩，跑題了，進入我們的主題：資料庫的書籤尋找
認識書籤尋找
書籤尋找這個詞可能對於很多開發人員比較陌生，很多人都遇到過，但是卻沒引起足夠的重視以至於一直都忽略它的存在了
定義：當查詢最佳化工具使用非叢集索引進行尋找時，如果所選擇的列或查詢條件中的列只部分包含在使用的非叢集索引和叢集索引中時，就需要一個尋找（lookup）來檢索其他欄位來滿足請求。對一個有聚簇索引的表來說是一個鍵尋找（key lookup），對一個堆表來說是一個RID尋找（RID lookup），這種尋找即是——書籤尋找(bookmark lookup)。簡單的說就是當你使用的sql查詢條件和select返回的列沒有完全包含在索引列中時就會發生書籤尋找。
書籤尋找的重要性
1.書籤尋找發生條件：只有在使用非叢集索引進行資料尋找時才會產生書籤尋找，叢集索引尋找、叢集索引掃描和表掃描不會發生書籤尋找。
2.書籤尋找發生頻率：書籤尋找發生頻率非常高，甚至可以說大部分查詢都會發生書籤尋找，我們知道一個表只能建立一個叢集索引，所以我們的查詢更多的會使用非叢集索引，非叢集索引不可能覆蓋所有的查詢列，所以會經常性產生書籤尋找。
3.書籤尋找的影響：導致索引失效的主要原因之一。書籤尋找根據索引的行定位器從表中讀取資料，除了索引頁面的邏輯讀取外，還需要資料頁面的邏輯讀取，如果查詢的結果返回資料量較大會導致大量的邏輯讀或者索引失效，這也是為什麼我們查看查詢計劃時有時明明在查詢列上建立了索引，查詢最佳化工具卻依然使用表掃描的原因。
4.如何消除書籤尋找:
　　1.使用叢集索引尋找，叢集索引的葉子節點就是資料行本身，因此不存在書籤尋找
　　2.叢集索引掃描、表掃描，說白了就是啥索引都不建直接全表掃描，肯定不會發生書籤尋找，不過效率嗎。。。
　　3.使用非叢集索引的鍵列包含所有查詢或返回的列，這個不靠譜，非叢集索引最大鍵列數為16，最大索引鍵大小為900位元組，就算你有勇氣在16列上全部建立索引，那如果表的列數超過16列了你咋辦，還有索引列長度之和不能超過900位元組，所以不可能讓非叢集索引包含所有列，而且索引涉及到得列越多維護索引的開銷也就越大。
　　4.使用include，嗯，這是個好東東，索引做到只能包含16列且不能超過900位元組，include不受此限制，最多可以包含1023列怎麼也夠你用了，而且對長度也沒有限制你可以隨心所欲的包含nvarchar(max)這也的列，當然了text之流就不要考慮了
　　5.其它，其它還有神馬呢，這個我也不知道了，估計應該、可能、大概木有了吧，若有知道的兄弟可以告訴我聲哈

可能上面說的有點抽象，我們開看看具體的例子
一般我們的資料庫都會建上叢集索引(一般大家喜歡建表時有用沒有肯定先來個自增ID列當主鍵，這個主鍵SQL Server預設就給你建立成叢集索引了)，故我們這裡都假設表上已經建立了叢集索引，不考慮堆表(就是沒有叢集索引的表)

1.首先建立表Users、插入一些樣本資料並建立叢集索引PK_UserID 非叢集索引IX_UserName 複製代碼 代碼如下:--懶得的肥兔 --建立表Users
Create table Users
(
UserID int identity,
UserName nvarchar(50),
Age int,
Gender bit,
CreateTime datetime
)
--在UserID列建立叢集索引PK_UserID
create unique clustered index PK_UserID on Users(UserID)
--在UserName建立非叢集索引IX_UserName
create index IX_UserName on Users(UserName)

--插入樣本資料
insert into Users(UserName,Age,Gender,CreateTime)
select N'Bob',20,1,'2012-5-1'
union all
select N'Jack',23,0,'2012-5-2'
union all
select N'Robert',28,1,'2012-5-3'
union all
select N'Janet',40,0,'2012-5-9'
union all
select N'Michael',22,1,'2012-5-2'
union all
select N'Laura',16,1,'2012-5-1'
union all
select N'Anne',36,1,'2012-5-7'

2.執行以下查詢並查看查詢計劃，可以看到第一個SQL執行叢集索引掃描，第二個SQL執行叢集索引尋找都沒有使用到書籤尋找 複製代碼 代碼如下:select * from Users
select * from Users where UserID=4

3.比較以下幾個查詢SQL，觀察其查詢計劃，思考下為什麼會發生書籤尋找 複製代碼 代碼如下:--查詢1:使用索引IX_UserName，選擇列UserID,UserName，查詢條件列為UserName
select UserID,UserName from Users with(index(IX_UserName)) where UserName='Robert'

--查詢2:使用索引IX_UserName，選擇列UserID,UserName,Age，查詢條件列為UserName
select UserID,UserName,Age from Users with(index(IX_UserName)) where UserName='Robert'

--查詢3:使用索引IX_UserName，選擇列UserID,UserName，查詢條件列為UserName,Age
select UserID,UserName from Users with(index(IX_UserName)) where UserName='Robert' and Age=28

--查詢4:使用索引IX_UserName，選擇列所有列，查詢條件列為UserName
select * from Users with(index(IX_UserName)) where UserName='Robert'

分析:

　　查詢1:選擇的列UserID是叢集索引PK_UserID的鍵列,UserName為索引IX_UserName的鍵列，查詢條件列為UserName，由於索引IX_UserName包含了查詢用到得所有列，所以僅需要掃描索引即可返回查詢結果，不需要再額外的去資料頁擷取資料，故不會發生書籤尋找

　　查詢2:選擇列Age不包含在叢集索引PK_UserID和IX_UserName中，故需要進行額外的書籤尋找

　　查詢3:查詢條件Age列不包含在叢集索引PK_UserID和IX_UserName中，故需要進行額外的書籤尋找

　　查詢4:包含了所有的列，Age、Gender、CreateTime列均不在叢集索引PK_UserID和IX_UserName中，所以需要書籤尋找以定位元據

　這裡解釋下：查詢中用到的列無論是一列還是多列不在索引覆蓋範圍查詢開銷基本上一樣，每條記錄均只需要一次書籤尋找開銷，不會說因為查詢3隻有一個Age列，查詢4有Age、Gender、CreateTime 3列不在索引覆蓋範圍而導致額外的開銷　

分析:
　　查詢1:選擇的列UserID是叢集索引PK_UserID的鍵列,UserName為索引IX_UserName的鍵列，查詢條件列為UserName，由於索引IX_UserName包含了查詢用到得所有列，所以僅需要掃描索引即可返回查詢結果，不需要再額外的去資料頁擷取資料，故不會發生書籤尋找
　　查詢2:選擇列Age不包含在叢集索引PK_UserID和IX_UserName中，故需要進行額外的書籤尋找
　　查詢3:查詢條件Age列不包含在叢集索引PK_UserID和IX_UserName中，故需要進行額外的書籤尋找
　　查詢4:包含了所有的列，Age、Gender、CreateTime列均不在叢集索引PK_UserID和IX_UserName中，所以需要書籤尋找以定位元據

　這裡解釋下：查詢中用到的列無論是一列還是多列不在索引覆蓋範圍查詢開銷基本上一樣，每條記錄均只需要一次書籤尋找開銷，不會說因為查詢3隻有一個Age列，查詢4有Age、Gender、CreateTime 3列不在索引覆蓋範圍而導致額外的開銷　　

書籤尋找是怎麼發生的

和許多人一樣看到大神們畫的二叉樹索引結構圖就腦袋大，看得雲裡霧裡，所以這裡我們以表Users為例來說叢集索引(PK_UserID)和非叢集索引(IX_UserName)的結構可以簡單的表示為

首先我們來看叢集索引PK_UserID，對於叢集索引來說資料行就是其葉子節點，故當執行叢集索引尋找時找到了具體的索引值後就可以直接去葉子節點擷取所有需要的資料不需要進行額外的邏輯讀，比如select * from Users where UserID=2,根據值2在索引PK_UserID中找到UserID為2的值後去葉子節點就可以拿到所需資料，然後返回查詢結果

然後看非叢集索引IX_UserName，上面我們說過非叢集索引覆蓋的列為非叢集索引的鍵列+包含的列+叢集索引的鍵列,對於IX_UserName來說就是中所示鍵列UserName儲存在索引的二叉樹節點中，叢集索引的列包含在其葉子節點中，這也就形成了對列(UserName,UserID)的覆蓋，對於查詢1(select UserID,UserName from Users with(index(IX_UserName)) where UserName='Robert')來說查詢只用到了UserName,UserID列，這樣只需要掃描索引IX_UserName即可拿到所有資料然後進行結果返回，而對於查詢2、查詢3來說由於需要用到Age列，而索引IX_UserName中並沒有包含Age列，這時就需要個書籤尋找(bookmark lookup)根據分葉節點中的RowID去定位到具體的資料行擷取Age列值，對於樣本查詢來說先根據索引IX_UserName定位Robert所在行，然後根據RowID=3去資料表裡擷取Age值，然後完成查詢，對於查詢4來說需要更多的列(Age,Gender,CreateTime)，同樣定位到Robert所在行RowID=3，去資料表一次性拿到Age,Gender,CreateTime資料然後返回，這樣就形成了書籤尋找(查詢計劃中顯示為鍵尋找或RID尋找)

書籤尋找的對查詢效能的影響
--這是我們現在使用的索引create index IX_UserName on Users(UserName)

開啟IO統計並執行下面兩個查詢 複製代碼 代碼如下:--set statistics io onselect * from Users where UserName like 'ja%'select * from Users with(index(IX_UserName)) where UserName like 'ja%'

兩個查詢都返回2條資料，叢集索引掃描僅僅2次邏輯讀，使用索引IX_UserName卻達到了6次的邏輯讀

我們樣本的資料量比較小，所以感受不明顯，不過我們卻也看到了我們在UserName列上市建立了索引 IX_UserName，預設情況下查詢最佳化工具並沒有使用我們的索引，而是選擇了表掃描，僅僅需要2次邏輯讀就拿到了我們需要的資料，在我們使用索引提示強制查詢最佳化工具使用索引IX_UserName後，同樣也是返回2條資料，邏輯讀缺達到了驚人的6次，看查詢計劃使用IX_UserName後發生了書籤尋找，而這個開銷主要是有書籤尋找造成的，而且隨著我們返回資料量的增加，由書籤尋找導致的邏輯讀將會成直線上升，造成的結果就是查詢開銷比進行全表掃描還要大的多，最終導致索引失效

使用覆蓋索引避免書籤尋找

覆蓋索引是指非叢集索引上的列(鍵列+包含列) + 叢集索引的鍵列包含了查詢中用到的所有列，對於索引IX_UserName來說索引覆蓋列就是(UserName,UserID)。若查詢中只用到了索引所覆蓋的列，那麼只需掃描索引即可完成查詢，若用到了索引覆蓋範圍以外的列就需要書籤尋找來擷取資料，當這種尋找發生次較多時就會導致索引失效從而導致表掃描，因為查詢最佳化工具是基於開銷的最佳化器，當其發現使用非叢集索引引發的書籤尋找開銷比表掃描開銷還大時就會放棄使用索引，轉向表掃描。

1.在UserName,Age列上重建索引IX_UserName，這時對於索引IX_UserName來說覆蓋列變為(UserName,Age,UserID)，再次執行上面的查詢SQL可以發現查詢計劃已經發生變化 複製代碼 代碼如下:drop index IX_UserName on Userscreate index IX_UserName on Users(UserName,Age)

我們可以看到查詢2、查詢3的書籤尋找已經消失，因為索引IX_UserName包含了查詢中用到得所有列(UserID,UserName,Age)，查詢4因為選擇返回所有列我們的索引沒有包含Gender和CreateTime列，故還是會進行書籤尋找

這時索引IX_UserName結構表示如下

可見對於查詢2、查詢3僅僅通過索引IX_UserName既可以拿到需要的列UserName,Age,UserID，而對於查詢4索引並沒有全部覆蓋還是需要進行書籤尋找

2.繼續修改我們的索引IX_UserName，使用include包含非鍵列(鍵列就是索引上的列，非鍵列就是索引之外的列，對於include來說就是存放於非叢集索引葉子節點上的列,叢集索引的列也放在非叢集索引的葉子節點上) 複製代碼 代碼如下:drop index IX_UserName on Userscreate index IX_UserName on Users(UserName,Age) include(Gender,CreateTime)

可以看到我們修改索引使用include包含了Gender,CreateTime後，索引IX_UserName達到了對資料表Users的所有列的全覆蓋,這時候毫無疑問的查詢2、查詢3沒有出現書籤尋找，查詢4的書籤尋找也消失了。

此時索引IX_UserName 結構如下

索引IX_UserName已經達到了對Users表的全覆蓋，對於我們的查詢2、查詢3、查詢4來說，僅通過索引IX_UserName即可完成查詢，不需要進行書籤尋找。

這時我們再來看一下這兩個查詢的開銷及查詢計劃，可以看到不需要我們進行索引提示，查詢最佳化工具已經自動選擇了我們的索引，邏輯讀也降至了2次

select * from Users where UserName like 'ja%'select * from Users with(index(IX_UserName)) where UserName like 'ja%'

關於Include請參考 SQL Server 索引中include的魅力（具有包含性列的索引）

　　這裡說明下書籤尋找對查詢效能有著較大的影響並且基本上不可避免，這並不意味著書籤尋找就是洪水猛獸，原來我們不是也不知道啥叫書籤尋找麼，查詢效能一樣也不差，是吧，呵呵。書籤尋找也說明了為什麼我們不推薦寫sql時使用select *，也解釋了為什麼有時候我們的索引會失效，同時可以作為最佳化查詢效能考慮的一個方面，在設計表和索引時盡量規避書籤尋找帶來的負面影響，比如非叢集索引盡量選擇高選擇性的列即返回盡量少的行，需要大批量資料查詢時盡量使用叢集索引等。　　

　　本文中為了便於示範僅僅使用了有幾條資料的表，而且查詢中為了使用索引都用了索引提示，實際開發中請不要使用索引提示，查詢最佳化工具大多數情況下會為我們產生最優(最優不代表開銷最小，只要開銷足夠小即認為最優)的執行計畫，索引結構裡面用到得RowID也僅僅是為了示範虛構出來的，我們只要認為它是對於資料行的一個標識位就行了。

　　此文旨在讓我們認識書籤尋找並意識到書籤尋找的意義，從而對於索引失效原因有清晰的認識，更好的理解查詢計劃。