談資料庫的性能優化

這篇文章是我花了很多時間寫出來的，曾經發表在javaeye論壇上，今天居然不見了，幸好網上有人轉載這篇文章，沒辦法，隻好再一份在部落格裡。這個是我以前寫給我部門的一個技術心得，鄙人才疏學淺，知道javaeye高人很多，如果我寫的不對的地方，歡迎指教。 

我靠這麼多關鍵字過濾啊，“fapiao”也成了關鍵字

1:前言

      資料庫優化是一個很廣的範圍,涉及到的東西比較多,并且每個特定的資料庫,其具體的優化過程也是不一樣的.因為優化的很大一部分最終都要跟具體的資料庫系統細節打交道,在此不可能針對所有的資料庫都一一詳細闡述,如果那樣,恐怕寫幾本書都寫不完.隻能針對一些比較通用的,經常用到的的東西進行一個讨論,一般情況下,資料庫的優化指的就是查詢性能的優化(雖然嚴格上來說不應該是這樣的),讓資料庫對查詢的響應盡可能的快.僅對資料庫系統本身而言,影響到查詢性能的因素從理論上來講，包括資料庫參數設定(其實就是通過參數控制資料庫系統的記憶體,i/o,緩存，備份等一些管理性的東西),索引,分區,sql語句.資料庫參數設定本身是一個很複雜的東西，分區則主要是針對大資料量的情況下，它分散了資料檔案的分布，減少磁盤競争，使效率得到提升。

      每種資料庫或多或少都有一些自己特定的索引,如oracle除了正常索引之外還有反向索引,位圖索引,函數索引,應用程式域索引等等,能夠讓使用者對資料的邏輯組織有着更為精确的控制,而sqlserver沒有這麼多的索引,大體來說,sqlserver的索引分為兩種:聚集索引和非聚集索引.在分區方面,oracle和sqlserver比較相似,不過sqlserver的分區更為繁瑣一些,但随着sqlserver的版本越來越高,其分區操作也趨向于簡潔.sql語句優化則基本上比較獨立，目前的一些資料庫系統處理sql的機制都比較類似，因為sql本身就是一個标準。這三種将會在下面作一個詳細的讨論.本讨論建立在sqlserver上,因為目前部門的很多系統的資料庫用到的是sqlserver,雖然oracle會給與我們更多的可探讨的範圍. 

2:測試資料庫的建立 

      因為要讨論索引,分區,sql等,是以有必要建立一個資料庫,不然隻是泛泛而談,我在sqlserver2000上建立了一個名為ipanel的資料庫,該資料庫隻有一張表,名為person,person的定義如下:

