设计数据库之经验谈
程序员文章站
2022-04-19 16:56:53
...
数据库设计经验谈
数据库模型的设计是否合理会极大影响系统的使用性能。笔者依据多年来设计和使用数据库的经验，提出以下一些设计原则，供同仁们参考。
慎用游标（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）较大时。所以对索引较多的表进行频繁的插入、更新、删除操作时，建表和索引时应设置较小的填充因子，以便在各数据页中留下较多的*空间，减少页分割及重新组织的工作。
数据库模型的设计是否合理会极大影响系统的使用性能。笔者依据多年来设计和使用数据库的经验，提出以下一些设计原则，供同仁们参考。
慎用游标（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）较大时。所以对索引较多的表进行频繁的插入、更新、删除操作时，建表和索引时应设置较小的填充因子，以便在各数据页中留下较多的*空间，减少页分割及重新组织的工作。