sqlite事务模型性能优化tips常见误区

共计 10627 个字符，预计需要花费 27 分钟才能阅读完成。

本文主要介绍 sqlite 的事务模型，以及基于事务模型的一些性能优化 tips，包括事务封装、WAL+ 读写分离、分库分表、page size 优化等。并基于手淘 sqlite 的使用现状总结了部分常见问题及误区，主要集中在多线程的设置、多线程下性能优化的误区等。本文先提出以下几个问题（作者在进行统一存储的关系存储框架优化过程中一直困惑的问题，同时也是客户端开发者经常搞错的问题）并在正文中进行解答：

1，sqlite 的多进程安全是怎么实现的？性能如何？
2，sqlite 的数据库连接是什么？
3，言 sqlite 必提的读写分离，具体指什么？能不能提升数据读写的性能？为什么
4，sqlite 提供的 WAL 特性解决了什么问题？
5，sqlite 的多线程设置是为了解决什么问题？与读写分离有什么关系？
6，什么情况下数据库会发生死锁？
7，有哪些性能优化的方案？

在深入了解 sqlite 之前，最好先对 sqlite 的主要数据结构有个概要的理解，sqlite 是一个非常完备的关系数据库系统，由很多部分组成（parser,tokenize,virtual machine 等等），同时 sqlite 的事务模型相对简化，是入门学习关系数据库方法论的一个不错的选择；下文对事务模型的分析也基于这些核心数据结构。下面这张图比较准确的描述了 sqlite 的几个核心数据结构：

connection 通过 sqlite3_open 函数打开，代表一个独立的事务环境（这里及下文提到的事务，包括显式声明的事务，也包括隐式的事务，即每条独立的 sql 语句）

B-Tree 负责请求 pager 从 disk 读取数据，然后把页面 (page) 加载到页面缓冲区(page cache)

Pager 负责读写数据库，管理内存缓存和页面（即下文提到的 page caches），以及管理事务，锁和崩溃恢复

关于建议锁（advisory lock）和强制锁（mandatory lock）
- 建议锁并不由内核强制实行，如果有进程不检查目标文件是否已经由别的进程加了锁就往其中写入数据，内核也不会加以阻拦。因此，建议锁并不能阻止进程对文件的访问，而是需要进程事先对锁的状态做一个约定，并根据锁的当前状态和相互关系来确定其他进程是否能对文件执行指定的操作
- 强制锁是由内核强制采用的文件锁——由于内核对每个 read()和 write()操作都会检查相应的锁，会降低系统性能
典型的建议锁
- 锁文件；锁文件是最简单的对文件加锁的方法，每个需要加锁的数据文件都有一个锁文件（lock file）。但这种方式存在比较大的问题是无法强制保护需要加锁的文件，并且当加锁进程非正常退出之后，会造成其他进程的死锁
- 记录锁；System V 和 BSD4.3 引入了记录锁，相应的系统调用为 lockf()和 flock()。而 POSIX 对于记录锁提供了另外一种机制，其系统调用为 fcntl()。记录锁和锁文件有两个很重要的区别：1）记录锁可以对文件的任何一部分加锁，这对 DBMS 有极大的帮助，2）记录锁的另一个优点就是它由进程持有，而不是文件系统持有，当进程结束时，所有的锁也随之释放。对于一个进程本身而言，多个锁绝不会冲突。（Windows 中的锁都是强制锁，具体不是很熟，只知道在由于 windows 上文锁的限制，sqlite 多进程下的并发性会受影响）

sqlite 的文件锁在 linux/posix 上基于记录锁实现，也就是说 sqlite 在文件锁上会有以下几个特点：

多进程使用安全，且不会因为进程异常退出引发死锁
单进程使用性能几乎不会受损，多进程使用的性能损耗会受一定的影响

sqlite 对每个连接设计了五钟锁的状态(UNLOCKED, PENDING, SHARED, RESERVED, EXCLUSIVE), sqlite 的事务模型中通过锁的状态保证读写事务（包括显式的事务和隐式的事务）的一致性和读写安全。sqlite 官方提供的事务生命周期如下图所示，我在这里稍微加了一些个人的理解：

