范式
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操作更加便捷且减少出错。但随着范式的级别越高，设计出的结构会更加精细化，原本一张表的数据会被分摊到多张表中存储，表的数量随之越来越多
范式设计的问题
但随之而来的不仅仅只有好处，也存在一个致命问题，也就是当同时需要这些数据时，只能采用联表查询的形式检索数据，有时候甚至为了一个字段的数据，也需要做一次连表查询才能获得。这其中的开销无疑是花费巨大的，尤其是当连接的表不仅两三张而是很多张时，有可能还会造成索引失效，这种情况带来的资源、时间开销简直是一个噩梦，这会严重地影响整个业务系统的性能
介绍
因为范式设计的问题，在设计库表结构时，我们不一定要100%遵守范式准则。这种违反数据库范式的设计方法，就被称之为反范式设计
反范式的问题
- 存储空间变大
- 一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则数据不一致
- 若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源
- 在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂
适用场景
当冗余信息有价值或者能大幅度提高查询效率的时候，我们才会采取反范式的优化
什么时候考虑增加冗余字段
- 这个冗余字段不需要经常进行修改
- 这个冗余字段查询的时候不可或缺
- 历史信息的需要
典型场景
9.范式设计与反范式设计的对比
1752
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
