设计规范

一般规范

• 任何表的设计都要考虑到数据的删除策略，表中的数据不能无止境的增长而不删除。
• 严禁将业务数据放在系统表空间中，必须有独立的表空间存放业务数据。
• 索引数据和表数据要分开存储，放在不同的表空间中。
• 在不同业务系统共用同一个数据库时，要注意不同业务系统的对象必须存放在不同的用户下面，绝不能混放在同一用户下。
• 除非基于特殊情况考虑，通常情况下每个表都要有主键。
• 表的设计要尽量满足第二范式（2NF），基于提升性能的考虑可以适当增加冗余而不必满足第三范式（3NF）。

  说明：

  • 第一范式 1NF

    定义：表中每一条记录的每个一个字段值，都是不可再分的最小数据单位。

    解释：例如工号，姓名，电话号码组成一个表，由于一个人可能有办公电话和家庭电话，因此该表不符合1NF，规范成为1NF有三种方法：

    1. 重复存储工号和姓名，主键只能是电话号码；
    2. 工号为主键，把电话号码分解为“单位电话”和“住宅电话”两个属性；
    3. 工号为主键，但强制每条记录只能有一个电话号码。

    以上三个方法，第一种方法最不可取，按实际情况选取后两种情况。

  • 第二范式 2NF

    定义：在满足1NF的基础上，每一个非主键字段必须完全依赖于主键。只有在复合字段作主键时，才可能出现不满足2NF的情况。

    解释：例如学号，课程号，学分，成绩组成一个表，主键为学号，课程号。 在应用中使用该表时可能存在以下问题：

    1. 数据冗余：假设同一门课由40个学生选修，学分字段就重复40次；
    2. 更新异常：若调整了某课程的学分，学分字段的值都要更新，有可能会出现同一门课学分不同；
    3. 插入异常：如果开设新的课程，由于还没人选修（主键中少了学号），只能等有人选修才能把新开设的课程和学分存入；
    4. 删除异常：若学生已经结业，从当前数据库删除选修记录。某些门课程新生尚未选修，则此门课程及学分记录无法保留。

    存在以上问题的原因是，非主键字段“学分”仅“部分依赖”于主键（学号，课程号），也就是“学分”字段仅函数依赖于“课程号”字段。该表存在部分依赖的字段“学分”。

    解决方法：采用纵向分表，将部分依赖的字段抽出来建立一个新表，该表可分解为（学号，课程号，成绩）和（课程号，学分）两个表，两个表之间通过“课程号”作为外键关联。

  • 第三范式 3NF

    定义：在满足1NF和2NF的基础上，所有非主键字段对任何主键字段都不存在传递依赖。

    解释：例如学号，学生姓名，系号，系名，系地址组成一个表，主键为学号，由于主键是单个字段，因此没有部分依赖的问题，肯定满足2NF。但是，在应用中使用该表时可能存在大量的冗余，有关学生所在的几个字段（系号，系名，系地址）将会重复重复存储。

    原因是，存在传递依赖而造成，“学号”能够决定“系号”，“系号”能够决定“系地址”，“学号”不能够直接决定“系地址”，因此“学号”对“系地址”的函数决定是通过传递依赖“系号->系地址”实现的。

    解决方法：采用纵向分表，将存在传递依赖的字段抽出来组成新表，该表可分为（学号，学生姓名，系号）和（系号，系名，系地址）两个表，两个表之间通过“系号”关联。

• 不建议表中存储过多的null值，要考虑使用not null约束。或者，可以考虑字符串使用NA，数值型用0作为缺省值。
• 尽量不要使用复杂视图。

  说明：

  简单视图：数据来自单个表，且无分组（DISTINCT/GROUP BY）、无函数。

  复杂视图：数据来自多个表，或有分组，有函数。

  复杂视图不好优化，易产生性能问题。

• 对于大数据量的插入，在设计上应考虑使用分区交换技术。
• 在数据库中实现数据完整性校验，不推荐在应用中完成对数据完整性校验。
• 不推荐用字符类型存放时间或日期类数据。
• 不推荐用字符类型存放数字类型的数据。
• 要区分近期记录和历史记录，不能把所有记录放都放到一个表中，要有历史表，要有定期删除历史表记录的功能。
• 尽量避免使用大字段（LOB）或者超长字段（varchar > 1000），如果不可避免，则尽量不要在较长字符串的字段上建立索引；尽量避免对大字段值进行order by、distinct、group by、union等会引起排序的操作；尽量避免频繁的查询与修改大字段。

表空间设计原则

• 用户表空间与系统表空间分离。
• 数据表空间与索引表空间分离。
• 业务表空间与日志表空间分离。
• OLTP系统最好不要使用BIG FILE TABLESPACE。
• 小型数据库数据文件统一使用8GB；中型数据库数据文件统一使用16GB；大型数据库数据文件统一使用24GB，不允许使用数据文件自动扩展。
• 用户新建立的表空间必须采用本地管理和自动段空间管理。
• 没有必要频繁的整理表空间中的碎片，除非碎片率达到了80%以上。
• 如果表空间因频繁的DDL操作出现碎片，建议设置表空间的UNIFORM SIZE为合适的数值（例如1MB），来减少碎片的产生。

索引设计原则

• 表的主键、外键必须有索引；
• 经常查询且选择率低于5%的列需要建立索引；
• 经常与其他表进行连接的表，在连接字段上应该建立索引；
• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
• 位图索引适合于DSS或者OLAP，不应该在OLTP的系统中或者DML操作较频繁的列上建立位图索引；
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
• 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
  • 正确选择复合索引中的主列字段，一般是选择性较好的字段；
  • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
  • 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
  • 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
  • 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
• 频繁进行数据操作的表，不要建立太多的索引；
• 删除无用的索引，避免对执行计划造成负面影响；
• 分区表上尽量使用本地索引，否则在分区维护的时候必须重建索引；
• 可以适当的使用函数索引来完成特殊的优化。

分区设计原则

• 范围分区和interval分区适合于划分历史类数据、周期性的加载和删除数据，通常是以DATE类型的列作为范围分区键。interval分区是范围分区的延伸，以时间为单位自动扩展分区。
• HASH分区适合于随机分布数据、通过HASH算法来避免热块竞争、没有明显的分区规则，通常选择唯一或者基本唯一的列作为HASH分区键，分区的数目必须是2的幂次方。
• 列表分区适合于离散数据，不支持多个列同时作为分区键。
• 复合分区同时具备2维分区的优点，分为：Range-Hash、Range-List、Range-Range、List-Hash、List-List、List-Range。
• Intervel分区适合于固定间隔的范围分区，当到插入数据到不存在的分区时，数据库会自动创建新分区，自动产生分区名，Oracle不允许手工增加新分区。由于这种分区的分区名称是自动生成的，且前期出现的BUG较多，并不推荐大规模使用。
• 分区表上尽量使用本地索引，在检索的时候where条件中尽量带上分区键，通过分区键缩小检索的范围。
• 合理的设计分区表的表空间，通过表空间和LUN可以在物理层面上隔离数据，提高并行度，降低资源竞争。