跳转到主内容
趣航编程网 - 趣学编程，启航技术之路！
📁 网站地图

mysql中的聚合函数与查询优化

顺亿
顺亿
• 👁 0人浏览过
MySQL 8.0+启用ONLY_FULL_GROUP_BY后，SELECT非聚合字段必须出现在GROUP BY中或用聚合函数包裹；COUNT(*)与COUNT(1)等价，COUNT(col)需判空且性能略低；WHERE应过滤行、HAVING过滤分组，错用致性能骤降；JOIN后GROUP BY易因一对多引发笛卡尔积和结果失真。 GROUP BY 后字段必须出现在 SELECT 或 聚合函数 中 MySQL 8.0+ 默认启用
sql_mode=ONLY_FULL_GROUP_BY
，这意味着
SELECT
列表里所有非聚合字段都必须严格出现在
GROUP BY
子句中。否则会报错：
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
。 常见误写：
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;
这里
name
既没参与分组，也没被聚合，MySQL 不知道该取哪一行的
name
—— 它拒绝“猜”。解决办法只有两种： 把
name
加进
GROUP BY
（如果业务逻辑允许，比如
user_id
和
name
是一一对应的） 用聚合函数包裹，例如
MAX(name)
或
ANY_VALUE(name)
（后者是 MySQL 特有，表示接受任意值，但需确认语义安全） COUNT(*)、COUNT(1)、COUNT(col) 性能差异真实存在吗？ 在绝大多数 InnoDB 场景下：
COUNT(*)
和
COUNT(1)
完全等价，优化器会忽略常量表达式，直接走索引或聚簇索引的行计数逻辑；而
COUNT(col)
会跳过
col
为
NULL
的行，必须实际读取该列值，开销略高。 注意点： 如果
col
有
NOT NULL
约束，
COUNT(col)
和
COUNT(*)
在语义和性能上几乎一致 没有合适索引时，三者都会触发全表扫描；但如果有覆盖索引（如对
status
建了索引），
COUNT(status)
可能走索引树，比
COUNT(*)
快 别迷信 “
COUNT(1)
比
COUNT(*)
快” —— 这是过时经验，在现代 MySQL 中不成立 WHERE 和 HAVING 混用导致性能崩溃
WHERE
过滤行，
HAVING
过滤分组结果，二者执行阶段不同。把本该写在
WHERE
的条件挪到
HAVING
里，会让 MySQL 先做全量分组，再筛组，极大增加内存和 CPU 开销。 反例： MySQL（Linux） MySQL 9.6.0是面向Linux平台的2026年创新版本，核心架构迎来重大革新。其将外键约束与级联操作从InnoDB引擎层上移至SQL层，确保所有数据变更均被完整记录至Binlog，彻底解决了CDC（变更数据捕获）与主从复制中的数据不一致难题。此外，该版本引入container_aware启动选项以原生适配容器环境，并对审计日志进行了组件化重构，为追求极致数据一致性与云原生体验的开发者提供了全新选择。 下载
SELECT user_id, COUNT(*) c FROM orders GROUP BY user_id HAVING user_id > 1000;
正确写法是：
SELECT user_id, COUNT(*) c FROM orders WHERE user_id > 1000 GROUP BY user_id;
关键区别：
WHERE user_id > 1000
可利用
user_id
索引快速定位，减少输入到
GROUP BY
的行数
HAVING user_id > 1000
无法下推，必须先按所有
user_id
分组（哪怕最终只留 10 行），临时表可能爆内存 尤其当
orders
表有千万级数据，且
GROUP BY
字段区分度低时，这个错误会让查询从秒级变分钟级 聚合 + JOIN 容易触发笛卡尔积和临时表膨胀 多表
JOIN
后再
GROUP BY
，若关联键不是一对一，极易放大行数。例如用户表 × 订单表 × 订单项表，一个用户多个订单、一个订单多个商品，
COUNT(*)
会统计“订单项”数量而非“订单”数量，结果失真。 典型陷阱：
SELECT u.id, COUNT(*) FROM users u JOIN orders o ON u.id = o.user_id JOIN order_items oi ON o.id = oi.order_id GROUP BY u.id;
想统计每个用户的订单数，却得到每个用户的订单项总数。修复方式取决于语义： 要订单数 → 改用
COUNT(DISTINCT o.id)
要订单项数 → 显式说明意图，但需确认是否真需要跨三层聚合 更稳妥：先子查询聚合订单层，再 JOIN 用户表，避免中间结果膨胀 临时表大小受
tmp_table_size
和
max_heap_table_size
控制，一旦溢出磁盘，性能断崖下跌 —— 这类问题往往在测试环境看不出来，上线后 大数据 量才暴露。

相关文章