CREATE TABLE [dbo].[person] ( 

[id] [bigint] NOT NULL , --記錄的id 

[name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,--姓名 

[age] [int] NULL ,--年齡 

[addr] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--位址 

[sex] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,--性别 

[dept] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--部門 

[pos] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--郵編 

[tel] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,--電話 

[fax] [char] (15) COLLATE Chinese_PRC_CI_AS NULL ,--傳真 

[emdate] [datetime] NULL --入職日期 

) ON [PRIMARY]

      ON[PRIMARY]表示該表建在系統的預設檔案組上，在sqlserver裡，檔案組的概念就相當于oracle的表空間，是一種邏輯概念,它包含了資料檔案，所謂資料檔案，當然就是存儲資料的檔案.預設情況下，sqlserver會在預設的路徑建立檔案組和初始的資料檔案，如果使用者在建立資料庫或表的時候沒有指定檔案組，則用預設的。資料檔案,日志檔案,參數檔案是所有資料庫系統最主要的檔案,oracle還有控制檔案,在很多的專業書籍裡面,從資料庫系統的實體結構上來講,資料庫就是指的靜态的資料檔案,資料庫系統或者資料庫執行個體指的是一組程序,如日志程序,資料緩沖程序，網絡監聽程序等,這些程序作用在各種檔案上面。不說了,扯遠了.建了一個資料插入的存儲過程: 

CREATE PROCEDURE initPerson @start int, @end int , --起始條數，結束條數 

@name varchar(10),@age int, --姓名，年齡 

@addr varchar(10),@sex char(2), --位址，性别 

@dept varchar(20),@emdate varchar(10 --部門，入職日期 

AS 

declare @id int 

set @id=@start 

while @id<=@end 

begin 

insert into person values(@id,@name,@age,@addr,@sex,@dept , 

'438200','82734664','82734665',@emdate) 

set @id=@id+1 

end 

GO 

以下插入記錄 

exec initPerson 1,100000, ‘王**’,24,’深圳’,’男’,’應用開發部’,’2007-06-04’ 

插入10萬條名叫王**的記錄，因為在目前的例子中，姓名不重要，是以相同的姓名不礙事。如下依次執行 

exec initPerson 100001,200000, ‘韓**’,25,’深圳’,’男’,’應用工程部’,’2007-06-05’ 

exec initPerson 200001,300000, ‘徐*’,26,’ 深圳’,’男’,’系統終端部’,’2007-06-06’ 

exec initPerson 300001,500000, ‘程*’,23’, 深圳’,’男’,’研發中心’,’2007-06-07’ 

exec initPerson 500001,750000, ‘卓*’,22,’ 深圳’,’男’,’行政部’,’2007-06-08’ 

exec initPerson 750001,1000000, ‘流*’,20,’ 深圳’,’男’,’業務合作部’,’2007-06-09’ 

接着依次插入類似的記錄,我就不一一列舉了. 

執行完畢，person表便有了200萬條記錄。為什麼我不用更多的資料呢,因為我要頻繁的改變資料庫的設定,如果資料非常多,那當我改變資料庫設定時候,會耗費很長的時間,比如索引更新維護等,不太友善.值得一提的是，如果沒有指定聚集索引，那麼sqlserver預設在主鍵上建立聚集索引，在目前情況下，系統在id列上建立了聚集索引。 

資料庫建立完畢,下面将會對索引,分區,sql做比較詳細的讨論 

3:索引 

      索引是各種關系資料庫系統最常見的一種邏輯單元,是關系資料庫系統舉足輕重的重要組成部分,對于提高檢索資料速度有着至關重要的作用,索引的原理是根據索引值得到行指針,然後快速定位到資料庫記錄..

3.1:常見索引介紹

1: B*樹索引 

      這是最常見的索引,幾乎所有的關系型資料庫系統都支援B*樹結構的索引,也是被最多使用的,其樹結構與二叉樹比較類似,根據行id快速定位到行.大部分資料庫預設建立的索引就是這種索引.B*樹索引在檢索高基數資料列(高基數列是指該列有很多不同的值,該列所有不同值的個數之和與該列所有值的個數之和的比成為列基數)時提供了比較好的性能,B*樹索引是基于二叉樹的,由分支塊和葉塊組成.在樹結構中,位于最底層的快成為葉塊,包含每個被索引列的值和行所對應的rowid.在葉節點的上面是分支塊,用來導航結構,包含了索引列(關鍵字)範圍和另一索引快的位址,如圖所示: （圖檔插入做的不夠好，插圖進來我覺得很好麻煩）

假設要查找索引中值為80的行，從索引樹的最上層入口開始，首先定位到大于等于50，然後往左找，找到第二個分支塊，定位到75―100，然後定位到葉塊，定位到葉塊，找到80所對應的rowid，然後根據rowid到資料塊讀取對應的資料。如果查詢條件是範圍選擇的，比如colume>20 and colume<80,那麼會先定位到20的塊，然後再橫向查找到80的塊為止，不是每次都從入口進去重新定位的。 

要說明的是,這種索引是用得最多的,基本上所有的資料庫系統都支援這種索引,它是索引裡最主要最普遍的,它之是以稱為B*樹索引,更多是因為它的存儲結構有着普遍的意義,很多索引都基于這種結構,當然sqlserver裡沒有名為B*樹的索引,但是不妨礙我們以對B*樹索引的認識去了解sqlserver的索引,不是嗎?這是我為什麼把它放在最前面的原因.

2:聚集索引 

      沒錯,這是sqlserver裡很重要的一個索引.也叫群集索引。 聚集索引是相對于正常索引而言的,oracle也有類似的索引,不過叫聚簇索引,注意,雖然聚簇和聚集僅有一字之差,但是oracle的聚簇索引和sqlserver的聚集索引還是有很多的不同的,oracle的聚簇索引可以針對多表,根據多個表相同列的不同值,将相關資料聚集在周圍.sqlserver聚集索引也有類似的意思,但是隻能針對單表.在oracle裡,聚簇”是oralce内部的一個對象,就像基本表,視圖,觸發器這些概念一樣. 聚簇索引就是對聚簇進行的索引,由于比較複雜,在此不詳細讨論,但在sqlserver裡,聚集索引直接作用在表上,是以不可以将二者混淆.反正不能等同來看就是了.

      舉個例子說明來說明sqlserver的聚集索引:我們的漢語字典的正文本身就是一個聚集索引。比如，我們要查“安”字，就會很自然地翻開字典的前幾頁，因為“安”的拼音是“an”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結尾的，那麼“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個字，那麼就說明您的字典中沒有這個字；同樣的，如果查“張”字，那您也會将您的字典翻到最後部分，因為“張”的拼音是“zhang”。也就是說，字典的正文部分本身就是一個目錄，您不需要再去查其他目錄來找到您需要找的内容。 我們把這種正文内容本身就是一種按照一定規則排列的目錄稱為“聚集索引”。 聚集索引都是排好序的.

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

      總而言之, 聚集索引就是使與被索引的值相關的行資料塊集中在一起,不是實體上的散列分布.這樣,首先縮小了掃描範圍,而且定位資料的時間短,可以想象一下查字典的時候,根據拼音查找漢字,找以”a”發音開頭的字,你隻會在a字母裡面找,如果a字母找完了,那麼不管有沒有這個字,查找過程也就結束了。 

非聚集索引也是B*樹結構，隻不過每個索引值對應的不是行id，而是資料行本身，聚集索引會對表排序，就像字典一樣，它按照英文字母的順序排序的，是以在基于某個範圍搜尋的時候，它的查詢效率是很高的，但同時我們也可以看到，它占據了更多的空間，在插入更新的時候，它會花多一點的時間維持自己的索引順序。每個表隻能有一個聚集索引，這是當然的，因為每個表肯定隻可能有一個全表排序的規則。

3:非聚集索引 

      非聚集索引是一種典型的B*樹索引,每個葉塊隻包含兩種資料,一種是索引項,一種是該索引項所在行的行指針,當查詢的資料比對該索引項資料的時候,将會取出對應的行指針,取得該行的資料.如果要根據鍵值從大型 SQL Server 表提取具有良好選擇性的少數幾行，非聚集索引最有用。B*樹的底部或葉級包含組成該索引的列中的所有資料。當用非聚集索引檢索表中與鍵值比對的資訊時，将搜尋整個索引 B 樹，直到在索引葉級找到一個與鍵值比對的值。 

在非聚集索引中，葉級節點僅包含參與索引的資料以及快速找到相關資料頁上其它行資料的指針。最糟糕的情況是，從非聚集索引中獲得的每一行都要求一個額外的不連續磁盤 I/O 才能檢索行資料。最好的情況是，所需要的行有許多都位于相同的資料頁，是以在提取每個資料頁時可檢索多行。如果是聚集索引，索引的葉級節點是表的實際資料行。是以，檢索表資料時不需要指針跳動。基于聚集索引的範圍掃描執行情況很好，因為聚集索引的葉級（即表的所有行）在實體上按照組成聚集索引的列順序排列在磁盤上.

4:覆寫索引 

      覆寫索引是非聚集索引的一個特例。覆寫索引的定義是在選擇條件和 WHERE 謂詞上均滿足 SQL 查詢的所有列的基礎上建立的非聚集索引。覆寫索引可以節省大量的 I/O，是以可極大地改善查詢的性能。但是有必要在建立索引（以及與它相關的 B 樹索引結構維護）所需要的代價和覆寫索引所帶來的 I/O 性能增益之間進行權衡。如果覆寫索引對于 SQL Server 上經常運作的查詢或查詢組極其有利，那麼建立覆寫索引是值得的。 

覆寫索引的示例

Select col1,col3 from table1 where col2 = 'value'. 

Create index indexname1 on table1(col2,col1,col3). 

本例中建立出來的索引“indexname1”是一個覆寫索引，因為它包括 SELECT 語句和 WHERE 謂詞中的所有列。即在執行此查詢期間，SQL Server 不需要通路與 table1 相關的資料頁。SQL Server 使用索引 indexname1 可以獲得滿足查詢所需要的全部資訊。在 SQL Server 已周遊與 indexname1 相關的 B 樹，并找到 col2 等于“value”的索引關鍵字範圍，SQL Server 就知道它可以從覆寫索引的葉級（底層）提取所有需要的資料 (col1,col2,col3)。這從兩個方面改進了 I/O 性能：

      SQL Server 僅從索引頁而不是資料頁擷取所有需要的資料，是以資料的壓縮率更高，使 SQL Server 可以節省磁盤 I/O 操作。 

覆寫索引按照 col2 将所有需要的資料以實體方式組織在磁盤上。使硬碟得以連續傳回與 where 謂詞 (col2 = "value") 相關的所有索引行。進而為我們提供了更好的 I/O 性能。 總而言之，如果覆寫索引中的所有列的位元組數比該表中單行的位元組數少，并且可以肯定将反複執行使用此覆寫索引的查詢，那麼使用覆寫索引是有意義的。 

5:位圖索引 

      這個不是sqlserver的索引，它是oracle的，是以請不要混淆。之是以提出來，是因為它不是B*樹結構的索引。位圖索引相對于B*tree索引來說,它的存儲結構是不一樣的,通常在B*tree索引中,在索引條目和行之間有一對一的關系.對于位圖索引,一個索引條目使用一個位圖同時指向許多行.這對于基本上隻讀的低基數(資料隻有很少的幾個截然不同的值)資料是合适的.比如說,一個person表,有個性别字段sex,Y代表男,N代表女,對于有幾百萬行資料的表來說, 位圖索引是一個非常好的選擇。它可以迅速的掃描出來，而不用象對B*樹索引那樣的查找。

3.2 有效的利用索引

      索引在資料庫的查詢優化中起着至關重要的作用，一個資料庫索引的好與壞，其查詢性能相差很多倍，下面将談一下各種索引的使用場合和一些觀點。如何選擇索引可顯著影響所産生的磁盤 I/O，并因而影響查詢性能。在非聚集索引中，選擇性很重要，因為如果在隻有少量唯一值的大型表上建立非聚集索引，使用非聚集索引将不會節省資料檢索中的 I/O。因為B*樹結構的索引都注重一種比較性，這樣它可以快速的确定範圍，定位位置,例如，person表的性别字段，非男即女，不具有可比性，如果以它為非聚集索引，查詢的時候也隻能一個個葉節點去比較。在這種情況下産生的 I/O 可能比對表進行連續掃描所産生的 I/O 多得多。比較适合非聚集索引的有票據編号、唯一的客戶編号、社會安全号碼和電話号碼，簡單來說，就是基于某種可比較的，有規律的資料。 

建立聚集索引之前，應先了解資料是如何被通路的。

考慮對具有以下特點的查詢使用聚集索引：

使用運算符（如 BETWEEN、>、>=、< 和 <=）傳回一系列值。 使用聚集索引找到包含第一個值的行後，便可以確定包含後續索引值的行實體相鄰。例如，如果某個查詢在一系列銷售訂單号間檢索記錄，銷售單号列的聚集索引可   快速定位包含起始銷售訂單号的行，然後檢索表中所有連續的行，直到檢索到最後的銷售訂單号。 

傳回大型結果集。 

使用 JOIN 子句；一般情況下，使用該子句的是外鍵列。 

使用 ORDER BY 或 GROUP BY 子句。 

在 ORDER BY 或 GROUP BY 子句中指定的列的索引，可以使資料庫引擎 不必對資料進行排序，因為這些行已經排序，這樣可以提高資料庫性能 

一般情況下，定義聚集索引鍵時使用的列越少越好。考慮具有下列一個或多個屬性的列： 

 唯一或包含許多不重複的值 

例如，雇員 ID 唯一地辨別雇員。EmployeeID 列的聚集索引或 PRIMARY KEY 限制将改善基于雇員 ID 号搜尋雇員資訊的查詢的性能。另外，可對 LastName、FirstName、MiddleName 列建立聚集索引，因為經常以這種方式分組和查詢雇員記錄，而且這些列的組合還可提供高區分度。

按順序被通路 

例如，id唯一地辨別person表中的記錄，在其中指定順序搜尋的查詢（如 WHERE ID BETWEEN 1000 and 2000）将從id的聚集索引受益。這是因為行将按該鍵列的排序順序存儲。

經常用于對表中檢索到的資料進行排序。 

按該列對表進行聚集（即實體排序）是一個好方法，它可以在每次查詢該列時節省排序操作的成本。 

聚集索引不适用于具有下列屬性的列：

頻繁更改的列 

這将導緻整行移動，因為資料庫引擎 必須按實體順序保留行中的資料值。這一點要特别注意，因為在大容量事務處理系統中資料通常是可變的。

寬鍵 

寬鍵是若幹列或若幹大型列的組合。所有非聚集索引将聚集索引中的鍵值用作查找鍵。為同一表定義的任何非聚集索引都将增大許多，這是因為非聚集索引項包含聚集鍵，同時也包含為此非聚集索引定義的鍵列。

3.3 談索引使用的誤區 

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

1:主鍵就是聚集索引 

      這種想法我認為不是太合理，大多數情況下，主鍵上的聚集索引是對聚集索引的一種浪費。雖然SQL SERVER預設是在主鍵上建立聚集索引的。通常，我們會在每個表中都建立一個ID列，以區分每條資料，并且這個ID列是自動增大的，步長一般為1。此時，如果我們将這個列設為主鍵，SQL SERVER會将此列預設為聚集索引。這樣做有好處，就是可以讓您的資料在資料庫中按照ID進行實體排序，但我認為這樣做意義不大。因為在很多情況下，由于主鍵的唯一性，對id或者主鍵進行範圍掃描 是比較少的。顯而易見，聚集索引的優勢是很明顯的，而每個表中隻能有一個聚集索引的規則，這使得聚集索引變得更加珍貴。 從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據查詢要求，迅速縮小查詢範圍，避免全表掃描。 

在實際應用中，因為ID号是自動生成的，我們并不知道每條記錄的ID号，是以我們很難在實踐中用ID号來進行查詢。這就使讓ID号這個主鍵作為聚集索引成為一種資源浪費。其次，讓每個ID号都不同的字段作為聚集索引也不符合“大數目的不同值情況下不應建立聚合索引”規則；當然，這種情況隻是針對使用者經常修改記錄内容，特别是索引項的時候會負作用，但對于查詢速度并沒有影響。 如果您的聚集索引盲目地建在ID這個主鍵上時，查詢速度不一定會提高的，即使你在其他字段上建立非聚集索引。下面我們就來看一下在200萬條資料量的情況下各種查詢的速度表現： 

（1）全表掃描 

 隻在主鍵上建立聚集索引： 

Select id,name,dept,emdate from person 

用時：20546毫秒（即：21秒） 

 不在主鍵上建立聚集索引，隻建普通索引 

用時：17923毫秒（即：18秒） 

以上查詢執行的實際上索引不會發揮作用，因為提取的是全部資料。聚集索引在這裡會耗費更多的資源，是以會看到，不建立聚集索引比建立聚集索引還要快 

(2):按日期進行過濾(用到索引) 

 在主鍵上建立聚集索引，在emdate上建立非聚集索引： 

select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate()) 

用時：12376毫秒（12秒） 

 在主鍵上建立聚集索引，在emdate上沒有索引： 

用時：21296毫秒（21秒） 

 在主鍵上建立非聚集索引，在emdate上建立非聚集索引： 

用時：11590毫秒（12秒） 

 在主鍵上建立非聚集索引，在emdate上建立聚集索引： 

andemdate<dateadd(day,+3,getdate()) 

用時：5233毫秒（5秒） 

雖然每條語句提取出來的都是30萬條資料，各種情況的差異卻是比較大的，特别是将聚集索引建立在日期列時的差異。事實上，如果您的資料庫真的有幾千萬條記錄的話，差距會更明顯。 

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

      這個想法是錯誤的。事實上，我們可以發現上面的例子中，上面按日期過濾的語句完全相同，且建立索引的字段也相同，但查詢速度卻有着非常大的差别。是以，并非是在任何字段上簡單地建立索引就能提高查詢速度。索引的建立，會帶來更多的系統開銷，因為系統要耗費資源去維護它 ，如果建立了沒有用到的索引，不适當的索引，過多的索引，反而會導緻查詢性能下降。總之索引的建立，要看表的結構，資料的分布，還有你要用到哪些資料，如果把索引建立在你根本不需要的資料列上，是根本不會發揮作用的。 

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

      這個不一定正确。上面已經談到。假設現在查詢要用到使用者名和日期這兩個字段，我們可以把他們合并起來，建立一個複合索引（compound index）。 很多人認為隻要把任何字段加進聚集索引，就能提高查詢速度，也有人感到迷惑：如果把複合的聚集索引字段分開查詢，那麼查詢速度會減慢嗎？帶着這個問題，我們來看一下以下的查詢速度（結果集都是25萬條）：（日期列emdate首先排在複合聚集索引的起始列，使用者名name排在後列） 

 select id,name,dept,emdate from person where emdate>'2007-06-01' 

查詢速度：1664毫秒 

 select id,name,dept,emdate from person 

where emdate>'2007-06-01' and name=’王小雪’ 

查詢速度：1640毫秒 

 select gid,fariqi,neibuyonghu,title from person 

where name='王小雪' 

查詢速度：5920毫秒 

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

3.4 其他索引經驗總結 

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

下面是執行個體語句：（都是提取25萬條資料） 

select id,name,dept,emdate from person where emdate='2007-06-04' 

使用時間：906毫秒 

select id,name,dept,emdate from person where id<=100000 

使用時間：1153毫秒 

這裡，用聚合索引比用不是聚合索引的主鍵速度略快一些。 

2:用聚合索引比用一般的主鍵作order by時速度快，特别是小資料量時 

select id,name,dept,emdate from person order by emdate 

用時：17856 (約18秒) 

select id,name,dept,emdate from person order by id 

用時：44046 (約45秒) 

這裡可以看到，用聚集索引比用一般的主鍵作order by時，速度幾乎快了2.5倍。事實上，有的資料說小資料量情況下，用聚集索引排序列比非聚集索引作為排序列快，10萬以上，則二者的速度差别不明顯。但據目前200萬條資料情況來看，在大資料量的情況下，這個結論依然成立。 

3:使用聚合索引内的時間段，搜尋時間會按資料占整個資料表的百分比 

比例減少，而無論聚合索引使用了多少個 

select id,name,dept,emdate from person where 

emdate='2007-06-04 00:00:00.000' 

用時：1123毫秒（提取10萬條） 

用時：1843毫秒（提取20萬條） 

emdate='2007-06-09 00:00:00.000' 

用時：4500毫秒（提取45萬條） 

從以上統計的資料看來，這個規律基本上是正确的 

其他注意事項 

    “水可載舟，亦可覆舟”，索引也一樣。索引有助于提高檢索性能，但過多或不當的索引也會導緻系統低效。因為使用者在表中每加進一個索引，資料庫就要做更多的工作。過多的索引甚至會導緻索引碎片。是以說，我們要建立一個“适當”的索引體系，特别是對聚合索引的建立，更應精益求精，以使您的資料庫能得到高性能的發揮。在實際的開發中，會遇到很多意想不到的情況，最好是多測試一些方案，找出哪種方案效率最高、最為有效。

4:SQL語句改善 

       一個sql語句大約要經過三個階段，編譯優化，執行，取值，而編譯階段，而第一階段大部分情況下都要花掉60％的時間，是以綁定變量是很重要的，sqlserver和oracle都有緩存區，存放最近使用的sql語句，當有一條sql語句到達資料庫伺服器時，資料庫會首先搜尋緩存區，看它是否存在可以重用的sql語句，如果存在，則無需編譯優化，因為緩存區的sql語句都是編譯優化好了的，可以直接執行，節省相當多的時間。如果沒有發現該語句，則必須要完全經曆語句編譯分析，優化計劃，安全檢查等過程，這不僅耗費了大量的cpu功率，而且還在相當長的一段時間内鎖住了一部分資料庫緩存，這樣執行sql語句的人越多，等待的時間越長，系統的性能會大幅度的下降。

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

select id,name,dept,emdate from person 

where name='王小雪' and id<100000 用時：1220毫秒 

和執行: 

select * from table1 where id< 100000 and name='王小雪' 用時：1173毫秒 

一些人不知道以上兩條語句的執行效率是否一樣，因為如果簡單的從語句先後上看，這兩個語句的确是不一樣，如果id是一個聚合索引，那麼後一句僅僅從表的100000條以内的記錄中查找就行了；而前一句則要先從全表中查找看有幾個name='王小雪'的，而後再根據限制條件條件id<100000來提出查詢結果。事實上，這樣的擔心是不必要的。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。 

原因是通配符%在字元串的開通使得索引無法使用。 如以下查詢 

沒有對name進行索引 

select id,name,dept,emdate from person where name like '%小雪' 

用時 3654毫秒 

對name進行非聚集索引 

用時 3673毫秒 

對name進行聚集索引 

由以上資料可以看到，将比對符号放在被查詢字段的前面，索引根本就不會發生作用，是以這也是要注意的地方，如果不會用到，最好少用 

2:or 是否會引起全表掃描 

有很多資料上說or會引起全表掃描。 

如name=’王小雪’ and emdate>’2007-01-10’不會全表掃描,而 

name=’王小雪’ or emdate>’2007-01-10’則會,但是據我觀察,情況不是這樣的.對于這樣的一個sql語句select id,name,dept,emdate from person where name='王小雪' or emdate>'2007-06-08'，我們可以看sqlserver對于它們的執行計劃 

在有聚集索引的情況下(無論聚集索引建在哪些字段上) 

沒有聚集索引但是主鍵索引的情況下 

沒有任何索引的情況下 

由上可以得出結論,在用到or的時候，如果有聚集索引,就不會引起全表掃描,沒有聚集索引,就會引起全表掃描，是以說，隻要用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 id,name,dept,emdate from person where name in('王小雪','聶海') 

所花時間:8936ms, 

select id,name,dept,emdate from person where name='王小雪' or name='聶海' 

所花時間:5390ms, 

沒有聚集索引 

所花時間:5310ms, 

所花時間:5326ms, 

可見,or 比 in速度快,因為作了聚集索引,是以它們都沒有執行table scan,不過因為聚集索引作用在日期字段emdate上,是以雖然查詢使用了聚集索引,但并不意味着比全表掃描快,其實使用作用在emdate上的聚集索引查詢,在某種意義上來說,也是一種全表掃描,隻不過資料的掃描順序不同而已,在這種情況下,甚至沒有聚集索引反而更快 

5:exists 和 in 的執行效率是一樣的 

     很多資料上都顯示說，exists要比in的執行效率要高，同時應盡可能的用not exists來代替not in。但事實上，我試驗了一下，發現二者無論是前面帶不帶not，二者之間的執行效率都是一樣的。因為涉及子查詢，我試驗這次用SQL SERVER自帶的pubs資料庫。運作前我們可以把SQL SERVER的statistics I/O狀态打開。 文法為:set statistics io on, 要檢視語句的執行過程,打開查詢分析器的消息欄就可以看到,但是在查詢語句之前要加上set statistics io on 

（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) 

