高效查询商户日终余额：一个SQL的优化实践
一、业务背景与数据模型
在交易系统中，准确记录和查询商户的日终余额是一项基础且关键的需求。我们的系统通过以下两张核心表来管理账户信息：
- 账户主表 (
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_TRANS_DETAIL)：记录每一笔资金变动的完整流水。每次记账时，都会在流水中记录当时的余额，并同步更新主表T_ACC_TRANS中的余额及UPDATE_TIME字段。
CREATE TABLE PAYMENTDB.T_ACC_TRANS_DETAIL (
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_TRANS_DETAIL(ORDER_ID, TYPE, BLSIGN, TRANS_TYPE, AMT, MEMO);
CREATE INDEX IDX_TIME_MERCHANT ON T_ACC_TRANS_DETAIL(CREATE_TIME, MER_ID); -- 优化按时间范围的查询
CREATE INDEX IDX_CREATE_TIME ON T_ACC_TRANS_DETAIL(CREATE_TIME);
CREATE INDEX IDX_MERCHANT_QUERY ON T_ACC_TRANS_DETAIL(MER_ID, TYPE, CREATE_TIME, ID);
我们需要实现一个定时任务，每日凌晨统计并保存前一日（T-1日）所有商户的日终余额快照。示例数据输出如下：
| 日期 | 商户编号 | 当日余额 |
|---|---|---|
| 2026/1/2 | M001 | 500 |
| 2026/1/2 | M002 | 1000 |
| 2026/1/3 | M001 | 600 |
| 2026/1/3 | M002 | 50 |
二、方案演进：从简单到健壮
1. 初版方案：仅依赖流水表（存在漏洞）
最直接的想法是：取出T-1日每个商户最后一条流水中的余额。
SELECT MER_ID, AVA_AMT
FROM T_ACC_TRANS_DETAIL
WHERE id IN (
SELECT MAX(t.id) AS last_trans_id
FROM T_ACC_TRANS_DETAIL 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_TRANS_DETAIL
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_TRANS_DETAIL 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日的方案，就不靠谱了。————对于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_TRANS_DETAIL 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_TRANS_DETAIL表从2025年8月份至今有9百万条数据记录。执行这个sql动辄需要10s~15s。
| PERIOD | DATA_COUNT |
|---|---|
| 2025-07~2025-08 | 102,075 |
| 2025-09~2025-10 | 2,399,903 |
| 2025-11~2025-12 | 2,592,862 |
| 2026-01~2026-02 | 3,245,861 |
| 2026-03~2026-04 | 1,216,260 |
| 总计 | 9,556,961 |
3.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_TRANS_DETAIL 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_TRANS_DETAIL 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_TRANS_DETAIL 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）来极大优化对另一张表（流水表）的查询效率，这是典型的“利用业务知识优化技术实现”的思维体现。
[附]草稿内容
我们系统会对商户的充值、结算等交易进行记账。有如下 账户表 和 账户记账流水表。
-- 账户表
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;
-- 账户记账流水表
CREATE TABLE PAYMENTDB.T_ACC_DETAIL_TRANS (
ID NUMBER(20) PRIMARY KEY, -- 主键ID，自增
SEQ NUMBER(12), -- 序列号
MAC VARCHAR2(64), -- 设备标识
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, ACCOUNT_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, ACCOUNT_TYPE, TYPE, CREATE_TIME, ID);
数据关系是：
- T_ACC_TRANS 保存商户最新的余额
- T_ACC_DETAIL_TRANS 保存每一次记账流水
- 在持久化每一条记账流水时，会记录最新的余额数据，同时，会更新 T_ACC_TRANS 的余额 以及 update_time。
我们要记录每天的商户余额快照。数据如下：
日期 商户编号 当日余额
2026/1/2 M001 500
2026/1/2 M002 1000
2026/1/2 M003 50
2026/1/3 M001 600
2026/1/3 M002 50
2026/1/3 M003 0
要得到这些数据，需要通过定时任务来实现。每天凌晨从T-1日的 最后一条记账流水 上获取日终余额。
（我们假定T-1日期是2026-03-24）
sql①如下：
select MER_ID, AVA_AMT from T_ACC_DETAIL_TRANS where id in(
SELECT
-- t.MER_ID,
MAX(t.id) AS last_trans_time
FROM PAYMENTDB.T_ACC_DETAIL_TRANS t
WHERE create_time >=DATE '2026-03-24' and create_time<DATE '2026-03-25'
GROUP BY t.MER_ID
)
不过，这里存在一个瑕疵：并不是每个商户每天都会发生记账操作，也就是说，会存在T-1日没有记账流水的商户。这时，我们应该怎么规避这个瑕疵呢？
方案是 查 账户主表。如下sql②：
SELECT MER_ID, AVA_AMT from T_ACC_TRANS a where update_time<DATE'2026-03-25' and not exists(
select * from T_ACC_DETAIL_TRANS WHERE create_time >=DATE '2026-03-24' and create_time<DATE '2026-03-25' and MER_ID =a.MER_ID
)
这样一来，通过 UNION ALL 最终就得到所有商户的日终余额数据。
select MER_ID, AVA_AMT from T_ACC_DETAIL_TRANS where id in(
SELECT
-- t.MER_ID,
MAX(t.id) AS last_trans_time
FROM PAYMENTDB.T_ACC_DETAIL_TRANS t
WHERE create_time >=DATE '2026-03-24' and create_time<DATE '2026-03-25'
GROUP BY t.MER_ID
)
UNION ALL
SELECT MER_ID, AVA_AMT from T_ACC_TRANS a where not exists(
select * from T_ACC_DETAIL_TRANS WHERE create_time >=DATE '2026-03-24' and create_time<DATE '2026-03-25' and MER_ID =a.MER_ID
)
正文结束。
------------ 我是萌萌哒分割线 ------------------
现在，我们思考一个问题：如何查询指定日期的日终商户余额？ 例如今天是2026-03-26， 要查询 2026-03-10 这天的日终商户余额，该怎么做呢？
2026-03-10当天有记账流水的，可以直接用上面的 sql①。
那对于 2026-03-10 当天没有记账流水的， 我们直接查询 账户主表 显然就会产生BUG。 正确的姿势，是从 2026-03-10 往前推算查找最近的那一条流水记录的余额。
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
注意，当系统使用一年半载后，记账流水表的数据量增大到超过百万条，这条sql将会灰常慢。 即使 create_time 有索引。 尤其是对于那些 沉默的僵尸商户，这些商户最初有记账，后面就没有了。 这个sql会扫描几乎全表数据，所以就不难理解为什么慢了。
就像下面的示例数据①，扫描的 create_time 时间区间是 [2025/2/1 00:00,2026/3/9 19:13]这个大区间的数据（id为52~2098）。
ID MER_ID ORDER_ID MEMO AMT AVA_AMT CREATE_TIME
51 M001 O202500168552 充值 500 500 2025/1/31 13:34
52 M001 O202500168552 结算交易 500 0 2025/2/1 00:00
953 M002 O202500180101 充值 1000 1000 2025/2/31 09:34:04
... ... ... ... ... ... ...
1025 M002 O202500380872 结算交易 999 1 2025/8/3 9:13
... ... ... ... ... ... ...
2097 M003 O202600123423 充值 600 600 2026/3/9 9:13
2098 M003 O202600280101 结算交易 50 550 2026/3/9 19:13
4011 M003 O202600280101 结算交易 100 450 2026/3/26 19:13
如何解决呢？
知识就是力量，但更重要的是运用知识的能力。
需要借助 账户主表 T_ACC_TRANS 的力量。
修改sql③为下面的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' -- 小于查询日期
and t.create_time>=a.UPDATE_TIME - INTERVAL '5' MINUTE -- 关键：为 create_time 指定起始值
and a.UPDATE_TIME<DATE '2026-03-10' -- 关键：
) a where rn=1
so，以上面的 示例数据①为例， sql④查询的是哪些数据？ 如果你知道，可在评论区留言交流。
附录：查询特定日期的余额快照，这里记录一下我们系统的完整的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
当看到一些不好的代码时，会发现我还算优秀；当看到优秀的代码时，也才意识到持续学习的重要！--buguge
本文来自博客园，转载请注明原文链接：https://www.cnblogs.com/buguge/p/19810986
浙公网安备 33010602011771号