考点3 数据库物理设计

考点3 数据库物理设计

一、物理设计概述

• 通过数据库概念设计和逻辑设计已得到规范化的关系模式。数据库物理设计的目的是将数据的逻辑描述转换为实现技术规范，其目标是设计数据存储方案，以便提供足够好的性能并确保数据库数据的完整性、完全性和可恢复性。
• 在这个阶段，将根据数据库中存储的数据量、用户对数据库的使用要求和使用方式，选择数据存储方案以加快数据检索速度。
• 在物理设计时需要了解不同文件组织方式、索引技术及其使用方法。

二、数据库的物理结构

• 数据库中的应用数据是以文件形式存储在外设存储介质(如磁盘)上的，文件在逻辑上被组成记录的序列–每个DB文件可以看作是逻辑记录的集合。
• 物理文件可以看作是由存放文件记录的一系列磁盘块组成的，文件的逻辑记录与磁盘块间的映射关系是由操作系统或DBMS来管理的。
• 从数据库物理结构角度需要解决的问题：文件的组织、文件的结构、文件的存取、索引技术。

三、索引

1、索引技术

• 索引的概念类似于图书中使用的术语 “索引” 。
• 索引技术（Indexing）是一种快速数据访问技术，它将一个文件的每个记录在某个或某些领域上的取值与该记录的物理地址直接联系起来，提供一种根据记录域的取值快速访问文件记录的机制。
• 索引技术关键是建立记录域取值到记录的物理地址间的映射关系(这种映射关系称为索引(Index)。
• 索引示意图如下所示:

2、索引技术分类

• 根据索引的实现方式，索引技术被分为两大类:：有序索引(Ordered Index)、散列索引(Hash)。

(1) 有序索引(Ordered Index)

数据文件和索引文件是有序索引技术中的两个主体，数据文件也被称为索引文件或者主文件。
有序索引作为基于索引文件的索引技术，需要考虑的两个关键问题：
① 如何组织索引文件中的索引记录；
② 如何从索引文件出发，访问数据文件中的数据记录。

索引文件建立方法：
首先，选定数据文件中的某个或某些记录域作为查找码；
然后，在建立起数据记录在查找码上的取值与该记录的物理地址间的映射关系，组成索引项。
最后，索引文件根据某个特定的查找码值的升序或降序存储索引记录，并且也组织为顺序文件。

索引建立在查找码上，对一个数据文件需要从几个方面去查询文件记录，可以定义多个查找码，针对每个查找码建立相应的索引文件 ---- 一个数据文件可以有多个查找码和多个索引文件。

数据文件中存储了关系模式为R(A,B,S.,D)的关系表的元组数据。

有序索引 ---- 类别及特点

• 聚集索引(Clustering Index)和非聚集索引(Non- clustering Index)
如果数据文件中数据记录的排列顺序与索引文件中索引项的排列顺序相一致 ,或者说索引文件按照其查找码指定的顺序与数据文件中数据记录的排列顺序相一致，则称该索引文件为聚集索引；否则，称该索引文件为非聚集索引。

注意：
聚集索引确定表中数据的物理顺序。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。非聚集索引与课本中的目录类似，数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置，一个表可以有多个非聚集索引。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

• 稠密索引和稀疏索引
如果数据文件中的每个查找码值在索引文件中都对应一个索引记录，则该索引称为稠密索引。如果只是一部分查找码的值有对应的索引记录，则该索引称为稀疏索引。

• 主索引和辅索引
在数据文件的主码属性集上建立的索引称为主索引;在数据文件的非主属性上建立的索引称为辅索引。

• 唯一索引
唯一索引可以确保索引列不包含重复的值。在多列唯一索引的精况下，可以确保索引列中每个值的组合都是唯一的。

• 单层索引和多层索引
单层索引也称为线性索引，其特点是索引项根据键值在索引文件中顺序排列，组织成一维线性结构,每个索引项直接指向数据文件中的数据记录。
多层索引可以建立多层树型索引结构来快速定位大数据量文件中的数据记录。多层索引的典型例子是数据库系统中广泛应用的B树和B+树索引。

当数据文件很大时，即使采用稀疏索引建成的索引文件也会很大，当需要读取某个索引项时，必须读取磁盘块，导致对索引文件本身的访问效率非常低。为解决这个问题，可以对索引文件中的索引项本身再建立一级稀疏索引，组成二层索引结构，用于快速查找索引项。

聚集、稠密、辅索引

非聚集、稠密、主索引

(2) 散列索引(Hash)

• 散列技术，也称为哈希（Hash）索引机制。
• 散列技术利用一个散列函数（HashFunction）实现记录域取值到记录物理地址间的直接映射关系。
• 记录域就是查找码，也称为散列函数的散列域或排序域。

四、数据库物理设计

1、物理设计内容

• 数据库物理结构设计是在具体的硬件环境、操作系统和DBMS约束下，根据数据库逻辑设计结果设计合适的数据库物理结构。其目标是得到存储空间占用少、数据访问效率高和维护代价低的数据库物理模式。

• 数据库物理设计环节
① 数据库逻辑模式描述
② 文件组织与存取设计
③ 数据分布设计
④ 确定系统配置
⑤ 物理模式评估

2、数据块逻辑模式描述

• 数据库逻辑设计产生了数据库的逻辑结构，其中有数据库的关系模式、关系模式上的完整性约束和面向具体应用的业务规则。这些内容都是独立于DBAS所采用的具体目标DBMS平台的。
• 数据库物理设计需要根据数据库逻辑结构信息设计目标DBMS平台可支持的关系表（这里称为基本表）的模式信息，这些模式信息代表了所要开发的具体目标数据库的结构。

逻辑模式描述主要设计内容：
• 面向目标数据库描述基本表和视图
• 采用目标DBMS所支持的建表语法，描述基本表及满足应用要求的完整性约束。
• 设计基本表业务规则
• 利用目标DBMS提供的完整性控制机制设计基本表应遵守的面向应用的完整性约束。

3、DB文件组织与存取设计

• 为了进行有效的数据库文件组织和存取路径设计，必须分析和理解数据库事务的数据访问特性。

• 事务分析步骤：
① 使用事务-基本表交叉引用矩阵
② 估计各事务的执行频率（单位时间内事务的执行次数）
③ 对每张基本表，汇总所有作用于该表上的各事务的操作频率信息。

• 事务-基本表交叉引用矩阵例子
其中事务1对基本表1和基本表3执行了插入和更新操作，对基本表2执行了读操作。

• 根据事务数据访问特性分析结果，可以对基本表设计成更为有效的文件组织和索引方式，
以下是几种数据库文件结构：
① 堆文件
适合表中数据量很少，且插入、删除、更新等操作非常频繁。
② 顺序文件
支持基于查找码的顺序访问，也支持快速的二分查找，适合用户的查询条件定义在查找码上。
③ 聚集文件
适合某些频繁执行且需要进行多表连接操作的查询。
④ 索引文件
适合定义在数据量基本表上，基于查找码的等值查询、范围查询、模糊查询和部分查询。
⑤ 散列文件
适合用户查询是基于散列域值的等值匹配，特别是访问顺序是随机。
不适合：基于散列域值的非精确查询 & 基于非散列域进行的查询。

• 设计存储路径时考虑的索引设计问题
索引可以提高文件存取速度，改善大数据量文件的访问性能。但索引是由DBMS管理的，它的建立和维护需要一定的系统开销，数据的插入、删除或更新也可能引起索引的重新调整。因此，索引加快了数据查询速度，却减慢了数据更新速度。索引本身还会占用一定的存储空间。因此，必须权衡考虑索引的优点和索引的维护代价。

• 基本表建立索引的原则
对于经常需要进行查询、连接、统计操作且数据量大的基本表可考虑建立索引。
一个基本表上除了可以建立一个聚集索引外，还可以建立多个非聚集索引。
对于经常执行插人、删除、更新操作或小数据量的基本表应尽量避免建立索引。
索引可以由用户根据需要随时创建或删除，以提高数据查询性能。

• 基本表可在一些属性上建立索引
表的主码；
在WHERE查询子句中引用率较高的属性；
参与连接操作的属性；
在Order By子句、Group By子句中出现的属性；
在某一范国内频繁搜索的属性；
如果在WHERE子句中同时包含一个表中的多个属性；
当一个属性有相对较多的不同值时，使用索引有明显的作用；
对于包含大量空值的属性建立索引时要仔细考虑

4、数据分布设计

• 不同类型数据的物理分布
数据库系统中不仅有组织为基本表的应用数据，还有索引、日志、数据库备份数据等其他类型数据。各种数据在系统中的作用不同，使用频率也不一样，根据实际使用情况存放在合适的物理介质上，以优化系统I/O性能。
① 数据库备份文件、日志文件备份数据用于故障恢复，使用频率低，而且数据量很大，可以存储在磁带中。
② 应用数据、索引和日志则使用频繁，要求的响应时间短，必须放在支持存取的磁盘存储介质上。

• 应用数据的划分与分布
为改善大数据量基本表的访问性能，将基本表划分为若干分区，各分区数据分别存储在不同位置的磁盘上，并采用不同物理组织方式，对基本表的划分可依据以下不同原则:

基本表划分的原则：
① 根据数据库中的使用特征划分
可以根据数据使用的频繁将基本表分为频繁使用分区和非频繁使用分区，分别存储在不同的磁盘上。对于频繁使用分区中的数据可以考虑建立B+树等多层索引，而对非频繁使用分区中的数据可以不建或只建立单层索引。
② 根据时间、地点划分
对于同一时间点生产的商品或者同一地点的商品归为同一个分区中。
③ 分布式数据库系统中数据划分
DDBS采用水平划分或垂直划分两种方法。水平划分将一张基本表划分为多张具有相同属性、结构完全相同的子表，子表包含的元组是基本表中元组的子集；垂直划分是将一张基本表划分为多张子表，每张子表包含的属性是原基本表的子集。

• 派生属性数据分布
基本表中的派生属性是指该属性的取值可根据表中其他属性的取值唯一确定。对带有派生属性的基本表可以采用如下两种实现方式。
① 将派生属性作为基本表中单独的一列(称为派生列)，如下表所示。

② 派生属性不出现在基本表中，如下表所示。
用year(current_time)-year(Birthdate) as age 来计算年龄

• 关系模式的去规范化
在数据库物理设计阶段，可以根据实际需要对数据库中某些3NF、BCNF模式考虑是否可以降低其规范化程度，提高查询效率。
在设计基本表时，需要在模式规范化与查询效率之间折中权衡。

5、其他物理设计环节

• 确定系统配置
数据库应用系统中的DBMS和操作系统产品提供了一些与存储分配有关的配置参数，供设计人员和DBA对数据库进行配置和优化。不同的DBMS和操作系统的存储分配参数各不相同，一般包括:数据库配置参数磁盘块使用参数，内存缓冲区参数、时间片大小，装填印制，锁的数目等。

• 物理模式评估
评价物理数据库的方法完全依赖于所选用的DBMS，主要是从定量估算各种方案的存储空间、存取时间和维护代价入手，对估算结果进行权衡和比较，选择出一个较优的合理的物理结构。如果设计不符合用户需求，则要改进。