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

在做项目时，经常在项目中会遇到有些值是通过其他表经过计算得来的，然后将计算结果保存到数据库中。比如在一个休假系统中，一个员工每年已休天数就是一个计算值，通过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）