SQLServer最佳化資料整理（二）

標籤：

預存程序編寫經驗和最佳化措施

　　一、適合讀者對象：資料庫開發程式員，資料庫的資料量很多，涉及到對SP（預存程序）的最佳化的項目開發人員，對資料庫有濃厚興趣的人。 　

　　二、介紹：在資料庫的開發過程中，經常會遇到複雜的商務邏輯和對資料庫的操作，這個時候就會用SP來封裝資料庫操作。如果項目的SP較多，書寫 又沒有一定的規範，將會影響以後的系統維護困難和大SP邏輯的難以理解，另外如果資料庫的資料量大或者項目對SP 的效能要求很，就會遇到最佳化的問題，否則速度有可能很慢，經過親身經驗，一個經過最佳化過的SP要比一個效能差的SP的效率甚至高几百倍。 　

　　三、內容： 　

　　1、開發人員如果用到其他庫的Table或View，務必在當前庫中建立View來實現跨庫操作，最好不要直接使用“databse.dbo.table_name”，因為sp_depends不能顯示出該SP所使用的跨庫table或view，不方便校正。　　

　　2、開發人員在提交SP前，必須已經使用set showplan on分析過查詢計劃，做過自身的查詢最佳化檢查。 　

　　3、高程式運行效率，最佳化應用程式，在SP編寫過程中應該注意以下幾點： 　　

　　a)SQL的使用規範：

　　　i.　盡量避免大事務操作，慎用holdlock子句，提高系統並發能力。

　　　ii.　盡量避免反覆訪問同一張或幾張表，尤其是資料量較大的表，可以考慮先根據條件提取資料到暫存資料表中，然後再做串連。

　　　iii.　盡量避免使用遊標，因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該改寫；如果使用了遊標，就要盡量避免在遊標迴圈中再進行表串連的操作。

　　　iv.　注意where字句寫法，必須考慮語句順序，應該根據索引順序、範圍大小來確定條件子句的前後順序，儘可能的讓欄位順序與索引順序相一致，範圍從大到小。

　　　v.　不要在where子句中的“=”左邊進行函數、算術運算或其他運算式運算，否則系統將可能無法正確使用索引。

　　　vi.　盡量使用exists代替select count(1)來判斷是否存在記錄，count函數只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。

　　　vii.　盡量使用“>=”，不要使用“>”。

　　　viii.　注意一些or子句和union子句之間的替換

　　　ix.　注意表之間串連的資料類型，避免不同類型資料之間的串連。

　　　x.　注意預存程序中參數和資料類型的關係。

　　　xi.　注意insert、update操作的資料量，防止與其他應用衝突。如果資料量超過200個資料頁面（400k），那麼系統將會進行鎖定擴大，頁級鎖會升級成表級鎖。 　　

　　b)索引的使用規範：

　　　i.　索引的建立要與應用結合考慮，建議大的OLTP表不要超過6個索引。

　　　ii.　儘可能的使用索引欄位作為查詢條件，尤其是聚簇索引，必要時可以通過index index_name來強制指定索引

　　　iii.　避免對大表查詢時進行table scan，必要時考慮建立索引。

　　　iv.　在使用索引欄位作為條件時，如果該索引是聯合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用。

　　　v.　要注意索引的維護，周期性重建索引，重新編譯預存程序。　　

　　c)tempdb的使用規範：

　　　i.　盡量避免使用distinct、order by、group by、having、join、cumpute，因為這些語句會加重tempdb的負擔。

　　　ii.　避免頻繁建立和刪除暫存資料表，減少系統資料表資源的消耗。

　　　iii.　在建立暫存資料表時，如果一次性插入資料量很大，那麼可以使用select into代替create table，避免log，提高速度；如果資料量不大，為了緩和系統資料表的資源，建議先create table，然後insert。

　　　iv.　如果暫存資料表的資料量較大，需要建立索引，那麼應該將建立暫存資料表和建立索引的過程放在單獨一個子預存程序中，這樣才能保證系統能夠很好的使用到該暫存資料表的索引。

　　　 v.　如果使用到了暫存資料表，在預存程序的最後務必將所有的暫存資料表顯式刪除，先truncate table，然後drop table，這樣可以避免系統資料表的較長時間鎖定。

　　　 vi.　慎用大的暫存資料表與其他大表的串連查詢和修改，減低系統資料表負擔，因為這種操作會在一條語句中多次使用tempdb的系統資料表。　　

