Sql Server 查詢性能優(yōu)化之走出索引的誤區(qū)分析

據(jù)了解絕大多數(shù)開發(fā)人員對(duì)于索引的理解都是一知半解，局限于大多數(shù)日常工作沒有機(jī)會(huì)、也什么沒有必要去關(guān)心、了解索引，實(shí)在哪天某個(gè)查詢太慢了找到查詢條件建個(gè)索引就ok，哪天又有個(gè)查詢慢了，再建立個(gè)索引就是，或者干脆把整個(gè)查詢SQL直接發(fā)給DBA，讓DBA直接幫忙優(yōu)化了，所以造成的狀況就是開發(fā)人員對(duì)于索引的理解、認(rèn)識(shí)很局限，以下就把我個(gè)人對(duì)于索引的理解及淺薄認(rèn)識(shí)和大家分享下，希望能解除一些大家的疑惑，一起走出索引的誤區(qū)

誤區(qū)1.在表上建立了索引，在查詢時(shí)用到了索引的列，索引就一定會(huì)生效
　　首先明確下這樣的觀點(diǎn)是錯(cuò)誤的，SQL Server查詢優(yōu)化器是基于開銷進(jìn)行選擇的優(yōu)化器，通過一系列復(fù)雜判斷來決定是否使用索引、使用什么類型索引、使用那個(gè)索引。SQL Server內(nèi)部維護(hù)著索引列上的數(shù)據(jù)的統(tǒng)計(jì)，統(tǒng)計(jì)信息會(huì)隨著索引列內(nèi)容的變化而變化，索引的有效期完全取決于索引列上的統(tǒng)計(jì)信息，隨著數(shù)據(jù)的變化關(guān)于索引的檢索機(jī)制也隨之變化。對(duì)于查詢優(yōu)化器來說始終保持查詢開銷最低始終是其的不二選擇，如果一個(gè)非聚集索引的列上有大量的重復(fù)值，那么這個(gè)索引就不會(huì)有什么存在的意義，這也是為什么不建議在類似性別，bit類型上面建立非聚集索引的原因。

　　說到這里可能會(huì)有人疑惑，我在性別列上建一個(gè)索引，性別只有兩個(gè)值男、女，當(dāng)我我們查詢條件中有性別這個(gè)字段時(shí)最起碼會(huì)過濾掉一半的數(shù)據(jù)，能大幅縮小我們需要檢索的數(shù)據(jù)范圍，怎么會(huì)沒用呢？(事實(shí)上這也是我曾經(jīng)困惑的地方)，對(duì)我們理解的沒錯(cuò)，比如說Users表性別列Gender上建立索引IX_Gender，執(zhí)行select Gender from Users where Gender='男' ，這個(gè)查詢效率非常高而且也成功使用了索引IX_Gender，然而我們這樣寫SQL的時(shí)候少之又少，更多的我們會(huì)寫這樣的SQL:select UserID,UserName,Phone,Email from Users where Gender='男' 這時(shí)再去看看查詢計(jì)劃根本沒用使用索引IX_Gender，而是進(jìn)行了一個(gè)聚集索引掃描或者表掃描，查詢條件where Gender='男' 明明在IX_Gender里面定義了，為什么沒使用呢，這一切罪惡的根源就在于書簽查找(RID、鍵查找)，好了關(guān)于書簽查找不是我們要討論的話題，在這里只想告訴大家，索引不是萬能的，索引不是創(chuàng)建了就一定有效。

誤區(qū)2.聚集索引掃描用到了聚集索引索引，所以性能很高
　　一般來說我們可以認(rèn)為聚集索引是效率最高的索引，但聚集索引掃描絕不代表高效，本質(zhì)上聚集索引掃描就是表掃描，一般出現(xiàn)掃描字樣時(shí)代表缺少索引或者索引無效，所以我們?nèi)粘?yīng)用中應(yīng)該避免在查詢計(jì)劃中看到掃描字樣，更多的出現(xiàn)聚集索引查找、索引查找才真正的使用到了索引，才是王道。

