《教妹学数据库系统》（五）逻辑数据库设计与物理数据库设计

天才程序YUAN
256 阅读12分钟

hello大家好，今天我们来学习逻辑数据库设计与物理数据库设计。教妹学数据库，没见过这么酷炫的标题吧？“语不惊人死不休”，没错，标题就是这么酷炫。

我的妹妹小埋18岁，校园中女神一般的存在，成绩优异体育万能，个性温柔正直善良。然而，只有我知道，众人眼中光芒万丈的小埋，在过去是一个披着仓鼠斗篷，满地打滚，除了吃就是睡和玩的超级宅女。而这一切的转变，是从那一天晚上开始的。

从此之后，小埋经常让我帮她辅导功课。今天她想了解逻辑数据库设计与物理数据库设计。本篇教程通过我与小埋的对话的方式来谈一谈逻辑数据库设计与物理数据库设计。

博客还在持续更新中，希望大家能够关注我，一起学数据库系统。

实体型的转换

在这里插入图片描述

复合属性的转换

在这里插入图片描述

多值属性的转换

在这里插入图片描述

弱实体型的转换

  1. 转换规则
  • 弱实体型的名称
  • 弱实体型的属性集U属主实体型的主键----->关系的属性集
  • 弱实体型的部分集U属主实体型的主键----->关系的主键
  • 建立弱实体型关系到属主实体型关系的外键约束
    在这里插入图片描述

M:N二元联系型的转换

在这里插入图片描述

N:1二元联系型的转换

在这里插入图片描述

如果单独构建一个controls(Number,DNumber)，根据Number找DNumber，需要进行连接操作。

在这里插入图片描述

1:1 二元联系型的转换

在这里插入图片描述

二元自联系型的转换

在这里插入图片描述

标识联系型的转换

在这里插入图片描述

函数依赖

  1. 定义：设R(U)是属性集U上的关系模式，X⊆U，Y⊆U。对于R(U)的任意关系实例中的任意两个元组t1和t2，如果由t1[X]=t2[X]可以推出t1[Y]=t2[Y]，则称X函数决定Y，或Y函数依赖于X，记作X→Y。
    在这里插入图片描述
  2. 重要提示

  • 函数依赖是关系模式的所有关系实例上都成立的依赖关系，不能只根据某些关系实例来确定函数依赖

  • 函数依赖是语义范畴的概念，只能根据数据的语义来确定函数依赖。例如，Sname→Sno只有在学生不同名时才成立

  • 数据库设计者可以对现实世界作出强制规定。例如，规定学生不允许同名，从而使得Sname→Sno成立

函数依赖的类型

平凡(trivial)函数依赖：若Y⊆X，则X→Y是平凡函数依赖

在这里插入图片描述
在这里插入图片描述

函数依赖的公理系统

  1. 逻辑蕴含

设R(U,F)是一个关系模式，其属性集为U，函数依赖集为F。若在R的任意关系实例r中，函数依赖X→Y都成立(即对于r中任意两个元组t1和t2，若t1[X]=t2[X]，则t1[Y]=t2[Y])，则称F逻辑蕴含X→Y，记作F|=X→Y。

在这里插入图片描述
在这里插入图片描述

  1. Armstrong公理系统是正确(sound)且完备(complete)的

  • 正确性:使用Armstrong公理系统推出的任何函数依赖一定被F逻辑蕴含

  • 完备性:任何被F逻辑蕴含的函数依赖一定能够使用Armstrong公理系统推出

属性集的闭包

在这里插入图片描述

计算算法

1:i←0;X(0)←X{初始化}
2:repeat
	3:B←{A|V→W∈F,V⊆X(i),A∈W}{匹配函数依赖}
	4:X(i+1)←X(i)∪B{扩充闭包}
	5:i←i+1
6:untilX(i)=X(i−1)或X(i)=U{终止条件}
7:returnX(i)

在这里插入图片描述

等价函数依赖集

  1. Definition (函数依赖集的闭包)

设R(U,F)是一个关系模式，由F逻辑蕴含的全部函数依赖的集合叫做F的闭包(closure)，记作F+

  1. Definition(等价函数依赖集)

设F和G是关系模式R(U)上的两个函数依赖集，如果F+=G+，则F与G等价

  1. Definition(函数依赖集的覆盖)

设F和G是关系模式R(U)上的两个函数依赖集，如果G+⊆F+(即G中每个函数依赖都被F逻辑蕴含)，则称F覆盖G(FcoversG)

  1. Theorem：函数依赖集F与G等价，当且仅当F覆盖G且G覆盖F

等价函数依赖集的判定

  1. 方法1: 使用F+=G+来判定F与G等价，但是计算F+和G+的代价太高

  2. 方法2: 如果F覆盖G(F逻辑蕴含G中任意函数依赖)，且G覆盖F(G逻辑蕴含F中任意函数依赖)，则F与G等价

