高效查询商户日终余额：一个SQL的优化实践

袁曼妮 · 10 小时前

一、业务背景与数据模型

在支付系统中，准确记录和查询商户的日终余额是一项基础且关键的需求。我们的系统通过以下两张核心表来管理账户信息：

账户主表 (T_ACC_TRANS)：存储商户账户的最新实时快照，包括可用余额、冻结金额等。

CREATE TABLE PAYMENTDB.T_ACC_TRANS (
MER_ID VARCHAR2(32) PRIMARY KEY, -- 商户ID
SEQ NUMBER(12) DEFAULT 0, -- 序列号
MAC VARCHAR2(64), -- 消息认证码
CASH_AMT NUMBER(15) DEFAULT 0, -- 现金金额
FREEZE_AMT NUMBER(15) DEFAULT 0, -- 冻结金额
AVA_AMT NUMBER(15) DEFAULT 0, -- 可用余额 (核心字段)
STATE NUMBER(5) DEFAULT 0, -- 账户状态
CREATE_TIME TIMESTAMP DEFAULT sysdate, -- 创建时间
UPDATE_TIME TIMESTAMP DEFAULT sysdate -- 最后更新时间
) TABLESPACE PAYMENTDB;

复制代码

账户记账流水表 (T_ACC_DETAIL_TRANS)：记录每一笔资金变动的完整流水。每次记账时，都会在流水中记录当时的余额，并同步更新主表 T_ACC_TRANS 中的余额及 UPDATE_TIME 字段。

CREATE TABLE PAYMENTDB.T_ACC_DETAIL_TRANS (
ID NUMBER(20) PRIMARY KEY, -- 主键ID，自增
SEQ NUMBER(12), -- 序列号
MER_ID VARCHAR2(32), -- 商户ID
MEMO VARCHAR2(500), -- 备注
ORDER_ID VARCHAR2(32), -- 订单号
TRANS_TYPE NUMBER(5) NOT NULL, -- 交易类型
TYPE NUMBER(5), -- 流水类型
AMT NUMBER(15) DEFAULT 0, -- 变动金额
BLSIGN NUMBER(5), -- 业务标识
CASH_AMT NUMBER(15) DEFAULT 0, -- 流水发生时的现金金额
FREEZE_AMT NUMBER(15) DEFAULT 0, -- 流水发生时的冻结金额
AVA_AMT NUMBER(15) DEFAULT 0, -- 流水发生时的可用余额 (核心字段)
CREATE_TIME TIMESTAMP DEFAULT sysdate, -- 流水创建时间
OPERATER VARCHAR2(50) -- 操作人
) TABLESPACE PAYMENTDB;
-- 核心索引
CREATE UNIQUE INDEX UK_FLOW_IDENTITY ON T_ACC_DETAIL_TRANS(ORDER_ID, TYPE, BLSIGN, TRANS_TYPE, AMT, MEMO);
CREATE INDEX IDX_TIME_MERCHANT ON T_ACC_DETAIL_TRANS(CREATE_TIME, MER_ID); -- 优化按时间范围的查询
CREATE INDEX IDX_CREATE_TIME ON T_ACC_DETAIL_TRANS(CREATE_TIME);
CREATE INDEX IDX_MERCHANT_QUERY ON T_ACC_DETAIL_TRANS(MER_ID, TYPE, CREATE_TIME, ID);

复制代码

我们需要实现一个定时任务，每日凌晨统计并保存前一日（T-1日）所有商户的日终余额快照。示例数据输出如下：
日期商户编号当日余额2026/1/2M0015002026/1/2M00210002026/1/3M0016002026/1/3M00250二、方案演进：从简单到健壮

1. 初版方案：仅依赖流水表（存在漏洞）

最直接的想法是：取出T-1日每个商户最后一条流水中的余额。

