无需截断balance表的超大transaction表增量更新余额SQL方案咨询

前置表准备 #

你需要提前准备两张辅助表（原transaction表无需调整）：

• balance 表：存储用户商品余额，字段为userId、item、balance，主键设为联合主键(userId, item)
• sync_progress 表：记录增量同步的进度位点，字段为固定主键id（值固定为1即可）、last_processed_transaction_id（存储最后一次处理完成的transaction表id）

第一次全量初始化执行SQL #

首次运行时执行以下逻辑生成初始余额，无需后续重复执行：

-- 全量聚合所有历史交易写入余额表
INSERT INTO balance (userId, item, balance)
SELECT userId, item, SUM(quantity)
FROM transaction
GROUP BY userId, item;

-- 记录首次计算的最大交易id作为初始位点
INSERT INTO sync_progress (id, last_processed_transaction_id)
SELECT 1, MAX(id) FROM transaction
-- 兼容重复执行的情况，位点直接更新为当前最大id
ON DUPLICATE KEY UPDATE last_processed_transaction_id = VALUES(last_processed_transaction_id);

后续周期增量更新SQL #

每隔数分钟执行一次以下逻辑即可，全程不会修改/删除balance表的历史数据：

-- 开启事务保证操作原子性，避免中途崩溃导致数据不一致
BEGIN;

-- 加行锁读取上次处理到的位点，防止并发执行重复计算
SELECT @last_processed_id := last_processed_transaction_id 
FROM sync_progress 
WHERE id = 1 FOR UPDATE;

-- 计算新增交易的用户商品数量变动
CREATE TEMPORARY TABLE temp_transaction_delta AS
SELECT userId, item, SUM(quantity) AS delta_quantity
FROM transaction
WHERE id > @last_processed_id
GROUP BY userId, item;

-- 增量更新余额：已存在的用户商品记录累加增量，不存在的直接插入新记录
INSERT INTO balance (userId, item, balance)
SELECT userId, item, delta_quantity
FROM temp_transaction_delta
ON DUPLICATE KEY UPDATE balance = balance + VALUES(balance);

-- 更新进度位点为本次处理完成的最大交易id
UPDATE sync_progress
SET last_processed_transaction_id = COALESCE((SELECT MAX(id) FROM temp_transaction_delta), @last_processed_id)
WHERE id = 1;

-- 提交事务完成本次计算
COMMIT;

逻辑说明 #

• 全程无TRUNCATE、DELETE操作操作balance表，完全基于增量更新实现，性能不受历史交易数据规模影响
• 基于transaction的自增id作为消费位点，不会重复计算也不会漏算新增数据
• 事务+行锁的机制保证就算执行中途中断，也不会出现余额和位点不一致的问题
• 适配示例场景：首次处理id 0-1的交易后，user0的item1余额为1、user1的item1余额为-1；第二次处理id 2-3的交易时，增量计算得到两个用户的item1变动分别为-1、+1，累加后余额均变为0，完全符合预期

不同数据库适配调整 #

如果使用非MySQL数据库，仅需要调整语法差异部分即可：

• PostgreSQL中把ON DUPLICATE KEY UPDATE替换为ON CONFLICT (userId, item) DO UPDATE SET balance = balance + EXCLUDED.balance
• 变量、临时表语法对应调整为对应数据库的兼容写法即可

内容的提问来源于stack exchange，提问作者Pandem1c