先JOIN再GROUP BY取Top 1极危险,因会生成亿级中间结果;正确做法是先用索引+窗口函数或自关联聚合出每组Top 1,再JOIN。

直接用 GROUP BY + JOIN 拿分组 Top 1,在大数据量下基本等于主动触发全表扫描+临时表+高内存占用——尤其当分组字段基数高(如 user_id 百万级)、排序字段无索引、或 JOIN 表本身很大时,查询很容易从秒级拖到分钟级甚至 OOM。
为什么“先JOIN再GROUP BY”在Top 1场景下特别危险
这类写法本质是把两个大表先拼起来,再分组取最新/最高/最小。哪怕用户表只有 10 万行,订单表有 5000 万行,INNER JOIN 后中间结果可能轻松突破亿级行数,而 Top 1 只需要每组 1 行。
-
SELECT u.name, o.order_id, o.amount FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id ORDER BY o.created_at DESC LIMIT 1—— 这种写法语法错误(o.created_at不在GROUP BY中且非聚合列),但很多开发者会绕开报错改写成子查询或加MAX(created_at),反而更慢 - 即使加了
ORDER BY ... LIMIT 1,MySQL 通常也无法下推到每个分组内,而是先完成全部 JOIN 和分组,再全局排序取 1 - 执行计划里频繁出现
Using temporary; Using filesort,rows值远超单表总行数,就是信号
正确路径:先聚合出Top 1,再JOIN
核心是把“对每个 user_id 找最新订单”这个逻辑独立出来,确保它只扫描订单表一次,且能走索引;再把结果作为小表和用户表关联。
- 给订单表建联合索引:
CREATE INDEX idx_user_created ON orders (user_id, created_at DESC)—— 注意DESC在 MySQL 8.0+ 支持,5.7 实际按 ASC 存但优化器仍可利用 - 用派生表预取 Top 1:
SELECT user_id, order_id, amount, created_at FROM (SELECT user_id, order_id, amount, created_at, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders) t WHERE rn = 1—— MySQL 8.0+ 推荐;5.7 则用变量或自关联 - 若用 MySQL 5.7,避免用户变量方案处理百万行:优先选自关联(correlated subquery)或
LEFT JOIN自连接,虽然语法稍冗,但执行计划更可控 - JOIN 时确保
user_id在两表上都有索引,否则关联又退化为全表扫描
容易被忽略的三个细节
很多人卡在这三步上,不是不会写,是没意识到它们对性能的决定性影响:
- 时间字段类型必须是
DATETIME或TIMESTAMP,不能是字符串(如VARCHAR存 '2024-01-01 10:30:00')——否则索引失效,ORDER BY变成逐行解析 -
PARTITION BY user_id的字段,必须和JOIN条件中的字段完全一致(含 NULL 处理);如果用户表id是INT UNSIGNED,而订单表user_id是普通INT,隐式转换会导致索引失效 - 若 Top 1 逻辑涉及去重(如相同时间有多条),需明确业务规则:用
ROW_NUMBER()(严格取第一条)、RANK()(并列后跳号)还是DENSE_RANK()(并列不跳号)——不同函数生成的执行计划资源消耗差异可达 3 倍


















