‘壹’ sqlitememory原理
SQLite创建的数据库有一种模式IN-MEMORY,但是它并不表示SQLite就成了一个内存数据库。IN-MEMORY模式可以简单地理解为,(2020 表述勘误:本来创建的数据库文件是基于磁盘的,现在整个文件使用内存空间来代替磁盘空间,没有了文件作为backingstore,不必在修改数据库后将缓存页提交到文件系统),其它操作保持一致。也就是数据库的设计没有根本改变。
inmemory与tempdb是两种节约模式,节约的对象为(rollback)日志文件以及数据库文件,减少IO。inmemory将日志写在内存,并且去除数据库文件作为backingStore,缓存页不用提交到文件系统。tempdb只会在只会在脏的缓存页超过当前总量的25%才会同步刷写到文件,换句话说在临时数据库模式下,事务提交时并不总同步脏页,因此减少了IO数量,事务日志也受这种机制影响,所以在临时数据库模式下,事务日志是不是MEMORY并不重要。回过头来看,内存模式则是临时模式的一种极致,杜绝所有的IO。这两种模式都只能存在一个sqlite3连接,关闭时销毁。
提到内存,许多人就会简单地理解为,内存比磁盘速度快很多,所以内存模式比磁盘模式的数据库速度也快很多,甚至有人望文生意就把它变成等同于内存数据库。
它并不是为内存数据库应用而设计的,本质还是文件数据库。它的数据库存储文件有将近一半的空间是空置的,这是它的B树存储决定的,(2020 勘误:对于固定长度记录,页面使用率最大化,对于非自增计数键的索引,页面一般会保留20~扒袜60%的空间,方便插入)请参看上一篇SQLite存储格式。内春睁激存模式只是将数据库存储文件放入内存空间,但并不考虑最有效管理你的内存空间,其它临时文件也要使用内存,事务回滚日志一样要生成,只是使用了内存空间。它的作用应该偏向于临时性的用途。
(2020 补充:下面的测试有局限性,)
我们先来看一下下面的测试结果,分别往memory和disk模式的sqlite数据库进行1w, 10w以及100w条数据的插入,采用一次性提交事务。另外使用commit_hook捕捉事务提交次数。
(注:测试场景为早袭在新建的数据库做插入操作,所以回滚日志是很小的,并且无需要在插入过程中查找而从数据库加载页面,因此测试也并不全面)
内存模式
磁盘模式
在事务提交前的耗时 (事务提交后的总耗时):
1w 10w 100w
内存模式 0.04s 0.35s 3.60s
磁盘模式 0.06s (0.27s) 0.47s (0.72s) 3.95s (4.62s)
可以看到当操作的数据越少时,内存模式的性能提高得越明显,事务IO的同步时间消耗越显注。
上图还有一组数据比较,就是在单次事务提交中,如果要为每条插入语句准备的话
1w 10w 100w
内存模式 0.19s 1.92s 19.46s
磁盘模式 0.21s (0.35s) 2.06s (2.26s) 19.88s (20.41s)
我们从SQLite的设计来分析,一次插入操作,SQLite到底做了些什么。首先SQLite的数据库操作是以页面大小为单位的。在单条记录插入的事务中,回滚日志文件被创建。在B树中查找目标页面,要读入一些页面,然后将目标页面以及要修改的父级页面写出到回滚日志。操作目标页面的内存映像,插入一条记录,并在页面内重排序(索引排序,无索引做自增计数排序,参看上一篇《SQLite数据库存储格式》)。最后事务提交将修改的页面写出到数据库文件,成功后再删除日志文件。在这过程中显式进行了2次写磁盘(1次写日志文件,1次同步写数据库),还有2次隐式写磁盘(日志文件的创建和删除),这是在操作目录节点。以及为查找加载的页面读操作。更加详细可以参看官方文档的讨论章节《Atomic Commit In SQLite》。
如果假设插入100条记录,每条记录都要提交一次事务就很不划算,所以需要批量操作来减少事务提交次数。假设页面大小为4KB,记录长度在20字节内,每页可放多于200条记录,一次事务提交插入100条记录,假设这100条记录正好能放入到同一页面又没有产生页面分裂,这样就可以在单条记录插入事务的IO开销耗损代价中完成100条记录插入。
当我们的事务中,插入的数据越多,事务的IO代价就会摊得越薄,所以在插入100w条记录的测试结果中,内存模式和磁盘模式的耗时都十分接近。实际应用场合中也很少会需要一次插入100w的数据。有这样的需要就不要考虑SQLite。
(补充说明一下,事务IO指代同步数据库的IO,以及回滚日志的IO,只在本文使用)
除了IO外,还有没有其它地方也影响着性能。那就是语句执行。其实反观一切,都是在对循环进行优化。
for (i = 0; i < repeat; ++i)
{
exec("BEGIN TRANS");
exec("INSERT INTO ...");
exec("END TRANS");
}
批量插入:
exec("BEGIN TRANS");
for (i = 0; i < repeat; ++i)
{
exec("INSERT INTO ...");
}
exec("END TRANS");
当我们展开插入语句的执行
exec("BEGIN TRANS");
for (i = 0; i < repeat; ++i)
{
// unwind exec("INSERT INTO ...");
prepare("INSERT INTO ...");
bind();
step();
finalize();
}
exec("END TRANS");
又发现循环内可以移出部分语句
exec("BEGIN TRANS");
// unwind exec("INSERT INTO ...");
prepare("INSERT INTO ...");
for (i = 0; i < repeat; ++i)
{
bind();
step();
}
finalize();
exec("END TRANS");
这样就得到了批量插入的最终优化模式。
所以对sql语句的分析,编译和释放是直接在损耗CPU,而同步IO则是在饥饿CPU。
请看下图
分别为内存模式1w和10w两组测试,每组测试包括4项测试
1.只编译一条语句,只提交一次事务
2.每次插入编译语句,只提交一次事务
3.只编译一条语句,但使用自动事务。
4.每次插入编译语句,并使用自动事务。
可以看到测试项目4基本上就是测试项目2和测试项目3的结果的和。
测试项目1就是批量插入优化的最终结果。
下面是探讨内存模式的使用:
经过上面的分析,内存模式在批量插入对比磁盘模式提升不是太显注的,请现在开始关注未批量插入的结果。
下面给出的是磁盘模式0.1w和0.2w两组测试,每组测试包括4项测试
可以看到在非批量插入情况,sqlite表现很差要100秒来完成1000次单条插入事务,但绝非sqlite很吃力,因为cpu在空载,IO阻塞了程序。
再来看内存模式20w测试
可以看到sqlite在内存模式,即使在20w次的单条插入事务,其耗时也不太逊于磁盘模式100w插入一次事务。
0.1w 0.2w 20w
内存模式(非批量插入) 15.87s
磁盘模式(非批量插入) 97.4s 198.28s
编译1次插入语句 每次插入编译1次语句
内存模式(20w,20w次事务) 11.10s 15.87s
磁盘模式(100w,1次事务) 4.62s 20.41s