如何解决SQLite数据库增大后CSV上传速度过慢的问题

这是SQLite批量插入场景里非常常见的性能瓶颈问题，我结合实际开发经验给你拆解下原因和对应的解决方案：

核心原因分析 #

你遇到的问题本质是SQLite的写入特性和批量操作的低效实现共同导致的：

• 小数据量时，单条插入或小事务的磁盘IO、索引更新开销不明显；
• 数据库体积增大后，每次写入的磁盘寻址、锁竞争、索引维护成本都会线性上升，原本的低效操作就会被放大。

针对性解决方案 #

1. 用事务包裹整个导入流程（最关键的优化） #

默认情况下，PDO执行每条INSERT都会自动开启并提交一个独立事务，SQLite每处理一个事务都要完成磁盘日志写入和刷新操作——数据量越大，这种频繁的IO操作开销就越恐怖。

把整个CSV导入逻辑包裹在一个事务里，就能把所有插入合并成一次磁盘写入操作，速度会直接提升一个量级：

try {
    // 开启事务
    $pdo->beginTransaction();

    // 这里放你的CSV解析、循环插入逻辑
    foreach ($csvRows as $row) {
        // 执行INSERT语句
        $stmt = $pdo->prepare("INSERT INTO your_table (col1, col2) VALUES (?, ?)");
        $stmt->execute([$row['col1'], $row['col2']]);
    }

    // 提交事务
    $pdo->commit();
} catch (PDOException $e) {
    // 出错回滚
    $pdo->rollBack();
    die("导入失败: " . $e->getMessage());
}

2. 改用批量INSERT语句（进一步减少SQL执行次数） #

如果循环里每次只插入一行，哪怕有事务，频繁的prepare和execute也会带来额外开销。可以把多行数据合并成一条INSERT语句，一次插入几十到几百行：

$batchSize = 200; // 根据内存情况调整，建议100-500行
$values = [];
$params = [];

foreach ($csvRows as $row) {
    $values[] = '(?, ?)'; // 对应你的表字段数量
    $params[] = $row['col1'];
    $params[] = $row['col2'];

    // 达到批量大小就执行一次插入
    if (count($values) >= $batchSize) {
        $sql = "INSERT INTO your_table (col1, col2) VALUES " . implode(',', $values);
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        
        // 重置批量数组
        $values = [];
        $params = [];
    }
}

// 处理最后一批不足批量大小的数据
if (!empty($values)) {
    $sql = "INSERT INTO your_table (col1, col2) VALUES " . implode(',', $values);
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
}

这种方式能把SQL执行次数减少到原来的1/200，性能提升非常明显。

3. 临时删除索引（数据量超大时必备） #

如果你的表上创建了多个索引，每次插入都要更新所有索引——数据量越大，索引树越复杂，更新成本就越高。可以先删除索引，导入完成后再重建：

// 导入前删除所有索引
$pdo->exec("DROP INDEX IF EXISTS idx_col1");
$pdo->exec("DROP INDEX IF EXISTS idx_col2");

// 执行上面的事务+批量插入逻辑

// 导入完成后重建索引
$pdo->exec("CREATE INDEX idx_col1 ON your_table (col1)");
$pdo->exec("CREATE INDEX idx_col2 ON your_table (col2)");

注意：如果导入过程中需要查询数据，这个方法不适用；但纯导入场景下，能节省大量时间。

4. 优化PDO配置和SQLite参数 #

• 关闭PDO的模拟预处理，让SQLite原生处理语句：

  $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  

• 调整SQLite的同步模式（牺牲一点安全性换速度，适合导入场景）：

  $pdo->exec("PRAGMA synchronous = OFF");
  $pdo->exec("PRAGMA journal_mode = MEMORY");
  

  导入完成后可以再改回默认的synchronous = NORMAL，保证数据安全性。

总结 #

优先实施事务包裹+批量插入这两个优化，基本能解决90%以上的性能问题；如果数据量特别大（比如百万级以上），再加上临时删除索引和SQLite参数调整，导入速度会和小数据量时差不多。

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