11.范式与反范式设计

范式

1.问题

MySQL的库表设计，在很多时候我们都是率性而为，往往在前期的设计中考虑不全面，同时对于库表结构的划分也并不明确，所以很多时候在开发过程中，代码敲着敲着会去重构某张表结构，甚至大面积重构多张表结构，这种随心所欲的设计方式，无疑给开发造成了很大困扰

2.介绍

实际上在设计DB库表结构时，也有一些共同需要遵守的规范，这些规范在数据库设计中称为范式，理解并掌握这些设计时的规范，能让咱们在项目之初，设计的库表结构更为合理且优雅。数据库范式中，声明远扬的有三大范式，但除此之外也有一些其他设计规范

范式（Normal Form）它就是指设计数据库时要遵守的一些原则

3.异常

介绍

不符合范式的关系，会产生很多异常

表数据示例

总览

冗余数据：例如学生-2 出现了两次
修改异常: 修改了一个记录中的信息，但是另一个记录中相同的信息却没有被修改
删除异常: 删除一个信息，那么也会丢失其它信息。例如删除了课程-1 需要删除第一行和第三行，那么学生-1 的信息就会丢失
插入异常: 例如想要插入一个学生的信息，如果这个学生还没选课，那么就无法插入

4.范式总览

5.数据库三大范式

介绍

三大范式之间，它们是递进的关系，也就是后续的范式都基于前一个范式的基础上推行

第一范式（1NF）

介绍

所有属性都不可再分，即数据项不可分

不符合的案例

SELECT * FROM `zz_student`;
+----------------------+--------+-------+
| student              | course | score |
+----------------------+--------+-------+
| 竹子，男，185cm      | 语文   |    95 |
| 竹子，男，185cm      | 数学   |   100 |
| 竹子，男，185cm      | 英语   |    88 |
| 熊猫，女，170cm      | 语文   |    99 |
| 熊猫，女，170cm      | 数学   |    90 |
| 熊猫，女，170cm      | 英语   |    95 |
+----------------------+--------+-------+

在上述的学生表中，其中有一个student学生列，这一列存储的数据则明显不符合第一范式：原子性的规定，因为这一列的数据还可以再拆分为姓名、性别、身高三项数据

修改后的案例

因此为了符合第一范式，应该将表结构更改为：

+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
| 竹子         | 男          | 185cm          | 语文   |    95 |
| 竹子         | 男          | 185cm          | 数学   |   100 |
| 竹子         | 男          | 185cm          | 英语   |    88 |
| 熊猫         | 女          | 170cm          | 语文   |    99 |
| 熊猫         | 女          | 170cm          | 数学   |    90 |
| 熊猫         | 女          | 170cm          | 英语   |    95 |
+--------------+-------------+----------------+--------+-------+

将student这一列数据，分别拆分为姓名、性别、身高三列，然后分别存储对应的数据才合理，通过这样的优化后，此时zz_student这张表则符合了数据库设计的第一范式

不满足第一范式会带来的问题

客户端语言和表之间无法很好的生成映射关系
查询到数据后，需要处理数据时，还需要对student字段进行额外拆分
插入数据时，对于第一个字段的值还需要先拼装后才能进行写入

第二范式（2NF）

介绍

要求表中的所有列，其数据都必须依赖于主键

不符合的案例

+--------------+-------------+----------------+--------+-------+
| student_name | student_sex | student_height | course | score |
+--------------+-------------+----------------+--------+-------+
| 竹子         | 男          | 185cm          | 语文   |    95 |
| 竹子         | 男          | 185cm          | 数学   |   100 |
| 竹子         | 男          | 185cm          | 英语   |    88 |
| 熊猫         | 女          | 170cm          | 语文   |    99 |
| 熊猫         | 女          | 170cm          | 数学   |    90 |
| 熊猫         | 女          | 170cm          | 英语   |    95 |
+--------------+-------------+----------------+--------+-------+

