超详细:三大范式和反范式设计详解

目录

1、三大范式

第一范式: 列不可再分 。

第二范式: 行可以唯一区分

第三范式:确保数据的完整性、减少数据冗余和避免更新异常。

反方式模式 

实验数据:模拟两张百万量级的数据表

反范式优化实验对比

反范式存在的问题 & 适用场景

那么反范式优化适用于哪些场景呢?

使用反范式好处:

总结:

‌三大范式具体应用场景‌:

    反范式的优点:

    反范式的缺点:


1、三大范式

首先,数据库设计我们要遵循3大范式,这套设计流程可以避免很对的问题,如数据冗余、重复数据等等。这是在设计层面前辈给我们总结出来的经验,对我们设计数据库非常有帮助。

  1. 减少数据冗余‌:通过遵循第一范式、第二范式和第三范式,可以最大限度地减少数据冗余,使得每个数据项只存储一次,避免了数据的重复存储。这样可以节省存储空间,并减少数据更新和维护的工作量‌ 。

  2. 提高数据一致性‌:三范式通过消除数据依赖关系,确保了数据的一致性。每个数据项只依赖于主键,避免了数据更新时可能出现的异常情况,保证了数据的完整性‌ 。

  3. 提高数据可维护性‌:三范式使得数据库结构更加清晰和易于维护。通过合理的表结构设计,减少了数据修改的复杂性,使得数据库更容易进行修改和扩展‌ 。

  4. 优化查询性能‌:通过合理的表结构设计,三范式可以提高查询的效率和响应速度,提升数据库的性能。避免了在查询时需要多次关联多个表的情况,减少了查询的复杂度‌ 。

  5. 增强数据完整性‌:三范式通过消除部分依赖和传递依赖,确保了数据的完整性。每个非主键字段只依赖于主键,避免了数据更新时可能出现的异常情况,保证了数据的准确性‌ 


 比如我们不使用三大范式直接存储数据:

  如果直接这样存储在数据库的一列中 即不好查找也不方便辨认 还容易混淆 数据冗余等等问题

这样一对比就可以看出来 明显阿萨姆奶茶的辨认度要高而且也非常容易辨认 ,所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项,不能是集合、数组等非原子数据项。第一范式要求表中的每个字段都代表一个不可分割的属性值

简单的说就是一个列且只对应一个属性。

第一范式: 列不可再分 。

 将信息进行分析和拆解,把字段一个个的拆分出来,不同的字段放不同的信息 。

 保证字段的原子性,一个字段说一个事情,这就符合第一范式 

如下图就是符合第一范式了:

  但是只满足第一范式也有问题: 

例如普通的可乐3元 但是无糖的也是3元 你只说要可乐 那你怎么区分呢 都是可乐

如果没有id   你怎么区分怎么知道哪个是无糖的哪个是常规的呢?

 如果发生这种重复冗余数据 是不是区别不了 这个时候 第二范式就来了

第二范式: 行可以唯一区分

在第一范式的基础上,非主键属性必须完全依赖于候选键。这意味着在第二范式中,表中的每个实例或记录必须可以被唯一地区分选取一个能区分每个实体的属性属性组作为实体的唯一标识。

解析:

因为重复的数据查找和区分比较困难,这时候我们就可以给这数据加个id 加个主键 ,这个主键就是唯一区分这条数据的一个键 通过这个唯一的主键 我们就可以明确的确定是那条数据 这个就叫做第二范式 行可以唯一区分。 

就和我们人的名字一样 当叫到你名字的时候就知道是在叫你

像这样就符合第一、二范式了 。但是只满足第一二范式就可以了吗?那这个时候我们商品的类型、商品的热度、排名什么什么的,很多种数据。

如果都直接存放在这一个表中:

  • 第一 他这个表的数据会有一个冗余重复 
  • 第二不方便修改,如果我们这个表有10亿数据的话 我们要修改这个类型的名称 是不是每个都要修改 修改10亿次 极其的不方便和麻烦
  • 第三而且这么多的数据也不好管理

那么怎么 确保数据的完整性、减少数据冗余和避免更新异常呢? 第三范式来了~

第三范式:确保数据的完整性、减少数据冗余和避免更新异常。

