Perl结合SQLite批量INSERT查询优化需求及代码求助

你现在遇到的问题其实很常见——单条循环INSERT在处理大量数据时，因为SQLite默认的自动提交机制，每次插入都会触发磁盘写入，这才是耗时的核心原因。作为编程爱好者，咱们可以用几个专业的技巧把速度提上去，下面给你详细拆解：

1. 用显式事务包裹所有插入操作 #

SQLite默认每执行一条INSERT就自动提交一次事务，频繁的磁盘IO是最大的性能杀手。咱们把所有插入放在一个事务里，只在最后提交一次，就能把磁盘IO次数从几万次降到1次，速度会暴涨。

修改你的代码，关键是关闭自动提交，然后用事务包裹循环：

use JSON::XS;
use DBI;

my $CCC = 'string'; 
# 连接时关闭AutoCommit，手动控制事务
my $dbh = DBI->connect(
    "dbi:SQLite:dbname=aaaa.db", 
    "", "", 
    { 
        RaiseError => 1,
        AutoCommit => 0, # 核心：关闭自动提交
    },
) or die $DBI::errstr;

# 创建表（假设你的表结构已经填好）
my $stmt = "CREATE TABLE IF NOT EXISTS $CCC (id INTEGER PRIMARY KEY, col1 TEXT, col2 INTEGER)";
$dbh->do($stmt);

# 读取并解析JSON数据
my $json_data = do {
    open my $fh, '<', 'your_data.json' or die "无法打开JSON文件: $!";
    local $/; # 一次性读取整个文件
    decode_json(<$fh>);
};

# 准备INSERT语句（预编译一次，重复使用）
my $insert_sth = $dbh->prepare("INSERT INTO $CCC (col1, col2) VALUES (?, ?)");

# 开启事务并执行插入
eval {
    $dbh->begin_work(); # 显式开启事务（可选，但代码更清晰）
    foreach my $record (@$json_data) {
        $insert_sth->execute($record->{col1}, $record->{col2});
    }
    $dbh->commit(); # 一次性提交所有更改
    print "数据导入完成！\n";
};

# 处理异常，回滚事务
if ($@) {
    warn "导入出错，回滚数据: $@";
    $dbh->rollback();
}

# 清理资源
$insert_sth->finish();
$dbh->disconnect();

2. 进阶：批量插入（一次插入多条记录） #

如果数据量特别大，咱们还可以把多条记录合并成一个INSERT语句，进一步减少SQL解析的次数。Perl的DBI提供了execute_array方法，可以批量绑定参数并执行：

# 接上面的代码，替换循环部分
my $batch_size = 100; # 每次插入100条，可根据内存调整
my @batch_records;

eval {
    $dbh->begin_work();
    foreach my $record (@$json_data) {
        push @batch_records, [$record->{col1}, $record->{col2}];
        # 达到批量大小就执行一次
        if (@batch_records >= $batch_size) {
            $insert_sth->execute_array({}, @batch_records);
            @batch_records = (); # 清空批量数组
        }
    }
    # 处理剩余的不足批量大小的记录
    if (@batch_records) {
        $insert_sth->execute_array({}, @batch_records);
    }
    $dbh->commit();
};

这个方法比单条执行又快了不少，因为减少了和数据库的交互次数。

3. 开启SQLite的性能优化参数 #

在连接数据库后，加上这两个PRAGMA配置，可以进一步提升写入速度（注意：synchronous = OFF会降低数据安全性，适合导入数据时临时使用，导入完成后建议改回默认值）：

# 连接后执行
$dbh->do("PRAGMA synchronous = OFF");
$dbh->do("PRAGMA journal_mode = WAL");

WAL（Write-Ahead Logging）模式是SQLite的高效写入模式，能大幅提升并发和写入性能。

总结 #

这几个技巧组合起来，能把你的导入速度提升几十甚至上百倍——核心就是减少事务提交次数、减少SQL解析次数、优化SQLite的写入模式。你可以根据自己的数据量选择合适的方案，先从事务开始改，效果就会很明显。

内容的提问来源于stack exchange，提问作者J. Doe