php操作sqlite3

2024-04-07 08:19•php•阅读 3985

距离上次接触sqlite3已经快一年了，去年这篇文章讲跟着菜鸟教程学python操作sqlite3，https://www.cnblogs.com/lizhaoyao/p/13717381.html

现在回头看看，在php的环境下用sqlite3也是小项目值得选择的。

老生常谈的安装

install sqlite3 php扩展以及GUI工具

sudo apt-get install sqlite3
sudo apt-get install php7.4-sqlite
sudo apt-get install sqlitebrowser

具体代码操作看这里 https://www.runoob.com/sqlite/sqlite-php.html 人家写的很详细了

总结出来最重要的也就是这3个方法

动手试了试，感觉效率上和mysql读差不多，但是写入可能不太适合并发，毕竟这东西的锁颗粒度太大了。测试了一下写入速度

php sqlite3 insert

1. 不关闭 autocommit每条SQL都自动提交的话

　　1.1 单条插入每秒大概 72 条

　　1.2 insert values 多值 value 放 1000 个每秒大概 43489 左右

2. 先关闭 autocommit 最后结束再 autocommit

　　2.1 单条插入每秒大概 158000 条 (100万条数据在6秒左右插入完成)

　　2.2 insert values 多值 value 放 1000 个每秒大概 264340 左右 (100万条数据在3.78秒左右插入完成)

问题是 sqlite 是文件锁整个数据库文件会被锁住的适合单机非并发服务器

分析得出可以使用 insert value 多值的方式配合 begin commit 这种方式手动提交能让sqlite3迅速插入上百万数据

$db->exec('BEGIN');

$ret = $db->exec($sql);

$db->exec('COMMIT');

查询的话，这里数据库放了1000万条记录，如果用id查询那很快的，基本上0.3ms左右，如果是其他字段查询的话，立刻就上400ms以上了，即使符合条件的记录只有一条也是这样，看了一下因为我使用了这样的代码

while($row = $ret->fetchArray(SQLITE3_ASSOC)){
    $data[]=$row;
}

这样的循环，在最后一次拿不到值的时候会等待很久，不知道是什么问题，还没有深入研究。如下代码可很容易看出问题

$row = $ret->fetchArray(SQLITE3_ASSOC);//这一行执行很快
$row = $ret->fetchArray(SQLITE3_ASSOC);//这一行执行很慢 因为符合的记录只有一条

感觉是如果用ID查，它能知道有多少行记录，如果不是ID，它不知道有多少记录，其实query的时间并不多，但是取结果集的地方就很慢（特别是第二次取结果集），就一直等。

为什么用while这样的方式也是没有办法，它没提供一个类似 result_num这样的方法不知道查询出来的结果集有多少行数据，只能一行行读出来，不得不说这样的话对效率确实堪忧。

总体来说，如果你的逻辑不那么复杂，而且你希望得到一个效率还不错但是读多写少，能为mysql分忧解难的场景解决方案，那用这个还是不错的。

想了一下可以这样设计今后的数据落地方案

1.数据量很小，但是很碎，可以用 xattr 扩展属性(4000字符左右)

2.数据量有，但是还是希望能快速，可以用file json_encode(igbinary_serialize)等方式（适合10000条数据以下的场景）

3.数据量上来了(超过10万)，但是还是希望快速，少占用mysql资源，可以采用sqlite3(高速写入读取，读多写少，相当于数据缓存)。我们可以定义和mysql一样的数据库结构，然后在上线前同步数据进来，相当于缓存预热，作为临时存储仓库

4.数据量非常大(超过百万)，真的需要永久存储落地，采用mysql方式存储，这里面有成熟的关系型数据库的各种方案。

其实本质上来说 sqlite3 也是读写文件方式的，只不过有一层接口支持规范化并实现了SQL模型，在项目初期或者某些边缘业务用这个性价比很高的(只需要写一个很基础的sqlite3操作类就相当于有了model层)。

我们不要小看了php操作文件数据的速度，其读取和写入都是非常高效率的(不然文件缓存为什么那么好用呢？)，当然随着文件增大，数据变多，其效率会越来越慢，所以建议控制文件大小在5M左右(和机器性能有关系)，条数控制在10000条内。

就实际业务而言，中小企业的数据大部分表的数据是不会破万，破十万的，只有少部分重要的表会增长到百万，所以用这个能迅速降低mysql的压力，在重要数据接口上面提高其吞吐量！