转载

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. 初始状态

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

SQLite3性能深入分析

图片来自 sqlite.org

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

2. 准备读取（加读锁）

SQLite3性能深入分析

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

3. 读取数据

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

SQLite3性能深入分析

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

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

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

SQLite3性能深入分析

5. 建立回滚日志

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

SQLite3性能深入分析

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

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

SQLite3性能深入分析

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

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

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

SQLite3性能深入分析

8. 加互斥锁

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

SQLite3性能深入分析

9. 写数据库文件

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

SQLite3性能深入分析

10. 冲(fsync)数据库文件

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

SQLite3性能深入分析

11. 删除回滚日志

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

SQLite3性能深入分析

12. 释放锁

SQLite3性能深入分析

过程分析

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

一次文件创建(回滚日志)
两次文件写入
两次文件冲入（回滚日志，数据库文件）
一次文件删除(回滚日志)
加了3次锁，最后一次不允许读取

mmap

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

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

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

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

异步IO

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

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);

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可以关闭这两次强制冲文件操作的。

可以通过一下方式关闭：

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)操作，你也可以使其每次事务都进行冲入，以确保数据完全可靠：

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:”打开的数据库。

测试分三种情况：