自学内容网 自学内容网
自学内容网 > 正文

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

🕗 发布于 2024-11-12 13:16 数据库  

目录

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语句。

总结：

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

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

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

    反范式的优点：

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

    反范式的缺点：

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

 

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


原文地址：https://blog.csdn.net/whs_8792/article/details/143672962

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024