Group By很慢，如何定位？如何优化？

前言

有些小伙伴在工作中可能遇到过这样的场景：原本运行良好的Group By查询，随着数据量的增长，执行时间从几秒变成了几分钟甚至几小时。
页面加载缓慢，用户抱怨连连，DBA着急上火。
这种性能下降往往是在不知不觉中发生的，背后一定有着深层次的原因。
今天这篇文章跟大家一起聊聊group by变慢后，如何定位和优化，希望对你会有所帮助。
一、为什么Group By会变慢？

在深入解决方案之前，我们需要先理解Group By操作的本质。
Group By的执行过程通常包含以下几个步骤：

从流程图可以看出，Group By性能问题主要出现在两个环节：数据读取和分组操作。
数据读取阶段可能因为没有索引而全表扫描，分组操作阶段可能因为数据量过大而使用磁盘临时表。
这两个问题都会导致group by性能变慢。
二、如何定位Group By性能问题？

1. 使用EXPLAIN分析执行计划

MySQL的EXPLAIN命令是我们分析查询性能的首选工具：

1. EXPLAIN
2. SELECT department, COUNT(*) as emp_count
3. FROM employees
4. WHERE hire_date > '2020-01-01'
5. GROUP BY department;

复制代码

执行结果可能包含以下关键信息：
列名说明可能的值和含义type访问类型index(索引扫描), ALL(全表扫描)key使用的索引实际使用的索引名称rows预估扫描行数数值越小越好Extra额外信息Using temporary(使用临时表), Using filesort(使用文件排序)2. 性能监控工具

除了EXPLAIN，我们还可以使用MySQL的性能监控工具：

1. -- 开启性能分析
2. SET PROFILING = 1;
4. -- 执行查询
5. SELECT department, COUNT(*) as emp_count
6. FROM employees
7. GROUP BY department;
9. -- 查看性能详情
10. SHOW PROFILE FOR QUERY 1;
12. -- 查看所有查询的性能信息
13. SHOW PROFILES;

复制代码

三、常见原因及解决方案

1. 缺少合适的索引

问题分析：
有些小伙伴在设计表结构时，可能没有为Group By字段和Where条件字段创建合适的索引，导致MySQL不得不进行全表扫描。
解决方案：
为Group By字段和Where条件字段创建复合索引：

1. -- 创建适合Group By的索引
2. CREATE INDEX idx_department_hire_date ON employees(department, hire_date);
4. -- 或者创建覆盖索引，避免回表操作
5. CREATE INDEX idx_department_hire_date_covering ON employees(department, hire_date, salary);

复制代码

索引设计原则：

• 将Where条件中的字段放在索引左侧
  
• 然后是Group By字段
  
• 最后是Select中需要返回的字段（覆盖索引）
  

2. 使用临时表和文件排序

问题分析：
当Group By的数据量较大时，MySQL可能需要使用临时表来存储中间结果，如果临时表太大而内存放不下，就会使用磁盘临时表，性能急剧下降。

解决方案：
方法一：调整临时表大小

1. -- 查看当前临时表设置
2. SHOW VARIABLES LIKE 'tmp_table_size';
3. SHOW VARIABLES LIKE 'max_heap_table_size';
5. -- 增大临时表内存大小（需重启）
6. SET GLOBAL tmp_table_size = 256 * 1024 * 1024;  -- 256MB
7. SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;  -- 256MB

复制代码

方法二：优化查询语句

1. -- 优化前：查询所有字段
2. SELECT *, COUNT(*)
3. FROM employees
4. GROUP BY department;
6. -- 优化后：只查询需要的字段
7. SELECT department, COUNT(*)
8. FROM employees
9. GROUP BY department;
11. -- 进一步优化：添加限制条件减少处理数据量
12. SELECT department, COUNT(*)
13. FROM employees
14. WHERE hire_date > '2023-01-01'
15. GROUP BY department;

复制代码

3. 数据量过大问题

问题分析：
当单表数据量达到千万级甚至亿级时，即使有索引，Group By操作也可能很慢。
解决方案：
方法一：分阶段聚合