第二句的執行結果為： 

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

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

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

select id,name,dept,emdate from person where charindex('小雪',name)>0 

用時：4010ms 

掃描計數 1，邏輯讀 29905 次，實體讀 0 次，預讀 0 次。 

用時：4123ms 

7:union并不絕對比or的執行效率高 

      很多資料都推薦用union來代替or。事實證明，這種說法對于大部分都是适用的。 

(1):select id,name,dept,emdate from person where name='王小雪' or emdate>'2007-06-04' 

用時：85626ms。掃描計數 1，邏輯讀 129905 次，實體讀 0 次，預讀 0 次。次。 

(2):select id,name,dept,emdate from person where name='王小雪' 

union 

select id,name,dept,emdate from person where emdate>'2007-06-04' 

用時：17373ms。掃描計數 2，邏輯讀 59810 次，實體讀 0 次，預讀 0 次。 

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

5:sqlserver的分區 

      對于一些超大型的表，分區是非常有用的。分區是一種邏輯概念，和oracle的分區概念是一樣的.在通常情況下，一個表就是一個整體，當發生資料通路的時候，也是對整個表或整個表的索引進行通路，所謂分區，通俗點講，就是把表按一定的規律劃分成更小的邏輯機關,當發生通路的時候,不以表為機關進行通路,而先在表的基礎上,判斷資料在哪個分區,然後對特定的分區進行通路.正确的分區有利于提高查詢性能.例如,有一個非常大的表,存儲了一些銷售記錄,現在查詢總是按銷售季度來執行這個查詢----每個銷售季度包含幾十萬個記錄,通常你隻是要查詢這個資料集的一個相當小的資料,但是給予銷售季度的檢索卻的确是不太可行的.這個索引可能指向無數個記錄,而以這種方式執行索引範圍掃描是可怕的.為了處理許多查詢任務,系統需要執行全表掃描,但是結果卻必須掃描幾百萬個記錄,其中絕大部分不使用我們的查詢任務.使用智能分區方案,就可以按季度隔離資料.這樣當我們為任意指定的季度去查詢資料時,結果将隻是掃描那個季度的資料.這是所有可能的解決方案種最好的方案.下面将介紹sqlserver的分區使用.

      分區是比較複雜的，以分區的對象來分類的話，則分為兩種，表分區和索引分區。 

