【SQL性能优化】反范式设计:3NF有什么不足,为什么有时候需要反范式设计?

2023-11-11 15:20

本文主要是介绍【SQL性能优化】反范式设计:3NF有什么不足,为什么有时候需要反范式设计?,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

上一篇文章中,我们介绍了数据表设计的三种范式。作为数据库的设计人员,理解范式的设计以及反范式优化是非常有必要的。

为什么这么说呢?了解以下几个方面的内容之后你就明白了。

  1. 3NF 有什么不足?除了 3NF,我们为什么还需要 BCNF?
  2. 有了范式设计,为什么有时候需要进行反范式设计?
  3. 反范式设计适用的场景是什么?又可能存在哪些问题?

BCNF(巴斯范式)

如果数据表的关系模式符合 3NF 的要求,就不存在问题了吗?我们来看下这张仓库管理关系 warehouse_keeper 表:
在这里插入图片描述
在这个数据表中,一个仓库只有一个管理员,同时一个管理员也只管理一个仓库。我们先来梳理下这些属性之间的依赖关系。

仓库名决定了管理员,管理员也决定了仓库名,同时(仓库名,物品名)的属性集合可以决定数量这个属性。

这样,我们就可以找到数据表的候选键是(管理员,物品名)和(仓库名,物品名),

然后我们从候选键中选择一个作为主键,比如(仓库名,物品名)。

在这里,主属性是包含在任一候选键中的属性,也就是仓库名,管理员和物品名。非主属性是数量这个属性。

如何判断一张表的范式呢?我们需要根据范式的等级,从低到高来进行判断。

首先,数据表每个属性都是原子性的,符合 1NF 的要求;其次,数据表中非主属性”数量“都与候选键全部依赖,(仓库名,物品名)决定数量,(管理员,物品名)决定数量,因此,数据表符合 2NF 的要求;最后,数据表中的非主属性,不传递依赖于候选键。因此符合 3NF 的要求。

既然数据表已经符合了 3NF 的要求,是不是就不存在问题了呢?我们来看下下面的情况:

  1. 增加一个仓库,但是还没有存放任何物品。根据数据表实体完整性的要求,主键不能有空值,因此会出现插入异常;
  2. 如果仓库更换了管理员,我们就可能会修改数据表中的多条记录;
  3. 如果仓库里的商品都卖空了,那么此时仓库名称和相应的管理员名称也会随之被删除。

你能看到,即便数据表符合 3NF 的要求,同样可能存在插入,更新和删除数据的异常情况。

这种情况下该怎么解决呢?

首先我们需要确认造成异常的原因:主属性仓库名对于候选键(管理员,物品名)是部分依赖的关系,这样就有可能导致上面的异常情况。人们在 3NF 的基础上进行了改进,提出了BCNF,也叫做巴斯 - 科德范式,它在 3NF 的基础上消除了主属性对候选键的部分依赖或者传递依赖关系。

根据 BCNF 的要求,我们需要把仓库管理关系 warehouse_keeper 表拆分成下面这样:

仓库表:(仓库名,管理员)

库存表:(仓库名,物品名,数量)

这样就不存在主属性对于候选键的部分依赖或传递依赖,上面数据表的设计就符合 BCNF。

反范式设计

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

我们在之前已经了解了越高阶的范式得到的数据表越多,数据冗余度越低。但有时候,我们在设计数据表的时候,还需要为了性能和读取效率违反范式化的原则。反范式就是相对范式化而言的,换句话说,就是允许少量的冗余,通过空间来换时间。

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

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

反范式可以通过空间换时间,提升查询的效率,但是反范式也会带来一些新问题。

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

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

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

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

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

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

我简单总结下数据仓库和数据库在使用上的区别:

  1. 数据库设计的目的在于捕获数据,而数据仓库设计的目的在于分析数据;
  2. 数据库对数据的增删改实时性要求强,需要存储在线的用户数据,而数据仓库存储的一般是历史数据;
  3. 数据库设计需要尽量避免冗余,但为了提高查询效率也允许一定的冗余度,而数据仓库在设计上更偏向采用反范式设计。

总结

今天我们讲了 BCNF,它是基于 3NF 进行的改进。你能看到设计范式越高阶,数据表就会越精细,数据的冗余度也就越少,在一定程度上可以让数据库在内部关联上更好地组织数据。但有时候我们也需要采用反范进行优化,通过空间来换取时间。

范式本身没有优劣之分,只有适用场景不同。没有完美的设计,只有合适的设计,我们在数据表的设计中,还需要根据需求将范式和反范式混合使用。
在这里插入图片描述

这篇关于【SQL性能优化】反范式设计:3NF有什么不足,为什么有时候需要反范式设计?的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/391000

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七