1. // Java代码示例：分阶段聚合大量数据
2. public Map<String, Integer> batchGroupBy(String tableName,
3.                                        String groupColumn,
4.                                        String condition,
5.                                        int batchSize) throws SQLException {
6.    
7.     Map<String, Integer> resultMap = new HashMap<>();
8.     int offset = 0;
9.     boolean hasMore = true;
10.    
11.     try (Connection conn = dataSource.getConnection()) {
12.         while (hasMore) {
13.             String sql = String.format(
14.                 "SELECT %s, COUNT(*) as cnt FROM %s WHERE %s GROUP BY %s LIMIT %d OFFSET %d",
15.                 groupColumn, tableName, condition, groupColumn, batchSize, offset);
16.             
17.             try (Statement stmt = conn.createStatement();
18.                  ResultSet rs = stmt.executeQuery(sql)) {
19.                
20.                 int rowCount = 0;
21.                 while (rs.next()) {
22.                     String key = rs.getString(groupColumn);
23.                     int count = rs.getInt("cnt");
24.                     resultMap.merge(key, count, Integer::sum);
25.                     rowCount++;
26.                 }
27.                
28.                 if (rowCount < batchSize) {
29.                     hasMore = false;
30.                 } else {
31.                     offset += batchSize;
32.                 }
33.             }
34.         }
35.     }
36.    
37.     return resultMap;
38. }

复制代码

方法二：使用异步处理和缓存

1. // 异步Group By处理示例
2. @Service
3. public class AsyncGroupByService {
4.    
5.     @Autowired
6.     private JdbcTemplate jdbcTemplate;
7.    
8.     @Autowired
9.     private CacheManager cacheManager;
10.    
11.     @Async("taskExecutor")
12.     public CompletableFuture<Map<String, Integer>> executeGroupByAsync(String sql, String cacheKey) {
13.         // 检查缓存
14.         Cache cache = cacheManager.getCache("groupByResults");
15.         Cache.ValueWrapper cachedResult = cache.get(cacheKey);
16.         
17.         if (cachedResult != null) {
18.             return CompletableFuture.completedFuture((Map<String, Integer>) cachedResult.get());
19.         }
20.         
21.         // 执行查询
22.         Map<String, Integer> result = jdbcTemplate.query(sql, rs -> {
23.             Map<String, Integer> map = new HashMap<>();
24.             while (rs.next()) {
25.                 map.put(rs.getString(1), rs.getInt(2));
26.             }
27.             return map;
28.         });
29.         
30.         // 设置缓存
31.         cache.put(cacheKey, result);
32.         
33.         return CompletableFuture.completedFuture(result);
34.     }
35. }

复制代码

4. 复杂Group By优化

问题分析：
有些小伙伴可能会写出包含多个字段、复杂条件甚至包含子查询的Group By语句，这些语句往往性能较差。
解决方案：
方法一：使用派生表优化

1. -- 优化前：复杂Group By
2. SELECT department,
3.        AVG(salary) as avg_salary,
4.        COUNT(*) as emp_count
5. FROM employees
6. WHERE hire_date > '2020-01-01'
7. GROUP BY department
8. HAVING avg_salary > 5000;
10. -- 优化后：使用派生表
11. SELECT t.department, t.avg_salary, t.emp_count
12. FROM (
13.     SELECT department,
14.            AVG(salary) as avg_salary,
15.            COUNT(*) as emp_count
16.     FROM employees
17.     WHERE hire_date > '2020-01-01'
18.     GROUP BY department
19. ) t
20. WHERE t.avg_salary > 5000;

复制代码

方法二：使用WITH ROLLUP进行多维度分组

1. -- 多层次分组统计
2. SELECT department, job_title, COUNT(*) as emp_count
3. FROM employees
4. GROUP BY department, job_title WITH ROLLUP;
6. -- 等价于以下三个查询的联合
7. -- 1. GROUP BY department, job_title
8. -- 2. GROUP BY department
9. -- 3. 总计

复制代码

5. 分布式环境下的Group By优化

问题分析：
在分库分表环境下，Group By操作变得更加复杂，需要在多个节点上执行并合并结果。
解决方案：
方法一：使用中间件实现跨库Group By

1. // 分库分表Group By处理示例
2. public class ShardingGroupByExecutor {
3.    
4.     public Map<String, Integer> executeAcrossShards(String logicSql, List<DataSource> shards) {
5.         // 并发执行所有分片
6.         List<CompletableFuture<Map<String, Integer>>> futures = shards.stream()
7.             .map(shard -> CompletableFuture.supplyAsync(() -> executeOnShard(logicSql, shard)))
8.             .collect(Collectors.toList());
9.         
10.         // 合并所有结果
11.         return futures.stream()
12.             .map(CompletableFuture::join)
13.             .flatMap(map -> map.entrySet().stream())
14.             .collect(Collectors.toMap(
15.                 Map.Entry::getKey,
16.                 Map.Entry::getValue,
17.                 Integer::sum
18.             ));
19.     }
20.    
21.     private Map<String, Integer> executeOnShard(String sql, DataSource dataSource) {
22.         try (Connection conn = dataSource.getConnection();
23.              Statement stmt = conn.createStatement();
24.              ResultSet rs = stmt.executeQuery(sql)) {
25.             
26.             Map<String, Integer> result = new HashMap<>();
27.             while (rs.next()) {
28.                 result.put(rs.getString(1), rs.getInt(2));
29.             }
30.             return result;
31.             
32.         } catch (SQLException e) {
33.             throw new RuntimeException("分片查询失败", e);
34.         }
35.     }
36. }

