数据库设计和优化

1、数据库设计和优化2001年6月中科院软件所软件工程技术中心金蓓弘对象模型和范式z范式是关系数据库设计的数学原则，它与使用的开发技术如面向对象设计、实体关系设计技术无关z范式提供了组织数据的方法z按面向对象方法设计出来的对象模型（逻辑数据库模型）也是一种组织数据的方法，应该能自然地满足范式的要求，也不需要太多的非正规化对象建模z表示y一个班最多只允许有20个学生y一个学校篮球队必须有至少8名，至多10名球员zER图不擅长表示基数关系z实现：用触发器，手工编写业务规则。StudentCourse Offeringn0.200.20nPlayerTeam18.1018.10逻辑数据库设计z用关系数据

2、库实现UML模型z正规化设计z非正规化设计z常用的非正规化技术用关系数据库实现UML模型z映射结构z映射功能映射结构之基本步骤z类映射成表z属性映射成列z有时，为生成型的标识符新增一列y用序列码做唯一标识符z映射关联关系z映射聚合和组合z映射继承关系映射关联关系：多对多z推荐的实现方法映射关联关系：一对多z推荐的实现方法映射关联关系：一对零/一z推荐的实现方法映射关联关系：一对零/一z另一种方法资金清算系统中的例子记帐凭证之间有1对0/1的关系，现设计成两个表，其中冲正记帐凭证关联表记录两个记帐凭证之间的关系（主键1，主键2）。不好的映射（1）不好的映射（2）不好的映射（3）映射聚合和组合z聚

3、合（aggregation）分成聚合（aggregate）和组合（composite）zOracle8用聚簇（包括索引组织表），嵌套表，VARRAY实现Movie DescriptorMovieKeywordnnnnMovieMovie Descriptorn1n1Keyword1n1n资金清算系统中的例子映射继承关系：推荐的方法映射继承关系：其他方法一映射继承关系：其他方法二映射继承关系：其他方法三映射功能之基本步骤z将在对象模型上的遍历表达式直接映射成SQL语句z在Oracle中有四种实现方式y构造方式y成员方式y排序方式y映射方式成员方式z成员方式是用户定义的存取路径，指明对一个指定的对

4、象类的数据存取和操纵方式z用PL/SQL过程或函数实现create or replace type person_t as object(person_ID number, last_name varchar2(50),first_name varchar2(50), birth_date date,)create or replace type body person_t ismember function age return number is begin return (sysdate - birth_date) /365; end;end;排序/映射方法z用于比较或排序同一对象类型的

5、数据，即在对象类型中定义排序或映射方法支持对象的不等值比较。z为对象类写方法（method）的方法： PL/SQL,C/C+zPL/SQL写的method在oracle服务器的地址空间运行，C/C+写的method在服务器的地址空间外运行z对计算量不大的method，用PL/SQL性能价格比最好。对计算量大的method，用C/C+逻辑数据库设计z用关系数据库实现UML模型z正规化设计z非正规化设计z常用的非正规化技术正规化(Normalization)设计z 范式y每种范式都包含上一个范式的规则，例如，满足3NF的关系一定满足2NF。z 第一范式y一行和一列位置包含的值不能超过一个（不包含任

6、何多值属性）z 第二范式y每个非键字段必须依赖整个主键，但不是复合主键的一部分（如果只依赖主键的一部分来确定信息，就违反了第二范式）z 第三范式y一个非键字段不能依赖另一个非键字段（即没有属性依赖于非键属性；数据库中不能存在传递依赖关系）违反第一范式的例子Number Date Item1 Qty1 Price1 Item2 Qty2 Price21001 1/1/2001 Ball 3 4.00 Bat 3 5.001002 2/2/2001 Ball 2 4.001003 3/3/2001 Glove 2 2.00 Ball 2 4.00这种表结构只允许定购两种货物。z 增定一种货物，要修

7、改表结构。z 查询某种货物被定购的次数，要查找多列。Third Normal Form (3NF) zA relation R is in 3NF if : whenever A1A2An-B is a nontrival dependency, either A1,A2,An is a superkey, or B is a member of some key.z如果一个表有多个键，不要求所有的键都在A1,A2,An中，只要一个即可。yBoyce-Codd Normal Form: 依赖关系仅存在于每个属性和整个主键之间。y用Boyce-Codd范式作为标准评价依赖性模式分解方法：例1z初

8、始模式：title, year, length, filmType, studioName, starNamez分析：ytitle, year, starName是主键y下列函数依赖不满足3NF条件xtitle, year- length, filmType, studioName,z分解成：ytitle, year, starName, title, year, length, filmType, studioName模式分解方法：例2z初始模式：title, year, length, filmType, studioName, studioAddrz分析：ytitle, year 是主键