最小覆盖

关系模式R(U)的函数依赖集F的最小覆盖(canonicalcover)是满足下列3个条件的F的等价函数依赖集Fc

  1. (不存在冗余函数依赖)Fc中不存在函数依赖X→Y，使得Fc与Fc−{X→Y}等价

  2. (函数依赖左部不存在冗余属性)Fc中不存在函数依赖X→Y，有Z⊂X使得Fc与(Fc−{X→Y})∪{Z→Y}等价

  3. (函数依赖左部唯一)Fc中任意两个函数依赖的左部不相同

关系模式的范式

关系模式R(U)的函数依赖集F的最小覆盖(canonicalcover)是满足下列3个条件的F 的等价函数依赖集Fc

  1. (不存在冗余函数依赖) Fc中不存在函数依赖X→Y，使得Fc与Fc−{X→Y}等价

  2. (函数依赖左部不存在冗余属性) Fc中不存在函数依赖X→Y，有Z⊂X使得Fc与(Fc−{X→Y})∪{Z→Y}等价

  3. (函数依赖左部唯一) Fc中任意两个函数依赖的左部不相同

    在这里插入图片描述

第一范式(1NF)

如果关系模式R的每个属性都是不可分的，则称R为第一范式关系模式，记作R∈1NF

在这里插入图片描述

  • 问题1: 数据插入异常。现象:该插入的数据无法插入
  • 问题2: 数据删除异常。现象:不该删除的数据不得不删除
  • 问题3: 数据修改繁琐。现象:数据修改非常繁琐，容易出错
  • 问题4: 数据冗余。现象:存在大量冗余数据

主属性：候选键中的属性

第6章 物理数据库设计

设计步骤

  1. 设计步骤1:分析数据库负载

  2. 设计步骤2:选择关系数据库的存取方法

  3. 设计步骤3:设计关系数据库的物理存储结构

索引

  1. 构成
  • 索引键(indexkey):索引根据一组属性(索引键)来定位元组
  • 索引记录了元组的索引键值与元组地址的对应关系
  • 索引项(indexentry):索引中的(键值，地址)对
  • 索引中的索引项按索引键值排序
    在这里插入图片描述
  1. 分类
  • 主索引与二级索引，根据索引键是否是关系的主键
  • 唯一索引与非唯一索引，根据索引键值是否重复
  • 聚簇索引与非聚簇索引

聚簇索引：索引中存储的是元组本身。一个关系上只能有一个聚簇索引。 非聚簇索引：索引中存储的是元组地址

  1. 创建索引
  • 创建主索引
  • 创建二级索引
  • 创建唯一索引
  1. 删除索引

索引的数据结构

B+树

  1. B+树是大多数RDBMS所使用的索引结构
  2. B+树是一棵平衡多叉树，所有叶节点的深度都相同
  3. 索引项全部存储在B+树的叶节点中，并按索引键值排序存储

B+树索引的限制

  1. 必须从索引的最左属性开始查找
SELECT*FROMStudentWHERESage=19;
不能在该索引上执行这个查询
  1. 条件中不能包含表达式
SELECT*FROMStudent
WHERESname='Elsa'AND2020-Sage=2000;在索引上只能根据条件Sname=’Elsa’进行查找，在返回的元组上验证条件2020-Sage=2000
  1. 不能跳过索引中的属性
SELECT*FROMStudent
WHERESname='Elsa'ANDSsex='F';在索引上只能根据条件Sname=’Elsa’进行查找，在返回的元组上验证条件Ssex=’F’
  1. 如果查询中有关于某个属性的范围查询，则其右边所有属性都无法使用索引查找
SELECT*FROMStudentWHERESname='Elsa'ANDSageBETWEEN18AND20ANDSsex='F';在索引上只能根据条件Sname=’Elsa’ANDSageBETWEEN18
AND20进行查找，在返回的元组上验证条件Ssex=’F’

哈希索引

  1. 哈希索引是基于哈希表(hashtable)实现的
  2. 哈希表中的索引项是(索引键值的哈希值,元组地址)
  3. 哈希索引只支持对所有索引属性的精确匹配
    在这里插入图片描述

哈希索引的限制

  1. 哈希索引不支持部分索引属性匹配
SELECT*FROMStudentWHERESage=19;(不能使用索引)
  1. 哈希索引只支持等值比较查询(=,IN)，不支持范围查询
SELECT*FROMStudentWHERESname='Elsa'AND
Sage<19ANDSsex='F';(不能使用索引)
  1. 哈希索引并不是按照索引值排序存储的，所以无法用于排序
  2. 哈希索引存在冲突问题

索引的设计过程

设计技巧1：伪哈希索引