数据库三大范式是数据库设计的基本原则:

  • 确保数据的完整性、减少数据冗余和避免更新异常。

可以看到 goods表 这里面的 type(类型)列 不是和主键直接相关 而是间接相关的一个小团体

解析: 

就比如我们的商品类型,我们可以直接将它抽离出主表,变成一个独立的从表 。然后通过这个独立的从表的外键的方式进行一个关联引用 ,这种拆表的方式就叫做第三范式 。 我们要使用的时候只需要在主表中引入 从表的主键id 进行一个关联即可。

1、我们直接将 type 列从goods主表进行抽离  然后变成从表的一个id

2、抽离后形成一个产品类别表(type):

 3、可以看到type表中只有 typeId (产品id)  和一个 typeName (产品名称)  

 

如果需要用的时候我们直接进行一个连表查询即可:

 SELECT g.*,t.typeName FROM `goods` g left join `type` t on g.typeId = t.typeId

这样抽离出一个 独立的从表 之后假如我们要修改他的 typeName (产品名称)是不是只需要在type从表中修改一次typeName即可     这样就达到了一个批量次修改的效果 只需要一次操作就能完成所有的修改避免了数据冗余的可能。

这样一来一套满足三大范式的规范就完成了!! 

这就是数据库的三大范式也是数据库设计的基本原则,确保数据的完整性、减少数据冗余和避免更新异常。

虽然三大范式很符合规范 但是每次都必须要关联从表,进行连表查询比较繁琐麻烦,这时候我们就可以使用空间换时间,我们将主表中常用且不易改变的数据

反方式模式 

我们在之前已经了解了越高阶的范式得到的数据表越多数据冗余度越低。但有时候,我们在设计数据表的时候,还需要为了性能和读取效率违反范式化的原则

反范式就是相对范式化而言的,换句话说,就是 允许少量的冗余通过空间来换时间

如果我们想对查询效率进行优化,有时候反范式优化也是一种优化思路。

尽管围绕着数据表的设计有很多范式,但事实上,我们在设计数据表的时候却不一定要参照这些标准。

比如我们想要查询某个商品的前 1000 条评论,会涉及到两张表。

商品评论表 product_comment,对应的字段名称及含义如下:

用户表 user,对应的字段名称及含义如下:

 

下面,我们就用这两张表模拟一下反范式优化。

实验数据:模拟两张百万量级的数据表

为了更好地进行 SQL 优化实验,我们需要给用户表和商品评论表随机模拟出百万量级的数据。我们可以通过存储过程来实现模拟数据。