复制代码

方法二：使用Elasticsearch等搜索引擎
对于复杂的聚合查询，可以考虑将数据同步到Elasticsearch中，利用其强大的聚合能力：

1. // Elasticsearch聚合查询示例
2. SearchRequest searchRequest = new SearchRequest("employees");
3. SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
5. // 构建聚合
6. TermsAggregationBuilder aggregation = AggregationBuilders.terms("by_department")
7.     .field("department.keyword")
8.     .subAggregation(AggregationBuilders.avg("avg_salary").field("salary"));
10. sourceBuilder.aggregation(aggregation);
11. searchRequest.source(sourceBuilder);
13. // 执行查询
14. SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT);
16. // 处理结果
17. Terms terms = response.getAggregations().get("by_department");
18. for (Terms.Bucket bucket : terms.getBuckets()) {
19.     String department = bucket.getKeyAsString();
20.     long count = bucket.getDocCount();
21.     Avg avgSalary = bucket.getAggregations().get("avg_salary");
22.     System.out.println(department + ": " + count + ", 平均薪资: " + avgSalary.getValue());
23. }

复制代码

四、实战案例

有些小伙伴在电商系统中可能会遇到订单统计的Group By性能问题，下面是一个真实案例：
原始查询：

1. SELECT DATE(create_time) as order_date,
2.        product_category,
3.        COUNT(*) as order_count,
4.        SUM(amount) as total_amount
5. FROM orders
6. WHERE create_time >= '2023-01-01'
7.   AND status = 'COMPLETED'
8. GROUP BY DATE(create_time), product_category;

复制代码

优化方案：

• 创建合适索引：
  

1. CREATE INDEX idx_orders_stats ON orders(create_time, status, product_category, amount);

复制代码

• 使用预聚合：
  

1. -- 创建预聚合表
2. CREATE TABLE orders_daily_stats (
3.     stat_date DATE NOT NULL,
4.     product_category VARCHAR(50) NOT NULL,
5.     order_count INT NOT NULL,
6.     total_amount DECIMAL(15,2) NOT NULL,
7.     PRIMARY KEY (stat_date, product_category)
8. );
10. -- 使用定时任务每天凌晨更新统计
11. INSERT INTO orders_daily_stats
12. SELECT DATE(create_time), product_category, COUNT(*), SUM(amount)
13. FROM orders
14. WHERE create_time >= CURDATE() - INTERVAL 1 DAY
15.   AND status = 'COMPLETED'
16. GROUP BY DATE(create_time), product_category
17. ON DUPLICATE KEY UPDATE
18.     order_count = VALUES(order_count),
19.     total_amount = VALUES(total_amount);

复制代码

• 查询优化后的结果：
  

1. -- 现在查询预聚合表，性能极大提升
2. SELECT stat_date, product_category, order_count, total_amount
3. FROM orders_daily_stats
4. WHERE stat_date >= '2023-01-01';

复制代码

总结

通过以上分析和解决方案，我们可以总结出Group By性能优化的关键点：

• 索引优化：为Group By字段和Where条件创建合适的复合索引
  
• 查询简化：避免SELECT *，只获取需要的字段
  
• 临时表优化：调整tmp_table_size，避免磁盘临时表
  
• 数据分片：对于大数据集，采用分批次处理策略
  
• 预聚合：对于常用统计，使用预聚合表提前计算
  
• 架构升级：考虑使用读写分离、分布式数据库或搜索引擎
  

不同场景下的优化策略选择：
场景推荐策略优点缺点中小数据量索引优化+查询优化简单有效需要设计合适的索引大数据量预聚合+分批次处理性能提升明显需要额外存储空间高并发查询缓存+异步处理降低数据库压力数据可能不是实时复杂聚合使用Elasticsearch聚合能力强需要数据同步Group By性能优化是一个需要综合考虑数据库设计、查询编写和系统架构的系统工程。
每个业务场景都有其特殊性，需要根据实际情况选择合适的优化方案。
最后说一句(求关注，别白嫖我)

如果这篇文章对您有所帮助，或者有所启发的话，帮忙关注一下我的同名公众号：苏三说技术，您的支持是我坚持写作最大的动力。
求一键三连：点赞、转发、在看。
关注公众号：【苏三说技术】，在公众号中回复：进大厂，可以免费获取我最近整理的10万字的面试宝典，好多小伙伴靠这个宝典拿到了多家大厂的offer。
本文收录于我的技术网站：http://www.susan.net.cn

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

热心回复！