轉：http://blog.csdn.net/jiangxinyu/archive/2008/09/03/2874201.aspx

摘要：數據庫優化不僅是數據庫管理員的任務，程序員也必須知道一些優化技巧，有利于開發高效的數據庫系統。
關鍵字：數據庫 優化 技巧

如果是團隊開發，作為程序員必須知道本文描述的數據庫優化技巧，如果你的sa水平比較差，那即使你再努力也些不出高效的數據庫應用系統。
如果是單獨開發，那就更不必說了。

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

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

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

宗旨你需要從上述5個方面考慮數據庫的優化。

什么時候需要數據庫優化？
（1）編寫代碼之前；
（2）系統速度慢了的時候；

下面就是一些具體的優化技巧了。

（1）超大量記錄數據庫的優化技巧

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

數據庫的拆分帶來的是查詢等操作的復雜性。簡單地可以通過 hash 或者 按序號 匹配不同的數據庫。復雜一些，應該設置一個獨立的應用服務器（軟件）協調其中的操作。

（2）中等量級數據庫的優化技巧

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

（3）避免使用視圖(viewport)與關聯

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

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

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

（5）二進制的 text image 等字段應該單獨設置別的表中

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

view plaincopy to clipboardprint?
tblMEMBER   
id (int)   
cName (varchar)(64)   
cDescription (text)   
bPhoto (image)   
dDate (datetime)   
就應該拆分為3個表   
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)
  就應該拆分為3個表
  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

一般的數據庫表都會以一個種子字段作為主鍵。可以在與不少年青的程序員朋友溝通過程中，發現他們很喜歡用字符串類型的作為系統的 id 號。
比如：id = XX XX XX XX 這樣的字符串，每兩個位置代表不同的類別等含義。
不知道是那本教材如此誤人子弟，作出這樣的表率 :<
作為系統的 id 號，一定要使用數字型的。

（7）數據庫表table的字段field不要太多

本以為無需說明，也是發現不少的朋友，為了省事，一股腦把所有的相關字段都放在一個表中間。這樣做的后果便是，程序寫起來簡單了，運行效率下來了。
無論字段多少，有兩類字段是必須獨立出去的：一是進程更新的字段，比如文章的點擊次數字段iShow，二是二進制或者是text字段；

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

每個系統都會有用戶管理，其中必然有 昵稱，密碼，郵件等的字符串類型數據比較的問題。在數據庫操作中，字符串比較的效率是相當低下的。因此遇到字符串的比較，必須將其轉換為數字型比較。
具體做法是：在數據庫表中增加相應的數字字段，比如 cNickname -> iNickNumber ，其中 iNickNumber 的數值為 cNickname 的 哈希值（如何計算字符串的哈希值？請參閱本站的其他文章）。
通過這樣的轉換，系統效率可以提高 100 倍哦！！！

（9）為每個數據庫表(table)設置 datetime 字段

在許多情況下，很多的表是不需要 datetime 字段用于保存時間的。本文的建議是你應該為每個表都設置 datetime 字段，而且默認值為 getdate()。
我們的經驗是，datetime 是實數，占用字節不多；在進行系統維護，遠程備份等環節都會發揮意想不到的效果。

（10）適當使用存儲過程(Stored Processing)

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

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

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

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();
 
上面的優化技巧僅是一些常見的手段，如果你的系統（小系統就算了）遇到效率問題，可以與聯高軟件聯系。

轉載本文請注明出處，以便遇到優化困難的朋友可以找到聯高提供幫助。

本文來自CSDN博客，轉載請標明出處：http://blog.csdn.net/jiangxinyu/archive/2008/09/03/2874201.aspx