　　d)合理的演算法使用： 　　

　　根據上面已提到的SQL最佳化技術和ASE Tuning手冊中的SQL最佳化內容,結合實際應用,採用多種演算法進行比較,以獲得消耗資源最少、效率最高的方法。具體可用ASE調優命令：set statistics io on, set statistics time on , set showplan on 等。

解析：Microsoft SQL Server中的鎖模式
在SQL Server資料庫中加鎖時，除了可以對不同的資源加鎖，還可以使用不同程度的加鎖方式，即鎖有多種模式，SQL Server中鎖模式包括：

1．共用鎖定 SQL Server中，共用鎖定用於所有的唯讀資料操作。共用鎖定是非獨佔的，允許多個並發事務讀取其鎖定資源。預設情況下，資料被讀取後，SQL Server立即釋放共用鎖定。例如，執行查詢“SELECT * FROM AUTHORS”時，首先鎖定第一頁，讀取之後，釋放對第一頁的鎖定，然後鎖定第二頁。這樣，就允許在讀操作過程中，修改未被鎖定的第一頁。但是，事務隔 離層級串連選項設定和SELECT語句中的鎖定設定都可以改變SQL Server的這種預設設定。例如，“ SELECT * FROM AUTHORS HOLDLOCK”就要求在整個查詢過程中，保持對錶的鎖定，直到查詢完成才釋放鎖定。

2．更新鎖定更新鎖定在修改操作的初始化階段用來鎖定可能要被修改的資源，這樣可以避免使用共用鎖定造成的死結現象。因為使用共用鎖定時，修改資料的操作分 為兩步，首先獲得一個共用鎖定，讀取資料，然後將共用鎖定升級為排它鎖，然後再執行修改操作。這樣如果同時有兩個或多個事務同時對一個事務申請了共用鎖定，在修 改資料的時候，這些事務都要將共用鎖定升級為排它鎖。這時，這些事務都不會釋放共用鎖定而是一直等待對方釋放，這樣就造成了死結。如果一個資料在修改前直接申 請更新鎖定，在資料修改的時候再升級為排它鎖，就可以避免死結。

3．排它鎖 排它鎖是為修改資料而保留的。它所鎖定資源，其他事務不能讀取也不能修改。

4．結構鎖 執行表的資料定義語言 (Data Definition Language) (DDL) 操作（例如添加列或除去表）時使用架構修改 (Sch-M) 鎖。當編譯查詢時，使用架構穩定性 (Sch-S) 鎖。架構穩定性 (Sch-S) 鎖不阻塞任何事務鎖，包括排它鎖。因此在編譯查詢時，其它事務（包括在表上有排它鎖的事務）都能繼續運行。但不能在表上執行 DDL 操作。

5．意圖鎖定 意圖鎖定說明SQL Server有在資源的低層獲得共用鎖定或排它鎖的意向。例如，表級的共用意圖鎖定說明事務意圖將排它鎖釋放到表中的頁或者行。意圖鎖定又可以分為共用意圖鎖定、 獨佔意圖鎖定和共用式獨佔意圖鎖定。共用意圖鎖定說明事務意圖在共用意圖鎖定所鎖定的低層資源上放置共用鎖定來讀取資料。獨佔意圖鎖定說明事務意圖在共用意圖鎖定所鎖定 的低層資源上放置排它鎖來修改資料。共用式排它鎖說明事務允許其他事務使用共用鎖定來讀取頂層資源，並意圖在該資源低層上放置排它鎖。

6．大容量更新鎖定 當將資料大量複製到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設定了 table lock on bulk 表選項時，將使用大容量更新鎖定。大容量更新鎖定允許進程將資料並發地大量複製到同一表，同時防止其它不進行大量複製資料的進程訪問該表。

詳細介紹最佳化SQL Server 2000的設定
　　SQL Server已經為了最佳化自己的效能而進行了良好的配置，比今天市場其他的關係型資料庫都要好得多。然而，你仍然有幾項設定需要進行修改，以便你的資料庫 每分鐘可以處理更多的事務(TPM)。本篇文章的目的就是討論這些設定。我們忽略那些可以通過硬體設定或者表或者索引設計提高的效能，因為這些內容在本篇 文章範圍之外。

　　破碎頁面檢測

