6-物理数据库设计-4

6-物理数据库设计-4第一页，共19页。步骤4选择文件组织和索引

确定最佳文件组织方式来存储基本表以及实现所要求性能的索引.由下列步骤组成:步骤4.1分析事务步骤4.2选择文件组织方式步骤4.3选择索引2第二页，共19页。步骤4.1分析事务

理解运行在数据库上的事务的功能并分析重要的事务.

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

–数据应用分析

标识了重要的事务后，做详细分析，确定:(a)

该事务访问的表和列以及访问的类型.对于更新事务，注意被更新的列，这些列可能要避免成为索引列。(b)查询条件中使用的列(WHERE子句中).模式匹配(nameLIKE‘%Smith%’)范围查找(salaryBETWEEN30000AND40000)准确匹配的键值检索(salary=30000)这些列可能成为索引列。7第七页，共19页。步骤4.1分析事务

–数据应用分析

(c)对于查询,注意包含在两个或更多个表的连接中的列.这些列可能成为索引列(d)事务运行的预期频率.(如，每天运行50次)(e)事务的性能目标.（如，必须在1秒钟内完成）8第八页，共19页。样例事务分析模式

9第九页，共19页。步骤4.2选择文件组织方式

确定每个基本表的有效文件组织方式.

文件组织方式包括Heap,Hash,IndexedSequentialAccessMethod(ISAM),B+-Tree,andClusters.目标DBMS可能不允许选择文件组织方式

(特别是用于PC机的DBMS).10第十页，共19页。步骤4.3选择索引–指定索引

确定添加索引是否会改善系统性能.一种方法是保持纪录的无序性并且创建所需数目的二级索引（定义在数据文件非排序字段上的索引）.

或者通过指定主键或聚簇索引使表中记录为有序的.后种情况下,如何选择列来排序或者聚簇索引记录:经常用于连接操作的列，因为这样使连接更有效率或者，在表中经常按某列的顺序访问记录的列.11第十一页，共19页。步骤4.3选择索引

–指定索引如果选择排序的列是表的键,那么该索引就是主索引;否则,索引就是聚簇索引.每个文件只能有一个主索引或者是一个聚簇索引.二级索引提供了为基本表指定其他键的机制，可以用于更有效的检索数据.12第十二页，共19页。步骤4.3选择索引–二级索引在保持和使用二级索引时还要考虑到当检索数据时，必须要平衡改善性能.

包括:每当在表中插入一条记录时，都要给每个二级索引增加一个索引记录;当表中相应记录被更新时，也要更新二级索引;需要使用额外的磁盘空间来存储二级索引;在查询优化期间，性能可能退化，因为在选择最佳执行策略之前，查询优化器可能考虑所有的二级索引.

13第十三页，共19页。步骤4.3选择索引–选择索引意愿表的方针(1)不必为小表创建索引.(2)为表的主键建立索引（有的DBMS自动创建）

(3)检索数据时大量使用的列增加二级索引（如NAME）.(4)如果经常基于外键访问数据，则为该外键增加二级索引.(5)为经常有如下情况的列添加二级索引:选择或连接条件;ORDERBY;GROUPBY;其他包括排序的操作

(如UNION或DISTINCT).14第十四页，共19页。步骤4.3选择索引–选择索引意愿表的方针(6)为在内置函数(avg,sum等)包含的列增加二级索引.SELECTbranchNo,AVG(salary)FROMStaffGROUPBYbranchNo;[branchNo,salary](7)为可以导致仅索引计划(index-onlyplan)的列（只使用索引中的数据就可以产生所需的结果）添加二级索引.15第十五页，共19页。步骤4.3选择索引–选择索引意愿表的方针(8)避免为经常被更新的列或表设置索引.(9)如果查询将检索表中记录的大部分（如25%)，即使表很大，也不创建索引。这时查询整表要比用索引查询更有效.(10)避免为由长字符串组成的列创建索引.SELECT*FROMvideoWHERE(category=‘Action’ORdailyrental>3ORprice>15);16第十六页，共19页。步骤4.3选择索引–从意愿表中删除索引如果维护索引可能会降低重要的更新事务，就考虑删除索引。如果大量的记录被插入到有索引的表中，可以先删除索引，再执行插入，然后重建索引（若增加表大小超过10%）有些DBMS允许检查优化器的策略，OracleEXPLAINPLAN；DB2EXPLAIN；ACCESS性能分析器

17第十七页，共19页。步骤4.3选择索引–更新数据库策略、存档查询优化器依