CodeIgniter中Mark表批量插入耗时过长问题求助

安全阿岳

2026-8-24

CodeIgniter+MariaDB批量插入mark表性能优化方案

核心问题分析

你的代码存在几个致命性能瓶颈：

7层嵌套循环导致N+1查询爆炸，每层循环都发起一次数据库请求，总请求数可能达到数万甚至数十万次
每次插入前单独查询mark表判断存在性，额外增加一倍查询量
手动生成mark_id的逻辑不仅多余，还会引发并发冲突
单条记录逐个插入，数据库IO开销极大

优化方案分步实施

1. 用JOIN查询一次性获取所有待插入数据

将所有关联表通过JOIN合并成一个查询，直接拿到所有需要插入mark的记录，彻底消除嵌套循环的N+1问题。

// 构建JOIN查询，一次性获取所有待插入的mark数据
$sql = "
    SELECT 
        t.terms_id,
        c.class_id,
        s.section_id,
        sub.subject_id,
        ep.exam_paper_id,
        asg.student_id,
        {$session_year_id} AS session_year_id,
        1 AS status
    FROM terms t
    CROSS JOIN class c ON c.session_year_id = {$session_year_id}
    INNER JOIN section s ON s.class_id = c.class_id AND s.session_year_id = {$session_year_id}
    INNER JOIN subject sub ON sub.class_id = c.class_id AND sub.section_id = s.section_id AND sub.session_year_id = {$session_year_id}
    INNER 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 = {$session_year_id}
    INNER JOIN assign_subject asg ON asg.class_id = c.class_id AND asg.section_id = s.section_id AND asg.session_year_id = {$session_year_id}
";

// 执行查询并获取结果
$insert_data = $this->db->query($sql)->result_array();

注意：如果terms表数据较多，CROSS JOIN可能生成大量数据，可根据业务限制terms范围（比如添加WHERE条件）

2. 批量插入+数据库层面去重

放弃单条插入和前置查询，改用INSERT IGNORE或ON DUPLICATE KEY UPDATE结合批量插入，让数据库自动处理重复判断，同时减少IO交互。

首先确保mark表有唯一联合索引：

ALTER TABLE mark ADD UNIQUE INDEX idx_unique_mark (terms_id, class_id, section_id, subject_id, exam_paper_id, student_id, session_year_id);

然后用CodeIgniter的批量插入结合原生SQL实现去重：

if (!empty($insert_data)) {
    // 开启事务提升批量操作性能
    $this->db->trans_start();

    // 构建批量插入SQL
    $fields = implode(', ', array_keys($insert_data[0]));
    $values = [];
    foreach ($insert_data as $row) {
        $row_values = [];
        foreach ($row as $val) {
            $row_values[] = $this->db->escape($val);
        }
        $values[] = '(' . implode(', ', $row_values) . ')';
    }
    $batch_sql = "INSERT IGNORE INTO mark ({$fields}) VALUES " . implode(', ', $values);
    
    // 执行批量插入
    $this->db->query($batch_sql);

    $this->db->trans_complete();
}

INSERT IGNORE会跳过重复记录；如果需要更新重复记录的status等字段，可替换为INSERT ... ON DUPLICATE KEY UPDATE status=VALUES(status)
事务可以将多个插入操作合并为一次提交，大幅提升性能

3. 移除手动生成mark_id的逻辑

如果mark_id是自增主键，完全不需要手动查询最大值+1，数据库会自动维护自增序列。手动生成不仅浪费性能，还会在并发场景下导致主键冲突。

4. 优化关联表索引

确保所有关联查询用到的字段都有索引：

class表：添加idx_session_year索引（session_year_id）
section表：添加idx_class_session联合索引（class_id, session_year_id）
subject表：添加idx_class_section_session联合索引（class_id, section_id, session_year_id）
exam_paper表：添加idx_terms_class_section_subject_session联合索引（terms_id, class_id, section_id, subject_id, session_year_id）
assign_subject表：添加idx_class_section_session联合索引（class_id, section_id, session_year_id）

预期效果

优化后插入耗时可从3小时缩短至数分钟甚至更短，页面卡顿问题也会因为数据库请求量骤降而解决。

内容的提问来源于stack exchange，提问作者ict nepal software