8000万条数据下MySQL聚合查询性能优化求助

DevOps 小满

2026-8-4

MySQL查询性能优化建议

针对你8000万条记录的event_log表，group by聚合查询耗时30秒的问题，给出以下优化方案：

调整覆盖索引，消除回表开销
现有复合索引如果是(events, case_id, net_value, total_time_spent)，可以简化为(events, total_time_spent, net_value)。因为case_id是主键，count(case_id)等价于count(*)（主键非空），不需要把case_id放到索引里。这个索引可以覆盖查询所需的所有字段（events用于分组，sum的两个字段直接从索引取），避免InnoDB回表查询主键索引。
创建语句：
```
CREATE INDEX idx_events_agg ON event_log(events, total_time_spent, net_value);
```
优化查询语句，替换count(case_id)为count(*)
由于case_id是主键，必然非空，count(case_id)和count(*)结果完全一致，但MySQL对count(*)的优化更彻底，尤其是在覆盖索引场景下。修改后的查询：
```
SELECT count(*), sum(net_value), sum(total_time_spent), events 
FROM event_log 
GROUP BY events 
ORDER BY count(*) DESC;
```

引入预聚合汇总表
8000万条数据全表聚合的开销本质上无法避免，最有效的方案是提前计算聚合结果并存储。创建一个汇总表：

CREATE TABLE event_log_agg (
    events VARCHAR(200) PRIMARY KEY,
    case_count BIGINT NOT NULL DEFAULT 0,
    total_net_value DECIMAL(18,2) NOT NULL DEFAULT 0, -- 根据net_value实际类型调整
    total_time_spent BIGINT NOT NULL DEFAULT 0,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

然后通过定时任务（比如MySQL事件、Cron）定期更新汇总表，采用INSERT ... ON DUPLICATE KEY UPDATE逻辑：

INSERT INTO event_log_agg (events, case_count, total_net_value, total_time_spent)
SELECT events, count(*), sum(net_value), sum(total_time_spent)
FROM event_log
GROUP BY events
ON DUPLICATE KEY UPDATE
    case_count = VALUES(case_count),
    total_net_value = VALUES(total_net_value),
    total_time_spent = VALUES(total_time_spent);

后续业务查询直接从event_log_agg取数据，响应时间会降到毫秒级。

优化MySQL配置参数
针对AWS RDS r5d.2xlarge实例（64GB内存），调整以下关键参数：
- innodb_buffer_pool_size：设置为45GB左右（占实例内存70%-80%），确保表和索引能完全缓存到内存，避免磁盘IO瓶颈。
- sort_buffer_size：适当调大（比如8MB），减少group by排序时的临时文件生成。
清理冗余索引
现有复合唯一键(case_id, events, creation_date)是冗余的，因为case_id已经是主键，主键本身具有唯一性，这个唯一键索引只会增加写入时的开销，且对当前查询无帮助，建议删除：
```
ALTER TABLE event_log DROP INDEX [唯一键索引名]; -- 替换为实际索引名
```
分析执行计划定位瓶颈
用EXPLAIN查看当前查询的执行计划，重点关注：
- type列是否为index（使用覆盖索引）
- Extra列是否包含Using index（确认覆盖索引生效）
- 是否出现Using temporary或Using filesort，如果有，说明排序或分组需要临时表，此时预聚合方案的优先级更高。

内容的提问来源于stack exchange，提问作者Basim Wangde