SQLite优化之路——让SQLite油门到底

1.前言

  以前对于数据库中的知识，只是知道其理论。并没有真正的接触过大量数据的数据库，没有经过实践(除了简单的增删改查，或者随便建个索引看看效率是不是提升了)。所以对于很多知识，其实根本就没有弄明白。比如:

• 数据库的锁应该如何解决
• 数据量大时，加入索引提升到底有多少
• 索引到底应该如何加
• 联合索引的数据结构是怎样的，它对查询的帮助到底又有多大
• 最左匹配在实际中应该怎么利用
• …

  对以上的知识点，以前可能只是知道理论或者看了太多"民科"，所以很多都是有点朦朦胧胧，甚至一些地方自己的理解是完全错误的（强烈建议有问题看英文文档或者直接看源码，不然多多少少会被“民科”误导）(ps:这里的“民科”也不是贬义词，只是相对于专业文档和源码，一些人的博客还是不够全面或者有点错误，这也包括我自己的)。

  最近，在公司里的一个项目里，要求使用sqlite数据库和公司刚写的orm框架(不太完善)来对数据进行操作，在这个过程中，通过自己和框架的不断犯错和实践，最终对于数据库的一些知识有了真正的理解。下面记录了我在这个过程遇到的问题和自己如何解决的问题，并做了总结。可能有些地方也有些错误，还望指出。

2.项目过程

这一章主要描写了项目背景与我在操作sqlite中遇到的问题。

2.1 背景描述

  后端的一个服务需要不断接收数据，每次接收到数据都需要去数据库中寻找20分钟内该数据是否被接收并被处理过，然后根据查询结果，对该数据再进行处理，最后将该数据再存入改数据库中。

ps:简化该场景，即为 接收数据——> 在数据库中查找一次——>存数据一次。

2.2 操作sqlite的问题

  在开发关于数据库操作这一块，几乎每一步都遇到了问题(也怪大学期间没有学过数据库这门课，也没有做过啥操作数据库很频繁的项目)。下面按照时间顺序写出了遇到的每个问题。

• database is locked:
  这个问题是我在自己用来开发的电脑(windows)上测试时没有问题(后面发现也会发生这个bug，只是我的测试条件不够极限)，但是放到开发环境(linux)系统，这个bug马上就出来了。google后发现有人和我的问题几乎一模一样(文章中说这个bug在配置高的机器上容易出现，我当时看了虽然觉得奇怪，但是也信了，后面发现根本也不是这样)
• 性能极限太低(在开发环境(linux)1秒只能处理15条数据，超过15条就会被阻塞，在本机(windows系统)1秒可以处理150条，但150条也达不到要求)
• 当数据库中数据量达到15w条以上后，查询效率就会变得很低
• 使用了WAL以及单线程写操作，还是会出现database is locked；
• update耗时太多;

  后面的章节按照顺序记录了我如何解决这些问题。这里说一下sqlite为什么在本机(windows)和开发环境(linux)上的性能有那么大的差距。究其原因，我觉得大概率是因为操作系统的不同（不同的操作系统对文件的处理不太一样），而不是因为两台机器的配置差别。因为我发现有些操作在本机（windows）上比较快(sqlite插入)，而有些操作(sqlite的查询操作)却在开发环境(linux)上比较快。而且两台机器上的配置也不至于相差10倍之多。

ps:不过也可能是因为其他原因，如果你知道的话，请在下方进行评论~

3.库级锁

  SQLite的锁是库级锁，锁的级别很大，具体如下:

3.1 锁的类型

  SQLite有5个不同的锁状态:

• UNLOCKED（未加锁）

• SHARED（共享）

• RESERVED（保留）

• PENDING（未决）

• EXCLUSIVE（排它）

  SQLite有一个加锁表，记录数据库连接的锁状态。每张表在同一时刻只能处于其中一个锁状态。每种状态都有一种锁与之对应。

