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

0，前言

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

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

1，sqlite主要数据结构

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

1.1 Connection

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

1.2 B-Tree

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

1.3 Pager

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

2，sqlite事务模型及锁

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

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

2.1.1 结论

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

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

2.2 事务模型(Without WAL)

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

这里有几点需要注意：

• UNLOCKED、PENDING、SHARE