SELECT MER_ID, AVA_AMT
FROM T_ACC_DETAIL_TRANS
WHERE id IN (
SELECT MAX(t.id) AS last_trans_id
FROM T_ACC_DETAIL_TRANS t
WHERE t.create_time >= DATE '2026-03-24'
AND t.create_time < DATE '2026-03-25'
GROUP BY t.MER_ID
)

复制代码

问题：此方案有一个致命缺陷——并非每个商户每天都有交易。对于T-1日没有流水的商户，此查询将直接忽略他们，导致余额快照数据不全。
2. 改进方案：主表与流水表结合

为了解决上述问题，我们需要将商户分为两类处理：

当日有流水的商户：从流水表取最后一条流水。
当日无流水的商户：其日终余额就等于当日凌晨时主表中的余额。

-- 1. 当日有流水的商户，从流水表获取
SELECT MER_ID, AVA_AMT
FROM T_ACC_DETAIL_TRANS
WHERE id IN (... /* 同上，省略 */)
UNION ALL
-- 2. 当日无流水的商户，从主表获取
SELECT a.MER_ID, a.AVA_AMT
FROM T_ACC_TRANS a
WHERE update_time<DATE'2026-03-25' and NOT EXISTS (
SELECT 1
FROM T_ACC_DETAIL_TRANS d
WHERE d.create_time >= DATE '2026-03-24'
AND d.create_time < DATE '2026-03-25'
AND d.MER_ID = a.MER_ID
)

复制代码

此方案解决了数据完整性问题，是生成“昨日”余额快照的有效方法。
三、核心挑战：如何查询任意历史日期的余额？

我们来思考另一个问题：如何查询一个过去任意日期（例如2026-03-10）的日终余额？
上面定时任务虽然实现了T-1日余额快照的功能。但是，我们的考虑异常情况，假如某天的JOB失败了，并且未能及时在T日发现，那么，我们需要补偿数据。这种情况下，用上面查T-1日的方案，就不靠谱了。————对于“昨日”快照，我们可以用主表余额作为无流水商户的兜底。但对于历史日期，我们不能直接查主表，因为主表只保存当前最新余额。
正确思路：对于历史日期当天无流水的商户，应从该日期往前回溯，找到距离它最近的一条历史流水，用那条流水的余额作为其日终余额。
3.1 基础回溯方案（性能低下）

我们很容易写出如下使用窗口函数的SQL：

SELECT * FROM (
SELECT
t.*,
ROW_NUMBER() OVER (
PARTITION BY mer_id
ORDER BY t.id DESC
) AS rn
FROM T_ACC_DETAIL_TRANS t
JOIN T_ACC_TRANS a ON t.mer_id = a.MER_ID
WHERE t.create_time < DATE '2026-03-10' -- 关键：查询该日期之前的所有流水
) a
WHERE rn = 1

复制代码

性能瓶颈：这个查询会扫描目标日期之前该商户的所有历史流水。对于长期无交易的“僵尸商户”，其create_time < '2026-03-10'的条件会变得非常宽泛，导致需要扫描海量数据。即使create_time字段有索引，当需要回溯的数据量很大时，查询依然会非常缓慢。
我司T_ACC_DETAIL_TRANS表从2025年8月份至今有9百万条数据记录。执行这个sql动辄需要10s~15s。
PERIODDATA_COUNT2025-07~2025-08102,0752025-09~2025-102,399,9032025-11~2025-122,592,8622026-01~2026-023,245,8612026-03~2026-041,216,260总计9,556,9613.2 优化方案：借助主表缩小时间范围

知识就是力量，但更重要的是运用知识的能力。
核心的优化灵感在于：虽然我们不能直接用主表的当前余额，但可以利用主表的UPDATE_TIME字段。
关键洞察：账户主表的UPDATE_TIME，表示该账户最后一次发生交易的时间。对于一个在历史查询日（2026-03-10）之后再也没有交易过的“僵尸商户”，其主表的UPDATE_TIME一定早于2026-03-10，并且，距离2026-03-10最近的那条流水，其发生时间一定在UPDATE_TIME附近。
因此，我们可以为流水表的create_time增加一个下限条件，将扫描范围从(无限远, 2026-03-10)缩小到(UPDATE_TIME - 5分钟, 2026-03-10)，数据量急剧减少。

