如何进一步优化含OR条件的LEFT JOIN多表关联SQL查询？

阿华AIGC实验室

2026-5-11

针对多表关联SQL的性能优化方案与执行计划分析

咱们先拆解下你的查询核心瓶颈：原查询中LEFT JOIN oc_order时使用的OR条件（o.customer_id = c2en.customer_id OR o.unreg_customer_id = c2en.customer_id）是导致慢查询的根本原因——数据库很难为OR关联高效利用单一索引，即使你强制指定了unreg_customer_id索引，也只能优化其中一半的匹配逻辑，另一半仍会低效扫描。接下来我会一步步给你分析执行计划，以及提供更优的优化方案。

第一步：分析查询执行计划

要精准定位问题，先执行EXPLAIN（或EXPLAIN EXTENDED）查看原查询的执行计划，重点关注oc_order表对应的行：

type字段：如果显示ALL说明是全表扫描，range/ref才是高效的索引扫描；
key字段：确认实际使用的索引是否符合预期；
rows字段：预估扫描的行数，数值越大说明扫描成本越高；
Extra字段：如果出现Using index说明用到了覆盖索引（无需回表），Using where表示在过滤条件，Using filesort/Using temporary则表示需要额外排序或临时表，会拖慢性能。

原查询中，OR条件大概率会让oc_order的type变成range甚至ALL，即使加了FORCE INDEX，也只能优化unreg_customer_id的匹配，customer_id的匹配还是会走低效路径。

第二步：针对性优化方案

方案1：拆分OR关联为双JOIN+UNION ALL

把原有的单个LEFT JOIN oc_order拆成两个独立的JOIN（分别匹配customer_id和unreg_customer_id），再用UNION ALL合并结果。这样每个子查询都能高效利用对应字段的索引，彻底规避OR的性能问题：

SELECT 
    en.name AS name,
    en.entity_id,
    COUNT(combined.order_id) AS orders_qty,
    ROUND(SUM(combined.total)) AS orders_sum,
    ROUND(SUM(combined.total) / NULLIF(COUNT(combined.order_id), 0)) AS average_purchase, -- 避免除以0错误
    MIN(combined.date_added) AS first_purchase,
    MAX(combined.date_added) AS last_purchase,
    COALESCE(cp.periodicity, 0) AS periodicity
FROM oc_xile_entity en
LEFT JOIN oc_xile_customer_to_entity c2en ON c2en.entity_id = en.entity_id
-- 提前统计所有用户的periodicity，避免关联子查询重复计算
LEFT JOIN (
    SELECT 
        customer_id,
        COUNT(*) AS periodicity
    FROM oc_order
    WHERE order_status_id <> 0 
      AND date_added <= NOW() - INTERVAL 3 MONTH
    GROUP BY customer_id
) cp ON cp.customer_id = c2en.customer_id
-- 拆分OR条件为两个独立查询，用UNION ALL合并（无需去重，效率更高）
LEFT JOIN (
    SELECT order_id, total, date_added, customer_id
    FROM oc_order
    WHERE order_status_id <> 0
    UNION ALL
    SELECT order_id, total, date_added, unreg_customer_id AS customer_id
    FROM oc_order
    WHERE order_status_id <> 0
) combined ON combined.customer_id = c2en.customer_id
WHERE en.entity_id IS NOT NULL
GROUP BY en.entity_id, cp.periodicity
ORDER BY name ASC
LIMIT 0,700

方案2：创建复合覆盖索引

为oc_order创建包含过滤条件的复合索引，让子查询完全走索引无需回表，进一步提升速度：

-- 针对注册用户订单的覆盖索引（MySQL 5.7及以下版本）
CREATE INDEX idx_order_customer_status ON oc_order(customer_id, order_status_id, order_id, total, date_added);
-- 针对未注册用户订单的覆盖索引（MySQL 5.7及以下版本）
CREATE INDEX idx_order_unreg_customer_status ON oc_order(unreg_customer_id, order_status_id, order_id, total, date_added);

如果你的MySQL版本是8.0+，可以用INCLUDE语法让非索引字段只作为覆盖字段，不参与索引排序，减少索引体积：

CREATE INDEX idx_order_customer_status ON oc_order(customer_id, order_status_id) INCLUDE (order_id, total, date_added);
CREATE INDEX idx_order_unreg_customer_status ON oc_order(unreg_customer_id, order_status_id) INCLUDE (order_id, total, date_added);

方案3：移除多余JOIN

原查询中LEFT JOIN oc_customer是多余的——你只用到了c.customer_id，而这个字段已经存在于oc_xile_customer_to_entity表中，直接用c2en.customer_id即可，减少一次JOIN的开销。

第三步：优化后的执行计划预期

执行优化后的查询，再看EXPLAIN结果：

UNION ALL的两个子查询，分别会用到idx_order_customer_status和idx_order_unreg_customer_status索引，type为ref，Extra显示Using index（覆盖索引）；
customer_periodicity子查询会走对应索引，一次性统计所有用户的周期数据，避免重复计算；
GROUP BY基于en.entity_id（假设是主键），会高效分组，不会生成临时表；
ORDER BY基于en.name，如果oc_xile_entity表有name字段的索引，会直接走索引排序，避免Using filesort。