資料庫性能優化詳解 性能調節的目的是通過将網絡流通、磁盤 I/O 和 CPU 時間減到最小，使每個查詢的響應時間最短并最大限度地提高整個資料庫伺服器的吞吐量。為達到此目的，需要了解應用程式的需求和資料的邏輯和實體結構，并在互相沖突的資料庫使用之間（如聯機事務處理 (OLTP) 與決策支援）權衡。 對性能問題的考慮應貫穿于開發階段的全過程，不應隻在最後實作系統時才考慮性能問題。許多使性能得到顯著提高的性能事宜可通過開始時仔細設計得以實作。為最有效地優化 Microsoft® SQL Server™ 2000 的性能，必須在極為多樣化的情形中識别出會使性能提升最多的區域，并對這些區域集中分析。 雖然其它系統級性能問題（如記憶體、硬體等）也是研究對象，但經驗表明從這些方面獲得的性能收益通常會增長。通常情況下， SQL Server 自動管理可用的硬體資源，進而減少對大量的系統級手動調節任務的需求（以及從中所得的收益）。 目錄： 設計聯合資料庫伺服器：描述如何通過将處理負荷分攤在多個伺服器間而達到高性能級别（如大型 Web 站點等所需的性能級别）。 1 資料庫設計：描述資料庫設計如何成為提高總體性能的最有效途徑。資料庫設計包括邏輯資料庫架構（如表和限制）和實體特性（如磁盤系統、對象位置和索引）。...2 查詢優化：描述正确設計的查詢（用于應用程式）如何顯著提高性能。...3 應用程式設計：描述正确設計的使用者應用程式如何顯著提高性能。應用程式設計包括事務邊界、鎖定和批處理的使用。 4 優化實用工具和工具性能：述及 Microsoft SQL Server 2000 提供的實用工具和工具的一些可用選項，描述這些選項如何突出說明提高這些工具的性能的方法，以及同時運作這些工具和應用程式的效果。...5 優化伺服器性能：描述如何更改作業系統（Microsoft Windows NT®、Microsoft Windows® 95、Microsoft Windows 98 或 Microsoft Windows 2000）和 SQL Server 的設定以提高總體性能。...6 作業系統相關優化:描述作業系統和資料庫之間可改善的方面…………………………………………………7 設計聯合資料庫伺服器 為達到大型 Web 站點所需的高性能級别，多層系統一般在多個伺服器之間平衡每一層的處理負荷。 Microsoft® SQL Server™ 2000 通過對 SQL Server 資料進行水準分區，在一組伺服器之間分攤資料庫處理負荷。這些伺服器互相獨立，但也可以互相協作以處理來自應用程式的資料庫請求；這樣的一組協作伺服器稱為聯合體。 隻有當應用程式将每個 SQL 語句發送到擁有該語句所需的大部分資料的成員伺服器時，聯合資料庫層才可以達到非常高的性能級别。這稱為使用語句所需的資料配置 SQL 語句。使用所需的資料配置 SQL 語句不是聯合伺服器所獨有的要求；在群集系統中同樣有此要求。 雖然伺服器聯合體與單個資料庫伺服器呈現給應用程式的圖像相同，但在實作資料庫服務層的方式上存在内部差異。
| 單個伺服器層 | 聯合伺服器層 |
| 生産伺服器上有一個 SQL Server 執行個體。 | 每個成員伺服器上都有一個 SQL Server 執行個體。 |
| 生産資料存儲在一個資料庫中。 | 每個成員伺服器都有一個成員資料庫。資料分布在成員資料庫之間。 |
| 一般每個表都是單個實體。 | 原始資料庫中的表被水準分區為成員表。一個成員資料庫有一個成員表，而且使用分布式分區視圖使每個成員伺服器上看起來似乎都有原始表的完整複本。 |
| 與單個伺服器的所有連接配接和所有 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 。如果還是沒有采用請用 提示強制指定 ....
上面的是單純從索引方面考慮。如果還是不能提高速度，考慮建立實體化視圖（物化視圖）。可以隻将 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 @[email protected]+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 @[email protected]+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 @[email protected]+1
end
set @[email protected]+1
end
set @[email protected]+1
end
set @[email protected]+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 @[email protected]+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 @[email protected][email protected]
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<[email protected] 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 ('[email protected]+') T WHERE T.'[email protected]+'NOT IN
(SELECT TOP '+CAST((@RecsPerPage*(@Page-1)) AS VARCHAR(20))+' '[email protected]+' FROM ('[email protected]+') T9 ORDER BY '[email protected]+') ORDER BY '[email protected]
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 "[email protected]
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) +" "[email protected]+ " from [" + @tblName + "] where " + @strWhere + " " + @strOrder
else
set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ " from ["+ @tblName + "] "+ @strOrder
-- 如果是第一頁就執行以上代碼，這樣會加快執行速度
end
else
begin
-- 以下代碼賦予了 @strSQL 以真正執行的 SQL 代碼
set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ " 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) +" "[email protected]+ " 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 秒；而用其他存儲過程，在實踐中就會導緻逾時，是以這個存儲過程非常适用于大容量資料庫的查詢。
筆者希望能夠通過對以上存儲過程的解析，能給大家帶來一定的啟示，并給工作帶來一定的效率提升，同時希望同行提出更優秀的實時資料分頁算法。
四、聚集索引的重要性和如何選擇聚集索引
在上一節的标題中，筆者寫的是：實作小資料量和海量數
![Oracle資料庫性能優化之優化器元件[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)