将带SUM和多WHERE条件的子查询改写为JOIN结构以提升查询性能

原查询使用了相关子查询逻辑：外层每返回1行t表的记录，都要对内层tasks_sub表执行一次独立的匹配求和计算，当表数据量较大时，整体时间复杂度为O(n²)，是性能瓶颈的核心原因。

我们可以先做一次预聚合计算好所有符合条件的分组求和值，再和主表关联，仅需要两次全表扫描（有索引时甚至不需要全表）即可完成计算，性能提升显著：

SELECT 
    t.*,
    COALESCE(s.sum_delivered, 0) AS sum_delivered
FROM tasks t
LEFT JOIN (
    SELECT
        `table`,
        proces_id,
        order_id,
        `index`,
        SUM(delivered) AS sum_delivered
    FROM tasks
    GROUP BY `table`, proces_id, order_id, `index`
) s ON (
    (s.`table` = t.`table` AND s.proces_id = t.proces_id) 
    OR s.order_id = t.order_id
) AND s.`index` = t.`index`;

如果你的数据库对JOIN中的OR条件优化不好，可以进一步拆分为两个聚合子查询分别匹配两个OR分支，再合并结果，避免OR导致的索引失效：

SELECT
    t.*,
    COALESCE(s1.sum_delivered, 0) + COALESCE(s2.sum_delivered, 0) AS sum_delivered
FROM tasks t
LEFT JOIN (
    SELECT `table`, proces_id, `index`, SUM(delivered) AS sum_delivered
    FROM tasks
    GROUP BY `table`, proces_id, `index`
) s1 ON s1.`table` = t.`table` AND s1.proces_id = t.proces_id AND s1.`index` = t.`index`
LEFT JOIN (
    SELECT order_id, `index`, SUM(delivered) AS sum_delivered
    FROM tasks
    GROUP BY order_id, `index`
) s2 ON s2.order_id = t.order_id AND s2.`index` = t.`index`;

注意：如果存在同时满足两个OR分支的记录，第二种写法会重复计算，需要根据你的业务逻辑判断是否需要去重，原查询的逻辑是不会重复计算的，所以如果有重叠场景，可以在预聚合阶段加上distinct逻辑处理

要进一步提升性能，可以给tasks表建立如下覆盖索引，避免回表查询：

• 针对第一个优化方案：INDEX idx_task_query (table, proces_id, order_id, index, delivered)
• 针对第二个拆分OR的优化方案：建立两个索引
  • INDEX idx_group1 (table, proces_id, index, delivered)
  • INDEX idx_group2 (order_id, index, delivered)

内容的提问来源于stack exchange，提问作者Michał Moskal