ThinkPHP 快速插入 SQLite

因为 SQLite 是无服务数据库，所以每次操作数据都需要消耗磁盘 IO。而想要快速插入数据，本质上就是想要考虑如何减少磁盘 IO 次数。

减少 io 有很多种方法，比如批量插入、使用事务、内存模式等，这里的优化方案选择批量插入和事务

这次测试使用的是一个15W 条数据的 CSV文件，将读入的数据写入数据库。

需要注意的是，本文只是优化方案的记录，并非严格的性能测试，所以这些性能结果在不同设备上会有所差异，不过这不重要。

单条插入

我们先测试一下单条插入的性能：

因单条插入性能实在太低，我们只测试一分钟的插入条数。

$list=file('bank.csv',FILE_IGNORE_NEW_LINES);

foreach ($list as $val){
    $temp = explode(',',$val);
    $data[]= [
        'number' => (int) $temp[1],
        'bankTypeName' => $temp[2],
        'bankName' => $temp[3],
    ];
}

$stime=microtime(true);
 foreach ($data as $item){
    Db::table('bank_test')->insert($item);
    // 执行 60 秒退出
    if((microtime(true) - $stime) > 60) {
        exit;
    }
}

经过测试一分钟插入数据 665 条，因为每次写入都是一次磁盘操作，所以性能非常低。

批量插入

$list=file('bank.csv',FILE_IGNORE_NEW_LINES);
foreach ($list as $val){
    $temp = explode(',',$val);
    $data[]= [
        'number' => (int) $temp[1],
        'bankTypeName' => $temp[2],
        'bankName' => $temp[3],
    ];
}
$stime=microtime(true);
$data_chunk = array_chunk($data,300);

foreach ($data_chunk as $item)
{
    Db::table('bank_test')->insertAll($item);
}

$etime=microtime(true);//获取程序执行结束的时间
$total=$etime-$stime;   //计算差值
// 指令输出
$output->writeln("插入:".count($data)."数据，耗时：{$total}");

结果：插入 150792 数据，耗时 64.941246032715 秒

可以看出来，即使只用上了批量插入，性能的提升也是非常明显的。

需要注意的是，单次批量插入条数有限制，有的文章说单次不能超过 999 条，但我测试不能超过 300 条。

使用事务

使用事务提升插入性能是 SQLite 的一个特性，原理就是将先将插入数据缓存到内存，最后提交事务的时候将数据写入磁盘，这样操作只会有一次磁盘IO。

$list=file('bank.csv',FILE_IGNORE_NEW_LINES);
foreach ($list as $val){
    $temp = explode(',',$val);
    $data[]= [
        'number' => (int) $temp[1],
        'bankTypeName' => $temp[2],
        'bankName' => $temp[3],
    ];
}
$stime=microtime(true);

Db::startTrans();
    foreach ($data as $item)
    {
        Db::table('bank_test')->insert($item);
    }
Db::commit();

$etime=microtime(true);//获取程序执行结束的时间
$total=$etime-$stime;   //计算差值
// 指令输出
$output->writeln("插入:".count($data)."数据，耗时：{$total}");

结果：插入 150792 数据，耗时 62.787980079651 秒

可以看到使用事务之后，即使是单条插入，性能也是很高的。

批量+事务

那么我们是否可以将上面的事务和批量插入结合在一起使用呢？

$list=file('bank.csv',FILE_IGNORE_NEW_LINES);
foreach ($list as $val){
    $temp = explode(',',$val);
    $data[]= [
        'number' => (int) $temp[1],
        'bankTypeName' => $temp[2],
        'bankName' => $temp[3],
    ];
}
$stime=microtime(true);
// 多次测试发现 事务中批量插入 每次插入 100 条性能最好
$data_chunk = array_chunk($data,100);
Db::startTrans();
foreach ($data_chunk as $item)
{
    Db::table('bank_test')->insertAll($item);
}
Db::commit();
$etime=microtime(true);//获取程序执行结束的时间
$total=$etime-$stime;   //计算差值
// 指令输出
$output->writeln("插入:".count($data)."数据，耗时：{$total}");

结果：插入 150792 数据，耗时 5.9146549701691 秒

而我使用 MySQL 5.7 批量插入每次1000 条，都需要耗时 14 秒。

所以我认为 SQLite 插入 15W 条数据，性能能在 5、6 秒，已经是非常可观了。

写同步

可以通过关闭写同步来进一步的提升性能，但这样设置可能会在断电、崩溃等情况造成数据丢失，需要谨慎选择。

具体可参考官方文档：https://www.sqlite.org/pragma.html#pragma_synchronous

Db::query('PRAGMA synchronous = OFF');

在开启事务前关闭写同步，既事务+批量+关闭写同步，插入15w 数据耗时：5.1300809383392 秒。

后记

SQLite 还支持其他方式用来提升插入性能，比如内存模式、执行准备等。但操作都比较复杂，如果只是想简单的提升写入性能，我认为 PHP 使用上面的几种方式配合就可以了。