InnoDB执行基于查询的大插入时，如何规避回滚代价？

这种场景我太有共鸣了！之前帮同事处理过类似的坑——跑了快一小时的大插入突然发现不对劲，终止后回滚又耗了更久，简直让人崩溃。针对你说的分析类 workload 需求，给你几个实用的解决方案：

• 手动拆分批次，分批提交事务
  既然大事务的回滚代价太高，那咱们就把它拆成一堆小事务！不要一次性执行全量的INSERT ... SELECT，而是用循环分批插入，每插完一批就手动提交，这样即使中途取消，也只需要回滚当前批次的少量数据，之前提交的部分会完整保留。

  举个简单的例子，用主键范围分页（比OFFSET更高效，适合超大表）：

  SET @last_id = 0;
  SET @batch_size = 1000; -- 这个数值可以根据服务器性能调整，比如5000或10000
  
  REPEAT
      INSERT INTO `newtable`
      SELECT * FROM `verylargetable` WHERE id > @last_id ORDER BY id LIMIT @batch_size;
      SET @last_id = (SELECT MAX(id) FROM `newtable`);
      COMMIT; -- 每批提交一次
  UNTIL ROW_COUNT() = 0 END REPEAT;
  

  如果你的表没有自增主键，也可以用LIMIT + OFFSET的方式，不过超大表下OFFSET会越来越慢，需要权衡。

• 用导入工具替代直接INSERT SELECT
  对于分析类的批量导入，LOAD DATA INFILE或者mysqlimport工具通常比INSERT ... SELECT更高效，而且支持按行数自动提交。比如用mysqlimport时加上--commit=1000参数，每导入1000行就自动提交一次，同样能避免大事务的回滚问题。

• 调整InnoDB参数辅助优化（谨慎操作）
  虽然没有直接让InnoDB跳过回滚的参数，但可以调整一些参数降低小事务的提交开销，让分批插入更顺畅：

  • 把innodb_flush_log_at_trx_commit设为2：这个参数会减少每次提交的磁盘IO开销，适合非核心的分析库（注意会有极小的概率丢失最后一秒的数据，业务场景要匹配）；
  • 适当调大innodb_buffer_pool_size：让更多数据在内存中处理，提升分批插入的效率。

最后回到你的核心问题：InnoDB作为事务型引擎，本身是遵循ACID的，没有办法直接让它在取消时跳过整个事务的回滚，但通过拆分小事务的方式，相当于把“全量回滚”变成了“仅回滚当前小批次”，代价就可控了，而且正好符合你分析场景的需求——即使只插入了部分数据，也比等几十分钟回滚要强。

备注：内容来源于stack exchange，提问作者juacala