 静默错误：原因、正确写法与索引优化)
1. 那句看起来没毛病的 SQL坑就埋在 SELECT 列表里排查一个对账报表时同事把 SQL 甩过来说这条语句我在测试库跑了三遍结果一模一样上了生产就少了两千多条记录。语句只有四行核心操作就是把MySQL里的group by和max()凑在一起用。这类问题我这些年遇到过不下十次每次长得都很像查询不报错结果看着也像那么回事但取出来的行和那个最大值根本不在同一条记录上。这就是group by配max()最容易埋雷的地方。它跟语法错误、类型转换失败不一样不会给你任何提示数据库会安安静静地返回一份部分正确的结果。新手看不出问题老手如果没盯着数据看也容易漏过去。这篇文章就是把这个坑从头到尾拆开讲它为什么会发生、什么条件下会暴露、几种正确的替代写法各自适合什么场景、索引和执行计划层面又有哪些门道。不管你是刚学 SQL 没多久还是写了几年业务查询只要涉及每个分组取最大/最新一条这类需求都值得花点时间看完。先把结论摆在前面SELECT列表里出现非聚合列、同时又在GROUP BY里分组这是所有问题的源头。数据库在语义上不知道该选哪一行给你MySQL 的做法是随便挑一行而随便的定义并不稳定。1.1 先还原现场一段典型的成绩统计查询假设有一张成绩表结构大概是这样CREATE TABLE score ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, class VARCHAR(20) NOT NULL COMMENT 班级, name VARCHAR(32) NOT NULL COMMENT 学生姓名, score INT NOT NULL COMMENT 分数, PRIMARY KEY (id), KEY idx_class_score (class, score) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;需求很朴素每个班里分数最高的那位同学是谁。很多人第一反应就是这么写SELECT class, name, MAX(score) AS max_score FROM score GROUP BY class;如果你的 MySQL 开启了ONLY_FULL_GROUP_BY5.7.5 之后的默认状态这条语句会直接被拒绝报错信息大概是这样ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column demo.score.name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by但如果你为了先把需求跑通顺手把ONLY_FULL_GROUP_BY从sql_mode里摘掉了语句立刻能跑结果也会返回比如classnamemax_score一班张三98二班李四95看起来完全符合预期。问题在于这个name跟这个98之间没有任何绑定关系。1.2 非聚合列到底取自哪一行MySQL 官方文档对这类查询的表述是服务器可以自由地从每个分组中选择任意一个值除非这些值本身相同否则选出来的值是不确定的nondeterministic。这句话翻译成人话就是——name列返回的是该分组里被读到的某一行的名字而不是取得MAX(score)那一行的名字。我做一个具体的表格来对比一下假设一班的数据是这样idclassnamescore1一班张三722一班李四983一班王五65上面那条 SQL 在一班这个分组里max_score一定是 98这是确定的。但name可能是张三、李四、王五中的任何一个——实际取到谁取决于存储引擎按什么顺序把行喂给聚合算子通常是扫描到的第一行也就是id1的张三。于是你会得到张三 98 分这样一个彻底错误、但语法完全合法的结果。这就是最要命的地方报错是好事静默出错才是灾难。报表里出现张三 98 分而且张三真实分数只有 72这种错误在人工抽查时极难发现因为你得把原始明细翻出来逐条比对。1.3 为什么这种错误能瞒过测试环境我见过好几次同一个模式开发在本地库写完 SQL跑出来结果正确提交上去到了生产或者口径核对环节才发现数据对不上。原因通常有两个。第一个原因是数据分布。测试库的构造数据往往是按id顺序插入的而最高分刚好也落在第一条或者最后一条上于是随便挑一行恰好就挑对了。生产环境数据是乱序写入、批量导入、甚至做过归档搬移的行的物理顺序完全不同取值自然就飘了。第二个原因是执行计划变化。同一张表数据量从 1000 行涨到 5000 万行之后优化器可能从全表扫描切换到索引扫描甚至先走覆盖索引再回表。扫描路径一变被读到的第一行就换了人非聚合列的取值跟着变。这种漂移不是代码改出来的是数据量和统计信息改出来的所以最难查。提示判断一条GROUP BY查询是否安全有个很快的自检方法——把SELECT列表里的每一列都过一遍问自己这一列是不是要么在GROUP BY里要么被聚合函数包着要么能被分组列唯一确定。三者都不满足这条 SQL 就有隐患。2. ONLY_FULL_GROUP_BY 不是来找麻烦的它是来救你的每次看到有人为了跑通查询而全局关掉ONLY_FULL_GROUP_BY我都会劝一句这个开关关掉的不是限制是把数据库对你代码的保护给拆了。它存在的唯一目的就是在编译期把 1.2 节里那种必然出错的写法直接拦下来而不是放任它跑出一个看起来正确的错误答案。2.1 报错信息逐字拆解回到那条报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column demo.score.name which is not functionally dependent on columns in GROUP BY clause拆成四段看每一段都在告诉你具体哪里有问题Expression #2 of SELECT listSELECT列表里第 2 个表达式出了状况从 1 开始计数对应的是name那一列。这个编号在写了几十列的宽查询里非常有用能直接定位。is not in GROUP BY clause它没出现在GROUP BY里。contains nonaggregated column demo.score.name它是一个没被聚合函数包裹的裸列库名表名列名都给你标出来了。which is not functionally dependent on columns in GROUP BY clause重点在这半句它说的不是绝对不能出现而是不能被分组列唯一确定。换句话说MySQL 拦的不是非聚合列而是无法从分组列推导出来的非聚合列。2.2 关掉它等于把静默出错合法化关掉的方式很多我列一下常见几种顺便说说各自的适用边界-- 1. 查看当前会话生效的 sql_mode SELECT SESSION.sql_mode; SELECT GLOBAL.sql_mode; -- 2. 只对当前会话生效推荐用这种做临时验证 SET SESSION sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION;如果要改全局或者配置文件写法和风险就不一样了# my.cnf / my.ini [mysqld] sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION# 容器环境下通过启动参数传入注意整串要写完整不要只写一半导致其他模式被清空 docker run -d --name mysql8 \ -e MYSQL_ROOT_PASSWORDyour_password \ mysql:8.0 \ --sql-modeSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION我的建议是生产库永远不要关。理由很简单一旦关掉1.3 节说的那种漂移就从查询直接失败升级成查询静默返回错误数据后者的排查成本高出一个量级。真要在本地临时验证某种写法能不能跑用SET SESSION就够了退出连接自动失效不会污染全局配置。顺带说一个易踩的细节SET GLOBAL sql_mode只影响之后新建的连接已经存在的连接还是用老配置。改完记得用SHOW PROCESSLIST看一眼有没有长连接没断开或者干脆重启。这一点在新老连接混杂的中间件池化场景里特别容易翻车。2.3 函数依赖GROUP BY 主键时为什么不报错有个现象很多人困惑为什么下面这条语句在ONLY_FULL_GROUP_BY下能正常跑-- 假设 order_detail 的主键是 id SELECT id, order_no, amount, MAX(batch_no) FROM order_detail GROUP BY id;原因就在函数依赖四个字。id是这张表的主键主键唯一确定一行所以当你按id分组时每一组里必然只有一行那么order_no、amount的取值没有任何歧义——它一定是那一行自己的值。MySQL 5.7.5 之后引入了对函数依赖的检测能力能够识别出这类情况并放行。同理如果GROUP BY的是一个唯一索引列或者唯一索引的全部列MySQL 也能推导出函数依赖。但注意普通索引不行因为普通索引允许重复值一组里可能有几百行非聚合列就又回到随便挑一行的状态了。这里有个隐形的坑业务上唯一不等于数据库上唯一。比如你觉得order_no肯定不重复但表上没建唯一索引只有普通索引MySQL 就不认。这时候你只能要么补上唯一约束要先确认数据真的没重复要么老老实实把列写进GROUP BY。2.4 ANY_VALUE() 的正确使用姿势与滥用风险MySQL 5.7 引入了ANY_VALUE()函数专门用来告诉数据库我知道这里取值不确定我接受。它的作用是压住ONLY_FULL_GROUP_BY的报错检查SELECT class, ANY_VALUE(name) AS any_name, MAX(score) AS max_score FROM score GROUP BY class;我见过不少人把它当成万能钥匙哪儿报错往哪儿套。这个习惯很危险。ANY_VALUE()只是把编译期的检查挪到了运行期取值不确定性一点没减少只是不再报错而已。它真正适合的场景是那些你确实不关心取值、只关心分组聚合结果的统计查询。比如每个品类下的商品数量、价格中位数随便带一个商品名做展示这种情况下带哪个名字都无所谓用ANY_VALUE()是合理的。但如果是每个班最高分的学生是谁这种明确要求行对齐的需求用它就是在给自己埋雷。注意ANY_VALUE()的语义是任意一个值不是第一个值也不是最小值。它不做任何排序保证。有人以为它等价于MIN()这是彻底的误解。3. 就算写法合规MAX() 自己的边界也得摸清把group by的写法改对了不代表max()就万事大吉。这个聚合函数本身还有几个容易被忽略的行为特性我在实际项目里都踩过。3.1 三条和 NULL 有关的规则第一条MAX()会忽略 NULL 值。如果一组的score是(NULL, 60, 80)结果是 80NULL 不参与比较。第二条如果一组里全都是 NULLMAX()返回 NULL。这一条本身没问题但它会在后续关联时引发麻烦——NULL 参与等值比较的结果是未知不是真。也就是说WHERE t.ct g.mct在g.mct IS NULL时永远匹配不上整组数据会在 JOIN 里凭空消失。这个坑我在 6.1 节会展开讲一个真实案例。第三条GROUP BY的列本身如果是 NULL所有 NULL 会被归到同一个分组里。这一点和DISTINCT的行为一致跟很多人NULL 各算一组的直觉不一样。-- 验证 MAX 忽略 NULL 的具体表现 SELECT MAX(score) FROM score WHERE class 三班; -- 若该班全为 NULL返回 NULL SELECT COUNT(*) AS total, COUNT(score) AS not_null_cnt FROM score GROUP BY class; -- total 与 not_null_cnt 的差值就是该组 NULL 的个数3.2 字符串、日期与隐式转换下的最大值可能不是你以为的那个这是另一个高频误区。MAX()的比较规则完全取决于列的数据类型和排序规则不是看起来像数字就按数字比。如果score被设计成了VARCHAR(10)很多从 Excel 导入的表就是这样那么MAX(100)和MAX(99)比的是字符串逐字符从左到右比第一个字符1 9所以结果是99而不是100。这个结果在数值语义下是错的但在字符串语义下完全正确。同样的道理适用于日期用字符串存储的情况。2024-9-5和2024-10-01放在一起比前者的第 6 位是9后者的第 6 位是1字符串比较会认为2024-9-5 2024-10-01。所以日期用字符串存的时候一定保证零填充格式统一成YYYY-MM-DD否则MAX()出来的最新时间是错的。另外还有排序规则collation的影响。utf8mb4_general_ci和utf8mb4_bin下的字符串比较结果可能不同涉及大小写、重音字符时尤其明显。我建议做这类聚合前先确认一下列的类型和三要素SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND TABLE_NAME score;3.3 并列最大值一组里出现两条相同的 MAX还有个特别隐蔽的情况一个分组里有两行或多行的聚合值完全相等。比如一班有两位同学都是 98 分。这时候无论你用哪种先取 MAX 再关联回去的写法只要关联条件里没有能区分这两行的列结果都会膨胀成两行甚至更多。很多人拿到结果后一看每班只应该有一个最高分怎么一班出来两个人第一反应是怀疑 JOIN 写错了实际上是数据本身存在并列。处理并列有两种思路取决于业务语义业务语义处理方式并列全部要展示接受多行结果关联条件保持只有分组键 聚合值只要其中一条在关联条件或排序里追加次级排序键如id最大者优先需要稳定可复现必须指定完整的排序规则不能依赖默认行为需要只要一条时用窗口函数是最干净的SELECT * FROM ( SELECT s.*, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC, id DESC) AS rn FROM score s ) t WHERE t.rn 1;ORDER BY score DESC, id DESC里的id DESC就是那个次级排序键它保证了即使分数并列结果也是确定且可复现的。这个细节看着小但在做对账和审计类需求时是硬要求——同一份数据跑两次结果必须一样。4. 取每组最新/最大那条完整记录的几种正规解法对比理解了坑在哪接下来就是怎么改。我把常用的几种写法都摆出来说说各自的适用场景和隐藏问题。4.1 子查询 JOIN 法最通用的写法先算出每组的聚合值再关联回原表拿完整行SELECT s.class, s.name, s.score FROM score s INNER JOIN ( SELECT class, MAX(score) AS max_score FROM score GROUP BY class ) g ON s.class g.class AND s.score g.max_score;这个写法有两个必须留意的点。第一关联条件必须同时包含分组键和聚合值。我见过有人写成ON s.score g.max_score漏掉了s.class g.class结果一班 98 分的人会跟二班的最高分 98 也匹配上跨组串数据。这种错误在小数据量下不容易发现因为分数很少正好撞上。第二MySQL 5.7 和 8.0 对派生表derived table的处理不一样。5.7 默认会把子查询物化成临时表8.0 的优化器可能会做派生表合并derived merge把子查询展开到外层。合并之后执行计划可能完全不同原本跑得很快的查询换版本后变慢。如果遇到这种情况可以用EXPLAIN确认一下是不是走了合并必要时用优化器开关或者NO_MERGE提示干预。4.2 窗口函数法MySQL 8.0 之后取每组 Top-N 首选窗口函数。它的优势不只是写法短更重要的是语义明确、结果确定。-- 取每个班最高分的那一行并列时取 id 最大的 SELECT class, name, score FROM ( SELECT class, name, score, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC, id DESC) AS rn FROM score ) t WHERE t.rn 1;如果并列的都要把ROW_NUMBER()换成RANK()或者DENSE_RANK()即可这也是窗口函数比 JOIN 法更灵活的地方——ROW_NUMBER严格编号、RANK跳号、DENSE_RANK不跳号三种语义按业务挑。代价是性能。窗口函数需要扫描分区内的所有行并做排序在数据量大、分组数少的情况下排序开销可能比索引扫描 子查询大不少。我的经验是分组数多、每组行数少时用窗口函数很舒服分组数少、每组几百万行时要慎重先 EXPLAIN 看排序开销。4.3 反连接与 EXISTS 法反连接的思路是找出没有比它更大的行的那一行SELECT s.* FROM score s LEFT JOIN score s2 ON s2.class s.class AND ( s2.score s.score OR (s2.score s.score AND s2.id s.id) ) WHERE s2.id IS NULL;WHERE s2.id IS NULL意味着在s2里找不到任何一个比s更大的行那s就是组内最大的。这里的OR (s2.score s.score AND s2.id s.id)就是在处理并列用id做次级比较。这种写法的优点是只扫原表不需要物化子查询缺点是在(class, score)上必须有合适的索引否则每一行都要在组内做一次嵌套循环复杂度接近 O(n²)。千万级以上的表如果索引没建好这种查询能把数据库拖垮。EXISTS版本语义一样只是写法不同SELECT s.* FROM score s WHERE NOT EXISTS ( SELECT 1 FROM score s2 WHERE s2.class s.class AND (s2.score s.score OR (s2.score s.score AND s2.id s.id)) );我个人更倾向于用LEFT JOIN ... IS NULL这一版因为优化器对它的处理更稳定而且EXPLAIN出来的执行计划更好读。4.4 用户变量法为什么现在别再用了在 8.0 之前网上流传很广的一种写法是用用户变量逐行比较-- 8.0 之前的写法现在不推荐 SET prev_class : NULL; SET rank : 0; SELECT class, name, score FROM ( SELECT class, name, score, rank : IF(prev_class class, rank 1, 1) AS rn, prev_class : class FROM score ORDER BY class, score DESC ) t WHERE t.rn 1;这种写法的问题非常多依赖ORDER BY和变量赋值在同一层的求值顺序而 SQL 标准并不保证这个顺序优化器调整执行计划就可能让结果错乱。MySQL 8.0.13 之后用户变量在查询中的赋值已经被标记为弃用未来版本可能直接移除。可读性极差别人接手时几乎看不懂意图。完全无法利用索引做优化本质上是强制全表扫描加逐行处理。我唯一能想到的使用场景是维护一个老版本 MySQL 5.6 且无法升级的系统。除此之外能用窗口函数就用窗口函数能用 JOIN 就用 JOIN。4.5 四种写法横向对比写法版本要求并列最大值行为索引友好度主要风险子查询 JOIN全版本并列全部返回高可走覆盖索引关联条件漏写分组键会串组窗口函数8.0可选三种排名语义中需要排序分组少、组内行多时排序开销大反连接 LEFT JOIN全版本需手工加次级比较高依赖联合索引索引缺失时退化成嵌套循环用户变量全版本已弃用依赖排序不稳定低基本全扫执行计划变化即结果错误选型时我的判断顺序是先看版本有 8.0 优先窗口函数分组数少、每组行数极大且索引齐全用反连接需要跨版本兼容、逻辑简单的用子查询 JOIN。5. EXPLAIN 里的三条路径松散索引扫描、紧凑索引扫描与临时表写法改对了只是第一步同一个查询在索引条件不同时性能可能差几百倍。这一节讲讲MAX()配GROUP BY时MySQL 到底有几种执行方式。5.1 三种执行路径分别长什么样执行路径EXPLAIN Extra 关键字段触发条件性能量级相对松散索引扫描Using index for group-by单表、GROUP BY列是某索引最左前缀、聚合只有MIN/MAX最快按分组数跳读索引紧凑索引扫描Using index无 temporaryGROUP BY列能形成索引前缀但存在SUM/COUNT/AVG等其他聚合中等需顺序扫索引临时表Using temporary; Using filesort分组列没有可用索引前缀或存在范围条件破坏前缀最慢可能落盘松散索引扫描是最理想的状态。它的原理是既然索引里(class, score)是按 class 有序、组内按 score 有序的那要拿每个 class 的 MAX(score)只需要跳到每个 class 的第一个位置取该组最后一条的 score 就行完全不用扫描组内所有行。分组数只有几十个的话实际读取的索引条目也是几十条量级。触发它需要满足几个条件缺一个都不行查询只涉及单张表。GROUP BY使用的列构成某个索引的最左前缀且SELECT里没有其他未在GROUP BY中的列参与聚合。聚合函数只用到MIN()和MAX()。WHERE条件里对分组列使用等值条件可以使用范围条件通常会破坏松散扫描退化成紧凑扫描。WHERE class IN (一班,二班)这种是可以走松散扫描的WHERE class 一班这种范围条件一般就不行了。5.2 用 EXPLAIN 判断你的查询落在哪条路径养成一个习惯写完带聚合的查询先EXPLAIN一遍再交付。EXPLAIN SELECT class, MAX(score) FROM score GROUP BY class\G重点看三个字段type理想情况是range或index出现ALL就是全表扫描。key实际用了哪个索引。Extra这里的信息量最大Using index for group-by/Using temporary/Using filesort都在这一栏。如果出现Using temporary说明优化器没法利用索引有序性只能先把所有行按分组键塞进临时表再聚合。数据量大时这个临时表会从内存溢出到磁盘性能断崖式下跌。还有一种情况容易被忽略EXPLAIN显示走了索引没有临时表但实际执行还是很慢。这时候用EXPLAIN ANALYZE8.0.18 之后支持能看到真实的行数和耗时EXPLAIN ANALYZE SELECT class, MAX(score) FROM score GROUP BY class;它会输出实际的actual rows和actual time跟预估的行数一对比就知道统计信息是不是过期了。统计信息过期时优化器可能选错索引跑一次ANALYZE TABLE score;往往能立竿见影。5.3 联合索引的列顺序怎么定针对每个分组取最大这类需求联合索引的列顺序基本是固定的分组列在前排序列在后。-- 分组键是 class聚合/排序列是 score ALTER TABLE score ADD INDEX idx_class_score (class, score);为什么不能反过来因为(score, class)这个索引是按 score 全局有序的同一个 class 的行散落在索引各处根本没法按 class 跳读。列顺序错了索引基本等于白建。如果需求是每个用户取最新一条订单排序键是时间ALTER TABLE orders ADD INDEX idx_user_ct (user_id, created_at);更进一步如果查询里还要带出几个字段做展示可以考虑把它们加到索引末尾做成覆盖索引避免回表-- 覆盖索引查询只需要 user_id、created_at、amount 时不用回表 ALTER TABLE orders ADD INDEX idx_user_ct_amt (user_id, created_at, amount);代价是索引变大、写入变慢。我的经验是覆盖索引只给那些高频且字段少的查询加别为了省几次回表把索引堆成十几个字段那样写放大的成本会远超读的收益。提示GROUP BY在 MySQL 5.7 及以前会隐式排序相当于自动加了ORDER BY 分组列当时流行的GROUP BY xxx ORDER BY NULL就是为了干掉这个多余排序。8.0 移除了隐式排序这句ORDER BY NULL已经没有实际作用但也不会报错。从 5.7 升级上来时别指望靠它继续保证输出有序。6. 真实项目里踩过的三个具体场景理论讲完了说几个我亲手踩过的坑都是那种文档里不会写、但真出事很疼的类型。6.1 取每个设备最后一条上报全组 NULL 导致整组消失项目背景是物联网设备上报每台设备不定时上报状态有一张device_report表记录device_id、report_time、status。需求是查每台设备最后一次上报的状态。我当时的写法SELECT d.device_id, d.report_time, d.status FROM device_report d INNER JOIN ( SELECT device_id, MAX(report_time) AS last_time FROM device_report GROUP BY device_id ) g ON d.device_id g.device_id AND d.report_time g.last_time;上线后发现设备总数是 12000 台查出来的结果只有 11800 多台少了将近 200 台。逐个排查才找到原因report_time这一列允许为 NULL历史遗留设计有 200 台设备的所有上报记录的report_time都是 NULL。MAX(NULL)返回 NULL所以子查询里这 200 台的last_time是 NULL。而外层d.report_time g.last_time在遇到 NULL 时比较结果是未知永远不成立这 200 台设备就被整体过滤掉了。修复方式是不依赖聚合值做等值关联改成先按设备分组排序、取序号为 1 的那一行SELECT device_id, report_time, status FROM ( SELECT device_id, report_time, status, ROW_NUMBER() OVER ( PARTITION BY device_id ORDER BY report_time IS NULL, report_time DESC, id DESC ) AS rn FROM device_report ) t WHERE t.rn 1;ORDER BY report_time IS NULL这个技巧值得记一下report_time IS NULL的结果是 0 或 1把 NULL 行排到最后非 NULL 的按时间倒序排在前面。这样全 NULL 的设备也能正常输出一行只是时间显示为空。如果必须留在 5.7可以用关联子查询配合IFNULL兜底但那会引入额外的复杂度能升版本尽量升。6.2 分组统计再关联明细行数翻倍与 COUNT 对不上另一次数仓对账业务方反馈页面显示订单数 500导出明细 800 行。排查发现 SQL 是这么写的-- 错误写法 SELECT g.user_id, g.order_cnt, o.order_no, o.amount FROM ( SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id ) g INNER JOIN orders o ON o.user_id g.user_id WHERE g.order_cnt 5;问题很明显g里每个用户只有一行但o里每个用户有多行关联之后每个用户的行数等于他自己的订单数跟order_cnt混淆了。这不是MAX()的坑但它跟group by配聚合函数的用法高度同源——把统计结果和明细数据混在一条 SQL 里输出很容易出现粒度错位。判断粒度是否一致有个简单办法列一个分组键的清单然后看每个表在这个清单上的唯一性。上面的例子里g在user_id上唯一o在user_id上不唯一所以 JOIN 后会膨胀。修复思路有两种。如果是统计展示就别 JOIN 明细SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY user_id HAVING COUNT(*) 5;如果确实要统计 明细一起出明确粒度后再关联或者干脆用窗口函数在明细行上直接算SELECT user_id, order_no, amount, COUNT(*) OVER (PARTITION BY user_id) AS order_cnt FROM orders;顺便说一句HAVING和WHERE的区别这也是高频错误WHERE在分组前过滤HAVING在分组后过滤聚合结果。写WHERE COUNT(*) 5会直接报ERROR 1111: Invalid use of group function因为聚合函数不能出现在WHERE里。6.3 5.7 升 8.0 后结果变了隐式排序消失带来的取值漂移这个坑最有意思也最值得警惕。有个报表查询长期运行在 5.7 上写法就是 1.1 节里那条有问题的 SQL——SELECT class, name, MAX(score) ... GROUP BY class内网报表sql_mode被整体放开了。两年多来结果一直稳定因为 5.7 的GROUP BY会隐式排序加上数据是按id顺序插入的name每次都能取到同一行。升级到 8.0 之后隐式排序被移除同样的数据、同样的 SQL取到的name变了报表数字跟着变了。业务方一看历史趋势出现断点第一反应是数据出问题了实际上数据完好无损只是那个看起来稳定的取值来源消失了。这件事给我的教训是任何依赖默认行为的结果都不算稳定。隐式排序、默认排序规则、未指定的次级排序键、优化器的执行计划选择这些都是当前恰好如此而不是语义上必然如此。判断标准很简单——如果换一个数据库版本、换一台机器、换一种数据导入顺序结果会不会变会变就是在赌。升级版本前我现在的固定动作里一定包含一条把所有GROUP BY查询捞出来逐条检查SELECT列表里有没有非聚合裸列。捞取方式可以翻代码也可以在慢查询日志和performance_schema里筛-- 从 performance_schema 找出最近执行过的、带 GROUP BY 的语句摘要 SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE %GROUP BY% ORDER BY COUNT_STAR DESC LIMIT 50;配合灰度环境跑一遍全量回归基本能把这类问题提前暴露出来。7. 排查这类问题的固定动作最后把我处理分组取最大/最新这类问题时的动作流程列一下形成肌肉记忆之后能省很多时间。第一步永远是验证粒度。列出GROUP BY后面的所有列问一句这组列能唯一确定我想要的粒度吗。如果答案是否定的那后面的一切都在错误的前提上。第二步是检查SELECT列表。每一列过一遍在GROUP BY里、被聚合函数包着、还是靠函数依赖被唯一确定。三种都不满足立刻改写。第三步是确认并列情况。用一条简单的 SQL 看看真的有没有并列-- 找出存在并列最高值的分组 SELECT class, score, COUNT(*) AS cnt FROM score s WHERE s.score (SELECT MAX(score) FROM score WHERE class s.class) GROUP BY class, score HAVING cnt 1;第四步是固定排序规则。只要涉及取一条就必须明确指定完整的ORDER BY包括次级排序键让结果在任何执行计划下都可复现。第五步是看执行计划。EXPLAIN一遍确认没有意外的Using temporary、Using filesort确认索引列顺序对得上分组列和排序列。数据量大的表还要跑一次EXPLAIN ANALYZE对比预估行数和实际行数。第六步是跨版本验证。如果系统有升级计划把关键查询在目标版本上跑一遍对比结果比看文档猜行为要靠谱得多。补一个我自己常用的小技巧写这类查询时先在表上造几条故意制造歧义的测试数据——同一分组内两行分数并列、一行时间字段为 NULL、分组列存在 NULL 值。这三条数据一进去写法有问题的 SQL 立刻就会露出马脚比在生产上等着出事强太多了。数据构造完成之后整个验证过程通常不超过十分钟但能省下的对账时间可能是好几天。关于MAX()和GROUP BY还有一个细节值得一提在分组数极大比如几十万分组的用户行为表的场景下无论哪种写法性能瓶颈往往不在写法本身而在分组键的选择性和索引的覆盖程度。这时候把GROUP BY换成WHERE EXISTS的过滤式写法或者先做时间范围裁剪再分组效果通常比纠结聚合函数的选择更明显。多测几版执行计划比看多少篇教程都有用。 
长河君 · 2026/9/18 2:11:08 · 阅读 1.2k MySQL GROUP BY 与 MAX() 静默错误：原因、正确写法与索引优化
1. 那句看起来没毛病的 SQL坑就埋在 SELECT 列表里排查一个对账报表时同事把 SQL 甩过来说这条语句我在测试库跑了三遍结果一模一样上了生产就少了两千多条记录。语句只有四行核心操作就是把MySQL里的group by和max()凑在一起用。这类问题我这些年遇到过不下十次每次长得都很像查询不报错结果看着也像那么回事但取出来的行和那个最大值根本不在同一条记录上。这就是group by配max()最容易埋雷的地方。它跟语法错误、类型转换失败不一样不会给你任何提示数据库会安安静静地返回一份部分正确的结果。新手看不出问题老手如果没盯着数据看也容易漏过去。这篇文章就是把这个坑从头到尾拆开讲它为什么会发生、什么条件下会暴露、几种正确的替代写法各自适合什么场景、索引和执行计划层面又有哪些门道。不管你是刚学 SQL 没多久还是写了几年业务查询只要涉及每个分组取最大/最新一条这类需求都值得花点时间看完。先把结论摆在前面SELECT列表里出现非聚合列、同时又在GROUP BY里分组这是所有问题的源头。数据库在语义上不知道该选哪一行给你MySQL 的做法是随便挑一行而随便的定义并不稳定。1.1 先还原现场一段典型的成绩统计查询假设有一张成绩表结构大概是这样CREATE TABLE score ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, class VARCHAR(20) NOT NULL COMMENT 班级, name VARCHAR(32) NOT NULL COMMENT 学生姓名, score INT NOT NULL COMMENT 分数, PRIMARY KEY (id), KEY idx_class_score (class, score) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;需求很朴素每个班里分数最高的那位同学是谁。很多人第一反应就是这么写SELECT class, name, MAX(score) AS max_score FROM score GROUP BY class;如果你的 MySQL 开启了ONLY_FULL_GROUP_BY5.7.5 之后的默认状态这条语句会直接被拒绝报错信息大概是这样ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column demo.score.name which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by但如果你为了先把需求跑通顺手把ONLY_FULL_GROUP_BY从sql_mode里摘掉了语句立刻能跑结果也会返回比如classnamemax_score一班张三98二班李四95看起来完全符合预期。问题在于这个name跟这个98之间没有任何绑定关系。1.2 非聚合列到底取自哪一行MySQL 官方文档对这类查询的表述是服务器可以自由地从每个分组中选择任意一个值除非这些值本身相同否则选出来的值是不确定的nondeterministic。这句话翻译成人话就是——name列返回的是该分组里被读到的某一行的名字而不是取得MAX(score)那一行的名字。我做一个具体的表格来对比一下假设一班的数据是这样idclassnamescore1一班张三722一班李四983一班王五65上面那条 SQL 在一班这个分组里max_score一定是 98这是确定的。但name可能是张三、李四、王五中的任何一个——实际取到谁取决于存储引擎按什么顺序把行喂给聚合算子通常是扫描到的第一行也就是id1的张三。于是你会得到张三 98 分这样一个彻底错误、但语法完全合法的结果。这就是最要命的地方报错是好事静默出错才是灾难。报表里出现张三 98 分而且张三真实分数只有 72这种错误在人工抽查时极难发现因为你得把原始明细翻出来逐条比对。1.3 为什么这种错误能瞒过测试环境我见过好几次同一个模式开发在本地库写完 SQL跑出来结果正确提交上去到了生产或者口径核对环节才发现数据对不上。原因通常有两个。第一个原因是数据分布。测试库的构造数据往往是按id顺序插入的而最高分刚好也落在第一条或者最后一条上于是随便挑一行恰好就挑对了。生产环境数据是乱序写入、批量导入、甚至做过归档搬移的行的物理顺序完全不同取值自然就飘了。第二个原因是执行计划变化。同一张表数据量从 1000 行涨到 5000 万行之后优化器可能从全表扫描切换到索引扫描甚至先走覆盖索引再回表。扫描路径一变被读到的第一行就换了人非聚合列的取值跟着变。这种漂移不是代码改出来的是数据量和统计信息改出来的所以最难查。提示判断一条GROUP BY查询是否安全有个很快的自检方法——把SELECT列表里的每一列都过一遍问自己这一列是不是要么在GROUP BY里要么被聚合函数包着要么能被分组列唯一确定。三者都不满足这条 SQL 就有隐患。2. ONLY_FULL_GROUP_BY 不是来找麻烦的它是来救你的每次看到有人为了跑通查询而全局关掉ONLY_FULL_GROUP_BY我都会劝一句这个开关关掉的不是限制是把数据库对你代码的保护给拆了。它存在的唯一目的就是在编译期把 1.2 节里那种必然出错的写法直接拦下来而不是放任它跑出一个看起来正确的错误答案。2.1 报错信息逐字拆解回到那条报错Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column demo.score.name which is not functionally dependent on columns in GROUP BY clause拆成四段看每一段都在告诉你具体哪里有问题Expression #2 of SELECT listSELECT列表里第 2 个表达式出了状况从 1 开始计数对应的是name那一列。这个编号在写了几十列的宽查询里非常有用能直接定位。is not in GROUP BY clause它没出现在GROUP BY里。contains nonaggregated column demo.score.name它是一个没被聚合函数包裹的裸列库名表名列名都给你标出来了。which is not functionally dependent on columns in GROUP BY clause重点在这半句它说的不是绝对不能出现而是不能被分组列唯一确定。换句话说MySQL 拦的不是非聚合列而是无法从分组列推导出来的非聚合列。2.2 关掉它等于把静默出错合法化关掉的方式很多我列一下常见几种顺便说说各自的适用边界-- 1. 查看当前会话生效的 sql_mode SELECT SESSION.sql_mode; SELECT GLOBAL.sql_mode; -- 2. 只对当前会话生效推荐用这种做临时验证 SET SESSION sql_mode STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION;如果要改全局或者配置文件写法和风险就不一样了# my.cnf / my.ini [mysqld] sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION# 容器环境下通过启动参数传入注意整串要写完整不要只写一半导致其他模式被清空 docker run -d --name mysql8 \ -e MYSQL_ROOT_PASSWORDyour_password \ mysql:8.0 \ --sql-modeSTRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION我的建议是生产库永远不要关。理由很简单一旦关掉1.3 节说的那种漂移就从查询直接失败升级成查询静默返回错误数据后者的排查成本高出一个量级。真要在本地临时验证某种写法能不能跑用SET SESSION就够了退出连接自动失效不会污染全局配置。顺带说一个易踩的细节SET GLOBAL sql_mode只影响之后新建的连接已经存在的连接还是用老配置。改完记得用SHOW PROCESSLIST看一眼有没有长连接没断开或者干脆重启。这一点在新老连接混杂的中间件池化场景里特别容易翻车。2.3 函数依赖GROUP BY 主键时为什么不报错有个现象很多人困惑为什么下面这条语句在ONLY_FULL_GROUP_BY下能正常跑-- 假设 order_detail 的主键是 id SELECT id, order_no, amount, MAX(batch_no) FROM order_detail GROUP BY id;原因就在函数依赖四个字。id是这张表的主键主键唯一确定一行所以当你按id分组时每一组里必然只有一行那么order_no、amount的取值没有任何歧义——它一定是那一行自己的值。MySQL 5.7.5 之后引入了对函数依赖的检测能力能够识别出这类情况并放行。同理如果GROUP BY的是一个唯一索引列或者唯一索引的全部列MySQL 也能推导出函数依赖。但注意普通索引不行因为普通索引允许重复值一组里可能有几百行非聚合列就又回到随便挑一行的状态了。这里有个隐形的坑业务上唯一不等于数据库上唯一。比如你觉得order_no肯定不重复但表上没建唯一索引只有普通索引MySQL 就不认。这时候你只能要么补上唯一约束要先确认数据真的没重复要么老老实实把列写进GROUP BY。2.4 ANY_VALUE() 的正确使用姿势与滥用风险MySQL 5.7 引入了ANY_VALUE()函数专门用来告诉数据库我知道这里取值不确定我接受。它的作用是压住ONLY_FULL_GROUP_BY的报错检查SELECT class, ANY_VALUE(name) AS any_name, MAX(score) AS max_score FROM score GROUP BY class;我见过不少人把它当成万能钥匙哪儿报错往哪儿套。这个习惯很危险。ANY_VALUE()只是把编译期的检查挪到了运行期取值不确定性一点没减少只是不再报错而已。它真正适合的场景是那些你确实不关心取值、只关心分组聚合结果的统计查询。比如每个品类下的商品数量、价格中位数随便带一个商品名做展示这种情况下带哪个名字都无所谓用ANY_VALUE()是合理的。但如果是每个班最高分的学生是谁这种明确要求行对齐的需求用它就是在给自己埋雷。注意ANY_VALUE()的语义是任意一个值不是第一个值也不是最小值。它不做任何排序保证。有人以为它等价于MIN()这是彻底的误解。3. 就算写法合规MAX() 自己的边界也得摸清把group by的写法改对了不代表max()就万事大吉。这个聚合函数本身还有几个容易被忽略的行为特性我在实际项目里都踩过。3.1 三条和 NULL 有关的规则第一条MAX()会忽略 NULL 值。如果一组的score是(NULL, 60, 80)结果是 80NULL 不参与比较。第二条如果一组里全都是 NULLMAX()返回 NULL。这一条本身没问题但它会在后续关联时引发麻烦——NULL 参与等值比较的结果是未知不是真。也就是说WHERE t.ct g.mct在g.mct IS NULL时永远匹配不上整组数据会在 JOIN 里凭空消失。这个坑我在 6.1 节会展开讲一个真实案例。第三条GROUP BY的列本身如果是 NULL所有 NULL 会被归到同一个分组里。这一点和DISTINCT的行为一致跟很多人NULL 各算一组的直觉不一样。-- 验证 MAX 忽略 NULL 的具体表现 SELECT MAX(score) FROM score WHERE class 三班; -- 若该班全为 NULL返回 NULL SELECT COUNT(*) AS total, COUNT(score) AS not_null_cnt FROM score GROUP BY class; -- total 与 not_null_cnt 的差值就是该组 NULL 的个数3.2 字符串、日期与隐式转换下的最大值可能不是你以为的那个这是另一个高频误区。MAX()的比较规则完全取决于列的数据类型和排序规则不是看起来像数字就按数字比。如果score被设计成了VARCHAR(10)很多从 Excel 导入的表就是这样那么MAX(100)和MAX(99)比的是字符串逐字符从左到右比第一个字符1 9所以结果是99而不是100。这个结果在数值语义下是错的但在字符串语义下完全正确。同样的道理适用于日期用字符串存储的情况。2024-9-5和2024-10-01放在一起比前者的第 6 位是9后者的第 6 位是1字符串比较会认为2024-9-5 2024-10-01。所以日期用字符串存的时候一定保证零填充格式统一成YYYY-MM-DD否则MAX()出来的最新时间是错的。另外还有排序规则collation的影响。utf8mb4_general_ci和utf8mb4_bin下的字符串比较结果可能不同涉及大小写、重音字符时尤其明显。我建议做这类聚合前先确认一下列的类型和三要素SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db AND TABLE_NAME score;3.3 并列最大值一组里出现两条相同的 MAX还有个特别隐蔽的情况一个分组里有两行或多行的聚合值完全相等。比如一班有两位同学都是 98 分。这时候无论你用哪种先取 MAX 再关联回去的写法只要关联条件里没有能区分这两行的列结果都会膨胀成两行甚至更多。很多人拿到结果后一看每班只应该有一个最高分怎么一班出来两个人第一反应是怀疑 JOIN 写错了实际上是数据本身存在并列。处理并列有两种思路取决于业务语义业务语义处理方式并列全部要展示接受多行结果关联条件保持只有分组键 聚合值只要其中一条在关联条件或排序里追加次级排序键如id最大者优先需要稳定可复现必须指定完整的排序规则不能依赖默认行为需要只要一条时用窗口函数是最干净的SELECT * FROM ( SELECT s.*, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC, id DESC) AS rn FROM score s ) t WHERE t.rn 1;ORDER BY score DESC, id DESC里的id DESC就是那个次级排序键它保证了即使分数并列结果也是确定且可复现的。这个细节看着小但在做对账和审计类需求时是硬要求——同一份数据跑两次结果必须一样。4. 取每组最新/最大那条完整记录的几种正规解法对比理解了坑在哪接下来就是怎么改。我把常用的几种写法都摆出来说说各自的适用场景和隐藏问题。4.1 子查询 JOIN 法最通用的写法先算出每组的聚合值再关联回原表拿完整行SELECT s.class, s.name, s.score FROM score s INNER JOIN ( SELECT class, MAX(score) AS max_score FROM score GROUP BY class ) g ON s.class g.class AND s.score g.max_score;这个写法有两个必须留意的点。第一关联条件必须同时包含分组键和聚合值。我见过有人写成ON s.score g.max_score漏掉了s.class g.class结果一班 98 分的人会跟二班的最高分 98 也匹配上跨组串数据。这种错误在小数据量下不容易发现因为分数很少正好撞上。第二MySQL 5.7 和 8.0 对派生表derived table的处理不一样。5.7 默认会把子查询物化成临时表8.0 的优化器可能会做派生表合并derived merge把子查询展开到外层。合并之后执行计划可能完全不同原本跑得很快的查询换版本后变慢。如果遇到这种情况可以用EXPLAIN确认一下是不是走了合并必要时用优化器开关或者NO_MERGE提示干预。4.2 窗口函数法MySQL 8.0 之后取每组 Top-N 首选窗口函数。它的优势不只是写法短更重要的是语义明确、结果确定。-- 取每个班最高分的那一行并列时取 id 最大的 SELECT class, name, score FROM ( SELECT class, name, score, ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC, id DESC) AS rn FROM score ) t WHERE t.rn 1;如果并列的都要把ROW_NUMBER()换成RANK()或者DENSE_RANK()即可这也是窗口函数比 JOIN 法更灵活的地方——ROW_NUMBER严格编号、RANK跳号、DENSE_RANK不跳号三种语义按业务挑。代价是性能。窗口函数需要扫描分区内的所有行并做排序在数据量大、分组数少的情况下排序开销可能比索引扫描 子查询大不少。我的经验是分组数多、每组行数少时用窗口函数很舒服分组数少、每组几百万行时要慎重先 EXPLAIN 看排序开销。4.3 反连接与 EXISTS 法反连接的思路是找出没有比它更大的行的那一行SELECT s.* FROM score s LEFT JOIN score s2 ON s2.class s.class AND ( s2.score s.score OR (s2.score s.score AND s2.id s.id) ) WHERE s2.id IS NULL;WHERE s2.id IS NULL意味着在s2里找不到任何一个比s更大的行那s就是组内最大的。这里的OR (s2.score s.score AND s2.id s.id)就是在处理并列用id做次级比较。这种写法的优点是只扫原表不需要物化子查询缺点是在(class, score)上必须有合适的索引否则每一行都要在组内做一次嵌套循环复杂度接近 O(n²)。千万级以上的表如果索引没建好这种查询能把数据库拖垮。EXISTS版本语义一样只是写法不同SELECT s.* FROM score s WHERE NOT EXISTS ( SELECT 1 FROM score s2 WHERE s2.class s.class AND (s2.score s.score OR (s2.score s.score AND s2.id s.id)) );我个人更倾向于用LEFT JOIN ... IS NULL这一版因为优化器对它的处理更稳定而且EXPLAIN出来的执行计划更好读。4.4 用户变量法为什么现在别再用了在 8.0 之前网上流传很广的一种写法是用用户变量逐行比较-- 8.0 之前的写法现在不推荐 SET prev_class : NULL; SET rank : 0; SELECT class, name, score FROM ( SELECT class, name, score, rank : IF(prev_class class, rank 1, 1) AS rn, prev_class : class FROM score ORDER BY class, score DESC ) t WHERE t.rn 1;这种写法的问题非常多依赖ORDER BY和变量赋值在同一层的求值顺序而 SQL 标准并不保证这个顺序优化器调整执行计划就可能让结果错乱。MySQL 8.0.13 之后用户变量在查询中的赋值已经被标记为弃用未来版本可能直接移除。可读性极差别人接手时几乎看不懂意图。完全无法利用索引做优化本质上是强制全表扫描加逐行处理。我唯一能想到的使用场景是维护一个老版本 MySQL 5.6 且无法升级的系统。除此之外能用窗口函数就用窗口函数能用 JOIN 就用 JOIN。4.5 四种写法横向对比写法版本要求并列最大值行为索引友好度主要风险子查询 JOIN全版本并列全部返回高可走覆盖索引关联条件漏写分组键会串组窗口函数8.0可选三种排名语义中需要排序分组少、组内行多时排序开销大反连接 LEFT JOIN全版本需手工加次级比较高依赖联合索引索引缺失时退化成嵌套循环用户变量全版本已弃用依赖排序不稳定低基本全扫执行计划变化即结果错误选型时我的判断顺序是先看版本有 8.0 优先窗口函数分组数少、每组行数极大且索引齐全用反连接需要跨版本兼容、逻辑简单的用子查询 JOIN。5. EXPLAIN 里的三条路径松散索引扫描、紧凑索引扫描与临时表写法改对了只是第一步同一个查询在索引条件不同时性能可能差几百倍。这一节讲讲MAX()配GROUP BY时MySQL 到底有几种执行方式。5.1 三种执行路径分别长什么样执行路径EXPLAIN Extra 关键字段触发条件性能量级相对松散索引扫描Using index for group-by单表、GROUP BY列是某索引最左前缀、聚合只有MIN/MAX最快按分组数跳读索引紧凑索引扫描Using index无 temporaryGROUP BY列能形成索引前缀但存在SUM/COUNT/AVG等其他聚合中等需顺序扫索引临时表Using temporary; Using filesort分组列没有可用索引前缀或存在范围条件破坏前缀最慢可能落盘松散索引扫描是最理想的状态。它的原理是既然索引里(class, score)是按 class 有序、组内按 score 有序的那要拿每个 class 的 MAX(score)只需要跳到每个 class 的第一个位置取该组最后一条的 score 就行完全不用扫描组内所有行。分组数只有几十个的话实际读取的索引条目也是几十条量级。触发它需要满足几个条件缺一个都不行查询只涉及单张表。GROUP BY使用的列构成某个索引的最左前缀且SELECT里没有其他未在GROUP BY中的列参与聚合。聚合函数只用到MIN()和MAX()。WHERE条件里对分组列使用等值条件可以使用范围条件通常会破坏松散扫描退化成紧凑扫描。WHERE class IN (一班,二班)这种是可以走松散扫描的WHERE class 一班这种范围条件一般就不行了。5.2 用 EXPLAIN 判断你的查询落在哪条路径养成一个习惯写完带聚合的查询先EXPLAIN一遍再交付。EXPLAIN SELECT class, MAX(score) FROM score GROUP BY class\G重点看三个字段type理想情况是range或index出现ALL就是全表扫描。key实际用了哪个索引。Extra这里的信息量最大Using index for group-by/Using temporary/Using filesort都在这一栏。如果出现Using temporary说明优化器没法利用索引有序性只能先把所有行按分组键塞进临时表再聚合。数据量大时这个临时表会从内存溢出到磁盘性能断崖式下跌。还有一种情况容易被忽略EXPLAIN显示走了索引没有临时表但实际执行还是很慢。这时候用EXPLAIN ANALYZE8.0.18 之后支持能看到真实的行数和耗时EXPLAIN ANALYZE SELECT class, MAX(score) FROM score GROUP BY class;它会输出实际的actual rows和actual time跟预估的行数一对比就知道统计信息是不是过期了。统计信息过期时优化器可能选错索引跑一次ANALYZE TABLE score;往往能立竿见影。5.3 联合索引的列顺序怎么定针对每个分组取最大这类需求联合索引的列顺序基本是固定的分组列在前排序列在后。-- 分组键是 class聚合/排序列是 score ALTER TABLE score ADD INDEX idx_class_score (class, score);为什么不能反过来因为(score, class)这个索引是按 score 全局有序的同一个 class 的行散落在索引各处根本没法按 class 跳读。列顺序错了索引基本等于白建。如果需求是每个用户取最新一条订单排序键是时间ALTER TABLE orders ADD INDEX idx_user_ct (user_id, created_at);更进一步如果查询里还要带出几个字段做展示可以考虑把它们加到索引末尾做成覆盖索引避免回表-- 覆盖索引查询只需要 user_id、created_at、amount 时不用回表 ALTER TABLE orders ADD INDEX idx_user_ct_amt (user_id, created_at, amount);代价是索引变大、写入变慢。我的经验是覆盖索引只给那些高频且字段少的查询加别为了省几次回表把索引堆成十几个字段那样写放大的成本会远超读的收益。提示GROUP BY在 MySQL 5.7 及以前会隐式排序相当于自动加了ORDER BY 分组列当时流行的GROUP BY xxx ORDER BY NULL就是为了干掉这个多余排序。8.0 移除了隐式排序这句ORDER BY NULL已经没有实际作用但也不会报错。从 5.7 升级上来时别指望靠它继续保证输出有序。6. 真实项目里踩过的三个具体场景理论讲完了说几个我亲手踩过的坑都是那种文档里不会写、但真出事很疼的类型。6.1 取每个设备最后一条上报全组 NULL 导致整组消失项目背景是物联网设备上报每台设备不定时上报状态有一张device_report表记录device_id、report_time、status。需求是查每台设备最后一次上报的状态。我当时的写法SELECT d.device_id, d.report_time, d.status FROM device_report d INNER JOIN ( SELECT device_id, MAX(report_time) AS last_time FROM device_report GROUP BY device_id ) g ON d.device_id g.device_id AND d.report_time g.last_time;上线后发现设备总数是 12000 台查出来的结果只有 11800 多台少了将近 200 台。逐个排查才找到原因report_time这一列允许为 NULL历史遗留设计有 200 台设备的所有上报记录的report_time都是 NULL。MAX(NULL)返回 NULL所以子查询里这 200 台的last_time是 NULL。而外层d.report_time g.last_time在遇到 NULL 时比较结果是未知永远不成立这 200 台设备就被整体过滤掉了。修复方式是不依赖聚合值做等值关联改成先按设备分组排序、取序号为 1 的那一行SELECT device_id, report_time, status FROM ( SELECT device_id, report_time, status, ROW_NUMBER() OVER ( PARTITION BY device_id ORDER BY report_time IS NULL, report_time DESC, id DESC ) AS rn FROM device_report ) t WHERE t.rn 1;ORDER BY report_time IS NULL这个技巧值得记一下report_time IS NULL的结果是 0 或 1把 NULL 行排到最后非 NULL 的按时间倒序排在前面。这样全 NULL 的设备也能正常输出一行只是时间显示为空。如果必须留在 5.7可以用关联子查询配合IFNULL兜底但那会引入额外的复杂度能升版本尽量升。6.2 分组统计再关联明细行数翻倍与 COUNT 对不上另一次数仓对账业务方反馈页面显示订单数 500导出明细 800 行。排查发现 SQL 是这么写的-- 错误写法 SELECT g.user_id, g.order_cnt, o.order_no, o.amount FROM ( SELECT user_id, COUNT(*) AS order_cnt FROM orders GROUP BY user_id ) g INNER JOIN orders o ON o.user_id g.user_id WHERE g.order_cnt 5;问题很明显g里每个用户只有一行但o里每个用户有多行关联之后每个用户的行数等于他自己的订单数跟order_cnt混淆了。这不是MAX()的坑但它跟group by配聚合函数的用法高度同源——把统计结果和明细数据混在一条 SQL 里输出很容易出现粒度错位。判断粒度是否一致有个简单办法列一个分组键的清单然后看每个表在这个清单上的唯一性。上面的例子里g在user_id上唯一o在user_id上不唯一所以 JOIN 后会膨胀。修复思路有两种。如果是统计展示就别 JOIN 明细SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS total_amount FROM orders GROUP BY user_id HAVING COUNT(*) 5;如果确实要统计 明细一起出明确粒度后再关联或者干脆用窗口函数在明细行上直接算SELECT user_id, order_no, amount, COUNT(*) OVER (PARTITION BY user_id) AS order_cnt FROM orders;顺便说一句HAVING和WHERE的区别这也是高频错误WHERE在分组前过滤HAVING在分组后过滤聚合结果。写WHERE COUNT(*) 5会直接报ERROR 1111: Invalid use of group function因为聚合函数不能出现在WHERE里。6.3 5.7 升 8.0 后结果变了隐式排序消失带来的取值漂移这个坑最有意思也最值得警惕。有个报表查询长期运行在 5.7 上写法就是 1.1 节里那条有问题的 SQL——SELECT class, name, MAX(score) ... GROUP BY class内网报表sql_mode被整体放开了。两年多来结果一直稳定因为 5.7 的GROUP BY会隐式排序加上数据是按id顺序插入的name每次都能取到同一行。升级到 8.0 之后隐式排序被移除同样的数据、同样的 SQL取到的name变了报表数字跟着变了。业务方一看历史趋势出现断点第一反应是数据出问题了实际上数据完好无损只是那个看起来稳定的取值来源消失了。这件事给我的教训是任何依赖默认行为的结果都不算稳定。隐式排序、默认排序规则、未指定的次级排序键、优化器的执行计划选择这些都是当前恰好如此而不是语义上必然如此。判断标准很简单——如果换一个数据库版本、换一台机器、换一种数据导入顺序结果会不会变会变就是在赌。升级版本前我现在的固定动作里一定包含一条把所有GROUP BY查询捞出来逐条检查SELECT列表里有没有非聚合裸列。捞取方式可以翻代码也可以在慢查询日志和performance_schema里筛-- 从 performance_schema 找出最近执行过的、带 GROUP BY 的语句摘要 SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1000000000 AS avg_ms FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE %GROUP BY% ORDER BY COUNT_STAR DESC LIMIT 50;配合灰度环境跑一遍全量回归基本能把这类问题提前暴露出来。7. 排查这类问题的固定动作最后把我处理分组取最大/最新这类问题时的动作流程列一下形成肌肉记忆之后能省很多时间。第一步永远是验证粒度。列出GROUP BY后面的所有列问一句这组列能唯一确定我想要的粒度吗。如果答案是否定的那后面的一切都在错误的前提上。第二步是检查SELECT列表。每一列过一遍在GROUP BY里、被聚合函数包着、还是靠函数依赖被唯一确定。三种都不满足立刻改写。第三步是确认并列情况。用一条简单的 SQL 看看真的有没有并列-- 找出存在并列最高值的分组 SELECT class, score, COUNT(*) AS cnt FROM score s WHERE s.score (SELECT MAX(score) FROM score WHERE class s.class) GROUP BY class, score HAVING cnt 1;第四步是固定排序规则。只要涉及取一条就必须明确指定完整的ORDER BY包括次级排序键让结果在任何执行计划下都可复现。第五步是看执行计划。EXPLAIN一遍确认没有意外的Using temporary、Using filesort确认索引列顺序对得上分组列和排序列。数据量大的表还要跑一次EXPLAIN ANALYZE对比预估行数和实际行数。第六步是跨版本验证。如果系统有升级计划把关键查询在目标版本上跑一遍对比结果比看文档猜行为要靠谱得多。补一个我自己常用的小技巧写这类查询时先在表上造几条故意制造歧义的测试数据——同一分组内两行分数并列、一行时间字段为 NULL、分组列存在 NULL 值。这三条数据一进去写法有问题的 SQL 立刻就会露出马脚比在生产上等着出事强太多了。数据构造完成之后整个验证过程通常不超过十分钟但能省下的对账时间可能是好几天。关于MAX()和GROUP BY还有一个细节值得一提在分组数极大比如几十万分组的用户行为表的场景下无论哪种写法性能瓶颈往往不在写法本身而在分组键的选择性和索引的覆盖程度。这时候把GROUP BY换成WHERE EXISTS的过滤式写法或者先做时间范围裁剪再分组效果通常比纠结聚合函数的选择更明显。多测几版执行计划比看多少篇教程都有用。