更新sqlite3索引的时间波动太大

Question

我有一个大型的sqlite3 (3.6.22)数据库(大约1 GB，500万行)，单表索引在一列上。问题是执行典型INSERT事务的时间波动很大。我一次插入大约10000行(当然是包装在一个事务中)。通常需要大约1.5秒，但大约每5个事务，突然需要几分钟才能完成完全相同的事务。我做了很多实验，我发现只有在有索引的情况下才会出现这种现象，这让我认为它正在更新索引，这需要很多时间。

我需要更稳定的性能。平均插入时间稍微长一点也没问题，只要我能避免某些事务突然花费200倍的时间……我该怎么办？

这是模式。blocks.md5中的字符串长度始终恰好为32个字节，并且可能是唯一的。rolling.value列将包含非常大的64位整数。

CREATE TABLE blocks (blob char(32) NOT NULL, 
                     offset long NOT NULL, 
                     md5 char(32) NOT NULL, 
                     row_md5 char(32));
CREATE TABLE rolling (value INT NOT NULL);

CREATE INDEX index_md5 ON blocks (md5);
CREATE UNIQUE INDEX index_rolling ON rolling (value);

Answer 1

我不确切地知道sqlite索引是如何实现的，但如果它们将索引存储在磁盘上或对数据进行重新排序，我希望看到您所描述的行为。

想象一下这样一种场景:当他们为索引分配块时，他们以N个数据槽开始一些页面。当页面填满时，它们必须分配另一个页面，并在它们之间拆分数据。

当您插入数据时，MD5的顺序将是随机的，因此每个页面都将独立填充。没有任何合理的方法让索引策略知道这一点。

其他数据库甚至会建议对字符串使用不同的索引策略，特别是在随机MD5s之类的情况下。

在全内存数据库中尝试这样做会告诉您它是算法访问还是磁盘访问。

我只在离线系统中尝试避免这种情况，在这种系统中，我可以在插入之前对数据进行排序。当它全部插入后，我会索引它，这是我能找到的最快的速度。如果你一次做10k，那可能是你的用例，尽管我不知道。