表分區主要指的是範圍分區，(貌似比較單一，oracle裡有散列分區等等，不過在sqlserver裡我目前還沒有看到).就這麼說可能不清不楚，下面将以我們已經建立好的ipanel資料庫為例，對person表進行按日期分區，假設ipanel每個月都要進出幾十萬人，然後HR每月還要作很多的統計吧。下面一步一步來，common 

建立檔案組 

各種資料最終是存儲在資料檔案裡，在實際應用中，表的分區都會分布在多個資料檔案中，這樣以便獲得更好的 I/O 平衡，對于檔案，是以檔案組為機關進行管理，檔案組相當于目錄，資料檔案就相當于目錄裡的檔案。為資料庫添加檔案組，這個檔案組分布存儲person表的資料： 

ALTER DATABASE ipanel ADD FILEGROUP [person_fg] 

現在為ipanel資料庫建立了一個名為person_fg的檔案組。下面為該檔案組添加資料檔案。 

添加資料檔案 

ALTER DATABASE ipanel 

ADD FILE 

(NAME = N'person001', 

FILENAME = N'C:\ipanel\person001.ndf', 

SIZE = 5MB, 

MAXSIZE = 100MB, 

FILEGROWTH = 5MB) 

TO FILEGROUP [person_fg] 

如上，為檔案組添加了一個資料檔案 

