轉(zhuǎn)：http://blog.csdn.net/jiangxinyu/archive/2008/09/03/2874201.aspx

摘要：數(shù)據(jù)庫(kù)優(yōu)化不僅是數(shù)據(jù)庫(kù)管理員的任務(wù)，程序員也必須知道一些優(yōu)化技巧，有利于開發(fā)高效的數(shù)據(jù)庫(kù)系統(tǒng)。
關(guān)鍵字：數(shù)據(jù)庫(kù) 優(yōu)化 技巧

如果是團(tuán)隊(duì)開發(fā)，作為程序員必須知道本文描述的數(shù)據(jù)庫(kù)優(yōu)化技巧，如果你的sa水平比較差，那即使你再努力也些不出高效的數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)。
如果是單獨(dú)開發(fā)，那就更不必說了。

多數(shù)公司的數(shù)據(jù)庫(kù)管理員(sa)是不夠格的，即使拿了各種認(rèn)證證件，也差不多還是垃圾管理員，無非是可以混個(gè)好職位，多拿一些工資。
如果你所在的公司沒有合格的sa，作為程序員的你必須執(zhí)行做許多數(shù)據(jù)庫(kù)優(yōu)化的工作了。
市面上的數(shù)據(jù)庫(kù)類圖書也不過是騙錢的把戲，無非為了出書而出書，為了出名而出書。
95％以上的作者沒有實(shí)踐的經(jīng)驗(yàn)，99％以上的作者沒有優(yōu)化的經(jīng)驗(yàn)。他們編寫圖書的來源無非是外文（不見得好啊）或者是互聯(lián)網(wǎng)上的資訊。

廢話不說了，開始吧。程序員級(jí)別的優(yōu)化有哪些手段？

（1）數(shù)據(jù)庫(kù)的設(shè)置：如果你的數(shù)據(jù)庫(kù)記錄數(shù)不會(huì)超過30萬條？如果你的數(shù)據(jù)庫(kù)記錄超過100萬條？該如何設(shè)置數(shù)據(jù)庫(kù)？一個(gè)或多個(gè)？
（2）數(shù)據(jù)庫(kù)表的設(shè)置：當(dāng)你的某個(gè)數(shù)據(jù)庫(kù)表記錄超過100萬級(jí)別，而且每天大量增長(zhǎng)，這是一個(gè)不得不考慮的問題。如果你的系統(tǒng)瀏覽量很大，即使是30萬條記錄也是需要考慮的。
（3）索引的使用：索引可以大大提高數(shù)據(jù)庫(kù)訪問速度。什么時(shí)候用？哪些字段使用？
（4）存儲(chǔ)過程的使用：存儲(chǔ)過程終歸是比較好的，但是如果需要維護(hù)成百上千的存儲(chǔ)過程，未必是劃算的工程。
（5）高效的分頁技術(shù)：數(shù)據(jù)庫(kù)記錄分頁列表是大量必須使用的基本技術(shù)，怎樣的分頁是快速的？

宗旨你需要從上述5個(gè)方面考慮數(shù)據(jù)庫(kù)的優(yōu)化。

什么時(shí)候需要數(shù)據(jù)庫(kù)優(yōu)化？
（1）編寫代碼之前；
（2）系統(tǒng)速度慢了的時(shí)候；

下面就是一些具體的優(yōu)化技巧了。

（1）超大量記錄數(shù)據(jù)庫(kù)的優(yōu)化技巧

如果你的數(shù)據(jù)庫(kù)表記錄有超過100萬級(jí)別，而且不斷增長(zhǎng)中。可以采取兩個(gè)手段：
第一：將數(shù)據(jù)庫(kù)表拆分到不同的庫(kù)中，比如 tblMEMBER 就可以拆分到 DB1 與 DB2 中去。
實(shí)際上，可以拆分到 DB001 ... DB100 甚至更多的庫(kù)中間去。
DB1 與 DB2 最好不在一塊硬盤上。
第二：如果更大量級(jí)的數(shù)據(jù)，則最好拆分到不同的數(shù)據(jù)庫(kù)服務(wù)器中去。

數(shù)據(jù)庫(kù)的拆分帶來的是查詢等操作的復(fù)雜性。簡(jiǎn)單地可以通過 hash 或者 按序號(hào) 匹配不同的數(shù)據(jù)庫(kù)。復(fù)雜一些，應(yīng)該設(shè)置一個(gè)獨(dú)立的應(yīng)用服務(wù)器（軟件）協(xié)調(diào)其中的操作。

（2）中等量級(jí)數(shù)據(jù)庫(kù)的優(yōu)化技巧

