SQL Server 查詢性能優(yōu)化——覆蓋索引

時間：2019-01-15 09:56:01

關鍵字： server sql 性能優(yōu)化

手機看文章

掃描二維碼
隨時隨地手機看文章

[導讀]覆蓋索引又可以稱為索引覆蓋。　　解釋一：?就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取，換句話說查詢列要被所使用的索引覆蓋?！　〗忉尪?索引是高效找到行的一個方法，當能通過檢索

覆蓋索引又可以稱為索引覆蓋。
　　解釋一：?就是select的數(shù)據(jù)列只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取，換句話說查詢列要被所使用的索引覆蓋。
　　解釋二：?索引是高效找到行的一個方法，當能通過檢索索引就可以讀取想要的數(shù)據(jù)，那就不需要再到數(shù)據(jù)表中讀取行了。如果一個索引包含了（或覆蓋了）滿足查詢語句中字段與條件的數(shù)據(jù)就叫做覆蓋索引。
　　解釋三：?是非聚集組合索引的一種形式，它包括在查詢里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆蓋查詢語句[select子句]與查詢條件[Where子句]中所涉及的字段，也即，索引包含了查詢正在查找的所有數(shù)據(jù)）。

首先，從聚集索引說起，聚集索引實際上就是一個覆蓋索引，在大多數(shù)情況下，可以很直觀地分辨出數(shù)據(jù)表的當前聚集索引是否有用，因為聚集索引根據(jù)鍵值字段控制了數(shù)據(jù)行的順序。由于SQL?SERVER以聚集索引的鍵值字段來排序數(shù)據(jù)行，所以當你經(jīng)常需要對某些字段排序時，把這些要排序的字段作為聚集索引的鍵值，創(chuàng)建聚集索引將對查詢性能會有很大的提升。因為數(shù)據(jù)已經(jīng)照聚集索引的鍵值字段的順序排序，所以查詢執(zhí)行時不需要額外的排序操作。同時如果使用聚集索引來查找同條記錄的其他字段的數(shù)據(jù)，SQL?SERVER也不需要額外地通過指針檢索數(shù)據(jù)，因為在聚集索引找到索引鍵值的同時就已經(jīng)找到整條數(shù)據(jù)。

聚集索引在檢索符合某個范圍的數(shù)據(jù)時也很有用。例如，你想要找到所有銷售訂單編號介于18000-19999的訂單，而聚集索引就是通過銷售訂單編號字段建立的，相近的記錄全部會擺放在一起，則訪問的分頁當然就比較少，通過聚集索引可快速定位包含起始銷售訂單編號的行，然后檢索表中所有連續(xù)的行，直到檢索到最后的銷售訂單號。

聚集索引在檢索占總行數(shù)比例很大的數(shù)據(jù)行時也比較有用。

下圖是使用聚集索引查找數(shù)據(jù)的示意圖。

使用聚集索引的好處在于：

?? 1）所需要的數(shù)據(jù)都在子葉層(即數(shù)據(jù)頁)，找到正確的索引鍵值后不需要再利用指針做額外的查找

?? 2）SQL?SERVER將符合相同條件的數(shù)據(jù)集中放在一起

其次，非聚集索引。非聚集索引結構如下圖。

如果想要使建立的非聚集索引同時具備以上兩種好處，那就要建立非聚集覆蓋索引。通過覆蓋索引，所有查詢想要的數(shù)據(jù)字段都是索引鍵值的一部分，而存放在索引的子葉層級。覆蓋索引不僅僅只包含你寫在WHERE條件內的字段，而且還包含所有SELECT?需要的字段，以及在GROUP?BY?或ORDER?BY?子句內的字段。

例：

Select?

在SQL?SERVER?2000中我們建立覆蓋索引采用以下方式

Create?index?idx?on?T(C,A,B)?

建立組合索引時，字段的順序很重要，要將條件字段C放在組合索引的第一位，把它做為在索引的上層結構的主要排序對象，且僅有它包含統(tǒng)計數(shù)據(jù)，也就是非子葉層查找出符合的記錄，然后在存放有其他字段記錄的子葉層讀取所需要的數(shù)據(jù)。