誤區(qū)3.聚集索引掃描(表掃描)是全表掃描，所以只要出現(xiàn)了表掃描就一定代表性能低下
　　在誤區(qū)2中我們說到應(yīng)該盡量避免出現(xiàn)聚集索引掃描或者表掃描，這是我們必須要堅(jiān)持的原則，但這并不代表這出現(xiàn)表掃描就一定性能低下，有些情況下表掃描反而比索引查找有著更高的效率(一般出現(xiàn)在返回?cái)?shù)據(jù)量較大，出現(xiàn)大量書簽查找的情況下)

誤區(qū)4.查詢計(jì)劃中看到了鍵查找或者RID查找時(shí)有著很高的性能
　　鍵查找和RID查找統(tǒng)稱為書簽查找，和錯(cuò)誤認(rèn)識(shí)正好相反，出現(xiàn)書簽查找反而代表著性能低下，有些情況下甚至有著比表掃描更低的效率，因此我們應(yīng)該盡量避免書簽查找。在返回?cái)?shù)據(jù)量較小時(shí)，書簽查找對(duì)性能影響不大，若返回?cái)?shù)據(jù)量較大，書簽查找會(huì)嚴(yán)重影響查詢性能，因此我們建立索引時(shí)應(yīng)該盡量覆蓋要返回的所有列，當(dāng)然索引列數(shù)是有限的而且也不能單純的為了避免書簽查找而在索引中包含大量的列，可以使用覆蓋索引來解決書簽查找問題，或者需要大數(shù)據(jù)量返回時(shí)盡量使用聚集索引；同時(shí)這也是為什么常聽說的不要使用select *,而只選擇需要的列進(jìn)行輸出，因?yàn)閟elect *很容易導(dǎo)致書簽查找，畢竟我們不打可能在所有列上建立索引，也不可能所有查詢都使用聚集索引(使用聚集索引和表掃描時(shí)不存在書簽查找)

誤區(qū)5.查詢開銷統(tǒng)計(jì)中的邏輯讀次數(shù)是讀取的記錄數(shù)
　　天真的我曾經(jīng)也這么認(rèn)為，查詢計(jì)劃中邏輯讀次數(shù)就是讀取的記錄數(shù)，然而看我們的查詢4.1全表掃描返回830行數(shù)據(jù)，為啥邏輯讀只有22次，而查詢4.5同樣是返回830行數(shù)據(jù)，邏輯讀為啥1724次呢，一次讀取一條的話邏輯讀22次最多返回22行數(shù)據(jù)，邏輯讀1724次的話應(yīng)該返回1724條數(shù)據(jù)吧，有點(diǎn)小暈，這里解釋下邏輯讀次數(shù)是指讀取的頁(yè)面數(shù)，一個(gè)面8KB，8個(gè)頁(yè)面構(gòu)成一個(gè)區(qū)64KB，對(duì)于我們的示例表來說22個(gè)頁(yè)面足以存下所有數(shù)據(jù)，所以表掃描時(shí)只需讀取22次就可以了，那查詢4.5為啥讀取了1724次呢，就算一個(gè)頁(yè)面就一條數(shù)據(jù)按理說最多800多次也可以讀取完畢了，這是因?yàn)镾ql Server對(duì)數(shù)據(jù)讀取的最小單位就是頁(yè)，哪怕讀取一條數(shù)據(jù)也需要讀取整頁(yè)數(shù)據(jù)，而非聚集索引的讀是隨機(jī)讀哪怕多條記錄在同一頁(yè)上也會(huì)導(dǎo)致多次重復(fù)讀取，外加書簽查找導(dǎo)致了這么多的邏輯讀，這也是為什么非聚集索引不適合讀取大量數(shù)據(jù)的原因之一。


我們以Northwind數(shù)據(jù)庫(kù)表Orders表為示例進(jìn)行下演示

