SQL Server 查詢效能最佳化——建立索引原則（二）

標籤：分享   觀察   多少   程式   產生   lookup   一段   statistic   集中   

三：索引的建立原則

 　　一般來說，建立索引要看資料使用的情境，換句話來說哪些訪問資料的SQL語句是常用的，而這些語句是否因為缺少索引（也有可能是索引過多）變的效率低下。但絕不是所有的SQL語句都要建立索引，如果所有的SQL語句都建立索引，那麼可能導致建立過多的索引。

　　我碰到過每秒鐘新增記錄超過千條的案例，雖然該資料表僅有叢集索引，但因為已存在的索引值欄位的值和新增資料索引值欄位的值並不是按順序遞增，每次新增記錄時，肯定造成整體資料行的重新排列。在移掉叢集索引後，效能約提升20%。也曾經碰到過一個資料表上有20個索引，結果新增一條記錄需要耗時4秒鐘才能完成。

　　另一個案例中，POS系統中的銷售資料變更，要同時更新多個資料表，每個資料表都有數十萬條記錄以上，但所使用的WHERE欄位沒有有效索引，除尋找緩慢外，導致更新時產生了大量的鎖定。各資料表加上應有的索引後，原來要幾十秒的更新，不到一秒鐘便完成了。

 　　另外，根據資料庫的使用時機，也有可能先建立索引，用完後再刪除。例如，當你做年報表、季報表時需要大量查詢各種資料，可以考慮在產生報表之前建立相關索引。但在報表產生完畢後，為了保證平時新增、修改和刪除操作的運行效率，再刪除為了產生報表所建立的相關的索引。

　　而針對SQL語句或視圖是否值得建立索引的問題，則有以下幾個可以參考的方面。

第一、選擇性

　　選擇性表示符合你查詢條件的記錄佔總記錄的百分比，也就是

　　選擇性=符合查詢條件的記錄數量/總記錄數量

　　這個值越小越好，越小代表選擇越高，越適合採用索引。例如 ：

　　select * from WBK_Goods_Info where COP_G_NO=‘00078027‘

　　在WBK_Goods_Info 表內符合這個條件的記錄只有1條，而整個資料表有100000條記錄，因此該查詢的選擇性是1/100000，這代表非常高的選擇性，如此通過索引來尋找資料才有效率。反過來說，或你的語句如下：

　　Select * from WBK_Goods_Info with(index(idx_cop_g_no)) where COP_G_NO>‘00018000‘

　　這時符合查詢記錄達82000條，選擇性變為82000/100000，說明選擇性非常低，除非以COP_G_NO欄位為索引值建立的索引是叢集索引，否則如果採用非叢集索引來訪問，反而變成需要讀取至少82000次資料頁，因為SQL SERVER在讀取每一條記錄時，都是先將整個資料頁讀取（請記住，這是SQL SERVER讀取資料的基本單位），再從中取出目標記錄。就算兩條記錄存在同一資料頁上，也要讀該資料頁兩次。因此在選擇性很低時，通過非叢集索引訪問是非常沒有效率的訪問方式，還不如直接進行全表掃描。

第二、資料密度

　　資料密度是指索引值惟一的記錄條數分之一，也就是

　　資料密度=1/索引值惟一的記錄數量

　　通過以下語句進行測試：在WBK_PDE_LIST_ORG_HISTROY資料表的COP_G_NO欄位建立索引，而後通過dbcc show_statistics語句查詢儲存系統內關於該索引的統計資訊的記錄，而後再應用上方的公式，以測試是否與儲存在系統內的ALL Density欄位值是否相同：

--建立索引

CREATE NONCLUSTERED INDEX [idx_WBK_PDE_LIST_COP_G_NO] ON [dbo].[WBK_PDE_LIST_ORG_HISTROY] 

(

[COP_G_NO] ASC

)

INCLUDE ( [WBOOK_NO],[G_NO],[CODE_T],[UNIT_1],[TRADE_TOTAL],[GROSS_WT]) 

--返回all desity欄位的值

 DBCC SHOW_STATISTICS (‘WBK_PDE_LIST_ORG_HISTROY‘,‘idx_WBK_PDE_LIST_COP_G_NO‘)

 --計算all desity欄位的值

Select 1.0/(select count(distinct COP_G_NO)  from WBK_PDE_LIST_ORG_HISTROY) [All Density] 

　　當資料密度越小，也就是惟一性越高時，代表該欄位越適合建立索引，因為當總資料條數乘上該密度值，就是一般平均查詢到的記錄數字。

第三、資料分布

　　 資料分布代表多條資料記錄組成的方式，與密度的概念有關。它代表資料記錄是平均散布在一段範圍內，還是集中在部分區塊。其分布如。

       

 　　以我們的範例而言，每一種貨物的貨物編號都是自增且惟一的，也就是貨物資訊表(wbk_goods_info)中有100000種貨物，以2000的倍數為範圍的切分點，則各資料範圍內的記錄條數是相等的，此種分布稱為平均分布。或資料類型如此，則要計算某個查詢條件的選擇性是否很高就相當的容易且精準。

　　如果資料是標準分布的，也就是說資料在有些範圍內多，有些範圍內少，以這個例子來說，就是有些貨物的銷售記錄很多，有些貨物可能基本上沒有銷售記錄，則該索引就需要有更細緻的統計資料，以記錄一個範圍的資料約略有多少條記錄，在查詢最佳化程式判斷某個索引是否適用某項查詢時，才可以精確判斷出該欄位的選擇性是否很高，以決定使用的索引。