　　在我們開始討論區伺服器配置開關之前，讓我們快速探索一下你的模型資料庫--或者說用作構建新的資料庫的基礎的模板。預設情況下，你可以在資料庫中建立預存程序、函數等類似的東西，隨後他們將會被加入新建立的資料庫中。

　　要最佳化效能，你也許想要關閉模型資料庫中的破碎頁面檢測。當一個頁面被成功寫入磁碟的時候，破碎頁面檢測進行識別。如果啟用了的話，你可以看到 每個寫操作對效能產生的每個細小的影響。大多數現代的磁碟陣列都有板上電池，使得陣列可以在突然斷電的情況下完成所有的寫操作--引起破碎頁面的最頻繁原 因。

　　以下的步驟可以接受如何關閉破碎頁面檢測:

exec sp_dboption ‘model‘, ‘torn page detection‘, ‘false‘

　　這篇基礎知識資源可以為你提供更多有關這個設定的資訊。
　　大多數的配置是通過系統預存程序sp_configure完成的。要顯示伺服器的全部設定列表以便定製，你可以輸入如下命令:

　sp_configure ‘show advanced options‘, 1　　GO　　RECONFIGURE WITH OVERRIDE

 

　　你可以配置的選項的數量根據你的SQL Server的版本、服務包，以及位元版本(64位的SQL Server比32位的選項要多)而定。我將直接討論最能影響SQL Server效能最佳化的選項。

　 　Affinity mask: Affinity mask讓你可以控制SQL Server使用哪個處理器。對於大多數情況，你不應該接觸這個設定，讓作業系統控制處理器關係。然而，你也許想要用這個選項來將某個處理器專門用於另一 個進程(例如，MSSearch 或者 SQL Server磁碟 IO ，以及 SQL Server的平衡)。參考基礎知識資源擷取更多有關這個設定的資訊。

　　Awe enabled: Awe的啟動可以讓SQL Server Enterprise版本運行在Windows 2000以及以上進階伺服器上，或者Windows 2003 Enterprise以及以上的版本使用超過4GB的記憶體。如果你的伺服器符合這些條件的話，就啟用這個設定吧。

　　並行成本極限:當查詢需要進行平行處理的時候，並行的成本極限就定下來了。預設情況是五秒鐘。將這個數值改為稍低的數值，俄可以讓更多個查詢獲得平行處理，但是這也會引起CPU瓶頸。這個設定只有在多個處理器的機器上才會起作用。

　　填滿因數:填滿因數設定了在建立聚簇索引的時候用來自動填滿的因子。在頻繁插入的表中，將數值從預設的90%設定為較低的數值，你會獲得收益。

　　輕量級緩衝池:這個設定啟動了光纖模式。使用這個選項在CPU利用率很高的8路及其以上的伺服器上。這可以讓光纖同時為每個線程提供服務，同時在預設情況下運行在每個處理器上。某些任務可以從這些光纖中獲得優勢。

　　並行的最大程度:當伺服器可以使用並行或者不能使用並行，或者是當某個數量的處理器可以用於並行操作的時候，這個設定就確定了。並行就是多個處理器上發生多個處理。例如，查詢的並行操作可以在不同的處理器上同時處理。

　　伺服器最大記憶體(MB):如果你在SQL Server上運行了其他的處理，並且有足夠的記憶體，那麼你有可能想要留出512MB的記憶體給作業系統和這些進程。例如，你可以在MSSearch或者在本地運行大量的代理的情況下將其設定為512。
最大背景工作執行緒:最大背景工作執行緒設定與ADO.net中的串連池有些類似。通過這個設定，任何超過限制(255個使用者)的使用者串連都可以線上程池中等待，直到 為某個串連服務的線程得到釋放，就好像是ADO.net中的串連與串連池共用。如果你有很大量的串連，並且大量的記憶體，那麼你就可以提高這個數值。

　　網路包尺寸(B):這個設定控制了網路中傳輸到你的用戶端的包的尺寸。在有損耗的網路中(例如電話線)，你可能想要將這個參數設定為比較低的數值，墨人數值是4096。在串連良好的網路中，你可以提高這個設定，特別是涉及BLOB的大型批處理操作。

　　優先推進:這個設定為SQL Server提供了處理器的推動。在工作管理員中，點擊進程標籤，定位SQL Server的位置，然後右擊它。選擇“設定優先權別”。注意，SQL Server應該運行在正常的優先順序別上。輸入如下命令:

　 1 　Sp_configure ‘priority boost‘, 1 2 3 　　Reconfigure with override 

