[MySQL进阶]——数据库的设计规范1

笔记整理自 【宋红康】MySQL数据库（mysql安装/基础/高级/优化），还参考网上的其他技术文章作为补充，具体参考链接在文末

文章目录

💻范式

简介

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结 构需要满足的某种设计标准的 级别 。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

范式都包括哪些

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式（1NF）、第二范式 （2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美 范式）。

我们平常一般只用到前面的三大范式

键和相关属性的概念

**超键(super key)**：在关系中能唯一标识元组的属性集称为关系模式的超键

候选键(candidate key): 不含有多余属性的超键称为候选键

**主键(primary key)**：用户选作元组标识的一个候选键程序主键

**外键(foreign key)**：如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键，则该属性集是关系模式R1的外键

🌰举例：

这里有两个表

球员表(player) ：球员编号 | 姓名 | 身份证号 | 年龄 | 球队编号

球队表(team) ：球队编号 | 主教练 | 球队所在地

• 超键 ：对于球员表来说，超键就是包括球员编号或者身份证号的任意组合，比如（球员编号） （球员编号，姓名）（身份证号，年龄）等。
• 候选键 ：就是最小的超键，对于球员表来说，候选键就是（球员编号）或者（身份证号）。
• 主键 ：我们自己选定，也就是从候选键中选择一个，比如（球员编号）。
• 外键 ：球员表中的球队编号。
• 主属性 、 非主属性 ：在球员表中，主属性是（球员编号）（身份证号），其他的属性（姓名） （年龄）（球队编号）都是非主属性。

主属性和主键

主属性：指主键列，即主键由一列构成
主键定义:能够唯一标识一个元组的属性或属性集，即可以由多列组成。
在教学中，大多实例都是主键由一列构成，所以也可以简单地说主属性与主键没有什么区别。

第一范式(1st NF)

第一范式是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性这就是第一范式的基本概念。(字段不能再分)

🌰例子

在一个表里有一个“电话”字段，其值既有手机号，又有座机号 那么这样就不符合第一范式的要求了。此时应该将“电话”拆分为“手机”跟“座机” 这样一来就变成了一个符合第一范式的数据表了。

第二范式(2nd NF)

第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或行必须可以被惟一的区分。为实现区分，通常需要为表加上一个列，以存储各个实例的惟一标识，要求实体的属性完全依赖于主键。

⚠️要讨论一个表符不符合第二范式的前提是，这个表的主键为组合主键，如果不是组合主键那就谈不上符不符合第二范式了。

总结：不能部分依赖，就是说当一个表有组合主键时，其他非主键的字段必须完全依赖于主键。

🌰反例：

比赛表 player_game ，里面包含球员编号、姓名、年龄、比赛编号、比赛时间和比赛场地等属性，这 里候选键和主键都为（球员编号，比赛编号），我们可以通过候选键（或主键）来决定如下的关系：

(球员编号, 比赛编号) → (姓名, 年龄, 比赛时间, 比赛场地，得分)

但是这个数据表不满足第二范式，因为数据表中的字段之间还存在着如下的对应关系：

(球员编号) → (姓名，年龄)
(比赛编号) → (比赛时间, 比赛场地)

对于非主属性来说，并非完全依赖候选键。姓名年龄并不依赖于比赛时间跟比赛场地。

❓这样会产生怎样的问题呢？

1. 数据冗余 ：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。一个比赛 也可能会有 n 个球员参加，比赛的时间和地点就重复了 n-1 次。
2. 插入异常 ：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没 法插入。
3. 删除异常 ：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删 除掉。
4. 更新异常 ：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调 整，否则就会出现一场比赛时间不同的情况。

💡1NF 告诉我们字段属性需要是原子性的，而 2NF 告诉我们一张表就是一个独立的对象，一张表只 表达一个意思。

第三范式(3rd NF)

第三范式是要符合第二范式，第三范式就是一个数据表中不能存在其他数据表中的非主键字段，也就是说如果存在其他表中的字段，就一定得是那个表的主键。

🌰举例：

• 部门信息表 ：每个部门有部门编号（dept_id）、部门名称、部门简介等信息。
• 员工信息表 ：每个员工有员工编号、姓名、部门编号。

列出部门编号后就不能再将部门名称、部门简介 等与部门有关的信息再加入员工信息表中。 如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。

总结：主键以外的字段必须依赖主键 而不能依赖其他字段。

💡符合3NF后的数据模型通俗地讲，2NF和3NF通常以这句话概括：“每个非键属性依赖于键，依赖于 整个键，并且除了键别无他物”。

💽反范式化

规范化 vs 性能

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要
2. 在数据规范化的同时 , 要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4. 通过在给定的表中插入计算列，以方便查询

🌰举例：

员工的信息存储在 employees 表 中，部门信息存储在 departments 表 中。通过 employees 表中的 department_id字段与 departments 表建立关联关系。如果要查询一个员工所在部门的名称：

select employee_id,department_name
from employees e join departments d
on e.department_id = d.department_id;

如果经常需要进行这个操作，连接查询就会浪费很多时间。可以在 employees 表中增加一个冗余字段 department_name，这样就不用每次都进行连接操作了。

