数据库中计算值的更新方法

简介:

在做项目时,经常在项目中会遇到有些值是通过其他表经过计算得来的,然后将计算结果保存到数据库中。比如在一个休假系统中,一个员工每年已休天数就是一个计算值,通过SUM员工的所有有效休假申请单可获得。再比如交易系统中的余额字段,对一个账号的所有流水进行SUM,所有收入减去所有支出就是余额。再比订单系统中,订单的总金额字段,就是订单明细的金额的SUM值。

对于这些字段,都有一个共性,那就是这个字段是可以通过其他表的字段计算出来的,可以认为这个字段是冗余的,如果没有这个字段,那么我们的系统仍然可以设计出来并且功能一个都不会少。这个字段主要为了提高查询的性能,出报表时也方便,效率高。

既然是一个冗余字段,那么就需要在更新数据时,及时更新这个字段,这里就涉及到一个问题,怎么更新呢?一般我们采用两种方法进行更新。

1.基于现有的计算值,在更新相关数据时加减该计算值。

在需要计算的数据量比较大的情况下一般采用这种方法。比如交易系统中,一个账户会产生大量的交易流水,而且随着时间的增长,流水会越来越多,那么在每次交易时直接用余额加减本次交易的金额即可得到新的余额,这种计算速度会很快。

2.每次更新相关数据时,根据所有数据重新计算。

在计算量较小是使用这种方法。比如我们的订单系统中,订单的总金额就是汇总订单明细的金额,如果删除了或者增加了订单明细,那么只需要重新汇总即可。由于一个订单的明细一般不可能很多,而且随着时间的增长,一个订单明细也不可能越来越多。所以每次修改订单,重新计算总金额也不会太多的占用CPU资源。

那么我们再来看一看前面提到的休假系统是采用哪种方式计算员工的已休年假天数呢?首先员工的休假单并不会很多,一个员工一年顶多也就请几十次假,不可能一年请个几千几万次假。其次,休假天数是按年划分的,不需要按照员工的所有休假记录进行汇总,所有计算量也不会随着时间的增长而增长。从这2点来看,那么休假系统的已休年假天数应该用第二种方式,每次休假申请时重新计算已休天数。

优缺点

使用第一种方法,计算量小,速度快,但是如果数据一旦发生异常,那么以后的计算就会将错就错,一直错下去。

第二种方法在每次更新数据时重新计算,需要一定的计算量,所以不能用于大数据量的计算,优点是不用担心数据不一致的问题,保证计算列是正确的。

如果使用第一种方法,如何避免数据不一致呢。一个常用的方法是建立一个定时任务,在数据库闲时使用全量数据重新计算每天发生更改的数据的计算值,然后用这个值和数据库中的该列进行比较,如果不相同,那么就通知管理员,人为清查数据不一致的原因,将数据修复。

另外在使用第一种方法的时候,一定要注意并发问题。比如一个银行系统,如果我们要取钱,那么这个操作会对应数据库的这样操作:

1.开启一个事务。

2.Select读取余额,判断是否有足够余额用于支取。

3.Insert,记录取钱这个流水。

4.Update账户的余额字段:新的余额=步骤1读取的余额-取钱金额。

5.提交事务。

如果用户有100元的余额,现在同时发起2个取100元的操作,那么按以上操作,在操作2时都是读取到100元,都可以取钱,然后会造成记录了2条取100的流水,但是余额却是0的情况。如果我们采用的是余额通过流水进行重新计算的方法:

4.Update账户的余额字段:新的余额=SUM(流水)。

那么同时发起2个取100元操作的话,两个事务都会执行到步骤3,事务1可以执行步骤4,但事务2由于需要读取流水表,该表被事务1的的步骤3所Lock了,所以事务2等待事务1完成。最后事务1提交成功,余额变为0,然后事务2执行步骤4,余额变成-100,违反约束,导致事务2回滚,取钱失败。

当然并不是说采用方法1就没办法避免并发问题。可以在步骤2时将共享锁上升为独占锁(select for update),那么就不会造成数据错误。

