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

最新推荐文章于 2023-07-27 10:41:34 发布

LCDZhao

最新推荐文章于 2023-07-27 10:41:34 发布

阅读量3.9k

点赞数 5

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_37465188/article/details/102521239

版权

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是不支持并发写的。