虽然此时已经满足了第一范式，但此刻观察course与score这两类列数据，并不依赖与主键，同时由于这样的结构，导致前面几列的数据出现了大量冗余

修改后的案例

此时可以再次拆分一下表结构

SELECT * FROM `zz_student`;
+------------+--------+------+--------+--------------+--------------+
| student_id | name   | sex  | height | department   | dean         |
+------------+--------+------+--------+--------------+--------------+
|          1 | 竹子   | 男   | 185cm  | 计算机系     | 竹子老大     |
|          2 | 熊猫   | 女   | 170cm  | 金融系       | 熊猫老大     |
+------------+--------+------+--------+--------------+--------------+

SELECT * FROM `zz_course`;
+-----------+-------------+
| course_id | course_name |
+-----------+-------------+
|         1 | 语文        |
|         2 | 数学        |
|         3 | 英语        |
+-----------+-------------+

SELECT * FROM `zz_score`;
+----------+------------+-----------+-------+
| score_id | student_id | course_id | score |
+----------+------------+-----------+-------+
|        1 |          1 |         1 |    95 |
|        2 |          1 |         2 |   100 |
|        3 |          1 |         3 |    88 |
|        4 |          2 |         1 |    99 |
|        5 |          2 |         2 |    90 |
|        6 |          2 |         3 |    95 |
+----------+------------+-----------+-------+

经过上述结构优化后，之前的一张表此时被我们拆分成学生表，课程表，成绩表三张，每张表中的id字段作为主键，其他字段都依赖于这个主键。无论在那张表中，都可以通过id主键确定其他字段的信息

此时再将目光看到先后两张学生表，原本的学生表有六条学生记录，其中有四条是冗余数据，此时的学生表则只有两条数据，同时这张学生表中只存储学生信息相关的数据。经过本次结构优化后，每张表的业务属性都具备唯一性，也就是每张表都只会描述了一件事情，不会存在一张表中会出现两个业务属性

第三范式（3NF）

介绍

要求表中每一列数据不能与主键之外的字段有直接关系

不符合的案例

+------------+--------+------+--------+--------------+--------------+
| student_id | name   | sex  | height | department   | dean         |
+------------+--------+------+--------+--------------+--------------+
|          1 | 竹子   | 男   | 185cm  | 计算机系     | 竹子老大     |
|          2 | 熊猫   | 女   | 170cm  | 金融系       | 熊猫老大     |
+------------+--------+------+--------+--------------+--------------+

比如这张学生表，目前即符合第一范式，也符合第二范式，但看最后的两个字段，department表示当前学生所属的院校，dean则表示这个院系的院长是谁。一般来说，一个学生的院长是谁，首先是取决于学生所在的院系的，因此最后的dean字段明显与department字段存在依赖关系，因此需要进一步调整表结构

修改后的案例

SELECT * FROM `department`;
+---------------+-----------------+-----------------+
| department_id | department_name | department_dean |
+---------------+-----------------+-----------------+
|             1 | 计算机系        | 竹子老大        |
|             2 | 金融系          | 熊猫老大        |
+---------------+-----------------+-----------------+

SELECT * FROM `zz_student`;
+------------+--------+------+--------+---------------+
| student_id | name   | sex  | height | department_id |
+------------+--------+------+--------+---------------+
|          1 | 竹子   | 男   | 185cm  |             1 |
|          2 | 熊猫   | 女   | 170cm  |             2 |
+------------+--------+------+--------+---------------+

经过进一步的结构优化后，又将原本的学生表拆为了院系表、学生表两张，学生表中则是只存储一个院系ID，由院系表存储院系相关的所有数据。至此，学生表中的每个非主键字段与其他非主键字段之间，都是相互独立的，之间不会再存在任何依赖性，所有的字段都依赖于主键

不满足第三范式会带来的问题