9、xtitle year-length,fileType, studioNamey下列函数依赖不满足3NF条件xstudioName-studioAddrz分解成：ytitle, year, length, fileType, studioName, studioName, studioAddr模式分解方法：例3z初始模式：title, year, studioName, president, presAddrz分析：ytitle, year 是主键xtitle year- studioNamey下列函数依赖不满足3NF条件xstudioName-president, president-pre

10、sAddrz分解成：ytitle, year, studioName, studioName, president, president, presAddr正规化设计z按照函数依赖分解的关系，能通过连接（join）恢复成原来的关系z优点y减少数据库冗余，数据库小，所以I/O速度快y表更小，行更窄y提供物理的、一致的格式y数据只要修改一次，所以修改更快。z缺点y需要连接，查询处理慢（I/O多和CPU时间长）y不允许汇总或复制数据逻辑数据库设计z用关系数据库实现UML模型z正规化设计z非正规化设计z常用的非正规化技术非正规化(Denormalization)设计z有意违反正规化设计原则z提供性能y

11、需要了解数据如何被使用y用空间换时间，具体体现在：x用冗余的数据如汇总的数据减少连接数，减少外键个数，减少索引x要维护冗余的数据决定进行非正规化z数据项一起被访问的频率如何y四个或四个以上的表被连接，考虑非正规化z涉及多少行z数据修改的花费与非正规化获得的好处的比较维护冗余数据z破坏数据正规化原则，需要维护数据完整性。z数据完整性通过下两种方式维护y触发器y存储过程逻辑数据库设计z用关系数据库实现UML模型z正规化设计z非正规化设计z常用的非正规化技术非正规化技术z复制列z重定义列z重定义表z复制整个表复制列：精确复制z候选y从双亲表中选择不大可能改变或根本不可能改变的列z性能优点y通过删除连

12、接，减少I/Oy减少查询表竞争y不改变与列有关的数据类型，故增加维护数据完整性的操作复制列：精确复制例子StoresPKStor_idStor_address7066中关村南二街1号Sales（原始表）PK,FKPKStor_idOrd_num700612345Sales（非正规化后）PK,FKPKStor_idOrd_numStor_addressv706612345中关村南二街1号复制列：派生列z候选y行数y总计另一列上n行的值y一个或多个列上的计算z考虑y如果包含派生列的列改变，派生列也要改变z性能优点y减少多表访问y预先执行计算操作，提高查询性能复制列：派生列例子z通常，在外键所在的表

13、上计算出总值，然后写入主键所在的表上的总计。TitlesPKTitle_idtitleYtd_salesMC2222编译原理15PC8888数据结构150SalesPKFKStor_idOrd_numTitle_idQty7066A233PC8888507131P333PC88881008042FJJJ3MC222215非正规化技术z复制列z重定义列z重定义表z复制整个表重定义列：虚键列z候选y键的大小超过8字节（4字节为可选）y多列主键z考虑y避免使用长主键y用虚键列一致性代替所有外键一致性z性能优点y虚键上的索引变得更小y查询优化器上有更精确有效的统计数据重定义列：虚键列例子z虚键列pro

14、p_id用于property list表中的主键和Rentals表中的外键，Rentals表可被缩小。Propety listPKProp_addressRental_price123 Main St.90065 First Ave.650RentalsPKProp_addressnamedate123 Main St.J. Smith7/8/01123 Main St.M. Jones8/7/0065 First Ave.F. Williams9/8/00Propety list PKProp_idProp_addressRental_price1123 Main St.900265 Fir

15、st Ave.650RentalsPK,FKProd_idnamedate1J. Smith7/8/011M. Jones8/7/002F. Williams9/8/00非正规化技术z复制列z重定义列z重定义表z复制整个表重定义表：按列分割表z候选y不经常访问的列yOLTP数据要求和决策支持数据要求之间有冲突z考虑y确保被分割成独立表的数据不经常使用，否则，需要连接将取消性能优点。z优点y通过列分割数据可减少锁定次数和对数据的竞争重定义表：按行分割表z候选y用户需要数据的不同子集y包含大量的行，但并没有可用的索引z考虑y查询策略若是用表扫描，那么通过行分割可提高性能，但如果通过索引访问列，行分

16、割提高的性能就很少了。非正规化技术z复制列z重定义列z重定义表z复制整个表非正规化技术z复制列z重定义列z重定义表z复制整个表物理数据库的设计/Orale8z如果属性可以被限制在几个值中，那么不用check constraint（写一段程序去检查属性值是否属于指定的几个值），而用value list classz除了共有的状态，还有某个对象可能有额外的状态，那么重载value list classy用两个或多个value list class，如果它们的值有重叠，那么意味着同一个值被存放在两个不同的地方z若在两个类之间存在多个关系，将关系转换为类j.relationshiptype1n1ndepartmentprojectn1n1financially controlled byn1n1administratively controlled byz用递归的Value lis