MySQL 8分区大表同服务器INSERT…SELECT插入极慢求助

针对3亿条消息数据迁移到分区表时速度骤降的问题，结合服务器配置和测试现象，给出以下针对性优化措施：

一、紧急调整InnoDB核心内存配置 #

服务器有16GB物理内存，但innodb_buffer_pool_size仅设置为128MB，这是导致分区表迁移性能暴跌的核心原因——分区表需要缓存多个分区的元数据、索引页和数据页，过小的缓冲池会引发频繁磁盘IO，后续数据量增大后性能急剧下滑。

• 临时调整（立即生效，重启后失效）：

  SET GLOBAL innodb_buffer_pool_size = 8589934592; -- 8GB，约占物理内存的50%-60%
  

• 永久生效（修改my.cnf/my.ini）：

  innodb_buffer_pool_size = 8G
  

二、迁移过程中的临时参数优化 #

在迁移会话中关闭不必要的检查和自动提交，减少事务开销：

SET autocommit = 0;          -- 关闭自动提交，手动控制事务边界
SET unique_checks = 0;       -- 关闭唯一键检查，迁移完成后再开启
SET foreign_key_checks = 0;  -- 关闭外键检查，避免关联校验开销
SET innodb_flush_log_at_trx_commit = 2; -- 每秒刷一次日志，而非每次提交

每次分块插入完成后执行COMMIT;，避免事务过大导致undo日志膨胀。

三、优化分块迁移策略 #

1. 缩小分块大小：将原1000万条的分块调整为50万-100万条，过大的分块会导致事务日志和undo日志暴增，引发IO瓶颈。
2. 按分区键分块：以Arrival（分区键）为分块依据，按时间区间批量查询原表数据，例如：

   INSERT INTO new_messages 
   SELECT * FROM old_messages 
   WHERE Arrival BETWEEN '2022-01-00 00:00:00' AND '2022-01-07 23:59:59';
   

   这种方式让数据直接写入对应分区，避免MySQL自动判断分区的额外开销。

四、分区表结构与索引优化 #

1. 先删后建非主键索引：迁移前删除新表除主键外的所有索引，待全量数据迁移完成后再重建。批量插入时维护索引的开销远高于批量重建索引，尤其是分区表的多分区索引。
   • 迁移前执行：

     DROP INDEX idx_senderid ON new_messages;
     

   • 迁移完成后执行：

     CREATE INDEX idx_senderid ON new_messages (Arrival, SenderID); -- 构建分区友好的复合索引
     

2. 确认复合主键合理性：分区表的主键必须包含分区键（Arrival），建议使用(Arrival, ID)作为复合主键，既满足分区要求，又保证主键唯一性，同时让数据在分区内按ID有序存储，提升插入和查询效率。

五、调整InnoDB IO相关参数 #

innodb_log_file_size = 1G          -- 增大重做日志文件，减少检查点频率
innodb_write_io_threads = 8        -- 提升写入IO线程数，适配高并发写入
innodb_read_io_threads = 8         -- 提升读取IO线程数，加快原表数据读取
innodb_flush_neighbors = 0         -- 关闭邻接页刷新，减少不必要的IO

修改后需重启MySQL生效。

六、关闭自适应哈希索引（按需） #

你之前测试发现关闭innodb_adaptive_hash_index后初期速度提升，但后续骤降，这是因为缓冲池过小导致哈希索引失效。在调整缓冲池大小后，可根据实际测试决定是否关闭：

SET GLOBAL innodb_adaptive_hash_index = OFF;

性能差异原因说明 #

非分区复合主键表迁移更快的核心原因：全局索引的缓存效率更高，缓冲池可以集中缓存热点索引页；而分区表的多分区索引需要更多缓存空间，在原缓冲池配置下，后续数据量增大后缓存命中率急剧下降，引发大量随机IO，导致性能暴跌。

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