SQLite 追加数据性能线性下降，这可解决吗？

Question

我设置了一个测试用例来将行写入数据库。每个事务插入10,000行，没有更新。每个步骤花费的时间比上一个步骤线性增加。前十个步骤执行提交所需的毫秒数如下：

568、772、942、1247、1717、1906、2268、2797、2922、3816、3945。

当它添加10,000行到500,000行的表时，需要37149毫秒才能提交！

以下是一些其他设置：

- setAutocommit(false) - PRAGMA page_size = 4096 - PRAGMA journal_size_limit = 104857600 - PRAGMA count_changes = OFF - PRAGMA cache_size = 10000

我没有外键约束。使用WAL可以提高性能（上面给出了数据），但还是会有线性下降。PRAGMA Synchronous=OFF没有效果。PRAGMA locking_mode=EXCLUSIVE没有效果。运行时没有额外的索引和额外的索引。产生了大致相同的时间差异，因此仍然是线性下降。完整模式如下（我已经运行了有索引和无索引的情况，但已经包含）。

create table if not exists [EventLog] (
Id INTEGER PRIMARY KEY ASC, 
DocumentId TEXT NOT NULL, 
Event TEXT NOT NULL, 
Content TEXT NOT NULL, 
TransactionId TEXT NOT NULL, 
Date INTEGER NOT NULL, 
User TEXT NOT NULL)

create index if not exists DocumentId ON EventLog (DocumentId)

create index if not exists TransactionId ON EventLog (TransactionId)

create index if not exists Date ON EventLog (Date)

这是在Windows环境下运行的sqlite-jdbc-3.7.2

- J Pullar

---

除了主键之外还有其他索引吗？ - Joachim Isaksson

你是否重新索引？（尽管在SQLite上是否相关？） - njzk2

你尝试过使用页面大小为65536（最大值）的#pragma吗？ - njzk2

一些关于SQLite优化的线索 https://dev59.com/reo6XIcBkEYKwwoYTzEw - njzk2

另外，如果您的数据库只需创建一次，请在导入结束时创建索引。 - njzk2

Answer 1

SQLite表格和索引内部都是B-Tree结构。在表格中，Rowid是排序键。(你的INTEGER PRIMARY KEY就是Rowid。)

---

如果插入的ID不大于表格中已有的最大ID，则记录不会被追加到末尾，而是插入到树的中间某个位置。当一次性插入足够多的记录并且ID的分布是随机的时候，这意味着几乎每个数据库页面都必须被重写。

为了避免这种情况:

1. 插入ID时以递增顺序进行; 或者
2. 将ID插入为NULL，让SQLite选择下一个值; 或者
3. 通过声明为INTEGER UNIQUE(或者如果你不需要额外的检查/索引就只声明为INTEGER)来防止SQLite使用你的ID字段作为Rowid，从而使表格的排序与你的ID无关。

---

在索引的情况下，插入带有随机分布的索引字段需要更新索引的随机位置。像表格一样，当一次性插入足够多的记录并且ID的分布是随机的时候，这意味着几乎每个索引页面都必须被重写。

在加载大量数据时，建议先不要创建任何索引，以后再重新创建它们。(与其他一些数据库不同，SQLite没有暂时禁用索引的功能;只能删除它们。)

- CL.

---

谢谢您的回答，不幸的是，这已经是情况了，我已经相应地更新了我的问题。 - J Pullar

我想，这些id被生成并且数据被插入时，它们是递增的。 - njzk2

更新了；你遇到了相同的问题，但是涉及到索引。 - CL.

我已提取代码并验证它完全基于索引，特别是在行具有自定义ID（如GUID）的情况下。 - J Pullar

我已经实现了一个删除并重新创建索引的方法，尽管速度较慢，但仍然呈现出线性退化。 - J Pullar

Answer 2

顺便提一下，虽然我没有限制密钥内容的结构，但在99.999%的情况下，它将是一个guid。因此，为了解决性能问题，我编写了一个算法，使用基于时间的值生成连续的guid，其中前8个十六进制数字是时间值。即使使用早期时间值生成块的guid，这也非常有效。

- J Pullar