在MariaDB中用触发器更新主表汇总数据：SUM()还是逐记录方式？

在MariaDB这类数据库中，要通过触发器自动用明细表（detail_table）的求和结果更新主表（master_table）的total_amount汇总字段，下面两种触发器实现方式各有优劣，具体选择要看业务场景：

一、全量SUM()计算方式 #

这种方式每次增删改明细记录时，都会重新计算对应主记录的所有明细金额总和，直接覆盖主表的汇总字段：

-- 插入时更新汇总
CREATE TRIGGER tr_insert_detail_table
AFTER INSERT ON detail_table
FOR EACH ROW
BEGIN
  UPDATE master_table
  SET total_amount = (
    SELECT SUM(amount)
    FROM detail_table
    WHERE master_id = NEW.master_id
  )
  WHERE id = NEW.master_id;
END;

-- 更新时更新汇总
CREATE TRIGGER tr_update_detail_table
AFTER UPDATE ON detail_table
FOR EACH ROW
BEGIN
  UPDATE master_table
  SET total_amount = (
    SELECT SUM(amount)
    FROM detail_table
    WHERE master_id = NEW.master_id
  )
  WHERE id = NEW.master_id;
END;

-- 删除时更新汇总
CREATE TRIGGER tr_delete_detail_table
AFTER DELETE ON detail_table
FOR EACH ROW
BEGIN
  UPDATE master_table
  SET total_amount = (
    SELECT SUM(amount)
    FROM detail_table
    WHERE master_id = OLD.master_id
  )
  WHERE id = OLD.master_id;
END;

优缺点 #

• 优势：数据一致性绝对可靠，哪怕主表的total_amount被手动修改过，或者之前触发器出现过异常，每次都会重新计算全量总和，确保结果准确；逻辑简单，不需要处理新旧值的加减逻辑，不容易出错。
• 劣势：性能开销大，当某条主记录对应的明细条目很多时，每次操作都要扫描所有相关明细行做求和，并发量高时会成为性能瓶颈；如果detail_table的master_id字段没有建立索引，性能会更差。

二、逐记录更新方式 #

这种方式每次增删改明细时，只对主表的汇总字段做对应的加减操作，不扫描全量明细：

-- 插入时更新汇总
CREATE TRIGGER tr_insert_detail_table
AFTER INSERT ON detail_table
FOR EACH ROW
BEGIN
  UPDATE master_table
  SET total_amount = total_amount + NEW.amount
  WHERE master_table.id = NEW.master_id;
END;

-- 更新时更新汇总
CREATE TRIGGER tr_update_detail_table
AFTER UPDATE ON detail_table
FOR EACH ROW
BEGIN
  UPDATE master_table
  SET total_amount = total_amount - OLD.amount + NEW.amount
  WHERE master_table.id = OLD.master_id;
END;

-- 删除时更新汇总
CREATE TRIGGER tr_delete_detail_table
AFTER DELETE ON detail_table
FOR EACH ROW
BEGIN
  UPDATE master_table
  SET total_amount = total_amount - OLD.amount
  WHERE master_table.id = OLD.master_id;
END;

优缺点 #

• 优势：性能极佳，每次操作都是简单的加减运算，属于O(1)级别的操作，不需要扫描明细表，资源消耗低，适合明细数据量大、并发高的场景。
• 劣势：依赖主表total_amount的初始值绝对正确，如果主表汇总值被手动修改、触发器曾失效过，或者出现其他异常导致初始值错误，后续的加减操作都会维持错误结果；逻辑相对复杂，比如如果更新明细时修改了master_id，当前示例代码没有处理这种情况（需要同时更新旧主记录和新主记录的汇总值），容易出现逻辑遗漏。

选择建议 #

• 如果数据一致性优先级最高，且明细记录数量少、并发量低，优先选全量SUM()方式；
• 如果性能优先级更高，明细数据量大、并发高，且能保证主表汇总值不会被异常修改（或有其他机制维护初始一致性），优先选逐记录更新方式；
• 补充：如果使用MariaDB 10.2及以上版本，也可以考虑用物化视图来自动维护汇总值，这是介于两者之间的方案，既保证一致性又有较好性能，但需要注意物化视图的刷新机制。

内容的提问来源于stack exchange，提问作者davor.geci