　　然後重新啟動你的SQL Server。在工作管理員中察看SQL Server現在運行在什麼優先順序別上。它應該是在高優先順序上。SQL Server應該比其他的使用者進程運行優先順序別要高。在專用於SQL Server的伺服器上使用這個設定。

總結

　　本篇討論了最常見的SQL Server最佳化設定。在做出改變之前和之後分別在測試環境中進行基準確定是非常重要的，可以據此來評估在典型的負載下，改變對你的系統的影響。
SQL Server 資料庫中關於死結的分析

SQL Server資料庫發生死結時不會像ORACLE那樣自動產生一個追蹤檔案。有時可以在[管理]->[當前活動] 裡看到阻塞資訊(有時SQL Server企業管理器會因為鎖太多而沒有響應).

設定跟蹤1204:

 1 USE MASTER 2 3 DBCC TRACEON (1204,-1) 

顯示當前啟用的所有跟蹤標記的狀態:

DBCC TRACESTATUS(-1)

取消跟蹤1204:

DBCC TRACEOFF (1204,-1)

在設定跟蹤1204後,會在資料庫的記錄檔裡顯示SQL Server資料庫死結時一些資訊。但那些資訊很難看懂,需要對照SQL Server聯機叢書仔細來看。根據PAG鎖要找到相關資料庫表的方法:

DBCC TRACEON (3604)
DBCC PAGE (db_id,file_id,page_no)
DBCC TRACEOFF (3604)

請參考sqlservercentral.com上更詳細的講解.但又從CSDN學到了一個找到死結原因的方法。我稍加修改, 去掉了遊標操作並增加了一些提示資訊,寫了一個系統預存程序sp_who_lock.sql。代碼如下：

1 if exists (select * from dbo.sysobjects2 where id = object_id(N‘[dbo].[sp_who_lock]‘)3 and OBJECTPROPERTY(id, N‘IsProcedure‘) = 1)4 drop procedure [dbo].[sp_who_lock]

 

需要的時候直接調用:

sp_who_lock

就可以查出引起死結的進程和SQL語句.

SQL Server內建的系統預存程序sp_who和sp_lock也可以用來尋找阻塞和死結, 但沒有這裡介紹的方法好用。如果想知道其它tracenum參數的含義,請看www.sqlservercentral.com文章

我們還可以設定鎖的逾時時間(單位是毫秒), 來縮短死結可能影響的時間範圍:

例如:

1 use master2 seelct @@lock_timeout3 set lock_timeout 9000004 -- 15分鐘5 seelct @@lock_timeout

最佳化SQLServer索引的小技巧
SQL Server中有幾個可以讓你檢測、調整和最佳化SQL Server效能的工具。在本文中，我將說明如何用SQL Server的工具來最佳化資料庫索引的使用，本文還涉及到有關索引的一般性知識。

關於索引的常識

影響到資料庫效能的最大因素就是索引。由於該問題的複雜性，我只可能簡單的談談這個問題，不過關於這方面的問題，目前有好幾本不錯的書籍可供你參 閱。我在這裡只討論兩種SQL Server索引，即clustered索引和nonclustered索引。當考察建立什麼類型的索引時，你應當考慮資料類型和儲存這些資料的 column。同樣，你也必須考慮資料庫可能用到的查詢類型以及使用的最為頻繁的查詢類型。

索引的類型

如果column儲存了高度相關的資料，並且常常被順序訪問時，最好使用clustered索引，這是因為如果使用clustered索引，SQL Server會在物理上按升序（預設）或者降序重排資料列，這樣就可以迅速的找到被查詢的資料。同樣，在搜尋控制在一定範圍內的情況下，對這些 column也最好使用clustered索引。這是因為由於物理上重排資料，每個表格上只有一個clustered索引。

與上面情況相反，如果columns包含的資料相關性較差，你可以使用nonculstered索引。你可以在一個表格中使用高達249個nonclustered索引--儘管我想象不出實際應用場合會用的上這麼多索引。

當表格使用主關鍵字（primary keys），預設情況下SQL Server會自動對包含該關鍵字的column(s)建立一個專屬的cluster索引。很顯然，對這些column(s)建立專屬索引意味著主關鍵字 的唯一性。當建立外關鍵字（foreign key）關係時，如果你打算頻繁使用它，那麼在外關鍵字cloumn上建立nonclustered索引不失為一個好的方法。如果表格有 clustered索引，那麼它用一個鏈表來維護資料頁之間的關係。相反，如果表格沒有clustered索引，SQL Server將在一個堆棧中儲存資料頁。

