找我排查慢SQL的同事里十个有八个都栽在同一个需求上在MySQL里按某个维度分组取出每一组的最新一条记录。比如取每个用户最近一单、每台设备最新一次心跳、每个商品当前价格……这类MySQL查询优化问题在业务系统里太常见了但写法五花八门性能差距可以从毫秒级拉到分钟级。这篇文章就把“分组取最新记录”这个场景彻头彻尾讲透我会给你列出6种主流写法、索引设计要点、EXPLAIN分析思路以及一份千万级数据量的真实调优案例。不管你是刚接手报表SQL的新人还是被线上慢查询折腾的DBA都能从里面找到对应的解决方案。1. 问题模型认识为什么“取每组最新记录”容易写错1.1 一个再常见不过的需求先把这个需求翻译成数据库语言有一张明细表每条记录归属于某个分组比如user_id、device_id、product_id每条记录带一个时间字段或自增ID。现在要按分组维度取每组中“最新”的那一条完整记录。用学校的例子类比就是全校几千个班每个班几十个学生现在要找出每个班分数最高的那个人的所有信息。听起来很简单但难点在于你要的是“整行记录”不是“每个班最高分这个数字”。我说这是MySQL查询优化里最经典的“分组Top-N”问题因为它其实由三步组成分组GROUP BY、组内排序ORDER BY、取第一条LIMIT 1 per group。这三步单独拎出来都不难组合在一起就会暴露很多性能和安全上的坑。常见的业务场景包括订单表取每个用户最近一笔订单、日志表取每台设备的最后一条心跳、库存表取每个SKU的当前价格、登录日志取每个账号的最后一次登录IP。这类查询在报表系统、数据看板、消息推送里几乎天天出现。1.2 新手常见的三种错误写法先说错误写法因为我在评审代码时见得太多了一旦理解了错误在哪正确写法的价值就体现出来了。错误一直接GROUP BY MAX然后SELECT其他非聚合列。SELECT user_id, MAX(order_time), order_amount FROM user_orders GROUP BY user_id;这条SQL的意图很明确取每个用户的最新订单金额。但order_amount并没有被聚合它跟MAX(order_time)没有任何关联关系。在MySQL 5.7及更早版本执行后order_amount取的是该组内“碰巧被读到的那一行”的值结果基本是随机的在开启了ONLY_FULL_GROUP_BY的8.0版本这条SQL会直接报错。所以这种写法本质上就是错的它并不满足“取整行最新记录”的需求。错误二全局ORDER BY LIMIT 1。SELECT * FROM user_orders ORDER BY order_time DESC LIMIT 1;这个只能拿到全表最新的一条不是每个分组的最新一条。但我在不少同事的代码里见过这种写法原因是他们把“最新记录”想成了“一个表一个时间线取最后一条”。如果业务确实只需要一条全局最新那没问题但需求说的是“每个分组”这就南辕北辙了。错误三应用层循环查询。SELECT * FROM user_orders WHERE user_id ? ORDER BY order_time DESC LIMIT 1;这段SQL在单用户维度下确实是最优写法但如果用户有100万个你在应用层写一个for循环去执行100万次就是经典的N1问题。每次查询都有网络往返、SQL解析、执行计划生成100万次下来十几分钟都跑不完。千万别这么干数据库能一次算完的东西就不要搬到应用层反复烧网络。这三种错误写法的共同点是没有理解“取分组最新”本质上是一个需要在数据库内部完成集合运算的逻辑而不是简单地把单条查询重复执行。2. 六种主流SQL写法逐一拆解2.1 关联子查询最直观的“逐行询问”第一种思路是关联子查询对主表每一行都去子查询里问一句“你这个组里比当前时间更新的记录存在吗不存在的话这一行就是我要的”。SELECT * FROM user_orders a WHERE a.order_time ( SELECT MAX(b.order_time) FROM user_orders b WHERE b.user_id a.user_id );这个写法理解成本最低逻辑也直接先在内层算出每个用户的最大下单时间再在外层把时间等于这个最大值的整行拿出来。但它有个必须正视的性能问题如果user_orders表没有合适的索引MySQL会对主表的每一行都执行一次全表子查询1000万行主表就是1000万次全表扫描基本跑不出来。即使有(user_id, order_time)联合索引MySQL仍然需要对外层每一行走索引查询并发一高资源消耗也很可观。它更适合分组数少、总行数可控、临时跑一次统计的场景。另外如果同一用户在同一秒下了两单两条记录的order_time相同这个写法会把两条都带出来。如果业务要求“同一时间只取一条”就必须在排序键上补充ID后面我会专门讲。2.2 派生表JOIN先找最大值再回去配对账第二种思路是先把每个分组的“最新时间”算出来得到一张临时表再把它跟原始表做JOIN匹配出完整记录。SELECT a.* FROM user_orders a INNER JOIN ( SELECT user_id, MAX(order_time) AS latest_time FROM user_orders GROUP BY user_id ) b ON a.user_id b.user_id AND a.order_time b.latest_time;这种写法比关联子查询更好理解也更容易被优化器处理。MySQL 5.7之后引入了derived_merge派生表合并优化很多时候不会真的物化成一张临时表而是直接把子查询的GROUP BY逻辑跟外层JOIN融合在一起。但要注意如果派生表比较复杂或者数据量大到内存放不下MySQL还是会物化成临时表并落盘那就是一次额外IO开销。有一个非常实用的变体值得记下来如果业务上“最新”可以等同于“自增ID最大”比如订单的ID是自增的、ID越大订单越晚那么可以直接取MAX(id)而不是MAX(order_time)。SELECT a.* FROM user_orders a INNER JOIN ( SELECT MAX(id) AS latest_id FROM user_orders GROUP BY user_id ) b ON a.id b.latest_id;这个变体的JOIN条件从“user_id order_time”两个字段变成了“主键id”一个字段回表路径大大缩短性能通常快一个量级。我在后面4.2的调优案例里会用到它。前提是你要跟业务确认清楚自增ID的顺序确实等价于业务时间顺序不能傻乎乎地拿ID做最新判断结果业务上周五手工改了一条老数据ID最大但时间不是最新那就翻车了。2.3 窗口函数MySQL 8.0 的标准答案如果你用的是MySQL 8.0那窗口函数是最好的默认选择。核心语法是ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)在分区内编号我们只要编号为1的行。SELECT id, user_id, order_amount, order_time FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM user_orders t ) r WHERE r.rn 1;这个写法的优势非常明显第一语义完全贴合需求——“按user_id分区组内按order_time倒序排取第一名”第二只需要扫描一遍数据不像关联子查询那样反复执行子查询第三代码可读性极强后续维护的人一眼就能看懂。我知道有同事担心窗口函数性能不好因为它涉及排序。实际上只要在(user_id, order_time)上建了联合索引MySQL可以在扫描索引的同时完成排序EXPLAIN里不会出现Using filesort。MySQL 8.0也提供了RANK()和DENSE_RANK()它们和ROW_NUMBER()的区别在于并列排名的处理。如果业务认为“同一时间的最新记录都算数”那可以用RANK()1如果只想要唯一一条就用ROW_NUMBER()并额外用ID打破并列。2.4 其他实用写法反连接、截断聚合、用户变量除了上面三种常规方案还有三个备选手段特殊场景下能救急。反连接写法LEFT JOIN判空 / NOT EXISTS。思路是找“组内没有比它更新记录”的行SELECT a.* FROM user_orders a LEFT JOIN user_orders b ON a.user_id b.user_id AND a.order_time b.order_time WHERE b.id IS NULL;如果b表里存在一条同用户、时间更晚的记录说明a不是最新JOIN会产生匹配b.id不为NULL只有当a是组内最新的时候b才会是NULL。这种反连接在MySQL 8.0里会被优化成anti join配合索引效果不错。它的问题是SQL理解门槛稍高新人看到“JOIN自己”容易懵而且当时间相等时需要用更复杂的排序键条件。GROUP_CONCAT截断法。另一种取巧思路是用GROUP_CONCAT把组内ID按时间倒序拼成字符串然后截取第一个SELECT user_id, CAST(SUBSTRING_INDEX( GROUP_CONCAT(id ORDER BY order_time DESC), ,, 1 ) AS UNSIGNED) AS latest_id FROM user_orders GROUP BY user_id;再用这个latest_id去JOIN主表。我偶尔在临时排查数据时用这一招因为它一条SQL就能看到每个用户的最新订单ID非常直观。但千万别用在生产环境原因有两个一是GROUP_CONCAT有默认长度限制group_concat_max_len默认1024字节用户订单一多就被截断取到的ID根本不是最新二是它返回的是TEXT类型跟主表的INT主键JOIN时存在隐式转换可能导致索引失效。用户变量法MySQL 5.7时代的过渡方案。老版本没有窗口函数有人用变量模拟ROW_NUMBERSET prev_user : NULL, rn : 0; SELECT id, user_id, order_amount, order_time FROM ( SELECT t.*, IF(prev_user user_id, rn : rn 1, rn : 1) AS rn, prev_user : user_id FROM user_orders t ORDER BY user_id, order_time DESC ) tmp WHERE rn 1;这段逻辑靠变量在扫描过程中记住“上一个user_id”相同分组就累加编号不同分组就重置为1。它在5.7时代确实帮很多人解决了问题但我要明确建议不要把这种写法搬到新项目。MySQL官方文档已经明确说明用户变量的赋值顺序在SELECT表达式中是不保证的依赖输出顺序的写法本质上是在跟优化器博弈到了8.0很多曾经能跑的变量写法会得到莫名其妙的结果。它只适合用来理解老代码不适合作为新方案。3. 索引设计与EXPLAIN实战3.1 让查询跑起来的关键索引无论上面选哪种写法“分组取最新记录”这三个动作都绕不开一个核心索引分区字段 排序字段。以本案例就是(user_id, order_time)。ALTER TABLE user_orders ADD INDEX idx_user_time (user_id, order_time DESC);为什么把user_id放前面因为我们的查询本质上是对user_id做等值或者分组匹配对order_time做范围排序。联合索引的最左前缀原则决定了user_id必须在第一列否则索引无法用于分区过滤。其次ORDER BY order_time DESC如果想利用索引order_time必须紧跟其后。MySQL 8.0支持降序索引可以显式写DESC5.7不支持降序索引但优化器在ORDER BY DESC时通常可以通过反向扫描索引来完成差别不大。这里有个容易忽略的点这个联合索引解决了“快速找到每组最新时间”的问题但最终还要把整行记录拿出来。如果你SELECT的列里有order_amount等不在索引中的字段MySQL需要回表去主键索引取数据每次回表都是一次随机IO。如果这种查询非常频繁可以考虑把常用字段也塞进索引做成覆盖索引ALTER TABLE user_orders ADD INDEX idx_user_time_full (user_id, order_time DESC, order_amount);覆盖索引能让查询完全绕开回表但会让索引体积变大、写入变慢属于典型的空间换时间用之前必须权衡。3.2 EXPLAIN解读不同方案在优化器眼里什么样我见过太多人建了索引就说“怎么还慢”然后一脸茫然。建完索引第一件事永远是EXPLAIN看看优化器到底怎么执行。EXPLAIN SELECT id, user_id, order_amount, order_time FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM user_orders t ) r WHERE r.rn 1;关键看这几个字段type表示访问类型从好到差大致是const、ref、range、index、ALLkey表示实际用到的索引rows是估算扫描行数Extra里如果出现Using filesort或Using temporary说明排序或临时表开销没法避免。我整理过一个简单的对比表供不同方案之间快速参考方案有没有索引时的主访问方式扫描行数特征Extra常见风险关联子查询有索引时外层ALL、内层ref外层全表内层每组一次子查询反复执行派生表JOIN有索引时内外都可能走ref/range先扫全表分组再回表配对派生表物化临时表窗口函数有索引时扫描索引排序基本一次扫描全表Using filesort / 临时表NOT EXISTS有索引时anti join理论一次关联写错条件会笛卡尔积GROUP_CONCAT有索引时range扫全表分组一次Using temporary注意EXPLAIN给出的rows是估算值别把它当成精确行数。我见过EXPLAIN显示rows100、实际跑了1000万的情况尤其在使用窗口函数时优化器对窗口计算的估算经常不准。所以正确姿势是EXPLAIN看执行形态实际运行看耗时两者结合才能下结论。3.3 数据分布对方案选择的影响你可能会问既然窗口函数是8.0标准答案那其他方案是不是可以扔掉了不是。性能问题永远要结合数据分布。我总结了一套经验选择逻辑。如果总行数大、分组数也多、每组只有几条记录窗口函数和派生表JOIN都不错重点是用索引避免filesort。如果分组数很少、每组记录特别多比如就100台设备、1亿条心跳日志那“先按设备分组取MAX(id)”的JOIN方案往往更高效因为窗口函数需要对1亿行做全排序哪怕有索引辅助排序缓冲区的压力也很大。如果业务需求是高频实时查询比如前端页面每次加载都要取当前用户的最新订单那你根本不该用“分组最新”的写法而是直接走“user_id等于什么、ORDER BY order_time DESC LIMIT 1”的单行查询这才是最优路径。没有万能银弹只有结合表结构和数据分布去选方案才能得到真正的“最优”。4. 千万级数据模拟与完整调优过程4.1 准备一张1000万行的订单表空谈理论没有感觉我直接做一个可复现的压测环境。先建测试表CREATE TABLE user_orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_amount DECIMAL(10,2) NOT NULL, order_time DATETIME NOT NULL, KEY idx_user_time (user_id, order_time DESC) ) ENGINEInnoDB;造数据的关键是快速生成1000万行。我习惯先建一张只含0~9的numbers表通过多表CROSS JOIN生成100万用户CREATE TABLE numbers (n INT PRIMARY KEY); INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); INSERT INTO users (id, name) SELECT n1.n n2.n * 10 n3.n * 100 n4.n * 1000 n5.n * 10000 n6.n * 100000, CONCAT(user, n1.n n2.n * 10 n3.n * 100 n4.n * 1000 n5.n * 10000 n6.n * 100000) FROM numbers n1 CROSS JOIN numbers n2 CROSS JOIN numbers n3 CROSS JOIN numbers n4 CROSS JOIN numbers n5 CROSS JOIN numbers n6;然后给每个用户生成10笔订单时间随机落在2023年1月1日之后的约579天内INSERT INTO user_orders (user_id, order_amount, order_time) SELECT u.id, ROUND(RAND() * 1000, 2), TIMESTAMP(2023-01-01) INTERVAL FLOOR(RAND() * 50000000) SECOND FROM users u CROSS JOIN numbers n WHERE n.n 10;这条语句会把100万用户乘以10行生成约1000万订单。如果感觉插入太慢可以先SET autocommit0分批提交或者把binlog关掉再做测试机数据初始化。测试环境操作完记得恢复设置。4.2 从慢到快一次完整调优记录我拿这个表实测了一遍。第一次我用最原始的关联子查询而且故意不建联合索引只留主键SELECT * FROM user_orders a WHERE a.order_time ( SELECT MAX(b.order_time) FROM user_orders b WHERE b.user_id a.user_id );结果是跑到快两分钟我直接kill了。EXPLAIN里清晰可见外层ALL全表扫内层ALL全表扫估算行数看一眼就让人头皮发麻。这不是SQL语法问题而是缺了索引之后MySQL真的会对每一行做一次百万行级别的子查询。接着加上idx_user_time联合索引同一套SQL再跑耗时降到了6秒左右。EXPLAIN里内层子查询变成了ref每次都能通过索引迅速找到该用户的MAX(order_time)。这说明对于1000万行和100万分组这种“组内行数少、分组特别多”的结构只要索引到位关联子查询也能用只不过6秒对于线上报表还是偏慢。然后把SQL换成窗口函数SELECT id, user_id, order_amount, order_time FROM ( SELECT t.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM user_orders t ) r WHERE r.rn 1;这次跑了0.8秒左右。EXPLAIN显示主查询走的type是indexExtra里没有Using filesort因为联合索引的第二个字段已经帮窗口函数完成了组内排序。这就很能说明问题同样是1000万行好的写法和好的索引配合性能可以从“跑不动”变成“亚秒级”。最后我试了MAX(id) JOIN的杀手锏变体SELECT a.* FROM user_orders a INNER JOIN ( SELECT MAX(id) AS latest_id FROM user_orders GROUP BY user_id ) b ON a.id b.latest_id;耗时大约0.4秒比窗口函数还要快一倍。原因不复杂子查询里只需要扫描(user_id, order_time)联合索引就能算出MAX(id)而且MAX(id)可以直接用主键跟外层JOIN避免了按user_id order_time两个字段配对回表。这印证了我前面说的如果业务语义允许用自增ID代表“最新”是性能最好的捷径。4.3 MySQL 5.7 与 8.0 的写法兼容如果你还在用MySQL 5.7窗口函数这条路走不通我的推荐顺序是MAX(id) JOIN或MAX(order_time) JOIN排第一数据量不大时关联子查询排第二NOT EXISTS排第三GROUP_CONCAT和用户变量只在临时排查里用。从5.7升级到8.0时有一个容易踩的坑8.0默认启用了ONLY_FULL_GROUP_BY以前在5.7里能跑的“GROUP BY 非聚合列”语句会直接报错。这其实是好事逼着你把SQL写规范。另一个坑是8.0对索引的命名和可见性规则有变化老脚本里如果存在重复的索引名升级时可能要调整。我的建议是升级后先跑一遍业务核心SQL把报错和变慢的都揪出来别等线上炸了再救火。5. 高频避坑点与排查速查表5.1 分组取最新常见问题排查表我把这些年帮别人排查同类问题时遇到的高频症状整理成了一张速查表。下次遇到类似问题直接对着症状找原因症状可能原因解决思路返回结果多出重复行组内有并列最新时间排序键不唯一在ORDER BY里追加自增ID或使用ROW_NUMBER查出的其他字段跟最新时间对不上SELECT了非聚合字段GROUP BY语义混乱改用子查询、窗口函数或MAX(id) JOINSQL超时或CPU打满关联子查询无索引或应用层循环查询加(user_id, order_time)联合索引改写为窗口函数GROUP_CONCAT结果丢数据group_concat_max_len默认只有1024字节SET SESSION group_concat_max_len 1024000加了索引但EXPLAIN还是Using filesort索引顺序不对或排序方向不匹配确认索引第二字段与ORDER BY方向一致老版本迁移8.0后SQL直接报错ONLY_FULL_GROUP_BY默认开启重写SQL把非聚合列移入子查询5.2 容易忽略的基础细节第一时间字段的选择会影响长远。尽量用DATETIME而不是TIMESTAMPTIMESTAMP有2038年问题和时区转换隐患DATETIME在8.0里还支持小数秒精度。如果你的业务存在跨时区场景要明确存的是哪个时区的时间否则“最新”很容易错乱。第二排序键的稳定性。如果同一时间可能有多条记录必须在ORDER BY里加一个唯一维度来打破并列比如(order_time DESC, id DESC)。不要想当然认为业务里不会出现同一秒两条数据日志系统、批量导入、消息重试都可能导致时间完全一致。第三数据量大到一定程度就别老想着“单条SQL优化到底”。如果分组维度稳定而且业务能接受一定的延迟可以建一张汇总表定时任务每隔几分钟算一次“每组最新记录”业务查询直接读这张小表。SQL再快也不如不查那张大表来得快。这也是生产环境里很常见的架构手段。第四GROUP BY在MySQL里的隐藏排序行为。老版本中GROUP BY会产生内部排序即使你不需要排序结果也可能有额外开销8.0对这块做了不少优化但并不意味着可以完全无视。遇到GROUP BY慢先看EXPLAIN有没有Using temporary。我把这些年的经验浓缩成一句话处理“MySQL里取每组最新记录”先别急着写SQL先回答三个问题——业务里的“最新”到底以什么为准同一时刻会不会多条表的数据分布是什么样的。回答完这三个问题再选择对应的写法配合联合索引性能基本不会差。我个人在8.0环境下的默认选择是窗口函数在5.7环境下首选MAX(id) JOIN。最后再分享一个工作习惯任何慢SQL优化都先用EXPLAIN看清楚执行计划再拿真实数据量去验证绝不在几万行的小表上拍板哪个方案最优。