本文轉載自：http://blog.csdn.net/andylaudotnet/article/details/1763573

?????? 性能調節的目的是通過將網絡流通、磁盤 I/O 和 CPU 時間減到最小，使每個查詢的響應時間最短并最大限度地提高整個數據庫服務器的吞吐量。為達到此目的，需要了解應用程序的需求和數據的邏輯和物理結構，并在相互沖突的數據庫使用之間（如聯機事務處理 (OLTP) 與決策支持）權衡。

對性能問題的考慮應貫穿于開發階段的全過程，不應只在最后實現系統時才考慮性能問題。許多使性能得到顯著提高的性能事宜可通過開始時仔細設計得以實現。為最有效地優化 Microsoft? SQL Server? 2000 的性能，必須在極為多樣化的情形中識別出會使性能提升最多的區域，并對這些區域集中分析。

雖然其它系統級性能問題（如內存、硬件等）也是研究對象，但經驗表明從這些方面獲得的性能收益通常會增長。通常情況下，SQL Server 自動管理可用的硬件資源，從而減少對大量的系統級手動調節任務的需求（以及從中所得的收益）。

設計聯合數據庫服務器

為達到大型 Web 站點所需的高性能級別，多層系統一般在多個服務器之間平衡每一層的處理負荷。Microsoft? SQL Server? 2000通過對 SQL Server 數據進行水平分區，在一組服務器之間分攤數據庫處理負荷。這些服務器相互獨立，但也可以相互協作以處理來自應用程序的數據庫請求；這樣的一組協作服務器稱為聯合體。

只有當應用程序將每個 SQL 語句發送到擁有該語句所需的大部分數據的成員服務器時，聯合數據庫層才可以達到非常高的性能級別。這稱為使用語句所需的數據配置 SQL 語句。使用所需的數據配置 SQL 語句不是聯合服務器所獨有的要求；在群集系統中同樣有此要求。

雖然服務器聯合體與單個數據庫服務器呈現給應用程序的圖像相同，但在實現數據庫服務層的方式上存在內部差異。

單個服務器層	聯合服務器層
生產服務器上有一個 SQL Server 實例。	每個成員服務器上都有一個 SQL Server 實例。
生產數據存儲在一個數據庫中。	每個成員服務器都有一個成員數據庫。數據分布在成員數據庫之間。
一般每個表都是單個實體。	原始數據庫中的表被水平分區為成員表。一個成員數據庫有一個成員表，而且使用分布式分區視圖使每個成員服務器上看起來似乎都有原始表的完整復本。
與單個服務器的所有連接和所有 SQL 語句都由 SQL Server 的同一個實例處理。	應用程序層必須能夠在包含語句所引用的大部分數據的成員服務器上配置 SQL 語句。

原始數據庫中的表被水平分區為成員表。一個成員數據庫有一個成員表，而且使用分布式分區視圖使每個成員服務器上看起來似乎都有原始表的完整復本。

與單個服務器的所有連接和所有 SQL 語句都由 SQL Server 的同一個實例處理。

應用程序層必須能夠在包含語句所引用的大部分數據的成員服務器上配置 SQL 語句。

雖然目的是設計數據庫服務器聯合體來處理全部的工作負荷，但是可通過設計一組在不同的服務器之間分布數據的分布式分區視圖來達到此目的。

數據庫設計

數據庫的設計包括兩個組成部分：邏輯設計和物理設計。邏輯數據庫設計包括使用數據庫組件（如表和約束）為業務需求和數據建模，而無須考慮如何或在哪里物理存儲這些數據。物理數據庫設計包括將邏輯設計映射到物理媒體上、利用可用的硬件和軟件功能使得盡可能快地對數據進行物理訪問和維護，還包括生成索引。要在設計后更改這些組件很困難，因此在數據庫應用程序開發的早期階段正確設計數據庫、使其為業務需求建模并利用硬件和軟件功能很重要。

實現SQL Server數據庫的優化，首先要有一個好的數據庫設計方案。在實際工作中，許多SQL Server方案往往是由于數據庫設計得不好導致性能很差。實現良好的數據庫設計必須考慮這些問題:

1.1　邏輯庫規范化問題

一般來說，邏輯數據庫設計會滿足規范化的前3級標準:

1.第1規范:沒有重復的組或多值的列。

2.第2規范:每個非關鍵字段必須依賴于主關鍵字，不能依賴于1個組合式主關鍵字的某些組成部分。

3.第3規范:1個非關鍵字段不能依賴于另1個非關鍵字段。

　　遵守這些規則的設計會產生較少的列和更多的表，因而也就減少了數據冗余，也減少了用于存儲數據的頁。但表關系也許需要通過復雜的合并來處理，這樣會降低系統的性能。某種程度上的非規范化可以改善系統的性能，非規范化過程可以根據性能方面不同的考慮用多種不同的方法進行，但以下方法經實踐驗證往往能提高性能。

1.如果規范化設計產生了許多4路或更多路合并關系，就可以考慮在數據庫實體(表)中加入重復屬性(列)

2.常用的計算字段(如總計、最大值等)可以考慮存儲到數據庫實體中。

　　比如某一個項目的計劃管理系統中有計劃表，其字段為:項目編號、年初計劃、二次計劃、調整計劃、補列計劃…，而計劃總數(年初計劃+二次計劃+調整計劃+補列計劃)是用戶經常需要在查詢和報表中用到的，在表的記錄量很大時，有必要把計劃總數作為1個獨立的字段加入到表中。這里可以采用觸發器以在客戶端保持數據的一致性。

3.重新定義實體以減少外部屬性數據或行數據的開支。相應的非規范化類型是:

　　(1)把1個實體(表)分割成2個表(把所有的屬性分成2組)。這樣就把頻繁被訪問的數據同較少被訪問的數據分開了。這種方法要求在每個表中復制首要關鍵字。這樣產生的設計有利于并行處理，并將產生列數較少的表。

　　(2)把1個實體(表)分割成2個表(把所有的行分成2組)。這種方法適用于那些將包含大量數據的實體(表)。在應用中常要保留歷史記錄，但是歷史記錄很少用到。因此可以把頻繁被訪問的數據同較少被訪問的歷史數據分開。而且如果數據行是作為子集被邏輯工作組(部門、銷售分區、地理區域等)訪問的，那么這種方法也是很有好處的。

　1.2　生成物理數據庫

　　要想正確選擇基本物理實現策略，必須懂得數據庫訪問格式和硬件資源的操作特點，主要是內存和磁盤子系統I/O。這是一個范圍廣泛的話題，但以下的準則可能會有所幫助。

　　1.與每個表列相關的數據類型應該反映數據所需的最小存儲空間，特別是對于被索引的列更是如此。比如能使用smallint類型就不要用integer類型，這樣索引字段可以被更快地讀取，而且可以在1個數據頁上放置更多的數據行，因而也就減少了I/O操作。

　　2.把1個表放在某個物理設備上，再通過SQL Server段把它的不分簇索引放在1個不同的物理設備上，這樣能提高性能。尤其是系統采用了多個智能型磁盤控制器和數據分離技術的情況下，這樣做的好處更加明顯。

　　3.用SQL Server段把一個頻繁使用的大表分割開，并放在2個單獨的智能型磁盤控制器的數據庫設備上，這樣也可以提高性能。因為有多個磁頭在查找，所以數據分離也能提高性能。

　　4.用SQL Server段把文本或圖像列的數據存放在1個單獨的物理設備上可以提高性能。1個專用的智能型的控制器能進一步提高性能。

查詢優化

查詢速度慢的原因很多，常見如下幾種：　　

　　1、沒有索引或者沒有用到索引(這是查詢慢最常見的問題，是程序設計的缺陷)　　

　　2、I/O吞吐量小，形成了瓶頸效應。　　

　　3、沒有創建計算列導致查詢不優化。　　

　　4、內存不足　　

　　5、網絡速度慢　　

　　6、查詢出的數據量過大（可以采用多次查詢，其他的方法降低數據量）　　

　　7、鎖或者死鎖(這也是查詢慢最常見的問題，是程序設計的缺陷)　　

　　8、sp_lock,sp_who,活動的用戶查看,原因是讀寫競爭資源。　　

　　9、返回了不必要的行和列　　

???? 10、查詢語句不好，沒有優化

?????? 可以通過如下方法來優化查詢 :　　

　　1、把數據、日志、索引放到不同的I/O設備上，增加讀取速度，以前可以將Tempdb應放在RAID0上，SQL2000不在支持。數據量（尺寸）越大，提高I/O越重要.　　

　　2、縱向、橫向分割表，減少表的尺寸(sp_spaceuse)　　

　　3、升級硬件　　

　　4、根據查詢條件,建立索引,優化索引、優化訪問方式，限制結果集的數據量。注意填充因子要適當（最好是使用默認值0）。索引應該盡量小，使用字節數小的列建索引好（參照索引的創建）,不要對有限的幾個值的字段建單一索引如性別字段　　

　　5、提高網速;　　

　　6、擴大服務器的內存,Windows 2000和SQL server 2000能支持4-8G的內存。配置虛擬內存：虛擬內存大小應基于計算機上并發運行的服務進行配置。運行 Microsoft SQL Server? 2000 時，可考慮將虛擬內存大小設置為計算機中安裝的物理內存的 1.5 倍。如果另外安裝了全文檢索功能，并打算運行 Microsoft 搜索服務以便執行全文索引和查詢，可考慮：將虛擬內存大小配置為至少是計算機中安裝的物理內存的 3 倍。將 SQL Server max server memory 服務器配置選項配置為物理內存的 1.5 倍（虛擬內存大小設置的一半）。　　

　　7、增加服務器 CPU個數;但是必須明白并行處理串行處理更需要資源例如內存。使用并行還是串行程是MsSQL自動評估選擇的。單個任務分解成多個任務，就可以在處理器上運行。例如耽擱查詢的排序、連接、掃描和GROUP BY字句同時執行，SQL SERVER根據系統的負載情況決定最優的并行等級，復雜的需要消耗大量的CPU的查詢最適合并行處理。但是更新操作Update,Insert， Delete還不能并行處理。　　

　　8、如果是使用like進行查詢的話，簡單的使用index是不行的，但是全文索引，耗空間。 like 'a%' 使用索引 like '%a' 不使用索引用 like '%a%' 查詢時，查詢耗時和字段值總長度成正比,所以不能用CHAR類型，而是VARCHAR。對于字段的值很長的建全文索引。　　

　　9、DB Server 和APPLication Server 分離；OLTP和OLAP分離　　

　　10、分布式分區視圖可用于實現數據庫服務器聯合體。聯合體是一組分開管理的服務器，但它們相互協作分擔系統的處理負荷。這種通過分區數據形成數據庫服務器聯合體的機制能夠擴大一組服務器，以支持大型的多層 Web 站點的處理需要。有關更多信息，參見設計聯合數據庫服務器。（參照SQL幫助文件'分區視圖'）　　

　　a、在實現分區視圖之前，必須先水平分區表　　

　　b、在創建成員表后，在每個成員服務器上定義一個分布式分區視圖，并且每個視圖具有相同的名稱。這樣，引用分布式分區視圖名的查詢可以在任何一個成員服務器上運行。系統操作如同每個成員服務器上都有一個原始表的復本一樣，但其實每個服務器上只有一個成員表和一個分布式分區視圖。數據的位置對應用程序是透明的。　　

　　11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG,收縮數據和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 設置自動收縮日志.對于大的數據庫不要設置數據庫自動增長，它會降低服務器的性能。在T-sql的寫法上有很大的講究，下面列出常見的要點：首先，DBMS處理查詢計劃的過程是這樣的：　　

　　 1、查詢語句的詞法、語法檢查　　

　　 2、將語句提交給DBMS的查詢優化器　　

　　 3、優化器做代數優化和存取路徑的優化　　

　　 4、由預編譯模塊生成查詢規劃　　

　　 5、然后在合適的時間提交給系統處理執行　　

　　 6、最后將執行結果返回給用戶其次，看一下SQL SERVER的數據存放的結構：一個頁面的大小為8K(8060)字節，8個頁面為一個盤區，按照B樹存放。　　

　　12、Commit和rollback的區別 Rollback:回滾所有的事物。 Commit:提交當前的事物. 沒有必要在動態SQL里寫事物，如果要寫請寫在外面如： begin tran exec(@s) commit trans 或者將動態SQL 寫成函數或者存儲過程。　　

　　13、在查詢Select語句中用Where字句限制返回的行數,避免表掃描,如果返回不必要的數據，浪費了服務器的I/O資源，加重了網絡的負擔降低性能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯接訪問表,后果嚴重。　　

　　14、SQL的注釋申明對執行沒有任何影響

