文章详细讲解了数据库的三大范式（第一范式、第二范式、第三范式）的概念、原则和案例，强调了它们在数据库设计中的重要性，以及如何通过范式优化表结构，减少数据冗余。同时，文章提到了反范式设计，指出在某些情况下，允许数据冗余可以提高查询效率。最后，通过一个订单系统案例展示了反范式设计的应用。
「MySQL库表设计」数据库的三大范式、反范式及其案例精讲
参考&鸣谢
文章目录
一、范式的概述
MySQL的库表设计，在很多时候我们都是率性而为，往往在前期的设计中考虑并不全面，同时对于库表结构的划分也并不明确，所以很多时候在开发过程中，代码敲着敲着会去重构某张表结构，甚至大面积重构多张表结构，这种随心所欲的设计方式，无疑给开发造成了很大困扰。
但实际上设计DB库表结构时，也有一些共同需要遵守的规范，这些规范在数据库设计中被称为“范式”，理解并掌握这些设计时的规范，能让咱们在项目之初，设计的库表结构更为合理且优雅。数据库范式中，声名远扬的有三大范式，但除此之外也有一些其他设计规范，如：
- ①数据库三大范式（
1NF、2NF、3NF） - ③第四范式(
4NF）和第五范式：完美范式（5NF） - ②巴斯-科德范式（
BCNF） - ④反范式设计
不过对于上述的几种设计范式，大部分小伙伴应该仅了解过三范式，对于其他的应该未曾接触，那在本篇中会重点阐述库表设计时，会用到的这些范式。
二、数据库的三大范式
范式(Normal Form)在前面也提到过，它就是指设计数据库时要遵守的一些原则，而数据库的三大范式，相信诸位在学习数据库知识时也定然接触过。三大范式之间，它们是递进的关系，也就是后续的范式都基于前一个范式的基础上推行，就好比下面这句话：
今天我要先炒菜，然后吃饭，最后洗碗。
炒菜、吃饭、洗碗三者也属于递进关系，后者都建立在前者之上，其顺序不能颠倒，比如先吃饭再炒菜，这必然是行不通的。数据库的三大范式也一样，第二范式必须建立在第一范式的基础之上，如若设计的库表第一范式都不满足，那定然是无法满足第二范式的。
写在前面的话：其实对于数据库三范式相关的资料，网上也有很多很多，但大部分资料都涉及了太多的概念，通篇看下来也很难让人理解，因此下述的三范式则会结合具体的设计实例来让诸位彻底理解三范式。
第一范式（确保每一列都保持原子性）
原子性，顾名思义，就是要求每一列都是原子，不可再分；什么叫列可再分，或者不可再分呢？
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字段进行额外拆分。 - 插入数据时，对于第一个字段的值还需要先拼装后才能进行写入。
简单来说，如果按照原本那张形式去做业务开发，显然操作起来会更加麻烦且复杂一些，但第一范式的原子性，除开对列级别生效之外，行级别的数据也是同理，也就是每一行数据之间是互不影响的，都是独立的一个整体。
第二范式（保证每一列都是和主键有关的）
第二范式是在第一范式的基础上提出的，所以满足第二范式就一定满足第一范式，也就是说要想符合第二范式必须先满足第一范式。
（第一范式是第二范式的必要条件，反过来第二范式是第一范式的充分条件）
第二范式的要求表中的所有列，其数据都必须依赖于主键，也就是一张表只存储同一类型的数据，不能有任何一列数据与主键没有关系，还是上面的那张表数据为例：
+--------------+-------------+----------------+--------+-------+
| 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主键确定其他字段的信息。
此时再将目光看到先后两张学生表，原本的学生表有六条学生记录，其中有四条是冗余数据，此时的学生表则只有两条数据，同时这张学生表中只存储学生信息相关的数据。经过本次结构优化后，每张表的业务属性都具备“唯一性”，也就是每张表都只会描述了“一件事情”，不会存在一张表中会出现两个业务属性（例如之前的学生表包含了学生信息和课程成绩）。
第三范式（确保每个列都是直接依赖主键，而不存在间接依赖）
前面已经对第一范式、第二范式进行了直观阐述，接下来聊一聊数据库的第三范式，第三范式要求表中每一列数据不能与主键之外的字段有直接关系，怎么理解呢？基于上述的例子：
+------------+--------+------+--------+--------------+--------------+
| 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，由院系表存储院系相关的所有数据。至此，学生表中的每个非主键字段与其他非主键字段之间，都是相互独立的，之间不会再存在任何依赖性，所有的字段都依赖于主键。
那这里为什么要调整呢？不调整不行吗？还真不行，来简单思考一下不调整结构的情况下会发生什么问题：
- ①当一个院系的院长换人后，需要同时修改学生表中的多条数据。
- ②当一个院长离职后，需要删除该院长的记录，会同时删除多条学生信息。
- …
也就是如果设计的表结构，无法满足第三范式，在操作表时就会出现异常，使得整个表较难维护。
三、数据库三范式小结
到这里就已经将库表设计的三范式做了直观阐述，总结如下：
- 第一范式：确保原子性，表中每一个列数据都必须是不可再分的字段。
- 第二范式：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。
- 第三范式：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。
经过三范式的示例后，数据库中的表数量也逐渐多了起来，似乎设计符合三范式的库表结构，反而更加麻烦了对吗？答案并非如此，因为在没有按照范式设计时，会存在几个问题：
- ①整张表数据比较冗余，同一个学生信息会出现多条。
- ②表结构特别臃肿，不易于操作，要新增一个学生信息时，需添加大量数据。
- ③需要更新其他业务属性的数据时，比如院系院长换人了，需要修改所有学生的记录。
但按照三范式将表结构拆开后，假设要新增一条学生数据，就只需要插入学生相关的信息即可，同时如果某个院系的院长换人了，只需要修改院系表中的院长就行，学生表中的数据无需发生任何更改。
因此，经过三范式的设计优化后，整个库中的所有表结构，会显得更为优雅，灵活性也会更强。
四、补充：数据库反范式设计
遵循数据库范式设计的结构优点很明显，它避免了大量的数据冗余，节省了大量存储空间，同时让整体结构更为优雅，能让SQL操作更加便捷且减少出错。但随着范式的级别越高，设计出的结构会更加精细化，原本一张表的数据会被分摊到多张表中存储，表的数量随之越来越多。
但随之而来的不仅仅只有好处，也存在一个致命问题，也就是当同时需要这些数据时，只能采用联表查询的形式检索数据，有时候甚至为了一个字段的数据，也需要做一次连表查询才能获得。这其中的开销无疑是花费巨大的，尤其是当连接的表不仅两三张而是很多张时，有可能还会造成索引失效，这种情况带来的资源、时间开销简直是一个噩梦，这会严重地影响整个业务系统的性能。
因此，也正是由于上述一些问题，在设计库表结构时，我们不一定要
100%遵守范式准则。这种违反数据库范式的设计方法，就被称之为 反范式设计。
遵循范式设计也好，反范式设计也罢，本身两者之间并没有优劣之分，只要能够对业务更有利，那就可以称之为好的设计方案。范式的目的仅在于让我们设计的结构更优雅合理，有时候在表中多增加一个字段，从数据库的角度来看，数据会存在冗余问题，会让表结构违反范式的定义，但如若能够在实际情况中减少大量的连表查询，这种设计自然也是可取的。
也就是说，在设计时千万不要拘泥于规则之内，一定要结合实际业务考虑，遵循业务优先的原则去设计结构。
当然啦，在日常实际开发当中，可能并不会完全遵守三大范式，只能说是具体情况具体分析，假如当前情况在表存在冗余的时候，更方便，那就允许存在一定的冗余。
随意设计出的结构，不满足范式要求，同时还无法给业务上带来收益的，这并不被称为反范式设计，反范式设计是一种刻意为之的思想。
五、综合案例
那么反范式优化适用于哪些场景呢？
在现实工作中，我们经常需要一些冗余信息，比如订单中的收货人信息：用户姓名、手机号码以及收货地址等等。
其实每次发生的订单收货信息都属于历史快照信息，需要进行保存，但同时用户又可以随时修改自己的信息，这时保存这些冗余信息是非常有必要的。
范式设计
- 用户表 用户ID、姓名、电话、地址、邮箱
- 订单表 订单ID、用户ID、下单时间、支付类型、订单状态
- 订单商品表 订单ID、商品 ID、商品价格
- 商品表 商品ID、名称、描述、过期时间
SELECT b.用户名, b.电话, b.地址, a.订单ID,
SUM(c.商品价价*C.商品数量) as 订单价格
// 上面这就需要三张表的关联了,可能效率就很低了
FROM‘订单表` a
JOIN‘用户表’b ON a用户ID=b.用户ID
JOIN `订单商品表` C ON c.订单ID= b.订单ID
GROUP BY b.用户名，b.电话b.地址,a.订单ID
反范式设计
- 用户表 用户ID、姓名、电话、地址、邮箱
- 订单表 订单ID、用户ID、下单时间、支付类型、订单状态、订单价格、用户名、电话、地址
- 订单商品表 订单ID、商品 ID、商品数量、商品价格
- 商品表 商品ID、名称、描述、过期时间
SELECT a.用户名,a.电话.a.地址
,a.订单ID
,a.订单价格
FROM `订单表` a
把用户表的地址加到了订单表，这样查询地址时，就不需要把用户表和订单表关联
扫一扫
1377
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