当一个院系的院长换人后，需要同时修改学生表中的多条数据
当一个院长离职后，需要删除该院长的记录，会同时删除多条学生信息

三范式总结

第一范式：确保原子性，表中每一个列数据都必须是不可再分的字段
第二范式：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事
第三范式：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的

6.巴斯-科德范式与四五范式

巴斯-科德范式

基础知识

一般在一张表中，可以用于区分每行数据的一个列，通常会被咱们设为主键，例如常用的ID字段就是如此，这类主键通常被称为单一主键，即一个列组成的主键。但除此之外，还有一个联合主键的概念，也就是由多个列组成的主键

介绍

巴斯-科德范式也被称为3.5NF，至于为何不称为第四范式，这主要是由于它是第三范式的补充版，第三范式的要求是：任何非主键字段不能与其他非主键字段间存在依赖关系，也就是要求每个非主键字段之间要具备独立性。而巴斯-科德范式在第三范式的基础上，进一步要求：任何主属性不能对其他主键子集存在依赖

说白了就是，规定了联合主键中的某列值，不能与联合主键中的其他列存在依赖关系

不符合的案例

+-------------------+---------------+--------+------+--------+
| classes           | class_adviser | name   | sex  | height |
+-------------------+---------------+--------+------+--------+
| 计算机-2201班     | 熊竹老师      | 竹子   | 男   | 185cm  |
| 金融-2201班       | 竹熊老师      | 熊猫   | 女   | 170cm  |
| 计算机-2201班     | 熊竹老师      | 子竹   | 男   | 180cm  |
+-------------------+---------------+--------+------+--------+

例如这张学生表，此时假设以classes班级字段、class_adviser班主任字段、name学生姓名字段，组合成一个联合主键，在这里我们可以通过联合主键，确定学生表中任何一个学生的信息

班主任字段其实也依赖于班级字段

修改后的案例

SELECT * FROM `zz_classes`;
+------------+-------------------+---------------+
| classes_id | classes_name      | class_adviser |
+------------+-------------------+---------------+
|          1 | 计算机-2201班     | 熊竹老师      |
|          2 | 金融-2201班       | 竹熊老师      |
+------------+-------------------+---------------+

SELECT * FROM `zz_student`;
+------------+--------+------+--------+
| classes_id | name   | sex  | height |
+------------+--------+------+--------+
|          1 | 竹子   | 男   | 185cm  |
|          2 | 熊猫   | 女   | 170cm  |
|          1 | 子竹   | 男   | 180cm  |
+------------+--------+------+--------+

经过结构调整后，原本的学生表则又被拆为了班级表、学生表两张，在学生表中只存储班级ID，然后使用classes_id班级ID和name学生姓名两个字段作为联合主键

不满足巴斯-科德范式会带来的问题

当一个班级的班主任老师换人后，需要同时修改学生表中的多条数据
当一个班主任老师离职后，需要删除该老师的记录，会同时删除多条学生信息
想要增加一个班级时，同时必须添加学生姓名数据，因为主键不允许为空

第四范式（4NF）

介绍

第四范式是基于BC范式之上的

一个表中至少需要有三个独立的字段才会出现多值依赖问题，多值依赖是指表中的字段之间存在一对多的关系，也就是一个字段的具体值会由多个字段来决定

不符合的案例

SELECT * FROM `zz_user_role_permission`;
+-----------+----------+-------+------------+
| user_name | user_sex | role  | permission |
+-----------+----------+-------+------------+
| 竹子      | 男       | ROOT  | *          |
| 熊猫      | 女       | ADMIN | BACKSTAGE  |
| 竹子      | 男       | ADMIN | BACKSTAGE  |
| 熊猫      | 女       | USER  | LOGIN      |
| 竹子      | 男       | USER  | LOGIN      |
| 子竹      | 男       | USER  | LOGIN      |
+-----------+----------+-------+------------+