資料頁

當索引建立起來的時候，SQLServer就建立資料頁（datapage），資料頁是用以加速搜尋的指標。當索引建立起來的時候，其對應的填充因 子也即被設定。設定填滿因數的目的是為了指示該索引中資料頁的百分比。隨著時間的推移，資料庫的更新會消耗掉已有的空閑空間，這就會導致頁被拆分。頁面分割 的後果是降低了索引的效能，因而使用該索引的查詢會導致資料存放區的支離破碎。當建立一個索引時，該索引的填滿因數即被設定好了，因此填滿因數不能動態維 護。

為了更新資料頁中的填滿因數，我們可以停止舊有索引並重建索引，並重新設定填滿因數（注意：這將影響到當前資料庫的運行，在重要場合請謹慎使用）。 DBCC INDEXDEFRAG和DBCC DBREINDEX是清除clustered和nonculstered索引片段的兩個命令。INDEXDEFRAG是一種線上操作（也就是說，它不會阻 塞其它表格動作，如查詢），而DBREINDEX則在物理上重建索引。在絕大多數情況下，重建索引可以更好的消除片段，但是這個優點是以阻塞當前發生在該 索引所在表格上其它動作為代價換取來得。當出現較大的片段索引時，INDEXDEFRAG會花上一段比較長的時間，這是因為該命令的運行是基於小的互動塊 （transactional block）。

填滿因數

當你執行上述措施中的任何一個，資料庫引擎可以更有效返回編入索引的資料。關於填滿因數（fillfactor）話題已經超出了本文的範疇，不過我還是提醒你需要注意那些打算使用填滿因數建立索引的表格。

在執行查詢時，SQL Server動態選擇使用哪個索引。為此，SQL Server根據每個索引上分布在該關鍵字上的統計量來決定使用哪個索引。值得注意的是，經過日常的資料庫活動（如插入、刪除和更新表格），SQL Server用到的這些統計量可能已經“到期”了，需要更新。你可以通過執行DBCC SHOWCONTIG來查看統計量的狀態。當你認為統計量已經“到期”時，你可以執行該表格的UPDATE STATISTICS命令，這樣SQL Server就重新整理了關於該索引的資訊了。

建立資料庫維護計劃

SQL Server提供了一種簡化並自動維護資料庫的工具。這個稱之為資料庫維護計劃嚮導（Database Maintenance Plan Wizard ，DMPW）的工具也包括了對索引的最佳化。如果你運行這個嚮導，你會看到關於資料庫中關於索引的統計量，這些統計量作為日誌工作並定時更新，這樣就減輕了 手工重建索引所帶來的工作量。如果你不想自動定期重新整理索引統計量，你還可以在DMPW中選擇重新組織資料和資料頁，這將停止舊有索引並按特定的填滿因數重 建索引。
Sybase SQL Server索引的使用和最佳化 
　　在應用系統中,尤其在聯機交易處理系統中,對資料查詢及處理速度已成為衡 量應用系統成敗的標準。而採用索引來加快資料處理速度也成為廣大資料庫使用者所 接受的最佳化方法。

　　在良好的資料庫設計基礎上，能有效地使用索引是SQL Server取得高效能的基礎，SQL Server採用基於代價的最佳化模型，它對每一個提交的有關表的查詢，決定是否使用索引或用哪一個索引。因為查詢執行的大部分開銷是磁碟I/O，使用索引 提高效能的一個主要目標是避免全表掃描，因為全表掃描需要從磁碟上讀表的每一個資料頁，如果有索引指向資料值，則查詢只需讀幾次磁碟就可以了。所以如果建 立了合理的索引，最佳化器就能利用索引加速資料的查詢過程。但是，索引並不總是提高系統的效能，在增、刪、改操作中索引的存在會增加一定的工作量，因此，在 適當的地方增加適當的索引並從不合理的地方刪除次優的索引，將有助於最佳化那些效能較差的SQL Server應用。實踐表明，合理的索引設計是建立在對各種查詢的分析和預測上的，只有正確地使索引與程式結合起來,才能產生最佳的最佳化方案。本文就 SQL Server索引的效能問題進行了一些分析和實踐。

　　一、聚簇索引(clustered indexes)的使用

