本文针对 codeigniter + mariadb 环境下因多层嵌套查询导致的批量插入极慢（耗时超3小时）问题，提供基于批量插入、避免重复查询和原生 sql 优化的高效解决方案。
本文针对 codeigniter + mariadb 环境下因多层嵌套查询导致的批量插入极慢（耗时超3小时）问题，提供基于批量插入、避免重复查询和原生 sql 优化的高效解决方案。
在实际开发中，尤其是教务管理系统等涉及多维关联（如学年→学期→年级→班级→科目→试卷→学生→成绩）的场景，开发者常误用“先查后插”的嵌套循环逻辑。您提供的代码中存在 7 层嵌套 foreach + 每次循环执行多次 get_where() 查询，最终导致数据库 I/O 指数级增长：若每张表平均有 10 条匹配记录，总查询次数将高达 $10^7 = 10,000,000$ 次——这正是页面卡死、插入耗时超 3 小时的根本原因。
✅ 正确优化思路：三步替代嵌套循环
1. 摒弃「先查再插」，改用 INSERT ... SELECT 原生批量插入
利用 MariaDB 的 INSERT INTO ... SELECT 语法，直接从多表联查结果一次性生成待插入数据，彻底消除 PHP 层循环与逐条查询：
INSERT INTO mark (
mark_id, terms_id, class_id, section_id, subject_id,
exam_paper_id, student_id, status, session_year_id
)
SELECT
COALESCE((SELECT MAX(mark_id) FROM mark), 0) + ROW_NUMBER() OVER (ORDER BY t.terms_id, c.class_id, s.section_id, sub.subject_id, ep.exam_paper_id, asg.student_id) AS mark_id,
t.terms_id,
c.class_id,
s.section_id,
sub.subject_id,
ep.exam_paper_id,
asg.student_id,
1 AS status,
? AS session_year_id
FROM terms t
CROSS JOIN class c ON c.session_year_id = ?
CROSS JOIN section s ON s.class_id = c.class_id AND s.session_year_id = ?
CROSS JOIN subject sub ON sub.class_id = c.class_id
AND sub.section_id = s.section_id
AND sub.session_year_id = ?
CROSS JOIN exam_paper ep ON ep.terms_id = t.terms_id
AND ep.class_id = c.class_id
AND ep.section_id = s.section_id
AND ep.subject_id = sub.subject_id
AND ep.session_year_id = ?
CROSS JOIN assign_subject asg ON asg.class_id = c.class_id
AND asg.section_id = s.section_id
AND asg.session_year_id = ?
WHERE NOT EXISTS (
SELECT 1 FROM mark m
WHERE m.terms_id = t.terms_id
AND m.class_id = c.class_id
AND m.section_id = s.section_id
AND m.subject_id = sub.subject_id
AND m.exam_paper_id = ep.exam_paper_id
AND m.student_id = asg.student_id
AND m.session_year_id = ?
);? 提示：将
?占位符统一替换为$session_year_id（CodeIgniter 支持query()绑定参数），并确保mark(terms_id, class_id, section_id, subject_id, exam_paper_id, student_id, session_year_id)上已建立唯一联合索引，否则NOT EXISTS效率会下降。
2. 若必须使用 CodeIgniter ORM，采用批量插入（Batch Insert）
避免 insert() 单条调用，改用 insert_batch() 并预生成完整数据集：
// 1. 一次性获取所有基础维度数据（去重+关联）
$this->db->select('t.terms_id, c.class_id, s.section_id, sub.subject_id, ep.exam_paper_id, asg.student_id');
$this->db->from('terms t');
$this->db->join('class c', 'c.session_year_id = ' . (int)$session_year_id);
$this->db->join('section s', 's.class_id = c.class_id AND s.session_year_id = ' . (int)$session_year_id);
$this->db->join('subject sub', 'sub.class_id = c.class_id AND sub.section_id = s.section_id AND sub.session_year_id = ' . (int)$session_year_id);
$this->db->join('exam_paper ep', 'ep.terms_id = t.terms_id AND ep.class_id = c.class_id AND ep.section_id = s.section_id AND ep.subject_id = sub.subject_id AND ep.session_year_id = ' . (int)$session_year_id);
$this->db->join('assign_subject asg', 'asg.class_id = c.class_id AND asg.section_id = s.section_id AND asg.session_year_id = ' . (int)$session_year_id);
$this->db->where_not_in("CONCAT(t.terms_id, '-', c.class_id, '-', s.section_id, '-', sub.subject_id, '-', ep.exam_paper_id, '-', asg.student_id, '-', $session_year_id)",
"(SELECT CONCAT(terms_id, '-', class_id, '-', section_id, '-', subject_id, '-', exam_paper_id, '-', student_id, '-', session_year_id) FROM mark)"
);
$query = $this->db->get();
$raw_data = $query->result_array();
// 2. 生成带自增 mark_id 的批量数组（需提前获取当前最大 ID）
$max_id = $this->db->select('COALESCE(MAX(mark_id), 0) as max_id')->from('mark')->get()->row()->max_id;
$data_batch = [];
foreach ($raw_data as $i => $row) {
$data_batch[] = [
'mark_id' => $max_id + $i + 1,
'terms_id' => $row['terms_id'],
'class_id' => $row['class_id'],
'section_id' => $row['section_id'],
'subject_id' => $row['subject_id'],
'exam_paper_id' => $row['exam_paper_id'],
'student_id' => $row['student_id'],
'status' => 1,
'session_year_id' => $session_year_id
];
}
// 3. 单次批量插入（比循环插入快 10 倍以上）
if (!empty($data_batch)) {
$this->db->insert_batch('mark', $data_batch);
}3. 关键注意事项与加固建议
- ? 强制唯一约束：在
mark表上添加唯一索引，防止重复插入：ALTER TABLE mark ADD UNIQUE KEY uk_mark_unique ( terms_id, class_id, section_id, subject_id, exam_paper_id, student_id, session_year_id ); - ⚙️ 关闭自动提交（可选）：大数据量插入前执行
$this->db->trans_start();+$this->db->trans_complete();，减少事务开销。 - ? 禁用查询日志：生产环境务必关闭 CodeIgniter 的
log_threshold，避免日志写入拖慢速度。 - ? 分批处理：若单次
INSERT...SELECT数据量过大（>50万行），可在 SQL 中添加LIMIT+OFFSET分页执行，或在 PHP 中按 1000 行/批切割$data_batch。
通过以上重构，原本需 3 小时的操作通常可在 数秒至数十秒内完成，同时显著降低数据库连接压力与内存占用。核心原则始终是：让数据库做它最擅长的事（集合运算），而非让 PHP 承担关系遍历逻辑。
