在良好的數據庫設計基礎上能有效地使用索引是SQL Server取得高性能的基礎SQL Server采用基於代價的優化模型它對每一個提交的有關表的查詢決定是否使用索引或用哪一個索引因為查詢執行的大部分開銷是磁盤I/O使用索引提高性能的一個主要目標是避免全表掃描因為全表掃描需要從磁盤上讀表的每一個數據頁如果有索引指向數據值則查詢只需讀幾次磁盤就可以了所以如果建立了合理的索引優化器就能利用索引加速數據的查詢過程但是索引並不總是提高系統的性能在增刪改操作中索引的存在會增加一定的工作量因此在適當的地方增加適當的索引並從不合理的地方刪除次優的索引將有助於優化那些性能較差的SQL Server應用實踐表明合理的索引設計是建立在對各種查詢的分析和預測上的只有正確地使索引與程序結合起來才能產生最佳的優化方案本文就SQL Server索引的性能問題進行了一些分析和實踐
一聚簇索引(clustered indexes)的使用
聚簇索引是一種對磁盤上實際數據重新組織以按指定的一個或多個列的值排序由於聚簇索引的索引頁面指針指向數據頁面所以使用聚簇索引查找數據幾乎總是比使用非聚簇索引快每張表只能建一個聚簇索引並且建聚簇索引需要至少相當該表%的附加空間以存放該表的副本和索引中間頁建立聚簇索引的思想是
大多數表都應該有聚簇索引或使用分區來降低對表尾頁的競爭在一個高事務的環境中對最後一頁的封鎖嚴重影響系統的吞吐量
在聚簇索引下數據在物理上按順序排在數據頁上重復值也排在一起因而在那些包含范圍檢查(between<<=>>=)或使用group by或order by的查詢時一旦找到具有范圍中第一個鍵值的行具有後續索引值的行保證物理上毗連在一起而不必進一步搜索避免了大范圍掃描可以大大提高查詢速度
在一個頻繁發生插入操作的表上建立聚簇索引時不要建在具有單調上升值的列(如IDENTITY)上否則會經常引起封鎖沖突
在聚簇索引中不要包含經常修改的列因為碼值修改後數據行必須移動到新的位置
選擇聚簇索引應基於where子句和連接操作的類型
聚簇索引的侯選列是
主鍵列該列在where子句中使用並且插入是隨機的
按范圍存取的列如pri_order > and pri_order <
在group by或order by中使用的列
不經常修改的列
在連接操作中使用的列
二非聚簇索引(nonclustered indexes)的使用
SQL Server缺省情況下建立的索引是非聚簇索引由於非聚簇索引不重新組織表中的數據而是對每一行存儲索引列值並用一個指針指向數據所在的頁面換句話說非聚簇索引具有在索引結構和數據本身之間的一個額外級一個表如果沒有聚簇索引時可有個非聚簇索引每個非聚簇索引提供訪問數據的不同排序順序在建立非聚簇索引時要權衡索引對查詢速度的加快與降低修改速度之間的利弊另外還要考慮這些問題
索引需要使用多少空間
合適的列是否穩定
索引鍵是如何選擇的掃描效果是否更佳
是否有許多重復值
對更新頻繁的表來說表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷對移到新頁的每一行而言指向該數據的每個非聚簇索引的頁級行也必須更新有時可能還需要索引頁的分理從一個頁面刪除數據的進程也會有類似的開銷另外刪除進程還必須把數據移到頁面上部以保證數據的連續性所以建立非聚簇索引要非常慎重非聚簇索引常被用在以下情況
某列常用於集合函數(如Sum)
某列常用於joinorder bygroup by
查尋出的數據不超過表中數據量的%
三覆蓋索引(covering indexes)的使用
覆蓋索引是指那些索引項中包含查尋所需要的全部信息的非聚簇索引這種索引之所以比較快也正是因為索引頁中包含了查尋所必須的數據不需去訪問數據頁如果非聚簇索引中包含結果數據那麼它的查詢速度將快於聚簇索引
但是由於覆蓋索引的索引項比較多要占用比較大的空間而且update操作會引起索引值改變所以如果潛在的覆蓋查詢並不常用或不太關鍵則覆蓋索引的增加反而會降低性能
四索引的選擇技術
p_detail是住房公積金管理系統中記錄個人明細的表有行觀察在不同索引下的查詢運行效果測試在C/S環境下進行客戶機是IBM PII(內存M)服務器是DEC AlphaA(內存M)數據庫為SYBASE
select count(*) from p_detail where op_date> and op_date< and pri_surplus>
select count(*)sum(pri_surplus) from p_detail where op_date> and pay_month between and
不建任何索引查詢 分秒
查詢分秒
在op_date上建非聚簇索引查詢 秒
查詢 秒
在op_date上建聚簇索引查詢 <秒
查詢 秒
在pay_monthop_datepri_surplus上建索引查詢 秒
查詢 <秒
在op_datepay_monthpri_surplus上建索引查詢 <秒
查詢 <秒
從以上查詢效果分析索引的有無建立方式的不同將會導致不同的查詢效果選擇什麼樣的索引基於用戶對數據的查詢條件這些條件體現於where從句和join表達式中一般來說建立索引的思路是
()主鍵時常作為where子句的條件應在表的主鍵列上建立聚簇索引尤其當經常用它作為連接的時候
()有大量重復值且經常有范圍查詢和排序分組發生的列或者非常頻繁地被訪問的列可考慮建立聚簇索引
()經常同時存取多列且每列都含有重復值可考慮建立復合索引來覆蓋一個或一組查詢並把查詢引用最頻繁的列作為前導列如果可能盡量使關鍵查詢形成覆蓋查詢
()如果知道索引鍵的所有值都是唯一的那麼確保把索引定義成唯一索引
()在一個經常做插入操作的表上建索引時使用fillfactor(填充因子)來減少頁分裂同時提高並發度降低死鎖的發生如果在只讀表上建索引則可以把fillfactor置為
()在選擇索引鍵時設法選擇那些采用小數據類型的列作為鍵以使每個索引頁能夠容納盡可能多的索引鍵和指針通過這種方式可使一個查詢必須遍歷的索引頁面降到最小此外盡可能地使用整數為鍵值因為它能夠提供比任何數據類型都快的訪問速度
五索引的維護
上面講到某些不合適的索引影響到SQL Server的性能隨著應用系統的運行數據不斷地發生變化當數據變化達到某一個程度時將會影響到索引的使用這時需要用戶自己來維護索引索引的維護包括
重建索引
隨著數據行的插入刪除和數據頁的分裂有些索引頁可能只包含幾頁數據另外應用在執行大塊I/O的時候重建非聚簇索引可以降低分片維護大塊I/O的效率重建索引實際上是重新組織B樹空間在下面情況下需要重建索引
()數據和使用模式大幅度變化
()排序的順序發生改變
()要進行大量插入操作或已經完成
()使用大塊I/O的查詢的磁盤讀次數比預料的要多
()由於大量數據修改使得數據頁和索引頁沒有充分使用而導致空間的使用超出估算
()dbcc檢查出索引有問題
當重建聚簇索引時這張表的所有非聚簇索引將被重建
索引統計信息的更新
當在一個包含數據的表上創建索引的時候SQL Server會創建分布數據頁來存放有關索引的兩種統計信息分布表和密度表優化器利用這個頁來判斷該索引對某個特定查詢是否有用但這個統計信息並不動態地重新計算這意味著當表的數據改變之後統計信息有可能是過時的從而影響優化器追求最有工作的目標因此在下面情況下應該運行update statistics命令
()數據行的插入和刪除修改了數據的分布
()對用truncate table刪除數據的表上增加數據行
()修改索引列的值
六結束語
實踐表明不恰當的索引不但於事無補反而會降低系統的執行性能因為大量的索引在插入修改和刪除操作時比沒有索引花費更多的系統時間例如下面情況下建立的索引是不恰當的
在查詢中很少或從不引用的列不會受益於索引因為索引很少或從來不必搜索基於這些列的行
只有兩個或三個值的列如男性和女性(是或否)從不會從索引中得到好處
另外鑒於索引加快了查詢速度但減慢了數據更新速度的特點可通過在一個段上建表而在另一個段上建其非聚簇索引而這兩段分別在單獨的物理設備上來改善操作性能
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22227.html
