物理数据库设计

1、物理数据库设计Chapter 6物理数据库设计物理数据库设计 4 (选择文件组织和索引选择文件组织和索引) 2步骤步骤 4 选择文件组织和索引选择文件组织和索引确定最佳文件组织方式来存储基本表以确定最佳文件组织方式来存储基本表以及实现所要求性能的索引及实现所要求性能的索引.n由下列步骤组成由下列步骤组成:n步骤步骤 4.1 分析事务分析事务n步骤步骤 4.2 选择文件组织方式选择文件组织方式n步骤步骤 4.3 选择索引选择索引3步骤步骤 4.1 分析事务分析事务 理解运行在数据库上的事务的功能并分析重要的事务理解运行在数据库上的事务的功能并分析重要的事务. n标识出性能标准标识出性能标准, 例

2、如例如:n经常运行的事务和对性能产生重大影响的事务经常运行的事务和对性能产生重大影响的事务;n业务操作的关键事务业务操作的关键事务;n当对数据库有很高要求时，每日当对数据库有很高要求时，每日/每周内访问数据库的次数每周内访问数据库的次数 (最大负荷最大负荷).n使用这些信息来标识可能会引起性能问题的数据库部分，使用这些信息来标识可能会引起性能问题的数据库部分，同时，标识事务的高层功能，如在更新事务中更新的列，同时，标识事务的高层功能，如在更新事务中更新的列，或在查询中检索的列或在查询中检索的列.n根据以上信息选择正确的文件组织和索引根据以上信息选择正确的文件组织和索引.4步骤步骤 4.1 分析

3、事务分析事务n通常不可能分析所有预期事务，因此只研究最重要的通常不可能分析所有预期事务，因此只研究最重要的那些事务那些事务. 80/20规则是有效的规则是有效的n为了帮助标识要研究的那些事务，可以使用为了帮助标识要研究的那些事务，可以使用:n事务事务/表交叉引用矩阵，显示每个事务访问的表表交叉引用矩阵，显示每个事务访问的表.n事务应用图事务应用图, 表明哪些表潜在的可能被多次使用表明哪些表潜在的可能被多次使用. 5交叉引用的事务和表交叉引用的事务和表6显示预期频率的示例事务的事务使用映射图显示预期频率的示例事务的事务使用映射图7步骤步骤 4.1 分析事务分析事务 数据应用分析数据应用分析 n标

4、识了重要的事务后，做详细分析，确定标识了重要的事务后，做详细分析，确定:(a)该事务访问的表和列以及访问的类型该事务访问的表和列以及访问的类型.n对于更新事务，注意被更新的列，这些列可能要避免成对于更新事务，注意被更新的列，这些列可能要避免成为索引列。为索引列。(b) 查询条件中使用的列查询条件中使用的列(WHERE子句中子句中).n模式匹配模式匹配 (name LIKE %Smith%)n范围查找范围查找 (salary BETWEEN 30000 AND 40000)n准确匹配的键值检索准确匹配的键值检索 (salary=30000)这些列可能成为索引列。这些列可能成为索引列。8步骤步骤

5、4.1 分析事务分析事务 数据应用分析数据应用分析 (c) 对于查询对于查询, 注意包含在两个或更多个表的连接中的列注意包含在两个或更多个表的连接中的列.n这些列可能成为索引列这些列可能成为索引列(d) 事务运行的预期频率事务运行的预期频率.(如，每天运行如，每天运行50次次)(e) 事务的性能目标事务的性能目标.（如，必须在（如，必须在1秒钟内完成）秒钟内完成）9样例事务分析模式样例事务分析模式 10步骤步骤 4.2 选择文件组织方式选择文件组织方式 确定每个基本表的有效文件组织方式确定每个基本表的有效文件组织方式. n文 件 组 织 方 式 包 括文 件 组 织 方 式 包 括 H e a

6、 p , H a s h , Indexed Sequential Access Method (ISAM), B+-Tree, and Clusters. n目标目标DBMS可能不允许选择文件组织方式可能不允许选择文件组织方式 (特别是用于特别是用于PC机的机的DBMS).11步骤步骤 4.3 选择索引选择索引 指定索引指定索引 确定添加索引是否会改善系统性能确定添加索引是否会改善系统性能.n一种方法是保持纪录的无序性并且创建所需数目一种方法是保持纪录的无序性并且创建所需数目的二级索引（定义在数据文件非排序字段上的索的二级索引（定义在数据文件非排序字段上的索引）引）. n或者通过指定主键或聚

7、簇索引使表中记录为有序的或者通过指定主键或聚簇索引使表中记录为有序的. n后种情况下后种情况下, 如何选择列来排序或者聚簇索引记录如何选择列来排序或者聚簇索引记录:n经常用于连接操作的列，因为这样使连接更有效率经常用于连接操作的列，因为这样使连接更有效率n或者，在表中经常按某列的顺序访问记录的列或者，在表中经常按某列的顺序访问记录的列.12步骤步骤 4.3 选择索引选择索引 指定索引指定索引n如果选择排序的列是表的键如果选择排序的列是表的键, 那么该索那么该索引就是主索引引就是主索引; 否则否则, 索引就是聚簇索引索引就是聚簇索引.n每个文件只能有一个主索引或者是一个每个文件只能有一个主索引或