3.2 读

  数据库连接最初处于UNLOCKED状态，在此状态下，连接还没有存取数据库。当连接到了一个数据库，甚至已经用BEGIN开始了一个事务时，连接都还处于UNLOCKED状态。为了能够从数据库中读取数据，连接必须必须进入SHARED状态，也就是说首先要获得一个SHARED锁。多个连接可以同时获得并保持共享锁，也就是说多个连接可以同时从同一个数据库中读数据，SQLite是支持并发读取数据的。

3.3 写

  一个连接想要写数据库，它必须首先获得一个RESERVED锁。一个数据库上同时只能有一个RESERVED锁，保留锁可以与共享锁共存，RESERVED锁即不阻止其它拥有SHARED锁的连接继续读数据库，也不阻止其它连接获得新的SHARED锁。 一旦一个连接获得了RESERVED锁，它就可以将数据写入缓冲区，而不是实际地写到磁盘。 当连接想要提交修改(或事务)时，需要获得PENDING锁，之后连接就不能再获得新的SHARED锁了，但已经拥有SHARED锁的连接仍然可以继续正常读数据库。当所有其它SHARED锁都被释放时，拥有PENDING锁的连接就可以将其锁提升至EXCLUSIVE锁，此时就可以将以前对缓冲区所做的修改写到数据库文件。所以SQLite是不支持并发写的。

3.4 读与写的并发

  由以上的内容可见，SQLite即不支持并发写，也不支持在写的时候读。

3.5 database is locked

  从3.4的总结中，得到了SQLite即不支持并发写，也不支持在写的时候读的结论，而database is locked。正是一开始我用四条线程对数据库进行增与改，而读则是更加频繁。所以出现了database is locked。
那么，如何解决这个问题呢，有以下两个方案:

3.5.1 加锁

  在自己的代码里对写与读操作加上锁，使得每一次都只有一个线程在读或者写。但是这样做的缺点就是效率会有点低。

3.5.2 WAL

  WAL机制使得SQLite数据库可以进行读写分离，即可以实现在写的时候读，那么在打开WAL的情况下，再对写的操作进行加锁，则可以解决database is locked的问题。同时效率要比对读和写都加锁的效率高很多。
  且打开WAL本身也会增加写的效率。大概提升5倍左右！

• 不开WAL:
  
• 开启WAL:
  

4.数据库连接

  在解决了锁的问题后，经过测试，我又发现了两个问题。

4.1 性能极限太低

  在开发环境(linux)1秒只能处理15条数据，超过15条就会被阻塞，在本机(windows系统)1秒可以处理150条，但150条也达不到要求。

  于是乎便开始找原因，后来发现我使用的公司的orm框架是没有使用连接池的，每次对于数据库的操作，都会新开连接，然后在sql语句执行完后，再断开连接。这就导致开闭连接浪费了大量的时间。 然后我专门写了代码，测试了在本机(windows)连接的打开和关闭所消耗的实践。

如上图所示，每次打开和关闭连接加起来消耗的时间都很多，因此要解决这个系统极限的问题，必须使用连接池，这样的话速度就能提升不少。

4.2 database is locked

  没错，又是这个问题，本来以为开启了WAL再加上对写操作加锁，这个问题已经解决了，但是它又出现了，稍微有点崩溃。于是我知道，真正学习的机会来了。通过阅读SQLite WAL的官方文档。我发现了原来使用WAL有时候还是会出现database is locked的问题。官方文档对其进行了如下的描述：

Sometimes Queries Return SQLITE_BUSY In WAL Mode:

The second advantage of WAL-mode is that writers do not block readers and readers to do not block writers. This is mostly true.But there are some obscure cases where a query against a WAL-mode database can return SQLITE_BUSY, so applications should be prepared for that happenstance.
Cases where a query against a WAL-mode database can return SQLITE_BUSY include the following:

