數據庫設計經驗談

2019-11-18 22:13:19

字體：大中小

來源：轉載

供稿：網友

數據庫模型的設計是否合理會極大影響系統的使用性能。筆者依據多年來設計和使用數據庫的經驗，提出以下一些設
計原則，供同仁們參考。

　　慎用游標（Cursor）

　　游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數據，根據取出數據條件的不同進行不同的操
作。而對于多表和大表中定義的游標（大的數據集合）循環很容易使程序進入一個漫長的等待甚至死機，筆者在某市“住
房公積金管理系統”進行日終賬戶滾積數計息處理時，對一個10萬個賬戶的游標處理時導致程序進入了一個無限期的等待
（后經測算需48小時才能完成）(硬件環境：Alpha/4000 128MB RAM ,SCO Unix ,Sybase 11.0)。經修改程序并改用UPDATE
語句后，該處理過程得以在20分鐘之內完成。示例如下：

　　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

...

　　Fetch Mycursor into @vcount—no

　　End

　　　　...

　　改為

　　Update COUNT set 操作1 for 條件1

　　Update COUNT set 操作2 for 條件2

...

　　在某些必須使用游標的場合，可考慮將符合條件的數據行轉入臨時表中，再對臨時表定義游標進行操作，這樣，可使
性能得到明顯提高。筆者在某地市“電信收費系統”數據庫后臺程序設計中，對一個表（3萬行中符合條件的30多行數據）
進行游標操作(硬件環境：PC服務器，PⅡ266 64MB RAM ,Windows NT4.0 MS SQL Server 6.5)。

　　示例如下：

Create ＃tmp /＊定義臨時表＊/

　　　　( 字段1

　　　　字段2

　　　　　　... )

　　Insert into ＃tmp select ＊ from TOTAL where 條件

　　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時的
事務提交（最后一個提交語句時），才把物理數據寫入磁盤。

　　數據類型的選擇

　　數據類型的合理選擇對于數據庫的性能和操作具有很大的影響，有關這方面的書籍也有不少的闡述，筆者這里主要介
紹幾點經驗：

　　1. Identify字段不要作為表的主鍵與其它表關聯，這將會影響到該表的數據遷移。

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

　　3. 日期型字段的優點是有眾多的日期函數支持，因此，在日期的大小比較、加減操作上非常簡單。但是，在按照日期
作為條件的查詢操作也要用函數，相比其它數據類型速度上就慢許多,因為用函數作為查詢的條件時，服務器無法用先進的
性能策略來優化查詢而只能進行表掃描遍歷每行。

上一篇：用SQL實現分布式數據復制

下一篇：從數據庫中動態選取下拉列表的方法