关系型数据库逻辑设计规范研究

陈英达， 黄巨涛， 林强， 唐亮亮

(广东电网有限责任公司信息中心，广东 广州 510080)

0 引言

在信息系统开发中普遍采用Oracle、MySQL等关系型数据库系统，而数据库的逻辑设计好坏影响着数据库及其应用系统的整体性能，决定了数据的完整性、准确性和一致性能否得到保证。如果数据库逻辑设计不合理，那么数据库的调优对于数据库的性能提升将十分有限。因此我们对数据库的逻辑设计规范开展研究，通过对数据库逻辑设计提出相应的要求与建议。遵照本文所制定的数据库逻辑设计规范可以减少数据库中数据冗余、提高数据库存储效率[1]。对数据定义恰当的约束条件，并能提升SQL语句执行效率，对信息系统的长期稳定运行具有重要的价值。

1 数据完整性设计规范

数据完整性需要使用不同的完整性约束条件来制约，数据设计的完整性约束主要包含以下四方面：域的完整性、实体完整性、参照完整性以及用户定义完整性，其含义分别如下。

(1) 域的完整性：数据库表中的字段必须满足某种特定的数据类型或约束。约束包括取值范围、精度等规定；

(2) 实体完整性：表中行主键的约束；

(3) 参照完整性：属于表间规则，用于设计表间数据的完整性；

(4) 用户定义完整性：对数据表中字段属性的约束。

数据完整性设计规范有如下要求。

(1) 每张表必须定义主键；

(2) 某一字段如果为另一表的主键，则该字段应定义为外键；

(3) 表中字段的类型定义必须与其实际含义和可能的值匹配；

(4) 对表中的字段应根据实际使用要求对字段属性进行约束，如非空、唯一性约束等；

(5) 如某一字段值需满足特定要求，例如有特定的取值范围等，则应为其定义合适的约束条件。

2 表逻辑设计规范

依托大量的数据库分析与设计实践，总结出表逻辑设计的规范如下：

(1) 应采用第三范式(3NF)的设计方法，最大化保证数据的完整性[2]。

(2) Oracle数据库中，对周期性插入大批量数据操作的表或对周期性建立的大表(数据文件大于300M)，INITIAL EXTENT应设置成首次操作数据量的大小。具体如下。

建议把INITIAL EXTENT设置成等于NEXT EXTENT的大小；

表的EXTENTS次数尽量不超过200次；

对于不发生变化的表：PCTUSED设置为90，PCTFREE设置为5；

对于并非频繁变化的表：PCTUSED设置为80，PCTFREE设置为5；

对于经常变化的表(插入与更新操作均较频繁)：PCTUSED设置为60，PCTFREE设置为20；

对于仅插入操作频繁但不经常修改原有记录的表：PCTUSED设置为80，PCTFREE设置为10；

对于更新操作频繁的表：PCTUSED设置为50，PCTFREE设置为40；

对于DB_BLOCK_SIZE>=8192的OLTP(联机事务处理过程)数据库信息系统，表及索引应设定为INITRANS>=4，MAXTRANS>=10。

(3) 对于频繁使用的表(如字典表)，在内存空闲空间较多且数据量不大(如不超1000行)时，可采用缓存的方式保存，提升表的使用性能。

(4) 创建表时需指定到相应的数据表空间，确保不同数据存储在不同的表空间。

(5) 主键字段个数不能过多，尽量不修改主键值。

(6) 在数据库中实现数据完整性的校验，避免在应用中对数据进行完整性校验。

(7) 避免使用字符类型存放时间或日期类数据。

(8) 避免使用字符类型存放数值类型的数据。

(9) 避免表中字段数值类型直接使用INT型，应明确写明字段的取值范围，如NUMBER(8)。

(10) 应尽量减少使用大字段，如BLOB，CLOB，LONG，TEXT与IMAGE等。

3 表分区设计规范

随着表中数据量不断增大，数据查询的速度会逐渐降低，从而导致应用系统的性能下降，此时应考虑对表进行分区操作。

表分区具有以下优点。

(1) 提升查询性能：分区对象的查询可以仅查询与自己相关的分区，不用再对整表进行查询，从而提高查询效率；

(2) 减少故障损失：表的某个分区发生故障时不会影响该表其他分区的正常使用；

(3) 维护效率高：只需对故障分区进行修复，不用对整表修复；

表分区设计的规范与建议如下。

(4) 应对有需要的大表进行分区，以提高性能和可维护性；

(5) 当表的大小接近或超过4GB时，可考虑对其进行分区；

(6) 对于OLTP系统，当表的数据量非常庞大时，应考虑对表进行分区，对于硬件性能较好的服务器，可适度放宽要求；

(7) 对经常执行并行操作的表，建议对其进行分区。

数据库分区表的基本类型可分如下4类：

范围分区。范围分区以表中分区字段的值的范围来作为分区的划分条件。不同的记录将按照分区字段的值的不同，存放在对应在的范围分区中。该分区方式最适用于查询条件中对分区键值进行区间查询的场景；

