mysql中的group by用法详解

MySQL中的GROUP BY是数据聚合分析的核心功能，主要用于将结果集按指定列分组，并结合聚合函数进行统计盘算。以下从基本语法到高级用法进行详细剖析：

---

一、基本语法与核心功能

1. SELECT 分组列, 聚合函数(计算列)
2. FROM 表名
3. [WHERE 条件]
4. GROUP BY 分组列
5. [HAVING 分组过滤条件]
6. [ORDER BY 排序列];

复制代码

核心功能：

• 数据分组：按一列或多列的值将数据划分为逻辑组。
  
• 聚合盘算：对每个分组应用聚合函数（如COUNT、SUM、AVG、MAX、MIN）进行统计。
  
• 结果过滤：通过HAVING对分组后的结果进行筛选（区别于WHERE的分组前过滤）。
  

---

二、基础用法示例

1. 单列分组统计

统计每个部分的员工数目平静均工资：

1. SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
2. FROM employees
3. GROUP BY department;  --

复制代码

2. 多列组合分组

按部分和职位统计员工数目：

1. SELECT department, job_title, COUNT(*)
2. FROM employees
3. GROUP BY department, job_title;  --

复制代码

3. 与WHERE结合使用

仅统计薪资高出2000元的员工部分平均工资：

1. SELECT department, AVG(salary)
2. FROM employees
3. WHERE salary > 2000
4. GROUP BY department;  --

复制代码

---

三、高级特性与扩展

1. HAVING子句过滤分组

筛选员工数目高出5人的部分：

1. SELECT department, COUNT(*) AS emp_count
2. FROM employees
3. GROUP BY department
4. HAVING emp_count > 5;  --

复制代码

2. WITH ROLLUP生成汇总行

生成部分及职位的薪资小计和总计：

1. SELECT department, job_title, SUM(salary)
2. FROM employees
3. GROUP BY department, job_title WITH ROLLUP;  --

复制代码

3. GROUP_CONCAT归并列值

统计每个用户购买的所有产品（逗号分隔）：

1. SELECT user_id, GROUP_CONCAT(product_name SEPARATOR ', ')
2. FROM orders
3. GROUP BY user_id;  --

复制代码

4. 按表达式/函数分组

按年份统计订单数目：

1. SELECT YEAR(order_date) AS year, COUNT(*)
2. FROM orders
3. GROUP BY YEAR(order_date);  --

复制代码

---

四、留意事项与常见错误

• ONLY_FULL_GROUP_BY模式
  MySQL 8.0+默认启用该模式，要求SELECT中的非聚合列必须出现在GROUP BY中，否则报错。
  
  1. -- 错误示例（salary未聚合且未分组）
  2. SELECT department, salary FROM employees GROUP BY department;
  4. -- 修正方法：添加聚合函数或分组字段
  5. SELECT department, MAX(salary) FROM employees GROUP BY department;

  复制代码
• WHERE与HAVING的区别
  
  
  
  • WHERE在分组前过滤行数据，不可使用聚合函数。
    
  • HAVING在分组后过滤组数据，必须与聚合条件结合。
    
  
  
• 性能优化建议
  
  
  
  • 在分组列上创建索引（如ALTER TABLE employees ADD INDEX(department)）。
    
  • 避免对大表直接分组，可先通过临时表或子查询缩小数据范围。
    
  
  

---

五、经典案例场景

1. 按时间维度聚合

统计每月的销售总额：

1. SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(amount)
2. FROM sales
3. GROUP BY year, month;  --

复制代码

2. 多层级统计

分析每个客户每年的订单总金额及平均金额：

1. SELECT customer_id, YEAR(order_date),
2.        SUM(total_amount), AVG(total_amount)
3. FROM orders
4. GROUP BY customer_id, YEAR(order_date);  --

复制代码

3. 数据去重

查找重复邮箱的用户：

1. SELECT email, COUNT(*)
2. FROM users
3. GROUP BY email
4. HAVING COUNT(*) > 1;  --

复制代码

---

六、聚合效率优化

在MySQL中优化GROUP BY聚合效率需要从索引计划、查询逻辑、执行引擎特性等多维度入手。以下基于最新优化实践和数据库引擎特性，总结9大核心优化策略：

