能否将含SUM-GROUP-BY的子查询改写为单查询以优化性能？

背景需求 #

要优化一条SQL查询，发现移除LEFT JOIN中子查询（基于billing_payment_applied表）能大幅缩短耗时，尝试改写成无此子查询的单查询，但改写后返回记录数远少于原查询，需要排查误区并给出正确改写方案。

原简化查询 #

SELECT a, b, c, ...
  FROM billing_invoice_item 
  LEFT JOIN billing_statement
  LEFT JOIN 
    (SELECT SUM(x), invoice_id 
       FROM billing_payment_applied
       WHERE invoice_id != 0  AND (apply_date <= now())
       GROUP BY invoice_id) AS BAL
  WHERE [...]

自行改写的查询 #

SELECT SUM(CASE WHEN BAL.id is NULL THEN 0 ELSE BAL.amount), ...
  FROM billing_invoice_item 
  LEFT JOIN billing_statement
  LEFT JOIN `billing_payments_applied` bpa on 
            bpa.invoice_id = stm.id 
        AND bpa.invoice_id != 0 
        AND (`apply_date` IS NOT NULL AND `apply_date` <= now())
  WHERE [...]
  GROUP BY bpa.invoice_id

完整原始查询 #

SELECT
    `item`.`id` AS `id`,
    `inv`.`member_id` AS `member_id`,
    `item`.`sku` AS `sku`,
    `item`.`item_name` AS `item_name`,
    `item`.`price_ext` AS `item_price`,                                 
    ROUND( item.`price_ext` - ( item.`price_ext` * ( ( IFNULL( invbal.`amount_applied`, 0 ) * 100 ) / IFNULL( stm.`amount`, 0 ) ) ) / 100, 2 ) AS `item_price_balance`,
    `inv`.`invoice_number` AS `invoice_number`,
    `inv`.`invoice_date` AS `invoice_date`,
    `inv`.`due_date` AS `due_date`,
    CASE
        WHEN diritem.`type` = 'dues_membership_levels' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_dues' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_events' THEN 'events'
        WHEN diritem.`type` = 'hidden_finance_discounts' THEN 'finance'
        WHEN diritem.`type` = 'programs_course_products' THEN 'programs'
        WHEN diritem.`type` != '' THEN 'commerce'
        ELSE 'billing'
    END AS `origin`,
    IFNULL( diritem.`display_name`, `item`.`sku` ) AS `title`,
    diritem.`category` AS `category`,
    CONCAT( 'product_' , IFNULL( diritem.`id`, `item`.`sku` ) ) AS `product_id`,
    '' AS `start_date`,
    '' AS `ticket_name`,
    '' AS `ticket_type`
FROM
    `billing_invoice_item` AS `item`
INNER JOIN `billing_invoice` AS `inv` ON
    item.`invoice_id` = inv.`id`
    AND inv.`invoice_type` = ''
LEFT JOIN `billing_statement` AS `stm` ON
    stm.`invoice_number` = inv.`invoice_number`
    AND stm.`trans_id` = inv.`id`
LEFT JOIN (
    SELECT
        SUM( `amount` * -1 ) AS `amount_applied`,
        `invoice_id`
    FROM
        `billing_payments_applied`
    WHERE
        `invoice_id` != 0
        AND ( `apply_date` IS NOT NULL
            AND `apply_date` <= '2023-05-02 23:59:59' )
    GROUP BY
        `invoice_id`) AS invbal ON
    invbal.`invoice_id` = stm.`id`
LEFT JOIN `shopping_sku` sku ON
    sku.`sku` = item.`sku`
    AND sku.`origin` = 'directory'
LEFT JOIN `directory_items` diritem ON
    sku.`product_id` = diritem.`id`
WHERE
    item.`sku` NOT LIKE 'events_%'
    AND IF( inv.`due_date` < IFNULL( stm.`trans_date`, inv.`invoice_date` )
                                ,
    inv.`due_date`
                                ,
    IFNULL( stm.`trans_date`, inv.`invoice_date` ) ) <= '2023-05-02 23:59:59'

误区分析 #

1. 记录粒度被破坏：原查询返回的是每条发票项记录，而改写后用GROUP BY bpa.invoice_id，会把同一个发票下的所有发票项合并成一条记录，直接导致返回记录数大幅减少。
2. SUM逻辑方向错误：原需求是为每条发票项获取对应发票的总已支付金额，而改写后的SUM是对关联后的支付记录求和，再加上错误的GROUP BY，完全偏离了原查询的逻辑。
3. NULL判断逻辑错误：改写时引用了不存在的别名BAL.id，实际关联的表别名是bpa，且该判断逻辑对最终聚合结果没有实际意义。

正确改写方案 #

方案1：使用关联子查询（推荐，逻辑清晰） #

直接在SELECT语句中通过关联子查询获取对应发票的总已支付金额，去掉原有的LEFT JOIN子查询：

