CodeIgniter4中如何优化数千行批量insert/update操作耗时

你当前使用$Model->insertBatch($array, null, count($array));单次提交8000行数据耗时过长，核心问题是单批次数据量过大触发框架、数据库层面的多重性能损耗，可按以下方案优化：

insertBatch 优化手段 #

• 拆分单批次数据量，不要全量单次提交。单批次传入500~1000行是综合性能最优的区间，拆分后循环插入的总耗时远低于单次提交全量数据，还能避免SQL长度超出数据库max_allowed_packet限制的问题。示例代码：

$chunkSize = 1000; // 可根据单条数据长度微调，单条字段多就调到500，字段少可适当上调
$dataChunks = array_chunk($insertData, $chunkSize);
foreach ($dataChunks as $chunk) {
    $model->insertBatch($chunk, null, count($chunk));
}

• 全量操作包裹在同一事务中。InnoDB引擎默认每次SQL执行都会触发事务提交、磁盘刷盘，把所有分片插入操作放到同一个事务里，只会在事务最终提交时做一次刷盘，能降低60%以上的磁盘IO消耗。注意不要给每个分片单独开事务。
• 临时关闭非必要的框架开销。批量操作前临时关闭SQL调试、SQL日志记录功能，避免框架生成超长SQL日志占用IO：

$db = \Config\Database::connect();
// 保存原有配置
$originDebug = $db->debug;
$db->debug = false; // 关闭调试，避免记录超长SQL

$db->transStart();
// 此处放分片插入的循环逻辑
$db->transComplete();

// 恢复原有配置
$db->debug = $originDebug;

• 提前预处理插入数据。插入前先裁剪掉和数据表字段无关的冗余键值，提前完成数据格式校验、过滤无效数据，不要让框架逐行做字段匹配、格式校验；如果插入数据是内部生成、完全可信无注入风险，可以给insertBatch传第四个参数false关闭自动转义，减少逐字段转义的性能消耗。
• 避免使用模型事件。如果对应Model挂载了beforeInsert、afterInsert等事件，批量操作时会逐行触发事件，带来大量额外开销，批量导入场景建议临时替换为直接调用数据库类的insertBatch方法，避开模型事件触发。

updateBatch 额外优化点 #

• 同样遵循500~1000行的单批次大小规则，不要单次传入全量更新数据。
• 匹配更新的条件字段必须加索引。updateBatch会根据你指定的匹配字段（默认是主键）逐行定位更新记录，如果匹配字段没有索引，会触发全表扫描，耗时会出现指数级上涨。
• 只传需要更新的字段。不要把未修改的字段也传入更新数组，能大幅缩短生成的SQL长度，同时减少数据库行锁的影响范围。

数据库层面配套优化 #

• 可信数据场景下临时关闭校验：如果批量操作的数据是内部生成、不存在重复值、外键关联错误，可以在操作前临时关闭外键校验、唯一键校验，操作完成后再恢复，能大幅降低索引维护开销：

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
-- 执行批量插入/更新操作
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

• 调整数据库配置：把max_allowed_packet设置为16M~64M，保证单批次生成的SQL可以正常传输解析；针对批量插入场景可适当调大bulk_insert_buffer_size参数提升缓存效率。
• 全量初始化数据场景下，可临时删除表上的非主键二级索引、暂停触发器，等数据全部导入完成后再重建索引、恢复触发器，导入速度能提升数倍。

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