SELECT * FROM (
SELECT
t.*,
ROW_NUMBER() OVER (
PARTITION BY mer_id
ORDER BY t.id DESC
) AS rn
FROM T_ACC_DETAIL_TRANS t
JOIN T_ACC_TRANS a ON t.mer_id = a.MER_ID
WHERE t.create_time < DATE '2026-03-10' -- 上限：查询日期
AND t.create_time >= a.UPDATE_TIME - INTERVAL '5' MINUTE -- 下限（重点）：最后一次更新时间附近
AND a.UPDATE_TIME < DATE '2026-03-10' -- 确保主表记录在查询日期前已存在
) a
WHERE rn = 1

复制代码

优化效果：此方案通过关联主表，为每个商户的流水扫描增加了一个精确的起始时间点（UPDATE_TIME附近），彻底避免了全历史扫描，查询耗时在0.9s~2s，性能得到数量级提升。
四、总结

将“当日有流水”和“当日无流水需回溯”两种场景用UNION ALL结合起来，就得到了查询任意历史日期日终余额的完整、高性能方案。附上我们系统的完整的sql。
点击查看代码

-- SELECT count(*) from (
SELECT id,mer_id, cash_amt,freeze_amt, create_time -- *
FROM (
SELECT
t.*,
ROW_NUMBER() OVER (
PARTITION BY mer_id
ORDER BY t.id DESC
) AS rn
FROM T_ACC_DETAIL_TRANS t
join T_ACC_TRANS a on t.mer_id=a.MER_ID
WHERE -- mer_id = '89900002398017098003' AND -- 替换为实际商户号
t.create_time < DATE '2026-03-23' -- 截至3月3日
and a.UPDATE_TIME<DATE '2026-03-23' -- 截至3月3日
and t.create_time>=a.UPDATE_TIME - INTERVAL '5' MINUTE
union all
SELECT
t.*,
ROW_NUMBER() OVER (
PARTITION BY mer_id
ORDER BY t.id DESC
) AS rn
FROM T_ACC_DETAIL_TRANS t
join T_ACC_TRANS a on t.mer_id=a.MER_ID
WHERE -- mer_id = '89900002398017098003' AND -- 替换为实际商户号
t.create_time < DATE '2026-03-24' -- 截至3月3日
and t.create_time>=DATE '2026-03-23' -- 截至3月3日
) tmp
WHERE rn = 1
-- ) a

复制代码

从最初不完整的方案，到能正确处理无流水商户的UNION ALL方案，再到最后针对历史查询的优化回溯方案，这个过程清晰地展示了一个典型的技术问题解决路径：

满足功能需求：首先实现基础逻辑，保证结果正确。
处理边界情况：识别出“当日无流水商户”这一边界场景，使方案健壮。
应对性能挑战：面对历史查询的海量数据，通过深入理解业务（UPDATE_TIME的含义）和数据关系，设计出巧妙的查询条件，将宽泛的范围扫描优化为精准的区间查询。

这种“功能 -> 健壮性 -> 性能”的演进思维，是处理复杂业务系统问题的有效方法。最终方案的核心技巧在于利用一张表的冗余信息（主表的UPDATE_TIME）来极大优化对另一张表（流水表）的查询效率，这是典型的“利用业务知识优化技术实现”的思维体现。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

账号		自动登录	找回密码
密码			立即注册

高效查询商户日终余额：一个SQL的优化实践

相关帖子

签约作者

高效查询商户日终余额：一个SQL的优化实践

相关帖子

相关推荐

签约作者