本文针对 codeigniter 中因多层嵌套查询导致的批量插入极慢问题，提出基于数据库原生能力的三大优化策略：避免重复校验查询、使用批量插入、改用 insert...select 一次性完成关联数据生成。
本文针对 codeigniter 中因多层嵌套查询导致的批量插入极慢问题，提出基于数据库原生能力的三大优化策略：避免重复校验查询、使用批量插入、改用 insert...select 一次性完成关联数据生成。
在实际开发中，尤其是教务系统等需批量生成成绩记录（如 mark 表）的场景，开发者常误用“先查后插”的防御式逻辑——即对每条待插入记录，都执行一次 SELECT ... WHERE ... 判断是否存在，再决定是否 INSERT。您提供的代码正是典型代表：7 层嵌套循环 + 每次插入前执行 2 次独立查询（get_where('mark', ...) + SELECT * FROM mark ORDER BY mark_id DESC LIMIT 1），最终导致 N⁷ 级查询爆炸。以每张表仅 10 条记录为例，总查询数就高达 10⁷ = 1000 万次，耗时超 3 小时绝非偶然。
✅ 正确解法不是优化 PHP 循环，而是将逻辑下沉至数据库层，利用 MariaDB/MySQL 的原子性与批量能力：
1. 摒弃「查后再插」，改用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE
确保 mark 表在业务关键字段（如 terms_id, class_id, section_id, subject_id, exam_paper_id, student_id, session_year_id）上建立唯一联合索引：
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
);随后用单条 SQL 替代全部 PHP 判断逻辑：
INSERT IGNORE INTO mark (
terms_id, class_id, section_id, subject_id,
exam_paper_id, student_id, status, session_year_id, mark_id
) VALUES (?, ?, ?, ?, ?, ?, 1, ?, ?);⚠️ 注意：
mark_id需改为AUTO_INCREMENT主键，彻底删除手动取最大 ID +1 的逻辑（该操作在并发下极易出错且严重拖慢性能）。
2. 批量插入替代逐行插入
CodeIgniter 支持批量插入，可将数百条记录一次性提交：
$batch_data = [];
foreach ($all_combinations as $combo) {
$batch_data[] = [
'terms_id' => $combo['terms_id'],
'class_id' => $combo['class_id'],
'section_id' => $combo['section_id'],
'subject_id' => $combo['subject_id'],
'exam_paper_id' => $combo['exam_paper_id'],
'student_id' => $combo['student_id'],
'status' => 1,
'session_year_id'=> $session_year_id
// mark_id 无需设置，由 AUTO_INCREMENT 自动处理
];
}
// 一次插入最多 1000 条（根据 max_allowed_packet 调整）
$this->db->insert_batch('mark', $batch_data);此方式可减少 90%+ 的网络往返与事务开销。
3. 终极方案：用 INSERT ... SELECT 一次性生成全量数据
若所有关联表数据均在数据库中，完全无需 PHP 循环。直接用一条 SQL 完成笛卡尔积 + 过滤 + 插入：
INSERT IGNORE INTO mark (
terms_id, class_id, section_id, subject_id,
exam_paper_id, student_id, status, session_year_id
)
SELECT
t.terms_id,
c.class_id,
s.section_id,
sub.subject_id,
ep.exam_paper_id,
asub.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 asub ON asub.class_id = c.class_id
AND asub.section_id = s.section_id
AND asub.session_year_id = ?
WHERE c.session_year_id = ?
AND s.session_year_id = ?
AND sub.session_year_id = ?
AND ep.session_year_id = ?
AND asub.session_year_id = ?;✅ 优势：零 PHP 循环、零多次查询、数据库引擎自动优化执行计划、毫秒级完成原本数小时的任务。
总结与建议
- 永远不要在循环内执行数据库查询（尤其是 N 层嵌套）；
-
INSERT IGNORE/ON DUPLICATE KEY UPDATE是去重插入的黄金标准； - 批量操作（
insert_batch）是性能分水岭，单次插入 100~1000 行为佳； - 复杂关联数据生成，优先考虑
INSERT ... SELECT，让数据库做它最擅长的事； - 同时检查并优化相关表的索引：所有
WHERE和JOIN字段必须有高效索引支持。
通过以上重构，原需 3 小时的操作可压缩至 1 秒内完成，且系统响应速度与稳定性将获得质的提升。
