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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 运维开发.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. 频繁查询的数据

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

  1. 数据仓库和报表系统

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

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

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

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

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

5.3 实现方式

  1. 在表中添加冗余字段

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

  1. 创建预聚合表

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

  1. 垂直分割

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

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 字段的值,以保证数据一致性。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
21天前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
13天前
|
运维 Devops 持续交付
自动化运维之路:从脚本到DevOps探索后端开发:从基础到高级实践
【8月更文挑战第28天】在数字化时代的浪潮中,企业对于IT运维的要求越来越高。从最初的手动执行脚本,到如今的自动化运维和DevOps实践,本文将带你领略运维的演变之旅。我们将探索如何通过编写简单的自动化脚本来提升效率,进而介绍DevOps文化的兴起及其对现代运维的影响。文章将为你揭示,通过持续集成、持续部署和微服务架构的实践,如何构建一个高效、可靠的运维体系。准备好让你的运维工作变得更加智能化和自动化了吗?让我们一起踏上这段旅程。 【8月更文挑战第28天】 本文旨在为初学者和有一定经验的开发者提供一个深入浅出的后端开发之旅。我们将一起探索后端开发的多个方面,包括语言选择、框架应用、数据库设计
|
28天前
|
运维 Kubernetes 监控
|
27天前
|
敏捷开发 运维 Devops
DevOps文化:打破开发与运维之间的壁垒
【8月更文挑战第14天】DevOps文化是现代软件开发和运维的重要趋势之一。通过打破开发与运维之间的壁垒,实现自动化、持续集成/持续部署以及紧密协作等关键实践,可以显著提高软件交付的质量和效率。对于任何希望在数字化时代保持竞争力的企业来说,拥抱DevOps文化无疑是一个明智的选择。
|
1月前
|
Kubernetes 网络协议 Python
运维开发.Kubernetes探针与应用
运维开发.Kubernetes探针与应用
59 2
|
10天前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
67 0
|
20天前
|
运维 关系型数据库 MySQL
在Linux中,MySQL数据库日常运维中涉及哪些关键任务?
在Linux中,MySQL数据库日常运维中涉及哪些关键任务?
|
24天前
|
运维 Devops 数据库
太卷了!DevOps,就是开发要把运维卷跑了?
太卷了!DevOps,就是开发要把运维卷跑了?
|
25天前
|
运维 监控 Kubernetes
揭秘运维开发:如何让你的系统更高效、更可靠?
揭秘运维开发:如何让你的系统更高效、更可靠?
|
26天前
|
SQL 数据可视化 关系型数据库
平时MySQL开发时,经常用到的小技巧
MySQL开发过程中常用的一些技巧,包括使用MD5函数、创建带逻辑删除标识和时间戳的表、多行合并为一行、时间差计算、不走索引的查询优化、多表连接更新等操作的SQL语句示例。
48 0
下一篇
DDNS