但是由于字段A,B兩列也會在索引的非子葉層出現(xiàn)，除非WHERE條件是多個字段，或多個字段排序，否則索引非子葉層放在其他數(shù)據(jù)字段用處不大，徒增索引數(shù)據(jù)量，減低索引性能。

在SQL?SERVER?2005可以采用以下方式：

Create?index?idx?on?T(C)?INCLUDE(A,B...)

為了增強覆蓋索引的功能以提升查詢效率，SQL?SERVER?2005?在Create?Index語句中提供INCLUDE參數(shù)，將與鍵值列無關的數(shù)據(jù)表其他字段添加到非聚集索引的子葉層，擴展非聚集索引的功能，但這些字段值不做排序等額外的維護動作。在查詢時僅讀取索引結構就可得到所有相關的數(shù)據(jù)，不訪問表或聚集索引的數(shù)據(jù)，從而減少磁盤?I/O?操作，減少讀取數(shù)據(jù)表本身所花的資源。SQL?SERVER?的組合索引最多只能有16個字段，而這些添加到索引子葉層中的相關字段并不計算在這16個字段中。

另外，當查詢優(yōu)化程序在該索引中可以發(fā)現(xiàn)處理查詢所需要的數(shù)據(jù)，則雖然組合索引的第一個字段不在WHERE條件內，但查詢優(yōu)化程序仍有可能采取適用的組合索引。或是當查詢語句沒有WHERE條件，但組合索引覆蓋了所有需要的字段時，則直接掃描索引的子葉層獲取數(shù)據(jù)而不是通過掃描數(shù)據(jù)表查找數(shù)據(jù)。

示例：

create??index?idx_WBK_PDE_LIST_ORG_HISTROY?on?[WBK_PDE_LIST_ORG_HISTROY]([WBOOK_NO])?include([QTY_1],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT])

則索引idx_WBK_PDE_LIST_ORG_HISTROY結構中，包含了以WBOOK_NO鍵值順序為主要排序對象的上層結構，以及包含數(shù)據(jù)表內所有WBOOK_NO與[QTY_1],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]字段內容的子葉層。如下圖。

接下來我用一個實例來解釋一下，上圖中的情況，我們來看看下面這條SQL語句在SQL執(zhí)行引擎中是如何執(zhí)行的：

SELECT??G_NO,?UNIT_1?FROM?[WBK_PDE_LIST_ORG_HISTROY]?WHERE?[COP_G_NO]=?'BENNET'

　　1)?[WBK_PDE_LIST_ORG_HISTROY]?表在[COP_G_NO]列上有一個非聚集索引，因此它查找非聚集索引的根節(jié)點中找出[COP_G_NO]=?'BENNET'的記錄。上圖中1）

　　2)?從包含[COP_G_NO]=?'BENNET'記錄的索引中間節(jié)點中找到指向該記錄的子葉層頁號。上圖中2）

　　3)?從索引的子葉層中針對每一行數(shù)據(jù)(假設這里有100條)獲取書簽（由數(shù)據(jù)庫物理文件編號，對應的Page頁碼，對應的行號組成），SQL?Server引擎通過書簽查找從聚集索引或數(shù)據(jù)表中找出真實的行在對應頁面中的位置。上圖中3）

　　4)?SQL?Server引擎從對應的行查找?G_NO和UNIT_1?列的值。

　　在上面的步驟中，對[COP_G_NO]=?'BENNET'的所有數(shù)據(jù)(這里是100條記錄)，SQL?Server引擎要搜索100次聚集索引或數(shù)據(jù)表以檢索查詢中指定的其它列(?G_NO,?UNIT_1?)。

　　如果非聚集索引頁中包括了查詢語句中所需要的數(shù)據(jù)列(COP_G_NO，G_NO,?UNIT_1)的值，SQL?Server引擎可能不會執(zhí)行上面的第3和4步，直接從非聚集索引中查找[COP_G_NO]列速度還會快一些，直接從索引的子葉層讀取這三列的數(shù)值。

