小小程序猿SQL Server認(rèn)知的成長(zhǎng)
1.沒(méi)畢業(yè)或工作沒(méi)多久，只知道有數(shù)據(jù)庫(kù)、SQL這么個(gè)東東，渾然分不清SQL和Sql Server Oracle、MySql的關(guān)系，通常認(rèn)為SQL就是SQL Server
2.工作好幾年了，也寫(xiě)過(guò)不少SQL，卻渾然不知道索引為何物，只知道數(shù)據(jù)庫(kù)有索引這么個(gè)東西，分不清聚集索引和非聚集索引，只知道查詢慢了建個(gè)索引查詢就快了，到頭來(lái)索引也建了不少，查詢也確實(shí)快了，偶然問(wèn)之：汝建之索引為何類型？答曰：。。。
3.終于受到刺激開(kāi)始奮發(fā)圖強(qiáng)，買書(shū)，gg查資料終于知道原來(lái)索引分為聚集索引和非聚集索引，頓時(shí)淚流滿面，嗚呼哀哉，吾終知索引為何物也。
4.再進(jìn)一步學(xué)習(xí)之亦知聚集索引為物理索引、非聚集索引為邏輯索引，聚集索引為數(shù)據(jù)的存儲(chǔ)順序，非聚集索引是邏輯索引既對(duì)聚集索引的索引
5.再往后學(xué)會(huì)了查看執(zhí)行計(jì)劃，通過(guò)查詢計(jì)劃終于對(duì)查詢過(guò)程有了大概了解，也知道了聚集索引掃描和表掃描沒(méi)有用到索引，看到聚集索引、索引查找高興的眉飛色舞，看到RID、鍵查找暗自竊喜，瞧，鍵查找肯定就是關(guān)鍵字查找了，用著索引呢，效率肯定高，于是每次寫(xiě)完sql都要觀看下其執(zhí)行計(jì)劃，表掃描的干貨統(tǒng)統(tǒng)不要，俺只要索引查找、鍵查找。
6.自信滿滿的過(guò)著悠哉的小日子，突然有一天迷茫了，為嘛俺明明在這個(gè)字段上建立了索引，它她妹的老給我顯示聚集索引掃描的，難道查詢優(yōu)化器發(fā)燒了，實(shí)際執(zhí)行下，發(fā)現(xiàn)實(shí)際的執(zhí)行計(jì)劃還是表掃描，這下徹底迷惑了，興許是查詢優(yōu)化器顯示的有問(wèn)題吧。
7.繼續(xù)深入學(xué)習(xí)終發(fā)現(xiàn)，數(shù)據(jù)庫(kù)這潭水太深了，了解的太片面了，想想從猿到人的進(jìn)化過(guò)程吧，恩恩，現(xiàn)在就是一個(gè)靈智初開(kāi)的程序猿，向著偉大的程序員奮勇前進(jìn)
恩恩，跑題了，進(jìn)入我們的主題：數(shù)據(jù)庫(kù)的書(shū)簽查找
認(rèn)識(shí)書(shū)簽查找
書(shū)簽查找這個(gè)詞可能對(duì)于很多開(kāi)發(fā)人員比較陌生，很多人都遇到過(guò)，但是卻沒(méi)引起足夠的重視以至于一直都忽略它的存在了
定義：當(dāng)查詢優(yōu)化器使用非聚集索引進(jìn)行查找時(shí)，如果所選擇的列或查詢條件中的列只部分包含在使用的非聚集索引和聚集索引中時(shí)，就需要一個(gè)查找（lookup）來(lái)檢索其他字段來(lái)滿足請(qǐng)求。對(duì)一個(gè)有聚簇索引的表來(lái)說(shuō)是一個(gè)鍵查找（key lookup），對(duì)一個(gè)堆表來(lái)說(shuō)是一個(gè)RID查找（RID lookup），這種查找即是——書(shū)簽查找(bookmark lookup)。簡(jiǎn)單的說(shuō)就是當(dāng)你使用的sql查詢條件和select返回的列沒(méi)有完全包含在索引列中時(shí)就會(huì)發(fā)生書(shū)簽查找。
書(shū)簽查找的重要性
1.書(shū)簽查找發(fā)生條件：只有在使用非聚集索引進(jìn)行數(shù)據(jù)查找時(shí)才會(huì)產(chǎn)生書(shū)簽查找，聚集索引查找、聚集索引掃描和表掃描不會(huì)發(fā)生書(shū)簽查找。
2.書(shū)簽查找發(fā)生頻率：書(shū)簽查找發(fā)生頻率非常高，甚至可以說(shuō)大部分查詢都會(huì)發(fā)生書(shū)簽查找，我們知道一個(gè)表只能建立一個(gè)聚集索引，所以我們的查詢更多的會(huì)使用非聚集索引，非聚集索引不可能覆蓋所有的查詢列，所以會(huì)經(jīng)常性產(chǎn)生書(shū)簽查找。
3.書(shū)簽查找的影響：導(dǎo)致索引失效的主要原因之一。書(shū)簽查找根據(jù)索引的行定位器從表中讀取數(shù)據(jù)，除了索引頁(yè)面的邏輯讀取外，還需要數(shù)據(jù)頁(yè)面的邏輯讀取，如果查詢的結(jié)果返回?cái)?shù)據(jù)量較大會(huì)導(dǎo)致大量的邏輯讀或者索引失效，這也是為什么我們查看查詢計(jì)劃時(shí)有時(shí)明明在查詢列上建立了索引，查詢優(yōu)化器卻依然使用表掃描的原因。
4.如何消除書(shū)簽查找:
1.使用聚集索引查找，聚集索引的葉子節(jié)點(diǎn)就是數(shù)據(jù)行本身，因此不存在書(shū)簽查找
2.聚集索引掃描、表掃描，說(shuō)白了就是啥索引都不建直接全表掃描，肯定不會(huì)發(fā)生書(shū)簽查找，不過(guò)效率嗎。。。
3.使用非聚集索引的鍵列包含所有查詢或返回的列，這個(gè)不靠譜，非聚集索引最大鍵列數(shù)為16，最大索引鍵大小為900字節(jié)，就算你有勇氣在16列上全部建立索引，那如果表的列數(shù)超過(guò)16列了你咋辦，還有索引列長(zhǎng)度之和不能超過(guò)900字節(jié)，所以不可能讓非聚集索引包含所有列，而且索引涉及到得列越多維護(hù)索引的開(kāi)銷也就越大。
4.使用include，嗯，這是個(gè)好東東，索引做到只能包含16列且不能超過(guò)900字節(jié)，include不受此限制，最多可以包含1023列怎么也夠你用了，而且對(duì)長(zhǎng)度也沒(méi)有限制你可以隨心所欲的包含nvarchar(max)這也的列，當(dāng)然了text之流就不要考慮了
5.其它，其它還有神馬呢，這個(gè)我也不知道了，估計(jì)應(yīng)該、可能、大概木有了吧，若有知道的兄弟可以告訴我聲哈
可能上面說(shuō)的有點(diǎn)抽象，我們開(kāi)看看具體的例子
一般我們的數(shù)據(jù)庫(kù)都會(huì)建上聚集索引(一般大家喜歡建表時(shí)有用沒(méi)有肯定先來(lái)個(gè)自增ID列當(dāng)主鍵，這個(gè)主鍵SQL Server默認(rèn)就給你創(chuàng)建成聚集索引了)，故我們這里都假設(shè)表上已經(jīng)建立了聚集索引，不考慮堆表(就是沒(méi)有聚集索引的表)
1.首先創(chuàng)建表Users、插入一些示例數(shù)據(jù)并建立聚集索引PK_UserID 非聚集索引IX_UserName
復(fù)制代碼 代碼如下:
--懶得的肥兔 --創(chuàng)建表Users
Create table Users
(
UserID int identity,
UserName nvarchar(50),
Age int,
Gender bit,
CreateTime datetime
)
--在UserID列創(chuàng)建聚集索引PK_UserID
create unique clustered index PK_UserID on Users(UserID)
--在UserName創(chuàng)建非聚集索引IX_UserName
create index IX_UserName on Users(UserName)
--插入示例數(shù)據(jù)
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.執(zhí)行以下查詢并查看查詢計(jì)劃，可以看到第一個(gè)SQL執(zhí)行聚集索引掃描，第二個(gè)SQL執(zhí)行聚集索引查找都沒(méi)有使用到書(shū)簽查找
復(fù)制代碼 代碼如下:
select * from Users
select * from Users where UserID=4
3.比較以下幾個(gè)查詢SQL，觀察其查詢計(jì)劃，思考下為什么會(huì)發(fā)生書(shū)簽查找
復(fù)制代碼 代碼如下:
--查詢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包含了查詢用到得所有列，所以僅需要掃描索引即可返回查詢結(jié)果，不需要再額外的去數(shù)據(jù)頁(yè)獲取數(shù)據(jù)，故不會(huì)發(fā)生書(shū)簽查找
查詢2:選擇列Age不包含在聚集索引PK_UserID和IX_UserName中，故需要進(jìn)行額外的書(shū)簽查找
查詢3:查詢條件Age列不包含在聚集索引PK_UserID和IX_UserName中，故需要進(jìn)行額外的書(shū)簽查找
查詢4:包含了所有的列，Age、Gender、CreateTime列均不在聚集索引PK_UserID和IX_UserName中，所以需要書(shū)簽查找以定位數(shù)據(jù)
這里解釋下：查詢中用到的列無(wú)論是一列還是多列不在索引覆蓋范圍查詢開(kāi)銷基本上一樣，每條記錄均只需要一次書(shū)簽查找開(kāi)銷，不會(huì)說(shuō)因?yàn)椴樵?只有一個(gè)Age列，查詢4有Age、Gender、CreateTime 3列不在索引覆蓋范圍而導(dǎo)致額外的開(kāi)銷
分析:
查詢1:選擇的列UserID是聚集索引PK_UserID的鍵列,UserName為索引IX_UserName的鍵列，查詢條件列為UserName，由于索引IX_UserName包含了查詢用到得所有列，所以僅需要掃描索引即可返回查詢結(jié)果，不需要再額外的去數(shù)據(jù)頁(yè)獲取數(shù)據(jù)，故不會(huì)發(fā)生書(shū)簽查找
查詢2:選擇列Age不包含在聚集索引PK_UserID和IX_UserName中，故需要進(jìn)行額外的書(shū)簽查找
查詢3:查詢條件Age列不包含在聚集索引PK_UserID和IX_UserName中，故需要進(jìn)行額外的書(shū)簽查找
查詢4:包含了所有的列，Age、Gender、CreateTime列均不在聚集索引PK_UserID和IX_UserName中，所以需要書(shū)簽查找以定位數(shù)據(jù)
這里解釋下：查詢中用到的列無(wú)論是一列還是多列不在索引覆蓋范圍查詢開(kāi)銷基本上一樣，每條記錄均只需要一次書(shū)簽查找開(kāi)銷，不會(huì)說(shuō)因?yàn)椴樵?只有一個(gè)Age列，查詢4有Age、Gender、CreateTime 3列不在索引覆蓋范圍而導(dǎo)致額外的開(kāi)銷
書(shū)簽查找是怎么發(fā)生的
和許多人一樣看到大神們畫(huà)的二叉樹(shù)索引結(jié)構(gòu)圖就腦袋大，看得云里霧里，所以這里我們以表Users為例來(lái)說(shuō)聚集索引(PK_UserID)和非聚集索引(IX_UserName)的結(jié)構(gòu)可以簡(jiǎn)單的表示為下圖
首先我們來(lái)看聚集索引PK_UserID，對(duì)于聚集索引來(lái)說(shuō)數(shù)據(jù)行就是其葉子節(jié)點(diǎn)，故當(dāng)執(zhí)行聚集索引查找時(shí)找到了具體的鍵值后就可以直接去葉子節(jié)點(diǎn)獲取所有需要的數(shù)據(jù)不需要進(jìn)行額外的邏輯讀，比如select * from Users where UserID=2,根據(jù)值2在索引PK_UserID中找到UserID為2的值后去葉子節(jié)點(diǎn)就可以拿到所需數(shù)據(jù)，然后返回查詢結(jié)果
然后看非聚集索引IX_UserName，上面我們說(shuō)過(guò)非聚集索引覆蓋的列為非聚集索引的鍵列+包含的列+聚集索引的鍵列,對(duì)于IX_UserName來(lái)說(shuō)就是如圖中所示鍵列UserName保存在索引的二叉樹(shù)節(jié)點(diǎn)中，聚集索引的列包含在其葉子節(jié)點(diǎn)中，這也就形成了對(duì)列(UserName,UserID)的覆蓋，對(duì)于查詢1(select UserID,UserName from Users with(index(IX_UserName)) where UserName='Robert')來(lái)說(shuō)查詢只用到了UserName,UserID列，這樣只需要掃描索引IX_UserName即可拿到所有數(shù)據(jù)然后進(jìn)行結(jié)果返回，而對(duì)于查詢2、查詢3來(lái)說(shuō)由于需要用到Age列，而索引IX_UserName中并沒(méi)有包含Age列，這時(shí)就需要個(gè)書(shū)簽查找(bookmark lookup)根據(jù)葉節(jié)點(diǎn)中的RowID去定位到具體的數(shù)據(jù)行獲取Age列值，對(duì)于示例查詢來(lái)說(shuō)先根據(jù)索引IX_UserName定位Robert所在行，然后根據(jù)RowID=3去數(shù)據(jù)表里獲取Age值，然后完成查詢，對(duì)于查詢4來(lái)說(shuō)需要更多的列(Age,Gender,CreateTime)，同樣定位到Robert所在行RowID=3，去數(shù)據(jù)表一次性拿到Age,Gender,CreateTime數(shù)據(jù)然后返回，這樣就形成了書(shū)簽查找(查詢計(jì)劃中顯示為鍵查找或RID查找)
書(shū)簽查找的對(duì)查詢性能的影響
--這是我們現(xiàn)在使用的索引create index IX_UserName on Users(UserName)
打開(kāi)IO統(tǒng)計(jì)并執(zhí)行下面兩個(gè)查詢
復(fù)制代碼 代碼如下:
--set statistics io onselect * from Users where UserName like 'ja%'select * from Users with(index(IX_UserName)) where UserName like 'ja%'
兩個(gè)查詢都返回2條數(shù)據(jù)，聚集索引掃描僅僅2次邏輯讀，使用索引IX_UserName卻達(dá)到了6次的邏輯讀
我們示例的數(shù)據(jù)量比較小，所以感受不明顯，不過(guò)我們卻也看到了我們?cè)赨serName列上市建立了索引 IX_UserName，默認(rèn)情況下查詢優(yōu)化器并沒(méi)有使用我們的索引，而是選擇了表掃描，僅僅需要2次邏輯讀就拿到了我們需要的數(shù)據(jù)，在我們使用索引提示強(qiáng)制查詢優(yōu)化器使用索引IX_UserName后，同樣也是返回2條數(shù)據(jù)，邏輯讀缺達(dá)到了驚人的6次，看查詢計(jì)劃使用IX_UserName后發(fā)生了書(shū)簽查找，而這個(gè)開(kāi)銷主要是有書(shū)簽查找造成的，而且隨著我們返回?cái)?shù)據(jù)量的增加，由書(shū)簽查找導(dǎo)致的邏輯讀將會(huì)成直線上升，造成的結(jié)果就是查詢開(kāi)銷比進(jìn)行全表掃描還要大的多，最終導(dǎo)致索引失效
使用覆蓋索引避免書(shū)簽查找
覆蓋索引是指非聚集索引上的列(鍵列+包含列) + 聚集索引的鍵列包含了查詢中用到的所有列，對(duì)于索引IX_UserName來(lái)說(shuō)索引覆蓋列就是(UserName,UserID)。若查詢中只用到了索引所覆蓋的列，那么只需掃描索引即可完成查詢，若用到了索引覆蓋范圍以外的列就需要書(shū)簽查找來(lái)獲取數(shù)據(jù)，當(dāng)這種查找發(fā)生次較多時(shí)就會(huì)導(dǎo)致索引失效從而導(dǎo)致表掃描，因?yàn)椴樵儍?yōu)化器是基于開(kāi)銷的優(yōu)化器，當(dāng)其發(fā)現(xiàn)使用非聚集索引引發(fā)的書(shū)簽查找開(kāi)銷比表掃描開(kāi)銷還大時(shí)就會(huì)放棄使用索引，轉(zhuǎn)向表掃描。
1.在UserName,Age列上重建索引IX_UserName，這時(shí)對(duì)于索引IX_UserName來(lái)說(shuō)覆蓋列變?yōu)?UserName,Age,UserID)，再次執(zhí)行上面的查詢SQL可以發(fā)現(xiàn)查詢計(jì)劃已經(jīng)發(fā)生變化
復(fù)制代碼 代碼如下:
drop index IX_UserName on Userscreate index IX_UserName on Users(UserName,Age)
我們可以看到查詢2、查詢3的書(shū)簽查找已經(jīng)消失，因?yàn)樗饕齀X_UserName包含了查詢中用到得所有列(UserID,UserName,Age)，查詢4因?yàn)檫x擇返回所有列我們的索引沒(méi)有包含Gender和CreateTime列，故還是會(huì)進(jìn)行書(shū)簽查找
這時(shí)索引IX_UserName結(jié)構(gòu)表示如下
可見(jiàn)對(duì)于查詢2、查詢3僅僅通過(guò)索引IX_UserName既可以拿到需要的列UserName,Age,UserID，而對(duì)于查詢4索引并沒(méi)有全部覆蓋還是需要進(jìn)行書(shū)簽查找
2.繼續(xù)修改我們的索引IX_UserName，使用include包含非鍵列(鍵列就是索引上的列，非鍵列就是索引之外的列，對(duì)于include來(lái)說(shuō)就是存放于非聚集索引葉子節(jié)點(diǎn)上的列,聚集索引的列也放在非聚集索引的葉子節(jié)點(diǎn)上)
復(fù)制代碼 代碼如下:
drop index IX_UserName on Userscreate index IX_UserName on Users(UserName,Age) include(Gender,CreateTime)
可以看到我們修改索引使用include包含了Gender,CreateTime后，索引IX_UserName達(dá)到了對(duì)數(shù)據(jù)表Users的所有列的全覆蓋,這時(shí)候毫無(wú)疑問(wèn)的查詢2、查詢3沒(méi)有出現(xiàn)書(shū)簽查找，查詢4的書(shū)簽查找也消失了。
此時(shí)索引IX_UserName 結(jié)構(gòu)如下
索引IX_UserName已經(jīng)達(dá)到了對(duì)Users表的全覆蓋，對(duì)于我們的查詢2、查詢3、查詢4來(lái)說(shuō)，僅通過(guò)索引IX_UserName即可完成查詢，不需要進(jìn)行書(shū)簽查找。
這時(shí)我們?cè)賮?lái)看一下這兩個(gè)查詢的開(kāi)銷及查詢計(jì)劃，可以看到不需要我們進(jìn)行索引提示，查詢優(yōu)化器已經(jīng)自動(dòng)選擇了我們的索引，邏輯讀也降至了2次
select * from Users where UserName like 'ja%'select * from Users with(index(IX_UserName)) where UserName like 'ja%'
關(guān)于Include請(qǐng)參考 SQL Server 索引中include的魅力（具有包含性列的索引）
這里說(shuō)明下書(shū)簽查找對(duì)查詢性能有著較大的影響并且基本上不可避免，這并不意味著書(shū)簽查找就是洪水猛獸，原來(lái)我們不是也不知道啥叫書(shū)簽查找么，查詢性能一樣也不差，是吧，呵呵。書(shū)簽查找也說(shuō)明了為什么我們不推薦寫(xiě)sql時(shí)使用select *，也解釋了為什么有時(shí)候我們的索引會(huì)失效，同時(shí)可以作為優(yōu)化查詢性能考慮的一個(gè)方面，在設(shè)計(jì)表和索引時(shí)盡量規(guī)避書(shū)簽查找?guī)?lái)的負(fù)面影響，比如非聚集索引盡量選擇高選擇性的列即返回盡量少的行，需要大批量數(shù)據(jù)查詢時(shí)盡量使用聚集索引等?！ ?
本文中為了便于演示僅僅使用了有幾條數(shù)據(jù)的表，而且查詢中為了使用索引都用了索引提示，實(shí)際開(kāi)發(fā)中請(qǐng)不要使用索引提示，查詢優(yōu)化器大多數(shù)情況下會(huì)為我們生成最優(yōu)(最優(yōu)不代表開(kāi)銷最小，只要開(kāi)銷足夠小即認(rèn)為最優(yōu))的執(zhí)行計(jì)劃，索引結(jié)構(gòu)里面用到得RowID也僅僅是為了演示虛構(gòu)出來(lái)的，我們只要認(rèn)為它是對(duì)于數(shù)據(jù)行的一個(gè)標(biāo)識(shí)位就行了。
此文旨在讓我們認(rèn)識(shí)書(shū)簽查找并意識(shí)到書(shū)簽查找的意義，從而對(duì)于索引失效原因有清晰的認(rèn)識(shí)，更好的理解查詢計(jì)劃。
您可能感興趣的文章:- SQL Server里書(shū)簽查找的性能傷害
