SQLite3读取数据库与优化的一点探索

发表于 更新于 分类于

如题。

背景

想用python开发一个离线版查词工具。其中，离线词典文件的选择是一个麻烦事。常见的离线词典格式有.mdx, .eudic等，但这些格式解析起来有些麻烦，有些甚至需要专有库。因此一直未能选择到合适的离线词库。

前几天逛Github时看到一个存储库：

https://github.com/skywind3000/ECDICT

这个存储库的作者收集了四百多万个英语单词和词组，构建了一个非常全面的离线词库，非常符合我的心意。看release页面，作者提供的格式除了.mdx和 .eudic以外，还提供了SQLite格式（.db）的数据库文件，这意味着我们可以使用python标准库sqlite3进行读取。

SQLite是一个轻量化的SQL数据库管理工具，支持SQL语法进行数据库操作，并提供许多种编程语言的接口，目前已成为使用最广泛的数据库，应用场景包括轻量级网站服务器、嵌入式设备、移动应用程序等的数据存储。

我们从作者的GitHub release中下载SQLite格式的词库文件并解压缩，得到stardict.db文件，这个就是SQLite能够直接操作的数据库文件了。整个文件大小为 811.9M ，非常大（后面会提到如何减小文件大小）。

直接在SQLite中查询这个数据库（以查询单词“python”为例。需要首先在命令行中通过sqlite3指令启动到SQLite3工具的交互式界面，然后运行下面的指令）：

1
2
3
.open stardict.db  --打开数据库文件
.header on         --开启表头显示
SELECT * FROM stardict WHERE word='python';  --使用SQL语句查询数据库

输出结果如下。

1
2
3
4
5
6
7
8
9
sqlite> .open stardict.db
sqlite> .header on
sqlite> SELECT * FROM stardict WHERE word='python';
id|word|sw|phonetic|definition|translation|pos|collins|oxford|tag|bnc|frq|exchange|detail|audio
2451896|python|python|'paiθ?n|n. large Old World boas
n. a soothsaying spirit or a person who is possessed by such a spirit
n. (Greek mythology) dragon killed by Apollo at Delphi|n. 大蟒, 巨蟒
[计] Python 程序设计语言；人生苦短，我用 Python|n:100|1|||24103|19021|s:pythons||
sqlite>

这个词库收录了单词的音标、中英文释义、各种场景下的词频统计以及单词的变形，仅此一例就可以看出这个离线词库做得很精心。

下面是在python程序中的一些探索。

python对SQLite数据库的操作

python标准库中提供了sqlite3这个库，可以原生支持对SQLite数据库的操作。使用前需要import sqlite3 。在python中查词的方法（仍然以查询单词“python”为例）：

1
2
3
4
5
6
import sqlite3 
con = sqlite3.connect("stardict.db") # 连接到SQLite数据库文件
cur = con.cursor() # 新建一个用于操作数据库的指针对象。后续操作需通过指针进行。
sql = "SELECT * FROM stardict WHERE word='python';" # 定义SQL查询语句
res = cur.execute(sql).fetchall() # 通过指针执行上述SQL查询语句，将查询结果保存在变量res中
print(res)

输出结果如下。注意到，在python中，sqlite3查询数据库以后返回的是一个python list，这大大方便了后续的处理。

1
2
3
4
5
6
7
>>> import sqlite3
>>> con = sqlite3.connect("stardict.db") # 连接到SQLite数据库文件
>>> cur = con.cursor() # 新建一个用于操作数据库的指针对象。后续操作需通过指针进行。
>>> sql = "SELECT * FROM stardict WHERE word='python';" # 定义SQL查询语句
>>> res = cur.execute(sql).fetchall() # 通过指针执行上述SQL查询语句，将查询结果保存在变量res中
>>> print(res)
[(2451896, 'python', 'python', "'paiθɒn", 'n. large Old World boas\nn. a soothsaying spirit or a person who is possessed by such a spirit\nn. (Greek mythology) dragon killed by Apollo at Delphi', 'n. 大蟒, 巨蟒\n[计] Python 程序设计语言；人生苦短，我用 Python', 'n:100', 1, None, '', 24103, 19021, 's:pythons', None, '')]

整个离线查词工具的实现细节此处省略，源代码见GitHub存储库 ，目前仅支持在命令行环境下使用。

减小SQLite3文件的大小

我们注意到，作者提供的数据库原始文件，其大小达到了 811.9M之多，然而当我们用下面的指令导出数据到.csv以后却发现整个CSV表格才306.0M。