　　這也就是當你觀察Dbcc show_statistics時(如)，如果呈現的分布記錄有很多條，表示該索引值在整個記錄中是標準分布，所以需要各區段的記錄數目，以較為精確地判斷合格記錄數多少，若只有寥寥三四筆，表示接近平均分布，只需要描述平均分布的狀態即可。

 

第四、索引的有效性

      在根據以上三原則建立相應的索引之後，我們再來看看如何觀察在建立索引後，查詢語句是否變得較有效率，索引的使用效率是否高。

--沒有索引的情況

Set statistics io on

Select [WBOOK_NO]      ,[COP_G_NO]      ,[G_NO]

      ,[CONTR_ITEM]      ,[CODE_S]      ,[CODE_T]

     ,[G_NAME]      ,[G_MODEL]   ,[G_QTY]      ,[G_UNIT]      ,[QTY_1]      ,[UNIT_1] ,[TRADE_CURR]      ,[DECL_PRICE]      ,[TRADE_TOTAL]     ,[GROSS_WT]      ,[NET_WT] from WBK_PDE_LIST_ORG_HISTROY c

Where c.WBOOK_NO=‘BE404942450001‘ and c.COP_G_NO=‘60196928‘ and QTY_1>15

Select * from sys.dm_db_missing_index_groups 

Select * from sys.dm_db_missing_index_group_stats

Select * from sys.dm_db_missing_index_details

 

Select mig.*,statement as table_name,column_id,column_name,column_usage

From  sys.dm_db_missing_index_details as mid

Cross apply  sys.dm_db_missing_index_columns (mid.index_handle)

Inner join sys.dm_db_missing_index_groups as mig on mig.index_handle=mid.index_handle

Order by mig.index_group_handle,mig.index_handle,column_id

 

---在建立索引之後，再次執行以上語句。

接下來通過sys_dm_db_index_usage_stats可觀察是否產生了過多的索引。

 --插入資料會影響到索引

insert WBK_PDE_LIST_ORG_HISTROY 

Select ‘BE404942451001‘,‘60196928‘,‘11427‘,‘305‘,‘92‘,‘52083200‘

      ,null ,‘布料‘,null,‘215‘,25,‘011‘,25,‘011‘,null,null,null,10.82,270.5,null,null,null,5,3.8

表‘WBK_PDE_LIST_ORG_HISTROY‘。掃描計數0，邏輯讀取17 次，物理讀取5 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 

--更新資料會影響到索引

--通過PK_WBK_PDE_LIST_ORG_HISTROY

--idx_WBK_PDE_LIST_QTY1

--idx_WBK_PDE_LIST_COP_G_NO索引掃描WBOOK_NO=‘BE404942451001‘的記錄

 update WBK_PDE_LIST_ORG_HISTROY set QTY_1=50000

where WBOOK_NO=‘BE404942451001‘

--表‘WBK_PDE_LIST_ORG_HISTROY‘。掃描計數1，邏輯讀取9 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。

 

 Select * from sys.dm_db_index_usage_stats where object_id=object_id(‘WBK_PDE_LIST_ORG_HISTROY‘)

 

 

               

圖1，索引使用方式分布圖　　　　

圖2，索引名稱

 

　　從1中可以看到 sys.dm_db_index_usage_stats系統檢視表是指某個查詢利用索引所進行的尋找、掃描、尋找或更新操作都被計為對該索引的一次使用，每次使用都會對視圖中的相應計數器累加1。它針對使用者提交的查詢所導致的操作，以及由系統內部產生的查詢所導致的操作（例如，掃描以收集統計資料）分開累積資訊。而由於前述的insert語句會影響到之前建立的所有索引，所以index_id等於1、6、10的記錄行的user_updates欄位為是1 （見圖1中2）。update 語句會更新資料表中的QTY_1欄位，但是沒有更新COP_G_NO欄位，所以隻影響index_id等於1與6的記錄行，這兩行的user_updates欄位是2（見圖1中3）。update語句的where條件則會利用index_id等於1的索引，見user_seeks的值為1（見圖1中3）。

　　User_updates欄位是指由於基礎資料表或視圖的插入、更新或刪除操作導致的更新次數。利用這個資料可判斷應用程式是否很少用到某個索引。如果該索引的更新次數(user_updates)值很大，那麼說明產生的維護量比較大，再參見搜尋次數(user_seeks)與書籤尋找操作的次數(user_lookups)，如是這兩個值很小，則可以考慮刪除索引。

　　重新啟動SQL SERVER服務時，sys.dm_db_index_usage_stats系統檢視表內的各種計數器會初始化為空白值。此外，每當分離或關閉資料時（例如，由於 AUTO_CLOSE 設定為 ON），就會刪除所有與該資料庫關聯的資料行。初次使用某個索引後，才會加入到系統的統計資訊中，sys.dm_db_index_usage_stats隨後才看得到代表該索引的資料行，此時各項計數器的初始設定值為零。

 

　　最後再次重申一下，“水可載舟，亦可覆舟”，索引也一樣。索引有助於提高檢索效能，但過多或不當的索引也會導致系統低效。因為使用者在表中每加進一個索引，資料庫就要做更多的工作。過多的索引甚至會導致索引片段。

　　所以說，我們要建立一個“適當”的索引體系，特別是對叢集索引的建立，更應精益求精，以使您的資料庫能得到高效能的發揮。

　　因為非叢集索引需要在非叢集索引的B樹中找到每一行的指標，再去其所在表上找資料，效能因此會大打折扣，有時甚至不如不加非叢集索引。

 

SQL Server 查詢效能最佳化——建立索引原則（二）