4-数据库物理设计解析

4数据库物理设计,4物理数据库设计,物理数据库设计步骤3：为目标DBMS转换全局逻辑数据模型步骤3.1：设计基本表步骤3.2：设计派生数据的表示步骤3.3：设计其他的业务规则步骤4：选择文件组织方式和索引步骤4.1：分析事务步骤4.2：选择文件组织方式步骤4.3：选择索引步骤5：设计用户视图步骤6：设计安全性机制步骤7：引入受控冗余的考虑步骤8：监视并调整操作系统,逻辑与物理数据库设计的比较,逻辑数据库设计独立于实现细节,例如独立于目标DBMS的具体功能逻辑数据库设计关心的是“什么”,而物理数据库设计关心的是“怎么”逻辑数据模型和数据字典是物理设计的信息源,物理数据库设计,产生二级存储上的数据库实现的描述的过程。它描述了基本表、文件组织方式和用于实现数据有效访问的索引以及任何相关的完整性约束和安全限制,物理数据库设计方法学概述,步骤3为目标DBMS转换全局逻辑数据模型步骤4选择文件组织方式和索引步骤5设计用户视图步骤6设计安全机制步骤7考虑引入受控冗余步骤8监控并调整操作系统,步骤3为目标DBMS转换全局逻辑数据模型,从逻辑数据模型产生基本的工作关系数据库由下列步骤组成:Step3.1设计基本表Step3.2设计派生数据的表示Step3.3设计其他业务规则,步骤3为目标DBMS转换全局逻辑数据模型,需要非常了解目标DBMS所提供的有关功能的知识，例如应该知道怎样创建基本表，以及DBMS是否支持如下定义：是否支持主码、外码和备用码是否允许某列被定义为NOTNULL是否支持域定义是否支持关系完整性规则是否支持业务规则定义,步骤3.1设计基本表,确定如何在目标DBMS中描述逻辑数据模型所标识的基本表需要比较和吸收在逻辑数据库设计阶段创建的表的信息,步骤3.1设计基本表,对每个表,需要定义：表名括号内的简单列名表主码以及在适当的地方的备用码和外码任何标识出的外键的参照完整性约束应该对每个列有如下定义：它的域，包括数据类型、长度和域上的约束每个列设置可选的默认值该列是否可以为空,使用扩展DBDL的Branch表物理设计,步骤3.2设计其他业务规则,为目标DBMS设计其他业务规则.有些DBMS比其他系统提供了更多的定义业务规则的功能.例如:CONSTRAINTmember_not_renting_too_manyCHECK(NOTEXISTS(SELECTmemberNoFROMRentalAgreementGROUPBYmemberNoHAVINGCOUNT(*)10)或使用触发器,步骤4选择文件组织和索引,确定最佳文件组织方式来存储基本表以及实现所要求性能的索引.由下列步骤组成:步骤4.1分析事务步骤4.2选择文件组织方式步骤4.3选择索引,步骤4.1分析事务,理解运行在数据库上的事务的功能并分析重要的事务.标识出性能标准,例如:经常运行的事务和对性能产生重大影响的事务业务操作的关键事务当对数据库有很高要求时，每日/每周内访问数据库的次数(最大负荷)使用这些信息来标识可能会引起性能问题的数据库部分，同时，标识在更新事务中更新的列，以及在查询中作为选择条件的列根据以上信息选择正确的文件组织和索引,步骤4.1分析事务,通常不可能分析所有预期事务，因此只研究最重要的那些事务.80/20规则是有效的为了帮助标识要研究的那些事务，可以使用:事务/表交叉引用矩阵，显示每个事务访问的表事务应用图,表明哪些表潜在的可能被多次使用,交叉引用的事务和表,显示预期频率的示例事务的事务使用映射图,步骤4.1分析事务数据应用分析,标识了重要的事务后，做详细分析，确定:(a)该事务访问的表和列以及访问的类型对于更新事务，注意被更新的列，这些列可能要避免成为索引列(b)查询条件中使用的列(WHERE子句中)模式匹配(nameLIKE%Smith%)范围查找(salaryBETWEEN30000AND40000)准确匹配的键值检索(salary=30000)这些列可能成为索引列,步骤4.1分析事务数据应用分析,(c)对于查询,注意包含在两个或更多个表的连接中的列，这些列可能成为索引列(d)事务运行的预期频率(如，每天运行50次)(e)事务的性能目标（如，必须在1秒钟内完成）,样例事务分析模式,步骤4.2选择文件组织方式,确定每个基本表的有效文件组织方式文件组织方式包括Heap,Hash,IndexedSequentialAccessMethod(ISAM),B+-Tree,andClusters目前DBMS不允许选择文件组织方式,步骤4.3选择索引指定索引,索引的类型：聚集索引（主索引）：包含记录的文件按照某个搜索码指定的顺序排序。主索引看起来像是建立在主码上的索引，其实它可以建立在任何搜索码上。（每个文件只能有一个主索引或者是一个聚簇索引）非聚集索引（二级索引、辅助索引）：搜索码指定的顺序与文件中记录的物理顺序不同的索引（一个文件中可以有多个二级索引）索引建立的原则：经常用于连接操作的列，因为这样使连接更有效率或者，在表中经常按某列的顺序访问记录的列,步骤4.3选择索引二级索引,在保持和使用二级索引时还要考虑到当检索数据时，必须要平衡改善性能。包括:每当在表中插入一条记录时，都要给每个二级索引增加一个索引记录当表中相应记录被更新时，也要更新二级索引需要使用额外的磁盘空间来存储二级索引在查询优化期间，性能可能退化，因为在选择最佳执行策略之前，查询优化器可能考虑所有的二级索引,步骤4.3选择索引选择索引意愿表的方针,(1)不必为小表创建索引(2)为表的主码建立索引（大部分DBMS自动创建）(3)检索数据时大量使用的列增加二级索引（如NAME）(4)如果经常基于外码访问数据，则为该外码增加二级索引(5)为经常有如下情况的列添加二级索引：选择或连接条件;ORDERBY;GROUPBY;其他包括排序的操作(如UNION或DISTINCT),步骤4.3选择索引选择索引意愿表的方针,(6)为在聚集函数(avg,sum等)包含的列增加二级索引SELECTbranchNo,AVG(salary)FROMStaffGROUPBYbranchNo;(7)避免为经常被更新的列或表设置索引,步骤4.3选择索引选择索引意愿表的方针,(8)如果查询将检索表中记录的大部分（如25%)，即使表很大，也不创建索引。这时查询整表要比用索引查询更有效（选择率）,步骤4.3选择索引从意愿表中删除索引,如果维护索引可能会降低重要的更新事务，就考虑删除索引如果大量的记录被插入到有索引的表中，可以先删除索引，再执行插入，然后重建索引（若增加表大小超过10%）有些DBMS允许检查优化器的策略，OracleEXPLAINPLAN；DB2EXPLAIN；,步骤4.3选择索引更新数据库策略、存档,查询优化器依赖于存储在系统目录中的数据库统计来选择最佳策略，当创建索引时DBMS自动将此索引增加到系统目录中。但是，DBMS要求使用一个工具来更新系统目录中与表和索引相关的统计信息。索引的选择以及选择的原因应当存档,StayHome的Branch视图的索引,表列事务原因Branchcitym搜索条件Staffnamen排序Videocategoryp排序u搜索条件v分组titleqru排序t搜索条件ActoractorNameq搜索条件x分组、排序DirectordirectorNamer搜索条件MemberfName/lnames搜索条件RentalAgreementdateReturns搜索条件RegistrationdateJoinedy搜索条件,步骤5设计用户视图,设计在数据库应用生命周期的需求分析和收集阶段标识的用户视图通常，视图使用SQL创建.例如，对分xs公司B001的监理和助理:CREATEVIEWStaff1_ViewASSELECTstaffNo,name,positionFROMStaffWHEREbranchNo=B001;,步骤6设计安全性机制,为数据库设计在数据库应用生命周期的需求收集和分析阶段用户指定的安全性机制.关系DBMS通常提供两种类型的数据库安全:系统安全:系统级的数据库访问和使用(例如，用户名/密码)数据安全:数据库对象的访问和使用(例如，表和视图),步骤6设计安全性机制-SQL,每个数据库用户都由DBA分配一个认证标识(此标识都有一个相关的口令)创建的每个对象都有一个拥有者.默认情况下，对象拥有者是唯一知道该对象的存在并且可以对该对象执行全部操作的用户权限是允许用户对一给定的基本表或视图可执行的操作(例如SELECT,UPDATE)GRANT语句允许拥有者把权限授予其他用户REVOKE语句收回权限,反规范化,术语“反规范化”是指对基本表结构的修改，使得新表比原始表的范式低“反规范化”也包括将两个表合并成一个新表的情况，该新表与原表满足相同范式但比原始表包含更多的空值“反规范化”后，每个表必须达到3NF,步骤7考虑引入受控冗余,确定是否放松规范化规则引入受控冗余数据来改善系统性能,步骤7考虑引入受控冗余,规范化产生一个结构上一致且最小冗余的逻辑数据库设计但是，规范化的数据库设计有时不能提供最大的处理效率数据库设计时，可能愿意接收规范化设计方面的一些损失而实现更好的性能,步骤7考虑引入受控冗余,反规范化的不利因素:使实现更加复杂经常会牺牲灵活性可能加快检索速度，但会降低更新速度,一个反规范化的例子,Branch(branchNo,street,city,state,zipCode,mgrStaffNo)Branch(branchNo,street,zipCode,mgrStaffNo)ZipCode(zipCode,city,state),步骤7考虑引入受控冗余,考虑如下情况的反规范化，以便加速进行常用或关键的事务:步骤7.1复制1:*关系中的非键列来减少连接步骤7.2复制1:*关系中的外键列来减少连接步骤7.3复制*:*关系中的列来减少连接步骤7.4引入重复组步骤7.5创建提取表步骤7.6分区表,步骤7.1复制1:*关系中的非键列来减少连接,处理前：Selectvfr.*,v,dailyRentalfromvideoForrentvrf,videovwherevrf.catalogno=v.catalognoandbranchno=B001处理后：Selectvfr.*,vfromvideoForrentvrfwherebranchno=B001,步骤7.1复制1:*关系中的非键列来减少连接,优势：提高SQL查询效率缺点：dailyRental属性出现在两个表中，会出现数据的不一致问题。比如：修改录像的租金时，需要修改两个表中的数据,步骤7.2复制1:*关系中的外键列来减少连接,步骤7.2复制1:*关系中的外键列来减少连接,处理前：selectra.*fromRentalAgreementra,VideoforRentvfrwherera.videono=vrf.videonoandvfr.branchno=B001处理后：select*fromRentalAgreementwherebranchno=B001,步骤7.3复制*:*关系中的列来减少连接,步骤7.3复制*:*关系中的列来减少连接,处理前：Selectv.title,a.*,r.*fromvideov,roler,actirawherev.catalogno=r.catalognoandr.actorno=a.actorno处理后selecta.*,r.*fromroler,actorawherer.actorno=a.actorno,步骤7.4引入重复组,为满足1NF，从逻辑数据模型中删除重复组，分离到新表，与父表形成1:*关系有时将重复组合并到父表是提高性能的有效办法，如电话号码重复组中项的绝对数量是已知的、静态的电话号码：设3个列：telNo1，telNo2(可空),telNo3(可空)设1个列：telNo（非空），另外2个仍在tel表中,步骤7.5创建提取表,在每天峰值的时候运行某种报表，这些报表要访问派生数据并且基于相同的一组基本表执行多表连接，但是报表所基于的数据可能是静态的，或者有时并不需要当前的数据创建一张基于报表所需要的表的反规范化的提取表，允许用户直接访问提取表代替访问基本表,步骤7.6分区表,除了将表合并在一起外,还有一种用于解决非常大的表的性能问题的方法，即将表分解成一些较小的并且更易于维护的片断（分区）水平分区:将表中的记录分布在几个较小的表中垂直分区:将表中的列分布在一些较小的表中.（主键是被复制的，以便重构原始表）分区在存储和分析大数量的数据的应用中非常有用.例如VideoForRent，按BranchNo水平分区,步骤7.6分区表,步骤7.6分区表,优点:改善负载平衡：分布在辅存不同位置，允许并发访问改善性能增强可用性改善可恢复性安全性缺点:复杂性降低性能重复.,如何维护数据完整性,触发器：自动更新或复制数据事务：在每个应用中构建事务，用于反规范化数据的更新批调和：在合适的时间运行批程序，以保持数据一致,步骤8监视和调整系统,监视系统并改善系统的性能以改正不正确的设计决策，或者反映变化的需求.,步骤8监视和调整系统,衡量效率有多种因素可以用:-事务吞吐量:在一个给定时间间隔内处理事务的数量-响应时间:完成单个事务所花费的时间-磁盘存储:存储数据库文件所需的磁盘空间没有一个因素总是正确的.通常要在几个因素之间进行权衡，来获得合理的平衡,步骤8监视和调整系统,必须清楚四个基本的硬件组件是如何相互影响并影响系统性能的:主存CPU磁盘I/O网络每个