这里有几点需要注意：

UNLOCKED、PENDING、SHARED、RESERVED 状态是非独占的，也就是说同一个连接中多个线程并发只读不会被阻塞。
写操作的数据修改会先写入 page cache，内容包括 journal 日志、b-tree 的修改等；正是由于 page cache 的存在，很多耗时的“重”操作都可以不干扰其他连接和当前连接的读操作，真正意义上保证了 sqlite 可以同时处理一个写连接和多个读连接。
连接由 RESERVED 状态进入 EXCLUSIVE 状态，需要等待读线程释放 SHARED 锁，也即写操作会被读操作阻塞
连接由 RESERVED 状态进入 EXCLUSIVE 状态后（显式或隐式的调用 commit），数据库进入独占状态，其他任何连接都无法由 UNLOCK 状态进入 SHARED 状态；也即写操作会阻塞所有连接的读操作（不包括已经进入 SHARED 状态的操作），直到 page caches 写入数据库文件（成功或失败）。
数据库独占状态越久，其他操作的等待时间越久，即 SQLITE_BUSY 产生的一个原因

对于常规的事务模型（without WAL），读写（连接）分离，不同连接或同一个连接上的读和写操作仍互相阻塞，对性能提升没有明显帮助
写事务在拿到 reserve 锁之前在 page cache 里的操作不会影响其他连接的读写，所以使用事务进行批量数据的更新操作有非常大的性能优势
事务模型存在死锁的场景，如下图所示：

按照官方文档，WAL 的原理如下：

对数据库修改是是写入到 WAL 文件里的，这些写是可以并发的（WAL 文件锁）。所以并不会阻塞其语句读原始的数据库文件。当 WAL 文件到达一定的量级时(CheckPoint)，自动把 WAL 文件的内容写入到数据库文件中。当一个连接尝试读数据库的时候，首先记录下来当前 WAL 文件的末尾 end mark，然后，先尝试在 WAL 文件里查找对应的 Page，通过 WAL-Index 来对查找加速（放在共享内存里，.shm 文件），如果找不到再查找数据库文件。

这里结合源码，有下面几个理解：

数据的写操作写入 WAL 的过程不再需要 SHARED 锁、EXCLUSIVE 锁，而是需要 WAL 文件锁
数据的写操作不会被读操作阻塞（写操作不再需要 SHARED 锁）
数据的读操作不会被写操作阻塞（写操作不再需要独占数据库）
WAL 文件写入数据库文件的过程，依然会被读操作阻塞，也会阻塞读操作
WAL 文件的大小设置很关键，过大的 WAL 文件，会让查找操作从 B -Tree 查找退化成线性查找(WAL 中 page 连续存储)；但大的 WAL 文件对写操作较友好。

只有开了 WAL，再使用读写（连接）分离才能有较大的性能提升
WAL 本质上是将部分随机写操作（数据库文件和 journal 日志）变成了串行写 WAL 文件，并进行了锁分离
WAL 文件的大小设置很关键，过大的 WAL 文件，会让查找操作从 B -Tree 查找退化成线性查找(WAL 中 page 连续存储)；但大的 WAL 文件对写操作较友好

多线程是 sqlite 使用过程中比较容易误解的一个概念，带来的问题要么是产生各种线程安全问题，要么是无法充分发掘 sqlite 的性能，这里结合代码我们简单分析一下并给出几个重要结论。
线程安全设置主要在设置 bCoreMutex 和 bFullMutex，启用 bFullMutex 之后数据库连接和 prepared statement 都已加锁（社区各种文档都到此为止）；但还是感觉不够清晰：这两个锁会对我们使用 sqlite 有怎样的影响？best practice 又是什么？

// 多线程的设置的实现：设置 bCoreMutex 和 bFullMutex

