Mysql学习之范式篇

一、定义

1.1 范式简介

在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。

1.2 范式包括

目前关系型数据库有六种常见范式，按照范式级别，从低到高分别是：第一范式、第二范式、第三范式、巴斯-科德范式、第四范式和第五范式

1.3 键和相关属性的概念

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

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

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

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

举例：

这里有两个表：

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

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

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

二、六大范式

2.1 第一范式（1st NF)

2.1.1 定义

属于第一方式关系的所有属性都不可再分，则数据项不可分

2.1.2 理解

举例2

user 表的设计不符合第一范式

其中，user_info字段为用户信息，可以进一步拆分成更小粒度的字段，不符合数据库设计对第一范式的

要求。将user_info拆分后如下：

举例3

属性的原子性是 主观的 。

2.2 第二范式(2nd NF)

2.2.1 定义

若某关系R属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则关系R属于第二范式。

2.2.2 理解

第二范式是指每个表必须有一个（有且仅有一个）数据项作为关键字或主键（primary key），其他数据项与关键字或者主键一一对应，即其他数据项完全依赖于关键字或主键。由此可知单主属性的关系均属于第二范式。

判断一个关系是否属于第二范式：

1. 找出数据表中的所有码；
2. 找出所有主属性和非主属性；
3. 判断所有的非主属性对码的部分函数依赖。

举例1

成绩表（学号、课程号、成绩）关系中，（学号、课程号）可以决定成绩，但是学号不能决定成绩，课程号也不能决定成绩，所以”（学号、课程号）→ 成绩“就是完全依赖关系

举例2

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

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

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

(球员编号) → (姓名，年龄)

(比赛编号) → (比赛时间, 比赛场地)

对于非主属性来说，并非完全依赖候选键。这样会产生怎样的问题呢？

1. 数据冗余 ：如果一个球员可以参加 m 场比赛，那么球员的姓名和年龄就重复了 m-1 次。一个比赛也可能会有 n 个球员参加，比赛的时间和地点就重复了 n-1 次。

2. 插入异常 ：如果我们想要添加一场新的比赛，但是这时还没有确定参加的球员都有谁，那么就没法插入。

3. 删除异常 ：如果我要删除某个球员编号，如果没有单独保存比赛表的话，就会同时把比赛信息删除掉。

4. 更新异常 ：如果我们调整了某个比赛的时间，那么数据表中所有这个比赛的时间都需要进行调整，否则就会出现一场比赛时间不同的情况。

为了避免出现上述的情况，我们可以把球员比赛表设计为下面的三张表。

2.3 第三范式(3rd NF)

2.3.1 定义

非主属性既不传递依赖于码，也不部分依赖于码。

2.3.2 理解

第三范式要求在满足第二范式的基础上，任何非主属性不依赖于其他非主属性，即在第二范式的基础上，消除了传递依赖。

举例1

商品类别名称依赖于商品类别编号，不符合第三范式。

修改：

表1：符合第三范式的 商品类别表 的设计

表2：符合第三范式的 商品表 的设计

商品表goods通过商品类别id字段（category_id）与商品类别表goods_category进行关联。

举例2

球员player表 ：球员编号、姓名、球队名称和球队主教练。现在，我们把属性之间的依赖关系画出来，如下图所示：

你能看到球员编号决定了球队名称，同时球队名称决定了球队主教练，非主属性球队主教练就会传递依赖于球员编号，因此不符合 3NF 的要求。

如果要达到 3NF 的要求，需要把数据表拆成下面这样：

2.4 BCNF(巴斯范式)

2.4.1 定义

关系模式R<U,F>中，若每一个决定因素都包含码，则R<U,F>属于BCFN。

2.4.2 理解

根据定义我们可以得到结论，一个满足BC范式的关系模式有：

1. 所有非主属性对每一个码都是完全函数依赖；
2. 所有主属性对每一个不包含它的码也是完全函数依赖；
3. 没有任何属性完全函数依赖于非码的任何一组属性。

举例1

候选键 ：是（管理员，物品名）和（仓库名，物品名），然后我们从候选键中选择一个作为 主键 ，比

如（仓库名，物品名）。

主属性 ：包含在任一候选键中的属性，也就是仓库名，管理员和物品名。

非主属性 ：数量这个属性。

• 存在的问题

1. 增加一个仓库，但是还没有存放任何物品。根据数据表实体完整性的要求，主键不能有空值，因

此会出现 插入异常 ；

2. 如果仓库更换了管理员，我们就可能会 修改数据表中的多条记录 ；

3. 如果仓库里的商品都卖空了，那么此时仓库名称和相应的管理员名称也会随之被删除。

因此引入BCNF，它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

根据 BCNF 的要求，我们需要把仓库管理关系 warehouse_keeper 表拆分成下面这样：

仓库表 ：（仓库名，管理员）

库存表 ：（仓库名，物品名，数量）

2.5 第四范式

定义：限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。

理解: 显然一个关系模式是4NF，则必为BCNF。也就是说，当一个表中的非主属性互相独立时（3NF），这些非主属性不应该有多值，有多值就违反了4NF。

2.6 第五范式

第五范式是在第四范式的基础上做的进一步规范化。第四范式处理的是相互独立的多值情况，而第五范式则处理相互依赖的多值情况。

三、反范式化

3.1 概述

规范化 vs 性能

1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要

2. 在数据规范化的同时 , 要综合考虑数据库的性能

3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间

4. 通过在给定的表中插入计算列，以方便查询

3.2 应用举例

举例1：

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

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

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

举例2：

反范式化的 goods商品信息表 设计如下：

3.3 反范式化新问题

• 存储空间变大

• 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则 数据不一致

• 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常 消耗系统资源

• 在 数据量小 的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加 复杂

3.4 反范式的适用场景

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

1. 增加冗余字段的建议

2. 历史快照、历史数据的需要

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

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

参考资料：

(107条消息) 数据库之六大范式详解_小九的博客的博客-CSDN博客_数据库范式

MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！_哔哩哔哩_bilibili