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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 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 字段的值,以保证数据一致性。

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
4月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
779 1
|
3月前
|
SQL 运维 自然语言处理
Dataphin智能化重磅升级!编码难题一扫光,开发运维更高效!
Dataphin重磅推出三大核心智能化能力:智能代码助手提升SQL开发效率;智能运维助手实现移动化任务管理;智能分析通过自然语言生成SQL,助力数据价值释放。未来将持续开放智能ETL、安全助手等能力,助力企业构建高效、稳定的数据资产体系。
365 0
|
4月前
|
人工智能 OLAP 数据处理
解锁数仓内AI流水线,AnalyticDB Ray基于多模ETL+ML提效开发与运维
AnalyticDB Ray 是AnalyticDB MySQL 推出的全托管Ray服务,基于开源 Ray 的丰富生态,经过多模态处理、具身智能、搜索推荐、金融风控等场景的锤炼,对Ray内核和服务能力进行了全栈增强。
|
21天前
|
存储 人工智能 运维
从“看得见”到“能决策”:Operation Intelligence 重构企业智能运维新范式
从 Observability 到 Operation Intelligence,日志服务 SLS 与云监控 2.0 协力之下,为企业打造高效、稳定、智能运营的数字化中枢,让复杂系统变得可视、可管、可优。
|
7月前
|
人工智能 运维 安全
AI大模型运维开发探索第四篇:智能体分阶段演进路线
本文探讨了智能体工程的演进历程,从最初的思维链(智能体1.0)到实例化智能体(智能体2.0),再到结构化智能体(智能体3.0),最终展望了自演进智能体(智能体4.0)。文章详细分析了各阶段遇到的问题及解决策略,如工具调用可靠性、推理能力提升等,并引入了大模型中间件的概念以优化业务平台与工具间的协调。此外,文中还提到了RunnableHub开源项目,为读者提供了实际落地的参考方案。通过不断迭代,智能体逐渐具备更强的适应性和解决问题的能力,展现了未来AI发展的潜力。
|
3月前
|
敏捷开发 运维 数据可视化
DevOps看板工具中的协作功能:如何打破开发、测试与运维之间的沟通壁垒
在DevOps实践中,看板工具通过可视化任务管理和自动化流程,提升开发与运维团队的协作效率。它支持敏捷开发、持续交付,助力团队高效应对需求变化,实现跨职能协作与流程优化。
|
3月前
|
人工智能 运维 自然语言处理
首个智能体模型实测:产品、开发、运维“全包了”
2025年,AI进入“动手”时代。智谱发布新一代大模型GLM-4.5,全球排名第三、国产第一,专为智能体设计,融合推理、编码与智能体能力,实现自主规划与执行任务。通过8个Demo展示其强大能力,涵盖网页设计、课件制作、小游戏开发等,展现其“带手的脑”特性,推动AI从实验室走向真实场景。
219 0
|
3月前
|
机器学习/深度学习 运维 监控
智能运维Agent:自动化运维的新范式
在数字化转型浪潮中,智能运维Agent正重塑运维模式。它融合人工智能与自动化技术,实现从被动响应到主动预防的转变。本文详解其四大核心功能:系统监控、故障诊断、容量规划与安全响应,探讨如何构建高效、可靠的自动化运维体系,助力企业实现7×24小时无人值守运维,推动运维效率与智能化水平全面提升。
785 0
|
6月前
|
人工智能 运维 监控
阿里云携手神州灵云打造云内网络性能监测标杆 斩获中国信通院高质量数字化转型十大案例——金保信“云内网络可观测”方案树立云原生运维新范式
2025年,金保信社保卡有限公司联合阿里云与神州灵云申报的《云内网络性能可观测解决方案》入选高质量数字化转型典型案例。该方案基于阿里云飞天企业版,融合云原生引流技术和流量“染色”专利,解决云内运维难题,实现主动预警和精准观测,将故障排查时间从数小时缩短至15分钟,助力企业降本增效,形成可跨行业复制的数字化转型方法论。
300 6
|
10月前
|
存储 分布式计算 Hadoop
【产品升级】Dataphin V4.4重磅发布:开发运维提效、指标全生命周期管理、智能元数据生成再升级
Dataphin V4.4版本引入了多项核心升级,包括级联发布、元数据采集扩展、数据源指标上架、自定义属性管理等功能,大幅提升数据处理与资产管理效率。此外,还支持Hadoop集群管理、跨Schema数据读取、实时集成目标端支持Hudi及MaxCompute delta等技术,进一步优化用户体验。
852 3
【产品升级】Dataphin V4.4重磅发布:开发运维提效、指标全生命周期管理、智能元数据生成再升级

推荐镜像

更多