建立分區函數 

既然分區，那麼就應該有一個分區的标準，就是說資料将以什麼标準來分區，分區函數就是做這件事情的，它定義資料劃分的标準，對表進行邏輯上的劃分。 

CREATE PARTITION FUNCTION personRangePFN(datetime) 

RANGE LEFT FOR VALUES ('20030930', 

'20050930', 

'20070930', 

'20090930') 

上面的分區函數建立了5個分區，并且定義了分區列的資料類型為datetime,因為分區的标準要建在表的某一列上，在此定義，分區列必須是日期時間型。RANGE LEFT表示範圍分區，LEFT所在的選項有兩個：LEFT,RIGHT.分區辨別着資料的上界和下界。如目前選項是LEFT，則表示： 

分區1：<=20030930 

分區2：>20030930,<=20050930 

分區3：>20050930,<=20070930 

分區4：>20070930,<=20090930 

分區5：>20030930 

如果目前選項是RIGHT,則表示： 

分區1：<20030930 

分區2：>=20030930,<20050930 

分區3：>=20050930,<20070930 

分區4：>=20070930,<20090930 

分區5：>=20030930 

建立分區架構 

建立分區函數後，必須将其與分區架構相關聯，以便将分區定向至特定的檔案組。定義分區架構時，即使多個分區位于同一個檔案組中，也必須為每個分區指定一個檔案組。對于前面建立的範圍分區 (personRangePFN)，存在五個分區；最後一個空分區将在 PRIMARY 檔案組中建立。因為此分區永遠不包含資料，是以不需要指定特殊的位置 