　　15、盡可能不使用光標，它占用大量的資源。如果需要row-by-row地執行，盡量采用非光標技術,如：在客戶端循環，用臨時表，Table變量，用子查詢，用Case語句等等。游標可以按照它所支持的提取選項進行分類：只進必須按照從第一行到最后一行的順序提取行。FETCH NEXT 是唯一允許的提取操作,也是默認方式。可滾動性可以在游標中任何地方隨機提取任意行。游標的技術在SQL2000下變得功能很強大，他的目的是支持循環。有四個并發選項 READ_ONLY：不允許通過游標定位更新(Update)，且在組成結果集的行中沒有鎖。 OPTIMISTIC WITH valueS:樂觀并發控制是事務控制理論的一個標準部分。樂觀并發控制用于這樣的情形，即在打開游標及更新行的間隔中，只有很小的機會讓第二個用戶更新某一行。當某個游標以此選項打開時，沒有鎖控制其中的行，這將有助于最大化其處理能力。如果用戶試圖修改某一行，則此行的當前值會與最后一次提取此行時獲取的值進行比較。如果任何值發生改變，則服務器就會知道其他人已更新了此行，并會返回一個錯誤。如果值是一樣的，服務器就執行修改。選擇這個并發選項OPTIMISTIC WITH ROW VERSIONING:此樂觀并發控制選項基于行版本控制。使用行版本控制，其中的表必須具有某種版本標識符，服務器可用它來確定該行在讀入游標后是否有所更改。在 SQL Server 中，這個性能由 timestamp 數據類型提供，它是一個二進制數字，表示數據庫中更改的相對順序。每個數據庫都有一個全局當前時間戳值：@@DBTS。每次以任何方式更改帶有timestamp 列的行時，SQL Server 先在時間戳列中存儲當前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某個表具有timestamp 列，則時間戳會被記到行級。服務器就可以比較某行的當前時間戳值和上次提取時所存儲的時間戳值，從而確定該行是否已更新。服務器不必比較所有列的值，只需比較 timestamp 列即可。如果應用程序對沒有 timestamp 列的表要求基于行版本控制的樂觀并發，則游標默認為基于數值的樂觀并發控制。 SCROLL LOCKS 這個選項實現悲觀并發控制。在悲觀并發控制中，在把數據庫的行讀入游標結果集時，應用程序將試圖鎖定數據庫行。在使用服務器游標時，將行讀入游標時會在其上放置一個更新鎖。如果在事務內打開游標，則該事務更新鎖將一直保持到事務被提交或回滾；當提取下一行時，將除去游標鎖。如果在事務外打開游標，則提取下一行時，鎖就被丟棄。因此，每當用戶需要完全的悲觀并發控制時，游標都應在事務內打開。更新鎖將阻止任何其它任務獲取更新鎖或排它鎖，從而阻止其它任務更新該行。然而，更新鎖并不阻止共享鎖，所以它不會阻止其它任務讀取行，除非第二個任務也在要求帶更新鎖的讀取。滾動鎖根據在游標定義的 Select 語句中指定的鎖提示，這些游標并發選項可以生成滾動鎖。滾動鎖在提取時在每行上獲取，并保持到下次提取或者游標關閉，以先發生者為準。下次提取時，服務器為新提取中的行獲取滾動鎖，并釋放上次提取中行的滾動鎖。滾動鎖獨立于事務鎖，并可以保持到一個提交或回滾操作之后。如果提交時關閉游標的選項為關，則 COMMIT 語句并不關閉任何打開的游標，而且滾動鎖被保留到提交之后，以維護對所提取數據的隔離。所獲取滾動鎖的類型取決于游標并發選項和游標 Select 語句中的鎖提示。鎖提示只讀樂觀數值樂觀行版本控制鎖定無提示未鎖定未鎖定未鎖定更新NOLOCK 未鎖定未鎖定未鎖定未鎖定 HOLDLOCK 共享共享共享更新 UPDLOCK 錯誤更新更新更新 TABLOCKX 錯誤未鎖定未鎖定更新其它未鎖定未鎖定未鎖定更新 *指定 NOLOCK 提示將使指定了該提示的表在游標內是只讀的。　　

　　16、用Profiler來跟蹤查詢，得到查詢所需的時間，找出SQL的問題所在;用索引優化器優化索引　　

　　17、注意UNion和UNion all 的區別。UNION all好　　

　　18、注意使用DISTINCT，在沒有必要時不要用，它同UNION一樣會使查詢變慢。重復的記錄在查詢里是沒有問題的　　

　　19、查詢時不要返回不需要的行、列　　

　　20、用sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT來限制查詢消耗的資源。當評估查詢消耗的資源超出限制時，服務器自動取消查詢,在查詢之前就扼殺掉。 SET LOCKTIME設置鎖的時間　　

　　21、用select top 100 / 10 Percent 來限制用戶返回的行數或者SET ROWCOUNT來限制操作的行　　

　　22、在SQL2000以前，一般不要用如下的字句: "IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE", and "LIKE '%500'"，因為他們不走索引全是表掃描。也不要在Where字句中的列名加函數，如Convert，substring等,如果必須用函數的時候，創建計算列再創建索引來替代.還可以變通寫法：Where SUBSTRING(firstname,1,1) = 'm'改為Where firstname like 'm%'（索引掃描），一定要將函數和列名分開。并且索引不能建得太多和太大。NOT IN會多次掃描表，使用EXISTS、NOT EXISTS，IN , LEFT OUTER JOIN 來替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，現在2000的優化器能夠處理了。相同的是IS NULL，"NOT", "NOT EXISTS", "NOT IN"能優化她，而"<>"等還是不能優化，用不到索引。　　

　　23、使用Query Analyzer，查看SQL語句的查詢計劃和評估分析是否是優化的SQL。一般的20%的代碼占據了80%的資源，我們優化的重點是這些慢的地方。　　

　　24、如果使用了IN或者OR等時發現查詢沒有走索引，使用顯示申明指定索引： Select * FROM PersonMember (INDEX = IX_Title) Where processid IN ('男'，'女')　　

　　25、將需要查詢的結果預先計算好放在表中，查詢的時候再Select。這在SQL7.0以前是最重要的手段。例如醫院的住院費計算。　　

　　26、MIN() 和 MAX()能使用到合適的索引。　　

　　27、數據庫有一個原則是代碼離數據越近越好，所以優先選擇Default,依次為Rules,Triggers, Constraint（約束如外健主健CheckUNIQUE……,數據類型的最大長度等等都是約束）,Procedure.這樣不僅維護工作小，編寫程序質量高，并且執行的速度快。　　

　　28、如果要插入大的二進制值到Image列，使用存儲過程，千萬不要用內嵌Insert來插入(不知JAVA是否)。因為這樣應用程序首先將二進制值轉換成字符串（尺寸是它的兩倍），服務器受到字符后又將他轉換成二進制值.存儲過程就沒有這些動作: 方法：Create procedure p_insert as insert into table(Fimage) values (@image), 在前臺調用這個存儲過程傳入二進制參數，這樣處理速度明顯改善。　　

　　29、Between在某些時候比IN 速度更快,Between能夠更快地根據索引找到范圍。用查詢優化器可見到差別。 select * from chineseresume where title in ('男','女') Select * from chineseresume where between '男' and '女' 是一樣的。由于in會在比較多次，所以有時會慢些。　　

　　30、在必要是對全局或者局部臨時表創建索引，有時能夠提高速度，但不是一定會這樣，因為索引也耗費大量的資源。他的創建同是實際表一樣。　　

　　31、不要建沒有作用的事物例如產生報表時，浪費資源。只有在必要使用事物時使用它。　　

　　32、用OR的字句可以分解成多個查詢，并且通過UNION 連接多個查詢。他們的速度只同是否使用索引有關,如果查詢需要用到聯合索引，用UNION all執行的效率更高.多個OR的字句沒有用到索引，改寫成UNION的形式再試圖與索引匹配。一個關鍵的問題是否用到索引。　　

　　 33、盡量少用視圖，它的效率低。對視圖操作比直接對表操作慢,可以用stored procedure來代替她。特別的是不要用視圖嵌套,嵌套視圖增加了尋找原始資料的難度。我們看視圖的本質：它是存放在服務器上的被優化好了的已經產生了查詢規劃的SQL。對單個表檢索數據時，不要使用指向多個表的視圖，直接從表檢索或者僅僅包含這個表的視圖上讀，否則增加了不必要的開銷,查詢受到干擾.為了加快視圖的查詢，MsSQL增加了視圖索引的功能。　　

　　34、沒有必要時不要用DISTINCT和ORDER BY，這些動作可以改在客戶端執行。它們增加了額外的開銷。這同UNION和UNION ALL一樣的道理。　　　

　　35、在IN后面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最后面，減少判斷的次數。　　

　　36、當用Select INTO時，它會鎖住系統表(sysobjects，sysindexes等等)，阻塞其他的連接的存取。創建臨時表時用顯示申明語句，而不是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = 'XYZ' --commit 在另一個連接中Select * from sysobjects可以看到 Select INTO 會鎖住系統表，Create table 也會鎖系統表(不管是臨時表還是系統表)。所以千萬不要在事物內使用它！！！這樣的話如果是經常要用的臨時表請使用實表，或者臨時表變量。　　

　　37、一般在GROUP BY 個HAVING字句之前就能剔除多余的行，所以盡量不要用它們來做剔除行的工作。他們的執行順序應該如下最優：select 的Where字句選擇所有合適的行，Group By用來分組個統計行，Having字句用來剔除多余的分組。這樣Group By個Having的開銷小，查詢快.對于大的數據行進行分組和Having十分消耗資源。如果Group BY的目的不包括計算，只是分組，那么用Distinct更快　　

　　38、一次更新多條記錄比分多次更新每次一條快,就是說批處理好　　

　　39、少用臨時表，盡量用結果集和Table類性的變量來代替它,Table 類型的變量比臨時表好　　

　　40、在SQL2000下，計算字段是可以索引的，需要滿足的條件如下：　　

　　a、計算字段的表達是確定的　　

　　b、不能用在TEXT,Ntext，Image數據類型　　

　　c、必須配制如下選項 ANSI_NULLS = ON, ANSI_PADDINGS = ON, …….　　

　　41、盡量將數據的處理工作放在服務器上，減少網絡的開銷，如使用存儲過程。存儲過程是編譯好、優化過、并且被組織到一個執行規劃里、且存儲在數據庫中的SQL語句，是控制流語言的集合，速度當然快。反復執行的動態SQL,可以使用臨時存儲過程，該過程（臨時表）被放在Tempdb中。以前由于SQL SERVER對復雜的數學計算不支持，所以不得不將這個工作放在其他的層上而增加網絡的開銷。SQL2000支持UDFs,現在支持復雜的數學計算，函數的返回值不要太大，這樣的開銷很大。用戶自定義函數象光標一樣執行的消耗大量的資源，如果返回大的結果采用存儲過程　　

　　42、不要在一句話里再三的使用相同的函數，浪費資源,將結果放在變量里再調用更快　　

　　43、Select COUNT(*)的效率教低，盡量變通他的寫法，而EXISTS快.同時請注意區別： select count(Field of null) from Table和 select count(Field of NOT null) from Table 的返回值是不同的！！！　　

　　44、當服務器的內存夠多時，配制線程數量 = 最大連接數+5，這樣能發揮最大的效率；否則使用配制線程數量<最大連接數啟用SQL SERVER的線程池來解決,如果還是數量 = 最大連接數+5，嚴重的損害服務器的性能。　　

　　45、按照一定的次序來訪問你的表。如果你先鎖住表A，再鎖住表B，那么在所有的存儲過程中都要按照這個順序來鎖定它們。如果你（不經意的）某個存儲過程中先鎖定表B，再鎖定表A，這可能就會導致一個死鎖。如果鎖定順序沒有被預先詳細的設計好，死鎖很難被發現　　

　　46、通過SQL Server Performance Monitor監視相應硬件的負載 Memory: Page Faults / sec計數器如果該值偶爾走高，表明當時有線程競爭內存。如果持續很高，則內存可能是瓶頸。

　　Process:　　

　　1、% DPC Time 指在范例間隔期間處理器用在緩延程序調用(DPC)接收和提供服務的百分比。(DPC 正在運行的為比標準間隔優先權低的間隔)。由于 DPC 是以特權模式執行的，DPC 時間的百分比為特權時間百分比的一部分。這些時間單獨計算并且不屬于間隔計算總數的一部分。這個總數顯示了作為實例時間百分比的平均忙時。　　

　　2、%Processor Time計數器　如果該參數值持續超過95%，表明瓶頸是CPU。可以考慮增加一個處理器或換一個更快的處理器。　　

　　3、% Privileged Time 指非閑置處理器時間用于特權模式的百分比。(特權模式是為操作系統組件和操縱硬件驅動程序而設計的一種處理模式。它允許直接訪問硬件和所有內存。另一種模式為用戶模式，它是一種為應用程序、環境分系統和整數分系統設計的一種有限處理模式。操作系統將應用程序線程轉換成特權模式以訪問操作系統服務)。特權時間的 % 包括為間斷和 DPC 提供服務的時間。特權時間比率高可能是由于失敗設備產生的大數量的間隔而引起的。這個計數器將平均忙時作為樣本時間的一部分顯示。　　

　　4、% User Time表示耗費CPU的數據庫操作，如排序，執行aggregate functions等。如果該值很高，可考慮增加索引，盡量使用簡單的表聯接，水平分割大表格等方法來降低該值。 Physical Disk: Curretn Disk Queue Length計數器該值應不超過磁盤數的1.5~2倍。要提高性能，可增加磁盤。 SQLServer:Cache Hit Ratio計數器該值越高越好。如果持續低于80%，應考慮增加內存。注意該參數值是從SQL Server啟動后，就一直累加記數，所以運行經過一段時間后，該值將不能反映系統當前值。　　

　　47、分析select emp_name form employee where salary > 3000 在此語句中若salary是Float類型的，則優化器對其進行優化為Convert(float,3000)，因為3000是個整數，我們應在編程時使用3000.0而不要等運行時讓DBMS進行轉化。同樣字符和整型數據的轉換。　　

　　48、查詢的關聯同寫的順序　　

　　select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '號碼'）　　

　　select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' （A = B ,B = '號碼'， A = '號碼'）　　