所謂中等量級(jí)數(shù)據(jù)庫(kù)是指數(shù)據(jù)庫(kù)100萬－500萬條記錄左右（單個(gè)數(shù)據(jù)庫(kù)表）。這樣的數(shù)據(jù)庫(kù)為了提高訪問（響應(yīng)）速度，可以將表拆分到更小的表。比如 tblMEMBER 可以拆分為 tblMEMBER_00 ... tblMEMBER_99 。
這樣可以保證每個(gè)表的記錄數(shù)不超過50萬，那速度是"相當(dāng)"快了。

（3）避免使用視圖(viewport)與關(guān)聯(lián)

視圖viewport與關(guān)聯(lián)都是為了程序員處理相對(duì)復(fù)雜的數(shù)據(jù)管理提供方便的手段。萬物有其利，必有其弊。視圖和關(guān)聯(lián)提高了編程效率，都會(huì)較大地影響數(shù)據(jù)庫(kù)的訪問效率（事實(shí)上并不像一般資料說介紹的的那樣高效），因此如果是web應(yīng)用，則建議一般不要使用視圖與關(guān)聯(lián)。

（4）不要忘記索引(index)也不要濫用索引(index)

索引是提高數(shù)據(jù)庫(kù)效率的簡(jiǎn)單又高效的方法。只要是設(shè)置了數(shù)據(jù)庫(kù)表(table)，就不要忘記設(shè)置索引(index)。將索引設(shè)置在經(jīng)常用于排序的字段上，其他字段就不要設(shè)置了。
索引不是越多越好，也不是什么字段都適合建立索引的。數(shù)據(jù)重復(fù)性太多的字段不要設(shè)置索引。比如 tblMEMBER 的 iSex 字段只有 0 1 兩個(gè)值，就不要設(shè)置索引。

（5）二進(jìn)制的 text image 等字段應(yīng)該單獨(dú)設(shè)置別的表中

一般的數(shù)據(jù)庫(kù)應(yīng)用難免都需要保存比如描述、圖片等信息；一般描述類信息用 text 字段，圖片類信息用 image 字段；這里要說的是，不要將這些字段與其他字段放在一個(gè)表中。
比如：

view plaincopy to clipboardprint?
tblMEMBER   
id (int)   
cName (varchar)(64)   
cDescription (text)   
bPhoto (image)   
dDate (datetime)   
就應(yīng)該拆分為3個(gè)表   
tblMEMBER   
id (int)   
cName (varchar)(64)   
dDate (datetime)   
tblMEMBER_DESC   
id (int)   
cDescription (text)   
dDate (datetime)   
tblMEMBER_PHOTO   
id (int)   
bPhoto (image)   
dDate (datetime) 
  tblMEMBER
  id (int)
  cName (varchar)(64)
  cDescription (text)
  bPhoto (image)
  dDate (datetime)
  就應(yīng)該拆分為3個(gè)表
  tblMEMBER
  id (int)
  cName (varchar)(64)
  dDate (datetime)
  tblMEMBER_DESC
  id (int)
  cDescription (text)
  dDate (datetime)
  tblMEMBER_PHOTO
  id (int)
  bPhoto (image)
  dDate (datetime)
 （6）不要使用文本類型的 id

一般的數(shù)據(jù)庫(kù)表都會(huì)以一個(gè)種子字段作為主鍵。可以在與不少年青的程序員朋友溝通過程中，發(fā)現(xiàn)他們很喜歡用字符串類型的作為系統(tǒng)的 id 號(hào)。
比如：id = XX XX XX XX 這樣的字符串，每?jī)蓚€(gè)位置代表不同的類別等含義。
不知道是那本教材如此誤人子弟，作出這樣的表率 :<
作為系統(tǒng)的 id 號(hào)，一定要使用數(shù)字型的。

（7）數(shù)據(jù)庫(kù)表table的字段field不要太多

本以為無需說明，也是發(fā)現(xiàn)不少的朋友，為了省事，一股腦把所有的相關(guān)字段都放在一個(gè)表中間。這樣做的后果便是，程序?qū)懫饋砗?jiǎn)單了，運(yùn)行效率下來了。
無論字段多少，有兩類字段是必須獨(dú)立出去的：一是進(jìn)程更新的字段，比如文章的點(diǎn)擊次數(shù)字段iShow，二是二進(jìn)制或者是text字段；

（8）將字符串(varchar)比較變成數(shù)字型(int)比較