哈希分区。哈希分区将表中数据的存放位置依据分区字段的值进行特定的哈希计算后得到的结果来决定，该分区方式最适用于查询条件中对分区字段采用等号进行比较的情况[3]；

列表分区。与范围分区不同，列表分区必须指定分区字段的具体值而不仅仅是一个范围。该分区的使用范围比范围分区和哈希分区小；

组合分区。通过在不同字段上，使用“范围分区”、 “哈希分区”以及“列表分区”的不同组合方式，实现组合分区。该分区适用于数据量大的表以及对性能具有特殊要求的情况。

4 字段逻辑设计规范

对表中字段的逻辑设计规范如下。

(1) 应对通信地址等特定的信息采用多个字段来表示，增加灵活性；

(2) 应使用角色实体来定义关联属性，方便创建时间关联关系；

(3) 数字类型与文本类型的字段长度应保证充足；

(4) 对删除记录的操作需用统一的特定字段标注，而非直接删除记录；

(5) 尽量避免使用大字段。

5 索引设计规范

在设计关系型系统的数据库时，通常需要创建大量索引，良好的索引可以加快表与表之间的连接[4]，通常能显著减少复杂的SQL查询所花费的时间，从而提升数据库系统的性能[5]。

创建索引应遵循如下规范。

(1) 不要索引大型字符字段；

(2) 不要索引常用的小型表；

(3) DML操作频繁的表应尽量少建索引；

(4) 尽量不要将经常修改的字段作为索引字段；

(5) 选择性高的字段适合建立索引；

(6) 如某一字段或字段组合经常在WHERE子句中使用并且满足该字段或字段组合查询条件的行数占表总行数的比例小于等于5%时，适合创建索引；

(7) 应在频繁使用DISTINCT关键字查询的字段上建立索引；

(8) 进行表连接时，应在连接字段上建立索引；

(9) 复合索引创建时应把最常用的字段放在第一位，而将不常用的字段排后；

(10) 当索引字段的记录重复较多而DISTINCT记录值又较少(一般少于30)时，应建立位图索引；

(11) 尽量避免对OLTP系统的一张数据库表创建过多索引，例如超过10个；

(12) 由于位图索引会影响DML操作的速度，因此OLTP系统中尽量不使用位图索引；

(13) 如查询中需使用函数，且满足该查询条件的记录数比例很小，建议创建相应的函数索引；

创建索引完毕后，正确使用索引才能使其发挥作用，使用索引的规范如下。

(1) 确认是否已使用索引。应尽量使用选择率高的索引，避免全表扫描；

(2) 确认已使用的索引是否合理。错误的索引将导致性能的降低。索引的合理使用规范如下：

应避免在字段上进行类型转换操作，否则无法使用该字段上的索引；

避免对索引字段进行任何计算操作，对索引字段的计算操作会引起索引的失效；

尽可能增加查询的条件，限制全范围的查询

当索引效率很低时，应避免使用索引；

尽量避免模糊查询，如必须使用模糊查询时，尽量使用前端匹配的模糊查询；

WHERE条件中对索引字段尽量使用等值“=”进行比较查询；

尽量使用前导字段(复合索引的首字段)作为查询条件；

WHERE条件中对索引字段的查询条件应保证比较值的类型和字段类型一致。

索引在创建和维护过程中需注意如下事项。

(1) 创建索引时数据和索引应放在不同的表空间；

(2) 创建分区表索引时，尽量创建本地索引；

(3) 对于经常执行删除操作的表上的索引应定期重建索引。由于索引重建时会阻塞DML操作，应选择在业务空闲时进行，尽量减少其对业务产生影响；

(4) 对于键值频繁更新的索引，也应定期进行重建；

(5) 删除无用的索引，避免多余索引降低数据库DML操作的执行速度。

6 总结

关系型数据库是信息应用系统的核心组成部分，不合理的关系型数据库设计会加大编程难度，引起操作繁琐、性能降低、空间浪费等不良后果，甚至影响应用系统的安全与稳定，合理设计关系型数据库十分有必要。而逻辑结构的设计是关系型数据库设计过程中的重要环节，逻辑结构设计的好坏直接决定并影响了数据的完整性、准确性与一致性，因此在关系型数据库逻辑设计过程中研究并提出相应的规范具有重大价值。

[1] 陶勇，丁维明. 数据库中规范化与反规范化设计的比较与分析[J]. 计算机技术与发展, 2006,16(4):107-109.

[2] 丁智斌，石浩磊. 关系数据库设计与规范化[J]. 计算机与数字工程, 2005,33(2):114-116.

[3] 韦平飞. 移动业务运营支撑系统数据库性能优化的研究[D]. 广州:华南理工大学, 2011.

[4] 王力等. 基于免疫遗传算法的关系型数据库查询优化技术[J]. 微型电脑应用, 2008,24(3):45-47.

[5] 张若唯. 基于ORACLE的炼钢—连铸综合优化系统信息平台的设计与实现[D]. 沈阳:东北大学, 2012.