CREATE PARTITION SCHEME PersonEmdateScheme 

PARTITION personRangePFN 

TO ([person001], [person002], [person003], [person004], [PRIMARY])

建立分區表 

      定義分區函數（邏輯結構）和分區架構（實體結構）後，即可建立表來利用它們。表定義應使用的架構，而架構又定義函數。要将這三者結合起來，必須指定應該應用分區函數的列。範圍分區始終隻映射到表中的一列，此列應與分區函數中定義的邊界條件的資料類型相比對。另外，如果表應明确限制資料集（而不是從負無窮大到正無窮大），則還應添加 CHECK 限制。 

) 

On PersonEmdateScheme (emdate) 

如果要限制的emdate的值的範圍，則必須給它加上限制，如隻允許emdate的值從2002年9月1日到2010年9月1日， 

則将[emdate] [datetime] NULL 改為 

[emdate] [datetime] NULL 

CONSTRAINT personRangeYear 

CHECK ([emdate] >= '20020901' 

AND [emdate] <= '20100901 11:59:59.997') 

分區總結 

     到此，對于分區表person已經設定完畢，person的資料會根據emdate的值分布到幾個不同的資料檔案裡，在查詢的時候，系統會首先判斷emdate的值，看它在哪個分區，然後隻進入該分區查找資料，這對于超大規模的系統來說，是很有用的，如果一個表有幾千萬上億的資料，即使是索引掃描也是一個很費時的過程，不要忘記，索引也就像相當于簡化了的表。對于索引，sqlserver裡有索引分區，如果索引分區和表分區對齊的話，就是說和表一樣使用了相同的分區函數和相同的分區架構，那麼對于索引的查找，就不是對整個索引的查找了，而是先判斷在哪個索引分區，然後再取查找該索引值，然後找到資料，這樣就會節省很多時間。分區還有一個好處就是，對于一些資料可以更好的進行管理，比如說，定義了2006年度的銷售資料存儲在對應的分區area6，而area6對應的資料檔案是sale006.ndf,到2007年的時候，一般情況下，可能不用06年的資料，按照分區的理論，它也不會通路06年的資料所在的區域。 

