數(shù) 據(jù) 庫(kù) 設(shè) 計(jì) 經(jīng) 驗(yàn) 談

2019-11-18 22:13:19

字體：大中小

供稿：網(wǎng)友

數(shù)據(jù)庫(kù)模型的設(shè)計(jì)是否合理會(huì)極大影響系統(tǒng)的使用性能。筆者依據(jù)多年來(lái)設(shè)計(jì)和使用數(shù)據(jù)庫(kù)的經(jīng)驗(yàn)，提出以下一些設(shè)
計(jì)原則，供同仁們參考。

　　慎用游標(biāo)（Cursor）

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

　　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

...

　　在某些必須使用游標(biāo)的場(chǎng)合，可考慮將符合條件的數(shù)據(jù)行轉(zhuǎn)入臨時(shí)表中，再對(duì)臨時(shí)表定義游標(biāo)進(jìn)行操作，這樣，可使
性能得到明顯提高。筆者在某地市“電信收費(fèi)系統(tǒng)”數(shù)據(jù)庫(kù)后臺(tái)程序設(shè)計(jì)中，對(duì)一個(gè)表（3萬(wàn)行中符合條件的30多行數(shù)據(jù)）
進(jìn)行游標(biāo)操作(硬件環(huán)境：PC服務(wù)器，PⅡ266 64MB RAM ,Windows NT4.0 MS SQL Server 6.5)。

　　示例如下：

Create ＃tmp /＊定義臨時(shí)表＊/

　　　　( 字段1

　　　　字段2

　　　　　　... )

　　Insert into ＃tmp select ＊ from TOTAL where 條件

　　Declare Mycursor cursor for select ＊ from ＃tmp /＊對(duì)臨時(shí)表定義游標(biāo)＊/

　　　　...

　　索引(Index)的使用技巧

　　創(chuàng)建索引一般有兩個(gè)目的：維護(hù)被索引列的惟一性和提供快速訪問(wèn)表中數(shù)據(jù)的策略。大型數(shù)據(jù)庫(kù)有兩種索引，即簇索
引和非簇索引，一個(gè)沒(méi)有簇索引的表是按堆結(jié)構(gòu)存儲(chǔ)數(shù)據(jù)，所有的數(shù)據(jù)均添加在表的尾部；而建立了簇索引的表，其數(shù)據(jù)
在物理上會(huì)按照簇索引鍵的順序存儲(chǔ)，一個(gè)表只允許有一個(gè)簇索引，因此，根據(jù)B樹結(jié)構(gòu)，可以理解添加任何一種索引均能
提高按索引列查詢的速度，但與此同時(shí)會(huì)降低插入、更新、刪除操作的性能，尤其是當(dāng)填充因子（Fill Factor）較大時(shí)。
所以對(duì)索引較多的表進(jìn)行頻繁的插入、更新、刪除操作時(shí)，建表和索引時(shí)應(yīng)設(shè)置較小的填充因子，以便在各數(shù)據(jù)頁(yè)中留下
較多的自由空間，減少頁(yè)分割及重新組織的工作。

　　數(shù)據(jù)的一致性和完整性

　　為了保證數(shù)據(jù)庫(kù)的一致性和完整性，設(shè)計(jì)人員往往會(huì)設(shè)計(jì)過(guò)多的表間關(guān)聯(lián)（Relation），盡可能地降低數(shù)據(jù)冗余。表
間關(guān)聯(lián)是一種強(qiáng)制性措施，建立后，對(duì)父表（Parent Table）和子表(Child Table)的插入、更新、刪除操作均要占用系統(tǒng)
的開銷，另外，最好不要用Identify 屬性字段作為主鍵與子表關(guān)聯(lián)。如果數(shù)據(jù)冗余低，數(shù)據(jù)的完整性容易得到保證，但增
加了表間連接查詢的操作。為了提高系統(tǒng)的響應(yīng)時(shí)間，合理的數(shù)據(jù)冗余也是必要的。使用規(guī)則（Rule）和約束（Check）來(lái)
防止系統(tǒng)操作人員誤輸入造成數(shù)據(jù)的錯(cuò)誤是，設(shè)計(jì)人員的另一種常用手段，但是，不必要的規(guī)則和約束也會(huì)占用系統(tǒng)的不
必要開銷，需要注意的是，約束對(duì)數(shù)據(jù)的有效性驗(yàn)證要比規(guī)則快。所有這些，設(shè)計(jì)人員在設(shè)計(jì)階段應(yīng)根據(jù)系統(tǒng)操作的類
型、頻度加以均衡考慮。

　　事務(wù)的陷阱

　　事務(wù)是在一次性完成的一組操作。雖然這些操作是單個(gè)的操作，SQL Server能夠保證這組操作要么全部都完成，要么
一點(diǎn)兒都不做。正是大型數(shù)據(jù)庫(kù)的這一特性，使得數(shù)據(jù)的完整性得到了極大的保證。

　　眾所周知，SQL Server為每個(gè)獨(dú)立的SQL語(yǔ)句都提供了隱含的事務(wù)控制，使得每個(gè)DML的數(shù)據(jù)操作得以完整提交或回
滾，但是SQL Server還提供了顯式事務(wù)控制語(yǔ)句，如：

　　BEGIN TRANSACTION 開始一個(gè)事務(wù)

　　COMMIT TRANSACTION 提交一個(gè)事務(wù)

　　ROLLBACK TRANSACTION 回滾一個(gè)事務(wù)

　　事務(wù)可以嵌套，可以通過(guò)全局變量@@trancount檢索到連接的事務(wù)處理嵌套層次。要特別注意的是，每個(gè)顯示或隱含的
事物開始都使得該變量加1，每個(gè)事務(wù)的提交使該變量減1，每個(gè)事務(wù)的回滾都會(huì)使得該變量置0，而只有當(dāng)該變量為0時(shí)的
事務(wù)提交（最后一個(gè)提交語(yǔ)句時(shí)），才把物理數(shù)據(jù)寫入磁盤。

　　數(shù)據(jù)類型的選擇

　　數(shù)據(jù)類型的合理選擇對(duì)于數(shù)據(jù)庫(kù)的性能和操作具有很大的影響，有關(guān)這方面的書籍也有不少的闡述，筆者這里主要介
紹幾點(diǎn)經(jīng)驗(yàn)：

　　1. Identify字段不要作為表的主鍵與其它表關(guān)聯(lián)，這將會(huì)影響到該表的數(shù)據(jù)遷移。

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

　　3. 日期型字段的優(yōu)點(diǎn)是有眾多的日期函數(shù)支持，因此，在日期的大小比較、加減操作上非常簡(jiǎn)單。但是，在按照日期
作為條件的查詢操作也要用函數(shù)，相比其它數(shù)據(jù)類型速度上就慢許多,因?yàn)橛煤瘮?shù)作為查詢的條件時(shí)，服務(wù)器無(wú)法用先進(jìn)的
性能策略來(lái)優(yōu)化查詢而只能進(jìn)行表掃描遍歷每行。

上一篇：用SQL實(shí)現(xiàn)分布式數(shù)據(jù)復(fù)制

下一篇：從數(shù)據(jù)庫(kù)中動(dòng)態(tài)選取下拉列表的方法