[zt] 談資料庫的效能優化

轉載自  http://www.垃圾廣告.com/topic/216078  作者 jameswxx   

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秒）
 不在主鍵上建立聚集索引，只建普通索引
Select id,name,dept,emdate from person
用時：17923毫秒（即：18秒）
以上查詢執行的實際上索引不會發揮作用，因為提取的是全部資料。聚集索引在這裡會耗費更多的資源，所以會看到，不建立聚集索引比建立聚集索引還要快

(2):按日期進行過濾(用到索引)
 在主鍵上建立聚集索引，在emdate上建立非聚集索引：
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate())
用時：12376毫秒（12秒）

 在主鍵上建立聚集索引，在emdate上沒有索引：
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate())
用時：21296毫秒（21秒）

 在主鍵上建立非聚集索引，在emdate上建立非聚集索引：
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate())
用時：11590毫秒（12秒）

 在主鍵上建立非聚集索引，在emdate上建立聚集索引：
select id,name,dept,emdate from person where emdate>dateadd(day,+1,getdate())
andemdate用時：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萬條）

select id,name,dept,emdate from person where
emdate='2007-06-04 00:00:00.000'
用時：1843毫秒（提取20萬條）

select id,name,dept,emdate from person where
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 ，50005000
如果一個表示式不能滿足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進行非聚集索引
select id,name,dept,emdate from person where name like '%小雪'
用時 3673毫秒
對name進行聚集索引
select id,name,dept,emdate from person where name like '%小雪'
用時 3673毫秒
由以上資料可以看到，將匹配符號放在被查詢欄位的前面，索引根本就不會發生作用，所以這也是要注意的地方，如果不會用到，最好少用

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,

沒有聚集索引
select id,name,dept,emdate from person where name in('王小雪','聶海')
所花時間:5310ms,
select id,name,dept,emdate from person where name='王小雪' or name='聶海'
所花時間: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)
第二句的執行結果為：
表 'sales'。掃描計數 18，邏輯讀 56 次，物理讀 0 次，預讀 0 次。
表 'titles'。掃描計數 1，邏輯讀 2 次，物理讀 0 次，預讀 0 次。

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

6:用函式charindex()和前面加萬用字元%的LIKE執行效率一樣
      前面，我們談到，如果在LIKE前面加上萬用字元%，那麼將會引起全表掃描，所以其執行效率是低下的。但有的資料介紹說，用函式charindex()來代替LIKE速度會有大的提升，但據我測試，發現這種說明也是錯誤的：
select id,name,dept,emdate from person where charindex('小雪',name)>0
用時：4010ms
掃描計數 1，邏輯讀 29905 次，物理讀 0 次，預讀 0 次。

select id,name,dept,emdate from person where name like '%小雪'
用時：4123ms
掃描計數 1，邏輯讀 29905 次，物理讀 0 次，預讀 0 次。

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)
AS
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
AS
PARTITION personRangePFN
TO ([person001], [person002], [person003], [person004], [PRIMARY])

建立分割槽表
      定義分割槽函式（邏輯結構）和分割槽架構（物理結構）後，即可建立表來利用它們。表定義應使用的架構，而架構又定義函式。要將這三者結合起來，必須指定應該應用分割槽函式的列。範圍分割槽始終只對映到表中的一列，此列應與分割槽函式中定義的邊界條件的資料型別相匹配。另外，如果表應明確限制資料集（而不是從負無窮大到正無窮大），則還應新增 CHECK 約束。

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 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的資料庫程式呢，語法只是一些細微的差別，而很多的概念卻是相同的。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-605313/，如需轉載，請註明出處，否則將追究法律責任。