---

1、索引优化策略

• 复合索引精准匹配分组列
  • 创建与GROUP BY顺序完全匹配的复合索引（如GROUP BY a,b则创建(a,b)索引），可触发疏松索引扫描，减少90%以上的磁盘I/O。
  • 典型案例：当对(department, job_title)分组时，复合索引idx_dept_job可使查询跳过全表扫描，直接通过索引完成分组。
  
• 覆盖索引避免回表
  • 确保SELECT列与聚合函数涉及的列均包罗在索引中。例如索引(category, sales)，查询SELECT category, SUM(sales)时可直接通过索引完成盘算，无需访问数据行。
  
• 使用函数索引应对复杂分组
  • 对含表达式的分组（如YEAR(date_col)），创建假造列或函数索引（MySQL 8.0+支持）。例如：
  
  1. ALTER TABLE orders ADD COLUMN year_date INT AS (YEAR(order_date)) VIRTUAL;
  2. CREATE INDEX idx_year ON orders(year_date);

  复制代码

---

2、查询计划与执行优化

• 减少分组字段数目与复杂度
  • 每增加一个分组字段，排序复杂度呈指数级增长。优先归并相干字段（如将province和city归并为region字段）。
  • 避免在GROUP BY中使用函数，否则索引失效。需改写为基于原字段分组，如将GROUP BY DATE(created_at)改为GROUP BY created_at_date预盘算列。
  
• 分阶段过滤与聚合
  • 先通过子查询过滤无关数据再分组：
  
  1. SELECT department, AVG(salary)
  2. FROM (SELECT * FROM employees WHERE salary > 5000) AS filtered
  3. GROUP BY department;  -- 比直接HAVING效率提升40%

  复制代码
• 内存排序与临时表优化
  • 调整tmp_table_size和max_heap_table_size参数（建议设置为物理内存的20%），避免临时表落盘。
  • 监控Created_tmp_disk_tables状态变量，若频仍出现磁盘临时表，需优化索引或拆分查询。
  

---

3、高级优化技能

• 分区表加快大数据处置惩罚
  • 按时间或业务维度分区（如按月分区），使GROUP BY仅扫描特定分区。例如对10亿级日记表按event_date分区后，月度统计耗时从分钟级降至秒级。
  
• 物化视图与结果缓存
  • 对高频聚合查询使用物化视图（如通过CREATE TABLE mv AS SELECT...定期刷新），减少实时盘算压力。
  • 应用层缓存重复查询结果（如Redis缓存日汇总数据），低沉数据库负载。
  
• 并行查询（MySQL 8.0+）
  • 启用parallel_query功能，通过多线程处置惩罚复杂分组：
  
  1. SET SESSION optimizer_switch='parallel_query=on';
  2. SELECT region, SUM(revenue) FROM sales GROUP BY region;  -- 利用多核CPU加速

  复制代码

---

4、诊断工具与留意事项

• 执行计划分析
使用EXPLAIN FORMAT=JSON观察using_index（是否用索引）、using_temporary（是否用临时表）、filesort（排序方式）等关键指标。
• 严酷模式规避错误
启用ONLY_FULL_GROUP_BY模式，防止非聚合列误用导致结果不稳定。

---

性能优化对比案例

场景优化前耗时优化手段优化后耗时百万级用户举动分析12.8s创建(user_id,action_time)覆盖索引1.2s十亿级日记日聚合3分钟按日分区+并行查询8秒 通过上述策略组合，可系统性办理GROUP BY性能瓶颈。实际应用中建议结合EXPLAIN分析和A/B测试，选择最适合业务场景的优化方案。
七、扩展知识

• NULL值的处置惩罚：GROUP BY将NULL视为独立分组。
  
• 排序结合：分组后使用ORDER BY对结果排序（如按平均工资降序）。
  
• 动态分组：通过CASE WHEN实现条件分组（如按薪资区间统计）。
  

通过灵活组合这些功能，GROUP BY可满足复杂的数据分析需求。实际应用中需结合索引优化和查询逻辑计划，以提升执行效率。

---

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

继续阅读请点击广告