SQLServer索引效率分析

王桃群

摘要：數(shù)據(jù)庫系統(tǒng)性能不夠理想的原因之一是存在大量性能低下的SQL語句，例如缺乏高效的索引，導(dǎo)致語句執(zhí)行速度慢以及大量的磁盤讀寫操作。解決這類問題的主要辦法是優(yōu)化這些沒有索引或索引不夠合理的SQL語句。該文以關(guān)系數(shù)據(jù)庫SQL Server為例，通過案例分析了索引的執(zhí)行效率。

關(guān)鍵詞：聚集索引；非聚集索引；覆蓋索引；索引掃描；索引查找

中圖分類號：TP311 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2017）26-0011-02

優(yōu)化SQL語句的關(guān)鍵在于盡可能減少語句的邏輯讀取次數(shù)。邏輯讀取次數(shù)越少，需要的內(nèi)存和CPU時間也就越少，語句的執(zhí)行速度也就越快。索引的最大好處就是可以極大地減少SQL語句的邏輯讀取次數(shù)，從而減少語句的執(zhí)行時間。邏輯讀取包含該語句從內(nèi)存數(shù)據(jù)緩沖區(qū)中訪問的頁數(shù)和從物理磁盤讀取的頁數(shù)，而物理讀取表示那些沒有駐留在內(nèi)存緩沖區(qū)中需要從磁盤讀取的數(shù)據(jù)頁。預(yù)讀是SQL Server為了提高性能而多讀取一些數(shù)據(jù)。優(yōu)化SQL語句時重在關(guān)注邏輯讀取次數(shù)，如果物理讀取或預(yù)讀的次數(shù)較大，往往意味著在磁盤IO上消耗更多的時間。通過SQL server中的set statistics io on命令可以查看SQL語句執(zhí)行時的邏輯讀?。╨ogical reads）、物理讀?。╬hysical reads）和預(yù)讀（Read-ahead reads）等信息。

1 單字段索引、組合索引和覆蓋索引

單字段索引是指索引中只包含一個字段；組合索引是指索引中包含有多個字段；覆蓋索引又稱為索引覆蓋，是指索引包含查詢引用的所有列。

（1） 單字段索引

對于單字段索引，若查詢語句的where子句中不包含索引字段，則系統(tǒng)會對整個表進(jìn)行掃描，全表掃描的性能通常都很差，尤其是在表中記錄很多的情況下。為此，可對在查詢中經(jīng)常用到的字段創(chuàng)建索引。這樣，全表掃描就變成了索引查找，通常情況下，索引查找需要的邏輯讀取次數(shù)比表掃描要少得多，從而提高查詢效率。

（2） 組合索引

如果查詢語句的where字句中有多個字段，可以考慮創(chuàng)建組合索引。組合索引中字段的順序是非常重要的，記錄唯一性高的盡量靠前，這樣可以降低索引掃描時的IO成本。

（3） 覆蓋索引

在SQL Server中，每一個從非聚集索引取回的行都可以查找聚集索引中剩余行的值，這個操作稱之為書簽查找（bookmark lookup）。書簽指向堆或聚集索引中的行。SQL Server嚴(yán)格地為非聚集索引中的每一行都存儲了書簽，這樣，在基本表中就可以找到非聚集索引所對應(yīng)的行。

覆蓋索引是指索引的葉子節(jié)點已包含所有要查詢的列，因此不需要訪問表數(shù)據(jù)，只需要訪問索引即可得到所有數(shù)據(jù)。聚集索引的葉子節(jié)點就是數(shù)據(jù)，不存在覆蓋的概念，覆蓋索引主要是針對非聚集索引。

查詢語句的查詢列表中含有不包含在非聚集索引中的字段，為得到一行數(shù)據(jù)至少需要兩次IO，一次訪問索引，一次訪問基本表。故而查詢列表中的選擇列應(yīng)盡可能選取索引覆蓋的列，這樣可以直接返回結(jié)果，避免昂貴的Bookmark Lookup操作，減少IO的次數(shù)，從而提高查詢的性能。

任何非聚集索引列都不需要覆蓋創(chuàng)建了聚集索引的列，因為創(chuàng)建聚集索引的列是非聚集索引集合列的一部分，也就是說，只要一個表上的列創(chuàng)建了聚集索引，那么非聚集索引集合列就包含了這個聚集索引。創(chuàng)建覆蓋索引時也不能包含過多的列，因為覆蓋索引列的值是存儲在內(nèi)存中的，這樣會消耗過多的內(nèi)存，也會導(dǎo)致性能下降。

