第二章_4物理结构设计

单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,数据库系统设计与开发,北京邮电大学,计算机学院,数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于给定的计算机系统。,为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程，就是数据库的物理设计。,充分了解应用环境，详细分析要运行的事务，以获得选择物理数据库设计所需参数,充分了解所用,RDBMS,的内部特征，特别是系统提供的存取方法和存储结构,2.6,数据库物理结构设计,关系数据库物理设计的内容,为关系模式选择存取方法,(,建立存取路径,),设计关系、索引等数据库文件的物理存储结构,物理数据库设计所需参数,数据库查询事务（查询的关系，查询条件所涉及的属性，连接条件所涉及的属性，查询的投影属性）,数据更新事务（被更新的关系，每个关系上的更新操作条件所涉及的属性，修改操作要改变的属性值）,每个事务在各关系上运行的频率和性能要求,其他需考虑的问题：,目标,DBMS,支持的特性、功能和选项；,主机计算机系统的特性和能力；,磁盘存储配置；,数据量。,数据库物理设计步骤：,数据库逻辑模式调整,文件组织与存取设计,数据分布设计,安全模式设计,确定系统配置,物理模式评估,1,数据库逻辑模式调整,将与平台无关的描述数据库逻辑结构的关系模式及其视图转换为所选定的具体,DBMS,平台可支持的基本表和视图，并利用,DBMS,提供的完整性机制设计定义在基本表上的面向应用的业务规则。,（,1),实现目标数据库基本表和视图,遵循目标数据库的语法规则或变通,（,2),设计基本表业务规则,利用目标,DBMS,提供的,Check,、,断言、触发器等完成完整性约束,2,文件组织与存取设计,（,1),分析事务的数据访问特性,使用事务,/,表交叉引用矩阵，分析系统內重要事务对各基表的访问情况，确定事务访问哪些基本表，对哪些基本表执行了何种操作，并进一步分析各操作涉及到的基本属性表。,将所有事务路径映射到表中；,确定哪些表最常被事务访问；,分析选出的包含了这些表的事务。,估计各事务的执行频率，单位时间内事务的执行次数，分析事务中的每个数据访问操作对各个基表的相关属性的操作频率。,对每张基本表，汇总所有作用于该表的各事务的操作频率信息，得到：该表是否被频繁访问、该表中哪些属性列的访问频率较高和作用于这些属性上的操作类型和查询条件类型。,根据事务数据访问特性分析结果，可以对基本表设计成更为有效的文件组织和索引方式。,（,2),了解并选择数据库文件结构,每种,DBMS,平台都提供一种或若干种数据库文件结构，设计者应当了解目标,DBMS,提供的各种数据库物理结构的机制，根据应用系统的特点，为基本表和数据库选择合适的文件结构。,选择文件组织方式：,确定每个基本表的有效文件组织方式。（如果目标,DBMS,允许）,堆,HASH,索引顺序存取方法（,ISAM,）,B+,树,堆（无序的）：（下面情况较适合）,1,）当数据批量加载到表时；,2,）,表只有几页长；,3,）每当访问表时都要检索表中的每条记录；,4,）当表有其他的访问结构时，例如索引键，则堆存储可用来保存空间。,当仅访问表中的选定记录时，堆文件不合适。,HASH,：（,在下面情况下并不适合）,1,）,当记录是基于,Hash,字段值的模式匹配进行检索时。,（例如检索成员号以,M2,开始的所有成员）,2,）当记录是基于,HASH,字段值的范围进行检索时。,3,）当记录是基于一个其他列而不是基于,HSAH,列检索时。,4,）当记录是基于,HSAH,字段的一部分进行检索时。,5,）当,HSAH,列被经常更新时。,ISAM,（,索引顺序存取方法）：,支持基于准确键匹配、模式匹配、值的范围和制定的部分码。,B+,树：,支持基于准确键匹配、模式匹配、值的范围和指定的部分键。其索引是动态的，随着表内容的增加而增加。,（,3),设计存取路径,为数据库文件设计合理的物理存储位置,涉及数据分布,为基本表设计索引机制,3,数据分布设计,（,1),不同类型数据的物理分布,有基本表、索引、日志、数据库备份数据等，各类数据在系统中作用不同，使用频率不同，应根据实际情况放在合适的物理介质上。,数据库数据备份、日志文件备份等由于只在故障恢复时才使用，而且数据量很大，可以考虑存放在磁带上。,应用数据、索引和日志使用频繁，要求响应时间短，必须放在支持直接存取的磁盘存储介质上。,如果计算机有多个磁盘，可以考虑将表和索引分别放在不同的磁盘上，在查询时，由于两个磁盘驱动器分别在工作，因而可以保证物理读写速度比较快。,可以将比较大的表分别放在两个磁盘上，以加快存取速度，这在多用户环境下特别有效。,可以将日志文件与数据库对象（表、索引等）放在不同的磁盘以改进系统的性能。,（,2),应用数据的划分和分布,根据数据的使用特征划分。,存取频率较高部分与存取频率较低划分。,频繁使用分区中的数据可考虑建立,B+,树等多层索引，对非频繁使用分区中的数据可以不建或只建立单层索引；,可以将大的基本表划分多个分区，每个分区作为一个数据文件分别存储在不同的磁盘上。,根据时间、地点划分不同分区。,（,3),派生属性数据分布,将派生属性作为基表中单独一列；,派生属性不出现在基表中。,（,4),关系模式的反规范化,模式规范化和查询效率之间的权衡。,4,安全模式设计,（,1),系统安全设计,为数据库服务器合法用户分配用户名和口令，使其能正常登录服务器。,（,2),数据安全设计,通过数据库系统视图机制和授权机制为用户分配对数据库对象访问的权限。,5,确定系统配置,DBMS,产品一般都提供了一些存储分配参数,同时使用数据库的用户数,同时打开的数据库对象数,使用的缓冲区长度、个数,时间片大小,数据库的大小,装填因子,锁的数目,等等,系统都为这些变量赋予了合理的缺省值。但是这些值不一定适合每一种应用环境，在进行物理设计时，需要根据应用环境确定这些参数值，以使系统性能最优。,在物理设计时对系统配置变量的调整只是初步的，在系统运行时还要根据系统实际运行情况做进一步的调整，以期切实改进系统性能。,物理模式评估,主要从定量估算各种方案的存储空间、存取时间和维护代价入手，对估算结果进行权衡、比较，选择出一个较优的合理的物理结构。,聚簇,为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块称为聚簇,许多关系型,DBMS,都提供了聚簇功能,聚簇存放与聚簇索引的区别,聚簇索引：,索引项顺序与表中元组的物理顺序一致。,聚簇的用途,1.,大大提高按聚簇属性进行查询的效率,2.,节省存储空间,聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了,聚簇的局限性,1.,聚簇只能提高某些特定应用的性能,2.,建立与维护聚簇的开销相当大,对已有关系建立聚簇，将导致关系中元组移动其物理存储位置，并使此关系上原有的索引无效，必须重建。,当一个元组的聚簇码改变时，该元组的存储位置也要做相应移动。,聚簇的适用范围,既适用于单个关系独立聚簇，也适用于多个关系组合聚簇,2.,当通过聚簇码进行访问或连接是该关系的主要应用，与聚簇码无关的其他访问很少或者是次要的时，可以使用聚簇。,当,SQL,语句中包含有与聚簇码有关的,ORDER BY,，,GROUP BY,，,UNION,，,DISTINCT,等子句或短语时，使用聚簇特别有利，可以省去对结果集的排序操作,索引机制用于加速对所需数据的存取,.,例如,图书馆中的作者目录,搜索键,用来在文件中查找记录的属性或属性集合,.,索引文件,由如下形式的记录,(,称为,索引项,),组成,索引文件一般比原始文件小的多,两种基本索引,:,有序索引,:,搜索键按顺序存储,散列索引,:,搜索键被,“,散列函数,”,一致地分配到若干,“,桶,”,中,.,search-key,pointer,索引,索引评价度量,有效支持的存取类型,如,在某属性上具有特定值的记录,属性值落入指定范围的记录,存取时间,插入时间,删除时间,空间开销,对索引技术的评价是基于,:,有序索引,有序索引,:,索引项按搜索键值的顺序有序存储,.,主索引,:,顺序文件的记录顺序正是索引搜索键的顺序,.,也称为,聚簇索引,主索引的搜索键通常是主键,但并非必要,.,索引顺序文件,:,带有主索引的顺序文件,.,次级索引,:,索引搜索键的顺序与文件的记录顺序不同,.,也称为,非聚簇索引,选择索引：,目标是确定添加索引是否会改善系统性能。,索引选择的一般规则：,主键的属性一般都要建索引；,经常用于连接操作的列；,经常作为最大最小值等聚集函数的参数的属性,在表中经常按某列的顺序访问记录的列；,一般，属性值很少、在应用中很少用到、查询频率很低或文件中的记录数很少、小表等情况不用建索引。,索引类型,主索引（聚集索引）,/,辅助索引；,稠密索引,/,稀疏索引,唯一索引、非唯一索引,位图索引,Bitmap,索引,r,o,w,1,2,3,4,Name,Joe,Jane,John,James,M_Status,Single,Married,Divorced,Married,State,PA,CA,CA,PA,Gender,M,F,M,M,M_Status-IDX,Single,Married,Divorced,State-IDX,CA,PA,Gender-IDX,M,F,1 0 0 0,0 1 0 1,0 0 1 0,0 1 1 0,1 0 0 1,1 0 1 1,0 1 0 0,Select count(*)from customers,where M_Status=married AND,State=CA AND Gender=M,B,树索引和位图索引,B,树,适合高基数的列,更新关键字列的费用相对较低,使用,OR,谓词的查询效率低,对,OLTP,有用,位图,适合低基数的列,更新关键字列的费用非常昂贵,使用,OR,谓词的查询效率高,对数据仓库有用,2.7,数据库实施,用,DDL,定义数据库结构,组织数据入库,编制与调试应用程序,数据库试运行,应用程序开发的主要工作：,应用程序设计：应用程序设计主要包括事务设计和用户界面设计。,事务代表了现实世界的事件，事务设计包括事务使用什么数据，事务要做什么，事务的输出，事务的使用频度。事务有检索事务、更新事务、混合事务之分。,用户界面设计要易于掌握、操作直观。,应用程序开发,应用程序开发的主要工作：,应用程序编写,组织数据入库,应用程序的调试与试运行,数据库的运行和维护,（,1),数据库的转储和恢复,制定合理的转储计划，定期对数据库和日志文件进行备份。,（,2),数据库安全性、完整性控制,行政规范、权限管理、口令、跟踪及审计等来保证。,（,3),数据库性能的检测与改善,（,4),数据库的重组和重构,监控分析,监控分析实现方法不同：,（,1),自动监控机制,（,2),手动监控机制,监控对象不同：,（,1),对数据库构架体系的监控（空间基本信息、空间使用率与剩余空间大小、空间是否有自动扩展能力等）,（,2),对数据库性能的监控（数据缓冲、锁、回滚段、临时段使用情况、索引使用情况、等待事件等）,参数调整,1,外部调整,数据库并不是脱离于外部环境单独运行，外部环境出现瓶颈，再多的数据库调整没有帮助。主要有：,（,1)CPU,：,适当增加,CPU,的数量或将占有许多资源的进程停止；,（,2),网络：大量,SQL,数据在网络上传输会导致网速变慢。调整网络设备，可以一定程度上提高数据库性能。,2,调整内存分配,通过调整相关参数控制数据库的内存分配，可以在很大程度上改善数据库性能。,3,调整磁盘,I/O,通过令磁盘,I/O,最小化，减少磁盘上的文件竞争带来的瓶颈，改善数据库的性能。,4,调整竞争,（,1),控制连接到数据库的最大进程数,（,2),减少重做日志缓冲区竞争,（,3),减少回滚段竞争,（,4),减少调度进程竞争,。,