　　select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '號碼'， A = '號碼'）　　

　　49、　　

　　(1)IF 沒有輸入負責人代碼 THEN code1=0 code2=9999 ELSE code1=code2=負責人代碼 END IF 執行SQL語句為: Select 負責人名 FROM P2000 Where 負責人代碼>=:code1 AND負責人代碼 <=:code2　　

　　(2)IF 沒有輸入負責人代碼 THEN 　Select 負責人名 FROM P2000 ELSE code= 負責人代碼 Select 負責人代碼 FROM P2000 Where 負責人代碼=:code END IF 第一種方法只用了一條SQL語句,第二種方法用了兩條SQL語句。在沒有輸入負責人代碼時,第二種方法顯然比第一種方法執行效率高,因為它沒有限制條件; 在輸入了負責人代碼時,第二種方法仍然比第一種方法效率高,不僅是少了一個限制條件,還因相等運算是最快的查詢運算。我們寫程序不要怕麻煩　　

　　50、關于JOBCN現在查詢分頁的新方法（如下），用性能優化器分析性能的瓶頸，如果在I/O或者網絡的速度上，如下的方法優化切實有效，如果在CPU或者內存上，用現在的方法更好。請區分如下的方法，說明索引越小越好。　　

　　begin　　

　　DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))　　

　　insert into @local_variable (ReferenceID)　　

　　select top 100000 ReferenceID from chineseresume order by ReferenceID　　

　　select * from @local_variable where Fid > 40 and fid <= 60　　

　　end 和

　　begin　　

　　DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20))　　

　　insert into @local_variable (ReferenceID)　　

　　select top 100000 ReferenceID from chineseresume order by updatedate　　

　　select * from @local_variable where Fid > 40 and fid <= 60　　

　　end 的不同

　　begin　　

　　create table #temp (FID int identity(1,1),ReferenceID varchar(20))　　

　　insert into #temp (ReferenceID)　　

　　select top 100000 ReferenceID from chineseresume order by updatedate　　

　　select * from #temp where Fid > 40 and fid <= 60 drop table #temp　　

　　end

完全通過系統級服務器性能優化（如內存大小、文件系統類型、處理器的數目及類型等）解決性能問題可能很誘人。但經驗表明大多數性能問題不能用這種方法解決。必須通過這些方法解決性能問題：分析應用程序以及應用程序提交給數據庫的查詢和更新，并分析這些查詢和更新如何與數據庫架構交互。

持續時間意外地長的查詢和更新可能由下列原因引起：

· 網絡通訊速度慢。

· 服務器計算機的內存不足或 Microsoft? SQL Server? 2000 可用的內存不足。

· 缺少有用的統計數據。

· 統計數據過期。

· 缺少有用的索引

· 缺少有用的數據條帶化。

當查詢或更新花費的時間比預期的長時，使用下面的檢查清單提高性能：

說明? 建議在與技術支持提供商聯系之前先參考該檢查清單。

1. 性能問題與查詢以外的組件是否有關？例如，問題是否為網絡性能慢？是否有任何其它可能引起或間接導致性能下降的組件？可以使用 Windows NT 性能監視器監視與 SQL Server 相關和與 SQL Server 不相關的組件性能。有關更多信息，請參見使用系統監視器進行監視。

2. 如果性能問題與查詢相關，涉及哪個查詢或哪組查詢？使用 SQL 事件探查器幫助識別慢速查詢。有關更多信息，請參見使用 SQL 事件探查器進行監視。

通過使用 SET 語句啟用 SHOWPLAN、STATISTICS IO、STATISTICS TIME 和 STATISTICS PROFILE 選項，可以確定數據庫查詢性能。

·SHOWPLAN 描述 SQL Server 查詢優化器選擇的數據檢索方法。有關更多信息，請參見 SET SHOWPLAN_ALL。

·STATISTICS IO 報告與語句內引用的每個表的掃描數、邏輯讀取數（在高速緩存中訪問的頁數）和物理讀取數（訪問磁盤的次數）有關的信息。有關更多信息，請參見 SET STATISTICS IO。

· STATISTICS TIME 顯示分析、編譯和執行查詢所需的時間（以毫秒為單位）。有關更多信息，請參見 SET STATISTICS TIME。

·STATISTICS PROFILE 顯示每個查詢執行后的結果集，代表查詢執行的配置文件。有關更多信息，請參見SET STATISTICS PROFILE。

在 SQL 查詢分析器中，還可以打開 graphical execution plan 選項查看關于 SQL Server 如何檢索數據的圖形表示。

由這些工具收集的信息使您得以確定 SQL Server 查詢優化器正在如何執行查詢以及正在使用哪些索引。利用這些信息，可以確定通過重寫查詢、更改表上的索引或修改數據庫設計等方法能否提高性能。有關更多信息，請參見分析查詢。

3.是否已經用有用的統計數據優化查詢？

SQL Server 自動在索引列上創建對列內的值分布情況的統計。也可以使用 SQL 查詢分析器或 CREATE STATISTICS 語句在非索引列上手動創建統計；或者如果將 auto create statistics 數據庫選項設置為 true，則自動在非索引列上創建統計。查詢處理器可以利用這些統計確定最佳的查詢評估策略。在聯接操作所涉及的非索引列上維護附加的統計信息可以提高查詢性能。有關更多信息，請參見統計信息。

使用 SQL 事件探查器或 SQL 查詢分析器內的圖形執行計劃來監視查詢，以確定查詢是否有足夠的統計信息。有關更多信息，請參見錯誤和警告事件分類。

4.查詢統計信息是否為最新？統計信息是否自動更新？

SQL Server 自動在索引列上創建并更新查詢統計（只要沒有禁用自動查詢統計更新特性）。另外，可以使用 SQL 查詢分析器或 UPDATE STATISTICS 語句在非索引列上手工更新統計；或者如果 auto update statistics 數據庫選項設置為 true，則自動在非索引列上更新統計。最新的統計不取決于日期或時間數據。如果尚未進行 UPDATE 操作，則查詢統計信息仍是最新的。

如果沒有將統計設置為自動更新，則應設置為自動更新。有關更多信息，請參見統計信息。

5.是否有合適的索引？添加一個或多個索引是否會提高查詢性能？有關更多信息，請參見索引優化建議。

6.是否有任何數據熱點或索引熱點？如果有，考慮使用磁盤條帶化。有關更多信息，請參見使用文件組放置數據和 RAID。

7.是否為查詢優化器提供了優化復雜查詢的最有利條件？有關更多信息，請參見查詢優化建議。

存儲過程的優化

一、前言：在經過一段時間的存儲過程開發之后，寫下了一些開發時候的小結和經驗與大家共享，希望對大家有益，主要是針對Sybase和SQL Server數據庫，但其它數據庫應該有一些共性。

二、適合讀者對象：數據庫開發程序員，數據庫的數據量很多，涉及到對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 等。

以下是一些常用的優化需要注意的方面：

操作符優化

IN 操作符

用IN寫出來的SQL的優點是比較容易寫及清晰易懂，這比較適合現代軟件開發的風格。

但是用IN的SQL性能總是比較低的，從ORACLE執行的步驟來分析用IN的SQL與不用IN的SQL有以下區別：

ORACLE試圖將其轉換成多個表的連接，如果轉換不成功則先執行IN里面的子查詢，再查詢外層的表記錄，如果轉換成功則直接采用多個表的連接方式查詢。由此可見用IN的SQL至少多了一個轉換的過程。一般的SQL都可以轉換成功，但對于含有分組統計等方面的SQL就不能轉換了。

推薦方案：在業務密集的SQL當中盡量不采用IN操作符。

NOT IN操作符

此操作是強列推薦不使用的，因為它不能應用表的索引。

推薦方案：用NOT EXISTS 或（外連接+判斷為空）方案代替

<> 操作符（不等于）

不等于操作符是永遠不會用到索引的，因此對它的處理只會產生全表掃描。

推薦方案：用其它相同功能的操作運算代替，如

a<>0 改為 a>0 or a<0

a<>’’ 改為 a>’’

IS NULL 或IS NOT NULL操作（判斷字段是否為空）

判斷字段是否為空一般是不會應用索引的，因為B樹索引是不索引空值的。

推薦方案：

用其它相同功能的操作運算代替，如

a is not null 改為 a>0 或a>’’等。

不允許字段為空，而用一個缺省值代替空值，如業擴申請中狀態字段不允許為空，缺省為申請。

建立位圖索引（有分區的表不能建，位圖索引比較難控制，如字段值太多索引會使性能下降，多人更新操作會增加數據塊鎖的現象）

> 及 < 操作符（大于或小于操作符）

大于或小于操作符一般情況下是不用調整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優化，如一個表有100萬記錄，一個數值型字段A，30萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那么執行A>2與A>=3的效果就有很大的區別了，因為A>2時ORACLE會先找出為2的記錄索引再進行比較，而A>=3時ORACLE則直接找到=3的記錄索引。

LIKE操作符

LIKE操作符可以應用通配符查詢，里面的通配符組合可能達到幾乎是任意的查詢，但是如果用得不好則會產生性能上的問題，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用范圍索引。一個實際例子：用YW_YHJBQK表中營業編號后面的戶標識號可來查詢營業編號 YY_BH LIKE ‘%5400%’ 這個條件會產生全表掃描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會利用YY_BH的索引進行兩個范圍的查詢，性能肯定大大提高。

UNION操作符

UNION在進行表鏈接后會篩選掉重復的記錄，所以在表鏈接后會對所產生的結果集進行排序運算，刪除重復的記錄再返回結果。實際大部分應用中是不會產生重復的記錄，最常見的是過程表與歷史表UNION。如：

select * from gc_dfys

union

select * from ls_jg_dfys

這個SQL在運行時先取出兩個表的結果，再用排序空間進行排序刪除重復的記錄，最后返回結果集，如果表數據量大的話可能會導致用磁盤進行排序。

推薦方案：采用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys

SQL書寫的影響

同一功能同一性能不同寫法SQL的影響

如一個SQL在A程序員寫的為

Select * from zl_yhjbqk

B程序員寫的為

Select * from dlyx.zl_yhjbqk（帶表所有者的前綴）

C程序員寫的為

Select * from DLYX.ZLYHJBQK（大寫表名）

D程序員寫的為

Select * from DLYX.ZLYHJBQK（中間多了空格）

以上四個SQL在ORACLE分析整理之后產生的結果及執行的時間是一樣的，但是從ORACLE共享內存SGA的原理，可以得出ORACLE對每個SQL 都會對其進行一次分析，并且占用共享內存，如果將SQL的字符串及格式寫得完全相同則ORACLE只會分析一次，共享內存也只會留下一次的分析結果，這不僅可以減少分析SQL的時間，而且可以減少共享內存重復的信息，ORACLE也可以準確統計SQL的執行頻率。

WHERE后面的條件順序影響

WHERE子句后面的條件順序對大數據量表的查詢會產生直接的影響，如

Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1

Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'

以上兩個SQL中dy_dj（電壓等級）及xh_bz（銷戶標志）兩個字段都沒進行索引，所以執行的時候都是全表掃描，第一條SQL的dy_dj = '1KV以下'條件在記錄集內比率為99%，而xh_bz=1的比率只為0.5%，在進行第一條SQL的時候99%條記錄都進行dy_dj及xh_bz的比較，而在進行第二條SQL的時候0.5%條記錄都進行dy_dj及xh_bz的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低。

查詢表順序的影響

在FROM后面的表中的列表順序會對SQL執行性能影響，在沒有索引及ORACLE沒有對表進行統計分析的情況下ORACLE會按表出現的順序進行鏈接，由此因為表的順序不對會產生十分耗服務器資源的數據交叉。（注：如果對表進行了統計分析，ORACLE會自動先進小表的鏈接，再進行大表的鏈接）

SQL語句索引的利用

對操作符的優化（見上節）

對條件字段的一些優化

采用函數處理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’5400’，優化處理：hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate)，優化處理：

sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

進行了顯式或隱式的運算的字段不能進行索引，如：

ss_df+20>50，優化處理：ss_df>30

‘X’||hbs_bh>’X5400021452’，優化處理：hbs_bh>’5400021542’

sk_rq+5=sysdate，優化處理：sk_rq=sysdate-5

hbs_bh=5401002554，優化處理：hbs_bh=’ 5401002554’，注：此條件對hbs_bh 進行隱式的to_number轉換，因為hbs_bh字段是字符型。

條件內包括了多個本表的字段運算時不能進行索引，如：

ys_df>cx_df，無法進行優化

qc_bh||kh_bh=’5400250000’，優化處理：qc_bh=’5400’ and kh_bh=’250000’

應用ORACLE的HINT（提示）處理

提示處理是在ORACLE產生的SQL分析執行路徑不滿意的情況下要用到的。它可以對SQL進行以下方面的提示

目標方面的提示：

COST（按成本優化）

RULE（按規則優化）

CHOOSE（缺省）（ORACLE自動選擇成本或規則進行優化）

ALL_ROWS（所有的行盡快返回）

FIRST_ROWS（第一行數據盡快返回）

執行方法的提示：

USE_NL（使用NESTED LOOPS方式聯合）

USE_MERGE（使用MERGE JOIN方式聯合）

USE_HASH（使用HASH JOIN方式聯合）

索引提示：

INDEX（TABLE INDEX）（使用提示的表索引進行查詢）

其它高級提示（如并行處理等等）

