MySQL处理每小时4000万条数据时聚合查询过慢优化咨询

问题核心解答 #

MySQL 原生不存在完全隔离其他运行查询的「冻结机制」，但可通过索引优化、资源调度、参数调优三类方案，大幅降低聚合任务被业务查询干扰的概率，同时提升聚合本身的执行效率，完全可以满足你1小时内完成聚合的需求。

第一类：索引&查询逻辑优化（收益最高，可直接落地） #

• 现有二级索引key2未覆盖聚合所需字段，每次查询都需要回表查询k1/k2/k3，是Sending data阶段耗时过高的核心原因。将索引修改为覆盖索引即可消除回表开销：

ALTER TABLE NETWORK_USAGE_F1 DROP KEY `key2`, ADD KEY `key2` (`clientKey`,`k4`,`k5`,`k1`,`k2`,`k3`);

修改后聚合查询的select逻辑完全可以通过索引直接拿到所有需要的字段，无需访问主键索引，执行速度可提升3~10倍。

• 调整聚合批次大小：当前每次处理100个clientKey，全分区10万个clientKey需要执行1000次查询，大量重复的SQL解析、事务开启开销拉低了整体效率，可将单次批次调整为1000_{2000个clientKey，总查询次数降低到50}100次，整体耗时可进一步降低。
• 主键优化：当前主键id为char(15)，存储开销远高于数值型ID，可将id改为bigint类型的有序ID（如雪花ID），进一步缩小索引体积，降低IO开销。

第二类：资源隔离&调度优化 #

• 利用MySQL资源组功能（MySQL 8.0及以上支持）：将聚合任务的查询线程绑定到单独的CPU核心，同时调高IO优先级，让系统优先分配资源给聚合任务，业务查询线程分配更低的优先级，从内核层面降低业务查询对聚合任务的资源抢占。
• 预加载旧分区数据：你聚合的是已停止写入的最旧分区，可在聚合执行前10分钟执行一次全分区的扫描（如SELECT count(*) FROM NETWORK_USAGE_F1 PARTITION (旧分区名)），将分区的索引数据提前加载到Buffer Pool中，避免聚合过程中频繁触发磁盘IO。
• 调整写入优化参数：你业务不需要ACID特性，可临时调整聚合执行阶段的参数：

innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

这两个参数调整后，聚合结果的insert性能可提升数倍，解决你当前insert耗时超过2s的问题，聚合完成后可按需改回原有配置。

• 确认聚合会话使用READ-UNCOMMITTED隔离级别：你聚合的是已停止写入的分区，不会出现脏读问题，该隔离级别可避免不必要的锁等待和MVCC开销，进一步提升查询速度。

第三类：配置参数优化 #

• 你的业务未使用MyISAM引擎，key_buffer_size设置为1G属于完全浪费内存，可改为16M，空出的内存可将innodb_buffer_pool_size从20G调整到24G，缓存更多的索引数据，降低磁盘IO频率。
• 你当前的sort_buffer_size=512M、join_buffer_size=1G设置过大：每个连接都会独立分配这两块内存，100个连接就会占用超过50G内存，远超你的32G物理内存，会触发频繁swap，严重影响性能。可将两个参数分别调整为2M，完全满足你的查询需求，同时释放大量内存给Buffer Pool使用。
• SSD环境下可将innodb_io_capacity从2000调整为8000，充分发挥SSD的IO性能。

可选架构优化 #

如果上述优化后仍有性能瓶颈，可单独搭建一个只读的从库，专门用于旧分区的聚合计算，聚合完成后再将结果同步回主库，完全隔离聚合任务与业务查询的资源竞争，从根本上解决互相干扰的问题。

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