CREATE TABLE `user`  (`user_id` int(11) NOT NULL,`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`create_time` datetime(0) NOT NULL,PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
CREATE TABLE `product_comment`  (`comment_id` int(11) NOT NULL AUTO_INCREMENT,`product_id` int(11) NOT NULL,`comment_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`comment_time` datetime(0) NOT NULL,`user_id` int(11) NOT NULL,PRIMARY KEY (`comment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10000 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

下面是给用户表随机生成 100 万用户的代码:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_user`(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, interval RAND()*60 second);
INSERT INTO user(user_id, user_name, create_time)
VALUES((start+i), CONCAT('user_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

我用 date_start 变量来定义初始的注册时间,时间为 2017 年 1 月 1 日 0 点 0 分 0 秒,然后用 date_temp 变量计算每个用户的注册时间,新的注册用户与上一个用户注册的时间间隔为 60 秒内的随机值。然后使用 REPEAT … UNTIL … END REPEAT 循环,对 max_num 个用户的数据进行计算。在循环前,我们将 autocommit 设置为 0,这样等计算完成再统一插入,执行效率更高。

call insert_many_user(10000, 1000000)

可以看到这里我插入1000000 数据使用了61秒 还是比较慢的

接着我们再来给商品评论表 product_comment 随机生成 100 万条商品评论。这里我们设置为给某一款商品评论,比如 product_id=10001。评论的内容为随机的 20 个字母。以下是创建随机的 100 万条商品评论的存储过程:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_product_comments`(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE user_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = substr(MD5(RAND()),1, 20);
SET user_id = FLOOR(RAND()*1000000);
INSERT INTO product_comment(comment_id, product_id, comment_text, comment_time, user_id)
VALUES((START+i), 10001, comment_text, date_temp, user_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

同样的,我用 date_start 变量来定义初始的评论时间。这里新的评论时间与上一个评论的时间间隔还是 60 秒内的随机值,商品评论表中的 user_id 为随机值。我们使用 REPEAT … UNTIL … END REPEAT 循环,来对 max_num 个商品评论的数据进行计算。

--  起始值和最大循环次数
call insert_many_product_comments(1000, 1000000)

 可以看到评论表插入用了80秒


反范式优化实验对比

如果我们想要查询某个商品 ID,比如 10001 的前 1000 条评论,需要写成下面这样:

SELECT p.comment_text, p.comment_time, u.user_name FROM product_comment AS p 
LEFT JOIN user AS u 
ON p.user_id = u.user_id 
WHERE p.product_id = 10001 
ORDER BY p.comment_id DESC LIMIT 1000;

可以看到是0.024s 

我们在显示商品评论的时候,通常会显示这个用户的昵称,而不是用户 ID,因此我们还需要关联 product_comment 和 user 这两张表来进行查询。当表数据量不大的时候,查询效率还好,但如果表数据量都超过了百万量级,查询效率就会变低。这是因为查询会在 product_comment 表和 user 表这两个表上进行聚集索引扫描,然后再嵌套循环,这样一来查询所耗费的时间就有几百毫秒甚至更多。对于网站的响应来说,这已经很慢了,用户体验会非常差。

如果我们想要提升查询的效率,可以允许适当的数据冗余,也就是在商品评论表中增加用户昵称字段,在 product_comment 数据表的基础上增加 user_name 字段,就得到了 product_comment2  数据表。

创建 `product_comment2` 表

CREATE TABLE `product_comment2`  (`comment_id` int(11) NOT NULL AUTO_INCREMENT,`product_id` int(11) NOT NULL, `comment_text` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`comment_time` datetime(0) NOT NULL,`user_id` int(11) NOT NULL,	PRIMARY KEY (`comment_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10000 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

 插入数据: 


CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_product_comments2`(IN start INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2024-11-11 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = date_add(date_temp, interval RAND()*60 second);
INSERT INTO user(user_id, user_name, create_time)
VALUES((start+i), CONCAT('user_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END

插入数据:

--  起始值和最大循环次数
call insert_many_product_comments2(1000, 10000)

测试:

--  查询前10000条数据
SELECT comment_text, comment_time, user_name FROM product_comment2 WHERE product_id = 10001 ORDER BY comment_id DESC LIMIT 1000;

这样一来,只需单表查询就可以得到数据集结果:



 

优化之后只需要扫描一次聚集索引即可,运行时间为 0.020 秒,查询比之前少了0.004秒。 你能看到,在数据量大的情况下,查询效率会有显著的提升。


反范式存在的问题 & 适用场景

从上面的例子中可以看出,反范式可以通过空间换时间,提升查询的效率,但是反范式也会带来一些新问题。

在数据量小的情况下,反范式不能体现性能的优势,可能还会让数据库的设计更加复杂。比如采用存储过程来支持数据的更新、删除等额外操作,很容易增加系统的维护成本。

比如用户每次更改昵称的时候,都需要执行存储过程来更新,如果昵称更改频繁,会非常消耗系统资源。


那么反范式优化适用于哪些场景呢?

在现实生活中,我们经常需要一些冗余信息,比如订单中的收货人信息,包括姓名、电话和地址等。每次发生的订单收货信息都属于历史快照,需要进行保存,但用户可以随时修改自己的信息,这时保存这些冗余信息是非常有必要的

当冗余信息有价值或者能大幅度提高查询效率的时候,我们就可以采取反范式的优化。

此外反范式优化也常用在数据仓库的设计中,因为数据仓库通常存储历史数据,对增删改的实时性要求不强,对历史数据的分析需求强。这时适当允许数据的冗余度,更方便进行数据分析。


使用反范式好处:

  •  范式化的数据库更新起来更加快
  •  范式化之后,只有很少的重复数据,只需要修改更少的数据
  •  范式化的表更小,可以在内存中执行
  •  很少的冗余数据,在查询的时候需要更少的distinct或者group by语句。

总结:

‌三大范式具体应用场景‌:

  • 第一范式‌:适用于所有字段的值都是不可分割的原子值的情况。例如,将地址信息拆分成街道、城市、省份等单独的字段。
  • 第二范式‌:适用于非主键列完全依赖于主键的情况。例如,在联合主键的情况下,非主键列不能只依赖于主键的一部分,需要将相关数据拆分到不同的表中。
  • 第三范式‌:适用于消除非主键字段之间的传递依赖。例如,将部门信息和员工信息分开存储,避免员工表中包含部门信息,确保每个非主键字段只依赖于主键‌ 。

 通过遵循三范式,可以构建高效、可维护的数据库结构,减少数据冗余,提高数据一致性,优化查询性能,从而提升数据库的整体性能和可靠性

    反范式的优点:

  •  可以避免关联,因为所有的数据几乎都可以在一张表上显示;
  •  可以设计有效的索引;

    反范式的缺点:

  •  表格内的冗余较多,删除数据时候会造成表有些有用的信息丢失。

 


具体开发中可以根据自己的情况自行选择设计模式开发出更高效实用的数据库

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/469341.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Python 实现阿里滑块全攻略

阿里划块技术为开发者提供了高精度的视觉分割能力,而 Python 作为一种简洁高效的编程语言,可以轻松调用阿里划块接口,实现各种场景下的图像分割需求。 Python 调用阿里云分割抠图 - 商品分割接口的步骤如下:首先,开通…

尤雨溪都点赞的测试工具,你还不用?

尤雨溪都点赞的测试工具,你还不用? 想要一个跨浏览器兼容、无闪退的测试体验?Playwright来了!它不仅支持主流浏览器自动化,还能轻松跨平台,让你轻松构建可靠的端到端测试。本文带你了解Playwright的功能亮点…

「IDE」VS2022插件 Visual Assist X 番茄助手介绍说明

✨博客主页何曾参静谧的博客📌文章专栏「IDE」集成开发环境📚全部专栏「Win」Windows程序设计「IDE」集成开发环境「UG/NX」BlockUI集合「C/C++」C/C++程序设计「DSA」数据结构与算法「UG/NX」NX二次开发「QT」QT5程序设计「File」数据文件格式「UG/NX」NX定制开发「Py」Py

qt QFrame详解

1、概述 QFrame是Qt框架中用于提供框架或边框的控件,主要用于在图形用户界面(GUI)中创建框架,并提供各种边框样式和功能。它是Qt中一个基础的容器类,也是许多基础控件的基类,可以被QLCDNumber、QToolBox、…

数据重塑:长宽数据转换【基于tidyr】

在数据分析和可视化过程中,数据的组织形式直接影响着我们能够进行的分析类型和可视化效果。这里简单介绍两种常见的数据格式:长格式(Long Format)和宽格式(Wide Format),以及如何使用tidyr包进行…

【网络】应用层——HTTP协议

> 作者:დ旧言~ > 座右铭:松树千年终是朽,槿花一日自为荣。 > 目标:了解什么是HTTP协议。 > 毒鸡汤:有些事情,总是不明白,所以我不会坚持。早安! > 专栏选自:网络 &g…

【安全测试】sqlmap工具(sql注入)学习

前言:sqimap是一个开源的渗透测试工具,它可以自动化检测和利用SQL注入缺陷以及接管数据库服务器的过程。它有一个强大的检测引擎,许多适合于终极渗透测试的小众特性和广泛的开关,从数据库指纹、从数据库获 取数据到访问底层文件系…

【Android】webview常用方法和使用

文章目录 前言一、常见用法二、基础属性webView的常用方法WebViewClient的常用方法WebChromeClient的常用方法WebSettings的相关方法 三、加载流程和事件回调四、webview和JS之间的互相调用总结 五、参考链接 前言 最近项目又用到了webview,在回顾复习一次webview相…

可编辑PPT | 指挥中心系统建设与应用方案

本方案是一份全面的指挥中心系统建设与应用方案,涵盖了建设方案分析、指挥调度、远程通讯、会务管理等多个方面,旨在通过整合语音、视频监控、会议、指挥调度等多种技术,构建一个现代化、网络化、智慧化的城市指挥中心,以提高应对…

计算机新手练级攻略——善用搜索引擎

计算机学生新手练级攻略——善用搜索引擎 在信息爆炸的时代,计算机专业的学生如何有效地自我提升?答案可能就藏在一个简单却强大的工具——搜索引擎中。搜索引擎不仅是获取知识的入口,更是解决问题的利器。下面,我将分享一些善用搜…

vs2019托管调试助手 “ContextSwitchDeadlock“错误

错误描述 托管调试助手 "ContextSwitchDeadlock":“CLR 无法从 COM 上下文 0xd183e0 转换为 COM 上下文 0xd18328,这种状态已持续 60 秒。拥有目标上下文/单元的线程很有可能执行的是非泵式等待或者在不发送 Windows 消息的情况下处理一个运行时间非常长…

【ARM】MDK-烧录配置文件无权限访问

【更多软件使用问题请点击亿道电子官方网站】 1、 问题场景 客户代码编译正常、调试出现报错<Error: Flash Download failed - "Cortex-M4"> 仿真器识别正常&#xff0c;keil-Debug内显示相关信息、设备启动正常。 记录排查步骤&#xff0c;找到配置文件位…

【C语言刷力扣】66.加一

题目&#xff1a; 解题思路&#xff1a; 最初思路是打算将数组中的数提出来&#xff0c;加一&#xff0c;再放回另一数组中。后来发现数组最大长度100&#xff0c;而100位的数字太大了。 所有在数组上实现加一。 利用 carry 标记每一位是否进位&#xff0c;即该位数加 carry &a…

Docker使用docker-compose一键部署nacos、Mysql、redis

下面是一个简单的例子&#xff0c;展示如何通过Docker Compose文件部署Nacos、MySQL和Redis。请确保您的机器上已经安装了Docker和Docker Compose。 1&#xff0c;准备好mysql、redis、nacos镜像 sudo docker pull mysql:8 && sudo docker pull redis:7.2 &&…

【LLM】3:从零开始训练大语言模型(预训练、微调、RLHF)

一、 大语言模型的训练过程 预训练阶段&#xff1a;PT&#xff08;Pre training&#xff09;。使用公开数据经过预训练得到预训练模型&#xff0c;预训练模型具备语言的初步理解&#xff1b;训练周期比较长&#xff1b;微调阶段1&#xff1a;SFT&#xff08;指令微调/有监督微调…

YOLO即插即用---PConv

Run, Don’t Walk: Chasing Higher FLOPS for Faster Neural Networks 论文地址&#xff1a; 1. 论文解决的问题 2. 解决问题的方法 3. PConv 的适用范围 4. PConv 在目标检测中的应用 5. 评估方法 6. 潜在挑战 7. 未来研究方向 8.即插即用代码 论文地址&#xff1a; …

Fortran安装(vscode+gcc+Python)

编写时间&#xff1a; 2024年11月7日 环境配置&#xff1a; gcc VScode Python 条件&#xff1a; Windows 10 x64 VMware虚拟机 前言 这是我出的第2个关于Fortran安装的教程&#xff0c;由于上一个方法&#xff08;你可以在本专栏里找到&#xff09;对储存空间的要求比较…

ModuleNotFoundError: No module named ‘_ssl‘ centos7中的Python报错

报错 ModuleNotFoundError: No module named ‘_ssl’ 解决步骤&#xff1a; 1.下载openssl wget https://www.openssl.org/source/openssl-3.0.7.tar.gz tar -zxvf openssl-3.0.7.tar.gz cd openssl-3.0.72.编译安装 ./config --prefix/usr/local/openssl make make install3…

TensorFlow|猫狗识别

&#x1f368; 本文为&#x1f517;365天深度学习训练营中的学习记录博客&#x1f356; 原作者&#xff1a;K同学啊 要求&#xff1a; 了解model.train_on_batch()并运用了解tqdm&#xff0c;并使用tqdm实现可视化进度条 &#x1f37b; 拔高&#xff08;可选&#xff09;&…

Python学习从0到1 day27 Python 高阶技巧 ③ 设计模式 — 单例模式

此去经年&#xff0c;再难同游 —— 24.11.11 一、什么是设计模式 设计模式是一种编程套路&#xff0c;可以极大的方便程序的开发最常见、最经典的设计模式&#xff0c;就是我们所学习的面向对象了。 除了面向对象外,在编程中也有很多既定的套路可以方便开发,我们称之为设计模…