#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0  /* IMP: R-54466-46756 */
    case SQLITE_CONFIG_SINGLETHREAD: {
      /* EVIDENCE-OF: R-02748-19096 This option sets the threading mode to
      ** Single-thread. */
      sqlite3GlobalConfig.bCoreMutex = 0;  /* Disable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 0;  /* Disable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-20520-54086 */
    case SQLITE_CONFIG_MULTITHREAD: {
      /* EVIDENCE-OF: R-14374-42468 This option sets the threading mode to
      ** Multi-thread. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 0;  /* Disable mutex on connections */
      break;
    }
#endif
#if defined(SQLITE_THREADSAFE) && SQLITE_THREADSAFE>0 /* IMP: R-59593-21810 */
    case SQLITE_CONFIG_SERIALIZED: {
      /* EVIDENCE-OF: R-41220-51800 This option sets the threading mode to
      ** Serialized. */
      sqlite3GlobalConfig.bCoreMutex = 1;  /* Enable mutex on core */
      sqlite3GlobalConfig.bFullMutex = 1;  /* Enable mutex on connections */
      break;
    }
#endif

如果 FullMutex 打开，则每个数据库连接会初始化一个互斥量成员（db->mutex），也就是社区各种文档上所说的“bFullMutex 是对连接的线程保护”

if(isThreadsafe){    // bFullMutex = 1
    db->mutex = sqlite3MutexAlloc(SQLITE_MUTEX_RECURSIVE);    // 每个数据库连接会初始化一个成员锁
    if(db->mutex==0){sqlite3_free(db);
      db = 0;
      goto opendb_out;
    }
  }

如果 CoreMutex 打开，则会设置全局的锁控制函数

/* If the xMutexAlloc method has not been set, then the user did not
    ** install a mutex implementation via sqlite3_config() prior to 
    ** sqlite3_initialize() being called. This block copies pointers to
    ** the default implementation into the sqlite3GlobalConfig structure.
    */
    sqlite3_mutex_methods const *pFrom;
    sqlite3_mutex_methods *pTo = &sqlite3GlobalConfig.mutex;

    if(sqlite3GlobalConfig.bCoreMutex){pFrom = sqlite3DefaultMutex();
    }else{pFrom = sqlite3NoopMutex();
    }
    pTo->xMutexInit = pFrom->xMutexInit;
    pTo->xMutexEnd = pFrom->xMutexEnd;
    pTo->xMutexFree = pFrom->xMutexFree;
    pTo->xMutexEnter = pFrom->xMutexEnter;
    pTo->xMutexTry = pFrom->xMutexTry;
    pTo->xMutexLeave = pFrom->xMutexLeave;
    pTo->xMutexHeld = pFrom->xMutexHeld;
    pTo->xMutexNotheld = pFrom->xMutexNotheld;
    sqlite3MemoryBarrier();
    pTo->xMutexAlloc = pFrom->xMutexAlloc;

而 CoreMutext 未打开的话，sqlite3NoopMutex()的实现如下（CoreMutext 未打开的话，对应使用的锁函数均为空实现）：

sqlite3_mutex_methods const *sqlite3NoopMutex(void){
  static const sqlite3_mutex_methods sMutex = {
    noopMutexInit,
    noopMutexEnd,
    noopMutexAlloc,
    noopMutexFree,
    noopMutexEnter,
    noopMutexTry,
    noopMutexLeave,
    0,
    0,
  };

  return &sMutex;
}

// CoreMutext 未打开的话，对应使用的锁函数均为空实现
static int noopMutexInit(void){return SQLITE_OK;}
static int noopMutexEnd(void){return SQLITE_OK;}
static sqlite3_mutex *noopMutexAlloc(int id){UNUSED_PARAMETER(id);
  return (sqlite3_mutex*)8; 
}
static void noopMutexFree(sqlite3_mutex *p){UNUSED_PARAMETER(p); return; }
static void noopMutexEnter(sqlite3_mutex *p){UNUSED_PARAMETER(p); return; }
static int noopMutexTry(sqlite3_mutex *p){UNUSED_PARAMETER(p);
  return SQLITE_OK;
}
static void noopMutexLeave(sqlite3_mutex *p){UNUSED_PARAMETER(p); return; }

FullMutex 保护了什么？

粗略看了一下，通过 db->mutex(sqlite3_mutex_enter(db->mutex);)保护的逻辑块和函数主要如下列表：

sqlite3_db_status、sqlite3_finalize、sqlite3_reset、sqlite3_step、sqlite3_exec、sqlite3_preppare_v2、column_name、blob 操作、sqlite3Close、sqlite3_errmsg...

基本覆盖了所有的读、写、DDL、DML，也包括 prepared statement 操作；也就是说，在未打开 FullMutex 的情况下，在一个连接上的所有 DB 操作必须严格串行执行，包括只读操作。

CoreMutex 保护了什么？

sqlite3 中的 mutex 操作函数，除了用于操作 db->mutex 这个成员之外，还主要用于以下逻辑块（主要是影响数据库所有连接的逻辑）：

shm 操作(index for wal)、内存池操作、内存缓存操作等

多线程设置是决定 DDL、DML、WAL(包括 SHM)操作是否线程安全的设置
多线程设置与读写（连接）分离没有任何关系，并不是实现读写（连接）分离的必要条件（很多人对这一点有误解）

由 #2.2 的分析可知，写操作会在 RESERVED 状态下将数据更改、b-tree 的更改、日志等写入 page cache，并最终 flush 到数据库文件中；使用事务的话，只需要一次对 DB 文件的 flush 操作，同时也不会对其他连接的读写操作阻塞；对比以下两种数据写入方式(这里以统一存储提供的 API 为例)，实测耗时有十几倍的差距（当然对于频繁的读操作，使用事务可以减事务状态的切换，也会有一点点性能提升）：

// batch insert in transaction with 1000000 records
//
AliDBExecResult* execResult = NULL;
_database->InTransaction([&]() -> bool {    // in transaction
  auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)");
  for (auto record : records) {    // bind 1000000 records
    // bind record
    ...
    ...
    statement->AddBatch();}
  auto result = statement->ExecuteUpdate();
  return result->is_success_;
});