2 表掃描、索引掃描和索引查找

SQL Server數(shù)據(jù)庫主要有3種方法查找數(shù)據(jù)，分別是表掃描（Table scan）、索引掃描（Index scan）和索引查找（Index seek）。

（1） 索引查找（Index seek）：SQL Server 沿著索引樹從根級向下進(jìn)行索引鍵值匹配搜索，直到搜索到指定的行，然后使用存儲在索引鍵值中的書簽值直接從數(shù)據(jù)頁中檢索匹配的數(shù)據(jù)行。

（2） 索引掃描（Index scan）：SQL Server搜索索引樹中所有葉級中的行來進(jìn)行可能匹配的查找。如果發(fā)現(xiàn)滿足匹配的行，然后利用書簽檢索數(shù)據(jù)行。

（3） 表掃描：SQL Server數(shù)據(jù)庫從數(shù)據(jù)表，可能是堆表或者聚集索引的葉子頁從頭到尾進(jìn)行掃描來查找數(shù)據(jù)。如果是數(shù)據(jù)表上已建立聚集索引，此時相當(dāng)于索引掃描。

盡管前兩者都使用了索引，從I/O代價角度來講，索引掃描比索引查找的代價要高，但比表掃描要略微要小些。

3 案例分析

下面通過一個案例來演示索引的執(zhí)行情況。首先，創(chuàng)建一個測試表Test，該表包含三個int類型的字段，對于表中每個元組，這三個字段的取值相同。接著，分別在這三個字段上建立聚集索引、非聚集索引和無索引。然后，通過不同的查詢語句來查看語句執(zhí)行時的邏輯讀取次數(shù)、物理讀取次數(shù)、預(yù)讀次數(shù)和執(zhí)行時間等信息。為獲取語句的執(zhí)行時間，可在語句執(zhí)行之前先獲取系統(tǒng)當(dāng)前時間，語句執(zhí)行完畢后再次獲取系統(tǒng)當(dāng)前時間，兩次系統(tǒng)當(dāng)前時間之差即為語句執(zhí)行所需的時間。測試代碼如下。

語句執(zhí)行前設(shè)置set statistics io on并開啟執(zhí)行計劃。上述四條查詢語句的執(zhí)行結(jié)果如圖1所示。

從圖1可見，有索引的語句花費的時間極短，無索引的語句花費的時間相對較長，差距明顯。對于邏輯讀取次數(shù)，無索引語句的邏輯讀取次數(shù)是有索引語句的很多倍，這正是語句執(zhí)行效率差異的直接原因。限于SQL Server時間類型的精度（3毫秒），覆蓋索引在執(zhí)行時間上看不到差異，但無覆蓋索引的邏輯讀取次數(shù)是有索引的2倍，隨著表中記錄數(shù)的增加和查詢的復(fù)雜，差距會更加明顯。

上述四條查詢語句的執(zhí)行計劃如圖2所示。

從圖2可見，包括無索引查詢在內(nèi)的每個查詢都使用了索引。不同之處在于，盡管無索引查詢中使用了表中的聚集索引，但是對這個聚集索引使用的是索引掃描，其他幾個語句均使用的是索引查找。索引掃描是從開頭掃描到結(jié)尾，幾乎等同于表掃描。索引查找會先定位到索引的局部位置，然后再開始查找，不用把整個索引全部掃描一遍，在效率上比索引掃描快很多。

4 結(jié)束語

合理地使用索引能顯著提高語句的執(zhí)行效率，除了應(yīng)該回避不適合建立索引的原則以外，索引并不是越多越好。因為索引要占用表空間，還需要額外的維護(hù)，這對數(shù)據(jù)庫而言都是巨大的負(fù)擔(dān)。建立索引是典型的以空間換時間的做法，在設(shè)計索引時需要在空間與時間兩者間進(jìn)行適當(dāng)權(quán)衡。

參考文獻(xiàn)：

[1] 對SQL Server數(shù)據(jù)庫中三種查找數(shù)據(jù)方案的比較. http：//database.51cto.com/art/201007/209964.htm

[2] 趙愛濤.合理創(chuàng)建索引 實現(xiàn)數(shù)據(jù)庫訪問的優(yōu)化.石家莊職業(yè)技術(shù)學(xué)院學(xué)報，2013，25（4）：24-26.endprint