【MySQL 8 优化实战】GROUP BY + LEFT JOIN 优化多重子查询，性能提升明显

用户79168161804
5 阅读3分钟

前言

在实际业务开发中，经常需要在主表查询时，顺带统计子表的数据。
如果写法不当，很容易造成 SQL 执行慢、扫描表次数过多 的问题。

本文通过一个真实业务 SQL，讲解如何将 多个相关子查询
优化为 一次 GROUP BY + LEFT JOIN，提升性能和可维护性。

一、业务场景说明

涉及三张表：

  • arr_air_waybill：运单主表（1 条运单 1 行）
  • hz_arr_record_order：到货记录表（与运单 1 对 1）
  • hz_arr_tally：理货记录表（与运单 1 对多）

查询运单列表时，需要返回以下与理货相关的字段：

  • 是否已理货
  • 理货记录数量
  • 理货总件数
  • 理货总重量

二、原始 SQL 写法（存在性能问题）

最初的写法是在 SELECT 中对 hz_arr_tally 做多次子查询：

SELECT
    aaw.waybill_code,

    CASE
        WHEN EXISTS (
            SELECT 1
            FROM hz_arr_tally hat
            WHERE hat.waybill_code = aaw.waybill_code
        ) THEN '是'
        ELSE '否'
    END AS isTally,

    (SELECT COUNT(*) 
     FROM hz_arr_tally hat2 
     WHERE hat2.waybill_code = aaw.waybill_code) AS tallyNumber,

    (SELECT COUNT(pieces)
     FROM hz_arr_tally hat3
     WHERE hat3.waybill_code = aaw.waybill_code) AS tallyPieces,

    (SELECT COUNT(weight)
     FROM hz_arr_tally hat4
     WHERE hat4.waybill_code = aaw.waybill_code) AS tallyWeight

FROM arr_air_waybill aaw;

三、存在的问题分析

1️⃣ 重复扫描子表，性能差

  • 每一条运单记录
  • 都会对 hz_arr_tally 执行 4 次查询
  • 运单数量一多，性能急剧下降

本质问题：N 行主表 × 多个子查询

2️⃣ COUNT(pieces) / COUNT(weight) 使用不当

COUNT(weight)

统计的是 weight 不为 NULL 的行数，
并不是重量合计。

⚠️ 正确语义应为：

SUM(weight)
SUM(pieces)

四、优化思路

核心原则：

统计类数据不要在 SELECT 中逐行计算，而是先按业务主键 GROUP BY 一次算完，再 JOIN 回主表。

五、优化后的关键 SQL

LEFT JOIN (
    SELECT
        waybill_code,
        COUNT(*)    AS tallyNumber,
        SUM(pieces) AS tallyPieces,
        SUM(weight) AS tallyWeight
    FROM hz_arr_tally
    GROUP BY waybill_code
) ht
ON ht.waybill_code = aaw.waybill_code

六、SQL 执行逻辑说明（重点）

1️⃣ 子查询：先按运单号汇总理货数据

原始理货表示例：

waybill_codepiecesweight
WB00110100
WB001540
WB002860

子查询执行结果：

waybill_codetallyNumbertallyPiecestallyWeight
WB001215140
WB0021860

👉 一张运单只保留一行汇总数据

2️⃣ LEFT JOIN 回主表

ON ht.waybill_code = aaw.waybill_code

JOIN 后效果：

waybill_codeconsigntallyNumbertallyPiecestallyWeight
WB001张三215140
WB002李四1860
WB003王五NULLNULLNULL

说明：

  • 没有理货记录的运单也能正常查询
  • 所以必须使用 LEFT JOIN

七、是否理货（isTally）的判断方式

CASE
  WHEN ht.waybill_code IS NULL THEN '否'
  ELSE '是'
END AS isTally

原理：

  • JOIN 成功 → 存在理货记录
  • JOIN 失败 → 字段为 NULL

👉 JOIN 结果本身即可判断是否存在数据，无需 EXISTS

八、性能与可维护性对比

对比项优化前优化后
子表扫描次数多次1 次
SQL 可读性差高
统计语义错误风险准确
扩展新统计字段困难简单

九、索引建议

CREATE INDEX idx_tally_waybill
ON hz_arr_tally(waybill_code);

CREATE INDEX idx_air_waybill_flight
ON arr_air_waybill(flight_date1, flight_no1);

十、总结

适合使用 GROUP BY + LEFT JOIN 的场景：

  • 主表 + 明细表（1 对多）
  • 查询主表时需要统计明细数据
  • 明细数据较多，性能要求较高

记住一句话：
统计字段先聚合，再 JOIN；不要在 SELECT 中一行一行算。

如果你想，我还能帮你把下一篇规划成：

  • 《MyBatis 中 GROUP BY + LEFT JOIN 的正确写法》
  • 《EXISTS / 子查询 / JOIN 性能对比》
  • 《MySQL ONLY_FULL_GROUP_BY 实战避坑》

要哪一篇，直接说 👍

avatar
文章
阅读
粉丝