AWS上MySQL简单AVG()聚合查询耗时极长问题求助

首先咱们先拆解下你遇到的问题根源：虽然date字段建了索引，但你的查询需要返回count(*)和avg(01)——普通的date二级索引只包含date字段和主键ID，InnoDB执行这个查询时，得先通过date索引找到所有匹配的主键，再回表到主键索引去读取01字段的值。如果2017-11-01这一天的数据量不小（比如几百万甚至上千万行），回表带来的IO开销会极其巨大，这就是查询耗时近10分钟的核心原因。

下面给你几个针对性的优化方案，按实施成本从低到高排序：

方案1：创建覆盖索引（最直接见效） #

这是成本最低、效果最显著的优化方式，创建一个包含date和01字段的联合索引，这样MySQL可以直接从这个索引里获取所有需要的数据，完全不需要回表：

CREATE INDEX idx_date_01 ON mytable(`date`, `01`);

创建完成后再执行查询，你可以看EXPLAIN结果的Extra字段，应该会显示Using index（表示用了覆盖索引），查询速度会有数量级的提升。

方案2：预计算统计值（适合离线报表场景） #

如果这个查询是定期跑的报表类需求，不需要实时数据，那可以提前预计算结果：

• 建一个汇总表用来存储每日的统计值：

  CREATE TABLE daily_stats (
      stat_date DATE PRIMARY KEY,
      row_count BIGINT NOT NULL,
      avg_01 DECIMAL(10,2) NOT NULL
  );
  

• 每天定时跑任务（比如用crontab或MySQL事件），统计当天的count(*)和avg(01)写入这个汇总表；
• 之后查询直接从汇总表取数，耗时会降到毫秒级：

  SELECT row_count, avg_01 FROM daily_stats WHERE stat_date = '2017-11-01';
  

方案3：分区表优化（适合按日期归档的场景） #

如果你的数据是按日期自然划分的，可以把mytable改造成按date字段分区的表（比如按天或按月分区）。这样查询2017-11-01的数据时，MySQL会直接定位到对应的分区，只扫描该分区的数据，避免遍历整个10亿行的大表：

ALTER TABLE mytable 
PARTITION BY RANGE (TO_DAYS(`date`)) (
    PARTITION p20171101 VALUES LESS THAN (TO_DAYS('2017-11-02')),
    -- 按需添加其他历史日期的分区
    PARTITION p_default VALUES LESS THAN MAXVALUE
);

注意：分区最好和覆盖索引配合使用，不然还是会有回表的开销。

方案4：调整InnoDB配置（辅助优化） #

如果服务器IO性能是瓶颈，可以调整几个InnoDB参数来提升缓存效率：

• 增大innodb_buffer_pool_size：尽可能把常用的数据和索引缓存到内存里，建议设置为服务器内存的70%-80%（比如32G内存的服务器设置为24G）；
• 开启innodb_adaptive_hash_index：加速索引的查找速度；
• 调整innodb_flush_log_at_trx_commit：如果对数据一致性要求不是极高（允许秒级数据丢失），可以设置为2，减少IO竞争。

补充一句：你提供的EXPLAIN结果没贴全，如果能看到key字段确认是否用到了date索引，以及rows字段的预估扫描行数，能更精准地定位问题，但上面的方案应该能解决你的核心痛点。

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