数据库原理课件：选择文件组织方式和索引.ppt

Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,September 98,Pearson Education Limited,2004,*,1,物理数据设计,步骤,4,选择文件组织方式和索引,2,目的,如何分析用户事务来确定可能会影响性能的特性,基于对事物的分析，如何选择合适的文件组织方式,何时选择索引来改善系统性能,3,步骤,4,选择文件组织方式和索引,确定最佳文件组织方式来存储基本表以及实现所要求性能的索引,包括如下几步,:,步骤,4.1,分析事务,步骤,4.2,选择文件组织方式,步骤,4.3,选择索引,4,步骤,4.1,分析事务,理解运行在数据库上的事务的功能并分析重要的事务确定性能指标,经常运行的事务对性能产生重大影响的事务,业务操作的关键事务,当对数据库有很高要求时，每日,/,每周内访问数据库的次数,(,最大负载,),使用这些信息来标识可能会引起性能问题的数据库部分,需要标识事务的高层功能，来选择正确的文件组织方式和索引,更新事务中更新的列,查询事务中检索的列,5,步骤,4.1,分析事务,研究最重要的那些事务,为了帮助标识要研究的那些事务,事务,/,表交叉引用矩阵,(transaction/table cross-reference matrix),，显示了每个事务访问的表,事务应用图,(transaction usage map),，用图形的方式表明了哪些表潜在地可能被多次使用,方法：,(1),将所有事务路径映射到表中,(2),确定常被事务访问的表,(3),数据应用分析,6,交叉引用的事务和表,(e),输入在一指定分公司登记的新会员的详细信息,此表中还可以表示一定时间间隔（每小时、每天、每周）内访问的数量。

7,交叉引用的事务和表,编号,事务,(e),输入在一指定分公司登记的新会员的详细信息,(k),更新,/,删除指定成员的详细信息,(p),根据种类排序，列出指定分公司中的所有录像的标题、种类和是否可租借,(q),根据标题排序，列出指定分公司中的给定演员姓名的所有录像的标题、种类和是否可租借,(r),根据标题排序，列出指定分公司中的给定导演姓名的所有录像的标题、种类和是否可租借,(s),列出指定会员当前出租的所有录像的详细信息,8,示例事务的事务使用映射图,(s),Video,和,VideoForRent,表被事务（,p,、,q,、,r,、,s,）访问，由于,VideoForRent,表较大，因此应尽可能有效地访问该表9,数据应用分析,对每个事务而言，应确定：,访问表和列以及访问的类型,类型：插入、更新、删除、检索,“更新或删除”的列，有可能是二级检索的候选，需要重新建立索引，影响性能在查询条件中使用的列,模式匹配，如，（,name LIKE%Smith%,）,范围查找，如，（,Salary BETWEEN 30000 AND 40000,）,准确匹配的键值索引，例如（,Salary=30000,）,包含在两个或更多表的连接列（主键,/,外键）,这些列可能是访问结构的候选，建立索引，提高性能。

事务运行的预期频率,例如，每天大约事务执行的次数,事务的性能目标,例如，事务必须在几秒中完成,对常用和关键事务中的查询条件使用的列，需要比访问结构有更高的优先级,10,样例事务分析模式,11,步骤,4.2,选择文件组织方式,确定每个基本表的有效文件组织方式,文件组织方式包括：,堆（,Heap,）,哈希（,Hash,）,索引顺序访问方法（,ISAM,）,B,+,树,一些,DBMS,文件组织方式是固定的，无法更改,12,步骤,4.2,选择文件组织方式,确定每个基本表的有效文件组织方式,文件组织方式包括：,堆（,Heap,）,哈希（,Hash,）,索引顺序访问方法（,ISAM,）,B,+,树,一些,DBMS,文件组织方式是固定的，无法更改,13,选择文件组织方式指南,堆（,Heap,）,适于无序访问的表，例如，检索,StayHome,中所有会员地址有索引键的表哈希（,Hash,）,检索是基于与哈希字段值精确匹配的表例如，如果在,Member,表的,memberNo,列上进行哈希，则基于会员号的检索非常有效哈希列被经常更新时，会影响性能索引顺序访问方法（,ISAM,）,支持基于模式匹配或者基于范围值的检索,ISAM,索引是静态的，它在文件被创建时建立，随着表的不断更新，性能会降低。

B,+,树,它的索引是动态的，对于经常修改的表，采用此模式更有效14,步骤,4.3,选择索引,索引（,Index,）：,是一种数据结构，类似于书的目录它是与要查找信息的文件相关联的一个辅助结构一个索引结构是与一个特定的查询关键字相关的，并且包含由关键字和包含这个值的逻辑记录组成的记录包含逻辑记录的文件称为数据文件，而含有索引记录的文件称为索引文件索引文件的值是根据索引字段的值来排序的，这个索引字段通常是基于表中的某个列的15,步骤,4.3,选择索引,索引类型：,主索引：数据文件根据一个有序的键字段（候选码）顺序排序，并且索引字段是建立在有序的键字段上的键字段保证了每条记录的唯一性聚簇索引：数据文件根据非键字段的值排序，并且索引字段是建立在这些非键字段上的，因此对应索引字段的一个值可能会有多条记录非键字段被称为聚簇字段二级索引：定义在一个数据文件中的非排序字段上的索引数据文件不一定是按照索引字段排序的一个文件最多只能有一个主索引或一个聚簇索引，但可以有多个二级索引16,步骤,4.3,选择索引,确定添加索引是否会改善系统性能,一种方法是保持记录的无序性并且创建所需数目的二级索引,通过指定主键或聚簇索引使表中记录为有序的。