反范式的新问题

• 存储 空间变大 了
• 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致
• 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源
• 在 数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加 复杂

反范式的适用场景

当冗余信息有价值或者能 大幅度提高查询效率 的时候，我们才会采取反范式的优化。

在现实生活中，我们经常需要一些冗余信息，比如订单中的收货人信息，包括姓名、电话和地址等。每 次发生的 订单收货信息 都属于 历史快照 ，需要进行保存，但用户可以随时修改自己的信息，这时保存这 些冗余信息是非常有必要的。

反范式优化也常用在 数据仓库 的设计中，因为数据仓库通常 存储历史数据 ，对增删改的实时性要求不 强，对历史数据的分析需求强。这时适当允许数据的冗余度，更方便进行数据分析。

🖱️实战案例

案例介绍

商超进货系统中的进货单表进行剖析：

进货单表：

❓这个表中的字段很多，表里的数据量也很惊人。大量重复导致表变得大，效率极低。如何改造？

⚠️在实际工作场景中，这种由于数据表结构设计不合理，而导致的数据重复的现象并不少见。往往是系统虽然能够运行，承载能力却很差，稍微有点流量，就会出现内存不足、CUP使用率飙升的情况，甚至会导致整个项目失败。

迭代1次：考虑1NF

第一范式要求：所有的字段都是基本数据字段，不可进一步拆分。这里需要确认，所有的列中，每个字段只包含一种数据。

这张表里，我们把"property"这一字段，拆分成"specification（规格）“和"unit（单位）”，这2个字段如下：

送代2次：考虑2NF

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所字段，都必须完全依赖主键，不能只依赖主键的一部分。

• 第1步，就是要确定这个表的主键。通过观察发现，字段"listnumber（单号）"+"barcode（条码）"可以唯一标识每一记录，可以作为主键。
• 第2步，确定好了主键以后，判断哪些字段完全依赖主键，哪些字段只依赖于主键的一部分。把只依赖于主键-部分的字段拆分出去，形成新的数据表。

首先，进货单明细表里面的"goodsname（名称）"“specification（规格）”“unit（单位）”，这些信息是商品的属性，只依赖于"barcode（条码）"，不完全依赖主键，可以拆分出去。

我们把这3个字段加上它们所依赖的字段"barcode（条码"，拆分形成一个新的数据表“商品信息表

这样一来，原来的数据表就被拆分成了两个表。

1、商品信息表

2、进货单表

此外，字段"supplierid（供应商编号）”"suppliername（供应商名称）“"stock（仓库）“只依赖于"listnumber（单号）”，不完全依赖于主键。

所以，我们可以把"supklierid"”suppliername"“stock"这3个字段拆出去，再加上它们依赖的字段"listnumber（单号）"，就形成了一个新的表“进货单头表”。剩下的字段，会组成新的表，我们叫它“进货单明细表”。

原来的数据表就拆分成了3个表。

1、商品信息表

2、进货单明细表

3、进货单头表

现在，我们再来分析一下拆分后的3个表，保证这3个表都满足第二范式的要求。

第3步，在"商品信息表"中，字段"barcode"是有可能存在重复的，比如，用户门店可能有散装称重商品和自产商品，会存在条码共用的情况。所以，所有的字段都不能唯一标识表里的记录。

这个时候，我们必须给这个表加上一个主键，比如说是自增字段"itemnumber"

现在，我们就可以把进货单明细表里面的字段"barcode"都替换成字段"itemnumber"，这就得到了新的如下表

迭代3次：考虑3NF

我们的进货单头表，还有数据冗余的可能。

按照第三范式原则进行拆分

反范式化：业务优先的原则

在进货单明细表中，quantity * importprice = importvalue，“importprice” 、”quantity"和"importvalue"可以通过计算任意两个计算出第三个来，这就存在冗余字段。

如果严格按照第三范式的要求，现在我们应该进行进一步的优化。优化的办法是删除其中一个字段，只保留另外2个，这样就没有冗余数据了。

❓可是，真的可以这样做吗？要回答这个问题，我们就要先了解下实际工作中的业务优先原则

所谓的业务优先原则，就是指一切以业务需求为主，技术服务于业务。完全按照理论的设计不一定就是最优，还要根据实际情况来决定。这里我们就来分析一下不同选择的利与弊。

对于quantity * importprice = importvalue，看起来"importvalue"似乎是冗余字段，但并不会导致数据不一致。

可是，如果我们把这个字段取消，是会影响业务的。
因为有的时候，供货商会经常进行一些促销活动，按金额促销，那他们拿来的进货单只有金额，没有价格。而"importprice"反而是通过"importvalue"除"quantity"计算出来的，经过四舍五入，会产生较大的误差。这样日积月累，最终会导致查询结果出现较大偏差，影响系统的可靠性。

所以，本着业务优先的原则，在不影响系统可靠性的前提下，我们可以保留"importprice" 、”quantity"和"importvalue"

🔗参考链接：

数据库 之 理解 超键、候选键、主键、主属性、外键