SQLite3性能深入分析

 

SQLite3是移动终端最常用的数据库，它非常轻量，编译后只有数百KB。但它麻雀虽小，五脏俱全，它可以支持多线程，支持事务、约束以及几乎所有的SQL常见特性。iOS中很多App经常会使用到SQLite，在使用SQLite的时候经常会遇到其性能问题。本文将深入SQLite内部实现，分析其性能优化途径。

一些基本概念

在开始分析之前，首先需要了解一下数据库的基本知识。

什么是ACID？

这个术语在数据库设计者非常熟悉的，而使用者往往不关注这些。ACID是“Atomicity, Consistency, Isolation, Durability”英文的缩写，它用来确保一个数据库事务的可靠性。中文意思是“原子性，一致性，隔离性，持久性”。维基百科上有其定义解释：

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：当两个或者多个事务并发访问（此处访问指查询和修改的操作）数据库的同一数据时所表现出的相互关系。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性：在事务完成以后，该事务对数据库所作的更改便持久地保存在数据库之中，并且是完全的。

SQLite3是符合ACID的，它保证了即使在程序Crash或者进程被杀，甚至是内核崩溃或者断电的情况下，数据库依然是完整的。既要维持ACID特性，同时要保证性能最大化，这是数据库设计上的一大挑战。

SQLite3中的事务

SQLite3中可以使用BEGIN TRANSACTION和COMMIT TRANSACTION来开始和结束一个事务。如果你没有添加这些事务语句，SQLite3会为你的每条SQL语句加上一个事务。

一次正常执行的事务过程

要想优化SQLite3的性能，那么必须要了解SQLite3中一次事务执行过程。这里已一个有写操作的并成功执行的事务来举例。

1. 初始状态

数据库打开后，未进行任何数据库操作时大概是下图的状态。

image

图片来自sqlite.org

这里分了三个部分，左面是用户空间，中间是内核缓存区（文件的读写缓存），右边是物理磁盘设备（iOS的闪存）。在SQLite中数据最小的读写单位扇区（sector），通常是512B，图中每个小矩形代表一个扇区。蓝色代表未更改的原始数据，中间白色表示是空的，即此时数据没有读取到内核缓存区。

2. 准备读取（加读锁）

image

任何写操作都会先进行读操作，因为写之前要读取数据库的schema，插入和修改的位置等。在读取操作之间要加上读锁。加读锁是为了防止其它数据库连接进行写操作，而保证读取时数据不被破坏。这时其它数据库的读取操作依然可以正常执行。

3. 读取数据

加了读锁之后就开始读取数据了：

image

这里读取了3个扇区的数据，读取时通过系统文件读取调用，会从内核缓存中拷贝到用户空间。（疑问：能否直接读取到用户空间？）

4. 准备修改数据（加写锁）

数据读取完毕后，就准备开始修改数据了，修改数据之前首先要加写锁，此写锁可以和其它进程的读锁同时存在：

image

5. 建立回滚日志

开始写操作之前，先建立一个回滚日志文件，已便进行回滚操作。将更改之前的旧数据保存到回滚日志文件中。

image

回滚日志文件包含一个头信息（绿色部分），记录回滚必要信息。

6. 在用户空间中修改数据

image

图中粉色表示已修改的数据。

7. 冲(fsync)回滚日志文件

用户空间修改数据后，未确保回滚日志文件可靠，必须把回滚日志文件冲入物理磁盘进行持久存储。这样以确保内核崩溃或断电后依然可恢复数据。

image

8. 加互斥锁

准备开始真正的写文件了，要加互斥锁了。互斥锁可以和已经打开的读锁同时存在，但不允许新建读锁了。

image

9. 写数据库文件

现在可以安全的写数据文件了。

image

10. 冲(fsync)数据库文件

冲数据库文件到持久存储设备。

image

11. 删除回滚日志

冲入数据库文件后才能删除回滚日志，确保内核崩溃或断电后依然可恢复数据。

image

12. 释放锁

image

过程分析

为确保ACID，一次数据库事务竟需要这么多步骤。下面对此过程进行分析一下：

• 一次文件创建(回滚日志)

• 两次文件写入

• 两次文件冲入（回滚日志，数据库文件）

• 一次文件删除(回滚日志)

• 加了3次锁，最后一次不允许读取

mmap

在读取和写入过程中，每次都将用户空间的数据和内核空间的数据拷贝一次，能否直接将文件读取到用户空间？SQLite3提供了mmap方式的IO。

打开mmap方式的IO只要执行下面语句即可：

1	sqlite3_exec(db, "PRAGMA mmap_size=268435456;", NULL, NULL, NULL);

理论上mmap方式能减少内核和用户空间的IO，但在iOS系统中，这个从我这里测试效果看，影响并不大。

异步IO

在事务操作中有大量写操作，能否将写操作放到后台线程执行？SQLite3是支持这种的，SQLite可以自定义文件的读取、写入等操作方式。需要配置一下VFS结构即可：

1 2 3 4 5 6 7 8