ORACLE的提示功能是比較強的功能，也是比較復雜的應用，并且提示只是給ORACLE執行的一個建議，有時如果出于成本方面的考慮ORACLE也可能不會按提示進行。根據實踐應用，一般不建議開發人員應用ORACLE提示，因為各個數據庫及服務器性能情況不一樣，很可能一個地方性能提升了，但另一個地方卻下降了，ORACLE在SQL執行分析方面已經比較成熟，如果分析執行的路徑不對首先應在數據庫結構（主要是索引）、服務器當前性能（共享內存、磁盤文件碎片）、數據庫對象（表、索引）統計信息是否正確這幾方面分析。

與沒有優化數據庫的網站相比，數據庫的存取會降低你的系統性能。但是大多數情況下，網站和數據庫有密不可分的關系，正是數據庫給站點提供了大容量、多樣性、個性化等特色，并實現了很多特殊的功能。

1 不要忘記給數據庫做索引。

合理的索引能立即顯著地提高數據庫整個系統的性能。可以參考有關SQL性能調試書籍，學會根據所需查詢方式合理制作索引和根據索引方式改進查詢語句。

2 在適當的情況下，盡可能的用存儲過程而不是SQL查詢。

因為前者已經過了預編譯，運行速度更快。同時讓數據庫僅僅返回你所需要的那些數據，而不是返回大量數據再讓ASP程序過濾。總之要充分和有效地發揮數據庫的強大功能，讓它按照我們的要求反饋給我們最合適和最精練的信息。

3 在可能情況下我們應該使用SQL Server而不是Access。因為Access僅僅是基于文件的數據庫，多用戶性能很差。數據庫連接盡量使用OLEDB和非DSN方式，因為這種連接方式有更好的并發性能。

4 避免使用DAO（Data Access Objects）和RDO（Remote Data Objects）數據源。因為他們主要應用在單用戶的處理系統里，ADO（ActiveX Data Objects）才是為Web應用設計的。

5 建立記錄集Rescordset的時候要清晰合理地設置數據游標(cursort)和鎖定方式(locktype)。

因為在不同的方式下ASP會以不同的方式操縱數據庫，其執行速度也有很大區別，尤其在大數據量的時候。如果你只想遍歷數據，那么默認游標（前進、只讀）會帶來最好的性能。

6 當你引用ADO變量的時候，會消耗較多的CPU周期。因此，如果在一個ASP頁面中多次引用數據庫的字段變量，一個較好的方式是將字段值先放入本地變量，然后可以直接調用本地變量來計算和顯示數據。

7 緩存ADO Connection對象也許不是一個好主意。

如果一個連接（Connection）對象被存儲在Application對象中而被所有ASP頁面使用，那么所有頁面就會爭著使用這個連接。但是如果連接對象被存儲在Session對象中，就要為每個用戶創建一個數據庫連接，這就減小了連接池的作用，并且增大了Web服務器和數據庫服務器的壓力。可以用在每個使用ADO的ASP頁創建和釋放ADO對象來替代緩存數據庫連接。因為IIS內建了數據庫連接池，所以這種方法非常有效，缺點是每個ASP頁面都需要進行一些創建和釋放操作。

8 ASP最強大和主要的用途之一就是對數據庫進行操作，在數據庫操作中我們要注意：不要任意使用“SELECT ＊ ......” 形式的SQL查詢語句。應該盡量檢索你所需要的那些字段。比如一個表中有10個字段，但是你只會用到其中的一個字段（name），就該使用“select name from mytable”，而不是用“select ＊ from mytable”。在字段數比較少的時候，兩者的區別可能并不明顯，但是當一個表中擁有幾十個字段的時候，數據庫會多檢索很多你并不需要的數據。在這種情況下你最好不要為了節省打字時間或者害怕查找對應字段名稱的麻煩，而要老老實實地使用“select id,name,age... from mytable”。

9 及時關閉打開的記錄集對象以及連接(Connection)對象。

記錄集對象和連接對象耗費系統資源相當大，因此它們的可用數量是有限的。如果你打開了太多的記錄集對象以及連接對象而最后卻沒有關閉它們，可能會出現ASP程序剛開始的時候運行速度很快，而多運行幾遍就越來越慢的現象，甚至導致服務器死機。請使用如下方法進行關閉：

MyRecordSet.closeSet MyRecordSet=Nothing

Set MyConnection=Nothing

10 連接數據庫

仍然使用ODBC系統或者文件DSN來連接數據庫，或者使用很快的OLEDB技術來連接。使用后者，當移動Web文件時，不再需要修改配置。

OLEDB位于應用程序與ODBC層之間。在ASP頁面中，ADO就是位于OLEDB之上的程序。調用ADO時，首先發送給OLEDB，然后再發送給ODBC層。可以直接連接到OLEDB層，這么做后，將提高服務器端的性能。怎么直接連接到OLEDB呢？

如果使用SQLServer 7，使用下面的代碼做為連接字符串：

strConnString = "DSN='';DRIVER={SQL SERVER};" & _

"UID=myuid;PWD=mypwd;" & _

"DATABASE=MyDb;SERVER=MyServer;"

最重要的參數就是“DRIVER=”部分。如果你想繞過ODBC而使用OLEDB來訪問SQL Server，使用下面的語法：

strConnString ="Provider=SQLOLEDB.1;Password=mypassword;" & _

"Persist Security Info=True;User ID=myuid;" & _

"Initial Catalog=mydbname;" & _

"Data Source=myserver;Connect Timeout=15"

為什么這很重要

現在你可能奇怪為什么學習這種新的連接方法很關鍵？為什么不使用標準的DSN或者系統DSN方法？好，根據Wrox在他們的ADO 2.0程序員參考書籍中所做的測試，如果使用OLEDB連接，要比使用DSN或者DSN－less連接，有以下的性能提高表現：

性能比較：

----------------------------------------------------------------------

SQL Access

連接時間: 18 82

重復1，000個記錄的時間：2900 5400

OLEDB DSN OLEDB DSN

連接時間：62 99

重復1，000個記錄的時間：100 950

----------------------------------------------------------------------

這個結論在Wrox的ADO 2.0程序員參考發表。時間是以毫秒為單位，重復1，000個記錄的時間是以服務器油標的方式計算的。

有一個例子：

select a. *, m.amount

from tableA a,

(

select b.fieldD, sum(c.total_amount) amount

from tableA b, tableB c

where b.fieldC = 100 and

b.fieldA in ('AA', 'BB', 'CC', 'DD', 'EE', 'FF') and

b.fieldId = c.fieldId

group by b.fieldD

) m

where a.fieldC = 100 and a.fieldD = m.fieldD and

a.fieldA = 'GG'

這句sql當中對同一個表掃描了兩次,所以效率太低,有什么辦法可以避免這種寫法?

tableA,tableB 是主從表關系。

請不要用sql server 中太特殊的語法，因為要用到oracle中。

在oracle中無人回答。

------------------------------------------

SQL語句的寫法是根據你的業務要求，改寫起來效果不能很明顯。

先分析一下你的SQL的執行路徑：

1、

首先會分別對tableA和tableB應用filter動作（使用m子查詢中的where條件）。然后進行連接，可能會是nestloop或hash join...這取決于你的兩個表數據過濾情況。然后進行匯總（group by）輸出m結果集。

2、接下來會將m結果集與tableA（外層）過濾后（a.fieldC = 100 and a.fieldA = 'GG'）的結果集進行連接，還是有多種連接方式。最后輸出a. *, m.amount。大致分析了一下執行的路徑，就會對你的描述產生疑惑：“對同一個表掃描了兩次”肯定指的是tableA了。但是你沒有建立相關的索引嗎？如果說外層的查詢就算建立索引也會通過rowid定位到表中，我們權當這是“表掃描”，但是內層的查詢應該不會發生產生表掃描（all table access）的情況！應該是索引掃描（index scan）才對。根據這一點，我們可以首先考慮建立索引來提高效率。

可以考慮建立的索引：

create index idx_1 on tableA(fieldC,fieldA,fieldId,fieldD)

create index idx_2 on tableB(fieldId,total_amount)

建立完這兩個索引后別忘了重新執行分析，以保證統計值準確。

建立完這兩個索引后，內層的執行計劃應該是對idx_1和idx_2進行索引掃描（index scan）然后連接輸出m結果集，再與外層的經過索引掃描（index scan + rowid to table）的結果集進行連接。如果查詢計劃不對，請檢查你的優化器參數設置，不要使用rbo要使用cbo。如果還是沒有采用請用/* index*/提示強制指定....

上面的是單純從索引方面考慮。如果還是不能提高速度，考慮建立實體化視圖（物化視圖）。可以只將m部分進行實體化。如果tableA和tableB基本屬于靜態表，可以考慮將整條語句實體化。

這里有個非常好的例子并總結了：

SERVER數據庫中實現快速的數據提取和數據分頁。以下代碼說明了我們實例中數據庫的“紅頭文件”一表的部分數據結構：

