php操作sqlite3

月未盈 發表於 2021-7-27 23:01:00

距离上次接触sqlite3已经快一年了，去年这篇文章讲跟着菜鸟教程学python操作sqlite3，https://www.cnblogs.com/lizhaoyao/p/13717381.html
现在回头看看，在php的环境下用sqlite3也是小项目值得选择的。
老生常谈的安装
install sqlite3 php扩展以及GUI工具
<div class="cnblogs_code">
<pre>sudo apt-get install sqlite3
sudo apt-get install php7.4-sqlite
sudo apt-get install sqlitebrowser</pre>
</div>
具体代码操作看这里 https://www.runoob.com/sqlite/sqlite-php.html 人家写的很详细了
php官方手册看这里 https://www.php.net/manual/en/book.sqlite3.php
总结出来最重要的也就是这3个方法
<table border="0">
<tbody>
<tr>
<td style="text-align: left">query</td>
<td style="text-align: left">发送查询</td>
</tr>
<tr>
<td style="text-align: left">exec</td>
<td style="text-align: left">更新删除增加修改表结构</td>
</tr>
<tr>
<td style="text-align: left">fetchArray</td>
<td style="text-align: left">获取结果集</td>
</tr>
</tbody>
</table>
 
动手试了试，感觉效率上和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以上了，即使符合条件的记录只有一条也是这样，看了一下因为我使用了这样的代码
<div class="cnblogs_code">
<pre>while($row = $ret->fetchArray(SQLITE3_ASSOC)){
$data[]=$row;
}</pre>
</div>
这样的循环，在最后一次拿不到值的时候会等待很久，不知道是什么问题，还没有深入研究。如下代码可很容易看出问题
<div class="cnblogs_code">
<pre>$row = $ret->fetchArray(SQLITE3_ASSOC);//这一行执行很快
$row = $ret->fetchArray(SQLITE3_ASSOC);//这一行执行很慢因为符合的记录只有一条</pre>
</div>
感觉是如果用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的压力，在重要数据接口上面提高其吞吐量！ 
来源：https://www.cnblogs.com/lizhaoyao/p/15068350.html

頁: [1]

琼殿技术论坛's Archiver

php操作sqlite3