数据库查询数据量过大该如何优化？

针对数据库查询数据量过大的问题，以下是系统化的优化方案，结合用户提供的SQL场景进行针对性分析：

1. 避免函数操作索引列

   当前SQL中YEAR(create_time)和WEEK(create_time)函数导致create_time索引失效

   优化方案：改用范围查询替代函数计算

   SELECT CONCAT(YEAR(create_time),'年',WEEK(create_time),'周') AS dt, COUNT(DISTINCT `to`) AS numFROM mail_record202108WHERE create_time BETWEEN '2021-01-01' AND '2021-12-31'GROUP BY YEAR(create_time), WEEK(create_time)ORDER BY YEAR(create_time), WEEK(create_time)

2. 建立高效复合索引

   必须创建覆盖索引：ALTER TABLE mail_record202108 ADD INDEX idx_create_to (create_time, to)

   索引选择依据：WHERE条件列 > GROUP BY列 > ORDER BY列

3. 执行计划分析

   使用EXPLAIN检查是否出现"Using temporary; Using filesort"

   理想状态应显示：type: rangekey: idx_create_torows: 预估扫描行数显著减少

1. 表分区策略

   ALTER TABLE mail_record202108 PARTITION BY RANGE (YEAR(create_time)) ( PARTITION p2021 VALUES LESS THAN (2022), PARTITION pmax VALUES LESS THAN MAXVALUE);

   查询时自动剪枝，只扫描相关分区

2. 预计算聚合表

   CREATE TABLE weekly_stats ASSELECT YEAR(create_time) AS year, WEEK(create_time) AS week, COUNT(DISTINCT `to`) AS recipient_countFROM mail_record202108GROUP BY YEAR(create_time), WEEK(create_time);

   通过定时任务更新统计表

1. 简化嵌套查询原SQL存在冗余GROUP BY，优化为：

   SELECT CONCAT(year_week.year, '年', year_week.week, '周') AS dt, recipient_count AS numFROM ( SELECT YEAR(create_time) AS year, WEEK(create_time) AS week, COUNT(DISTINCT `to`) AS recipient_count FROM mail_record202108 GROUP BY YEAR(create_time), WEEK(create_time)) year_weekORDER BY year, week;

2. 分页查询实现

   -- 第一页SELECT * FROM ( SELECT CONCAT(YEAR(create_time),'年',WEEK(create_time),'周') AS dt, COUNT(DISTINCT `to`) AS num FROM mail_record202108 GROUP BY YEAR(create_time), WEEK(create_time) ORDER BY YEAR(create_time), WEEK(create_time) LIMIT 0, 50) AS result;

1. 调整InnoDB缓冲池大小（通常设为可用内存的70-80%）innodb_buffer_pool_size = 12G # 根据服务器内存调整
2. 增加排序缓冲区sort_buffer_size = 4Mjoin_buffer_size = 4M

1. 建立慢查询日志监控：slow_query_log = 1long_query_time = 2log_queries_not_using_indexes = 1
2. 定期执行ANALYZE TABLE mail_record202108更新统计信息

1. 立即执行：添加复合索引 + 重写查询语句
2. 3天内完成：分区表改造 + 预计算表建立
3. 长期优化：服务器参数调优 + 监控体系搭建

通过组合使用这些优化手段，在200万级数据量下，查询响应时间可从分钟级优化至秒级。实际优化效果建议通过EXPLAIN分析和压力测试验证。