SELECT
    `item`.`id` AS `id`,
    `inv`.`member_id` AS `member_id`,
    `item`.`sku` AS `sku`,
    `item`.`item_name` AS `item_name`,
    `item`.`price_ext` AS `item_price`,
    ROUND( item.`price_ext` - ( item.`price_ext` * ( ( IFNULL( 
        -- 关联子查询获取对应发票的总已支付金额
        (SELECT SUM(amount * -1) FROM billing_payments_applied 
         WHERE invoice_id = stm.id 
           AND invoice_id !=0 
           AND apply_date IS NOT NULL 
           AND apply_date <= '2023-05-02 23:59:59'), 0 ) * 100 ) / IFNULL( stm.`amount`, 0 ) ) ) / 100, 2 ) AS `item_price_balance`,
    `inv`.`invoice_number` AS `invoice_number`,
    `inv`.`invoice_date` AS `invoice_date`,
    `inv`.`due_date` AS `due_date`,
    CASE
        WHEN diritem.`type` = 'dues_membership_levels' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_dues' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_events' THEN 'events'
        WHEN diritem.`type` = 'hidden_finance_discounts' THEN 'finance'
        WHEN diritem.`type` = 'programs_course_products' THEN 'programs'
        WHEN diritem.`type` != '' THEN 'commerce'
        ELSE 'billing'
    END AS `origin`,
    IFNULL( diritem.`display_name`, `item`.`sku` ) AS `title`,
    diritem.`category` AS `category`,
    CONCAT( 'product_' , IFNULL( diritem.`id`, `item`.`sku` ) ) AS `product_id`,
    '' AS `start_date`,
    '' AS `ticket_name`,
    '' AS `ticket_type`
FROM
    `billing_invoice_item` AS `item`
INNER JOIN `billing_invoice` AS `inv` ON
    item.`invoice_id` = inv.`id`
    AND inv.`invoice_type` = ''
LEFT JOIN `billing_statement` AS `stm` ON
    stm.`invoice_number` = inv.`invoice_number`
    AND stm.`trans_id` = inv.`id`
LEFT JOIN `shopping_sku` sku ON
    sku.`sku` = item.`sku`
    AND sku.`origin` = 'directory'
LEFT JOIN `directory_items` diritem ON
    sku.`product_id` = diritem.`id`
WHERE
    item.`sku` NOT LIKE 'events_%'
    AND IF( inv.`due_date` < IFNULL( stm.`trans_date`, inv.`invoice_date` )
        , inv.`due_date`
        , IFNULL( stm.`trans_date`, inv.`invoice_date` ) ) <= '2023-05-02 23:59:59'

方案2：使用窗口函数（适合需要多聚合值的场景） #

通过窗口函数按发票ID分区计算总已支付金额，同时GROUP BY发票项主键保证记录粒度：

SELECT
    `item`.`id` AS `id`,
    `inv`.`member_id` AS `member_id`,
    `item`.`sku` AS `sku`,
    `item`.`item_name` AS `item_name`,
    `item`.`price_ext` AS `item_price`,
    ROUND( item.`price_ext` - ( item.`price_ext` * ( ( IFNULL( invbal.`amount_applied`, 0 ) * 100 ) / IFNULL( stm.`amount`, 0 ) ) ) / 100, 2 ) AS `item_price_balance`,
    `inv`.`invoice_number` AS `invoice_number`,
    `inv`.`invoice_date` AS `invoice_date`,
    `inv`.`due_date` AS `due_date`,
    CASE
        WHEN diritem.`type` = 'dues_membership_levels' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_dues' THEN 'dues'
        WHEN diritem.`type` = 'hidden_commerce_events' THEN 'events'
        WHEN diritem.`type` = 'hidden_finance_discounts' THEN 'finance'
        WHEN diritem.`type` = 'programs_course_products' THEN 'programs'
        WHEN diritem.`type` != '' THEN 'commerce'
        ELSE 'billing'
    END AS `origin`,
    IFNULL( diritem.`display_name`, `item`.`sku` ) AS `title`,
    diritem.`category` AS `category`,
    CONCAT( 'product_' , IFNULL( diritem.`id`, `item`.`sku` ) ) AS `product_id`,
    '' AS `start_date`,
    '' AS `ticket_name`,
    '' AS `ticket_type`,
    -- 窗口函数按发票ID分区计算总已支付金额
    SUM(CASE WHEN bpa.invoice_id = stm.id AND bpa.invoice_id !=0 AND bpa.apply_date IS NOT NULL AND bpa.apply_date <= '2023-05-02 23:59:59' THEN bpa.amount * -1 ELSE 0 END) OVER(PARTITION BY stm.id) AS amount_applied
FROM
    `billing_invoice_item` AS `item`
INNER JOIN `billing_invoice` AS `inv` ON
    item.`invoice_id` = inv.`id`
    AND inv.`invoice_type` = ''
LEFT JOIN `billing_statement` AS `stm` ON
    stm.`invoice_number` = inv.`invoice_number`
    AND stm.`trans_id` = inv.`id`
LEFT JOIN `billing_payments_applied` bpa ON
    bpa.invoice_id = stm.id
LEFT JOIN `shopping_sku` sku ON
    sku.`sku` = item.`sku`
    AND sku.`origin` = 'directory'
LEFT JOIN `directory_items` diritem ON
    sku.`product_id` = diritem.`id`
WHERE
    item.`sku` NOT LIKE 'events_%'
    AND IF( inv.`due_date` < IFNULL( stm.`trans_date`, inv.`invoice_date` )
        , inv.`due_date`
        , IFNULL( stm.`trans_date`, inv.`invoice_date` ) ) <= '2023-05-02 23:59:59'
GROUP BY item.id, inv.member_id, item.sku, item.item_name, item.price_ext, inv.invoice_number, inv.invoice_date, inv.due_date, origin, title, diritem.category, product_id

补充说明 #

两种方案都能保留原查询的记录粒度（每条发票项一条记录），同时避免了原LEFT JOIN子查询的性能开销。建议给billing_payments_applied表的invoice_id和apply_date字段建立联合索引，进一步提升查询效率。

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