　1.先將Orders表的索引全部刪除
　4.在OrderID上面創(chuàng)建聚集索引，索引列為OrderID　　

復(fù)制代碼 代碼如下:

create unique clustered index IX_OrderID on Orders(OrderID)

3.在Orders表上創(chuàng)建非聚集索引IX_OrderDate

create index IX_OrderDate on Orders(OrderDate)
4.設(shè)置查詢分析器選中包含實(shí)際的執(zhí)行計(jì)劃（右鍵-->包含實(shí)際的執(zhí)行計(jì)劃），打開IO統(tǒng)計(jì)，并依次執(zhí)行以下查詢

復(fù)制代碼 代碼如下:

set statistics io on
select * from Orders
select * from Orders where OrderDate='1996-7-10'
select * from Orders where OrderDate='1997-1-1'

--強(qiáng)制使用索引IX_OrderDate 查詢?nèi)掌?997-1-1
select * from Orders with(index=IX_OrderDate) where OrderDate='1997-1-1'

--強(qiáng)制使用索引IX_OrderDate查詢?nèi)?000-1-1
select * from Orders with(index=IX_OrderDate) where OrderDate='2001-1-1'

4.1 執(zhí)行 select * from Orders 的查詢開銷及查詢計(jì)劃
　　　　可以看到執(zhí)行的聚集索引掃描，邏輯讀22次，沒有使用索引，返回行數(shù)830行
　　　　

　　4.2 執(zhí)行 select * from Orders  where OrderDate='1996-7-10' 的查詢開銷借查詢計(jì)劃
　　　　可以看到成功使用了在OrderDate上面建立的索引IX_OrderDate，邏輯讀次數(shù)為14，返回行數(shù)6行

　　

　　4.3 執(zhí)行 select * from Orders  where OrderDate='1997-1-1' 的查詢開銷及查詢計(jì)劃
　　　　可以看到雖然我們?cè)贠rderDate上面建立了索引IX_OrderDate，但執(zhí)行計(jì)劃并沒有使用索引IX_OrderDate而是執(zhí)行了一個(gè)聚集索引掃描，邏輯讀次數(shù)22而這個(gè)查詢與4.2的區(qū)別僅僅在于OrderDate的值不一樣，返回行數(shù)154行
　　
　　4.4 執(zhí)行 select * from Orders with(index=IX_OrderDate)  where OrderDate='1997-1-1' 的查詢開銷及查詢計(jì)劃
　　　　可以看到查詢條件和4.3完全一致，我們強(qiáng)制使用了IX_OrderDate，返回記錄數(shù)和4.3完全一致，但邏輯讀達(dá)到了328次，返回行數(shù)154行
　　　　
　　　　

　　4.5 執(zhí)行 select * from Orders with(index=IX_OrderDate)  where OrderDate='2001-1-1' 查詢開銷及查詢計(jì)劃

　　　　同樣我們強(qiáng)制使用了索引IX_OrderDate，查詢條件進(jìn)行改變，邏輯讀達(dá)到了1724次，返回行數(shù)數(shù)830行
　　　　

　　　　

 

 通過對(duì)比以上查詢我們可以知道雖然我們建立了索引，但索引并不總是有效，強(qiáng)制使用索引只會(huì)帶來更低的效率，查詢優(yōu)化器會(huì)根據(jù)索引列的統(tǒng)計(jì)信息自動(dòng)選擇最優(yōu)的查詢計(jì)劃進(jìn)行執(zhí)行。查詢4.3和4.4查詢條件完全一樣，雖然我們建立了索引IX_OrderDate，但查詢優(yōu)化器并沒有采用而是選擇了開銷更低的聚集索引掃描，在我們強(qiáng)制使用了索引后查詢開銷反而激增從邏輯讀22次達(dá)到了328次，而我們僅僅查詢到了154行數(shù)據(jù)；在查詢4.5中我們繼續(xù)強(qiáng)制使用索引，改變查詢條件的值，在返回830行數(shù)據(jù)的情況下邏輯讀次數(shù)達(dá)到了1724次，而返回相同數(shù)據(jù)的查詢4.1僅僅執(zhí)行了22次邏輯讀。