分區的應用是比較複雜的，上面隻是介紹了其中一部分，其他還有索引分區，分區合并，分區移出等比較多的的東西。在oracle裡，分區的概念是比較多的，包括對索引的分區都會有很多介紹，如散列分區，混合分區，局部索引，全局索引，原理上是差不多的。在此談分區隻是一個抛磚引玉的過程，如果對sqlserver分區想更深入了解的話，可以看看msdn，有中文的，不過翻譯得很爛。 

6:後記 

     我以前看到過很多項目，資料庫系統隻是被純粹的當作了一個存儲資料的地方，建完表能增删改查就萬事大吉了，有的連索引都沒有，對于資料庫的建立也很不嚴謹，更談不上管理，雖然很多人認為資料庫的管理是DBA的事情，但是我想作為一個技術人員，加深對資料庫的了解是絕對沒有壞處的，開發大型的系統，資料庫肯定是非常重要的。如果想深入學習一門資料庫的話，我建議大家從oracle開始學，因為sqlserver作了很多封裝，而oracle更為複雜，是的，雖然它概念很多，比較複雜，但是卻有助你了解更多的資料庫細節，在很多方面，大部分的資料庫系統都是相同的，oracle學好再來學其他的資料庫，上手就非常容易，如果你會寫PL/SQL程式，那有什麼理由不會寫TRANACT-SQL的資料庫程式呢，文法隻是一些細微的差别，而很多的概念卻是相同的。