使用索引的情况：,经常用于连接操作的列，因为这样使连接更有效率,在表中经常按某列的顺序访问记录的列,17,步骤,4.3,选择索引,如果选择排序的列是表的键，那么该索引就是主索引，如果排序的列不是键，那么该索引就是聚簇索引每个文件,只能有一个主索引或者是一个聚簇索引，而不能两者兼有但可以有多个二级索引18,指定索引,为,Video,表创建基于,catalogNo,列的主索引：,CREATE UNIQUE INDEX catalogno_index,ON Video(catalogNo),为,VideoForRent,表创建基于,catalogNo,列的聚簇索引：,CREATE INDEX catalogno_index,ON VideoForRent(catalogNo)CLUSTER,注：,VideoForRent(,videoNo,available,catalogNo,branchNo),删除索引,DROP INDEX catalogno_index,19,选择二级索引,二级索引提供了为基本表指定其他键的机制，便于更有效地检索数据例如，,Member,表在,memberNo,上进行了,Hash,映射，也就是主索引。

但是，可能会有经常使用基于,lName,列来访问数据，这时可以增加,lName,作为二级索引20,选择二级索引,在保持和使用二级索引时还要考虑到当检索数据时，必须要平衡改善性能当在表中插入一条记录时，都要给每个二级索引增加一个索引记录当表中相应记录被更新时，也要更新二级索引需要使用额外的磁盘空间来存储二级索引在查询优化期间，性能可能退化，因为在选择最佳执行策略之前，查询优化器可能考虑所有的二级索引21,选择索引“意愿表”的方针,(1),不必为小表创建索引在内存中查询该表会比存储额外的索引结构更加有效2),如果没有文件组织方式的键，则索引表的主键,(3),为检索数据时大量使用的列增加二级索引,如，为,Member,表增加基于,lName,列的二级索引,(4),如果经常给予外键访问数据，则为该外键增加二级索引,对于经常使用,branchNo,连接,VideoForRent,表和,Branch,表，需为,VideoForRent,表创建基于,branchNo,的二级索引，以提高效率22,选择索引“意愿表”的方针,(5),为经常有如下情况的列增加二级索引,查询或者连接条件,ORDER BY,GROUP BY,其他操作包括排序（例如,UNION,或,DISTINCT,）,(6),为在内置函数包含的列增加二级索引，包括内置函数中用于聚簇的列。

SELECT branchNo,AVG(salary),FROM Staff,GROUP BY branchNo;,由于有,GROUP BY,子句，可以考虑为,branchNo,添加索引，可是该查询又用了聚簇列，因此为,branchNo,和,salary,共同创建索引会更加有效DBMS,只根据索引中的数据就可以完成整个查询，而不需要创建数据文件23,选择索引“意愿表”的方针,(7),避免为经常被更新的列或表设置索引,(8),如果查询将检索表中记录的大部分（例如,25%,），也不创建索引9),避免为由长字符串组成的列创建索引,(10),对于列值变化较小的列，不适宜对该列作索引如，在,VideoForRent,表中，,available,列被事务用作查询条件但是，这个列只有两个值（,Y,或,N,），并不值得为该列建立索引24,关于条件查询,如果查询条件包含多个条件，并且条件中包含一个,OR,子句，而该条件没有索引,/,排序，那么为其他列添加索引将不会改善查询速度，因为仍然需要表的线性查找例如，假定只有,Video,表的,category,和,dailyRental,列有索引，要使用下列查询：,SELECT*FROM Video,WHERE,（,category=Action OR dailyRental3 OR price 15);,由于,price,没有索引，因此，本查询的效率得不到改善。

但是如果条件是,AND,，则该两列的索引就可以达到优化查询25,关于大量插入记录,如果大量的记录被插入到有索引的表中，可以先删除索引，在执行插入数据后，再创建索引会更加有效作为一个不成文的规则，如果插入动作将增加表的,10%,的大小，那么暂时将索引删除26,分析工具,Oracle,EXPLAIN PLAN,诊断工具,DB2,EXPLAIN,实用工具,Microsoft Access,性能分析器,27,StayHome,的,Branch,视图的附加索引,28,StayHome,的,Branch,视图的查询事务所需的表,29,StayHome,的,Branch,视图的查询事务所需的表（续）,30,小结,步骤,4,中，分析事务并基于这个分析，设计文件的组织方式和用于存储基本表的索引在理解要支持的事务的细节之前，是不能做出有意义的物理设计的这包括分析最重要的事务二级索引提供了为基本表说明其他键的机制，这可用于更有效的检索表但是，在维护和使用二级索引时还要靠虑到当数据索引时，必须要平衡改善性能为表选择合适的文件组织方式的一种方法是保持记录的无序，并且创建所需的二级索引另一种方法是将表中的记录由指定的主键或者聚簇索引来排序。

确定所需要的二级索引的一种方法是创建索引候选列的“意愿表”，然后考虑维护这些索引的影响。