　　困惑：通過查詢4.1我們知道Orders表一共才有830條數(shù)據(jù)，為什么我們?cè)诓樵?.5中強(qiáng)制使用索引后邏輯讀達(dá)到了恐怖的1724次呢，即便一條數(shù)據(jù)讀取一次也才不過830次啊。

　　解惑：查詢4.5強(qiáng)制使用索引后，查詢優(yōu)化器首先去到索引IX_OrderDate上面檢索，然后在根據(jù)索引IX_OrderDate去找聚集索引指針，根據(jù)聚集索引指針去聚簇索引葉子節(jié)點(diǎn)(實(shí)際數(shù)據(jù)行)查找數(shù)據(jù)(書簽查找)，才導(dǎo)致了更大的查詢開銷。

　　結(jié)論：
　　　　1.索引不是萬能的，查詢列上建立了索引不代表就一定會(huì)使用索引(參見結(jié)論2)
　　　　2.絕大多數(shù)情況下查詢優(yōu)化器會(huì)根據(jù)索引列上的數(shù)據(jù)統(tǒng)計(jì)信息自動(dòng)選擇最優(yōu)的執(zhí)行計(jì)劃，而且查詢計(jì)劃會(huì)隨著數(shù)據(jù)量變化而變化，所以如果不是有必要不要使用索引提示來強(qiáng)制使用某索引
　　　　3.聚集索引掃描、表掃描不代表一定低效(表掃描不存在書簽查找，使用非聚集索引返回大量行時(shí)，若存在書簽查找反而不如表掃描性能高)
　　　　4.索引查找不一定高效(非聚集索引查找時(shí)容易出現(xiàn)書簽查找)
　　　　5.書簽查找會(huì)降低查詢效率，尤其是大范圍讀取數(shù)據(jù)時(shí)會(huì)嚴(yán)重影響效率，所以應(yīng)該盡量避免書簽查找或出現(xiàn)書簽查找時(shí)盡量返回較少的數(shù)據(jù)行
　　　　6.需要注意下查詢開銷統(tǒng)計(jì)里的邏輯讀是指讀取的頁(yè)面數(shù)而不是數(shù)據(jù)行數(shù)

　示例中采用的語(yǔ)句及數(shù)據(jù)僅作為演示使用，實(shí)際開發(fā)應(yīng)用中要比示例的數(shù)據(jù)復(fù)雜的多，同一個(gè)查詢?cè)诓煌沫h(huán)境下可能產(chǎn)生完全相反的結(jié)果，如何應(yīng)用好還主要在于我們個(gè)人的認(rèn)識(shí)和理解，希望有幸看到本文的朋友能借此加深一些對(duì)索引的理解和認(rèn)識(shí)，走出索引的誤區(qū)，開發(fā)出高性能的應(yīng)用。

　　本人不是DBA，只是一名普通的開發(fā)人員，以上均為實(shí)際工作中的一些經(jīng)驗(yàn)、體會(huì)，鑒于本人水平非常有限，有說的不對(duì)或理解不到位的地方還望各位大神給予指正，以免誤導(dǎo)他人，不勝感激。

后續(xù)會(huì)繼續(xù)寫一些關(guān)于Sql Server查詢性能優(yōu)化方面的實(shí)踐經(jīng)驗(yàn)，主要包含以下幾方面
Sql Server查詢性能優(yōu)化之建立合理的索引
Sql Server查詢性能優(yōu)化之避免書簽查找
Sql Server查詢性能優(yōu)化之復(fù)用查詢計(jì)劃
Sql Server查詢性能優(yōu)化之選擇合適的字段類型 

附上用的數(shù)據(jù)表：DemoDB.rar

從Northwind數(shù)據(jù)庫(kù)分離出來的，僅用了其中的Orders表

此文章屬懶惰的肥兔原創(chuàng)