目录

1.范式介绍

2. 范式都包括哪些

3. 键和相关属性的概念

4. 第一范式(1st NF)

5. 第二范式(2nd NF)

6. 第三范式(3rd NF)

7. 反范式化

7.1 概述

7.2 应用举例

7.3 反范式的新问题

7.4 反范式的适用场景

---

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

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

数据库的范式设计越高阶, 冗余程度越低, 同时高阶的范式一定符合低阶范式的要求, 满足最低要求的范式是第一范式(1NF), 在第一范式的基础上进一步满足更多要求的称为第二范式(2NF), 其余范式以此类推.

一般来说, 在关系型数据库设计中, 最高也就遵循到 BCNF, 普遍还是3NF. 但不绝对, 有时候为了提高某些查询性能, 我们还需要破坏范式规则, 也就只反范式化. 

范式的定义会使用到主键和候选键, 数据库中的键(Key) 由一个或者多个属性组成. 数据表中常用的几种键和属性的定义:

超键: 能唯一标识元组的属性集叫做超键。

候选键: 如果超键不包括多余的属性，那么这个超键就是候选键。

主键: 用户可以从候选键中选择一个作为主键。

外键: 如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。

主属性: 包含在任一候选键中的属性称为主属性。

非主属性: 与主属性相对，指的是不包含在任何一个候选键中的属性。

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

通常，我们也将候选键称之为“码”，把主键也称为“主码”。因为键可能是由多个属性组成的，针对单个属性，我们还可以用主属性和非主属性来进行区分。

第一范式主要是确保数据表中每个字段的值都必须具有原子性, 也就是说数据表中每个字段的值为不可再次拆分的最小数据单元.

我们在设计某个字段的时候，对于字段x来说，不能把字段X拆分成字段 X1和字段 X-2。事实上，任何的 DBMS都会满足第一范式的要求，不会将字段进行拆分。

举例1:

假设一家公司要存储员工的姓名和联系方式。它创建一个如下表:

该表不符合 1NF ，因为规则说 “表的每个属性必须具有原子(单个)值”，lisi 和 zhaoliu 员工的
emp_mobile 值违反了该规则。为了使表符合 1NF，我们应该有如下表数据:

举例2:

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

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

举例3:

属性的原子性是主观的 。例如，Employees关系中雇员姓名应当使用1个(fullname)、2个(firstname和lastname) 还是3个 (firstname、middlename和lastname)属性表示呢? 答案取决于应用程序。如果应用程序需要分别处理雇员的姓名部分(如:用于搜索目的)，则有必要把它们分开，否则，不需要

表1:

表2:

第二范式要求，在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。如果知道主键的所有属性的值，就可以检索到任(要求中的主键，其实可以拓展替换为候选键)。何元组(行)的任何属性的任何值。

举例1:

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

举例2:

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

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

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

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

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

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

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

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

这样的话，每张数据表都符合第二范式，也就避免了异常情况的发生。

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

举例3:

违反了第二范式，因为有非主键属性仅依赖于候选键(或主键)的一部分。例如，可以仅通过orderid找到订单的 orderdate，以及 customerid 和 companyname，而没有必要再去使用productid。

修改:Orders表和OrderDetails表如下，此时符合第二范式。

第三范式是在第二范式的基础上，确保数据表中的每一个非主键字段都和主键字段直接相关，也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。(即，不能存在非主属性A依赖于非主属性 B，非主属性B 依赖于主键c的情况，即存在 “A→B→C” 的决定关系) 通俗地讲，该规则的意思是所有 非主键属性 之间不能有依赖关系，必须 相互独立

这里的主键可以拓展为候选键。

举例1:

部门信息表: 每个部门有部门编号(dept id)、部门名称、部门简介等信息,

员工信息表: 每个员工有员工编号、姓名、部门编号。列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中

如果不存在部门信息表，则根据第三范式(3NF)也应该构建它，否则就会有大量的数据兄余。

举例2:

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

修改:

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

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

商品表 goods 通过商品类别 id 字段(category_id) 与 商品类别表 goods_category 进行关联.

举例3:

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

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

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

举例4:

修改第二范式中的举例3。

比时的0rders关系包含 orderid、orderdate、customerid和 companyname 属性，丰键定义为 orderid、customerid 和companyname均依赖于主键--orderid。例如，你需要通过orderid主键来查找代表订单中客户的customerid，同样，你需要通过 orderid 主键查找订单中客户的公司名称(companyname)。然而，customerid和companyname也是互相依靠的。为满足第三范式，可以改写如下:

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

有的时候不能简单按照规范要求设计数据表，因为有的数据看似冗余，其实对业务来说十分重要。这个时候，我们就要遵循 业务优先 的原则，首先满足业务需求，再尽量减少冗余。

如果数据库中的数据量比较大，系统的UV和PV访问频次比较高，则完全按照MySQL的三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。如果我们想对查询效率进行优化，反范式优化 也是一种优化思路。此时，可以通过在数据表中 增加冗余字段 来提高数据库的读性能。

规范化 vs 性能

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

举例1:

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

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

举例2:

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

举例3:

我们有2个表，分别是 商品流水表(atguigu.trans ) 和 商品信息表(atguigu.goodsinfo)。商品流水表里有 400 万条流水记录，商品信息表里有 2000 条商品记录。

商品流水表:

商品信息表:

新的商品流水表如下所示:

举例4:

课程评论表 class_comment ，对应的字段名称及含义如下

学生表 student ，对应的字段名称及含义如下

在实际应用中，我们在显示课程评论的时候，通常会显示这个学生的昵称，而不是学生ID，因此当我们想要查询某个课程的前 1000 条评论时，需要关联 class_comment 和 student这两张表来进行查询。

实验数据: 模拟两张百万量级的数据表

为了更好地进行 SQL 优化实验，我们需要给学生表和课程评论表随机模拟出百万量级的数据。我们可以通过存储过程来实现模拟数据

反范式优化实验对比

如果我们想要査询课程 ID 为 10001 的前 1000 条评论，需要写成下面这样:

运行结果(1000条数据行):

运行时长为 0.395 秒，对于网站的响应来说，这已经很慢了，用户体验会非常差。

如果我们想要提升查询的效率，可以允许适当的数据冗余，也就是在商品评论表中增加用户昵称字段在 class_comment 数据表的基础上增加 stu_name 字段，就得到了 class_comment2 数据表。这样一来，只需单表查询就可以得到数据集结果:

运行结果(1000条数据):

优化之后只需要扫描一次聚集索引即可，运行时间为 0.839 秒，查询时间是之前的 1/10。 你能看到, 在数据量大的情况下，查询效率会有显著的提升。

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

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

1.增加兄余字段的建议

增加冗余字段一定要符合如下两个条件。只有满足这两个条件，才可以考虑增加冗余字段
1) 这个几余字段 不需要经常进行修改;
2) 这个几余字段 查询的时候不可或缺

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

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

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

简单总结下数据仓库和数据库在使用上的区别:

1.数据库设计的目的在于 捕获数据 ，而数据仓库设计的目的在于 分析数据;

2.数据库对数据的 增删改实时性 要求强，需要存储在线的用户数据，而数据仓库存储的一般是 历史数据;

3.数据库设计需要 尽量避免冗余，但为了提高査询效率也允许一定的 冗余度，而数据仓库在设计上更偏向采用反范式设计。