运维开发.MySQL.范式与反范式化

运维开发 MySQL.三大范式

---

- 文章信息 - Author: 李俊才 (jcLee95)
Visit me at CSDN: https://jclee95.blog.csdn.net
My WebSite：http://thispage.tech/
Email: [e-mail].
Shenzhen China
Address of this article:https://blog.csdn.net/qq_28550263/article/details/139157049
HuaWei:https://bbs.huaweicloud.com/blogs/428259

---

【介绍】：本文介绍MySQL中常用的三大范式，以及如何反范式化。

---

1. 概述

在数据库设计中，范式（Normalization）是用于 减少数据冗余 和 提高数据完整性的规则。

MySQL数据库设计中常用的三大范式是：

第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。

2. 第一范式：原子性

2.1 概念

定义：第一范式要求数据库表中的每一列都是原子的，即每个字段都不能再分割。

要求：

• 每个表格的每一列都只能包含一个值；

• 每个表格的每一列都必须是不可分割的基本数据项。

2.2 示例

假设我们有一个存储学生信息的表 students,其中包含学生的姓名、联系方式等信息。以下是一个不符合第一范式的表设计:

不符合第一范式的表设计

字段名称	字段类型	是否主键	描述
学生ID	INT	是	学生唯一标识
姓名	VARCHAR	否	学生姓名
联系方式	VARCHAR	否	学生联系方式,包括电话和邮箱

在这个设计中，联系方式字段包含了多个信息(电话和邮箱)，这违反了第一范式的原子性要求。

为了使表符合第一范式，我们需要将联系方式字段拆分为电话和邮箱两个字段，使每个字段只包含一个不可再次拆分的值。修改后的表设计如下:

符合第一范式的表设计

字段名称	字段类型	是否主键	描述
学生ID	INT	是	学生唯一标识
姓名	VARCHAR	否	学生姓名
电话	VARCHAR	否	学生电话号码
邮箱	VARCHAR	否	学生邮箱地址

通过这种方式，每个字段都只包含一个不可再次拆分的值，满足了第一范式的要求。

3. 第二范式：完全依赖主键

3.1 概念

定义：第二范式在满足第一范式的基础上，要求表中的 每个非主键字段都完全依赖于主键，而不是部分依赖。

特点：

• 必须先满足第一范式。

• 表中的非主键字段必须完全依赖于主键，而不能只依赖于主键的一部分（对于复合主键而言）。

3.2 示例

不符合第二范式的表设计

假设我们有一个学生信息表 students，其中包含学生编号、学生姓名、班级编号和班级名称等信息。表结构如下:

字段名称	字段类型	是否主键	描述
学生编号	INT	是	学生唯一标识
学生姓名	VARCHAR	否	学生姓名
班级编号	INT	否	班级编号×
班级名称	VARCHAR	否	班级名称×

在这个设计中,主键只有学生编号一个字段。但是，班级名称 字段只依赖于 班级编号，而不完全依赖于主键 学生编号。这违反了第二范式的要求。

符合第二范式的表设计

为了使表符合第二范式,我们需要将班级编号和班级名称字段从学生信息表中移除,并将其放入一个单独的班级表中。修改后的表设计如下：

学生信息表 students:

字段名称	字段类型	是否主键	描述
学生编号	INT	是	学生唯一标识
学生姓名	VARCHAR	否	学生姓名
班级编号	INT	否	班级编号

班级表 classes:

字段名称	字段类型	是否主键	描述
班级编号	INT	是	班级唯一标识
班级名称	VARCHAR	否	班级名称

通过这种方式,学生信息表中的每个非主键字段都完全依赖于主键,满足了第二范式的要求。同时,班级名称字段被移到了班级表中，与班级编号形成完全依赖关系。

4. 第三范式：和主键直接相关

4.1 概念

定义：第三范式在满足第二范式的基础上，要求表中的非主键字段之间不能有传递依赖关系。

特点：

• 必须先满足第二范式。

• 表中的非主键字段之间不能有传递依赖，即非主键字段不能依赖于其他非主键字段。

4.2 示例

不符合第三范式的表设计

假设我们有一个学生信息表 students，其中包含学生编号、学生姓名、班级编号、班级名称和班主任等信息。表结构如下:

字段名称	字段类型	是否主键	描述
学生编号	INT	是	学生唯一标识
学生姓名	VARCHAR	否	学生姓名
班级编号	INT	否	班级编号
班级名称	VARCHAR	否	班级名称
班主任	VARCHAR	否	班主任姓名

在这个设计中，虽然每个非主键字段都完全依赖于主键学生编号，满足了第二范式,但是班级名称和班主任字段依赖于班级编号字段，存在传递依赖关系。这违反了第三范式的要求。

符合第三范式的表设计

为了使表符合第三范式，我们需要将班级编号、班级名称和班主任字段从学生信息表中移除，并将其放入一个单独的班级表中。修改后的表设计如下:

学生信息表 students

字段名称	字段类型	是否主键	描述
学生编号	INT	是	学生唯一标识
学生姓名	VARCHAR	否	学生姓名
班级编号	INT	否	班级编号

班级表 classes

字段名称	字段类型	是否主键	描述
班级编号	INT	是	班级唯一标识
班级名称	VARCHAR	否	班级名称
班主任	VARCHAR	否	班主任姓名

通过这种方式,学生信息表中的非主键字段不再有传递依赖关系,满足了第三范式的要求。班级名称和班主任字段被移到了班级表中,与班级编号形成直接依赖关系。