// batch insert with 1000000 records, no transaction
//
auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)");
for (auto record : records) {    // bind 1000000 records
  // bind record
  ...
  ...
  statement->ExecuteUpdate();}

启用 WAL 之后，数据库大部分写操作变成了串行写（对 WAL 文件的串行操作），对写入性能提升有非常大的帮助；同时读写操作可以互相完全不阻塞（如 #2.3 所述）。上述两点比较好的解释了启用 WAL 带来的提升；同时推荐一个写连接 + 多个读连接的模型，如下图所示：

读操作使用不同的连接并发执行，可以完全避免由于显式事务、写操作之间的锁竞争带来的死锁
所有的写操作、显式事务操作都使用同一个连接，且所有的写操作、显式事务操作都串行执行
- 可以完全避免由于显式事务、写操作之间的锁竞争带来的死锁，如 #2.2.1 提到的死锁的例子
- 并发写并不能有效的提高写入效率，参考如下伪代码，哪段执行更快？

// two transactions: 
void Transaction_1() {connection_->Exec("BEGIN");
      connection_->Exec("insert into table(value) values('xxxx')");
      connection_->Exec("COMMIT");
}

void Transaction_2() {connection_->Exec("BEGIN");
      connection_->Exec("insert into table(value) values('xxxx')");
      connection_->Exec("COMMIT");
}

// code fragment 1: concurrent transaction
thread1.RunBlock([]() -> void {for (int i=0; i< 100000; i++) {Transaction_1();
    }
});

thread2.RunBlock([]() -> void {for (int i=0; i< 100000; i++) {Transaction_2();
    }
});

thread1.Join(); thread2.join();

// code fragment 2: serial transaction
for (int i=0; i< 100000; i++) {Transaction_1();
}
for (int i=0; i< 100000; i++) {Transaction_2();
}

