数据库的物理结构设计

2.6 数据库物理结构设计
•数据库在物理设备上的存储结构与存取方法称为数
据库的物理结构，它依赖于给定的计算机系统。

•为一个给定的逻辑数据模型选取一个最适合应用环
境的物理结构的过程，就是数据库的物理设计。

•充分了解应用环境，详细分析要运行的事务，以获得选择物理数据库设计所需参数
•充分了解所用RDBMS的内部特征，特别是系统提供的存取方法和存储结构
•关系数据库物理设计的内容
–为关系模式选择存取方法(建立存取路径)
–设计关系、索引等数据库文件的物理存储结构
•物理数据库设计所需参数
－数据库查询事务（查询的关系，查询条件所涉及的属性，连接条件所涉及的属性，查询的投影属性）－数据更新事务（被更新的关系，每个关系上的更新操作条件所涉及的属性，修改操作要改变的属性值）－每个事务在各关系上运行的频率和性能要求
其他需考虑的问题：
目标DBMS支持的特性、功能和选项；
主机计算机系统的特性和能力；
磁盘存储配置；
数据量。

数据库物理设计步骤：
1.数据库逻辑模式调整
2.文件组织与存取设计
3.数据分布设计
4.安全模式设计
5.确定系统配置
6.物理模式评估
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产品一般都提供了一些存储分配参数–同时使用数据库的用户数
–同时打开的数据库对象数
–使用的缓冲区长度、个数
–时间片大小
–数据库的大小
–装填因子
–锁的数目
–等等
➢系统都为这些变量赋予了合理的缺省值。

但是这些值不一定适合每一种应用环境，在进行物理设计时，需要根据应用环境确定这些参数值，以使系统性能最优。

➢在物理设计时对系统配置变量的调整只是初步的，在系统运行时还要根据系统实际运行情况做进一步的调整，以期切实改进系统性能。

6物理模式评估
主要从定量估算各种方案的存储空间、存取时间和维护代价入手，对估算结果进行权衡、比较，选择出一个较优的合理的物理结构。

•聚簇
–为了提高某个属性（或属性组）的查询速度，把这个或这些属性（称为聚簇码）上具有相同值的元组集中存放在连续的物理块称为聚簇
–许多关系型DBMS都提供了聚簇功能
–聚簇存放与聚簇索引的区别
聚簇索引：索引项顺序与表中元组的物理顺序一
致。

聚簇的用途
1. 大大提高按聚簇属性进行查询的效率
2. 节省存储空间
•聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在
一组中存一次就行了
聚簇的局限性
1. 聚簇只能提高某些特定应用的性能
2. 建立与维护聚簇的开销相当大
•对已有关系建立聚簇，将导致关系中元组移动其物理存储位置，并使此关系上原有的索引无效，
必须重建。

•当一个元组的聚簇码改变时，该元组的存储位置也要做相应移动。

•聚簇的适用范围
1.既适用于单个关系独立聚簇，也适用于多个关系组合聚
簇
2. 当通过聚簇码进行访问或连接是该关系的主要应用，与
聚簇码无关的其他访问很少或者是次要的时，可以使用
聚簇。

当SQL语句中包含有与聚簇码有关的ORDER BY，
GROUP BY，UNION，DISTINCT等子句或短语时，
使用聚簇特别有利，可以省去对结果集的排序操作
•索引机制用于加速对所需数据的存取.–例如, 图书馆中的作者目录
•搜索键–用来在文件中查找记录的属性或属性集合.•索引文件由如下形式的记录(称为索引项)组成
•索引文件一般比原始文件小的多•两种基本索引:
–有序索引: 搜索键按顺序存储
–散列索引:搜索键被“散列函数”一致地分配到若干“桶”中.
search-key
pointer
索引
索引评价度量
对索引技术的评价是基于:
•有效支持的存取类型, 如
–在某属性上具有特定值的记录
–属性值落入指定范围的记录•存取时间
•插入时间
•删除时间
•空间开销
有序索引
•有序索引: 索引项按搜索键值的顺序有序存储.
•主索引: 顺序文件的记录顺序正是索引搜索键的顺序.
–也称为聚簇索引
–主索引的搜索键通常是主键, 但并非必要.
–索引顺序文件:带有主索引的顺序文件.
•次级索引:索引搜索键的顺序与文件的记录顺序不同.
–也称为非聚簇索引
选择索引：
目标是确定添加索引是否会改善系统性能。

