高效查询商户日终余额：一个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_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/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_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日的方案，就不靠谱了。————对于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。

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_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）来极大优化对另一张表（流水表）的查询效率，这是典型的“利用业务知识优化技术实现”的思维体现。

posted on 2026-04-02 13:00 buguge 阅读(10) 评论(0) 收藏举报

刷新页面返回顶部

buguge - Keep it simple,stupid

导航

公告