4-数据库物理设计解析.ppt

1、4 数据库物理设计,4 物理数据库设计,物理数据库设计步骤3：为目标DBMS转换全局逻辑数据模型 步骤3.1：设计基本表 步骤3.2：设计派生数据的表示 步骤3.3：设计其他的业务规则步骤4：选择文件组织方式和索引 步骤4.1：分析事务 步骤4.2：选择文件组织方式 步骤4.3：选择索引步骤5：设计用户视图步骤6：设计安全性机制步骤7：引入受控冗余的考虑步骤8：监视并调整操作系统,逻辑与物理数据库设计的比较,逻辑数据库设计独立于实现细节, 例如独立于目标DBMS的具体功能逻辑数据库设计关心的是“什么”,而物理数据库设计关心的是“怎么” 逻辑数据模型和数据字典是物理设计的信息源,物理数据库设计,

2、产生二级存储上的数据库实现的描述的过程。它描述了基本表、文件组织方式和用于实现数据有效访问的索引以及任何相关的完整性约束和安全限制,物理数据库设计方法学概述,步骤 3 为目标DBMS转换全局逻辑数据模型步骤 4 选择文件组织方式和索引步骤 5 设计用户视图步骤 6 设计安全机制步骤 7 考虑引入受控冗余步骤 8 监控并调整操作系统,步骤 3 为目标DBMS转换全局逻辑数据模型,从逻辑数据模型产生基本的工作关系数据库由下列步骤组成:Step 3.1 设计基本表Step 3.2 设计派生数据的表示Step 3.3 设计其他业务规则,步骤 3 为目标DBMS转换全局逻辑数据模型,需要非常了解目标DB

3、MS所提供的有关功能的知识，例如应该知道怎样创建基本表，以及DBMS是否支持如下定义：是否支持主码、外码和备用码是否允许某列被定义为NOT NULL是否支持域定义是否支持关系完整性规则是否支持业务规则定义,步骤 3.1 设计基本表,确定如何在目标DBMS中描述逻辑数据模型所标识的基本表需要比较和吸收在逻辑数据库设计阶段创建的表的信息,步骤 3.1 设计基本表,对每个表, 需要定义：表名括号内的简单列名表主码以及在适当的地方的备用码和外码任何标识出的外键的参照完整性约束应该对每个列有如下定义：它的域，包括数据类型、长度和域上的约束每个列设置可选的默认值该列是否可以为空,使用扩展DBDL的Bran

