我在用SQLite3 http://www.sqlite.org/在我的一个项目中,我需要确保插入表中的行相对于某些列的组合而言是唯一的。在大多数情况下,插入的行在这方面会有所不同,但如果匹配,新行必须更新/替换现有行。
显而易见的解决方案是使用复合主键,并使用冲突子句来处理冲突。因此:
CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT);
变成这样:
CREATE TABLE Event (Id INTEGER, Fld0 TEXT, Fld1 INTEGER, Fld2 TEXT, Fld3 TEXT, Fld4 TEXT, Fld5 TEXT, Fld6 TEXT, PRIMARY KEY (Fld0, Fld2, Fld3) ON CONFLICT REPLACE);
这确实按照我的需要强制执行唯一性约束。不幸的是,这种变化也带来了性能损失,远远超出了我的预期。我做到了
使用一些测试sqlite3
命令行实用程序,以确保我的代码的其余部分没有错误。测试涉及输入 100,000 行,或者在单个
事务或 100 个事务,每个事务 1,000 行。我得到以下结果:
| 1 * 100,000 | 10 * 10,000 | 100 * 1,000 |
|---------------|---------------|---------------|
| Time | CPU | Time | CPU | Time | CPU |
| (sec) | (%) | (sec) | (%) | (sec) | (%) |
--------------------------------|-------|-------|-------|-------|-------|-------|
No primary key | 2.33 | 80 | 3.73 | 50 | 15.1 | 15 |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld3 | 5.19 | 84 | 23.6 | 21 | 226.2 | 3 |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld2, Fld3 | 5.11 | 88 | 24.6 | 22 | 258.8 | 3 |
--------------------------------|-------|-------|-------|-------|-------|-------|
Primary key: Fld0, Fld2, Fld3 | 5.38 | 87 | 23.8 | 23 | 232.3 | 3 |
我的应用程序目前最多执行 1,000 行事务,性能下降 15 倍令我感到惊讶。我预计吞吐量最多会下降 3 倍,而 CPU 使用率会上升,如 10 万事务案例中所示。我猜想维护主键约束所涉及的索引需要大量的同步数据库操作,因此在这种情况下我的硬盘成为瓶颈。
Using WAL mode http://www.sqlite.org/draft/wal.html确实有一些效果——性能提升约15%。不幸的是,这本身还不够。PRAGMA synchronous = NORMAL http://www.sqlite.org/pragma.html#pragma_synchronous似乎没有任何效果。
I might能够通过增加事务大小来恢复一些性能,但我宁愿不这样做,因为内存使用量增加以及对响应性和
可靠性。
每行中的文本字段的长度可变,平均约为 250 字节。查询性能并不重要,但插入性能非常重要。我的应用程序代码是用 C 编写的,并且(应该)至少可以移植到 Linux 和 Windows。
有没有办法在不增加事务大小的情况下提高插入性能?要么是 SQLite 中的某些设置(即除了永久强制数据库进入异步操作之外的任何设置),还是以编程方式在我的应用程序代码中?例如,有没有一种方法可以在不使用索引的情况下确保行的唯一性?
BOUNTY:
通过使用我自己的答案中描述的散列/索引方法,我设法将性能下降程度控制在我的应用程序可能可以接受的程度。
然而,似乎随着表中行数的增加,索引的存在使得插入变得越来越慢。
我对任何能够提高此特定用例中性能的技术或微调设置感兴趣,只要它不涉及破解 SQLite3 代码或以其他方式导致项目变得无法维护。