CREATE table [dbo].[TGongwen] (　　--TGongwen是紅頭文件表名

[Gid] [int] ideNTITY (1, 1) NOT NULL ,

--本表的id號，也是主鍵

[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,

--紅頭文件的標題

[fariqi] [datetime] NULL ,

--發布日期

[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,

--發布用戶

[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,

--需要瀏覽的用戶。每個用戶中間用分隔符“,”分開

) ON [PRIMARY] TEXTimage_ON [PRIMARY]

GO

下面，我們來往數據庫中添加1000萬條數據：

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,治安支隊,外事科','這是最先的25萬條記錄')

set @i=@i+1

end

GO

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','辦公室','辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科','這是中間的25萬條記錄')

set @i=@i+1

end

GO

declare @h int

set @h=1

while @h<=100

begin

declare @i int

set @i=2002

while @i<=2003

begin

declare @j int

set @j=0

while @j<50

begin

declare @k int

set @k=0

while @k<50

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'通信科','辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,外事科','這是最后的50萬條記錄')

set @k=@k+1

end

set @j=@j+1

end

set @i=@i+1

end

set @h=@h+1

end

GO

declare @i int

set @i=1

while @i<=9000000

begin

insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通信科','通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊,特勤支隊,交巡警支隊,經偵支隊,戶政科,治安支隊,外事科','這是最后添加的900萬條記錄')

set @i=@i+1000000

end

GO

通過以上語句，我們創建了25萬條由于2004年2月5日發布的記錄，25萬條由辦公室于2004年9月6日發布的記錄，2002年和2003年各100個2500條相同日期、不同分秒的記錄（共50萬條），還有由通信科于2004年5月5日發布的900萬條記錄，合計1000萬條。

一、因情制宜，建立“適當”的索引

建立“適當”的索引是實現查詢優化的首要前提。

索引（index）是除表之外另一重要的、用戶定義的存儲在物理介質上的數據結構。當根據索引碼的值搜索數據時，索引提供了對數據的快速訪問。事實上，沒有索引,數據庫也能根據select語句成功地檢索到結果，但隨著表變得越來越大，使用“適當”的索引的效果就越來越明顯。注意，在這句話中，我們用了“適當”這個詞，這是因為，如果使用索引時不認真考慮其實現過程，索引既可以提高也會破壞數據庫的工作性能。

（一）深入淺出理解索引結構

實際上，您可以把索引理解為一種特殊的目錄。微軟的SQL SERVER提供了兩種索引：聚集索引（clustered index，也稱聚類索引、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）。下面，我們舉例來說明一下聚集索引和非聚集索引的區別：

其實，我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那么就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會將您的字典翻到最后部分，因為“張”的拼音是“zhang”。也就是說，字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的內容。

我們把這種正文內容本身就是一種按照一定規則排列的目錄稱為“聚集索引”。

如果您認識某個字，您可以快速地從自動中查到這個字。但您也可能會遇到您不認識的字，不知道它的發音，這時候，您就不能按照剛才的方法找到您要查的字，而需要去根據“偏旁部首”查到您要找的字，然后根據這個字后的頁碼直接翻到某頁來找到您要找的字。但您結合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字并不是真正的分別位于“張”字的上下方，現在您看到的連續的“馳、張、弩”三字實際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個過程，先找到目錄中的結果，然后再翻到您所需要的頁碼。

我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。

通過以上例子，我們可以理解到什么是“聚集索引”和“非聚集索引”。

進一步引申一下，我們可以很容易的理解：每個表只能有一個聚集索引，因為目錄只能按照一種方法進行排序。

（二）何時使用聚集索引或非聚集索引

下面的表總結了何時使用聚集索引或非聚集索引（很重要）。

動作描述

使用聚集索引

使用非聚集索引

列經常被分組排序

應

應

返回某范圍內的數據

應

不應

一個或極少不同值

不應

不應

小數目的不同值

應

不應

大數目的不同值

不應

應

頻繁更新的列

不應

應

外鍵列

應

應

主鍵列

應

應

頻繁修改索引列

不應

應

事實上，我們可以通過前面聚集索引和非聚集索引的定義的例子來理解上表。如：返回某范圍內的數據一項。比如您的某個表有一個時間列，恰好您把聚合索引建立在了該列，這時您查詢2004年1月1日至2004年10月1日之間的全部數據時，這個速度就將是很快的，因為您的這本字典正文是按日期進行排序的，聚類索引只需要找到要檢索的所有數據中的開頭和結尾數據即可；而不像非聚集索引，必須先查到目錄中查到每一項數據對應的頁碼，然后再根據頁碼查到具體內容。

（三）結合實際，談索引使用的誤區

理論的目的是應用。雖然我們剛才列出了何時應使用聚集索引或非聚集索引，但在實踐中以上規則卻很容易被忽視或不能根據實際情況進行綜合分析。下面我們將根據在實踐中遇到的實際問題來談一下索引使用的誤區，以便于大家掌握索引建立的方法。

1、主鍵就是聚集索引

這種想法筆者認為是極端錯誤的，是對聚集索引的一種浪費。雖然SQL SERVER默認是在主鍵上建立聚集索引的。

通常，我們會在每個表中都建立一個ID列，以區分每條數據，并且這個ID列是自動增大的，步長一般為1。我們的這個辦公自動化的實例中的列Gid就是如此。此時，如果我們將這個列設為主鍵，SQL SERVER會將此列默認為聚集索引。這樣做有好處，就是可以讓您的數據在數據庫中按照ID進行物理排序，但筆者認為這樣做意義不大。

顯而易見，聚集索引的優勢是很明顯的，而每個表中只能有一個聚集索引的規則，這使得聚集索引變得更加珍貴。

從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據查詢要求，迅速縮小查詢范圍，避免全表掃描。在實際應用中，因為ID號是自動生成的，我們并不知道每條記錄的ID號，所以我們很難在實踐中用ID號來進行查詢。這就使讓ID號這個主鍵作為聚集索引成為一種資源浪費。其次，讓每個ID號都不同的字段作為聚集索引也不符合“大數目的不同值情況下不應建立聚合索引”規則；當然，這種情況只是針對用戶經常修改記錄內容，特別是索引項的時候會負作用，但對于查詢速度并沒有影響。

在辦公自動化系統中，無論是系統首頁顯示的需要用戶簽收的文件、會議還是用戶進行文件查詢等任何情況下進行數據查詢都離不開字段的是“日期”還有用戶本身的“用戶名”。

通常，辦公自動化的首頁會顯示每個用戶尚未簽收的文件或會議。雖然我們的where語句可以僅僅限制當前用戶尚未簽收的情況，但如果您的系統已建立了很長時間，并且數據量很大，那么，每次每個用戶打開首頁的時候都進行一次全表掃描，這樣做意義是不大的，絕大多數的用戶1個月前的文件都已經瀏覽過了，這樣做只能徒增數據庫的開銷而已。事實上，我們完全可以讓用戶打開系統首頁時，數據庫僅僅查詢這個用戶近3個月來未閱覽的文件，通過“日期”這個字段來限制表掃描，提高查詢速度。如果您的辦公自動化系統已經建立的2年，那么您的首頁顯示速度理論上將是原來速度8倍，甚至更快。

在這里之所以提到“理論上”三字，是因為如果您的聚集索引還是盲目地建在ID這個主鍵上時，您的查詢速度是沒有這么高的，即使您在“日期”這個字段上建立的索引（非聚合索引）。下面我們就來看一下在1000萬條數據量的情況下各種查詢的速度表現（3個月內的數據為25萬條）：

（1）僅在主鍵上建立聚集索引，并且不劃分時間段：

Select gid,fariqi,neibuyonghu,title from tgongwen

用時：128470毫秒（即：128秒）

（2）在主鍵上建立聚集索引，在fariq上建立非聚集索引：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用時：53763毫秒（54秒）

（3）將聚合索引建立在日期列（fariqi）上：

select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi> dateadd(day,-90,getdate())

用時：2423毫秒（2秒）

雖然每條語句提取出來的都是25萬條數據，各種情況的差異卻是巨大的，特別是將聚集索引建立在日期列時的差異。事實上，如果您的數據庫真的有1000萬容量的話，把主鍵建立在ID列上，就像以上的第1、2種情況，在網頁上的表現就是超時，根本就無法顯示。這也是我摒棄ID列作為聚集索引的一個最重要的因素。

得出以上速度的方法是：在各個select語句前加：declare @d datetime

set @d=getdate()

并在select語句后加：

select [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())

2、只要建立索引就能顯著提高查詢速度

事實上，我們可以發現上面的例子中，第2、3條語句完全相同，且建立索引的字段也相同；不同的僅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查詢速度卻有著天壤之別。所以，并非是在任何字段上簡單地建立索引就能提高查詢速度。

從建表的語句中，我們可以看到這個有著1000萬數據的表中fariqi字段有5003個不同記錄。在此字段上建立聚合索引是再合適不過了。在現實中，我們每天都會發幾個文件，這幾個文件的發文日期就相同，這完全符合建立聚集索引要求的：“既不能絕大多數都相同，又不能只有極少數相同”的規則。由此看來，我們建立“適當”的聚合索引對于我們提高查詢速度是非常重要的。

3、把所有需要提高查詢速度的字段都加進聚集索引，以提高查詢速度

上面已經談到：在進行數據查詢時都離不開字段的是“日期”還有用戶本身的“用戶名”。既然這兩個字段都是如此的重要，我們可以把他們合并起來，建立一個復合索引（compound index）。

很多人認為只要把任何字段加進聚集索引，就能提高查詢速度，也有人感到迷惑：如果把復合的聚集索引字段分開查詢，那么查詢速度會減慢嗎？帶著這個問題，我們來看一下以下的查詢速度（結果集都是25萬條數據）：（日期列fariqi首先排在復合聚集索引的起始列，用戶名neibuyonghu排在后列）

（1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

查詢速度：2513毫秒

（2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='辦公室'

查詢速度：2516毫秒

（3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='辦公室'

查詢速度：60280毫秒

從以上試驗中，我們可以看到如果僅用聚集索引的起始列作為查詢條件和同時用到復合聚集索引的全部列的查詢速度是幾乎一樣的，甚至比用上全部的復合索引列還要略快（在查詢結果集數目一樣的情況下）；而如果僅用復合聚集索引的非起始列作為查詢條件的話，這個索引是不起任何作用的。當然，語句1、2的查詢速度一樣是因為查詢的條目數一樣，如果復合索引的所有列都用上，而且查詢結果少的話，這樣就會形成“索引覆蓋”，因而性能可以達到最優。同時，請記住：無論您是否經常使用聚合索引的其他列，但其前導列一定要是使用最頻繁的列。

（四）其他書上沒有的索引使用經驗總結

1、用聚合索引比用不是聚合索引的主鍵速度快

下面是實例語句：（都是提取25萬條數據）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

使用時間：3326毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

使用時間：4470毫秒

這里，用聚合索引比用不是聚合索引的主鍵速度快了近1/4。

2、用聚合索引比用一般的主鍵作order by時速度快，特別是在小數據量情況下

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

用時：12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

用時：18843

這里，用聚合索引比用一般的主鍵作order by時，速度快了3/10。事實上，如果數據量很小的話，用聚集索引作為排序列要比使用非聚集索引速度快得明顯的多；而數據量如果很大的話，如10萬以上，則二者的速度差別不明顯。

3、使用聚合索引內的時間段，搜索時間會按數據占整個數據表的百分比成比例減少，而無論聚合索引使用了多少個

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

用時：6343毫秒（提取100萬條）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

用時：3170毫秒（提取50萬條）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

用時：3326毫秒（和上句的結果一模一樣。如果采集的數量一樣，那么用大于號和等于號是一樣的）

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

用時：3280毫秒

　　4 、日期列不會因為有分秒的輸入而減慢查詢速度

下面的例子中，共有100萬條數據，2004年1月1日以后的數據有50萬條，但只有兩個不同的日期，日期精確到日；之前有數據50萬條，有5000個不同的日期，日期精確到秒。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

用時：6390毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

用時：6453毫秒

（五）其他注意事項

“水可載舟，亦可覆舟”，索引也一樣。索引有助于提高檢索性能，但過多或不當的索引也會導致系統低效。因為用戶在表中每加進一個索引，數據庫就要做更多的工作。過多的索引甚至會導致索引碎片。

所以說，我們要建立一個“適當”的索引體系，特別是對聚合索引的創建，更應精益求精，以使您的數據庫能得到高性能的發揮。

當然，在實踐中，作為一個盡職的數據庫管理員，您還要多測試一些方案，找出哪種方案效率最高、最為有效。

二、改善SQL語句

很多人不知道SQL語句在SQL SERVER中是如何執行的，他們擔心自己所寫的SQL語句會被SQL SERVER誤解。比如：

select * from table1 where name='zhangsan' and tID > 10000

和執行:

select * from table1 where tID > 10000 and name='zhangsan'

一些人不知道以上兩條語句的執行效率是否一樣，因為如果簡單的從語句先后上看，這兩個語句的確是不一樣，如果tID是一個聚合索引，那么后一句僅僅從表的10000條以后的記錄中查找就行了；而前一句則要先從全表中查找看有幾個name='zhangsan'的，而后再根據限制條件條件tID>10000來提出查詢結果。

事實上，這樣的擔心是不必要的。SQL SERVER中有一個“查詢分析優化器”，它可以計算出where子句中的搜索條件并確定哪個索引能縮小表掃描的搜索空間，也就是說，它能實現自動優化。

雖然查詢優化器可以根據where子句自動的進行查詢優化，但大家仍然有必要了解一下“查詢優化器”的工作原理，如非這樣，有時查詢優化器就會不按照您的本意進行快速查詢。

在查詢分析階段，查詢優化器查看查詢的每個階段并決定限制需要掃描的數據量是否有用。如果一個階段可以被用作一個掃描參數（SARG），那么就稱之為可優化的，并且可以利用索引快速獲得所需數據。

SARG的定義：用于限制搜索的一個操作，因為它通常是指一個特定的匹配，一個值得范圍內的匹配或者兩個以上條件的AND連接。形式如下：

列名操作符 <常數或變量>

或

<常數或變量> 操作符列名

列名可以出現在操作符的一邊，而常數或變量出現在操作符的另一邊。如：

Name=’張三’

價格>5000

5000<價格

Name=’張三’ and 價格>5000

如果一個表達式不能滿足SARG的形式，那它就無法限制搜索的范圍了，也就是SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個索引對于不滿足SARG形式的表達式來說是無用的。

介紹完SARG后，我們來總結一下使用SARG以及在實踐中遇到的和某些資料上結論不同的經驗：

1、Like語句是否屬于SARG取決于所使用的通配符的類型

如：name like ‘張%’ ，這就屬于SARG

而：name like ‘%張’ ,就不屬于SARG。

原因是通配符%在字符串的開通使得索引無法使用。

2、or 會引起全表掃描

Name=’張三’ and 價格>5000 符號SARG，而：Name=’張三’ or 價格>5000 則不符合SARG。使用or會引起全表掃描。

3、非操作符、函數引起的不滿足SARG形式的語句

不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外還有函數。下面就是幾個不滿足SARG形式的例子：

ABS(價格)<5000

Name like ‘%三’

有些表達式，如：

WHERE 價格*2>5000

SQL SERVER也會認為是SARG，SQL SERVER會將此式轉化為：

WHERE 價格>2500/2

但我們不推薦這樣使用，因為有時SQL SERVER不能保證這種轉化與原始表達式是完全等價的。

4、IN 的作用相當與OR

語句：

Select * from table1 where tid in (2,3)

和

Select * from table1 where tid=2 or tid=3

是一樣的，都會引起全表掃描，如果tid上有索引，其索引也會失效。

5、盡量少用NOT

6、exists 和 in 的執行效率是一樣的

很多資料上都顯示說，exists要比in的執行效率要高，同時應盡可能的用not exists來代替not in。但事實上，我試驗了一下，發現二者無論是前面帶不帶not，二者之間的執行效率都是一樣的。因為涉及子查詢，我們試驗這次用SQL SERVER自帶的pubs數據庫。運行前我們可以把SQL SERVER的statistics I/O狀態打開。

（1）select title,price from titles where title_id in (select title_id from sales where qty>30)

該句的執行結果為：

表 'sales'。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。

表 'titles'。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

（2）select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)

第二句的執行結果為：

表 'sales'。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。

表 'titles'。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

我們從此可以看到用exists和用in的執行效率是一樣的。

7、用函數charindex()和前面加通配符%的LIKE執行效率一樣

前面，我們談到，如果在LIKE前面加上通配符%，那么將會引起全表掃描，所以其執行效率是低下的。但有的資料介紹說，用函數charindex()來代替LIKE速度會有大的提升，經我試驗，發現這種說明也是錯誤的：

select gid,title,fariqi,reader from tgongwen where charindex('刑偵支隊',reader)>0 and fariqi>'2004-5-5'

用時：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

select gid,title,fariqi,reader from tgongwen where reader like '%' + '刑偵支隊' + '%' and fariqi>'2004-5-5'

用時：7秒，另外：掃描計數 4，邏輯讀 7155 次，物理讀 0 次，預讀 0 次。

8、union并不絕對比or的執行效率高

我們前面已經談到了在where子句中使用or會引起全表掃描，一般的，我所見過的資料都是推薦這里用union來代替or。事實證明，這種說法對于大部分都是適用的。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000

用時：68秒。掃描計數 1，邏輯讀 404008 次，物理讀 283 次，預讀 392163 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000

用時：9秒。掃描計數 8，邏輯讀 67489 次，物理讀 216 次，預讀 7499 次。

看來，用union在通常情況下比用or的效率要高的多。

但經過試驗，筆者發現如果or兩邊的查詢列是一樣的話，那么用union則反倒和用or的執行速度差很多，雖然這里union掃描的是索引，而or掃描的是全表。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'

用時：6423毫秒。掃描計數 2，邏輯讀 14726 次，物理讀 1 次，預讀 7176 次。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

union

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where　fariqi='2004-2-5'

用時：11640毫秒。掃描計數 8，邏輯讀 14806 次，物理讀 108 次，預讀 1144 次。

9、字段提取要按照“需多少、提多少”的原則，避免“select *”

我們來做一個試驗：

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時：4673毫秒

select top 10000 gid,fariqi,title from tgongwen order by gid desc

用時：1376毫秒

select top 10000 gid,fariqi from tgongwen order by gid desc

用時：80毫秒

由此看來，我們每少提取一個字段，數據的提取速度就會有相應的提升。提升的速度還要看您舍棄的字段的大小來判斷。

10、count(*)不比count(字段)慢

某些資料上說：用*會統計所有列，顯然要比一個世界的列名效率低。這種說法其實是沒有根據的。我們來看：

select count(*) from Tgongwen

用時：1500毫秒

select count(gid) from Tgongwen

用時：1483毫秒

select count(fariqi) from Tgongwen

用時：3140毫秒

select count(title) from Tgongwen

用時：52050毫秒

從以上可以看出，如果用count(*)和用count(主鍵)的速度是相當的，而count(*)卻比其他任何除主鍵以外的字段匯總速度要快，而且字段越長，匯總的速度就越慢。我想，如果用count(*)， SQL SERVER可能會自動查找最小字段來匯總的。當然，如果您直接寫count(主鍵)將會來的更直接些。

11、order by按聚集索引列排序效率最高

我們來看：（gid是主鍵，fariqi是聚合索引列）

select top 10000 gid,fariqi,reader,title from tgongwen

用時：196 毫秒。掃描計數 1，邏輯讀 289 次，物理讀 1 次，預讀 1527 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

用時：4720毫秒。掃描計數 1，邏輯讀 41956 次，物理讀 0 次，預讀 1287 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

用時：4736毫秒。掃描計數 1，邏輯讀 55350 次，物理讀 10 次，預讀 775 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

用時：173毫秒。掃描計數 1，邏輯讀 290 次，物理讀 0 次，預讀 0 次。

select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

用時：156毫秒。掃描計數 1，邏輯讀 289 次，物理讀 0 次，預讀 0 次。

從以上我們可以看出，不排序的速度以及邏輯讀次數都是和“order by 聚集索引列” 的速度是相當的，但這些都比“order by 非聚集索引列”的查詢速度是快得多的。

同時，按照某個字段進行排序的時候，無論是正序還是倒序，速度是基本相當的。

12、高效的TOP

事實上，在查詢和提取超大容量的數據集時，影響數據庫響應時間的最大因素不是數據查找，而是物理的I/0操作。如：

select top 10 * from (

select top 10000 gid,fariqi,title from tgongwen

where neibuyonghu='辦公室'

order by gid desc) as a

order by gid asc

這條語句，從理論上講，整條語句的執行時間應該比子句的執行時間長，但事實相反。因為，子句執行后返回的是10000條記錄，而整條語句僅返回10條語句，所以影響數據庫響應時間最大的因素是物理I/O操作。而限制物理I/O操作此處的最有效方法之一就是使用TOP關鍵詞了。TOP關鍵詞是SQL SERVER中經過系統優化過的一個用來提取前幾條或前幾個百分比數據的詞。經筆者在實踐中的應用，發現TOP確實很好用，效率也很高。但這個詞在另外一個大型數據庫ORACLE中卻沒有，這不能說不是一個遺憾，雖然在ORACLE中可以用其他方法（如：rownumber）來解決。在以后的關于“實現千萬級數據的分頁顯示存儲過程”的討論中，我們就將用到TOP這個關鍵詞。

到此為止，我們上面討論了如何實現從大容量的數據庫中快速地查詢出您所需要的數據方法。當然，我們介紹的這些方法都是“軟”方法，在實踐中，我們還要考慮各種“硬”因素，如：網絡性能、服務器的性能、操作系統的性能，甚至網卡、交換機等。

　　三、實現小數據量和海量數據的通用分頁顯示存儲過程

建立一個web 應用，分頁瀏覽功能必不可少。這個問題是數據庫處理中十分常見的問題。經典的數據分頁方法是:ADO 紀錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標）來實現分頁。但這種分頁方法僅適用于較小數據量的情形，因為游標本身有缺點：游標是存放在內存中，很費內存。游標一建立，就將相關的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數據，根據取出數據條件的不同進行不同的操作。而對于多表和大表中定義的游標（大的數據集合）循環很容易使程序進入一個漫長的等待甚至死機。

更重要的是，對于非常大的數據模型而言，分頁檢索時，如果按照傳統的每次都加載整個數據源的方法是非常浪費資源的。現在流行的分頁方法一般是檢索頁面大小的塊區的數據，而非檢索所有的數據，然后單步執行當前行。

最早較好地實現這種根據頁面大小和頁碼來提取數據的方法大概就是“俄羅斯存儲過程”。這個存儲過程用了游標，由于游標的局限性，所以這個方法并沒有得到大家的普遍認可。

后來，網上有人改造了此存儲過程，下面的存儲過程就是結合我們的辦公自動化實例寫的分頁存儲過程：

CREATE procedure pagination1

(@pagesize int,　--頁面大小，如每頁存儲20條記錄

@pageindex int　 --當前頁碼

)

as

set nocount on

begin

declare @indextable table(id int identity(1,1),nid int)　--定義表變量

declare @PageLowerBound int　--定義此頁的底碼

declare @PageUpperBound int　--定義此頁的頂碼

set @PageLowerBound=(@pageindex-1)*@pagesize

set @PageUpperBound=@PageLowerBound+@pagesize

set rowcount @PageUpperBound

insert into @indextable(nid) select gid from TGongwen where fariqi >dateadd(day,-365,getdate()) order by fariqi desc

select O.gid,O.mid,O.title,O.fadanwei,O.fariqi from TGongwen O,@indextable t where O.gid=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

end

set nocount off

以上存儲過程運用了SQL SERVER的最新技術――表變量。應該說這個存儲過程也是一個非常優秀的分頁存儲過程。當然，在這個過程中，您也可以把其中的表變量寫成臨時表：CREATE TABLE #Temp。但很明顯，在SQL SERVER中，用臨時表是沒有用表變量快的。所以筆者剛開始使用這個存儲過程時，感覺非常的不錯，速度也比原來的ADO的好。但后來，我又發現了比此方法更好的方法。

筆者曾在網上看到了一篇小短文《從數據表中取出第n條到第m條的記錄的方法》，全文如下：

從publish 表中取出第 n 條到第 m 條的記錄：

SELECT TOP m-n+1 *

FROM publish

WHERE (id NOT IN

(SELECT TOP n-1 id

FROM publish))

id 為publish 表的關鍵字

我當時看到這篇文章的時候，真的是精神為之一振，覺得思路非常得好。等到后來，我在作辦公自動化系統（ASP.net+ C#＋SQL SERVER）的時候，忽然想起了這篇文章，我想如果把這個語句改造一下，這就可能是一個非常好的分頁存儲過程。于是我就滿網上找這篇文章，沒想到，文章還沒找到，卻找到了一篇根據此語句寫的一個分頁存儲過程，這個存儲過程也是目前較為流行的一種分頁存儲過程，我很后悔沒有爭先把這段文字改造成存儲過程：

CREATE PROCEDURE pagination2

(

@SQL nVARCHAR(4000),　　--不帶排序語句的SQL語句

@Page int,　　　　　　　--頁碼

@RecsPerPage int,　　　 --每頁容納的記錄數

@ID VARCHAR(255),　　　 --需要排序的不重復的ID號

@Sort VARCHAR(255)　　　--排序字段及規則

)

AS

DECLARE @Str nVARCHAR(4000)

SET @Str='SELECT　 TOP '+CAST(@RecsPerPage AS VARCHAR(20))+' * FROM ('+@SQL+') T WHERE T.'+@ID+'NOT IN

(SELECT　 TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '+@ID+' FROM ('+@SQL+') T9 ORDER BY '+@Sort+') ORDER BY '+@Sort

PRINT @Str

EXEC sp_ExecuteSql @Str

GO

其實，以上語句可以簡化為：

SELECT TOP 頁大小 *

FROM Table1

WHERE (ID NOT IN

(SELECT TOP 頁大小*頁數 id

FROM 表

ORDER BY id))

ORDER BY ID

但這個存儲過程有一個致命的缺點，就是它含有NOT IN字樣。雖然我可以把它改造為：

SELECT TOP 頁大小 *

FROM Table1

WHERE not exists

(select * from (select top (頁大小*頁數) * from table1 order by id) b where b.id=a.id )

order by id

即，用not exists來代替not in，但我們前面已經談過了，二者的執行效率實際上是沒有區別的。

既便如此，用TOP 結合NOT IN的這個方法還是比用游標要來得快一些。

雖然用not exists并不能挽救上個存儲過程的效率，但使用SQL SERVER中的TOP關鍵字卻是一個非常明智的選擇。因為分頁優化的最終目的就是避免產生過大的記錄集，而我們在前面也已經提到了TOP的優勢，通過TOP 即可實現對數據量的控制。

在分頁算法中，影響我們查詢速度的關鍵因素有兩點：TOP和NOT IN。TOP可以提高我們的查詢速度，而NOT IN會減慢我們的查詢速度，所以要提高我們整個分頁算法的速度，就要徹底改造NOT IN，同其他方法來替代它。

我們知道，幾乎任何字段，我們都可以通過max(字段)或min(字段)來提取某個字段中的最大或最小值，所以如果這個字段不重復，那么就可以利用這些不重復的字段的max或min作為分水嶺，使其成為分頁算法中分開每頁的參照物。在這里，我們可以用操作符“>”或“<”號來完成這個使命，使查詢語句符合SARG形式。如：

Select top 10 * from table1 where id>200

于是就有了如下分頁方案：

select top 頁大小 *

from table1

where id>

(select max (id) from

(select top ((頁碼-1)*頁大小) id from table1 order by id) as T

)

order by id

在選擇即不重復值，又容易分辨大小的列時，我們通常會選擇主鍵。下表列出了筆者用有著1000萬數據的辦公自動化系統中的表，在以GID（GID是主鍵，但并不是聚集索引。）為排序列、提取gid,fariqi,title字段，分別以第1、10、100、500、1000、1萬、10萬、25萬、50萬頁為例，測試以上三種分頁方案的執行速度：（單位：毫秒）

頁　碼

方案1

方案2

方案3

1

60

30

76

10

46

16

63

100

1076

720

130

500

540

12943

83

1000

17110

470

250

1萬

24796

4500

140

10萬

38326

42283

1553

25萬

28140

128720

2330

50萬

121686

127846

7168

從上表中，我們可以看出，三種存儲過程在執行100頁以下的分頁命令時，都是可以信任的，速度都很好。但第一種方案在執行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。

在確定了第三種分頁方案后，我們可以據此寫一個存儲過程。大家知道SQL SERVER的存儲過程是事先編譯好的SQL語句，它的執行效率要比通過WEB頁面傳來的SQL語句的執行效率要高。下面的存儲過程不僅含有分頁方案，還會根據頁面傳來的參數來確定是否進行數據總數統計。

-- 獲取指定頁的數據

CREATE PROCEDURE pagination3

@tblName　 varchar(255),　　　 -- 表名

@strGetFields varchar(1000) = '*',　-- 需要返回的列

@fldName varchar(255)='',　　　-- 排序的字段名

@PageSize　 int = 10,　　　　　-- 頁尺寸

@PageIndex　int = 1,　　　　　 -- 頁碼

@doCount　bit = 0,　 -- 返回記錄總數, 非 0 值則返回

@OrderType bit = 0,　-- 設置排序類型, 非 0 值則降序

@strWhere　varchar(1500) = ''　-- 查詢條件 (注意: 不要加 where)

AS

declare @strSQL　 varchar(5000)　　　 -- 主語句

declare @strTmp　 varchar(110)　　　　-- 臨時變量

declare @strOrder varchar(400)　　　　-- 排序類型

if @doCount != 0

begin

if @strWhere !=''

set @strSQL = "select count(*) as Total from [" + @tblName + "] where "+@strWhere

else

set @strSQL = "select count(*) as Total from [" + @tblName + "]"

end

--以上代碼的意思是如果@doCount傳遞過來的不是0，就執行總數統計。以下的所有代碼都是@doCount為0的情況

else

begin

if @OrderType != 0

begin

set @strTmp = "<(select min"

set @strOrder = " order by [" + @fldName +"] desc"

--如果@OrderType不是0，就執行降序，這句很重要！

end

else

begin

set @strTmp = ">(select max"

set @strOrder = " order by [" + @fldName +"] asc"

end

if @PageIndex = 1

begin

if @strWhere != ''

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "　from [" + @tblName + "] where " + @strWhere + " " + @strOrder

else

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "　from ["+ @tblName + "] "+ @strOrder

--如果是第一頁就執行以上代碼，這樣會加快執行速度

end

else

begin

--以下代碼賦予了@strSQL以真正執行的SQL代碼

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "　from ["

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from [" + @tblName + "]" + @strOrder + ") as tblTmp)"+ @strOrder

