数据库设计指南.doc

数据库设计指南版本所有：珠海政采软件技术有限公司保密级别：普通 保密 机密文件编号： 数据库设计指南版本号 1.0珠海政采软件技术有限公司修订历史版本发布日期生效日期修订说明作者审核人批准人当前状态1.02009-8-19新建草稿目 录1.引言41.1.目的41.2.适用范围41.2.1.适用部门41.2.2.适用业务41.3.概述41.4.术语和缩略语42.面向对象的数据库设计42.1.主键42.1.1.OID不应具有商业意义42.1.2.OID的唯一性42.1.3.分配OID的策略42.2.对象映射52.2.1.属性映射成域62.2.2.类映射成表62.2.3.泛化映射62.2.4.将整个类层次映射为单个数据库表。62.2.5.每个具体子类映射成单个数据库表。72.2.6.每个类均映射为数据库表。72.2.7.关系映射72.2.8.可选的1对强制的173.优化SQL语句以提高性能83.1.提高SQL语句的可读性83.2.避免扫描整个表83.3.新增加一个索引93.4.查询中各成分的排列93.5.避免使用OR94.数据库的跨平台设计94.1.不使用存储过程94.2.不使用连接（JOIN）104.3.ANSI SQL105.相关文档106.参考资料111. 引言1.1. 目的描述怎样在数据库设计中提高查询性能和减少系统移植的复杂性。1.2. 适用范围1.2.1. 适用部门产品管理部、ERP软件部、基础软件部、BI软件部、平台及定制支持部、信息集成部、测试部。1.2.2. 适用业务数据库设计活动。1.3. 概述无1.4. 术语和缩略语术语/缩略语解释无2. 面向对象的数据库设计2.1. 主键针对于对象，需要能够唯一识别它们的标识符。在关系数据库中，对应的概念称之为键（Key）；在面向对象的技术中，称之为OID（Object ID）。OID在对象模型中的典型实现是作为完整的对象，而在关系模型中，则作为整数来实现，或者对于较大的应用中，以若干整数来实现。使用OID在关系数据库中唯一的标识对象。OID简化了关系数据库的主键方案；并易于维护对象间关系的位置。2.1.1. OID不应具有商业意义OID在任何情况下，都不应包含商业内涵。存在商业意义的任何列都有潜在变化的可能。而在关系数据库中，采用有意义的主键是致命的错误。如果用户决定改变字段的商业含义，则需要在所有使用到该信息的地方进行修改。主键的作用应是保持唯一性和作为外键使用。任何对主键的修改会导致巨大的数据库维护工作量，显然这是不合适宜的设计。就关系数据库而言，OID策略采用的是代理主键的方法。2.1.2. OID的唯一性所有对象标识应具有唯一性。2.1.3. 分配OID的策略分配OID时，可以使用下面几种策略。GUID/UUID许多年前，Digital使用了一种称之为UUID的策略。该策略基于哈希计算机以太网卡的物理标识和当前时间来得到唯一的128位键值。而对于无以太网卡的计算机，则可以通过在线的文件得到标识数字。Microsoft具有类似的GUID策略来得到128位的字符串。使用Persistence机制提供的功能许多数据库，如Oracle，能自动的产生唯一的序列值。尽管该方案可以很好的工作，但它们采用了厂商私有的方法，且在定义时确定，从而无法进行有效地控制。如果面临跨平台移植时，可能成为非常严重的问题。HIGH/LOW方法替代使用较大整数来获取OID的方法（要求对单个资源字段进行访问，从而成为瓶颈）：将OID分解为两个逻辑组成部分。在应用程序首次需要创建OID时，向数据库的单个字段请求HIGH值（或者从某些数据的内建函数获得），对于LOW值，初始化为0，在本次会话随后的请求递增。如果LOW值到达了极限，则再次向数据库申请HIGH值。由于HIGH值互斥的获得，进而保证了唯一性。HIGH/LOW方法的优点，每次会话只需与数据库交互一次，减少了流量，使键值表的访问不再成为瓶颈。其次，保证了OID在所有对象中的唯一性。与前面所提到的方法比较，HIGH/LOW方法是最有效和实现较简易的方法。HIGH/LOW方法的实现HIGH/LOW OID的一种可能实现的类示意图下图显示了实现OID的一种法方法。其基本思想是在创建永久对象：由ObjectFactory对象（可参见创建设计模式中的Singleton）为它分配OID，ObjectFactory的唯一职责创建新的OID对象。ObjectFactory跟踪HIGH和LOW的取值来得到新的OID。具体而言，通过访问Persistence机制（数据库）来获取HIGH，而基于LOW的当前值返回唯一的OID。asColumns方法则以关系数据库的存储的对应形式返回OID的实例。高位（HIGH）采用96位的数字（通常是3个32位的整数），低位（LOW）采用32位的数字。接着，将其转换成128位的字符串。2.2. 对象映射由于 RDBMS 是以二维表为基本管理单元的,所以对象模型最终是由二维表及表间关系来描述的。换言之,对象模型向数据库概念模型的映射就是向数据库表的变换过程。有关的变换规则简单归纳如下：(1) 一个对象类可以映射为一个以上的库表，当类间有一对多的关系时，一个表也可以对应多个类。(2) 关系(一对一、一对多、多对多以及三项关系)的映射可能有多种情况，但一般映射为一个表，也可以在对象类表间定义相应的外键。(3) 单一继承的泛化关系可以对超类、子类分别映射表，也可以不定义父类表而让子类表拥有父类属性；反之，也可以不定义子类表而让父类表拥有全部子类属性。(4) 对多重继承的超类和子类分别映射表，对多次多重继承的泛化关系也映射一个表。(5) 对映射后的库表进行冗余控制调整,使其达到合理的关系范式。2.2.1. 属性映射成域UML中的属性类型（Attribute Type）映射成数据库中的域（Domain）。域的使用提高了设计的一致性，且优化了应用的移植性。简单的域是非常容易实现的，仅仅需要替换相对应的数据类型和数据的尺寸。同时，对于使用域的属性，可能要求为域的约束加入SQL的Check串。例如，限定域的取值范围等。枚举域（Enumeration Domain）限定了域允许取值的集合。枚举域较简单域实现更复杂。下表显示了四个方法。实现方法优点缺点推荐枚举字符。定义一条SQL检查约束，把该枚举限制在允许的值里。简单。受控的方便搜索的词汇表。大的枚举难以使用检查。约束难以编码。我们正常地选择。每个枚举值一个标记。为每个枚举的值定义一个布尔型属性。回避命名的难处。冗长 每个值一个属性。当枚举值不是互相排斥的并且多个值可能同时地应用时使用。枚举表。把枚举定义存储到一个表里。不是每个枚举一个表，也不是所有的枚举一个表。高效地处理大的枚举。不用改变应用的代码就可以定义新的枚举值偶尔使用时很麻烦。必须编写通用的软件来阅读枚举表和加强值。适合大的枚举和没有结尾（open-ended）的枚举。枚举编码。把枚举值编码作为有序的数字。节省磁盘空间。有助于用多种语言处理。大大地复杂化了维护和调试。避免使用，除非你要用多种语言处理。并不是类中的所有属性均是永久的。例如，发票（Invoice）中的合计（grandTotal）属性可能是用于计算而不需保存在数据库中。另外，有时某个对象包含其它对象，如顾客（Customer）中的Address属性（Address本身映射为数据库表）。此时，属性映射成多个字段。2.2.2. 类映射成表正常情况下，我们把每个类映射为一个表，每个属性映射为一个列。你可能因一个已产生的标识符（OID）、隐藏的关联和通用鉴别器需要一些另外的列。2.2.3. 泛化映射可以根据不同需要，选择下面三种方法之一：2.2.4. 将整个类层次映射为单个数据库表。类层次的所有类映射为单个的数据库表，表中保存所有类（基类、子类）的属性。优点：实现简单。支持多态对象角色发生变化，或存在多重角色时。报表操作实现简单：表中包含了所有信息。缺点：增加类层次中的耦合。类层次中任何类的属性的增加会导致表的变更；如果在某个子类属性的修改错误会影响到整个层次结构，而不仅仅是该子类。浪费了大量的数据库空间。可能需要指明具体的角色。2.2.5. 每个具体子类映射成单个数据库表。数据库表包括自身的属性和继承的属性，每个具体的子类包含各自的OID。抽象的基类不参与映射。优点：报表操作实现简单：表中包含了具体子类的所有信息。缺点：类的修改会导致相对应的表及其子类所对应表的更改。角色的更改会造成ID的重新赋值（因为不同子类的ID可能重复）。难以在支持多重角色时，保持数据的完整性。2.2.6. 每个类均映射为数据库表。为每一个类创建数据库表，表中包含特定于该类的属性和OID。优点：与面向对象的概念的一致性最好。对多态的支持最好，对于对象所可能的充当的角色仅需要在相应的表中保存记录。易于修改基类和增加新的类。缺点：数据库中存在大量的表。访问数据的时间较长。对报表的支持较差，除非定义视图。2.2.7. 关系映射关系数据库中通过使用外键来实现关系。外键允许将表中的某一行与其它表中的行相关联。实现一对一或一对多关系，仅仅需要在表中加入另一个表的主键。2.2.8. 可选的1对强制的1将外键放置在可选的一端，该外键不能为空值。例如，某公司员工使用电脑的情况，要求员工最多能使用一台电脑，且电脑资源应充分的利用。下图中，Computer中放置外键。可选1对强制1关系的实现其它1对1的情况外键可放置在任意一边，具体情况依赖于性能等因素。例子如下图。注：对于1对1的情况，不要在两个表中均放置对方的主键。这样，增加了冗余，且并不会提高性能。对于强制性，一般在商业规则的对应层实现，而不在Persistent Layer中实现。1对1关系的实现1对多的情况将外键放置在“多”的一方。外键的空/非空由对1的强制性决定。示意图如下图。1对多关系的实现多对多的情况使用关联表实现多对多关系。将关联表视为普通表，使用自身的主键OID，然后加入实现关系所必需的外键。Customer和Account之间存在多对多的关系。下图显示了如何在关系数据库中实现多对多的关系。多对多关系的类关系数据库中多对多关系的实现3. 优化SQL语句以提高性能3.1. 提高SQL语句的可读性即时可读性不影响SQL语句的执行效率，但是使程序代码具有可读性是一个良好的习惯。如果在where子句中有多个条件，可读性就十分重要了。3.2. 避免扫描整个表可以根据SQL语句的Where子句中的列建立索引，从而避免扫描整个表。索引最好用于大表。当设计索引时，一定要考虑索引的大小。建立合适的索引还需要对数据十分了解，因该知道那些数据列最常被查询，也许还需要经过实践以了解哪些索引工作的最好。缺省情况下建立的索引是非集簇索引，但有时它并不是最佳的。在非集簇索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：.有大量重复值、且经常有范围查询（between,=,=）和orderby、groupby发生的列，可考虑建立集簇索引；.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就要做相应的更新工作。任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。3.3. 新增加一个索引当数据的检索条件发生变化时，经常会发生某些SQL语句的执行时间长的惊人。可能的原因是where子句中的新条件没有索引。观察SQL语句中的WHERE子句中，如果是下列情况，我们需要增加索引： 最严格限定条件返回的数据郎书少于表中数据总行数的10% 最严格限定条件在SQL语句中经常被使用 建立在索引列上的条件返回唯一值 列经常被Order by和group by子句引用也可以使用符合索引。如果where子句中的各索引列经常一起作为条件使用，复合索引比单列索引更有效。3.4. 查询中各成分的排列查询语句中，特别是在WHERE子句中，各组成部分的最佳排列顺序依赖于特定实现的处理顺序。条件的安排顺序依赖于被索引的列，同时也依赖于那个条件检索记录最少。尽管使用WHERE子句的索引列不是必须的，但使用索引列的优点是显而易见的。使用返回最少记录数的索引可以限定SQL语句的返回结果。使用记录数最少的表最为查询的基本表。3.5. 避免使用OR在查询中，引尽量避免使用逻辑操作符OR。OR运算不可避免的降低任何查询的效率。尽管很多数据库管理系统宣称查询优化器将IN运算转化为OR运算，但我们发现IN运算一般比OR运算快得多。4. 数据库的跨平台设计使用那些非标准的SQL命令（比如Oracle、微软和MySQL等数据库系统）存在程度相当高的危险。也就是说，从跨平台和遵守标准的角度出发，你应该尽量采用ANSI SQL，它是一种和平台无关的数据库语言。不管你在使用哪种数据库系统，如果它完全支持SQL那么它就应该支持ANSI SQL-92标准。可是，“应该支持”和“确实支持”完全是两码事。在进行DBMS跨平台编程的时候，并不是所有的系统都完全支持ANSI SQL标准。4.1. 不使用存储过程在某些编程环境下，出于效率和安全等方面的原因，存储过程成为程序员开发数据库应用程序的唯一方法。Visual Basic、C和Java程序员都并不需要了解SQL。然而，在其他某些编程环境下，存储过程又是完全禁用的。MySQL通常和Apache Web服务器组合使用，这是因为这两种软件不仅免费、可靠而且功能强大，但是，恰恰是MySQL不支持存储过程。Oracle和SQL服务器两者都包括了“内建”的存储过程，其使用也是很方便的。可是，如果明智的话千万别指望它们，你无法保证数据库管理员是否锁定、删除或修改了这些存储过程。如果你发现可用的某些存储过程正是你需要的，你这才可以在自己的代码中使用它们的功能。通常，你能在Oracle或SQL Server上使用存储过程但在MySQL上则无法利用。如果你确实用到了它们，那么你得保证自己没有用到同特定平台相关的命令。4.2. 不使用连接（JOIN）连接是令人头痛的一个问题，因为在不同DBMS上连接的使用语法不同，甚至在有的系统上连接的概念上也是根本不同的。基本上，各个DBMS的连接工作原理不总是一样的，而且你最终可能会得到意外的结果集合。在我们谈论各个系统有关连接的概念差别以及如何解决这些问题之前，你应该理解基本的连接子句：连接Join（连接）是从多个数据源获取数据的SQL数据表创建命令。 等连接Equi-Join从两个独立的数据源获取数据并把它们组合成一个大表。 内/外连接内连接（Inner Join）把两个表的内部列连接起来。外连接把两个表的外部列连接起来。 左/右连接左