Firebird 2.5环境下计算累计余额的SELECT语句性能优化咨询

优化方案 #

1. 新增覆盖索引优先落地 #

你当前的单列索引无法匹配存储过程中同时按账户ID+支付日期过滤的查询逻辑，每次求和都需要回表取数，性能损耗极大。直接创建如下复合覆盖索引，存储过程的求和查询可以直接通过索引拿到全部所需数据，无需访问主表：

CREATE ASC INDEX IDX_PAYMENTS_BANKDATE_CALC ON PAYMENTS (BANKING_ACCOUNTID, PAYMENT_DATE, CALCAMOUNT);

该方案改造成本最低，正常可以带来数倍的性能提升。

2. 改用单次遍历的存储过程返回结果集 #

当前性能慢的核心原因是逐行重复调用存储过程求和，如果查询范围内有1000条记录，就要执行1000次全量历史求和，复杂度为O(n²)。
可以直接写一个返回查询结果集的存储过程，把复杂度降到O(n)，逻辑如下：

SET TERM ^^ ;
CREATE PROCEDURE SP_QUERY_PAYMENTS_WITH_BALANCE (
    START_DATE timestamp,
    END_DATE timestamp,
    BANK_ID integer
) returns (
    ID INTEGER,
    PAYMENT_TYPE SMALLINT,
    BANKING_ACCOUNTID INTEGER,
    AMOUNT DOUBLE PRECISION,
    CALCAMOUNT DOUBLE PRECISION,
    PAYMENT_DATE TIMESTAMP,
    CURRENTBALANCE DOUBLE PRECISION
)
as
declare variable INIT_BALANCE double precision;
begin
    -- 第一步仅计算一次期初余额：查询起始日期之前的累计值
    select coalesce(sum(CALCAMOUNT), 0) from PAYMENTS 
    where BANKING_ACCOUNTID = :BANK_ID and PAYMENT_DATE < :START_DATE
    into INIT_BALANCE;

    -- 第二步遍历查询范围内的记录，逐行累加余额，全程只扫一次表
    for select ID, PAYMENT_TYPE, BANKING_ACCOUNTID, AMOUNT, CALCAMOUNT, PAYMENT_DATE
        from PAYMENTS
        where BANKING_ACCOUNTID = :BANK_ID
        and PAYMENT_TYPE in (1,2)
        and PAYMENT_DATE >= :START_DATE
        and PAYMENT_DATE <= :END_DATE
        order by PAYMENT_DATE
        into :ID, :PAYMENT_TYPE, :BANKING_ACCOUNTID, :AMOUNT, :CALCAMOUNT, :PAYMENT_DATE
    do
    begin
        if (PAYMENT_TYPE = 1) then
            CURRENTBALANCE = INIT_BALANCE + AMOUNT;
        else
            CURRENTBALANCE = INIT_BALANCE - AMOUNT;
        suspend;
        -- 累加当前记录的计算值，作为下一行的期初余额
        INIT_BALANCE = INIT_BALANCE + CALCAMOUNT;
    end
end
^^
SET TERM ; ^^

业务查询时直接调用该存储过程即可：

select * from SP_QUERY_PAYMENTS_WITH_BALANCE('01.11.2021', '30.11.2021 23:59:59', :BANKING_ACCOUNTID);

该方案是Firebird 2.5版本下的最优解，性能提升幅度和查询范围内的记录数正相关，记录越多提升越明显。

3. 存量SQL简易优化 #

如果不想修改存储过程架构，可以直接把原SQL中每行两次的存储过程调用合并为一次，性能直接提升1倍：

select 
    p.*,
    case when p.PAYMENT_TYPE = 1 then b.BALANCE + p.AMOUNT
    else b.BALANCE - p.AMOUNT end as CURRENTBALANCE
from PAYMENTS p
left join SP_BALANCE_FOR_DATE_AND_BANKID(p.PAYMENT_DATE, p.BANKING_ACCOUNTID) b on 1=1
where p.ID > 0 
and p.BANKING_ACCOUNTID = :BANKING_ACCOUNTID 
and p.PAYMENT_TYPE in (1,2) 
and p.PAYMENT_DATE >= '01.11.2021' 
and p.PAYMENT_DATE <= '30.11.2021 23:59:59'
order by p.PAYMENT_DATE

4. 持久化余额+触发器方案（可选，适合读远多于写的场景） #

你担心的历史记录修改导致余额错误的问题，可以通过触发器解决：

1. 在PAYMENTS表新增CURRENTBALANCE字段存储计算好的余额
2. 建INSERT/UPDATE/DELETE触发器，当某条记录发生变动时，仅重算该账户下变动记录日期之后的所有余额即可
   该方案下查询直接取字段值，性能达到最高，只要写入操作的QPS不高，完全可以落地。

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