尽管有些存储引擎不支持哈希索引，但我们可以模拟哈希索引。

  • 优点: 查询速度快
  • 缺点: 仅支持等值查询，不支持范围查询I需要改写查询
  • 需要在数据更新时维护哈希值属性
    在这里插入图片描述

设计技巧2：前缀索引

  1. 当索引很长的字符串时，索引会变得很大，而且很慢。当字符串的前缀(prefix)具有较好的选择性时，可以只索引字符串的前缀
    在这里插入图片描述
  2. 缺点
  • 前缀索引不支持排序(ORDERBY)
  • 前缀索引不支持分组查询(GROUPBY)
  1. 例子
    在这里插入图片描述

设计技巧3:单个多属性索引vs.多个单属性索引

1.
在这里插入图片描述

  1. 多个单属性索引的缺点
  • 效率没有在单个多属性索引上做查询高
  • 索引合并涉及排序，要消耗大量计算和存储资源
  • 在查询优化时，索引合并的代价并不被计入，故“低估”了查询代价

设计技巧4: 索引属性的顺序

当不考虑排序(ORDERBY)和分组(GROUPBY)时，将选择性最高的属性放在最前面通常是好的，可以更快地过滤掉不满足条件的记录。

设计技巧5: 聚簇索引

  1. 优点

  • 相关数据保存在一起，可以减少磁盘I/O

  • 无需“回表”，数据访问更快

  1. 缺点

  • 设计聚簇索引是为了提高I/O密集型应用的性能，如果数据全部在内存中，那么聚簇索引就没什么优势了

  • 聚簇索引上元组插入的速度严重依赖于元组的插入顺序

  • 更新聚簇索引键值的代价很高，需要将每个被更新的元组移动到新的位置

  • 如果每条元组都很大，需要占用更多的存储空间，全表扫描变慢

    在这里插入图片描述

设计技巧5: 覆盖索引

如果一个索引包含(覆盖)一个查询需要用到的所有属性，则称该索引为覆盖索引(coverageindex)

避免不合理地使用索引

一方面要设计好的索引，另一方面要避免不合理地使用索引 若不合理地使用索引，则不但不会让查询变快，反而会使查询变慢

在这里插入图片描述

查询改写

查询优化器不能保证总是找到好的查询计划 用户不能给DBMS指定查询计划 用户只能通过添加索引或改写查询来影响查询优化器的决策 如果基于现有索引对查询进行改写就能获得好的查询计划，就没必要添加新的索引 如何改写查询取决于查询优化器的实现

在这里插入图片描述

物理存储结构的设计

  1. 尽量使用可以正确存储数据的最小数据类型

  • 原因:最小数据类型占用空间更少，处理速度更快

  • 例:INTEGER或INT占4字节；SMALLINT占2字节；TINYINT占1字 节；MEDIUMINT占3字节；BIGINT占8字节

  • 例:使用VARCHAR(5)和VARCHAR(100)来存储’hello’的空间开销是一 样的；但在排序时，MySQL会按照类型分配固定大小的内存块

  1. 尽量选择简单的数据类型

  • 原因:简单数据类型的处理速度更快

  • 例:用DATE、DATETIME等类型来存储日期和时间，不要用字符串I例:用整型来存储IP地址，而不是用字符串

  1. 若无需存储空值，则最好将属性声明为NOTNULL
  • 原因:含空值的属性使得索引、统计、比较都更复杂

标识符类型的选择

为标识符属性选择合适的数据类型非常重要

  • 标识符属性通常会被当作索引属性，频繁地进行比较- 标识符属性通常会被当作主键或外键，频繁地进行连接
  • 在设计时，既要考虑标识符属性类型占用的空间，还要考虑比较的 效率 整型:最好的选择
  • 占用空间少- 比较速度快
  • 可声明为AUTO- NCREMENT，为应用提供便利 ENUM和SET类型:糟糕的选择
  • MySQL内部用整型来存储ENUM和SET类型的值，占用空间少
  • 在比较时会被转换为字符串，比较速度慢 字符串型:糟糕的选择
  • 占用空间大- 比较速度慢

关系模式的设计

备注

  1. true，反过来不一定是false，可能是unkown。null表示不知道，它不是c语言的null。 null的比较会得到null的结果 null是无法比较的。

NULL就好像你和朋友出去吃饭，点菜时她说随便。

  • 对数据库要有约束，若不能为空则要声明
    在这里插入图片描述
  1. 回表

  • 数据库根据索引找到了指定的记录所在行后，还需要根据rowid再次到数据块里取数据的操作。 比如这样的执行计划，先索引扫描，再通过rowid去取索引中未能提供的数据，即为回表。

  • “回表”一般就是指执行计划里显示的“TABLE ACCESS BY INDEX ROWID”。 再例如，虽然只查询索引里的列，但是需要回表过滤掉其他行

avatar
文章
阅读
粉丝