MENU

ThinkPHP 快速插入 SQLite

2022 年 01 月 15 日 • 阅读: 12 • PHP,编码

因为 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 使用 上面的几种方式配合就可以了。