搞了十多年MySQL这类“取每个分组最新一条记录”的需求遇到太多次了。订单表要查每个客户最近的一单商品表要查每个分类最近调价的价格传感器表要查每台设备最近一次上报的状态日志表要查每个模块最近一次报错的信息——本质上都是同一个问题按某个维度分组再在组内按时间或某个序号排序取第一条。这个需求看着简单但网上能搜到的答案五花八门而且很多写法在一百万行数据以下跑得飞快数据一上千万就慢得让人怀疑人生。这篇文章我把能想到的方案全部拆开讲一遍包括窗口函数、子查询JOIN、GROUP_CONCAT、老版本变量法、NOT EXISTS写法以及索引怎么建、执行计划怎么看、数据量大之后怎么优化把每一步背后的原理和坑都讲明白方便你直接拿去用。1. 内容整体设计与思路拆解1.1 先想清楚“最新”到底由什么决定拿到需求别急着写SQL先问一句组内的“最新”是什么含义严格来说这就是一个“组内排序后取第一条”的问题所以必须有一个明确的排序依据。绝大多数场景下是时间字段比如order_time、created_at、event_time。但时间字段有个天然的毛病——可能重复。同一秒下了两单、同一毫秒上报两条日志都很常见。所以更稳妥的做法是找“唯一且递增”的字段。最常见的就是自增主键id。如果业务上保证id越大就代表记录越新那直接用MAX(id)就是“最新”这比用时间字段可靠得多。另外还有一种情况是“按业务序号取最新”比如版本号最大的、流水号最大的道理都一样。我建议在动手写SQL之前先把这个判定标准定死并且写进开发文档。因为不同方案的SQL写法完全不一样等上线后才发现时间字段有重复数据导致结果多出来几行再回头改SQL可就不只是改一条语句的事了。1.2 方案选型的核心矛盾性能与兼容性这个需求在不同版本的MySQL里解法天差地别。MySQL 8.0 提供了窗口函数ROW_NUMBER()写起来最简洁但很多生产环境还停留在 5.7 甚至 5.6窗口函数用不了就得靠子查询 JOIN、变量法这些土办法。选型时主要看三个维度数据量级几十万行和几千万行方案的选择完全不同MySQL版本8.0以上优先窗口函数5.x只能另想办法分组数量与每组大小客户表可能有一千万个老客户但每人就几条订单传感器表可能只有几百台设备但每台上百万条记录。这两种形态对索引和SQL结构的要求截然不同我在后面的章节里会把每个方案的适用条件讲清楚你看完就能组合出最合适自己业务的做法。2. 核心方案窗口函数ROW_NUMBER()的思路与实操2.1 窗口函数为什么是“降维打击”MySQL 8.0 开始支持窗口函数以后这类问题从“折腾半小时”变成了“一条SQL的事”。ROW_NUMBER()的作用就是给每个分组内的行按指定顺序编号取编号为1的就是每组最新记录。直接上例子。假设有一张订单表CREATE TABLE orders ( order_id INT NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, order_amount DECIMAL(10,2) NOT NULL, order_time DATETIME NOT NULL, PRIMARY KEY (order_id), KEY idx_customer_time (customer_id, order_time) );插入一些测试数据后查询每个客户最新一笔订单SELECT order_id, customer_id, order_amount, order_time FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time DESC, order_id DESC) AS rn FROM orders o ) t WHERE rn 1;内层按customer_id分组PARTITION BY组内按order_time倒序排列时间相同时再按order_id倒序兜底。外层过滤rn 1就把每个组的第一条拿出来了。这套SQL的语义非常清晰写出来基本是“自解释”的DBA接手也能一眼看懂这种可读性在团队协作里特别重要。2.2 窗口函数方案的两个隐患注意别被优雅的语法迷惑窗口函数并不总是最快的方案。它要在内存或磁盘上做排序如果分组多、每组数据量大排序开销相当可观。需要关注执行计划里有没有出现Using temporary和Using filesort这两个词出现在分析结果里就说明MySQL正在为窗口函数做额外排序。还有一点容易踩坑如果只想看部分分组的“最新记录”一定要先过滤再开窗。比如只查最近30天下过单的客户的最新一笔订单应该先在子查询里把时间范围过滤掉再对结果开窗这样可以大大减少参与排序的数据量SELECT order_id, customer_id, order_amount, order_time FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time DESC, order_id DESC) AS rn FROM orders o WHERE order_time NOW() - INTERVAL 30 DAY ) t WHERE rn 1;先缩数据范围再排序这个先后顺序很多人会忽略直接对全表开窗结果就是慢没别的原因。3. 兼容性方案子查询JOIN与聚合函数的老手艺3.1 经典分组取最大值JOIN写法如果生产环境还是MySQL 5.7窗口函数用不了最经典的替代方案是“先查每组最新的标识再JOIN原表拿整行”SELECT o.* FROM orders o INNER JOIN ( SELECT customer_id, MAX(order_time) AS max_time FROM orders GROUP BY customer_id ) t ON o.customer_id t.customer_id AND o.order_time t.max_time;思路很好理解子查询先把每个客户最大的order_time算出来JOIN回去匹配得到的就是每个客户最新时间对应的完整订单。这个写法最大的坑我在前面已经暗示过了——如果同一个客户在同一个时刻下了两笔订单这个客户的记录会返回两行。因为order_time max_time并不能保证唯一性。做报表可能觉得无所谓多一行但做列表分页就会出现重复数据。严格一点的写法是取“最新时间对应的最大订单ID”SELECT o.* FROM orders o INNER JOIN ( SELECT customer_id, MAX(order_time) AS max_time FROM orders GROUP BY customer_id ) t ON o.customer_id t.customer_id AND o.order_time t.max_time INNER JOIN ( SELECT customer_id, order_time, MAX(order_id) AS max_order_id FROM orders GROUP BY customer_id, order_time ) t2 ON o.customer_id t2.customer_id AND o.order_time t2.order_time AND o.order_id t2.max_order_id;两层嵌套确实啰嗦但保证了结果行数和组数完全一致不会多出重复记录。3.2 更高效的变体直接取MAX(id)如果业务上自增主键id和时间顺序完全一致也就是“越晚建的订单ID越大”那整个SQL可以大大简化SELECT o.* FROM orders o INNER JOIN ( SELECT customer_id, MAX(order_id) AS max_id FROM orders GROUP BY customer_id ) t ON o.order_id t.max_id;子查询直接算每个分组最大的订单ID再按主键JOIN回原表主键连接在InnoDB里效率极高这种方式比用时间字段JOIN快不少。这种简化方式不是谁都能用关键看业务约束——如果存在补单、回填、历史数据导入之类的操作ID大小和时间先后可能就没关系了这时老老实实用时间字段别偷懒。3.3 手写变量法5.x时代的黑科技MySQL 5.7 之前还有一种比较野的写法利用用户变量模拟窗口函数的功能SELECT order_id, customer_id, order_amount, order_time FROM ( SELECT o.*, IF(prev_customer customer_id, rn : rn 1, rn : 1) AS rn, prev_customer : customer_id FROM orders o, (SELECT prev_customer : NULL, rn : 0) vars ORDER BY customer_id, order_time DESC, order_id DESC ) t WHERE rn 1;原理不复杂先把数据按customer_id和order_time排好序然后一行一行扫如果客户ID没变就编号加1变了就重新从1开始。但这个方案有两个隐患需要知道变量赋值顺序依赖SELECT列表从左到右的执行顺序MySQL官方并不保证这个顺序升级个小版本可能行为就变了ORDER BY必须要做那order_time加上customer_id上必须有合适的索引否则filesort跑一次数据一多就完蛋我自己现在不推荐新项目用它。除非是维护老代码、临时救火改一条SQL跑批否则有这个功夫不如推动升级MySQL到一个能开窗函数的版本。4. 取巧方案GROUP_CONCAT与NOT EXISTS的用法4.1 GROUP_CONCAT能干什么GROUP_CONCAT的思路是把组内记录按时间倒序拼成一个逗号分隔的字符串然后再取第一个元素。SELECT customer_id, SUBSTRING_INDEX( GROUP_CONCAT(order_id ORDER BY order_time DESC, order_id DESC), ,, 1 ) AS latest_order_id FROM orders o GROUP BY customer_id;内层拼出来的是每组一串有序的order_id列表SUBSTRING_INDEX(..., ,, 1)取第一个就是最新的。这个写法的优点是SQL短不依赖窗口函数MySQL 5.7也能跑。但缺点同样明显——GROUP_CONCAT默认最大长度是1024字节如果组内记录多超过长度直接截断得到的latest_order_id就可能是错的而且数据量大时拼接长字符串本身就是一种性能浪费。真要临时用需要先调整SET SESSION group_concat_max_len 10240;我个人的态度是这种写法适合“临时查一下、快速验证数据”的场景不适合放到对账程序或者核心接口里。因为它在数据量大时既不快也不稳出了问题还不够直观。4.2 NOT EXISTS写法语义最严谨另一个被低估的写法是NOT EXISTS它直接翻译业务逻辑“没有比这条更新的记录”SELECT o.* FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM orders o2 WHERE o2.customer_id o.customer_id AND (o2.order_time o.order_time OR (o2.order_time o.order_time AND o2.order_id o.order_id)) );这个方案最大的优势是语义严谨不管时间是否重复都能保证返回组内最后一条同时不需要GROUP BY不会把多个分组的中间结果透出来。性能方面NOT EXISTS往往能走索引提前终止扫描尤其是每组数据量很小、分组很多的情况下反而比窗口函数快。因为对于每一行它只要在索引里确认“不存在更新的行”大概率扫几个索引页就停了。4.3 自连接的隐藏用法还有不少人喜欢用自连接代替NOT EXISTSSELECT o.* FROM orders o LEFT JOIN orders o2 ON o.customer_id o2.customer_id AND (o2.order_time o.order_time OR (o2.order_time o.order_time AND o2.order_id o.order_id)) WHERE o2.order_id IS NULL;逻辑上和NOT EXISTS等价但一定要知道这种写法如果两边数据量大且索引不合适相当于两张大表做连接会产生巨大的中间结果集很容易把数据库搞垮。我会优先推荐NOT EXISTS可读性更清晰优化器对它的处理也通常更优化。5. 索引设计慢查询与快查询的分水岭5.1 联合索引怎么建才能支撑“分组最新”不管上面用哪种方案索引是性能的基石。这类查询最核心的等值字段是分组字段customer_id排序字段是时间或ID所以联合索引结构就是ALTER TABLE orders ADD INDEX idx_customer_time (customer_id, order_time, order_id);为什么是这个字段顺序因为索引最左前缀原则——等值过滤字段放最前面然后紧跟着排序字段让索引内部顺序已经和SQL要求的排序一致MySQL就可以顺序扫描索引不需要对整表数据做filesort。MySQL 8.0还支持降序索引可以更精确地匹配ORDER BY order_time DESC, order_id DESCALTER TABLE orders ADD INDEX idx_customer_time_desc (customer_id, order_time DESC, order_id DESC);如果你的业务查询绝大多数都是 “取每个客户最新N条记录”降序索引性能会有可感知的提升尤其在每组数据量大的场景。5.2 执行计划怎么读拿窗口函数那条SQL举例分析之后重点关注几列type如果看到ALL表示全表扫描这时候不管SQL写得再漂亮都没戏key实际用到的索引是哪棵Extra出现Using filesort或者Using temporary就要警惕说明排序没走索引理想情况下内层子查询应该走idx_customer_time索引Extra里是Using index或Using index condition。排查的方法很简单EXPLAIN SELECT ...你的查询语句多花两分钟看执行计划比盲目加缓存有用得多。我见过很多“慢SQL优化”案例最后发现问题根本不是SQL不对而是索引压根没建。5.3 一个经常被忽略的索引失效场景有朋友会问我在order_time上单独建了索引为什么这条SQL还是慢因为单列索引无法同时服务“按customer_id等值过滤”和“按order_time排序”两个动作。优化器只能在customer_id索引和order_time索引里二选一另一个条件必然要回表处理这就快不了。记住一个口诀等值字段放前面排序字段放后面一起塞进一棵联合索引。6. 数据量大到千万级之后的进阶优化思路6.1 正视深分页问题像“取每个分组最新一条”本身返回的数据量很小但如果你要的是“每页显示20个分组每组取最新一条”很容易写出类似LIMIT 100000, 20的深分页查询这时MySQL会扫描并丢弃前10万行慢得毫无悬念。优化套路是“延迟关联”SELECT o.* FROM ( SELECT order_id FROM orders WHERE customer_id 0 ORDER BY customer_id, order_time DESC LIMIT 100000, 20 ) t INNER JOIN orders o ON t.order_id o.order_id;先只查主键走索引完成分页再回头关联原表取整行。主键关联的代价远小于大字段回表延迟关联在这里能快好几倍。6.2 物化表与预计算如果这个查询是报表系统里的高频查询每天跑几十次那每次现算就不太合理了。更务实的做法是建一张“客户最新订单表”每天用批处理更新一次或者用事件触发器维护查询完全走这张小表。以订单表为例CREATE TABLE customer_latest_order ( customer_id INT NOT NULL PRIMARY KEY, order_id INT NOT NULL, order_amount DECIMAL(10,2) NOT NULL, order_time DATETIME NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );每天凌晨跑一次全量重建或者实时监听订单表写入事件增量更新。查询时直接SELECT * FROM customer_latest_order;这在架构上叫“读模型”和“写模型”分离虽然引入了一点数据一致性延迟但换来的是查询速度质的提升。对绝大多数业务来说凌晨到零点前的数据延迟完全可以接受。6.3 分页读取配合游标深分页还有一条路子是“游标式分页”——不用LIMIT改用“WHERE 条件 上次最后一条记录”的方式SELECT o.* FROM ( SELECT order_id FROM orders WHERE order_time 上次最后一条数据的时间 ORDER BY order_time DESC LIMIT 20 ) t;前端把上一次返回的最后一条记录的order_time作为参数传给下一次查询每次只往后翻查询永远只扫极小范围的数据非常适合滚动加载列表。6.4 关于分区表的个人看法有些文章会推荐用MySQL分区表把数据按时间分到多个分区然后只查最新分区。我的建议是谨慎。分区表在运维上有很多限制分区键必须是主键的一部分跨分区查询反而更慢而且很多DBA对分区表的运维并不熟悉。能用好联合索引解决的问题就不值得引入分区表复杂度。7. 实战对比各方案在百万级数据下的表现7.1 测试环境与样本结构我实际构造了一张500万行左右的订单表包含50万个客户每个客户大约10条订单运行环境是MySQL 8.0配置8核16G内存跑几种主流方案做对比方案SQL写法耗时约说明窗口函数ROW_NUMBER() OVER(...) rn12.3s排序开销大但写法最稳子查询JOINGROUP BY MAX(order_time) JOIN1.8s提前使用物化子查询索引利用好时较快子查询JOINMAX idGROUP BY MAX(id) JOIN主键0.6s走主键连接速度最理想NOT EXISTSNOT EXISTS找更新记录2.1s数据分布影响大单组多条时稍慢GROUP_CONCATGROUP_CONCAT SUBSTRING_INDEX4.5s组内数据多时明显劣势变量法用户变量模拟ROW_NUMBER3.2s不推荐行为不可控这个数据只是参考不同数据分布结果差异很大。但有一个规律是稳定的凡是能提前缩小排序集合、走主键连接、避免filesort的方案大概率就是最优的。7.2 真实场景该怎么选我的选择逻辑大致是这样MySQL 8.0、数据量在几百万以内、代码可读性优先无脑上窗口函数数据量大且业务ID和自增ID能对应上优先用MAX(id) JOIN方案每组数据量比较小、分组非常多尝试NOT EXISTS临时跑批验证数据可以用GROUP_CONCAT但别上线还在维护5.7老代码又不想升级只能做子查询JOIN核心思路永远是先看业务能否提供一条“唯一且递增”的排序字段比如自增ID。如果有优先用它没有再退回到时间字段ID的组合排序。8. 高频问题排查速查表最后整理一份我自己在实际运维中经常遇到的问题清单方便你对着排查现象可能原因解决方案查询结果多出几行重复记录时间字段有并列JOIN条件没有保证唯一性用order_id做唯一兜底或改NOT EXISTSEXPLAIN出现Using filesort联合索引字段顺序不对排序未走索引按等值字段在前、排序字段在后重建索引窗口函数版本不支持MySQL低于8.0改用子查询JOIN或NOT EXISTSGROUP_CONCAT返回的ID不对组内数据超长字符串被截断调大group_concat_max_len或换方案查询很慢但SQL看不出来问题数据分布极度不均某几个分组数据量特别大单独为热点分组做缓存或物化表深分页时慢得不能忍LIMIT offset过大扫描了太多不需要的行改游标式分页或延迟关联加索引后还是慢OR条件或函数包裹字段导致索引失效避免在字段上使用函数改写范围条件这里面最典型的坑就是“时间字段并列导致结果多行”。我建议所有做这个需求的同学写任何一条分组取最新SQL前先确认是不是真的有“唯一排序键”。如果只有一个时间字段赶紧看看有没有并发的可能性别等测试环境里发现多了几条记录才开始查。再通透地讲一点索引不是加得越多越好每多一个索引插入和更新都要多维护一棵B树。像idx_customer_time_desc这种专门为取最新记录加的索引用得很少的话反而成了写入的负担。设计这类索引之前先统计一下实际查询频率一个低频接口不值得为它牺牲整体写入性能。在我经手的项目里最让大家改完之后心情舒畅的永远是先用自增ID的人。把业务“最新一次”的定义提前锚定到ID上后面的SQL设计和索引设计都会简单很多。如果你们的产品也能接受这个约束这个优化问题的难度其实已经降了一半。说到底“高效获取每个分组的最新记录”不是背一条SQL的事而是理解业务语义、匹配合适方案、把索引布对这三件事的组合。把这篇文章里的思路过一遍再去你的生产库里试几条相信会有不一样的感受。
MySQL分组取每组最新记录：窗口函数、索引优化与性能实战
搞了十多年MySQL这类“取每个分组最新一条记录”的需求遇到太多次了。订单表要查每个客户最近的一单商品表要查每个分类最近调价的价格传感器表要查每台设备最近一次上报的状态日志表要查每个模块最近一次报错的信息——本质上都是同一个问题按某个维度分组再在组内按时间或某个序号排序取第一条。这个需求看着简单但网上能搜到的答案五花八门而且很多写法在一百万行数据以下跑得飞快数据一上千万就慢得让人怀疑人生。这篇文章我把能想到的方案全部拆开讲一遍包括窗口函数、子查询JOIN、GROUP_CONCAT、老版本变量法、NOT EXISTS写法以及索引怎么建、执行计划怎么看、数据量大之后怎么优化把每一步背后的原理和坑都讲明白方便你直接拿去用。1. 内容整体设计与思路拆解1.1 先想清楚“最新”到底由什么决定拿到需求别急着写SQL先问一句组内的“最新”是什么含义严格来说这就是一个“组内排序后取第一条”的问题所以必须有一个明确的排序依据。绝大多数场景下是时间字段比如order_time、created_at、event_time。但时间字段有个天然的毛病——可能重复。同一秒下了两单、同一毫秒上报两条日志都很常见。所以更稳妥的做法是找“唯一且递增”的字段。最常见的就是自增主键id。如果业务上保证id越大就代表记录越新那直接用MAX(id)就是“最新”这比用时间字段可靠得多。另外还有一种情况是“按业务序号取最新”比如版本号最大的、流水号最大的道理都一样。我建议在动手写SQL之前先把这个判定标准定死并且写进开发文档。因为不同方案的SQL写法完全不一样等上线后才发现时间字段有重复数据导致结果多出来几行再回头改SQL可就不只是改一条语句的事了。1.2 方案选型的核心矛盾性能与兼容性这个需求在不同版本的MySQL里解法天差地别。MySQL 8.0 提供了窗口函数ROW_NUMBER()写起来最简洁但很多生产环境还停留在 5.7 甚至 5.6窗口函数用不了就得靠子查询 JOIN、变量法这些土办法。选型时主要看三个维度数据量级几十万行和几千万行方案的选择完全不同MySQL版本8.0以上优先窗口函数5.x只能另想办法分组数量与每组大小客户表可能有一千万个老客户但每人就几条订单传感器表可能只有几百台设备但每台上百万条记录。这两种形态对索引和SQL结构的要求截然不同我在后面的章节里会把每个方案的适用条件讲清楚你看完就能组合出最合适自己业务的做法。2. 核心方案窗口函数ROW_NUMBER()的思路与实操2.1 窗口函数为什么是“降维打击”MySQL 8.0 开始支持窗口函数以后这类问题从“折腾半小时”变成了“一条SQL的事”。ROW_NUMBER()的作用就是给每个分组内的行按指定顺序编号取编号为1的就是每组最新记录。直接上例子。假设有一张订单表CREATE TABLE orders ( order_id INT NOT NULL AUTO_INCREMENT, customer_id INT NOT NULL, order_amount DECIMAL(10,2) NOT NULL, order_time DATETIME NOT NULL, PRIMARY KEY (order_id), KEY idx_customer_time (customer_id, order_time) );插入一些测试数据后查询每个客户最新一笔订单SELECT order_id, customer_id, order_amount, order_time FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time DESC, order_id DESC) AS rn FROM orders o ) t WHERE rn 1;内层按customer_id分组PARTITION BY组内按order_time倒序排列时间相同时再按order_id倒序兜底。外层过滤rn 1就把每个组的第一条拿出来了。这套SQL的语义非常清晰写出来基本是“自解释”的DBA接手也能一眼看懂这种可读性在团队协作里特别重要。2.2 窗口函数方案的两个隐患注意别被优雅的语法迷惑窗口函数并不总是最快的方案。它要在内存或磁盘上做排序如果分组多、每组数据量大排序开销相当可观。需要关注执行计划里有没有出现Using temporary和Using filesort这两个词出现在分析结果里就说明MySQL正在为窗口函数做额外排序。还有一点容易踩坑如果只想看部分分组的“最新记录”一定要先过滤再开窗。比如只查最近30天下过单的客户的最新一笔订单应该先在子查询里把时间范围过滤掉再对结果开窗这样可以大大减少参与排序的数据量SELECT order_id, customer_id, order_amount, order_time FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time DESC, order_id DESC) AS rn FROM orders o WHERE order_time NOW() - INTERVAL 30 DAY ) t WHERE rn 1;先缩数据范围再排序这个先后顺序很多人会忽略直接对全表开窗结果就是慢没别的原因。3. 兼容性方案子查询JOIN与聚合函数的老手艺3.1 经典分组取最大值JOIN写法如果生产环境还是MySQL 5.7窗口函数用不了最经典的替代方案是“先查每组最新的标识再JOIN原表拿整行”SELECT o.* FROM orders o INNER JOIN ( SELECT customer_id, MAX(order_time) AS max_time FROM orders GROUP BY customer_id ) t ON o.customer_id t.customer_id AND o.order_time t.max_time;思路很好理解子查询先把每个客户最大的order_time算出来JOIN回去匹配得到的就是每个客户最新时间对应的完整订单。这个写法最大的坑我在前面已经暗示过了——如果同一个客户在同一个时刻下了两笔订单这个客户的记录会返回两行。因为order_time max_time并不能保证唯一性。做报表可能觉得无所谓多一行但做列表分页就会出现重复数据。严格一点的写法是取“最新时间对应的最大订单ID”SELECT o.* FROM orders o INNER JOIN ( SELECT customer_id, MAX(order_time) AS max_time FROM orders GROUP BY customer_id ) t ON o.customer_id t.customer_id AND o.order_time t.max_time INNER JOIN ( SELECT customer_id, order_time, MAX(order_id) AS max_order_id FROM orders GROUP BY customer_id, order_time ) t2 ON o.customer_id t2.customer_id AND o.order_time t2.order_time AND o.order_id t2.max_order_id;两层嵌套确实啰嗦但保证了结果行数和组数完全一致不会多出重复记录。3.2 更高效的变体直接取MAX(id)如果业务上自增主键id和时间顺序完全一致也就是“越晚建的订单ID越大”那整个SQL可以大大简化SELECT o.* FROM orders o INNER JOIN ( SELECT customer_id, MAX(order_id) AS max_id FROM orders GROUP BY customer_id ) t ON o.order_id t.max_id;子查询直接算每个分组最大的订单ID再按主键JOIN回原表主键连接在InnoDB里效率极高这种方式比用时间字段JOIN快不少。这种简化方式不是谁都能用关键看业务约束——如果存在补单、回填、历史数据导入之类的操作ID大小和时间先后可能就没关系了这时老老实实用时间字段别偷懒。3.3 手写变量法5.x时代的黑科技MySQL 5.7 之前还有一种比较野的写法利用用户变量模拟窗口函数的功能SELECT order_id, customer_id, order_amount, order_time FROM ( SELECT o.*, IF(prev_customer customer_id, rn : rn 1, rn : 1) AS rn, prev_customer : customer_id FROM orders o, (SELECT prev_customer : NULL, rn : 0) vars ORDER BY customer_id, order_time DESC, order_id DESC ) t WHERE rn 1;原理不复杂先把数据按customer_id和order_time排好序然后一行一行扫如果客户ID没变就编号加1变了就重新从1开始。但这个方案有两个隐患需要知道变量赋值顺序依赖SELECT列表从左到右的执行顺序MySQL官方并不保证这个顺序升级个小版本可能行为就变了ORDER BY必须要做那order_time加上customer_id上必须有合适的索引否则filesort跑一次数据一多就完蛋我自己现在不推荐新项目用它。除非是维护老代码、临时救火改一条SQL跑批否则有这个功夫不如推动升级MySQL到一个能开窗函数的版本。4. 取巧方案GROUP_CONCAT与NOT EXISTS的用法4.1 GROUP_CONCAT能干什么GROUP_CONCAT的思路是把组内记录按时间倒序拼成一个逗号分隔的字符串然后再取第一个元素。SELECT customer_id, SUBSTRING_INDEX( GROUP_CONCAT(order_id ORDER BY order_time DESC, order_id DESC), ,, 1 ) AS latest_order_id FROM orders o GROUP BY customer_id;内层拼出来的是每组一串有序的order_id列表SUBSTRING_INDEX(..., ,, 1)取第一个就是最新的。这个写法的优点是SQL短不依赖窗口函数MySQL 5.7也能跑。但缺点同样明显——GROUP_CONCAT默认最大长度是1024字节如果组内记录多超过长度直接截断得到的latest_order_id就可能是错的而且数据量大时拼接长字符串本身就是一种性能浪费。真要临时用需要先调整SET SESSION group_concat_max_len 10240;我个人的态度是这种写法适合“临时查一下、快速验证数据”的场景不适合放到对账程序或者核心接口里。因为它在数据量大时既不快也不稳出了问题还不够直观。4.2 NOT EXISTS写法语义最严谨另一个被低估的写法是NOT EXISTS它直接翻译业务逻辑“没有比这条更新的记录”SELECT o.* FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM orders o2 WHERE o2.customer_id o.customer_id AND (o2.order_time o.order_time OR (o2.order_time o.order_time AND o2.order_id o.order_id)) );这个方案最大的优势是语义严谨不管时间是否重复都能保证返回组内最后一条同时不需要GROUP BY不会把多个分组的中间结果透出来。性能方面NOT EXISTS往往能走索引提前终止扫描尤其是每组数据量很小、分组很多的情况下反而比窗口函数快。因为对于每一行它只要在索引里确认“不存在更新的行”大概率扫几个索引页就停了。4.3 自连接的隐藏用法还有不少人喜欢用自连接代替NOT EXISTSSELECT o.* FROM orders o LEFT JOIN orders o2 ON o.customer_id o2.customer_id AND (o2.order_time o.order_time OR (o2.order_time o.order_time AND o2.order_id o.order_id)) WHERE o2.order_id IS NULL;逻辑上和NOT EXISTS等价但一定要知道这种写法如果两边数据量大且索引不合适相当于两张大表做连接会产生巨大的中间结果集很容易把数据库搞垮。我会优先推荐NOT EXISTS可读性更清晰优化器对它的处理也通常更优化。5. 索引设计慢查询与快查询的分水岭5.1 联合索引怎么建才能支撑“分组最新”不管上面用哪种方案索引是性能的基石。这类查询最核心的等值字段是分组字段customer_id排序字段是时间或ID所以联合索引结构就是ALTER TABLE orders ADD INDEX idx_customer_time (customer_id, order_time, order_id);为什么是这个字段顺序因为索引最左前缀原则——等值过滤字段放最前面然后紧跟着排序字段让索引内部顺序已经和SQL要求的排序一致MySQL就可以顺序扫描索引不需要对整表数据做filesort。MySQL 8.0还支持降序索引可以更精确地匹配ORDER BY order_time DESC, order_id DESCALTER TABLE orders ADD INDEX idx_customer_time_desc (customer_id, order_time DESC, order_id DESC);如果你的业务查询绝大多数都是 “取每个客户最新N条记录”降序索引性能会有可感知的提升尤其在每组数据量大的场景。5.2 执行计划怎么读拿窗口函数那条SQL举例分析之后重点关注几列type如果看到ALL表示全表扫描这时候不管SQL写得再漂亮都没戏key实际用到的索引是哪棵Extra出现Using filesort或者Using temporary就要警惕说明排序没走索引理想情况下内层子查询应该走idx_customer_time索引Extra里是Using index或Using index condition。排查的方法很简单EXPLAIN SELECT ...你的查询语句多花两分钟看执行计划比盲目加缓存有用得多。我见过很多“慢SQL优化”案例最后发现问题根本不是SQL不对而是索引压根没建。5.3 一个经常被忽略的索引失效场景有朋友会问我在order_time上单独建了索引为什么这条SQL还是慢因为单列索引无法同时服务“按customer_id等值过滤”和“按order_time排序”两个动作。优化器只能在customer_id索引和order_time索引里二选一另一个条件必然要回表处理这就快不了。记住一个口诀等值字段放前面排序字段放后面一起塞进一棵联合索引。6. 数据量大到千万级之后的进阶优化思路6.1 正视深分页问题像“取每个分组最新一条”本身返回的数据量很小但如果你要的是“每页显示20个分组每组取最新一条”很容易写出类似LIMIT 100000, 20的深分页查询这时MySQL会扫描并丢弃前10万行慢得毫无悬念。优化套路是“延迟关联”SELECT o.* FROM ( SELECT order_id FROM orders WHERE customer_id 0 ORDER BY customer_id, order_time DESC LIMIT 100000, 20 ) t INNER JOIN orders o ON t.order_id o.order_id;先只查主键走索引完成分页再回头关联原表取整行。主键关联的代价远小于大字段回表延迟关联在这里能快好几倍。6.2 物化表与预计算如果这个查询是报表系统里的高频查询每天跑几十次那每次现算就不太合理了。更务实的做法是建一张“客户最新订单表”每天用批处理更新一次或者用事件触发器维护查询完全走这张小表。以订单表为例CREATE TABLE customer_latest_order ( customer_id INT NOT NULL PRIMARY KEY, order_id INT NOT NULL, order_amount DECIMAL(10,2) NOT NULL, order_time DATETIME NOT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );每天凌晨跑一次全量重建或者实时监听订单表写入事件增量更新。查询时直接SELECT * FROM customer_latest_order;这在架构上叫“读模型”和“写模型”分离虽然引入了一点数据一致性延迟但换来的是查询速度质的提升。对绝大多数业务来说凌晨到零点前的数据延迟完全可以接受。6.3 分页读取配合游标深分页还有一条路子是“游标式分页”——不用LIMIT改用“WHERE 条件 上次最后一条记录”的方式SELECT o.* FROM ( SELECT order_id FROM orders WHERE order_time 上次最后一条数据的时间 ORDER BY order_time DESC LIMIT 20 ) t;前端把上一次返回的最后一条记录的order_time作为参数传给下一次查询每次只往后翻查询永远只扫极小范围的数据非常适合滚动加载列表。6.4 关于分区表的个人看法有些文章会推荐用MySQL分区表把数据按时间分到多个分区然后只查最新分区。我的建议是谨慎。分区表在运维上有很多限制分区键必须是主键的一部分跨分区查询反而更慢而且很多DBA对分区表的运维并不熟悉。能用好联合索引解决的问题就不值得引入分区表复杂度。7. 实战对比各方案在百万级数据下的表现7.1 测试环境与样本结构我实际构造了一张500万行左右的订单表包含50万个客户每个客户大约10条订单运行环境是MySQL 8.0配置8核16G内存跑几种主流方案做对比方案SQL写法耗时约说明窗口函数ROW_NUMBER() OVER(...) rn12.3s排序开销大但写法最稳子查询JOINGROUP BY MAX(order_time) JOIN1.8s提前使用物化子查询索引利用好时较快子查询JOINMAX idGROUP BY MAX(id) JOIN主键0.6s走主键连接速度最理想NOT EXISTSNOT EXISTS找更新记录2.1s数据分布影响大单组多条时稍慢GROUP_CONCATGROUP_CONCAT SUBSTRING_INDEX4.5s组内数据多时明显劣势变量法用户变量模拟ROW_NUMBER3.2s不推荐行为不可控这个数据只是参考不同数据分布结果差异很大。但有一个规律是稳定的凡是能提前缩小排序集合、走主键连接、避免filesort的方案大概率就是最优的。7.2 真实场景该怎么选我的选择逻辑大致是这样MySQL 8.0、数据量在几百万以内、代码可读性优先无脑上窗口函数数据量大且业务ID和自增ID能对应上优先用MAX(id) JOIN方案每组数据量比较小、分组非常多尝试NOT EXISTS临时跑批验证数据可以用GROUP_CONCAT但别上线还在维护5.7老代码又不想升级只能做子查询JOIN核心思路永远是先看业务能否提供一条“唯一且递增”的排序字段比如自增ID。如果有优先用它没有再退回到时间字段ID的组合排序。8. 高频问题排查速查表最后整理一份我自己在实际运维中经常遇到的问题清单方便你对着排查现象可能原因解决方案查询结果多出几行重复记录时间字段有并列JOIN条件没有保证唯一性用order_id做唯一兜底或改NOT EXISTSEXPLAIN出现Using filesort联合索引字段顺序不对排序未走索引按等值字段在前、排序字段在后重建索引窗口函数版本不支持MySQL低于8.0改用子查询JOIN或NOT EXISTSGROUP_CONCAT返回的ID不对组内数据超长字符串被截断调大group_concat_max_len或换方案查询很慢但SQL看不出来问题数据分布极度不均某几个分组数据量特别大单独为热点分组做缓存或物化表深分页时慢得不能忍LIMIT offset过大扫描了太多不需要的行改游标式分页或延迟关联加索引后还是慢OR条件或函数包裹字段导致索引失效避免在字段上使用函数改写范围条件这里面最典型的坑就是“时间字段并列导致结果多行”。我建议所有做这个需求的同学写任何一条分组取最新SQL前先确认是不是真的有“唯一排序键”。如果只有一个时间字段赶紧看看有没有并发的可能性别等测试环境里发现多了几条记录才开始查。再通透地讲一点索引不是加得越多越好每多一个索引插入和更新都要多维护一棵B树。像idx_customer_time_desc这种专门为取最新记录加的索引用得很少的话反而成了写入的负担。设计这类索引之前先统计一下实际查询频率一个低频接口不值得为它牺牲整体写入性能。在我经手的项目里最让大家改完之后心情舒畅的永远是先用自增ID的人。把业务“最新一次”的定义提前锚定到ID上后面的SQL设计和索引设计都会简单很多。如果你们的产品也能接受这个约束这个优化问题的难度其实已经降了一半。说到底“高效获取每个分组的最新记录”不是背一条SQL的事而是理解业务语义、匹配合适方案、把索引布对这三件事的组合。把这篇文章里的思路过一遍再去你的生产库里试几条相信会有不一样的感受。