if @strWhere != ''

set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "　from ["

+ @tblName + "] where [" + @fldName + "]" + @strTmp + "(["

+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["

+ @fldName + "] from [" + @tblName + "] where " + @strWhere + " "

+ @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder

end

end

exec (@strSQL)

GO

上面的這個存儲過程是一個通用的存儲過程，其注釋已寫在其中了。

在大數據量的情況下，特別是在查詢最后幾頁的時候，查詢時間一般不會超過9秒；而用其他存儲過程，在實踐中就會導致超時，所以這個存儲過程非常適用于大容量數據庫的查詢。

筆者希望能夠通過對以上存儲過程的解析，能給大家帶來一定的啟示，并給工作帶來一定的效率提升，同時希望同行提出更優秀的實時數據分頁算法。

四、聚集索引的重要性和如何選擇聚集索引

在上一節的標題中，筆者寫的是：實現小數據量和海量數據的通用分頁顯示存儲過程。這是因為在將本存儲過程應用于“辦公自動化”系統的實踐中時，筆者發現這第三種存儲過程在小數據量的情況下，有如下現象：

1、分頁速度一般維持在1秒和3秒之間。

2、在查詢最后一頁時，速度一般為5秒至8秒，哪怕分頁總數只有3頁或30萬頁。

雖然在超大容量情況下，這個分頁的實現過程是很快的，但在分前幾頁時，這個1－3秒的速度比起第一種甚至沒有經過優化的分頁方法速度還要慢，借用戶的話說就是“還沒有ACCESS數據庫速度快”，這個認識足以導致用戶放棄使用您開發的系統。

筆者就此分析了一下，原來產生這種現象的癥結是如此的簡單，但又如此的重要：排序的字段不是聚集索引！

本篇文章的題目是：“查詢優化及分頁算法方案”。筆者只所以把“查詢優化”和“分頁算法”這兩個聯系不是很大的論題放在一起，就是因為二者都需要一個非常重要的東西――聚集索引。

在前面的討論中我們已經提到了，聚集索引有兩個最大的優勢：

1、以最快的速度縮小查詢范圍。

2、以最快的速度進行字段排序。

第1條多用在查詢優化時，而第2條多用在進行分頁時的數據排序。

而聚集索引在每個表內又只能建立一個，這使得聚集索引顯得更加的重要。聚集索引的挑選可以說是實現“查詢優化”和“高效分頁”的最關鍵因素。

但要既使聚集索引列既符合查詢列的需要，又符合排序列的需要，這通常是一個矛盾。

筆者前面“索引”的討論中，將fariqi，即用戶發文日期作為了聚集索引的起始列，日期的精確度為“日”。這種作法的優點，前面已經提到了，在進行劃時間段的快速查詢中，比用ID主鍵列有很大的優勢。

但在分頁時，由于這個聚集索引列存在著重復記錄，所以無法使用max或min來最為分頁的參照物，進而無法實現更為高效的排序。而如果將ID主鍵列作為聚集索引，那么聚集索引除了用以排序之外，沒有任何用處，實際上是浪費了聚集索引這個寶貴的資源。

為解決這個矛盾，筆者后來又添加了一個日期列，其默認值為getdate()。用戶在寫入記錄時，這個列自動寫入當時的時間，時間精確到毫秒。即使這樣，為了避免可能性很小的重合，還要在此列上創建UNIQUE約束。將此日期列作為聚集索引列。

有了這個時間型聚集索引列之后，用戶就既可以用這個列查找用戶在插入數據時的某個時間段的查詢，又可以作為唯一列來實現max或min，成為分頁算法的參照物。

經過這樣的優化，筆者發現，無論是大數據量的情況下還是小數據量的情況下，分頁速度一般都是幾十毫秒，甚至0毫秒。而用日期段縮小范圍的查詢速度比原來也沒有任何遲鈍。

聚集索引是如此的重要和珍貴，所以筆者總結了一下，一定要將聚集索引建立在：

1、您最頻繁使用的、用以縮小查詢范圍的字段上；

2、您最頻繁使用的、需要排序的字段上。

應用程序設計

應用程序設計在決定使用 Microsoft? SQL Server? 2000 的系統的性能方面起關鍵作用。將客戶端視為控制實體而非數據庫服務器。客戶端確定查詢類型、何時提交查詢以及如何處理查詢結果。這反過來對服務器上的鎖類型和持續時間、I/O 活動量以及處理(CPU) 負荷等產生主要影響，并由此影響總體性能的優劣。

正因為如此，在應用程序的設計階段做出正確決策十分重要。然而，即使在使用總控應用程序時（這種情況下似乎不可能更改客戶端應用程序）出現性能問題，也不會改變影響性能的根本因素：客戶端具有支配作用，如果不更改客戶端則許多性能問題都無法解決。設計優秀的應用程序允許 SQL Server 支持成千上萬的并發用戶。反之，設計差的應用程序會防礙即使是最強大的服務器平臺處理少數用戶的請求。

客戶端應用程序的設計準則包括：

·???????????????????? 消除過多的網絡流量。

客戶端和 SQL Server 之間的網絡往返通常是數據庫應用程序性能較差的首要原因，甚至超過了服務器和客戶端之間傳送的數據量這一因素的影響。網絡往返描述在客戶端應用程序和 SQL Server 之間為每個批處理和結果集發送的會話流量。通過使用存儲過程，可以將網絡往返減到最小。例如，如果應用程序根據從 SQL Server 收到的數據值采取不同的操作，只要可能就應直接在存儲過程中做出決定，從而消除過多的網絡流量。

如果存儲過程中有多個語句，則默認情況下，SQL Server 在每個語句完成時給客戶端應用程序發送一條消息，詳細說明每個語句所影響的行數。大多數應用程序不需要這些消息。如果確信應用程序不需要它們，可以禁用這些消息，以提高慢速網絡的性能。請使用 SET NOCOUNT 會話設置為應用程序禁用這些消息。有關更多信息，請參見 SET NOCOUNT。

·???????????????????? 使用小結果集。

檢索沒必要大的結果集（如包含上千行）并在客戶端瀏覽將增加 CPU 和網絡 I/O 的負載，使應用程序的遠程使用能力降低并限制多用戶可伸縮性。最好將應用程序設計為提示用戶輸入足夠的信息，以便查詢提交后生成大小適中的結果集。有關更多信息，請參見使用高效數據檢索優化應用程序性能。

可幫助實現上述目標的應用程序設計技術包括：在生成查詢時對通配符進行控制，強制某些輸入字段，不允許特殊查詢，以及使用 TOP、PERCENT 或 SET ROWCOUNT 等 Transact-SQL 語句限制查詢返回的行數。有關更多信息，請參見使用 TOP 和PERCENT 限制結果集和 SET ROWCOUNT。

·???????????????????? 允許在用戶需要重新控制應用程序時取消正在執行的查詢。

應用程序決不應強迫用戶重新啟動客戶機以取消查詢。無視這一點將導致無法解決的性能問題。如果應用程序取消查詢（例如使用開放式數據庫連接 (ODBC) sqlcancel 函數取消查詢），應對事務級別予以適當的考慮。例如，取消查詢并不會提交或回滾用戶定義的事務。取消查詢后，所有在事務內獲取的鎖都將保留。因此，在取消查詢后始終要提交或回滾事務。同樣的情況也適用于可用于取消查詢的 DB-Library 和其它應用程序接口 (API)。

·???????????????????? 始終實現查詢或鎖定超時。

不要讓查詢無限期運行。調用適當的 API 以設置查詢超時。例如，使用 ODBC SQLSetStmtOption 函數。

有關設置查詢超時的更多信息，請參見 ODBC API 文檔。

有關設置鎖定超時的更多信息，請參見自定義鎖超時。

·???????????????????? 不要使用不允許顯式控制發送到 SQL Server 的 SQL 語句的應用程序開發工具。

如果工具基于更高級的對象透明地生成 Transact-SQL 語句，而且不提供諸如查詢取消、查詢超時和完全事務控制等關鍵功能，則不要使用這類工具。如果應用程序生成透明的 SQL 語句，通常不可能維護好的性能或解決性能問題，因為在這種情況下不允許對事務和鎖定問題進行顯式控制，而這一點對性能狀況至關重要。

·???????????????????? 不要將決策支持和聯機事務處理 (OLTP) 查詢混在一起。有關更多信息，請參見聯機事務處理與決策支持。

·???????????????????? 只在必要時才使用游標。

游標是關系數據庫中的有用工具，但使用游標完成任務始終比使用面向集合的 SQL 語句花費多。

當使用面向集合的 SQL 語句時，客戶端應用程序讓服務器更新滿足指定條件的記錄集。服務器決定如何作為單個工作單元完成更新。當通過游標更新時，客戶端應用程序要求服務器為每行維護行鎖或版本信息，而這只是為了客戶端在提取行后請求更新行。

而且，使用游標意味著服務器通常要在臨時存儲中維護客戶端的狀態信息，如用戶在服務器上的當前行集。為眾多客戶端維護這類狀態信息需消耗大量的服務器資源。對于關系數據庫，更好的策略是讓客戶端應用程序快速進出，以便在各次調用之間不在服務器上維護客戶端的狀態信息。面向集合的 SQL 語句支持此策略。

然而，如果查詢使用游標，請確定如果使用更高效的游標類型（如快速只進游標）或單個查詢能否更高效地編寫游標查詢。有關更多信息，請參見使用高效數據檢索優化應用程序性能。

·???????????????????? 使事務盡可能簡短。有關更多信息，請參見事務和批處理對應用程序性能的影響。

·???????????????????? 使用存儲過程。有關更多信息，請參見存儲過程對應用程序性能的影響。

·???????????????????? 使用 Prepared Execution 來執行參數化 SQL 語句。有關更多信息，請參見 Prepared Execution (ODBC)。

·???????????????????? 始終處理完所有結果。

不要設計或使用在未取消查詢時就停止處理結果行的應用程序。否則通常會導致阻塞和降低性能。有關更多信息，請參見了解和避免阻塞。

·???????????????????? 確保將應用程序設計為可避免死鎖。有關更多信息，請參見將死鎖減至最少。

·???????????????????? 確保已設置所有能夠優化分布式查詢性能的適當選項。有關更多信息，請參見優化分布式查詢。

在應用系統的設計中，要著重考慮以下幾點：

　　1．合理使用索引

索引是數據庫中重要的數據結構，它的根本目的就是為了提高查詢效率。現在大多數的數據庫產品都采用IBM最先提出的ISAM索引結構。索引的使用要恰到好處，其使用原則如下：

●在經常進行連接，但是沒有指定為外鍵的列上建立索引，而不經常連接的字段則由優化器自動生成索引。

●在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引。

●在條件表達式中經常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。

●如果待排序的列有多個，可以在這些列上建立復合索引（compound index）。

●使用系統工具。如Informix數據庫有一個tbcheck工具，可以在可疑的索引上進行檢查。在一些數據庫服務器上，索引可能失效或者因為頻繁操作而使得讀取效率降低，如果一個使用索引的查詢不明不白地慢下來，可以試著用tbcheck工具檢查索引的完整性，必要時進行修復。另外，當數據庫表更新大量數據后，刪除并重建索引可以提高查詢速度。

2．避免或簡化排序

應當簡化或避免對大型表進行重復的排序。當能夠利用索引自動以適當的次序產生輸出時，優化器就避免了排序的步驟。以下是一些影響因素：

●索引中不包括一個或幾個待排序的列；

●group by或order by子句中列的次序與索引的次序不一樣；

●排序的列來自不同的表。

為了避免不必要的排序，就要正確地增建索引，合理地合并數據庫表（盡管有時可能影響表的規范化，但相對于效率的提高是值得的）。如果排序不可避免，那么應當試圖簡化它，如縮小排序的列的范圍等。

3．消除對大型表行數據的順序存取

在嵌套查詢中，對表的順序存取對查詢效率可能產生致命的影響。比如采用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那么這個查詢就要查詢10億行數據。避免這種情況的主要方法就是對連接的列進行索引。例如，兩個表：學生表（學號、姓名、年齡……）和選課表（學號、課程號、成績）。如果兩個表要做連接，就要在“學號”這個連接字段上建立索引。

還可以使用并集來避免順序存取。盡管在所有的檢查列上都有索引，但某些形式的where子句強迫優化器使用順序存取。下面的查詢將強迫對orders表執行順序操作：

SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008

雖然在customer_num和order_num上建有索引，但是在上面的語句中優化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句：

SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001

UNION

SELECT ＊ FROM orders WHERE order_num=1008

這樣就能利用索引路徑處理查詢。

4．避免相關子查詢

一個列的標簽同時在主查詢和where子句中的查詢中出現，那么很可能當主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。

5．避免困難的正規表達式

MATCHES和LIKE關鍵字支持通配符匹配，技術上叫正規表達式。但這種匹配特別耗費時間。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”

即使在zipcode字段上建立了索引，在這種情況下也還是采用順序掃描的方式。如果把語句改為SELECT ＊ FROM customer WHERE zipcode >“98000”，在執行查詢時就會利用索引來查詢，顯然會大大提高速度。

另外，還要避免非開始的子串。例如語句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“80”，在where子句中采用了非開始子串，因而這個語句也不會使用索引。

6．使用臨時表加速查詢

把表的一個子集進行排序并創建臨時表，有時能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優化器的工作。例如：

SELECT cust.name，rcvbles.balance，……other columns

FROM cust，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id

AND rcvblls.balance>0

AND cust.postcode>“98000”

ORDER BY cust.name

如果這個查詢要被執行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時文件中，并按客戶的名字進行排序：

SELECT cust.name，rcvbles.balance，……other columns

FROM cust，rcvbles

WHERE cust.customer_id = rcvlbes.customer_id

優化實用工具和工具性能

可在生產數據庫上執行以獲得最佳性能收益的三個操作包括：

·???????????????????? 備份和還原操作。

·???????????????????? 將數據大容量復制到表中。

·???????????????????? 執行數據庫控制臺命令 (DBCC) 操作。

一般情況下，不需要優化這些操作。然而，在性能很關鍵的情形中，可采用一些技巧優化性能。

　Microsoft SQL Server數據庫內核用1個基于費用的查詢優化器自動優化向SQL提交的數據查詢操作。數據操作查詢是指支持SQL關鍵字WHERE或HAVING的查詢，如SELECT、DELETE和UPDATE。基于費用的查詢優化器根據統計信息產生子句的費用估算。

　　了解優化器數據處理過程的簡單方法是檢測SHOWPLAN命令的輸出結果。如果用基于字符的工具(例如isql)，可以通過鍵入SHOW SHOWPLAN ON來得到SHOWPLAN命令的輸出。如果使用圖形化查詢，比如SQL Enterprise Manager中的查詢工具或isql/w，可以設定配置選項來提供這一信息。

　SQL Server的優化通過3個階段完成:查詢分析、索引選擇、合并選擇:

1.查詢分析

　　在查詢分析階段，SQL Server優化器查看每一個由正規查詢樹代表的子句，并判斷它是否能被優化。SQL Server一般會盡量優化那些限制掃描的子句。例如，搜索和/或合并子句。但是不是所有合法的SQL語法都可以分成可優化的子句，如含有SQL不等關系符“<>”的子句。因為“<>”是1個排斥性的操作符，而不是1個包括性的操作符，所在掃描整個表之前無法確定子句的選擇范圍會有多大。當1個關系型查詢中含有不可優化的子句時，執行計劃用表掃描來訪問查詢的這個部分，對于查詢樹中可優化的SQL Server子句，則由優化器執行索引選擇。

2.索引選擇

　　對于每個可優化的子句，優化器都查看數據庫系統表，以確定是否有相關的索引能用于訪問數據。只有當索引中的列的1個前綴與查詢子句中的列完全匹配時，這個索引才被認為是有用的。因為索引是根據列的順序構造的，所以要求匹配是精確的匹配。對于分簇索引，原來的數據也是根據索引列順序排序的。想用索引的次要列訪問數據，就像想在電話本中查找所有姓為某個姓氏的條目一樣，排序基本上沒有什么用，因為你還是得查看每一行以確定它是否符合條件。如果1個子句有可用的索引，那么優化器就會為它確定選擇性。

　　所以在設計過程中，要根據查詢設計準則仔細檢查所有的查詢，以查詢的優化特點為基礎設計索引。

　　(1)比較窄的索引具有比較高的效率。對于比較窄的索引來說，每頁上能存放較多的索引行，而且索引的級別也較少。所以，緩存中能放置更多的索引頁，這樣也減少了I/O操作。

　　(2)SQL Server優化器能分析大量的索引和合并可能性。所以與較少的寬索引相比，較多的窄索引能向優化器提供更多的選擇。但是不要保留不必要的索引，因為它們將增加存儲和維護的開支。對于復合索引、組合索引或多列索引，SQL Se

優化服務器性能

Microsoft? SQL Server? 2000 自動調整很多服務器配置選項，因此系統管理員只需做很少的調整（如果有）。這些配置選項可以由系統管理員修改，但一般建議保留為默認值，以使 SQL Server 能根據運行時的情況自動對自身進行調整。

不過，如果需要，可以配置下列組件以優化服務器性能：

·???????????????????? SQL Server 內存

·???????????????????? I/O 子系統

·???????????????????? Microsoft Windows NT? 選項

MSSQL是怎樣使用內存的:

　　最大的開銷一般是用于數據緩存，如果內存足夠，它會把用過的數據和覺得你會用到的數據統統扔到內存中，直到內存不足的時候，才把命中率低的數據給清掉。所以一般我們在看statistics io的時候，看到的physics read都是0。

　　其次就是查詢的開銷，一般地說，hash join是會帶來比較大的內存開銷的，而merge join和nested loop的開銷比較小，還有排序和中間表、游標也是會有比較大的開銷的。

　　所以用于關聯和排序的列上一般需要有索引。

　　再其次就是對執行計劃、系統數據的存儲，這些都是比較小的。

　　我們先來看數據緩存對性能的影響，如果系統中沒有其它應用程序來爭奪內存，數據緩

存一般是越多越好，甚至有些時候我們會強行把一些數據pin在高速緩存中。但是如果有其?

τ貿絳潁淙輝諦枰氖焙騇SSQL會釋放內存，但是線程切換、IO等待這些工作也是需要

時間的，所以就會造成性能的降低。這樣我們就必須設置MSSQL的最大內存使用。可以在SQL

Server

屬性（內存選項卡）中找到配置最大使用內存的地方，或者也可以使用sp_configure來完成

。如果沒有其它應用程序，那么就不要限制MSSQL對內存的使用。

　　然后來看查詢的開銷，這個開銷顯然是越低越好，因為我們不能從中得到好處，相反，

使用了越多的內存多半意味著查詢速度的降低。所以我們一般要避免中間表和游標的使用，

在經常作關聯和排序的列上建立索引。 不更改代碼的情況下如何優化數據庫系統

這個問題很多DBA可能都碰到過吧：比如剛接手一個舊有系統，原來的廠商不允許對代碼修?

模蛘呤竅低秤τ帽冉瞎丶２輝市磣饜薷模蛘呤竊創氤鲇諫桃的康模辛艘歡ǔ潭

鵲募用埽褂械氖焙蚩贍蓯切姓蛩?-

領導為了避免責任，不允許你這樣做，但這個時候，系統的性能上的問題還比較嚴重，還有

其他辦法怎么對系統進行優化么? 在這里我嘗試總結一下可能有的途徑。

針對特定的SQL進行"外科手術" (Metalink 122812.1)，改進執行計劃 ·

更新統計信息 (調整采樣率/柱狀圖統計) ·???????????????????????????????? 調整索引

(添加或調整合適的索引，刪除不必要的索引) ·

創建物化試圖(用空間開銷來換取時間收益) 優化OS和數據庫以外的其他東西

首先優化操作系統-比如核心參數的合理調整，操作系統資源的合理分配;

磁盤IO的調整,這是很重要的一部分，因為磁盤IO速度很容易造成系統瓶頸;網絡資源的優化

-TCP/IP的參數調整; 調整Oracle初始化參數 優化器模式的設定,db_cache 參數等設定,sga

大小等參數設定，都對數據庫性能有著重要的影響。 合理的系統資源調度

在一些批處理操作為主的系統中，系統資源的調度是比較重要的，調度不合理，很容易造成

資源爭用。有的系統可能在系統創建之初調度是比較合理的，經過一段時間運行之后，可能

因為數據量的變化，SQL語句的執行計劃變化等會造成操作時間上的重疊，這肯定會給系統?

囪沽ι系奈侍狻?調整數據庫對象 ·???????????????????????????????? 調整pctfree

,freelist ，存儲參數 ·

調整表空間文件和數據庫對象（表、索引）的磁盤分布。 ·

cache 一些常用的數據庫對象。 系統Bug問題帶來的影響/升級改進性能

Oracle軟件Bug多多，系統運行初期有的Bug帶來的危害還不夠明顯，隨著時間的推移，個別

的Bug會給系統性能造成問題。這個時候對系統的Bug

修復已經對數據庫系統進行升級就是必要的。通過升級，修正Oracle軟件缺陷，同時在升級

后也可能會增強數據庫引擎的效率。當然，也要注意升級可能帶來的不良的影響。 ·

???????????????????????? 操作系統相關優化 1.

操作系統性能的好壞直接影響數據庫的使用性能，如果操作系統存在問題，如CPU過載、過?

饒詿娼換弧⒋排蘄/O瓶頸等，在這種情況下，單純進行數據庫內部性能調整是不會改善系統

性能的。我們可以通過Windows NT的系統監視器(System

Monitor)來監控各種設備，發現性能瓶頸。　　CPU

一種常見的性能問題就是缺乏處理能力。系統的處理能力是由系統的CPU數量、類型和速度?

齠ǖ摹Ｈ綣低趁揮兇愎壞腃PU處理能力，它就不能足夠快地處理事務以滿足需要。我們可

以使用System

Monitor確定CPU的使用率，如果以75%或更高的速率長時間運行，就可能碰到了CPU瓶頸問題

，這時應該升級CPU。但是升級前必須監視系統的其他特性，如果是因為SQL語句效率非常低

，優化語句就有助于解決較低的CPU利用率。而當確定需要更強的處理能力，可以添加CPU或

者用更快的CPU 替換。　　內存 SQL Server可使用的內存量是SQL

Server性能最關鍵因素之一。而內存同I/O子系統的關系也是一個非常重要的因素。例如，?

贗/O操作頻繁的系統中，SQL

Server用來緩存數據的可用內存越多，必須執行的物理I/O也就越少。這是因為數據將從數?

莼捍嬤卸寥《皇譴喲排潭寥　Ｍ詿媼康牟蛔慊嵋鵜饗緣拇排潭列雌烤保蛭低

郴捍婺芰Σ蛔慊嵋鷥嗟奈錮澩排蘄/O。　　可以利用System Monitor檢查SQL

Server的Buffer Cache Hit

Ratio計數器，如果命中率經常低于90%，就應該添加更多的內存。　　I/O子系統由I/O子系

統發生的瓶頸問題是數據庫系統可能遇到的最常見的同硬件有關的問題。配置很差的I/O子?

低騁鸚閱芪侍獾難現爻潭冉齟斡詒嘈春懿畹腟QL語句。I/O子系統問題是這樣產生的，一?

齟排糖髂芄恢蔥械腎/O操作是有限的，一般一個普通的磁盤驅動器每秒只能處理85次I/

O操作，如果磁盤驅動器超載，到這些磁盤驅動器的I/O操作就要排隊，SQL的I/O延遲將很長

。這可能會使鎖持續的時間更長，或者使線程在等待資源的過程中保持空閑狀態，其結果就

是整個系統的性能受到影響。

解決I/O子系統有關的問題也許是最容易的，多數情況下，增加磁盤驅動器就可以解決這個?

閱芪侍狻！?

　當然，影響性能的因素很多，而應用又各不相同，找出一個通用的優化方案是很困難的，

只能是在系統開發和維護的過程中針對運行的具體情況，不斷加以調整。 2　與SQL

Server相關的硬件系統 　　與SQL

Server有關的硬件設計包括系統處理器、內存、磁盤子系統和網絡，這4個部分基本上構成?

擻布教ǎ琖indows NT和SQL Server運行于其上。 2.1　系統處理器(CPU)

　　根據自己的具體需要確定CPU結構的過程就是估計在硬件平臺上占用CPU的工作量的過程

。從以往的經驗看，CPU配置最少應是1個80586/100處理器。如果只有2～3個用戶，這就足?

渙耍綣蛩闃С指嗟撓沒Ш凸丶τ茫萍霾捎肞entium Pro或PⅡ級CPU。

2.2　內存(RAM) 　　為SQL

Server方案確定合適的內存設置對于實現良好的性能是至關重要的。SQL

Server用內存做過程緩存、數據和索引項緩存、靜態服務器開支和設置開支。SQL

Server最多能利用2GB虛擬內存，這也是最大的設置值。還有一點必須考慮的是Windows

NT和它的所有相關的服務也要占用內存。 　　Windows

NT為每個WIN32應用程序提供了4GB的虛擬地址空間。這個虛擬地址空間由Windows

NT虛擬內存管理器(VMM)映射到物理內存上，在某些硬件平臺上可以達到4GB。SQL

Server應用程序只知道虛擬地址，所以不能直接訪問物理內存，這個訪問是由VMM控制的。W

indows NT允許產生超出可用的物理內存的虛擬地址空間，這樣當給SQL

Server分配的虛擬內存多于可用的物理內存時，會降低SQL Server的性能。

　　這些地址空間是專門為SQL

Server系統設置的，所以如果在同一硬件平臺上還有其它軟件(如文件和打印共享，應用程?

蚍竦?在運行，那么應該考慮到它們也占用一部分內存。一般來說硬件平臺至少要配置32

MB的內存，其中，Windows

NT至少要占用16MB。1個簡單的法則是，給每一個并發的用戶增加100KB的內存。例如，如果

有100個并發的用戶，則至少需要32MB+100用戶*100KB=42MB內存，實際的使用數量還需要根

據運行的實際情況調整。可以說，提高內存是提高系統性能的最經濟的途徑。

　2.3　磁盤子系統 　　設計1個好的磁盤I/O系統是實現良好的SQL

Server方案的一個很重要的方面。這里討論的磁盤子系統至少有1個磁盤控制設備和1個或多

個硬盤單元，還有對磁盤設置和文件系統的考慮。智能型SCSI-

2磁盤控制器或磁盤組控制器是不錯的選擇，其特點如下:

　　(1)控制器高速緩存。　　(2)總線主板上有處理器，可以減少對系統CPU的中斷。　　(

3)異步讀寫支持。　　(4)32位RAID支持。　　(5)快速SCSI—2驅動。　　(6)超前讀高速緩

存(至少1個磁道)。 3　檢索策略

　　在精心選擇了硬件平臺，又實現了1個良好的數據庫方案，并且具備了用戶需求和應用?

矯嫻鬧逗螅衷謨Ω蒙杓撇檠退饕恕Ｓ?個方面對于在SQL

Server上取得良好的查詢和索引性能是十分重要的，第1是根據SQL

Server優化器方面的知識生成查詢和索引;第2是利用SQL

Server的性能特點，加強數據訪問操作。

轉載于:https://www.cnblogs.com/Binhua-Liu/p/5326540.html

總結

以上是生活随笔為你收集整理的转载：SqlServer数据库性能优化详解的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。