　　聚簇索引是一種對磁碟上實際資料重新組織以按指定的一個或多個列的值排序。由於聚簇索引的索引頁面指標指向資料頁面，所以使用聚簇索引尋找資料 幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引，並且建聚簇索引需要至少相當該表120%的附加空間，以存放該表的副本和索引中間頁。建立聚簇索 引的思想是：

　　1、 大多數表都應該有聚簇索引或使用分區來降低對錶尾頁的競爭，在一個高事務的環境中，對最後一頁的封鎖嚴重影響系統的輸送量。

　　2、在聚簇索引下，資料在物理上按順序排在資料頁上，重複值也排在一起，因而在那些包含範圍檢查(between、<、<=、& gt;、> =)或使用group by或order by的查詢時，一旦找到具有範圍中第一個索引值的行，具有後續索引值的行保證物理上毗連在一起而不必進一步搜尋，避免了大範圍掃描，可以大大提高查詢速度。

　　3、 在一個頻繁發生插入操作的表上建立聚簇索引時，不要建在具有單調上升值的列(如IDENTITY)上，否則會經常引起封鎖衝突。

　　4、 在聚簇索引中不要包含經常修改的列，因為碼值修改後，資料行必須移動到新的位置。

　　5、 選擇聚簇索引應基於where子句和串連操作的類型。聚簇索引的侯選列是：

　　● 主鍵列,該列在where子句中使用並且插入是隨機的。

　　● 按範圍存取的列，如pri_order > 100 and pri_order < 200 。

　　● 在group by或order by中使用的列。

　　● 不經常修改的列。

　　● 在串連操作中使用的列。

　　二、非聚簇索引(nonclustered indexes)的使用

　　SQL Server預設情況下建立的索引是非聚簇索引，由於非聚簇索引不重新組織表中的資料，而是對每一行儲存索引列值並用一個指標指向資料所在的頁面。換句話 說非聚簇索引具有在索引結構和資料本身之間的一個額外級。一個表如果沒有聚簇索引時,可有250個非聚簇索引。每個非聚簇索引提供訪問資料的不同排序順 序。在建立非聚簇索引時，要權衡索引對查詢速度的加快與降低修改速度之間的利弊。另外，還要考慮這些問題：

　　● 索引需要使用多少空間。

　　● 合適的列是否穩定。

　　● 索引鍵是如何選擇的，掃描效果是否更佳。

　　● 是否有許多重複值。

　　對更新頻繁的表來說，表上的非聚簇索引比聚簇索引和根本沒有索引需要更多的額外開銷。對移到新頁的每一行而言，指向該資料的每個非聚簇索引的頁 級行也必須更新，有時可能還需要索引頁的分理。從一個頁面刪除資料的進程也會有類似的開銷，另外，刪除進程還必須把資料移到頁面上部，以保證資料的連續 性。所以，建立非聚簇索引要非常謹慎。非聚簇索引常被用在以下情況:

　　● 某列常用於集合函數(如Sum,....)。

　　● 某列常用於join,order by,group by。

　　● 查尋出的資料不超過表中資料量的20%。

　　三、覆蓋索引(covering indexes)的使用

　　覆蓋索引是指那些索引項目中包含查尋所需要的全部資訊的非聚簇索引，這 種索引之所以比較快也正是因為索引頁中包含了查尋所必須的資料,不需去訪 問資料頁。 如果非聚簇索引中包含結果資料,那麼它的查詢速度將快於聚簇索引。

　　但是由於覆蓋索引的索引項目比較多,要佔用比較大的空間。而且update 操 作會引起索引值改變。所以如果潛在的覆蓋查詢並不常用或不太關鍵，則覆蓋索引的增加反而會降低效能。

　　四、索引的選擇技術

　　p_detail是房屋公積金管理系統中記錄個人明細的表，有890000行，觀察在不同索引下的查詢運行效果，測試在C/S環境下進行，客戶 機是 IBM PII350(記憶體64M),伺服器是DEC Alpha1000A(記憶體128M),資料庫為SYBASE11.0.3。

　　1、 select count(*) from p_detail where op_date>’19990101’ and op_date<’19991231’ and pri_surplus1>300

　　2、 select count(*),sum(pri_surplus1) from p_detail where op_date>’19990101’ and pay_month between ‘199908’ and ’199912’

　　不建任何索引 查詢1 1分15秒

　　查詢2 1分7秒

