MySQL取最大值整条记录：全局、分组Top1与索引优化实战

MySQL 查询某个字段为最大值的整条数据这个需求听起来简单写起来却能分出三六九等。刚入行那会儿我接手一个订单看板的需求要展示每个用户金额最高的一笔订单。我第一反应就是SELECT * FROM orders WHERE amount MAX(amount)结果 MySQL 直接甩给我一个 1111 错误说聚合函数用错了地方。后来换GROUP BY字段又对不上报 1055。再后来加ORDER BY amount DESC LIMIT 1发现并列第一的时候只返回一条业务方说数据不对。前前后后改了五六版才把这几个坑趟平。这篇就把取最大值整条记录这件事从头拆开讲单张表的全局最大值怎么取最快、分组取每组最大值怎么写得又对又快、并列最大值要全要还是只要一条、索引该怎么建、UPDATE和DELETE命中最大行时那个 1093 报错怎么绕。内容偏实操SQL 都能直接拿去跑适合日常写业务 SQL 的同学也适合正在准备面试、被分组取 Top1问住的人。1. 需求拆解为什么取最大值整条数据经常写错1.1 三类高频场景坑点各不相同需求落到 MySQL 里其实分三种完全不同的形态很多人混为一谈所以写出来的 SQL 时对时错。第一种是全局单条最大值比如查订单表里金额最高的那一单、查最新注册的那个用户。这类需求只返回一行通常用ORDER BY xxx DESC LIMIT 1或者等值子查询就能解决是最简单的一种。第二种是分组取每组最大值比如每个用户金额最高的订单、每个班级分数最高的学生、每个商品最近一次的报价。这类需求返回多行每组一行或几行是真正容易翻车的地方GROUP BY用不对、派生表关联写得不好结果就错了。第三种是最大值附带筛选条件比如最近 7 天内金额最高的订单、状态为已支付且分数最高的记录。这种要在子查询和主查询里保持条件一致条件漏写一边结果就会莫名其妙。提示先判断你的需求属于哪一类再选写法。用第二种的写法去解第一种往往慢十倍用第一种的写法去解第二种结果直接错。1.2 为什么 WHERE 里不能直接写聚合函数这是最常见的第一个坑。你如果写过SELECT * FROM orders WHERE amount MAX(amount)一定会收到 1111 报错Invalid use of group function。原因在于 SQL 的执行顺序WHERE是在分组和聚合之前执行的那时候MAX()还没算出来MySQL 自然不让你引用。这个顺序不是 MySQL 独有的几乎所有关系型数据库都这样。正确的做法是把MAX()的结果当成一个已知的值再拿去比较也就是把聚合放到子查询里让它先算完SELECT * FROM orders WHERE amount (SELECT MAX(amount) FROM orders);这种写法在 MySQL 里叫不相关子查询MySQL 5.6 以后会对它做一次优化把结果物化成常量只执行一次不会每行都去算一遍。所以别被子查询一定慢的说法吓到这个场景它其实很稳。1.3 最大值到底取哪一行并列怎么算这是业务上最容易被忽略的一问也是我当年被打回需求的原因。假设订单表里有两条金额都是 9999 的记录你说取金额最大的那一单到底返回几条如果业务含义是给我一个代表那就返回一条用ORDER BY ... LIMIT 1但你得知道返回的是哪一条并不确定除非加二级排序。如果业务含义是所有并列第一都要那就得用等值子查询它会老老实实返回两条。如果业务含义是并列时取时间最新的那条那就是ORDER BY amount DESC, created_at DESC LIMIT 1。我现在的习惯是写这类 SQL 之前先问清楚并列怎么处理然后把这个规则写进注释里。因为取最大值这三个字在业务方嘴里和在数据库里含义经常不是一回事。2. 五种主流写法的对比与选型2.1 子查询等值匹配法最直白但不一定最快SELECT * FROM orders WHERE amount (SELECT MAX(amount) FROM orders);优点是可读性极强语义就是金额等于最大金额一眼看懂并列全部返回。缺点也很明显子查询返回的就是个常量MySQL 只能拿这个常量去主表里做等值查找如果amount上有索引能走索引没索引就是全表扫描。数据量一大就明显了。要注意的一个细节是NULL。如果orders表是空的或者amount全为NULLMAX(amount)返回NULL而amount NULL永远不成立结果是零行。这不是 bug是 SQL 三值逻辑的正常表现但你得知道。2.2 ORDER BY LIMIT 1单条全局最大值的首选SELECT * FROM orders ORDER BY amount DESC LIMIT 1;只返回一行最简单的写法。如果amount上建了索引MySQL 会直接反向扫描 B 树索引取第一条然后再回表拿整行数据执行计划里type显示indexrows很小速度非常快。没有索引的话就得全表扫描加排序。这种写法有个天然优势LIMIT 1会让 MySQL 采用优先队列排序只维护一行候选内存开销极小。对比ORDER BY amount DESC全量排序几十万行差距不是一点半点。短板是并列问题。如果有两条 9999它只给你一条而且不保证是id最小的那条。想稳定就加二级排序ORDER BY amount DESC, id ASC LIMIT 1。2.3 派生表 JOIN分组取每组最大的正统解法分组取最大很多人的第一反应是这么写-- 危险写法不要这么干 SELECT user_id, order_no, MAX(amount) FROM orders GROUP BY user_id;这个 SQL 在ONLY_FULL_GROUP_BY开启时会直接报 1055 错误因为order_no既不在GROUP BY里也不在聚合函数里MySQL 不知道你要取哪一行的order_no。就算你关掉这个 sql_modeMySQL 返回的order_no也是不确定的可能是任意一行业务上就是错的。正统解法是先用派生表算出每组的最大值再 JOIN 回原表SELECT o.* FROM orders o INNER JOIN ( SELECT user_id, MAX(amount) AS max_amount FROM orders GROUP BY user_id ) m ON o.user_id m.user_id AND o.amount m.max_amount;这个写法语义正确并列的也会全部返回。派生表m先算好每组最大值再拿这个结果去匹配原表逻辑清晰。我在 MySQL 5.7 时代基本都是用这套。2.4 窗口函数8.0 之后最舒服的写法MySQL 8.0 引入了窗口函数分组取最大一下子变得优雅起来SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM orders o ) t WHERE rn 1;PARTITION BY user_id按用户分组组内按金额降序排ROW_NUMBER()给每行编号外层过滤rn 1就拿到每组第一名。写法上比派生表 JOIN 少写一个聚合子查询可读性更好而且组内加多个排序条件很方便比如ORDER BY amount DESC, created_at DESC就能解决并列问题。如果你要的是并列全返回把ROW_NUMBER()换成RANK()就行并列的第一名都会是rn 1。DENSE_RANK()则不跳号按业务需要选。代价是窗口函数算完后才在外层过滤优化器没法把rn 1下推到窗口计算里所以中间结果包含全部行。数据量大时排序和物化内存开销都不小。2.5 NOT EXISTS 反连接避免子查询重复扫描还有一种思路是从反面找没有比我更大的记录那我不就是最大的吗SELECT o.* FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM orders b WHERE b.user_id o.user_id AND b.amount o.amount );这个写法在语义上很干净而且如果存在(user_id, amount)复合索引每一行的NOT EXISTS子查询都能走index或者range快速判断实际表现往往不错。配合LIMIT 1使用效果更好。它的问题是当最大值的行数占比很高时比如某用户只有一条记录子查询的判断次数会接近行数 × 组数写不好会退化。下面这张表把五种写法的适用场景拉平对比选型的时候可以直接照着套写法返回行数适用场景前置条件主要短板等值子查询并列全返回全局最大值、需要并列quantity 上有索引更佳无索引时全表扫描ORDER BY LIMIT 11 行全局最大值、只要一条排序列有索引并列取哪条不确定派生表 JOIN并列全返回分组取每组最大分组列聚合列有复合索引派生表可能被物化窗口函数可控1 或并列分组取最大、逻辑复杂MySQL 8.0需要完整排序NOT EXISTS并列全返回分组最大、索引良好复合索引组内行数多时退化3. 索引怎么建让最大值查询真正跑得快3.1 单列最大值一个索引就够全局取最大这种需求ORDER BY amount DESC LIMIT 1是最优解前提是amount上有索引。B 树索引本身是有序的MySQL 从索引尾部往前扫一条就够回表拿到整行整个过程大概是 O(log n) 的定位加一次回表。CREATE INDEX idx_orders_amount ON orders (amount);这里有个细节值得说MySQL 8.0 支持降序索引CREATE INDEX idx_amount_desc ON orders (amount DESC)物理上就按降序存。但你如果只是ORDER BY amount DESC LIMIT 1普通升序索引反向扫描就够了没必要专门建降序索引。降序索引的真正用武之地是混合排序比如ORDER BY a ASC, b DESC这种普通索引组合做不到。3.2 分组取最大复合索引的字段顺序是关键分组取最大索引的字段顺序极其重要。规则是分组列在前排序列在后。-- 分组列 user_id 在前排序列 amount 在后 CREATE INDEX idx_user_amount ON orders (user_id, amount);有了这个复合索引GROUP BY user_id可以利用索引的有序性来分组组内MAX(amount)直接取每个user_id段落的最后一条不用真的把所有行读出来再算。理想情况下执行计划的Extra会显示Using index for group-by这就是所谓的松散索引扫描扫描的行数约等于分组数而不是总行数。我实测过一张 800 万行的订单表GROUP BY user_id取最大值没索引时是三四十秒加上(user_id, amount)复合索引之后降到 1 秒以内。差别就是这么直接。反过来说如果你建的是(amount, user_id)那GROUP BY user_id就用不上索引有序性MySQL 只能扫全索引再临时分组Extra里会出现Using temporary和Using filesort性能差一大截。3.3 用 EXPLAIN 验证别靠猜写完 SQL 别急着交付EXPLAIN一下再交。我看执行计划主要盯这几列type希望是const、eq_ref、ref、range或者index反向扫描取一条。出现ALL就是全表扫描。key实际用了哪个索引。如果是NULL说明没用索引。rows预估扫描行数。这个数字最好接近你期望的结果行数而不是表的总行数。Extra这是信息量最大的一列。Using index表示覆盖索引不用回表Using filesort表示额外排序Using temporary表示临时表Using index for group-by表示松散索引扫描是好消息。EXPLAIN SELECT * FROM orders ORDER BY amount DESC LIMIT 1;如果你看到Using filesort加上rows等于整张表的行数那这条 SQL 在生产环境基本就是个定时炸弹得回去补索引或者改写。3.4 降序索引与覆盖索引的取舍覆盖索引是另一个值得琢磨的点。如果你只查询user_id和amount两个字段用不上回表SELECT user_id, MAX(amount) FROM orders GROUP BY user_id;这条 SQL 只需要(user_id, amount)这一个索引就能完成Extra显示Using index完全不用碰主键回表。速度会更快。但业务上我们通常要拿整行数据订单号、状态、时间那就必须回表。这时候能优化的空间在于让回表的行数尽量少。比如派生表 JOIN 方案里派生表先算出每组最大值只走索引再拿这个很小的结果集去 JOIN 主表回表次数就等于分组数而不是总行数。这就是先缩小结果集再回表的思路比直接全表回表快很多。顺带提一句MySQL 5.7 之后派生表如果被物化并且没有索引优化器会自动给它加索引这就是所谓的 automatic generated index。你可以用EXPLAIN FORMATJSON看到materialized_from_subquery里有没有自动生成的索引。了解这一点你在评估派生表方案性能时心里就有底了。4. 从建表到查询一套可直接抄的实操流程4.1 建表与造测试数据先建一张贴近真实业务的订单表字段名故意用上status、order_no、amount这些常见名字方便你对照自己的业务表CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, amount DECIMAL(12,2) NOT NULL COMMENT 订单金额, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态 0待支付 1已支付 2已完成, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_user_amount (user_id, amount), KEY idx_amount (amount) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;造点测试数据用递归 CTE 造 500 条MySQL 8.0 支持INSERT INTO orders (user_id, order_no, amount, status, created_at) WITH RECURSIVE seq(n) AS ( SELECT 1 UNION ALL SELECT n 1 FROM seq WHERE n 500 ) SELECT (n % 20) 1, CONCAT(NO, LPAD(n, 6, 0)), ROUND(RAND() * 10000, 2), n % 3, DATE_SUB(NOW(), INTERVAL n MINUTE) FROM seq;这样就有 20 个用户、每个用户 25 条订单金额随机时间递增。后面所有例子都基于这张表。4.2 全局最大三种写法的实测对比先看全局金额最高的订单。-- 写法 A等值子查询并列全返回 SELECT * FROM orders WHERE amount (SELECT MAX(amount) FROM orders); -- 写法 B排序取一条 SELECT * FROM orders ORDER BY amount DESC LIMIT 1; -- 写法 C排序取一条并列时取最早创建的 SELECT * FROM orders ORDER BY amount DESC, created_at ASC LIMIT 1;三种写法我都跑过。写法 A 因为amount上有索引执行计划typeref很快写法 B 是typeindex反向扫索引第一条更快一点写法 C 多了一个排序字段如果并列多可能触发 filesort但LIMIT 1让它只维护一个候选影响可忽略。选哪个看业务。需要并列全返回就用 A只需要一个代表就用 B并列规则明确就用 C。心得ORDER BY后面如果有多个字段最好和索引顺序一致否则 MySQL 会在内存里排序。这个表只有(amount)单列索引所以写法 C 在并列数量大时会有额外开销。4.3 分组最大每个用户金额最高的一单这是最典型的场景。派生表 JOIN 版本SELECT o.id, o.user_id, o.order_no, o.amount, o.created_at FROM orders o INNER JOIN ( SELECT user_id, MAX(amount) AS max_amount FROM orders GROUP BY user_id ) m ON o.user_id m.user_id AND o.amount m.max_amount ORDER BY o.user_id;窗口函数版本MySQL 8.0SELECT id, user_id, order_no, amount, created_at FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM orders o ) t WHERE rn 1 ORDER BY user_id;两个结果集应该完全一致前提是每组最大值不并列。如果并列派生表 JOIN 会返回多行而ROW_NUMBER()只返回一行。想让它俩对齐窗口函数换成RANK()。我在 500 行的测试表上跑两者都在 10ms 以内看不出差距。但换成千万级数据差异就出来了派生表方案里派生表可以被idx_user_amount覆盖只涉及user_id和amount扫描行数接近总行数但只读索引窗口函数方案要读整行并做全量排序Using temporary概率更高。4.4 并列最大值全都要怎么改如果业务明确要所有并列第一窗口函数用RANK()SELECT id, user_id, order_no, amount FROM ( SELECT o.*, RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rk FROM orders o ) t WHERE rk 1 ORDER BY user_id, amount DESC;RANK()遇到并列会给相同排名下一名跳号正好满足并列全要的语义。用派生表 JOIN 同样可以因为它本来就是按值匹配的。但要注意一个隐蔽问题如果amount存在NULLMAX()会忽略NULL而o.amount m.max_amount也匹配不上NULL所以并列情况不影响但如果有用户所有订单金额都是NULL这个用户就不会出现在结果里。业务上通常amount是NOT NULL问题不大但心里要有数。4.5 UPDATE/DELETE 命中最大那行绕开 1093 错误这个坑我踩过。当时要把金额最大的那笔订单标记为优质订单很自然地写-- 报错 1093 UPDATE orders SET status 2 WHERE id (SELECT id FROM orders ORDER BY amount DESC LIMIT 1);MySQL 会扔给你ERROR 1093 (HY000): You cant specify target table orders for update in FROM clause意思是不能在更新某张表的同时又在子查询里查询同一张表。这是 MySQL 的限制其他数据库未必有。绕法是把子查询再包一层派生表让优化器把它当成独立的结果集UPDATE orders SET status 2 WHERE id ( SELECT id FROM ( SELECT id FROM orders ORDER BY amount DESC, id ASC LIMIT 1 ) AS tmp );仔细看多包了一层SELECT id FROM (...) AS tmp。因为要求派生表必须有别名所以加了AS tmp。这是绕过 1093 的标准姿势DELETE场景同样适用DELETE FROM orders WHERE id ( SELECT id FROM ( SELECT id FROM orders ORDER BY amount DESC, id ASC LIMIT 1 ) AS tmp );注意批量更新每组最大值的时候别一条条循环发 SQL直接用 JOIN 更新效率高得多第 6 节有示例。5. 常见问题与排查速查表5.1 NULL 值把结果搞没了MAX()会忽略NULL值这既是好事也是坏事。好事是不会被空值污染坏事是当所有值都是NULL时MAX()返回NULL等值子查询匹配不到任何行结果就是空集。排查思路是先把MAX单独跑一遍看看返回值SELECT MAX(amount) FROM orders WHERE user_id 5;如果返回NULL再确认这个用户是否真的有数据SELECT COUNT(*), COUNT(amount) FROM orders WHERE user_id 5;COUNT(*)和COUNT(amount)不一样时说明有NULL值。另外ORDER BY amount DESC时NULL被视为最小值排在最后具体位置还受排序规则影响业务上最好显式写WHERE amount IS NOT NULL。5.2 字段名撞上关键字status、key、order、group、desc、rank、row_number这些词在 MySQL 里都有特殊含义。比如你把一个字段命名成order那么SELECT order FROM t就会直接语法报错。解决办法是用反引号包起来SELECT order, key, rank FROM t;我的建议是建表时就别用关键字当列名改叫order_no、sort_order、rank_no之类。已经建好的表改列名代价很大那就老老实实加反引号所有 SQL 里都统一加上别有的加有的不加容易漏。5.3 字符串比大小与排序规则如果最大值字段是VARCHAR类型比较是按字典序来的9比10大因为逐字符比较时9大于1。这在编号类字段上特别容易出事。比如订单号NO999和NO1000字典序会认为NO999更大。解决办法有两个一是数值语义的字段就用数值类型存别用字符串二是如果历史数据没法改比较时显式转换SELECT * FROM t ORDER BY CAST(no AS UNSIGNED) DESC LIMIT 1;但这样索引就废了。排序规则collation也会影响字符串比较utf8mb4_general_ci是不区分大小写的utf8mb4_bin区分。做纯数值编号比较时_bin更符合预期。5.4 ONLY_FULL_GROUP_BY 报错 1055ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column db.orders.order_no which is not functionally dependent on columns in GROUP BY clause看到这个说明你写了SELECT user_id, order_no, MAX(amount) FROM orders GROUP BY user_id这种。两种解决方式一是改成派生表 JOIN 或窗口函数推荐二是用ANY_VALUE(order_no)包一下能跑但取哪一行不确定业务上等于随机慎用。SELECT user_id, ANY_VALUE(order_no), MAX(amount) FROM orders GROUP BY user_id;我现在基本不用ANY_VALUE因为它掩盖了逻辑问题后面维护的人看不懂到底为啥是这一行。5.5 常见问题速查表报错/现象典型原因排查方向解决方式1111 Invalid use of group functionWHERE 里用了 MAX看 SQL 结构改成子查询或 HAVING1055 ONLY_FULL_GROUP_BY非聚合列不在 GROUP BY看 SELECT 列表派生表 JOIN 或窗口函数1093 target table for updateUPDATE 子查询查同表看 UPDATE 结构包一层派生表加别名结果为空MAX 返回 NULL单独跑 MAX 看值补 IS NOT NULL 或空数据判断返回多行但只想要一行并列值存在看数据是否有重复加 LIMIT 1 和二级排序慢如蜗牛没走索引EXPLAIN 看 key 和 rows建复合索引分组列在前取到的不是最新那条只按值排序看时间字段精度加 created_at 二级排序字符串比较结果反直觉字典序 vs 数值序看字段类型改数值类型或 CAST6. 几个踩坑之后才明白的经验6.1 别一上来就上窗口函数窗口函数确实优雅但不是万能。我见过有人在 5.7 的库上写窗口函数直接语法报错也见过在千万级表上用ROW_NUMBER()全量排序把数据库 CPU 顶到 90%。经验是MySQL 8.0 且数据量在百万以内窗口函数随便用数据量到千万级优先考虑派生表 JOIN因为它只读索引就能算出每组最大值。如果不能升级版本那更是只能老老实实写派生表。判断标准很简单EXPLAIN看你的窗口函数方案有没有出现Using temporary和Using filesortrows是不是接近全表行数。如果答案是是换个写法试试。6.2 数据量到千万级后的变化小表上怎么写都对大表上写法差异会被放大。我总结了几条单表千万级全局取最大值用ORDER BY ... LIMIT 1配单列索引基本都在毫秒级。分组取最大值复合索引(分组列, 聚合列)是刚需没它就不要上线。派生表 JOIN 方案里务必让派生表只涉及索引覆盖的字段避免物化时把整行数据拖进去。超大表可以考虑先用时间范围或业务维度缩小数据量再做最大值计算比如近 30 天内每个用户最高金额条件加上去之后扫描行数可能少一个数量级。另外一个容易被忽略的点是ORDER BY和LIMIT的组合在小数据量下无所谓但数据量大且排序字段没索引时MySQL 会先把全部行读进内存排序触发Using filesort内存不够还会落磁盘速度断崖式下跌。养成看Extra的习惯能省很多排查时间。6.3 一些收尾的小技巧再补几个实用的小点。第一想快速知道最大值出现了几次可以直接SELECT COUNT(*) FROM orders WHERE amount (SELECT MAX(amount) FROM orders);第二批量把每组最大值那行更新掉用 JOIN 比循环快得多UPDATE orders o INNER JOIN ( SELECT user_id, MAX(amount) AS max_amount FROM orders GROUP BY user_id ) m ON o.user_id m.user_id AND o.amount m.max_amount SET o.status 2;第三MAX(id)常被用来取最新一条但要记得自增主键可能有空洞删除过记录严格意义上它代表的是现存记录里 id 最大的不是最后插入的。要保证是最后插入的老老实实用时间字段排序。第四字段注释和索引命名别偷懒。半年后你自己回来看idx_1、idx_2一定懵写成idx_user_amount一眼就知道是干什么的。这个习惯看着琐碎但在团队协作里的价值比你想的大得多。