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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 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 字段的值,以保证数据一致性。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
|
1月前
|
JavaScript 安全 Java
java版药品不良反应智能监测系统源码,采用SpringBoot、Vue、MySQL技术开发
基于B/S架构,采用Java、SpringBoot、Vue、MySQL等技术自主研发的ADR智能监测系统,适用于三甲医院,支持二次开发。该系统能自动监测全院患者药物不良反应,通过移动端和PC端实时反馈,提升用药安全。系统涵盖规则管理、监测报告、系统管理三大模块,确保精准、高效地处理ADR事件。
|
2月前
|
机器学习/深度学习 人工智能 运维
智能化运维:提升IT系统管理效率的新范式####
在数字化转型加速的今天,企业IT系统的复杂性日益增加,传统的运维模式已难以满足高效、稳定的业务需求。本文探讨了智能化运维(AIOps)如何通过融合人工智能、大数据分析和自动化工具,重塑IT运维流程,显著提升管理效率和服务质量,为企业带来前所未有的运营洞察力和响应速度。 ####
|
2月前
|
监控 前端开发 Java
【技术开发】接口管理平台要用什么技术栈?推荐:Java+Vue3+Docker+MySQL
该文档介绍了基于Java后端和Vue3前端构建的管理系统的技术栈及功能模块,涵盖管理后台的访问、登录、首页概览、API接口管理、接口权限设置、接口监控、计费管理、账号管理、应用管理、数据库配置、站点配置及管理员个人设置等内容,并提供了访问地址及操作指南。
|
2月前
|
存储 运维 安全
Spring运维之boot项目多环境(yaml 多文件 proerties)及分组管理与开发控制
通过以上措施,可以保证Spring Boot项目的配置管理在专业水准上,并且易于维护和管理,符合搜索引擎收录标准。
69 2
|
3月前
|
运维 关系型数据库 MySQL
运维|MySQL 数据库被黑,心力交瘁
前一阵有一个测试用的 MySQL 数据库被黑了,删库勒索的那种,这里记录一下事情经过,给自己也敲个警钟。
64 2
|
3月前
|
SQL JavaScript 关系型数据库
node博客小项目:接口开发、连接mysql数据库
【10月更文挑战第14天】node博客小项目:接口开发、连接mysql数据库
|
3月前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
63 1
|
3月前
|
SQL 关系型数据库 MySQL
PHP与MySQL协同工作的艺术:开发高效动态网站
在这个后端技术迅速迭代的时代,PHP和MySQL的组合仍然是创建动态网站和应用的主流选择之一。本文将带领读者深入理解PHP后端逻辑与MySQL数据库之间的协同工作方式,包括数据的检索、插入、更新和删除操作。文章将通过一系列实用的示例和最佳实践,揭示如何充分利用这两种技术的优势,构建高效、安全且易于维护的动态网站。
|
3月前
|
运维 Java Linux
【运维基础知识】掌握VI编辑器:提升你的Java开发效率
本文详细介绍了VI编辑器的常用命令,包括模式切换、文本编辑、搜索替换及退出操作,帮助Java开发者提高在Linux环境下的编码效率。掌握这些命令,将使你在开发过程中更加得心应手。
50 2

热门文章

最新文章