如 #2.3 提到，过大的 WAL 文件，会让查找操作从 B -Tree 查找退化成线性查找(WAL 中 page 连续存储)；但大的 WAL 文件对写操作较友好。对于大记录的写入操作，较大的 wal size 会有效提高写入效率，同时不会影响查询效率

分库分表可以有效提高数据操作的并发度；但同时过多的表会影响数据库文件的加载速度。现在数据库方向的很多研究包括 Auto sharding, paxos consensus, 存储和计算的分离等；Auto
application-awared optimization，Auto hardware-awared optimization，machine
learning based optimization 也是不错的方向。

包括 WAL checkpoint 策略、WAL size 优化、page size 优化等，均需要根据具体的业务场景设置。

sqlites configuration options: https://sqlite.org/c3ref/c_config_getmalloc.html
按照 sqlite 文档，sqlite 线程安全模式有以下三种：
- SQLITE_CONFIG_SINGLETHREAD（单线程模式）
  - This option sets the threading mode to Single-thread. In other words, it disables all mutexing and puts SQLite into a mode where it can only be used by a single thread.
- SQLITE_CONFIG_MULTITHREAD（多线程模式）
  - This option sets the threading mode to Multi-thread. In other words, it disables mutexing on database connection and prepared statement objects. The application is responsible for serializing access to database connections and prepared statements. But other mutexes are enabled so that SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time.
- SQLITE_CONFIG_SERIALIZED（串行模式）
  - This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time.

产生这个误区主的主要原因是官方文档里的最后一句话：

SQLite will be safe to use in a multi-threaded environment as long as no two threads attempt to use the same database connection at the same time.

但大家往往忽略了前面的一句话：

it disables mutexing on database connection and prepared statement objects

即对于单个连接的读、写操作，包括创建出来的 prepared statement 操作，都没有线程安全的保护。也即在多线程模式下，对单个连接的操作，仍需要在业务层进行锁保护。

关于这一点，#2.4 给出了具体的解释；多线程模式下 (SQLITE_CONFIG_MULTITHREAD) 对 prepared statement、connection 的操作都不是线程安全的

这个问题比较笼统；即使在串行模式下，所有的数据库操作仍需遵循事务模型；而事务模型已经将数据库操作的锁进行了非常细粒度的分离，串行模式的锁也是在上层保证了事务模型的完整性

多线程模式下，仍需要业务上层进行锁保护，串行模式则是在 sqlite 内部进行了锁保护；认为多线程模式性能好的兄弟哪来的自信认为业务层的锁实现比 sqlite 内部锁实现性能更高？

sqlite configuration reference, https://sqlite.org/c3ref/c_config_getmalloc.html
深入理解 sqlite, https://www.kancloud.cn/kangdandan/sqlite

本文作者：hamsongliu

阅读原文

本文为云栖社区原创内容，未经允许不得转载。

0，前言

1，sqlite 主要数据结构

1.1 Connection

1.2 B-Tree

1.3 Pager

2，sqlite 事务模型及锁

2.1 sqlite 多进程安全及 Linux & windows 文件锁

2.1.1 结论

2.2 事务模型(Without WAL)

2.2.1 结论

2.3 WAL 对事务模型的影响

2.3.1 结论

2.4 多线程设置

2.4.1 结论

3，性能优化 tips

3.1 合理使用事务

3.2 启用 WAL + 读写 (连接) 分离

3.2.1 读写连接分离的细节

3.3 针对具体业务场景，设置合适的 WAL SIZE

3.4 针对业务场景分库分表

3.5 其他

4，常见问题 & 误区

4.1 线程安全设置及误区

4.1.1 误区一：多线程模式是线程安全的

4.1.2 误区二：多线程模式下，并发读操作是安全的

4.1.3 误区三：串行模式下，所有数据库操作都是串行执行

4.1.4 误区四：多线程模式性能最好，串行模式性能差

5，参考文献

Just My Socks（注册教程内含优惠码）