如何优化使用交易分段的账龄报表SQL查询性能

核心问题定位 #

你当前写法的最大性能损耗是：每个分段都对journal_voucher_details表做了一次全量过滤扫描，加上总余额字段一共要扫描6次表，数据量越大耗时线性增长，加索引收益低的核心原因是多次重复扫描的开销没有被消除。

具体优化方案 #

1. 替换多次子查询为单次条件聚合 #

仅扫描1次交易明细表，通过CASE WHEN分支判断账龄分段，直接按ledger_id分组计算所有分段的数值，性能可以提升5~10倍，同时分段调整时只需要修改分支条件，扩展性更强。
优化后的SQL示例（根据数据库类型调整日期差函数即可）：

WITH slab_agg AS (
    SELECT 
        ledger_id,
        -- 分段间隔为2天的计算逻辑
        COALESCE(SUM(CASE WHEN DATEDIFF('2021-10-23', transaction_date) <= 2 THEN dr_amount - clearance_amount ELSE 0 END), 0) AS slab1,
        COALESCE(SUM(CASE WHEN DATEDIFF('2021-10-23', transaction_date) BETWEEN 3 AND 4 THEN dr_amount - clearance_amount ELSE 0 END), 0) AS slab2,
        COALESCE(SUM(CASE WHEN DATEDIFF('2021-10-23', transaction_date) BETWEEN 5 AND 6 THEN dr_amount - clearance_amount ELSE 0 END), 0) AS slab3,
        COALESCE(SUM(CASE WHEN DATEDIFF('2021-10-23', transaction_date) BETWEEN 7 AND 8 THEN dr_amount - clearance_amount ELSE 0 END), 0) AS slab4,
        COALESCE(SUM(CASE WHEN DATEDIFF('2021-10-23', transaction_date) > 8 THEN dr_amount - clearance_amount ELSE 0 END), 0) AS slab5,
        COALESCE(SUM(dr_amount - clearance_amount), 0) AS balance
    FROM journal_voucher_details
    WHERE 
        cr_amount = 0
        AND transaction_date >= '2021-10-01'
        AND transaction_date <= '2021-10-24'
    GROUP BY ledger_id
)
SELECT 
    l.id_ledger AS ledger_id,
    l.customer_id,
    l.title AS ledger,
    COALESCE(sa.slab1, 0) AS slab1,
    COALESCE(sa.slab2, 0) AS slab2,
    COALESCE(sa.slab3, 0) AS slab3,
    COALESCE(sa.slab4, 0) AS slab4,
    COALESCE(sa.slab5, 0) AS slab5,
    COALESCE(sa.balance, 0) AS balance
FROM ledgers l
LEFT JOIN slab_agg sa ON l.customer_id = sa.ledger_id;

2. 新建覆盖索引消除回表开销 #

之前索引无效大概率是索引字段不匹配查询逻辑，新建如下联合覆盖索引，查询时直接从索引取数不需要回表访问原表，性能再提升2~3倍：

CREATE INDEX idx_jvd_aging_calc ON journal_voucher_details(cr_amount, transaction_date, ledger_id, dr_amount, clearance_amount);

3. 可选优化：日期计算前置避免字段运算 #

如果数据库优化器无法自动转换运算逻辑，可以将日期差的判断改为对transaction_date的直接范围判断，进一步提升索引命中率，比如DATEDIFF('2021-10-23', transaction_date) <=2等价于transaction_date >= '2021-10-21'。

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