• If another database connection has the database mode open in exclusive locking mode then all queries against the database will return SQLITE_BUSY. Both Chrome and Firefox open their database files in exclusive locking mode, so attempts to read Chrome or Firefox databases while the applications are running will run into this problem, for example.
• When the last connection to a particular database is closing, that connection will acquire an exclusive lock for a short time while it cleans up the WAL and shared-memory files. If a second database tries to open and query the database while the first connection is still in the middle of its cleanup process, the second connection might get an SQLITE_BUSY error.
• If the last connection to a database crashed, then the first new connection to open the database will start a recovery process. An exclusive lock is held during recovery. So if a third database connection tries to jump in and query while the second connection is running recovery, the third connection will get an SQLITE_BUSY error.

怎么解决这个问题呢，相信阅读了上面文档的你也已经知道了，也是使用数据库连接池，让连接一直不关闭就可以了。

5.事务

  在进行了使用数据库连接池对于系统的读写效率进行提升之后，发现其效率虽然有明显的提升，但是还是达不到要求。

5.1 事务耗时

  于是通过和前辈的一番交流之后(前辈猛烈的说可能是事务影响了效率，我想和事务有啥关系，最终前辈让我试一试，于是我就进行了试一试)，… …(这里省略了前辈对我的各种教育，后面直奔主题)

  阅读orm的框架源码，我发现，这个框架对每次的操作都进行了开启事务，但是它又不支持批量操作。因此当要插入和改动很多数据的时候，它就会对每次改动都打开事务。而事务本身的开启就是很耗时的，这就导致了整个系统效率的低下。

  本机(windows)的事务操作耗时如下:

  最终这个问题通过增加批量操作，并只对这个批量操作进行开始事务，来解决。
直到现在，我们终于开心的解决了效率低下的问题。

看着屏幕上每次插入耗时0ms时，我差点流下了泪水。

5.2 SQLite的事务

  抛出下方链接，其自行学习。不管是不是SQLite，事务的内容都基本一样。
  点我学习SQLite 事务

5.3 事务到底是怎么耗时的

  看了4.2里的链接，发现只知道了一大批关于事务的理论，并不能知道它为啥耗时，那么我告诉你，开启事务，即是要保证 原子性，一致性，隔离性，持久性，就是这个保证的过程消耗的时间。那它到底具体是怎么保证保证的呢，请看下面的分析。

5.3.1 WAL 与 rollback journal

SQLite WAL与rollback journal 详细过程
  What?怎么又是WAL，没错的确是它，但是它的作用不仅是用来读写分离的，还是用来实现原子事务的。

  在引入WAL机制之前，SQLite使用rollback journal机制实现原子事务。

rollback journal机制

rollback journal机制的原理是：

在修改数据库文件中的数据之前，先将修改所在分页中的数据备份在另外一个地方，然后才将修改写入到数据库文件中；如果事务失败，则将备份数据拷贝回来，撤销修改；如果事务成功，则删除备份数据，提交修改。

WAL机制的原理

修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中；如果事务失败，WAL中的记录会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。

  同步WAL文件和数据库文件的行为被称为checkpoint（检查点），它由SQLite自动执行，默认是在WAL文件积累到1000页修改的时候；当然，在适当的时候，也可以手动执行checkpoint，SQLite提供了相关的接口。执行checkpoint之后，WAL文件会被清空。

  在读的时候，SQLite将在WAL文件中搜索，找到最后一个写入点，记住它，并忽略在此之后的写入点（这保证了读写和读读可以并行执行）；随后，它确定所要读的数据所在页是否在WAL文件中，如果在，则读WAL文件中的数据，如果不在，则直接读数据库文件中的数据。

  在写的时候，SQLite将之写入到WAL文件中即可，但是必须保证独占写入，因此写写之间不能并行执行。

  WAL在实现的过程中，使用了共享内存技术，因此，所有的读写进程必须在同一个机器上，否则，无法保证数据一致性。

6. 索引

  在解决了那么多问题之后，我以为我已经完成了这一块的所有东西，但是当我看着测试时，当屏幕上的查询和update的时间随着数据量的增加到大概15w的时候，它从0ms逐渐变为10+ms，然后慢慢的20+ms后，我再一次差点要哭了。但是我知道真正学习的机会又来了。