4、ch表物理设计,步骤 3.2 设计其他业务规则,为目标DBMS设计其他业务规则. 有些DBMS比其他系统提供了更多的定义业务规则的功能. 例如: CONSTRAINT member_not_renting_too_many CHECK (NOT EXISTS (SELECT memberNo FROM RentalAgreement GROUP BY memberNo HAVING COUNT(*) 10) 或使用触发器,步骤 4 选择文件组织和索引,确定最佳文件组织方式来存储基本表以及实现所要求性能的索引.由下列步骤组成:步骤 4.1 分析事务步骤 4.2 选择文件组织方式步骤 4.3 选择

5、索引,步骤 4.1 分析事务,理解运行在数据库上的事务的功能并分析重要的事务. 标识出性能标准, 例如:经常运行的事务和对性能产生重大影响的事务业务操作的关键事务当对数据库有很高要求时，每日/每周内访问数据库的次数 (最大负荷)使用这些信息来标识可能会引起性能问题的数据库部分，同时，标识在更新事务中更新的列，以及在查询中作为选择条件的列根据以上信息选择正确的文件组织和索引,步骤 4.1 分析事务,通常不可能分析所有预期事务，因此只研究最重要的那些事务. 80/20规则是有效的为了帮助标识要研究的那些事务，可以使用:事务/表交叉引用矩阵，显示每个事务访问的表事务应用图, 表明哪些表潜在的可能被多

6、次使用,交叉引用的事务和表,显示预期频率的示例事务的事务使用映射图,步骤 4.1 分析事务 数据应用分析,标识了重要的事务后，做详细分析，确定:(a)该事务访问的表和列以及访问的类型对于更新事务，注意被更新的列，这些列可能要避免成为索引列(b) 查询条件中使用的列(WHERE子句中)模式匹配 (name LIKE %Smith%)范围查找 (salary BETWEEN 30000 AND 40000)准确匹配的键值检索 (salary=30000)这些列可能成为索引列,步骤 4.1 分析事务 数据应用分析,(c) 对于查询, 注意包含在两个或更多个表的连接中的列，这些列可能成为索引列(d)

7、事务运行的预期频率 (如，每天运行50次)(e) 事务的性能目标（如，必须在1秒钟内完成）,样例事务分析模式,步骤 4.2 选择文件组织方式,确定每个基本表的有效文件组织方式文件组织方式包括Heap, Hash, Indexed Sequential Access Method (ISAM), B+-Tree, and Clusters目前DBMS不允许选择文件组织方式,步骤 4.3 选择索引 指定索引,索引的类型：聚集索引（主索引）：包含记录的文件按照某个搜索码指定的顺序排序。主索引看起来像是建立在主码上的索引，其实它可以建立在任何搜索码上。（每个文件只能有一个主索引或者是一个聚簇索引）非聚

8、集索引（二级索引、辅助索引）：搜索码指定的顺序与文件中记录的物理顺序不同的索引（一个文件中可以有多个二级索引）索引建立的原则：经常用于连接操作的列，因为这样使连接更有效率或者，在表中经常按某列的顺序访问记录的列,步骤 4.3 选择索引 二级索引,在保持和使用二级索引时还要考虑到当检索数据时，必须要平衡改善性能。包括:每当在表中插入一条记录时，都要给每个二级索引增加一个索引记录当表中相应记录被更新时，也要更新二级索引需要使用额外的磁盘空间来存储二级索引在查询优化期间，性能可能退化，因为在选择最佳执行策略之前，查询优化器可能考虑所有的二级索引,步骤 4.3 选择索引 选择索引意愿表的方针,(1)

9、不必为小表创建索引(2) 为表的主码建立索引（大部分DBMS自动创建） (3) 检索数据时大量使用的列增加二级索引（如NAME） (4) 如果经常基于外码访问数据，则为该外码增加二级索引(5) 为经常有如下情况的列添加二级索引：选择或连接条件; ORDER BY; GROUP BY; 其他包括排序的操作 (如 UNION 或 DISTINCT),步骤 4.3 选择索引 选择索引意愿表的方针,(6) 为在聚集函数(avg, sum等)包含的列增加二级索引SELECT branchNo, AVG(salary) FROM Staff GROUP BY branchNo; (7)避免为经常被更新的列

10、或表设置索引,步骤 4.3 选择索引 选择索引意愿表的方针,(8)如果查询将检索表中记录的大部分（如25%)，即使表很大，也不创建索引。这时查询整表要比用索引查询更有效（选择率）,步骤 4.3 选择索引 从意愿表中删除索引,如果维护索引可能会降低重要的更新事务，就考虑删除索引如果大量的记录被插入到有索引的表中，可以先删除索引，再执行插入，然后重建索引（若增加表大小超过10%）有些DBMS允许检查优化器的策略，Oracle EXPLAIN PLAN；DB2 EXPLAIN；,步骤 4.3 选择索引 更新数据库策略、存档,查询优化器依赖于存储在系统目录中的数据库统计来选择最佳策略，当创建索引时DB

11、MS自动将此索引增加到系统目录中。但是，DBMS要求使用一个工具来更新系统目录中与表和索引相关的统计信息。索引的选择以及选择的原因应当存档,StayHome的Branch视图的索引,表 列 事务 原因Branch city m 搜索条件Staff name n 排序Video category p 排序 u 搜索条件 v 分组 title q r u 排序 t 搜索条件Actor actorName q 搜索条件 x 分组、排序 Director directorName r 搜索条件Member fName/lname s 搜索条件RentalAgreement dateReturn s 搜

12、索条件Registration dateJoined y 搜索条件,步骤 5 设计用户视图,设计在数据库应用生命周期的需求分析和收集阶段标识的用户视图通常，视图使用SQL创建. 例如，对分xs公司B001的监理和助理:CREATE VIEW Staff1_View AS SELECT staffNo, name, position FROM Staff WHERE branchNo = B001;,步骤 6 设计安全性机制,为数据库设计在数据库应用生命周期的需求收集和分析阶段用户指定的安全性机制.关系DBMS通常提供两种类型的数据库安全:系统安全: 系统级的数据库访问和使用 (例如，用户名/密

13、码)数据安全: 数据库对象的访问和使用 (例如，表和视图),步骤 6 设计安全性机制 - SQL,每个数据库用户都由DBA分配一个认证标识 (此标识都有一个相关的口令)创建的每个对象都有一个拥有者. 默认情况下，对象拥有者是唯一知道该对象的存在并且可以对该对象执行全部操作的用户权限是允许用户对一给定的基本表或视图可执行的操作 (例如 SELECT, UPDATE)GRANT语句允许拥有者把权限授予其他用户REVOKE语句收回权限,反规范化,术语“反规范化”是指对基本表结构的修改，使得新表比原始表的范式低“反规范化”也包括将两个表合并成一个新表的情况，该新表与原表满足相同范式但比原始表包含更多的空值“反规范化”后，每个表必须达到3NF,步骤 7 考虑引入受控冗余,确定是否放松规范化规则引入受控冗余数据来改善系统性能,