还有就是步骤4,为什么不使用“余额=余额-取钱金额”呢,这样事务1执行了步骤4后,必须事务结束了事务2的步骤4再执行该操作,而此时余额已经是0了。余额=0-100,那么也是正确的结果-100啊。主要是因为我们现在编程大量使用ORMapping,这些计算变成了程序中的计算,就不会生成这样的SQL。(当然,如果你愿意也可以使用HQL、原生SQL等方式实现Balance=Balance-Amount)

目录
相关文章
|
3月前
|
人工智能 运维 关系型数据库
数据库运维:mysql 数据库迁移方法-mysqldump
本文介绍了MySQL数据库迁移的方法与技巧,重点探讨了数据量大小对迁移方式的影响。对于10GB以下的小型数据库,推荐使用mysqldump进行逻辑导出和source导入;10GB以上可考虑mydumper与myloader工具;100GB以上则建议物理迁移。文中还提供了统计数据库及表空间大小的SQL语句,并讲解了如何使用mysqldump导出存储过程、函数和数据结构。通过结合实际应用场景选择合适的工具与方法,可实现高效的数据迁移。
694 1
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库中进行日期比较的多种方法介绍。
以上方法提供了灵活多样地处理和对比MySQL数据库中存储地不同格式地日子信息方式。根据实际需求选择适当方式能够有效执行所需操作并保证性能优化。
219 10
|
6月前
|
数据库
【YashanDB知识库】数据库一主一备部署及一主两备部署时,主备手动切换方法及自动切换配置
【YashanDB知识库】数据库一主一备部署及一主两备部署时,主备手动切换方法及自动切换配置
【YashanDB知识库】数据库一主一备部署及一主两备部署时,主备手动切换方法及自动切换配置
|
2月前
|
SQL Oracle 关系型数据库
比较MySQL和Oracle数据库系统,特别是在进行分页查询的方法上的不同
两者的性能差异将取决于数据量大小、索引优化、查询设计以及具体版本的数据库服务器。考虑硬件资源、数据库设计和具体需求对于实现优化的分页查询至关重要。开发者和数据库管理员需要根据自身使用的具体数据库系统版本和环境,选择最合适的分页机制,并进行必要的性能调优来满足应用需求。
116 11
|
4月前
|
存储 算法 Java
实现不同数据库的表间的 JOIN 运算的极简方法
跨库计算是数据分析中的常见难题,尤其涉及多数据库系统时,表间 JOIN 操作复杂度显著提升。esProc 提供了一种高效解决方案,能够简化跨库 JOIN 的实现。例如,在车辆管理、交管和公民信息系统中,通过 esProc 可轻松完成如下任务:按城市统计有车公民事件数量、找出近一年获表彰的车主信息,以及按年份和品牌统计车辆违章次数。esProc 支持不同关联场景(如维表关联与主子表关联)的优化算法,如内存索引、游标处理和有序归并,从而大幅提升编码和运算效率。无论是同构还是异构数据源,esProc 均能灵活应对,为复杂数据分析提供强大支持。
|
5月前
|
Oracle 安全 关系型数据库
【Oracle】使用Navicat Premium连接Oracle数据库两种方法
以上就是两种使用Navicat Premium连接Oracle数据库的方法介绍,希望对你有所帮助!
1161 28
|
5月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。
|
10月前
|
存储 监控 安全
数据库多实例的部署与配置方法
【10月更文挑战第23天】数据库多实例的部署和配置需要综合考虑多个因素,包括硬件资源、软件设置、性能优化、安全保障等。通过合理的部署和配置,可以充分发挥多实例的优势,提高数据库系统的运行效率和可靠性。在实际操作中,要不断总结经验,根据实际情况进行调整和优化,以适应不断变化的业务需求。
|
6月前
|
SQL 数据库连接 Linux
数据库编程:在PHP环境下使用SQL Server的方法。
看看你吧,就像一个调皮的小丑鱼在一片广阔的数据库海洋中游弋,一路上吞下大小数据如同海中的珍珠。不管有多少难关,只要记住这个流程,剩下的就只是探索未知的乐趣,沉浸在这个充满挑战的数据库海洋中。
143 16
|
8月前
|
数据采集 数据库 Python
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
377 75