MySQL千万级表子查询性能优化咨询：如何高效获取各ID最新日期行？

嘿，这个场景我太熟悉了——千万级别的数据表，用关联子查询取每个分组的最新记录，确实会慢到让人抓狂，毕竟它本质上是逐行执行子查询，相当于跑了几百万次重复的小查询，性能能好才怪。给你几个经过生产环境验证的高效方案，按优先级给你捋清楚：

现在MySQL 8.0及以上版本支持窗口函数，这是处理「分组取Top N」场景的最优解之一，语法清晰，性能拉满：

SELECT id, date
FROM (
    SELECT 
        id, 
        date,
        -- 按id分组，每组内按date倒序排，给第一条标记为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
    FROM tab
) t
WHERE rn = 1;

要是你的业务允许同一个id存在多条相同的最新date记录（比如同一天多次操作），可以把ROW_NUMBER()换成RANK()，这样会保留所有符合条件的记录；如果只需要其中一条，ROW_NUMBER()就够了。

如果你的MySQL版本还停留在5.x，没法用窗口函数，那分组+JOIN的方式比你原来的关联子查询高效N倍：

SELECT a.id, a.date
FROM tab a
JOIN (
    -- 先一次性算出每个id的最大date，只扫一次表
    SELECT id, MAX(date) AS max_date
    FROM tab
    GROUP BY id
) b ON a.id = b.id AND a.date = b.max_date;

这个方法的核心是先通过子查询批量计算所有id的最大日期，只做一次全表扫描（有索引的话更快），再和原表关联匹配，避免了逐行触发子查询的重复开销。

上面两种方法如果没有合适的索引，性能还是上不去——这才是提速的关键！针对你的场景，一定要创建一个联合索引：

CREATE INDEX idx_id_date ON tab(id, date);

这个索引的作用太关键了：

• 对窗口函数来说，它能直接按id分组并按date排序，不需要额外的排序操作，大幅减少CPU消耗；
• 对分组关联的子查询，GROUP BY id和MAX(date)可以直接通过索引快速计算，不用扫全表；
• 原表的JOIN操作也能通过索引快速定位到匹配的记录，避免全表扫描。

• 别贪多：只SELECT你需要的id和date字段，别用SELECT *，减少数据传输和内存占用；
• 缓存优化：如果数据更新不频繁，可以把查询结果缓存到Redis或者本地缓存里，避免重复计算；
• 物化视图：MySQL 8.0+支持物化视图，可以把这个查询结果预计算并存储，定期刷新，适合只读场景。

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