Sql Server 查詢機能優化之走出索引的誤辨別析。本站提示廣大學習愛好者：（Sql Server 查詢機能優化之走出索引的誤辨別析）文章只能為提供參考，不一定能成為您想要的結果。以下是Sql Server 查詢機能優化之走出索引的誤辨別析正文
據懂得絕年夜多半開辟人員關於索引的懂得都是一知半解，局限於年夜多半平常任務沒無機會、也甚麼沒有需要去關懷、懂得索引，其實哪天某個查詢太慢了找到查詢前提建個索引就ok，哪天又有個查詢慢了，再樹立個索引就是，或許爽性把全部查詢SQL直接發給DBA，讓DBA直接協助優化了，所以形成的狀態就是開辟人員關於索引的懂得、熟悉很局限，以下就把我小我關於索引的懂得及淺陋熟悉和年夜家分享下，願望能消除一些年夜家的困惑，一路走出索引的誤區
誤區1.在表上樹立了索引，在查詢時用到了索引的列，索引就必定會失效
起首明白下如許的不雅點是毛病的，SQL Server查詢優化器是基於開支停止選擇的優化器，經由過程一系列龐雜斷定來決議能否應用索引、應用甚麼類型索引、應用誰人索引。SQL Server外部保護著索引列上的數據的統計，統計信息會跟著索引列內容的變更而變更，索引的有用期完整取決於索引列上的統計信息，跟著數據的變更關於索引的檢索機制也隨之變更。關於查詢優化器來講一直堅持查詢開支最低一直是其的不貳選擇，假如一個非集合索引的列上有年夜量的反復值，那末這個索引就不會有甚麼存在的意義，這也是為何不建議在相似性別，bit類型下面樹立非集合索引的緣由。
說到這裡能夠會有人困惑，我在性別列上建一個索引，性別只要兩個值男、女，當我我們查詢前提中有性別這個字段時最最少會過濾失落一半的數據，能年夜幅減少我們須要檢索的數據規模，怎樣會沒用呢？(現實上這也是我已經迷惑的處所)，對我們懂得的沒錯，好比說Users表性別列Gender上樹立索引IX_Gender，履行select Gender from Users where Gender='男' ，這個查詢效力異常高並且同樣成功應用了索引IX_Gender，但是我們如許寫SQL的時刻少之又少，更多的我們會寫如許的SQL:select UserID,UserName,Phone,Email from Users where Gender='男' 這時候再去看看查詢籌劃基本沒用應用索引IX_Gender，而是停止了一個集合索引掃描或許表掃描，查詢前提where Gender='男' 明明在IX_Gender外面界說了，為何沒應用呢，這一切罪行的本源就在於書簽查找(RID、鍵查找)，好了關於書簽查找不是我們要評論辯論的話題，在這裡只想告知年夜家，索引不是全能的，索引不是創立了就必定有用。
誤區2.集合索引掃描用到了集合索引索引，所以機能很高
普通來講我們可以以為集合索引是效力最高的索引，但集合索引掃描毫不代表高效，實質上集合索引掃描就是表掃描，普通湧現掃描字樣時期表缺乏索引或許索引有效，所以我們平常運用中應當防止在查詢籌劃中看到掃描字樣，更多的湧現集合索引查找、索引查找才真實的應用到了索引，才是霸道。
誤區3.集合索引掃描(表掃描)是全表掃描，所以只需湧現了表掃描就必定代表機能低下
在誤區2中我們說到應當盡可能防止湧現集合索引掃描或許表掃描，這是我們必需要保持的准繩，但這其實不代表這湧現表掃描就必定機能低下，有些情形下表掃描反而比索引查找有著更高的效力(普通湧現在前往數據量較年夜，湧現年夜量書簽查找的情形下)
誤區4.查詢籌劃中看到了鍵查找或許RID查找時有著很高的機能
鍵查找和RID查找統稱為書簽查找，和毛病熟悉正好相反，湧現書簽查找反而代表著機能低下，有些情形下乃至有著比表掃描更低的效力，是以我們應當盡可能防止書簽查找。在前往數據量較小時，書簽查找對機能影響不年夜，若前往數據量較年夜，書簽查找會嚴重影響查詢機能，是以我們樹立索引時應當盡可能籠罩要前往的一切列，固然索引列數是無限的並且也不克不及純真的為了不書簽查找而在索引中包括年夜量的列，可使用籠罩索引來處理書簽查找成績，或許須要年夜數據量前往時盡可能應用集合索引；同時這也是為何常據說的不要應用select *,而只選擇須要的列停止輸入，由於select *很輕易招致書簽查找，究竟我們不打能夠在一切列上樹立索引，也弗成能一切查詢都應用集合索引(應用集合索引和表掃描時不存在書簽查找)
誤區5.查詢開支統計中的邏輯讀次數是讀取的記載數
無邪的我已經也這麼以為，查詢籌劃中邏輯讀次數就是讀取的記載數，但是看我們的查詢4.1全表掃描前往830行數據，為啥邏輯讀只要22次，而查詢4.5異樣是前往830行數據，邏輯讀為啥1724次呢，一次讀取一條的話邏輯讀22次最多前往22行數據，邏輯讀1724次的話應當前往1724條數據吧，有點小暈，這裡說明下邏輯讀次數是指讀取的頁面數，一個面8KB，8個頁面組成一個區64KB，關於我們的示例表來講22個頁面足以存下一切數據，所以表掃描時只需讀取22次便可以了，那查詢4.5為啥讀取了1724次呢，就算一個頁面就一條數據按理說最多800屢次也能夠讀取終了了，這是由於Sql Server對數據讀取的最小單元就是頁，哪怕讀取一條數據也須要讀取整頁數據，而非集合索引的讀是隨機讀哪怕多筆記錄在統一頁上也會招致屢次反復讀取，外加書簽查找招致了這麼多的邏輯讀，這也是為何非集合索引不合適讀取年夜量數據的緣由之一。
我們以Northwind數據庫表Orders表為示例停止下演示
1.先將Orders表的索引全體刪除
4.在OrderID下面創立集合索引，索引列為OrderID
create unique clustered index IX_OrderID on Orders(OrderID)
3.在Orders表上創立非集合索引IX_OrderDate
create index IX_OrderDate on Orders(OrderDate)
4.設置查詢剖析器選中包括現實的履行籌劃（右鍵-->包括現實的履行籌劃），翻開IO統計，並順次履行以下查詢
set statistics io on
select * from Orders
select * from Orders where OrderDate<='1996-7-10'
select * from Orders where OrderDate<='1997-1-1'
--強迫應用索引IX_OrderDate 查詢日期1997-1-1
select * from Orders with(index=IX_OrderDate) where OrderDate<='1997-1-1'
--強迫應用索引IX_OrderDate查詢日2000-1-1
select * from Orders with(index=IX_OrderDate) where OrderDate<='2001-1-1'
4.1 履行 select * from Orders 的查詢開支及查詢籌劃
可以看到履行的集合索引掃描，邏輯讀22次，沒有應用索引，前往行數830行
4.2 履行 select * from Orders where OrderDate<='1996-7-10' 的查詢開支借查詢籌劃
可以看到勝利應用了在OrderDate下面樹立的索引IX_OrderDate，邏輯讀次數為14，前往行數6行
4.3 履行 select * from Orders where OrderDate<='1997-1-1' 的查詢開支及查詢籌劃
可以看到固然我們在OrderDate下面樹立了索引IX_OrderDate，但履行籌劃並沒有應用索引IX_OrderDate而是履行了一個集合索引掃描，邏輯讀次數22而這個查詢與4.2的差別僅僅在於OrderDate的值紛歧樣，前往行數154行
4.4 履行 select * from Orders with(index=IX_OrderDate) where OrderDate<='1997-1-1' 的查詢開支及查詢籌劃
可以看到查詢前提和4.3完整分歧，我們強迫應用了IX_OrderDate，前往記載數和4.3完整分歧，但邏輯讀到達了328次，前往行數154行
4.5 履行 select * from Orders with(index=IX_OrderDate) where OrderDate<='2001-1-1' 查詢開支及查詢籌劃
異樣我們強迫應用了索引IX_OrderDate，查詢前提停止轉變，邏輯讀到達了1724次，前往行數數830行
查詢統計
查詢SQL
索引
前往行數
邏輯讀次數
4.1 select * from Orders
集合索引掃描
830
22
4.2 select * from Orders where OrderDate<='1996-7-10'
IX_OrderDate
6
14
4.3 select * from Orders where OrderDate<='1997-1-1'
集合索引掃描
154
22
4.4 select * from Orders with(index=IX_OrderDate) where OrderDate<='1997-1-1'
強迫應用IX_OrderDate
154
328
4.5 select * from Orders with(index=IX_OrderDate) where OrderDate<='2001-1-1'
強迫應用IX_OrderDate
830
1724
經由過程比較以上查詢我們可以曉得固然我們樹立了索引，但索引其實不老是有用，強迫應用索引只會帶來更低的效力，查詢優化器會依據索引列的統計信息主動選擇最優的查詢籌劃停止履行。查詢4.3和4.4查詢前提完整一樣，固然我們樹立了索引IX_OrderDate，但查詢優化器並沒有采取而是選擇了開支更低的集合索引掃描，在我們強迫應用了索引後查詢開支反而激增從邏輯讀22次到達了328次，而我們僅僅查詢到了154行數據；在查詢4.5中我們持續強迫應用索引，轉變查詢前提的值，在前往830行數據的情形下邏輯讀次數到達了1724次，而前往雷同數據的查詢4.1僅僅履行了22次邏輯讀。
迷惑：經由過程查詢4.1我們曉得Orders表一共才有830條數據，為何我們在查詢4.5中強迫應用索引後邏輯讀到達了恐懼的1724次呢，即使一條數據讀取一次也才不外830次啊。
解惑：查詢4.5強迫應用索引後，查詢優化器起首去到索引IX_OrderDate下面檢索，然後在依據索引IX_OrderDate去找集合索引指針，依據集合索引指針去聚簇索引葉子節點(現實數據行)查找數據(書簽查找)，才招致了更年夜的查詢開支。
結論：
1.索引不是全能的，查詢列上樹立了索引不代表就必定會應用索引(拜見結論2)
2.絕年夜多半情形下查詢優化器會依據索引列上的數據統計信息主動選擇最優的履行籌劃，並且查詢籌劃會跟著數據質變化而變更，所以假如不是有需要不要應用索引提醒來強迫應用某索引
3.集合索引掃描、表掃描不代表必定低效(表掃描不存在書簽查找，應用非集合索引前往年夜量行時，若存在書簽查找反而不如表掃描機能高)
4.索引查找紛歧定高效(非集合索引查找時輕易湧現書簽查找)
5.書簽查找會下降查詢效力，特別是年夜規模讀取數據時會嚴重影響效力，所以應當盡可能防止書簽查找或湧現書簽查找時盡可能前往較少的數據行
6.須要留意下查詢開支統計裡的邏輯讀是指讀取的頁面數而不是數據行數
示例中采取的語句及數據僅作為演示應用，現實開辟運用中要比示例的數據龐雜的多，統一個查詢在分歧的情況下能夠發生完整相反的成果，若何運用好還重要在於我們小我的熟悉和懂得，願望有幸看到本文的同伙能借此加深一些對索引的懂得和熟悉，走出索引的誤區，開辟出高機能的運用。
自己不是DBA，只是一位通俗的開辟人員，以上均為現實任務中的一些經歷、領會，鑒於自己程度異常無限，有說的纰謬或懂得不到位的處所還望列位年夜神賜與斧正，以避免誤導別人，不堪感謝。
後續會持續寫一些關於Sql Server查詢機能優化方面的理論經歷，重要包括以下幾方面
Sql Server查詢機能優化之樹立公道的索引
Sql Server查詢機能優化之防止書簽查找
Sql Server查詢機能優化之復用查詢籌劃
Sql Server查詢機能優化之選擇適合的字段類型
附上用的數據表：DemoDB.rar
從Northwind數據庫分別出來的，僅用了個中的Orders表
此文章屬懶散的肥兔原創
