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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

索引是提高數(shù)據(jù)庫效率的簡單又高效的方法。只要是設(shè)置了數(shù)據(jù)庫表(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ù)庫應(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ù)庫表都會(huì)以一個(gè)種子字段作為主鍵。可以在與不少年青的程序員朋友溝通過程中，發(fā)現(xiàn)他們很喜歡用字符串類型的作為系統(tǒng)的 id 號。
比如：id = XX XX XX XX 這樣的字符串，每兩個(gè)位置代表不同的類別等含義。
不知道是那本教材如此誤人子弟，作出這樣的表率 :<
作為系統(tǒng)的 id 號，一定要使用數(shù)字型的。

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

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

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

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

（9）為每個(gè)數(shù)據(jù)庫表(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ù)庫記錄分頁列表是大量必須使用的基本技術(shù)，因此本文建議你在每個(gè)數(shù)據(jù)庫中建立下面的存儲(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)載本文請注明出處，以便遇到優(yōu)化困難的朋友可以找到聯(lián)高提供幫助。

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