5. 反范式化

5.1 概念

对于频繁查询的数据，如果严格遵循范式化设计，可能需要进行多表连接操作，导致查询性能下降。

反范式化（Denormalization）是一种在特定情况下 违反范式化规则 的数据库设计策略。它通过在表中引入冗余数据，可以避免多表连接，提高查询速度。

反范式化的目的是在数据库设计中 权衡范式化带来的好处（数据一致性、减少冗余）和查询性能之间的平衡。有时，为了获得更好的查询性能，我们可能需要在表中引入一些冗余数据,从而违反范式化规则。

5.2 应用场景

反范式化设计并不适用于所有情况。需要根据具体的业务需求、查询模式和性能要求来决定是否采用反范式化。

1. 频繁查询的数据

对于频繁查询的数据,如果严格遵循范式化设计,可能需要进行多表连接操作,导致查询性能下降。通过在表中引入冗余数据,可以避免多表连接,提高查询速度。

2. 数据仓库和报表系统

在数据仓库和报表系统中,数据通常是只读的,主要用于复杂的分析和统计查询。这种情况下,反范式化设计可以大大简化查询语句,提高查询性能。

3. 需要快速响应的实时系统

对于需要快速响应的实时系统,如在线交易系统,反范式化设计可以减少查询时间,提供更好的用户体验。

4. 数据冗余与数据一致性权衡

在某些情况下,数据冗余可能比数据一致性更重要。例如,在分布式系统中,为了提高可用性和性能,可能需要在不同的节点上保存相同的数据副本。

5.3 实现方式

1. 在表中添加冗余字段

通过在表中添加冗余字段,可以避免多表连接操作。例如,在学生信息表中添加班级名称字段,虽然这个字段可以通过连接班级表获得,但直接在学生信息表中保存可以提高查询速度。

2. 创建预聚合表

预聚合表是为了满足特定查询需求而创建的冗余表。它通过预先计算和存储聚合数据（如总和、平均值等）来加速查询。例如,创建一个销售额汇总表,存储按日期、产品类别等维度汇总的销售数据。

3. 垂直分割

将一个大表拆分成多个小表,每个小表包含部分字段。这样可以减少单表的数据量,提高查询速度。例如,将用户信息表拆分为基本信息表和详细信息表。

5.4 示例

假设我们有一个电商系统,包含订单表（orders）和商品表（products）。

订单表 orders:

字段名称	字段类型	是否主键	描述
order_id	INT	是	订单唯一标识
user_id	INT	否	用户ID
product_id	INT	否	商品ID
quantity	INT	否	购买数量

商品表 products:

字段名称	字段类型	是否主键	描述
product_id	INT	是	商品唯一标识
product_name	VARCHAR	否	商品名称
price	DECIMAL	否	商品价格

如果我们需要经常查询订单的总金额，可以考虑在订单表中添加一个冗余字段 total_amount，用于存储订单的总金额。这样，我们就可以直接从订单表中获取总金额，而不需要每次都连接商品表并进行计算。

反范式化后的订单表 orders:

字段名称	字段类型	是否主键	描述
order_id	INT	是	订单唯一标识
user_id	INT	否	用户ID
product_id	INT	否	商品ID
quantity	INT	否	购买数量
total_amount	DECIMAL	否	订单总金额

在这个设计中，我们引入了冗余字段 total_amount，它可以通过触发器或应用程序在插入或更新订单时自动计算和更新。
查询订单总金额的SQL语句从原来的：

SELECT o.order_id, SUM(p.price * o.quantity) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY o.order_id;

这条SQL查询语句计算每个订单的总金额。这里使用了JOIN操作来连接两个表：orders和products。

在原始的设计中，为了计算每个订单的总金额，我们需要从orders表和products表中提取数据，并通过JOIN操作将这两个表连接起来。这是因为订单表中只存储了商品的ID和购买数量，而商品的价格存储在商品表中。因此，为了得到每个订单的总金额，必须将订单中的每个商品的购买数量与其价格相乘，然后对一个订单中的所有商品进行求和。

可以看到，这种设计虽然在数据存储上是范式化的（避免了数据冗余），但在查询性能上可能不是最优的，特别是在数据量大或查询频繁的情况下。每次查询订单的总金额时，都需要执行计算密集型的JOIN操作和多次乘法及求和操作，这会增加数据库的负载和响应时间。

为了优化这种情况，可以通过在orders表中添加一个冗余字段total_amount来存储每个订单的总金额。这样，每当订单被创建或更新时，应用程序或数据库的触发器就可以立即计算该订单的总金额，并将这个值存储在total_amount字段中。这意味着：

1. 插入或更新操作时的计算：在订单创建或商品数量更新时，系统需要计算总金额并更新total_amount字段。这个计算只在订单数据变更时发生，而不是在每次查询时都进行。

2. 查询操作的简化：由于每个订单的总金额已经预先计算并存储好，因此查询订单总金额时，只需直接读取total_amount字段的值。这避免了复杂的JOIN操作和运行时的计算，从而显著提高了查询效率。

因此，查询语句可以从复杂的 联表查询 简化为 直接 查询单表：

SELECT order_id, total_amount
FROM orders;

通过反范式化设计，我们避免了多表连接，提高了查询性能。但同时，我们需要在插入或更新订单时额外维护 total_amount 字段的值，以保证数据一致性。