大型資料庫設計原則

一個好的資料庫産品不等于就有一個好的應用系統，如果不能設計一個合理的資料庫模型，不僅會增加用戶端和伺服器段程式的程式設計和維護的難度，而且将會影響系統實際運作的性能。一般來講，在一個MIS系統分析、設計、測試和試運作階段，因為資料量較小，設計人員和測試人員往往隻注意到功能的實作，而很難注意到性能的薄弱之處，等到系統投入實際運作一段時間後，才發現系統的性能在降低，這時再來考慮提高系統性能則要花費更多的人力物力，而整個系統也不可避免的形成了一個打更新檔工程。筆者依據多年來設計和使用資料庫的經驗，提出以下一些設計準則，供同仁們參考。

命名的規範

---- 不同的資料庫産品對對象的命名有不同的要求，是以，資料庫中的各種對象的命名、背景程式的代碼編寫應采用大小寫敏感的形式，各種對象命名長度不要超過30個字元，這樣便于應用系統适應不同的資料庫。

遊标（Cursor）的慎用

---- 遊标提供了對特定集合中逐行掃描的手段，一般使用遊标逐行周遊資料，根據取出的資料不同條件進行不同的操作。尤其對多表和大表定義的遊标（大的資料集合）循環很容易使程式進入一個漫長的等特甚至當機，筆者在某市《住房公積金管理系統》進行日終帳戶滾積數計息處理時，對一個10萬個帳戶的遊标處理導緻程式進入了一個無限期的等特（後經測算需48個小時才能完成）(硬體環境：Alpha/4000 128Mram ,Sco Unix ,Sybase 11.0)，後根據不同的條件改成用不同的UPDATE語句得以在二十分鐘之内完成。示例如下： 

Declare Mycursor cursor for select  count_no from COUNT

Open Mycursor

Fetch Mycursor into @vcount_no

While (@@sqlstatus=0)

   Begin

If  @vcount_no=’’  條件1

操作1

  If  @vcount_no=’’  條件2

操作2

。。。

End

改為

Update COUNT set  操作1 for 條件1

Update COUNT set  操作2 for 條件2

---- 在有些場合，有時也非得使用遊标，此時也可考慮将符合條件的資料行轉入臨時表中，再對臨時表定義遊标進行操作，可時性能得到明顯提高。筆者在某地市〈電信收費系統〉資料庫背景程式設計中，對一個表（3萬行中符合條件的30多行資料）進行遊标操作(硬體環境：PC伺服器，PII266 64Mram ,NT4.0 Ms Sqlserver 6.5)。 示例如下：

Create #tmp   /* 定義臨時表 */

(字段1

字段2

)

Insert into #tmp select * from TOTAL where  

條件  /* TOTAL中3萬行 符合條件隻有幾十行 */

Declare Mycursor cursor for select * from #tmp 

/*對臨時表定義遊标*/

索引(Index)的使用原則

---- 建立索引一般有以下兩個目的：維護被索引列的唯一性和提供快速通路表中資料的政策。大型資料庫有兩種索引即簇索引和非簇索引，一個沒有簇索引的表是按堆結構存儲資料，所有的資料均添加在表的尾部，而建立了簇索引的表，其資料在實體上會按照簇索引鍵的順序存儲，一個表隻允許有一個簇索引，是以，根據B樹結構，可以了解添加任何一種索引均能提高按索引列查詢的速度，但會降低插入、更新、删除操作的性能，尤其是當填充因子（Fill Factor）較大時。是以對索引較多的表進行頻繁的插入、更新、删除操作，建表和索引時因設定較小的填充因子，以便在各資料頁中留下較多的自由空間，減少頁分割及重新組織的工作。

資料的一緻性和完整性

---- 為了保證資料庫的一緻性和完整性，設計人員往往會設計過多的表間關聯（Relation），盡可能的降低資料的備援。表間關聯是一種強制性措施，建立後，對父表（Parent Table）和子表(Child Table)的插入、更新、删除操作均要占用系統的開銷，另外，最好不要用Identify 屬性字段作為主鍵與子表關聯。如果資料備援低，資料的完整性容易得到保證，但增加了表間連接配接查詢的操作，為了提高系統的響應時間，合理的資料備援也是必要的。使用規則（Rule）和限制（Check）來防止系統操作人員誤輸入造成資料的錯誤是設計人員的另一種常用手段，但是，不必要的規則和限制也會占用系統的不必要開銷，需要注意的是，限制對資料的有效性驗證要比規則快。所有這些，設計人員在設計階段應根據系統操作的類型、頻度加以均衡考慮。

事務的陷阱

---- 事務是在一次性完成的一組操作。雖然這些操作是單個的操作，SQL Server能夠保證這組操作要麼全部都完成，要麼一點都不做。正是大型資料庫的這一特性，使得資料的完整性得到了極大的保證。

---- 衆所周知，SQL Server為每個獨立的SQL語句都提供了隐含的事務控制，使得每個DML的資料操作得以完整送出或復原，但是SQL Server還提供了顯式事務控制語句

---- BEGIN TRANSACTION 開始一個事務

---- COMMIT TRANSACTION 送出一個事務

---- ROLLBACK TRANSACTION 復原一個事務

---- 事務可以嵌套，可以通過全局變量@@trancount檢索到連接配接的事務處理嵌套層次。需要加以特别注意并且極容易使程式設計人員犯錯誤的是，每個顯示或隐含的事物開始都使得該變量加1，每個事務的送出使該變量減1，每個事務的復原都會使得該變量置0，而隻有當該變量為0時的事務送出（最後一個送出語句時），這時才把實體資料寫入磁盤。

資料庫性能調整

---- 在計算機硬體配置和網絡設計确定的情況下，影響到應用系統性能的因素不外乎為資料庫性能和用戶端程式設計。而大多數資料庫設計員采用兩步法進行資料庫設計：首先進行邏輯設計，而後進行實體設計。資料庫邏輯設計去除了所有備援資料，提高了資料吞吐速度，保證了資料的完整性，清楚地表達資料元素之間的關系。而對于多表之間的關聯查詢（尤其是大資料表）時，其性能将會降低，同時也提高了客 戶端程式的程式設計難度，是以，實體設計需折衷考慮，根據業務規則，确定對關聯表的資料量大小、資料項的通路頻度，對此類資料表頻繁的關聯查詢應适當提高資料備援設計。

資料類型的選擇

---- 資料類型的合理選擇對于資料庫的性能和操作具有很大的影響，有關這方面的書籍也有不少的闡述，這裡主要介紹幾點經驗。

Identify字段不要作為表的主鍵與其它表關聯，這将會影響到該表的資料遷移。

Text 和Image字段屬指針型資料，主要用來存放二進制大型對象（BLOB）。這類資料的操作相比其它資料類型較慢，是以要避開使用。

日期型字段的優點是有衆多的日期函數支援，是以，在日期的大小比較、加減操作上非常簡單。但是，在按照日期作為條件的查詢操作也要用函數，相比其它資料類型速度上就慢許多,因為用函數作為查詢的條件時，伺服器無法用先進的性能政策來優化查詢而隻能進行表掃描周遊每行。 

---- 例如：要從DATA_TAB1中（其中有一個名為DATE的日期字段）查詢1998年的所有記錄。 

---- Select * from DATA_TAB1 where datepart(yy,DATE)=1998