MySQL高效汇总订单：JOIN中OR导致查询过慢的优化方案

看来你遇到了典型的OR条件导致索引失效的问题！从EXPLAIN结果能明显看出：p_all表的type是ALL（全表扫描），Extra里的Range checked for each record说明MySQL对p表的每一行都要全扫一遍p_all表——当数据量是6k的时候，这就是6k×6k=3600万次操作，速度自然暴跌。

我们的核心优化思路是把OR条件拆解成可利用索引的等值关联，先明确合并组的逻辑，再重构查询。

一、先明确合并组的正确逻辑 #

从你的测试数据和预期结果来看，合并组的规则应该是：

• 主产品：combine为NULL，是合并组的根节点
• 子产品：combine字段指向主产品的ID，属于同一合并组
• 同一组内的所有产品，订单量需要汇总到一起

原查询的OR条件存在逻辑冗余（比如p_all.combine=p.combine会把所有combine为NULL的独立主产品误关联），我们先纠正这个逻辑，再优化效率。

二、优化方案1：预计算分组映射（适用于单向关联场景） #

我们先给每个产品计算出对应的组ID（主产品用自己的ID，子产品用主产品的ID），然后通过等值JOIN关联同组产品，再汇总销量。

带过滤条件的最终查询 #

-- 1. 先计算每个产品的有效销量（应用所有过滤条件）
WITH product_sales AS (
    SELECT 
        i.p AS product_id,
        SUM(i.quantity) AS product_quantity
    FROM i
    JOIN orders o ON o.id = i.order_id  -- 假设i的订单字段是order_id，原语句的i.order可能是笔误
    WHERE 
        o.ordered <= '2018-05-10' 
        AND i.flag = false
        -- 这里添加其他过滤条件
    GROUP BY i.p
),
-- 2. 映射每个产品到对应的组ID
product_groups AS (
    SELECT 
        id,
        COALESCE(combine, id) AS group_id
    FROM p
)
-- 3. 按组汇总销量，关联回每个产品
SELECT 
    p.id,
    COALESCE(SUM(ps.product_quantity), 0) AS total_quantity
FROM p
JOIN product_groups pg ON p.id = pg.id
-- 关联同组的所有产品
JOIN product_groups pg_all ON pg.group_id = pg_all.group_id
LEFT JOIN product_sales ps ON pg_all.id = ps.product_id
GROUP BY p.id;

为什么这个方案更快？ #

• 所有JOIN都是等值关联，能充分利用p.combine、i.p、orders.id等索引
• 先过滤再汇总：product_sales子查询先应用所有过滤条件，大幅减少后续关联的数据量
• 避免了原查询的O(n²)全表扫描，时间复杂度降到O(n log n)

三、优化方案2：递归CTE（适用于多层级关联场景） #

如果你的合并组存在多层级关联（比如A关联到B，B关联到主产品C），可以用递归CTE来获取每个产品的最终根ID：

WITH RECURSIVE product_groups AS (
    -- 递归起始：所有主产品（combine为NULL）
    SELECT 
        id,
        id AS group_id
    FROM p
    WHERE combine IS NULL
    UNION ALL
    -- 递归步骤：关联所有指向当前组内产品的子产品
    SELECT 
        p.id,
        pg.group_id
    FROM p
    JOIN product_groups pg ON p.combine = pg.id
),
product_sales AS (
    -- 同方案1的销量计算逻辑
    SELECT 
        i.p AS product_id,
        SUM(i.quantity) AS product_quantity
    FROM i
    JOIN orders o ON o.id = i.order_id
    WHERE 
        o.ordered <= '2018-05-10' 
        AND i.flag = false
        -- 其他过滤条件
    GROUP BY i.p
)
SELECT 
    p.id,
    COALESCE(SUM(ps.product_quantity), 0) AS total_quantity
FROM p
JOIN product_groups pg ON p.id = pg.id
JOIN product_groups pg_all ON pg.group_id = pg_all.group_id
LEFT JOIN product_sales ps ON pg_all.id = ps.product_id
GROUP BY p.id;

递归CTE在MySQL 8.0+版本支持，能高效处理任意层级的合并关系，而且依然能利用索引。

四、额外的索引优化建议 #

为了进一步提升查询速度，建议添加以下联合索引：

• 给i表添加：CREATE INDEX idx_i_p_order_flag ON i(p, order_id, flag);（覆盖JOIN和过滤条件）
• 给orders表添加：CREATE INDEX idx_o_id_ordered ON orders(id, ordered);（覆盖JOIN和时间过滤）
• 确保p表的combine索引已经存在（你已经建了，没问题）

五、验证结果 #

用你的测试数据运行上述查询，会得到和原查询一致的结果：

• p=1: 12（5+2+1+4）
• p=2: 12（同组销量汇总）
• p=3: 12（同组销量汇总）
• p=4: 3（2+1）

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