每個(gè)系統(tǒng)都會(huì)有用戶管理，其中必然有 昵稱，密碼，郵件等的字符串類型數(shù)據(jù)比較的問題。在數(shù)據(jù)庫(kù)操作中，字符串比較的效率是相當(dāng)?shù)拖碌摹Ｒ虼擞龅阶址谋容^，必須將其轉(zhuǎn)換為數(shù)字型比較。
具體做法是：在數(shù)據(jù)庫(kù)表中增加相應(yīng)的數(shù)字字段，比如 cNickname -> iNickNumber ，其中 iNickNumber 的數(shù)值為 cNickname 的 哈希值（如何計(jì)算字符串的哈希值？請(qǐng)參閱本站的其他文章）。
通過這樣的轉(zhuǎn)換，系統(tǒng)效率可以提高 100 倍哦！！！

（9）為每個(gè)數(shù)據(jù)庫(kù)表(table)設(shè)置 datetime 字段

在許多情況下，很多的表是不需要 datetime 字段用于保存時(shí)間的。本文的建議是你應(yīng)該為每個(gè)表都設(shè)置 datetime 字段，而且默認(rèn)值為 getdate()。
我們的經(jīng)驗(yàn)是，datetime 是實(shí)數(shù)，占用字節(jié)不多；在進(jìn)行系統(tǒng)維護(hù)，遠(yuǎn)程備份等環(huán)節(jié)都會(huì)發(fā)揮意想不到的效果。

（10）適當(dāng)使用存儲(chǔ)過程(Stored Processing)

存儲(chǔ)過程(sp)已經(jīng)被大大地宣傳了，本文也不例外地贊許采用存儲(chǔ)過程。本文的建議是只在下列情況才使用存儲(chǔ)過程：一是一個(gè)業(yè)務(wù)處理是事務(wù)，包含了多個(gè)處理過程；二是一種處理被高頻使用，使用存儲(chǔ)過程可以提高效率；

（11）使用高效的分頁(ination)技術(shù)

數(shù)據(jù)庫(kù)記錄分頁列表是大量必須使用的基本技術(shù)，因此本文建議你在每個(gè)數(shù)據(jù)庫(kù)中建立下面的存儲(chǔ)過程：

view plaincopy to clipboardprint?
CREATE PROCEDURE xsp_ination   
(   
@tblName   varchar(64),                
@strGetFields varchar(256) = "*",    
@fldName varchar(64)="",               
@PageSize   int = 20,                  
@PageIndex  int = 1,                   
@OrderType bit = 1,                    
@strWhere  varchar(256) = ""     
)   
AS    
BEGIN  
declare @strSQL   varchar(1000)      
declare @strTmp   varchar(110)        
declare @strOrder varchar(400)      
SET NOCOUNT ON  
if @OrderType != 0   
   begin  
      set @strTmp = "<(select min"    
      set @strOrder = " order by [" + @fldName +"] desc"    
   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  
      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  
EXEC (@strSQL)   
if @@error=0 return 1   
SET NOCOUNT OFF  
END  
GO 
CREATE PROCEDURE xsp_ination
(
@tblName   varchar(64),      
@strGetFields varchar(256) = "*",
@fldName varchar(64)="",    
@PageSize   int = 20,     
@PageIndex  int = 1,      
@OrderType bit = 1,      
@strWhere  varchar(256) = "" 
)
AS
BEGIN
declare @strSQL   varchar(1000)  
declare @strTmp   varchar(110)    
declare @strOrder varchar(400)  
SET NOCOUNT ON
if @OrderType != 0
 begin
  set @strTmp = "<(select min"
  set @strOrder = " order by [" + @fldName +"] desc"
 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
  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
EXEC (@strSQL)
if @@error=0 return 1
SET NOCOUNT OFF
END
GO
 
使用方法是(C#)：

view plaincopy to clipboardprint?
sql = "EXEC [dbo].[xsp_ination] \"tblNEWS\",\"*\",\"id\",40," + pindex.ToString() + ",1,\"iType=" + type.ToString();   
SqlDataReader sr = ExecuteReader(sql);   
while (sr.Read())   
{   
   ...   
}   
sr.Close(); 
sql = "EXEC [dbo].[xsp_ination] \"tblNEWS\",\"*\",\"id\",40," + pindex.ToString() + ",1,\"iType=" + type.ToString();
SqlDataReader sr = ExecuteReader(sql);
while (sr.Read())
{
   ...
}
sr.Close();
 
上面的優(yōu)化技巧僅是一些常見的手段，如果你的系統(tǒng)（小系統(tǒng)就算了）遇到效率問題，可以與聯(lián)高軟件聯(lián)系。

轉(zhuǎn)載本文請(qǐng)注明出處，以便遇到優(yōu)化困難的朋友可以找到聯(lián)高提供幫助。

本文來自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：http://blog.csdn.net/jiangxinyu/archive/2008/09/03/2874201.aspx