上述是一个经典的业务，也就是一张用户角色权限表，先简单介绍一下表中各字段的信息：

满足第三范式，但对于BC范式仅是勉强满足，因为「用户、角色、权限」之间存在一些依赖关系，不过这里先不管，毕竟是举例说明，因此假设是满足BC范式

多值依赖

此时假设我们需要新增一条数据，那表中的权限字段究竟填什么？这个值是需要依赖多个字段决定的，权限来自于角色，而角色则来自于用户。也就是说，一个用户可以拥有多个角色，同时一个角色可以拥有多个权限，所以此时咱们无法单独根据用户名去确定权限值，权限值必须依赖用户、角色两个字段来决定，这种一个字段的值取决于多个字段才能确定的情况，就被称为多值依赖

修改后的案例

SELECT * FROM `zz_users`;
+---------+-----------+----------+----------+---------------------+
| user_id | user_name | user_sex | password | register_time       |
+---------+-----------+----------+----------+---------------------+
|       1 | 熊猫      | 女       | 6666     | 2022-08-14 15:22:01 |
|       2 | 竹子      | 男       | 1234     | 2022-09-14 16:17:44 |
|       3 | 子竹      | 男       | 4321     | 2022-09-16 07:42:21 |
+---------+-----------+----------+----------+---------------------+

SELECT * FROM `zz_roles`;
+---------+-----------+---------------------+
| role_id | role_name | created_time        |
+---------+-----------+---------------------+
|       1 | ROOT      | 2022-08-14 15:12:00 |
|       2 | ADMIN     | 2022-08-14 15:12:00 |
|       3 | USER      | 2022-08-14 15:12:00 |
+---------+-----------+---------------------+

SELECT * FROM `zz_permissions`;
+---------------+-----------------+---------------------+
| permission_id | permission_name | created_time        |
+---------------+-----------------+---------------------+
|             1 | *               | 2022-08-14 15:12:00 |
|             2 | BACKSTAGE       | 2022-08-14 15:12:00 |
|             3 | LOGIN           | 2022-08-14 15:12:00 |
+---------------+-----------------+---------------------+

SELECT * FROM `zz_users_roles`;
+----+---------+---------+
| id | user_id | role_id |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       1 |       3 |
|  4 |       2 |       2 |
|  5 |       2 |       3 |
|  6 |       3 |       3 |
+----+---------+---------+

SELECT * FROM `zz_roles_permissions`;
+----+---------+---------------+
| id | role_id | permission_id |
+----+---------+---------------+
|  1 |       1 |             1 |
|  2 |       2 |             2 |
|  3 |       3 |             3 |
+----+---------+---------------+

第五范式（5NF）/完美范式

省略…

7.实际上使用范式要注意的问题

经过一系列的阐述后，其实不难发现，越到后面的范式，越难令人理解，同时为了让表满足更高级别的范式，越往后付出代价也越大，而且拆分出的表数量也会越多，所以一般实际开发中，对于库表的设计最高满足BC范式即可，再往后就没意义了，因为表数量一多，查询也好，写入也罢，性能会越来越差

8.反范式设计

范式设计的优点

遵循数据库范式设计的结构优点很明显，它避免了大量的数据冗余，节省了大量存储空间，同时让整体结构更为优雅，能让SQL操作更加便捷且减少出错。但随着范式的级别越高，设计出的结构会更加精细化，原本一张表的数据会被分摊到多张表中存储，表的数量随之越来越多

范式设计的问题

但随之而来的不仅仅只有好处，也存在一个致命问题，也就是当同时需要这些数据时，只能采用联表查询的形式检索数据，有时候甚至为了一个字段的数据，也需要做一次连表查询才能获得。这其中的开销无疑是花费巨大的，尤其是当连接的表不仅两三张而是很多张时，有可能还会造成索引失效，这种情况带来的资源、时间开销简直是一个噩梦，这会严重地影响整个业务系统的性能