这个时候前辈问我，“你加索引了吗？”
我:“没有。”
这时他露出了诧异的眼光，并对我说“加上索引试试。”
通过前面的几次经验，我觉得前辈说什么，大部分时候我说行就可以了。虽然我心里面想到上次我加了索引后效率并没有提升太多的实验(上次我的实验是自己下瞎做的，可信度为0)，并没有太大的信心。但是！！！！请看下面的数据:

6.1查询条件

用于查询的字段字段主要有三个，即 title,create_time和status。于是我便开始了不同的索引搭配。
查询sql语句的字段顺序 title,status,create_time。同时查询的内容为 title为 测试+(随机整数1~200)，status = 3，create_time为查找20分中以内的数据。

6.2 用各种数据和不同的索引搭配进行查询

假数据特征

• title为1~100000的随机数。
• create_time 20分内有100w条左右的数据。
• status都为3。
• 其它字段都为空
• 数据总数: 10070379 条(一千万W)
• 数据库文件大小: 1.4 GB

真数据(无20分钟内数据)特征

• title为真实的titile,即可能重复，但是重复频率不算很高。
• create_time 无数据在20分钟以内。
• status都为4。
• 其它字段都不为空
• 数据总数: 1773439条(约一百八十万W)
• 数据库文件大小: 4.3 GB

ps:其实这里我觉的用假数据做了实验，就差不多，已经能说明问题了，但是前辈说，你再用真数据试试。虽然我心里%$#@%，但是我还是决定试试，结果没有想到！！！

真数据(压入5w20分钟内的假数据)特征

• title为真实的titile,即可能重复，但是重复频率不算很高，有5w条数据都在20分钟内，且它们的title为1~100000的随机整数。
• create_time 有5w条数据都在20分钟内。
• status都为4。20分钟内的5w条数据status为3。
• 其它字段都不为空，20分钟内的5w条数据其他字段为空。
• 数据总数: 1823660条(约一百八十万W)
• 数据库文件大小: 4.3 GB

ps:其实这里我觉的用真数据做了实验，就差不多，已经能说明问题了，但是前辈说，你再压入五分钟内的数据试试。虽然我心里还是%$#@%，但是我还是决定试试，结果没有想到！！！

6.2.1不加索引:

假数据(1000万条)(20分内有100w条左右的数据)	真数据(无20分钟内数据)(约180万条)	真数据(压入5w20分钟内数据)(约180万条)
2000m	500ms-1000ms	500ms-1000ms

6.2.2 加索引 (title,create_time, status);

假数据(1000万条)(20分内有100w条左右的数据)	真数据(无20分钟内数据)(约180万条)	真数据(压入5w20分钟内数据)(约180万条)
0ms	0ms	0ms

6.2.3 加索引 (create_time, title,status);

假数据(1000万条)(20分内有100w条左右的数据)	真数据(无20分钟内数据)(约180万条)	真数据(压入5w20分钟内数据)(约180万条)
100-200ms	0ms	5-10ms

6.2.4 加索引(create_time, title);

假数据(1000万条)(20分内有100w条左右的数据)	真数据(无20分钟内数据)(约180万条)	真数据(压入5w20分钟内数据)(约180万条)
100-200ms	0ms	5-10ms

6.2.5 加索引(create_time);