8、者是一个聚簇索引聚簇索引.n二级索引提供了为基本表指定其他键的二级索引提供了为基本表指定其他键的机制，可以用于更有效的检索数据机制，可以用于更有效的检索数据.13步骤步骤 4.3 选择索引选择索引 二级索引二级索引n在保持和使用二级索引时还要考虑到当在保持和使用二级索引时还要考虑到当检索数据时，必须要平衡改善性能检索数据时，必须要平衡改善性能. n包括包括:n每当在表中插入一条记录时，都要给每个二级索引增每当在表中插入一条记录时，都要给每个二级索引增加一个索引记录加一个索引记录;n当表中相应记录被更新时，也要更新二级索引当表中相应记录被更新时，也要更新二级索引;n需要使用额外的磁盘空间来存储二

9、级索引需要使用额外的磁盘空间来存储二级索引;n在查询优化期间，性能可能退化，因为在选择最佳在查询优化期间，性能可能退化，因为在选择最佳执行策略之前，查询优化器可能考虑所有的二级索执行策略之前，查询优化器可能考虑所有的二级索引引. 14步骤步骤 4.3 选择索引选择索引 选择索引意愿表选择索引意愿表的方针的方针(1) 不必为小表创建索引不必为小表创建索引. (2) 为表的主键建立索引（有的为表的主键建立索引（有的DBMS自动创建）自动创建） (3) 检索数据时大量使用的列增加二级索引（如检索数据时大量使用的列增加二级索引（如NAME）. (4) 如果经常基于外键访问数据，则为该外键增加如果经常基

10、于外键访问数据，则为该外键增加二级索引二级索引.(5) 为经常有如下情况的列添加二级索引为经常有如下情况的列添加二级索引: 选择或连接选择或连接条件条件; ORDER BY; GROUP BY; 其他包括排序的操作其他包括排序的操作 (如如 UNION 或或 DISTINCT).15步骤步骤 4.3 选择索引选择索引 选择索引意愿表选择索引意愿表的方针的方针(6) 为在内置函数为在内置函数(avg, sum等等)包含的列增加二级索引包含的列增加二级索引.SELECT branchNo, AVG(salary) FROM Staff GROUP BY branchNo; branchNo, sa

11、lary(7) 为可以导致仅索引计划为可以导致仅索引计划(index-only plan)的列（只的列（只使用索引中的数据就可以产生所需的结果）添加二级使用索引中的数据就可以产生所需的结果）添加二级索引索引.16步骤步骤 4.3 选择索引选择索引 选择索引意愿选择索引意愿表的方针表的方针(8) 避免为经常被更新的列或表设置索引避免为经常被更新的列或表设置索引.(9) 如果查询将检索表中记录的大部分（如如果查询将检索表中记录的大部分（如25%)，即使表很大，也不创建索引。这时查询整表要比用即使表很大，也不创建索引。这时查询整表要比用索引查询更有效索引查询更有效. (10) 避免为由长字符串组成的

12、列创建索引避免为由长字符串组成的列创建索引.SELECT * FROM videoWHERE (category=Action OR dailyrental3 OR price15);17步骤步骤 4.3 选择索引选择索引 从意愿表中删从意愿表中删除索引除索引n如果维护索引可能会降低重要的更新事如果维护索引可能会降低重要的更新事务，就考虑删除索引。务，就考虑删除索引。n如果大量的记录被插入到有索引的表中，如果大量的记录被插入到有索引的表中，可以先删除索引，再执行插入，然后重可以先删除索引，再执行插入，然后重建索引（若增加表大小超过建索引（若增加表大小超过10%）n有些有些DBMS允许检查优化器

13、的策略，允许检查优化器的策略，Oracle EXPLAIN PLAN；DB2 EXPLAIN；ACCESS性能分析器性能分析器 18步骤步骤 4.3 选择索引选择索引 更新数据库策更新数据库策略、存档略、存档n查询优化器依赖于存储在系统目录中的查询优化器依赖于存储在系统目录中的数据库统计来选择最佳策略，当创建索数据库统计来选择最佳策略，当创建索引时引时DBMS自动将此索引增加到系统目录自动将此索引增加到系统目录中。但是，中。但是，DBMS要求使用一个工具来更要求使用一个工具来更新系统目录中与表和索引相关的统计信新系统目录中与表和索引相关的统计信息。息。n索引的选择以及选择的原因应当存档索引的选择以及选择的原因应当存档19StayHome的的Branch视图的索引视图的索引 表表 列列 事务事务 原因原因nBranch city m 搜索条件搜索条件nStaff name n 排序排序nVideo category p 排序排序n u 搜索条件搜索条件n v 分组