　　在op_date上建非聚簇索引 查詢1 57秒

　　查詢2 57秒

　　在op_date上建聚簇索引 查詢1 <1秒

　　查詢2 52秒

　　在pay_month、op_date、pri_surplus1上建索引 查詢1 34秒

　　查詢2 <1秒

　　在op_date、pay_month、pri_surplus1上建索引 查詢1 <1秒

　　查詢2 <1秒

　　從以上查詢效果分析，索引的有無，建立方式的不同將會導致不同的查詢效果，選擇什麼樣的索引基於使用者對資料的查詢條件,這些條件體現於where從句和join運算式中。一般來說建立索引的思路是：

　　(1)、主鍵時常作為where子句的條件，應在表的主鍵列上建立聚簇索引，尤其當經常用它作為串連的時候。

　　(2)、有大量重複值且經常有範圍查詢和排序、分組發生的列，或者非常頻繁地被訪問的列，可考慮建立聚簇索引。

　　(3)、經常同時存取多列，且每列都含有重複值可考慮建立複合索引來覆蓋一個或一組查詢，並把查詢引用最頻繁的列作為前置列，如果可能盡量使關鍵查詢形成覆蓋查詢。

　　(4)、如果知道索引鍵的所有值都是唯一的，那麼確保把索引定義成唯一索引。

　　(5)、在一個經常做插入操作的表上建索引時，使用fillfactor(填滿因數)來減少頁分裂，同時提高並發度降低死結的發生。如果在唯讀表上建索引，則可以把fillfactor置為100。

　　(6)、在選擇索引鍵時，設法選擇那些採用小資料類型的列作為鍵以使每個索

　　引頁能夠容納儘可能多的索引鍵和指標，通過這種方式，可使一個查詢必須遍曆的索引頁面降到最小。此外，儘可能地使用整數為索引值，因為它能夠提供比任何資料類型都快的訪問速度。

　　五、索引的維護

　　上面講到,某些不合適的索引影響到SQL Server的效能,隨著應用系統的運行,資料不斷地發生變化,當資料變化達到某一個程度時將 會影響到索引的使用。這時 需要使用者自己來維護索引。索引的維護包括：

　　1、重建索引

　　隨著資料行的插入、刪除和資料頁的分裂，有些索引頁可能只包含幾頁資料，另外應用在執行大塊I/O的時候，重建非聚簇索引可以降低分區，維護大塊I/O的效率。重建索引實際上是重新組織B-樹空間。在下面情況下需要重建索引：

　　(1)、資料和使用模式大幅度變化。

　　(2)、排序的順序發生改變。

　　(3)、要進行大量插入操作或已經完成。

　　(4)、使用大塊I/O的查詢的磁碟讀次數比預料的要多。

　　(5)、由於大量資料修改，使得資料頁和索引頁沒有充分使用而導致空間的使用超出估算。

　　(6)、dbcc檢查出索引有問題。

　　當重建聚簇索引時,這張表的所有非聚簇索引將被重

　　建.

　　2、索引統計資訊的更新

　　當在一個包含資料的表上建立索引的時候，SQL Server會建立分布資料頁來存放有關索引的兩種統計資訊：分布表和密度表。最佳化器利用這個頁來判斷該索引對某個特定查詢是否有用。但這個統計資訊並不 動態地重新計算。這意味著，當表的資料改變之後，統計資訊有可能是過時的，從而影響最佳化器追求最有工作的目標。因此，在下面情況下應該運行update statistics命令：

　　(1)、資料行的插入和刪除修改了資料的分布。

　　(2)、對用truncate table刪除資料的表上增加資料行。

　　(3)、修改索引列的值。

　　六、結束語

　　實踐表明，不恰當的索引不但於事無補，反而會降低系統的執行效能。因為大量的索引在插入、修改和刪除操作時比沒有索引花費更多的系統時間。例如下面情況下建立的索引是不恰當的：

　　● 在查詢中很少或從不引用的列不會受益於索引，因為索引很少或從來不必搜尋基於這些列的行。

　　● 只有兩個或三個值的列，如男性和女性(是或否)，從不會從索引中得到好處。

　　另外，鑒於索引加快了查詢速度，但減慢了資料更新速度的特點。可通過在一個段上建表，而在另一個段上建其非聚簇索引，而這兩段分別在單獨的物理裝置上來改善操作效能。

 

SQLServer最佳化資料整理（二）