假数据(1000万条) (20分内有100w条左右的数据)	真数据(无20分钟内数据)(约180万条)	真数据(压入5w20分钟内数据)(约180万条)
100-200ms)	0ms	5-10ms(更接近10）

6.2.6 加索引(title);

假数据(1000万条)(20分内有100w条左右的数据)	真数据(无20分钟内数据)(约180万条)	真数据(压入5w20分钟内数据)(约180万条)
0ms	0ms	0ms

6.2.7 加索引(status);

假数据(1000万条)(20分内有100w条左右的数据)	真数据(无20分钟内数据)(约180万条)	真数据(压入5w20分钟内数据)(约180万条)
2000ms+	0ms	5-10ms(更接近10)

6.3 实验总结

如果是范围查询的话，可能要查询的话，可能要遍历好几个节点的下面的节点。如索引单为create_time和单为title的效率不同。因为createTime是范围查询，而title则是一个精确的查询。导致遍历的数目不一样。(如下图所示，下面两张图为单列索引，不是联合索引)。

(若是联合索引，则最下面的节点不为数据，而为二级索引。因此上面在联合索引时的,create_time与title的联合索引查询效率比单个create_time高一点)。
从下面两张图中，也可以看出来，如果查询的字段区分度越高，则最后遍历的数据越少。因此最好选择区分度高的字段来建索引。

6.3.1 索引为(title,create_time):

• 使用create_time进行范围查询:
  
• 使用title进行准确查询:
  

6.3.2 索引总结:

这里我抛出两个链接，因为觉得这两个链接很好的说明了关于索引的理论知识，配合上面的实践进行阅读，效果更好。

• sqlite索引的原理
• 数据库索引 (但是这个链接里面关于联合索引那有，讲了一个由三列组成的联合索引，作者先用了一本书中的索引结构，那本书里由于是一列确定之后，其它列的数据也基本都确定好了，所以结构是那样。但是作者自己画的那个结构有问题，一列确定好，而对应了不同的第二列，这个时候第二列应该还是树的结构，而不是作者那样的顺序的结构)。

7.SQLite还能再快一点

在WAL模式下开启 schema.synchronous = NORMAL；效率又能提升一个数量级！

7.1 性能对比

• 开启WAL的默认情况下:
  

• 在WAL模式下开启 schema.synchronous = NORMAL:
  

7.2 官网说明

PRAGMA schema.synchronous;
PRAGMA schema.synchronous = 0 | OFF | 1 | NORMAL | 2 | FULL | 3 | EXTRA;

Query or change the setting of the “synchronous” flag. The first (query) form will return the synchronous setting as an integer. The second form changes the synchronous setting. The meanings of the various synchronous settings are as follows:

• EXTRA (3)
  EXTRA synchronous is like FULL with the addition that the directory containing a rollback journal is synced after that journal is unlinked to commit a transaction in DELETE mode. EXTRA provides additional durability if the commit is followed closely by a power loss.
• FULL (2)
  When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. This ensures that an operating system crash or power failure will not corrupt the database. FULL synchronous is very safe, but it is also slower. FULL is the most commonly used synchronous setting when not in WAL mode.
• NORMAL (1)
  When synchronous is NORMAL (1), the SQLite database engine will still sync at the most critical moments, but less often than in FULL mode. There is a very small (though non-zero) chance that a power failure at just the wrong time could corrupt the database in journal_mode=DELETE on an older filesystem. WAL mode is safe from corruption with synchronous=NORMAL, and probably DELETE mode is safe too on modern filesystems. WAL mode is always consistent with synchronous=NORMAL, but WAL mode does lose durability. A transaction committed in WAL mode with synchronous=NORMAL might roll back following a power loss or system crash. Transactions are durable across application crashes regardless of the synchronous setting or journal mode. The synchronous=NORMAL setting is a good choice for most applications running in WAL mode.
• OFF (0)
  With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF.
  In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before each checkpoint and the database file is synchronized after each completed checkpoint and the WAL file header is synchronized when a WAL file begins to be reused after a checkpoint, but no sync operations occur during most transactions. With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after each transaction commit. The extra WAL sync following each transaction help ensure that transactions are durable across a power loss. Transactions are consistent with or without the extra syncs provided by synchronous=FULL. If durability is not a concern, then synchronous=NORMAL is normally all one needs in WAL mode.

The TEMP schema always has synchronous=OFF since the content of of TEMP is ephemeral and is not expected to survive a power outage. Attempts to change the synchronous setting for TEMP are silently ignored.