5物理模式设计

13一月20231第5章:物理模式设计主要内容5.1物理模式设计简介5.2索引的原理与设计原则13一月20232数据库系统概念----导论5.1数据库的三级模式物理模式数据的存储结构，研究数据如何存储也称作称存储模式、内模式

逻辑模式全体数据的逻辑结构又称作全局模式外模式具体用户看到的数据的逻辑结构又称作子模式、用户模式13一月20233数据库系统概念----导论5.1数据库的三级模式关系示意图app1app2外模式1外模式2外模式…逻辑模式物理模式hdapp3app……….三级模式及两级映像示意图物理映像逻辑映像5.1物理模式设计的目标物理模式设计目标在硬件及系统环境限制下，为逻辑模式的实现提供最理想的支持主要是性能支持13一月202345.1物理模式设计在工程中的位置数据的物理独立性当物理模式发生变化时，通过调整物理/逻辑模式映像，可以保持逻辑模式不变，进而应用程序不需改变；这种数据和程序的独立性，称为数据的物理独立性。物理模式设计在工程中的位置在逻辑模式设计完成后、程序开发之前进行可以在系统上线实施调整在系统运行期间，根据实际情况继续调整13一月202355.2索引的原理与设计原则本节要点5.2.1数据访问类型分析5.2.2索引的基本工作原理介绍5.2.3顺序文件与主索引5.2.4辅助索引5.2.5常见的索引结构5.2.6索引的相关说明5.2.7索引设计的原则13一月2023613一月202375.2.1:数据访问类型分析数据访问的类型查询修改据调查：数据查询的访问数量远大于数据修改的数量数据查询的类型特定值查询

例如：select*fromswheresno=‘2009012689’值范围查询

例如：select*fromscwherescore>=60andscore<70’13一月202385.2.2索引的基本工作原理许多查询只涉及文件中的少量记录找出计算机系的所有女学生找出学号为s01的学生姓名只涉及少量记录查询的实现读取所有记录并一一检查：非常低效理想目标：快速甚至直接定位查询记录索引为了实现快速数据定位，对数据文件设计的附加结构与图书馆中图书索引的原理相同13一月202395.2.2:索引设计基本原理示意s9999……s0002s0001……b5826t1t2t5t9997t9998t9999b1…b5555………select*fromswheresno=‘s4567’;全表扫描，o(n)，n=10,000，平均读入1700磁盘块索引扫描，o(logn)，n=10,000,除去索引块，只需读入1磁盘块，而索引所占空间小的多s:snosnamedeptt1s0001甲计t2s0002乙软……………s9999丁文索引5.2.3:顺序文件与主索引数据块可用的磁盘空间被划分为很多块块是磁盘空间输入和输出的最小单位索引记录（索引项）由一个搜索码值和指向具有该搜索码值的一个或多个记录的指针构成指向记录的指针包括磁盘块的标识和标识磁盘块内记录的块内偏移量顺序文件元组在块中按照搜索码的升序存储主索引（聚集索引）顺序文件中搜索码对应的索引有主索引的顺序文件称作索引顺序文件13一月20231013一月2023115.2.3:顺序文件与主索引示意顺序文件与主索引示意主索引可以采用稀疏索引稀疏索引只为搜索码的某些值建立索引主索引一般可以驻留内存特定值查询、值范围查询：非常高效一个表至多有一个主索引13一月2023125.2.4:辅助索引辅助索引（非聚集索引）文件中元组物理存储顺序与搜索码顺序不同的索引一个表可以有多个辅助索引辅助索引必须采用稠密索引13一月2023135.2.5常见的索引结构索引文件的主要组织类型散列索引顺序索引散列索引将值平均分布到若干散列桶中能很好地支持特定值查询不能有效支持值范围查询顺序索引基于值的大小顺序组织的索引能很好地支持特定值查询、值范围查询典型代表：b+树索引，是目前主流的索引结构5.2.5:b树简介一棵m序b树是一颗满足下列条件的树：1、每个结点至多有m个孩子；2、除根结点和叶结点外，其它每个结点至少有

m/2个孩子；3、根结点至少有两个孩子；4、所有叶结点在同一层，叶结点不包含任何关键字信息；5、有k个孩子的非叶结点恰好包含k-1个关键字。13一月2023145.2.5:b树示意一个3序b树示意13一月2023155.2.5:b树查询的高效性分析每个结点包含1000个关键字，故在第三层上有100多万个叶结点，这些叶节点可容纳10亿多个关键字。通常根结点可始终置于内存中，因此在这棵b树中查找任一关键字至多只需二次访问外存。13一月2023165.2.5:b+树简介b+树是一种b树的变形一棵m阶的b+树和b树的差异在于：所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶结点依关键字的大小从小到大顺序链接。非叶结点仅具有索引作用，结点中仅含有其子树中最小关键字。(b树键值只出现一次)叶结点的关键字可以多于m，也可以少于m。13一月2023175.2.5:一个b+树示意一个3序b+树示意13一月20231813一月2023195.2.6:索引

的相关说明索引的有关说明可以动态地定义索引，即可以随时建立和删除索引不允许用户在数据操作中引用索引，索引是否使用、如何使用、如何维护完全由系统决定；一个表上可建多个索引。索引可以提高查询效率，但索引过多耗费空间，且降低了插入、删除、更新的效率，并且会增加系统选择索引的时间代价有些dbms自动建立以下列上的索引

primarykeyunique13一月2023205.2.6:索引

的相关说明索引的定义格式create[unique/distinct][cluster]index

索引名on

表名(列名[asc/desc][,列名asc/desc]]…)unique（distinct）：唯一性索引，不允许表中不同的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。系统并拒绝违背唯一性的插入、更新

cluster：聚集索引，表中元组按索引项的值排序并物理地聚集在一起。一个基本表上只能建一个聚集索引

asc/desc：索引表中索引值的排序次序，缺省为asc13一月2023215.2.6:索引

的相关说明示例：

createclusterindexs-indexons（sn）索引的删除格式

dropindex

索引名索引连接(index-join)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组13一月2023225.2.7:索引设计的原则索引建立原则：不必为小表创建索引为表的主码建立索引为检索数据时大量使用的列建立辅助索引（如name）

若经常基于外码访问数据，则为该外码建立辅助索引为经常有如下情况的列建立辅助索引：

选择或连接条件;orderby;groupby;其他含有排序的操作

(如union或

distinct)

慎重为经常被更新的列或表建立索引如果查询将检索表中记录的大部分（如25%)，即使表很大，也不建立索引。这时查询整表要比用索引查询更有效（选择率）13一月2023235.2.7:索引设计的原则索引与查询优化有些dbms允许检查优化器的策略，从而可以分析改善查询的性能；oracleexplainplan，db2explain，access性能分析器查询优化器依赖于存储在系统目录中的数据库统计来选择最佳策略，每当创建索引时，dbms自动将此索引增加到系统目录中。但是，系统目录中与表和索引相关的统计信息的更新，需要数据库用户自己使用dbms提供的工具完成索引的删除如果维护索引可能会降低重要的更新事务，就考虑删除索引如果大量的记录被插入到有索引的表中，可以先删除索引，再执行插入，然后重建索引（若增加表大小超过10%）13一月202324数据库系统概念----e-r