SQLite性能 - inmemory模式。
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 |
不能给出内存模式100w次事务的测试结果是因为程序运行出问题。
在100w的插入一次性事务测试结果，内存模式和磁盘模式相差不到1秒，这1秒就是最后大量数据库同步到数据库的IO时间。
再回到上面两图两表的测试结果，磁盘模式在执行多事务显得偏瘫，每秒不多于10个单条插入的事务。而内存模式下执行事务的能力仍然坚挺，每秒1w次单条插入的事务也不在话下。
在实际应用中，数据随机实时，你又不想做批量插入控制，就可以考虑用内存模式将现有的数据马上用事务提交，不管事务提交的数据是多是少。你只要定制计划，将内存模式的数据库同步到你的外部数据库。因为每个内存模式的数据库是独立的，你同步一个内存模式的数据库到外部的期间，就可以同时使用另一个内存模式的数据库缓冲数据。
（上面删除段落是根据MinGW系统的测试结果。在真机环境测试了win 7 32bit和win 7 64bit，以及在它们之上使用mingw系统，测试结果是sqlite处理1000个单条插入事务总耗时在100秒级别。而在vm环境，vm虚拟磁盘上测试了xp，linux和macosx，测试结果是sqlite处理1000个单条插入事务总耗时在10秒级别内。值得注意的是，vm虚拟磁盘不是直接操作磁盘，所以我还要另找磁盘，挂接真实的磁盘对虚拟机环境进行测试。）
更多磁盘模式的测试结果在下一篇《意想不到，但又情理之中的测试结果》。
在经过慎重考虑后，在linux和mac环境下进行了测试，验证了一句“数据库都构建在痛苦的操作系统之上”。上面的测试环境是MinGw，痛苦的不是windows而是在windows之上加上的一层MinGw系统，磁盘操作十分痛苦。根据在linux和mac环境的测试结果，内存模式和磁盘模式在单条插入自动事务的性能更加接近，相差只有10倍左右，由于不用在MinGW这样的适配系统痛苦地操作磁盘，所以在其它批量插入事务的测试项目中，两种模式的测试结果更加趋于接近。
至于你想用sqlite的内存模式作持久用途或者去媲美内存数据库，可能不是正确的选择。sqlite是一个体积轻巧，可以帮你管理关系型数据的嵌入式数据库。它适应嵌入式的空间小，耗电低和占用内存有限的特殊环境。它的高效是不因为它的简单，而在基本的数据库查询功能上有打折扣。它在设计上有针对性的取舍，使它更适合某些应用场合，也必然在舍的部分蹩足。
本篇至此结束，谢谢观看。
后续会有":memory:"，"file:whereIsDb?mode=memory"以及"disk.db"这三种模式的对比。
测试代码在https://github.com/bbqz007/xw/test.sqlite.in-memory.zip。(不支持VC，需要自行下载编译sqlite。支持VC编译的测试代码未上传。)
mingw测试插入1000条数据使用自动事务，即一共提交1000次事务：
运行在 总耗时
xp (vm11) 9s
win 7 64bit 200s
win 7 32bit 100s
最后补上Linux (vm11)和MacOSX (vm11)的测试结果：
Linux 2.6.32-358.el6.x86_64 cpu MHz : 3591.699 cpu MHz : 3591.699 ----- 10000 in memory ---- repeat insert 10000 times, in 1 trans, with 1 stmt prepared 0.04s 0.04s commit: 1 repeat insert 10000 times, in 1 trans, with each stmt prepared 0.06s 0.06s commit: 1 repeat insert 10000 times, in auto trans(s), with 1 stmt prepared 0.02s 0.02s commit: 10000 repeat insert 10000 times, in auto trans(s), with each stmt prepared 0.06s 0.06s commit: 10000 ---- 100000 in memory ---- repeat insert 100000 times, in 1 trans, with 1 stmt prepared 0.11s 0.11s commit: 1 repeat insert 100000 times, in 1 trans, with each stmt prepared 0.40s 0.40s commit: 1 repeat insert 100000 times, in auto trans(s), with 1 stmt prepared 1.28s 1.28s commit: 100000 repeat insert 100000 times, in auto trans(s), with each stmt prepared 1.76s 1.76s commit: 100000 ---- 200000 in memory ---- repeat insert 200000 times, in 1 trans, with 1 stmt prepared 0.23s 0.23s commit: 1 repeat insert 200000 times, in 1 trans, with each stmt prepared 0.87s 0.87s commit: 1 repeat insert 200000 times, in auto trans(s), with 1 stmt prepared 7.35s 7.35s commit: 200000 repeat insert 200000 times, in auto trans(s), with each stmt prepared 9.10s 9.10s commit: 200000 --- 1000000 in memory ---- repeat insert 1000000 times, in 1 trans, with 1 stmt prepared 1.23s 1.23s commit: 1 repeat insert 1000000 times, in 1 trans, with each stmt prepared 4.39s 4.39s commit: 1 ------ 1000 in disk ---- rm: 无法删除"test.db": 没有那个文件或目录 repeat insert 1000 times, in 1 trans, with 1 stmt prepared 0.00s 0.00s commit: 1 repeat insert 1000 times, in 1 trans, with each stmt prepared 0.00s 0.00s commit: 1 repeat insert 1000 times, in auto trans(s), with 1 stmt prepared 0.80s 0.80s commit: 1000 repeat insert 1000 times, in auto trans(s), with each stmt prepared 0.87s 0.87s commit: 1000 ------ 2000 in disk ---- repeat insert 2000 times, in 1 trans, with 1 stmt prepared 0.00s 0.00s commit: 1 repeat insert 2000 times, in 1 trans, with each stmt prepared 0.01s 0.02s commit: 1 repeat insert 2000 times, in auto trans(s), with 1 stmt prepared 1.60s 1.60s commit: 2000 repeat insert 2000 times, in auto trans(s), with each stmt prepared 2.27s 2.27s commit: 2000 ----- 10000 in disk ---- repeat insert 10000 times, in 1 trans, with 1 stmt prepared 0.01s 0.02s commit: 1 repeat insert 10000 times, in 1 trans, with each stmt prepared 0.04s 0.04s commit: 1 ---- 100000 in disk ---- repeat insert 100000 times, in 1 trans, with 1 stmt prepared 0.11s 0.11s commit: 1 repeat insert 100000 times, in 1 trans, with each stmt prepared 0.45s 0.45s commit: 1 --- 1000000 in disk ---- repeat insert 1000000 times, in 1 trans, with 1 stmt prepared 1.27s 1.34s commit: 1 repeat insert 1000000 times, in 1 trans, with each stmt prepared 4.51s 4.57s commit: 1
MacOSX的测试结果：