　　本文將具體講一下使用不同索引對查詢性能的影響。

　　下面通過實例，來查看不同的索引結構，如聚集索引、非聚集索引、組合索引等來查看相同的SQL語句查詢的不同性能

例一：沒有任何索引的查詢訪問

　　1.表的碎片情況：

　　2.SQL查詢語句與查詢執(zhí)行計劃成本

--要求返回IO統(tǒng)計，也就是數(shù)據(jù)頁訪問的數(shù)量
SET?STATISTICS?IO?ON
--沒有任何索引情況下的數(shù)據(jù)頁訪問數(shù)量
SELECT??[WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT]
??FROM?[WBK_PDE_LIST_ORG_HISTROY]?where?qty_1?between?50?and?500

--表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數(shù)1，邏輯讀取1568?次，物理讀取54?次，預讀1568?次，lob?邏輯讀取0?次，lob?物理讀取0?次，lob?預讀?0?次。

SET?STATISTICS?IO?OFF

例二：通過聚集索引查詢訪問

　　1.聚集索引的碎片情況：

　　2.SQL查詢語句與查詢執(zhí)行計劃成本　　

--要求返回IO統(tǒng)計，也就是數(shù)據(jù)分頁訪問的數(shù)量
SET?STATISTICS?IO?ON
---通過聚集索引查詢訪問的數(shù)據(jù)頁數(shù)量
create?clustered?index?idx_WBK_PDE_LIST_ORG_HISTROY?on?[WBK_PDE_LIST_ORG_HISTROY](QTY_1)
SELECT?[WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT]
??FROM?[WBK_PDE_LIST_ORG_HISTROY]?where?qty_1?between?50?and?500

--表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數(shù)1，邏輯讀取351?次，物理讀取4?次，預讀345?次，lob?邏輯讀取0?次，lob?物理讀取0?次，lob?預讀0?次。

