在應用系統中，尤其在聯機事務處理系統中，對數據檢索及處理速度已成為衡量應用系統成敗標準。而采用索引來加快數據處理速度也成為廣大數據庫用戶所接受的優化方法。
索引的使用效果不僅僅依賴于SQL Server的優化策略，在相當程度上也依賴于應用程序的設。怎樣正確地使用索引，不能一概而論，究竟是讓索引滿足程序設計的需要，或是程序設計遵循已建立的索引，這兩者是相符相承的。只有正確地使索引與程序結合起來，才能產生最佳的優化方案。
建立索引的目地是為了優化檢索速度，如果檢索所需要的時間過長，便有理由懷疑是否索引不存在或者優化器沒有使用索引。尚若是索引不存在，那麼就要取決于用戶是否願意用空間來換取時間，使用索引來解決檢索速度慢的問題。如果優化器未使用表上已有的索引，那麼要分析為什麼，關于這一點將在後一點篇幅來說明如果update的效率很低，很可能是由于表上有太多的索引需要維護，從而浪費了時間。
優化器怎樣使用索引
Table scan
如果表上沒有任何索引，那麼檢索將采用Table Scan方式進行，其所用時間主要依賴于表的大小。
例如︰
- dbcc checktable 測出表佔76923頁
-系統每秒讀取50頁
-76923頁/50頁/秒=1538秒 （大約25分鐘）
如果系統有比較大的cathe，某些數據可能由于以前已被讀到內存，那麼讀取數據時間可能會低于估計的時間。一般情況下，Tablescan檢索是由于表上沒有ClusteredIndex或者優化器認為，表中將有20%的數據做為結果追回。
使用索引（條件為指定值）
索引中包含指定記錄的值及地址，SQL Server不必做全表掃描。
例︰ select * from title where title_id="mc3021"
當優化器認為讀取索引頁I/O加讀取數據頁I/O比做Table Scan效果更好時，檢索將使用索引。
使用索引（條件為某範圍內值）
例︰
select * from titles
where title_id >"BU1032"
and title-id <"mc3032"
如果數據是排序的（有Clustered索引），索引將被用來限制數據的掃描範圍。
使用索引避免檢索排序所需要的時間。
例︰
select * from titles
order by title_id
對Clustered索引來說，如果索引順序與Server順序一致，那麼上面的查尋不需要重新排列返回結果。但是，若數據存儲本身是升序排列，而查尋要求降序排列，那麼索引對加快查尋沒有任何作用。
對于Non-Clustered索引，優化器將判斷查尋Non_Clustered索引頁，找到滿足條件的數據進行排序是否比Table Scan更快，優化器將找出最佳結果。從以上幾例可以看出，並非在表上建立了Clustered或on-Clustered索引之後，就一定會被使用，優化器是否使用索引取決于數據的查尋命令，SQL Server將從幾個檢索方案中選擇最佳的一個。
在什麼樣的條件下才選擇Clustered索引呢？
選擇什麼樣的索引基于用戶對數據的檢索條件，這些條件體現于where從句和join表達式。如果你的應用與以下情況相符，你可以考慮選擇Clustered索引。
主鍵時常作為where子句的條件
某一列經常以這樣的格式出現在where表達式中(x<=column <="y)"
某一列非常頻繁地被訪問
某列被用作order by或group by
某列很少被改寫
某列常出現在join中。
Non-Clustered常被用在以下情況︰
某列常用于Aggregate函數（如Sum,....）
某列常用于join,order by,group by。
查尋檢索出的數據不超過表中數據量的20%。
索引覆蓋
檢索覆蓋是指Non_Clustered索引項中包含查尋所需要的全部信息。這種索引之所以比較快也正是因為索引頁中包含了查尋所必須的數據，不需去訪問數據頁。如果Non-Clustered索引中包含結果數據，那麼它的檢索速度將快于Clustered索引。覆蓋索引的缺點︰由于索引項比較多，要佔用比較大的空間。而且update操作會引起索引值改變。
SQL Server對索引的限制
每個表上最多僅能有一個Clustered索引。
如果表上有一個Clustered索引，最多還能有249 Non-Clustered索引。
當沒有Clustered索引時，則可有250個Non-Clustered索引。
索引最多建立在256個列上。
當索引被創建時，SQL Server需要120%的附加空間。
索引維護
隨著應用系統的運行，數據不斷地發生變化，當數據變化達到某一個程度時將會影響到索引的使用。上面講到，某些不合適的索引影響到SQL Server的性能，這時需要用戶自己來維護索引。一種方法是刪除老的索引，重新建新的索引。另外一種方法是保持索引統計有效（使用命令update statistics），在以下情況下需要重新索引。
使用數據模式發生了較大變化。
某段時間內有極大量的數據插入。
SQL Server排序改變。
dbcc發現索引錯誤。
當重建Clustered索引時，這張表的所有Non-Clustered索引將被重建。
維護索引統計表︰
數據庫擁有者必須用命令維護統計表。 update statistics table_name [index_name]
索引優化調整
用這條命令可以改善創建索引的性能,減少建索引所用的時間。 sp_configure "extent i/o buffers",nnnn帶來的影響是增加了extent i/o buffers大小，在SQL Server使用內存不變情況下，減少了procedure和data cathe，而且同一時刻僅有一個用戶能用到extent buffer。
沒有留言:
發佈留言