MySQL：复杂关联与分组下的SUM差值计算优化问题

问题核心 #

要按账单版本计算费用类型ID为100的剩余余额（费用总额 - 支付总额），同时必须包含无该费用、无支付记录的账单版本；之前直接LEFT JOIN后SUM出现错误（因一对多关联导致数据重复），嵌套子查询虽正确但大数据下性能差。

高效解决方案思路 #

避免直接关联产生笛卡尔积，先分别对charges（特定费用）和payments（对应支付）按账单版本做预聚合，再与bill_charges做LEFT JOIN，最后计算余额。这样聚合操作仅针对目标数据，大幅减少关联后的数据量，提升性能。

示例SQL代码 #

假设表字段补充如下（若实际字段不同，按需调整关联条件）：

• charges：bill_id, charge_type_id, amount（费用金额）
• payments：charge_id, payment_amount（支付金额，关联charges.charge_id）

SELECT
    bc.bill_nbr,
    bc.version,
    COALESCE(c.total_charge, 0) - COALESCE(p.total_payment, 0) AS remaining_balance
FROM
    bill_charges bc
LEFT JOIN (
    -- 预聚合账单版本下费用类型100的总金额
    SELECT
        bc_inner.bill_nbr,
        bc_inner.version,
        SUM(c.amount) AS total_charge
    FROM
        bill_charges bc_inner
    JOIN charges c ON bc_inner.bill_id = c.bill_id
    WHERE
        c.charge_type_id = 100
    GROUP BY
        bc_inner.bill_nbr, bc_inner.version
) c ON bc.bill_nbr = c.bill_nbr AND bc.version = c.version
LEFT JOIN (
    -- 预聚合账单版本下针对费用类型100的总支付金额
    SELECT
        bc_inner.bill_nbr,
        bc_inner.version,
        SUM(p.payment_amount) AS total_payment
    FROM
        bill_charges bc_inner
    JOIN charges c ON bc_inner.bill_id = c.bill_id
    JOIN payments p ON c.charge_id = p.charge_id
    WHERE
        c.charge_type_id = 100
    GROUP BY
        bc_inner.bill_nbr, bc_inner.version
) p ON bc.bill_nbr = p.bill_nbr AND bc.version = p.version
ORDER BY
    bc.bill_nbr, bc.version;

性能优化点 #

• 索引优化：给以下字段建立联合索引，加速聚合和关联：
  • bill_charges：(bill_nbr, version, bill_id)
  • charges：(bill_id, charge_type_id, charge_id, amount)
  • payments：(charge_id, payment_amount)
• 缩小数据范围：预聚合子查询仅筛选charge_type_id = 100的数据，减少处理的数据量。
• NULL值处理：用COALESCE确保无费用/支付的账单版本余额计算为0，符合需求。

替代方案（窗口函数版） #

如果数据库支持窗口函数，也可以用窗口聚合替代子查询，但预聚合方案在大数据量下性能更稳定：

SELECT
    DISTINCT bc.bill_nbr,
    bc.version,
    COALESCE(SUM(c.amount) OVER (PARTITION BY bc.bill_nbr, bc.version), 0) -
    COALESCE(SUM(p.payment_amount) OVER (PARTITION BY bc.bill_nbr, bc.version), 0) AS remaining_balance
FROM
    bill_charges bc
LEFT JOIN charges c ON bc.bill_id = c.bill_id AND c.charge_type_id = 100
LEFT JOIN payments p ON c.charge_id = p.charge_id
ORDER BY
    bc.bill_nbr, bc.version;

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