1
2
# 从sqlite3导出数据库到CSV文件
sqlite3 -header -csv stardict.db "select * from stardict;" > data.csv

SQLite格式比CSV表格格式大了这么多，为什么呢？能不能压缩数据库文件，让他节省点空间呢？

很遗憾不能。python的sqlite3库似乎并不支持直接读取压缩后的数据库文件；尽管有一些方法可以绕过上述限制，但总归很麻烦并且很占内存。

不过确实有方法可以降低SQLite数据库文件的体积的。一种方法是使用VACUUM命令清理未使用的空间；另一种方法是通过PRAGMA page_size指令修改数据库分页大小，从而提高空间利用率。

1. VACUUM指令

当我们从Sqlite删除数据后，未使用的磁盘空间被添加到一个内在的”空闲列表”中用于存储下次插入的数据。这会导致删除数据后文件大小没有变化。然而，”空闲列表”所占用的空间对于我们一般的操作来说是没有帮助的，这意味着我们可以清理掉这部分空间。VACUUM指令就是用来清理它们的。

1
2
.open stardict.db  --打开数据库文件
VACUUM; --清理空闲空间。这会花上一段时间。

很简单，对不对？这一个指令把所有能干的事情都干完了。

不过当我们再次查看stardict.db文件大小时，发现文件大小变化并不显著，仅仅从811.9M降低到了 762.0M 。

能不能进一步压缩空间呢？有 文章 提到可以通过设置分页大小以减少文件大小。

2. 修改分页大小

页面（PAGE）是SQLite的最小存储单元，它是表扩张和收缩的基本单位，表中的记录都存储在PAGE中。PAGE_SIZE用来指定PAGE的大小，这个数值必须是2的指数幂，并且在512和65536之间。SQLite的不同版本有不同的默认值（v3.12之前是1024 Byte即1KB，v3.12之后是4096 Byte即4KB），一般来说使用默认值即可，但也可也修改PAGE_SIZE以满足我们自己的需求。

根据 另一篇文章 的描述，SQLite在PAGE中的存储表记录时（在SQLite中也称为payload），会首先使用当前PAGE中剩余的存储空间，当剩余空间不够用时，会产生一个overflow page（溢出页）,然后继续在溢出页中存储payload剩余的内容，空间仍然不够用时，会继续产生溢出页，以此种方式直到将payload表达完整（如下图）。不论是1KB的PAGE_SIZE还是4KB的PAGE_SIZE，都实在是太小了，如果一条记录占用空间超过4KB，则需要不止一次的寻址，并且为了实现高效寻址，SQLite数据库也必然会使用更多的空间存储元数据，使得数据库文件占用空间增大。

1029647-20190902000100536-1382003152.png

查询和修改PAGE_SIZE的指令很简单：

1
2
PRAGMA page_size; --查询PAGE_SIZE
PRAGMA page_size=<new size>; --修改PAGE_SIZE为<new size>

但是请注意，通过上述指令完成PAGE_SIZE修改以后，并不会马上生效，需要手动更新整个数据库。

因此完整指令如下：

1
2
3
.open stardict.db  --打开数据库文件
PRAGMA page_size=16384; --修改PAGE_SIZE为16KB
VACUUM; --清理空闲空间。这会花上一段时间。

或者在修改完PAGE_SIZE以后，直接将数据表另存为一份副本，从而实现更新（参考这篇 文章 ）：

1
2
3
4
5
6
7
8
.open stardict.db  --打开数据库文件
PRAGMA page_size=16384; --修改PAGE_SIZE为16KB
BEGIN TRANSACTION; --开始一个另存数据表为副本的操作
CREATE TABLE db1 AS SELECT * FROM stardict; --另存数据表stardict为副本db1
DROP TABLE stardict;  --删除原始的stardict数据表
ALTER TABLE db1 RENAME TO stardict; --把新数据表的名字改回去
COMMIT;  --提交上述操作
VACUUM;  --并释放删除原数据表以后的空间

完成上述步骤以后，我们成功将 stardict.db 文件的大小减小到了 298.6M ，甚至小于导出的CSV文件（306.0M）。说明PAGE_SIZE对SQLite数据库文件的大小确实有很大的影响。

不过最后还得补充一点，经过上述方法处理以后，虽然数据库文件大小显著减小，但查询速度也受到了一些影响而变得很慢，这和数据库索引以及数据存储方式有关。重新建立一下数据库索引，会好很多。

参考文献