SET?STATISTICS?IO?OFF
---
drop?index?[WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY

---

例三：強制通過非聚集索引查詢訪問

　　1.非聚集索引的碎片情況：

　　2.SQL查詢語句與查詢執(zhí)行計劃成本

--要求返回IO統(tǒng)計，也就是數(shù)據(jù)頁訪問的數(shù)目
SET?STATISTICS?IO?ON

--強制通過非聚集索引查詢訪問的數(shù)據(jù)頁數(shù)量，用錯索引比不用索引更糟糕
create??index?idx_WBK_PDE_LIST_ORG_HISTROY?on?[WBK_PDE_LIST_ORG_HISTROY](WBOOK_NO)

SELECT?[WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT]
??FROM?[WBK_PDE_LIST_ORG_HISTROY]?with?(index(idx_WBK_PDE_LIST_ORG_HISTROY))?where?qty_1?between?50?and?500

--表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數(shù)1，邏輯讀取61065?次，物理讀取864?次，預讀727?次，lob?邏輯讀取0?次，lob?物理讀取0?次，lob?預讀0?次。

SET?STATISTICS?IO?OFF
---
drop?index?[WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY

例四：通過字段順序不適用的覆蓋索引查詢訪問
　　1.非聚集索引的碎片情況：

　　2.SQL查詢語句與查詢執(zhí)行計劃成本

--要求返回IO統(tǒng)計，也就是數(shù)據(jù)頁訪問的數(shù)量
SET?STATISTICS?IO?ON

--通過字段順序不適用的覆蓋索引查詢訪問的數(shù)據(jù)頁數(shù)量
create??index?idx_WBK_PDE_LIST_ORG_HISTROY?on?[WBK_PDE_LIST_ORG_HISTROY]([WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT])

SELECT?[WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT]
??FROM?[WBK_PDE_LIST_ORG_HISTROY]?where?qty_1?between?50?and?500

--表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數(shù)1，邏輯讀取687?次，物理讀取9?次，預讀683?次，lob?邏輯讀取0?次，lob?物理讀取0?次，lob?預讀0?次。

SET?STATISTICS?IO?OFF

---
drop?index?[WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY

例五：通過覆蓋索引查詢訪問

　　1.非聚集索引的碎片情況：

　　2.SQL查詢語句與查詢執(zhí)行計劃成本

--要求返回IO統(tǒng)計，也就是數(shù)據(jù)頁訪問的數(shù)量
SET?STATISTICS?IO?ON

--通過覆蓋索引查詢訪問的數(shù)據(jù)頁數(shù)量
create??index?idx_WBK_PDE_LIST_ORG_HISTROY?on?[WBK_PDE_LIST_ORG_HISTROY]([QTY_1]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[WBOOK_NO]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT])

SELECT??[WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT]
??FROM?[WBK_PDE_LIST_ORG_HISTROY]?where?qty_1?between?50?and?500

--表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數(shù)1，邏輯讀取178?次，物理讀取5?次，預讀175?次，lob?邏輯讀取0?次，lob?物理讀取0?次，lob?預讀0?次。

SET?STATISTICS?IO?OFF

---
drop?index?[WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY

例六：通過字段順序不適用的覆蓋索引查詢訪問

　　1.聚集索引的碎片情況：

　　2.SQL查詢語句與查詢執(zhí)行計劃成本

--要求返回IO統(tǒng)計，也就數(shù)據(jù)頁訪問的數(shù)量
SET?STATISTICS?IO?ON

---通過字段順序不適用的覆蓋索引查詢訪問的數(shù)據(jù)頁數(shù)量
create??index?idx_WBK_PDE_LIST_ORG_HISTROY?on?[WBK_PDE_LIST_ORG_HISTROY]([WBOOK_NO])?include(qty_1
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]???????????????????
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT])

SELECT??[WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT]
??FROM?[WBK_PDE_LIST_ORG_HISTROY]?where?qty_1?between?50?and?500

--表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數(shù)1，邏輯讀取682?次，物理讀取1?次，預讀492?次，lob?邏輯讀取0?次，lob?物理讀取0?次，lob?預讀0?次。

SET?STATISTICS?IO?OFF

---
drop?index?[WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY

例七：通過子葉層覆蓋索引查詢訪問(INCLUDE)

　　1.聚集索引的碎片情況：

　　2.SQL查詢語句與查詢執(zhí)行計劃成本

--要求返回IO統(tǒng)計，也就是數(shù)據(jù)頁訪問的數(shù)量
SET?STATISTICS?IO?ON

--通過子葉層覆蓋索引查詢訪問的數(shù)據(jù)頁數(shù)量
create??index?idx_WBK_PDE_LIST_ORG_HISTROY?on?[WBK_PDE_LIST_ORG_HISTROY](qty_1)?include([WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]???
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT])

SELECT??[WBOOK_NO]
??????,[COP_G_NO]
??????,[G_NO]?????
??????,[CODE_T]?????????????
??????,[QTY_1]
??????,[UNIT_1]
??????,[TRADE_TOTAL]??
??????,[GROSS_WT]
??FROM?[WBK_PDE_LIST_ORG_HISTROY]?where?qty_1?between?50?and?500

--表'WBK_PDE_LIST_ORG_HISTROY'。掃描計數(shù)1，邏輯讀取177?次，物理讀取4?次，預讀173?次，lob?邏輯讀取0?次，lob?物理讀取0?次，lob?預讀0?次。

SET?STATISTICS?IO?OFF
drop?index?[WBK_PDE_LIST_ORG_HISTROY].idx_WBK_PDE_LIST_ORG_HISTROY

訪問方式分頁

邏輯讀

物理讀

預讀

估計運算符開銷

全表掃描

1568

1.06575

以QTY_1字段建立聚集索引

351

345

0.275863

以WBOOK_NO字段建非立聚集索引

61065

864

727

14.10295

以[WBOOK_NO],[COP_G_NO],[G_NO],[CODE_T],[QTY_1],[UNIT_1],[TRADE_TOTAL]?,[GROSS_WT]八個字段建復合索引

687

683

0.570198

以[QTY_1],[COP_G_NO],[G_NO],[CODE_T],[WBOOK_NO],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]八個字段建復合索引