索引选择的一般规则：
•主键的属性一般都要建索引；
•经常用于连接操作的列；
•经常作为最大最小值等聚集函数的参数的属性
•在表中经常按某列的顺序访问记录的列；
•一般，属性值很少、在应用中很少用到、查询频率很低或文件中的记录数很少、小表等情况不用建索引。

索引类型
•主索引（聚集索引）/辅助索引；
•稠密索引/稀疏索引
•唯一索引、非唯一索引
•位图索引
Bitmap索引r
o w 1 2 3 4Name
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)减少调度进程竞争。

查询优化
（1)合理使用索引
一个列的值域很大时，应为其建立索引；
如果待排序的列有多个，应在这些列上建立复合索引；当数据库表更新大量数据后，删除并重建索引可以提高查询速度；
可以使用系统工具来检察索引的完整性，必要时进行修复。

（2）避免或简化排序
通常，在运行order by 和group by等SQL语句时，会涉及排序操作，应当简化或避免对大型表进行重复的排序，磁盘排序的开销很大。

当能够利用索引自动以适当的顺序产生输出时，优化器可以避免不必要的排序步骤。

有影响的：
由于现有索引的不足，导致排序时索引中不包括一个或几个待排序的列；
order by 或group by子句中列的次序和索引的次序不同；排序的列来自不同的表。

可以正确增建索引，合理合并数据库表等。

3 消除对大型表行数据的顺序存取
嵌套查询中，对表的顺序存取对查询效率可能产生影响；
可对连接的列进行索引，可使用并集来避免顺序存取。

4 避免相关子查询
查询嵌套层次越多，效率越低；若子查询不可避免，要在子查询中过滤掉尽可能多的行；
5 避免困难的正规表达式
避免matches和like关键字的正规表达式。

6 使用临时表加速查询
把表的一个子集进行排序并创建临时表，有时能加速查询；
7 用排序来取代非顺序磁盘存取
8 不充分的连接条件
使用内连接，不用左右连接
9 存储过程
对于频繁使用的SQL语句建议使用存储过程，存储过程不需要编译就能直接执行。

10 不要随意使用游标
游标会占用较多的系统资源，尤其是对于大规模并发量的情况下，很容易使得系统资源耗尽而崩溃。

11 事务处理
在保证数据库一致性的前提下，将频繁操作的多个可分割的处理过程放入到多个存储过程当中，会提高系统的响应速度。

思考题：
１就自己熟悉的业务系统给出数据需求，并进行数据库的各阶段模式设计。

需求分析以数据字典和数据流图的形式表述(可用用例，用例说明），数据库模式设计报告的内容应包括数据库模式的设计（ER图，表）与分析.
2 设公司的工程项目管理子系统需要对以下信息进行管理：
工程项目：有项目编号、项目名称和项目地址等属性；零件：有零件编号、零件名称、颜色、重量等属性；供应商：供应商编号、供应商名称、地址等属性。

供应商供给工程项目一定数量的零件。

完成以下设计：
（１）画出此子系统的E_R图；
（２）将E_R图转换成相应的关系模式，使得每个关系模式至少是3NF；
指出每个关系模式的主键。

3已知关系模式R<U,F>,U={A,B,C,D},F={A→C,C→A, B→AC,D→AC}，完成以下各小题要求：
(1)给出R的所有候选键；
(2)判断R最高属于第几范式，为什么？。