struct sqlite3_vfs {   int iVersion;            /* Structure version number (currently 3) */   int szOsFile;            /* Size of subclassed sqlite3_file */   int mxPathname;          /* Maximum file pathname length */   sqlite3_vfs *pNext;      /* Next registered VFS */   const char *zName;       /* Name of this virtual file system */   void *pAppData;          /* Pointer to application-specific data */   int (*xOpen)(sqlite3_vfs*, const char *zName, sqlite3_file*,

int flags, int *pOutFlags);

1 2 3 4 5 6 7 8 9 10 11

int (*xDelete)(sqlite3_vfs*, const char *zName, int syncDir); int (*xAccess)(sqlite3_vfs*, const char *zName, int flags, int *pResOut); int (*xFullPathname)(sqlite3_vfs*, const char *zName, int nOut, char *zOut); void *(*xDlOpen)(sqlite3_vfs*, const char *zFilename); void (*xDlError)(sqlite3_vfs*, int nByte, char *zErrMsg); void (*(*xDlSym)(sqlite3_vfs*,void*, const char *zSymbol))(void); void (*xDlClose)(sqlite3_vfs*, void*); int (*xRandomness)(sqlite3_vfs*, int nByte, char *zOut); int (*xSleep)(sqlite3_vfs*, int microseconds); int (*xCurrentTime)(sqlite3_vfs*, double*); int (*xGetLastError)(sqlite3_vfs*, int, char *);

这个结构定义了各种文件操作的函数指针。开启异步IO需要实现这个结构的定制，官方已提供了这个扩展，下载后加入工程即可：

http://www.sqlite.org/src/tree?name=ext/async

这个我测试了一下，性能提升并不明显，虽然做了异步，但主线程获取锁的等待时间增加太多，实际性能影响不大。

关闭冲文件

事务过程中有两次冲文件操作，能否将这两次冲文件关闭？SQLite可以关闭这两次强制冲文件操作的。

可以通过一下方式关闭：

1	sqlite3_exec(db, "PRAGMA synchronous=OFF;", NULL, NULL, NULL);

关闭冲文件确实提高了不少性能，但在内核崩溃或者系统断电时导致数据库写入不完整。即使如此，程序本身Crash还是安全的。

Write-Ahead Logging (WAL) 模式

SQLite3.7.0中新增了WAL模式，iOS大概在5.0中引入此支持。WAL模式正好和传统模式相反，WAL模式会将修改的数据单独写到一个WAL文件中，而且多个事务可以共用一个WAL文件。

checkpoint

WAL模式里有一个重要概念checkpoint。每个checkpoint默认是1000扇区的数据，此值可动态调整。当WAL文件里的数据更改量达到checkpoint时才会将WAL里的数据写回实际的数据库。

WAL冲入优化

WAL默认在checkpoint时进行文件冲入(fsync)操作，你也可以使其每次事务都进行冲入，以确保数据完全可靠：

1	sqlite3_exec(db, "PRAGMA synchronous=FULL;", NULL, NULL, NULL);

WAL并发优化

WAL模式会在共享内存中根据数据顺序建立索引，每个读操作都会记录一下最新的数据更改索引，读操作只会读取此索引之前的数据，而写操作可以继续在WAL中追加数据，并发性能有一定提升。

那么WAL模式下相比传统模式有以下改进：

• 一次文件创建(回滚日志) —> 仅在第一次和达到checkpoint时创建一次文件

• 两次文件写入 －> 大部分情况下只有WAL文件一次写入

• 两次文件冲入（回滚日志，数据库文件）－> FULL模式下有一次冲入，Normal模式下仅在checkpoint时冲入

• 一次文件删除(回滚日志) －> 大部分情况下不用删除

• 加了3次锁，最后一次不允许读取 －>

当然WAL模式也有一些缺点：

• 当每个事务数据量比较大时，接近或超过1000页的数据量时，会导致WAL内容频繁同步至实际数据库文件，导致性能下降。

• WAL在并发性方面的优化使用了系统共享内存，那么在一些网络文件系统中就无法使用。iOS目前并不存在这种文件系统。

各种模式性能实战分析

下面我对各种模式进行测试，各模式如下：

• 正常模式：正常创建的数据库，不做任何配置。

• 内存映射：使用语句“PRAGMA mmap_size=268435456;”开启内存映射。

• 异步IO：加入官方异步IO扩展并开启。

• WAL模式：使用语句“PRAGMA journal_mode=WAL;”开启WAL模式。

• sync OFF：使用语句“PRAGMA synchronous=OFF;”关闭文件强制同步。

• WAL(sync Full)：使用语句“PRAGMA journal_mode=WAL;PRAGMA synchronous=FULL;”开启WAL模式和全同步。

• 内存数据库：使用特殊文件名“:memory:”打开的数据库。

测试分三种情况：

• 1000条小数据写入

• 1000条小数据写入（合并为一个事务）

• 100条大数据（474940 Bytes）写入

测试机型为iPhone4，系统为iOS7.1。

1000条小数据写入

image

小数据WAL存在明显优势，关闭fsync的表现也不赖，但不完全可靠了，不过却还没有WAL（sync Full）模式快。

1000条小数据写入（合并为一个事务）

image

合并为一个事务后，各模式差别不大。因为IO次数有限。相比不合并事务，性能急剧提升到100毫秒级，请注意本图的单位。

100条大数据（474940 Bytes）写入

image

100条大数据时WAL模式性能相比其它模式都差一些，单个事务数据量比较大的情况不推荐WAL，或者要修改WAL的checkpoint设置，改的更大一些，以免产生过多的checkpoint。

结论

• 异步IO似乎并不能提高多少性能，官方已经deprecate它了，推荐使用WAL模式。

• 大量小记录写入（不合并为事务）时，一般模式即使关闭文件sync，还没有WAL全sync模式快。

• 总体数据占用量少的，而且可重建恢复的数据，建议使用内存数据库，必要时做备份到闪存文件。

• 总体数据占用量大，但是可重建恢复的数据库，可以关闭synchronous以提高性能。

• 操作频繁，单条记录数据量小的，建议使用WAL模式。

• 操作少，单条记录数据量大，建议使用一般数据库，不要使用WAL模式。

 

 

 

原文地址:http://www.cocoachina.com/ios/20161110/18030.html