【MySQL】三大范式

下面我们来聊聊表的设计，如何设计一张比较合理，冗余性低且IO次数比较少，效率高的表。

我们需要先认识一下范式

什么是范式？

范式是⼀组规则。在设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。
范式有哪些？

关系数据库有六种范式：第⼀范式（1NF）、第⼆范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（5NF，⼜称完美范式），越高的范式数据库冗余越小。然而，普遍认为范式越高虽然对数据关系有更好的约束性，但也可能导致数据库IO更繁忙，因此在实际应用中，数据库设计通常只需满足第三范式即可，如果在想提高效率，再去增加某个字段的冗余性

为啥越高的范式数据库冗余越小，IO效率越忙呢？继续看

第一范式

第一范式即：数据库表的每⼀列都是不可分割的原子数据项，而不能是集合，数组，对象等非原子数据
在关系型数据库的设计中，满足第⼀范式是对关系模式的基本要求。不满足第⼀范式的数据库就不能被称为关系数据库。

所以，在关系型数据库中，每⼀列都可以用基本数据类型表示，就天然满足第⼀范式。

不是第一范式的例子：

其中学校这一列是一个对象，还可以在分割，不满足第一范式。

上述例子，如果满足第一范式：

第二范式

前提：表必须先满足第一范式（1NF）（即列不可再分，每一列都是原子值），且表的主键是复合主键（由多个字段共同构成）。

核心要求：所有非主键字段必须完全依赖于整个复合主键，而不能只依赖于复合主键中的某一个或某几个字段（即杜绝 “部分函数依赖”）。

如何理解？举个例子：

需求：学生可以选修课程，课程有对应的学分，学生考试后每门课程会产生相应的成绩

学生是通过学号来确定的，学⽣的姓名、年龄和性别和课程没有关系，即学生的信息只依赖学号，

不依赖课程名；学分是通过课程来确定的，课程的学分与学生没有关系，即学分只依赖课程名，不依赖学号

而这张表中使⽤学号+课程名定义复合主键来唯⼀标识⼀个学⽣某门课程的成绩，这也是这张表的主要作用。

所以这张表的某些列不依赖与复合主键的所有列，而只和其中一个或几个复合主键列有关系，那么就是部分依赖，就不满足第二范式。

即对于使用复合主键的表，如果一行数据中的有些列只与复合主键中的⼀个或其中几个列有关系，那么就说他存在部分函数依赖，也就不满足第⼆范式

反过来说，如果所有列都和复合主键的所有列有关，就满足第二范式。

所以根据上述需求，如果满足第二范式，需要将上述例子拆为3张表

第⼆范式强调的是部分函数依赖，当⼀张表中的主键只有⼀列时，天然满足第二范式

不满足第二范式的问题：

1.数据冗余
        学生的姓名、年龄、性别和课程的学分在每行记录中重复出现，造成了大量的数据冗余
2.更新异常
        如果要调整MySQL的学分，那么就需要更新表中所有关于MySQL的记录，⼀旦执行中断导致某些记录更新成功，某些数据更新失败，就会造成表中同一门课程出现不同学分的情况，出现数据不一致问题。
3.插入异常

        目前这样的设计，成绩与每一门课和学生都有对应关系，也就是说只有学生参加选修课程考试取得了成绩才能生成⼀条记录。当有⼀门新课还没有学生参加考试取得成绩之前，那么这门新课在数据库中是不存在的，因为成绩为空时记录没有意义
4.删除异常
        把毕业学生的考试数据全都删除，此时课程和学分的信息也会被删除掉，有可能导致⼀段时间内，数据库里没有某门课程和学分的信息

第三范式

在满足第二范式的基础上，不存在非关键字段，对任⼀候选键的传递依赖。
如何理解？举个例子：

要求学生表中记录学生所属的学院，在满足第⼆范式的基础上对学生表做出修改

因为是要描述学生信息，并且在表中定义了Id为主键，Id可以明确的标识每条学生信息。

在这个表结构中，可以看出学生的学号、姓名、年龄、性别与主键Id强相关；学院电话、学院地址

与学院强相关；在⼀个表中出现了两个强相关的关系，而且这两个强相关关系又存在传递现象，即

通过学生Id可以找到学生记录，学生记录中包含学院名，每个学院⼜有自已的电话和地址

这种传递现象称为传递依赖，所以当前的表不满足第三范式
把上述例子改为满足第三范式：

把学院信息拆分出来定义学院表，学生表与学院表做关联

-- 精准查询指定学号学生的学院信息 SELECT s.student_id AS 学生学号, s.name AS 学生姓名, c.college_name AS 学院名称, c.phone AS 学院电话, FROM Student s INNER JOIN College c ON s.college_id = c.college_id -- 条件：指定要查询的学生学号 WHERE s.student_id = '10001';

在实际业务中，往往是先设计为第三范式，然后为了提高效率，通过反范式编程，即增加某个字段的冗余性，减少表的连接查询，来减少IO次数以提高效率。

如图：

如果使用反范式：

sql：

-- 精准查询指定学号学生的学院信息 SELECT c.college_name AS 学院名称, c.phone AS 学院电话, FROM Student s WHERE s.student_id = '10001';