178

175

0.146974

以WBOOK_NO建立索引,include以下字段?[QTY_1],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]

682

492

0.570198

以[QTY_1]建立索引,include?以下字段[WBOOK_NO],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]

177

173

0.146974

?例一/例二/例三/例四/例五/例六/例七

8.8/2/345/3.9/1/3.9/1

54/4/864/9/5/1/4

8.8/2/4.1/3.9/1/2.8

7.2/1.9/96/3.9/1/3.9/1

從上表中可以得出一個結論，如果索引使用不當，例如上面的例三——強制使用選擇性很低的索引來查找數(shù)據(jù)（或是索引統(tǒng)計數(shù)據(jù)錯誤、優(yōu)化引擎誤判等，造成索引使用不當），反而會導致大量的I/O操作（邏輯讀61065次，物理讀864次），其成本比進行全表掃描(例一)還高。

例二，通過聚集索引來查找，因為縮小了數(shù)據(jù)表掃描范圍，所以效果較佳。

例五、例七，建立覆蓋索引，因為數(shù)據(jù)結構遠小于數(shù)據(jù)表本身，所以不管組合索引的字段順序是否正確，都有更好的查詢效果。當然?，依WHERE條件所需要的字段建立索引數(shù)據(jù)擺放順序，也就是[QTY_1]放在索引順序的第一位，再include查詢所需要的字段([WBOOK_NO],[COP_G_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT])，其查詢性能最佳。

最后要提醒注意：

1）?在建立覆蓋查詢時要盡量限制索引鍵值的大小，保持Row-to-key的大小比例差異越大越好。否則掃描覆蓋索引與掃描數(shù)據(jù)表所花的I/O操作差不多，這樣就失去了建立覆蓋索引的意義。

2)?覆蓋索引可以用來提升查詢性能，因為索引中包含了所有查詢里的列.非聚集索引為表里的每一行用索引鍵值來存儲一行。另外SQL Server能使用索引頁級里的這些行來執(zhí)行聚集計算。這意味著SQLServer不必去實際的表執(zhí)行聚集計算，這樣可以提升性能。

3)?覆蓋索引能提升獲取數(shù)據(jù)的性能，但它們也能降低INSERT、UPDATE和DELETE操作的性能。這是因為維護覆蓋索引要求做一些額外的工作。通常這不是問題，除非你的數(shù)據(jù)庫經(jīng)常進行非常高的INSERT、UPDATE和DELETE操作。你也許不得不在你的產(chǎn)品系統(tǒng)上應用覆蓋索引之前，要先進行實驗，看看你所建立的覆蓋索引是否在提升性能方面上比影響性能方面更有幫助。

4)?應該在那些SELECT查詢中常使用到的列上創(chuàng)建覆蓋索引，但覆蓋索引中包括過多的列也不行，因為覆蓋索引列的值是存儲在內存中的，這樣會消耗過多內存，引發(fā)性能下降。

關于索引碎片的修復：

關于上圖的一些說明：

avg_fragmentation_in_percent:邏輯碎片（索引中的無序頁）的百分比。這是索引的葉級頁中出錯頁所占的百分比。對于出錯頁，分配給索引的下一個物理頁不是由當前葉級頁中的“下一頁”指針所指向的頁。?

fragment_count?:?索引中的碎片（物理上連續(xù)的葉頁）數(shù)量。?

avg_fragment_size_in_pages?:索引中一個碎片的平均頁數(shù)。?

知道索引碎片程度后，可以使用下表確定修復碎片的最佳方法。

avg_fragmentation_in_percent?值

??修復語句??

>?5%?且?<?=?30%

ALTER?INDEX?REORGANIZE

>?30%