database - SQLite 插入速度随着索引数量的增加而变慢

Question

原始问题

背景

众所周知，SQLite needs to be fine tuned以实现大约 50k 插入/秒的插入速度。这里有很多关于缓慢插入速度和大量建议和基准的问题。

还有claims that SQLite can handle large amounts of data ，有报告称 50+ GB 不会导致正确设置的任何问题。

我已经按照此处和其他地方的建议来实现这些速度，并且我对 35k-45k 插入/秒感到满意。我遇到的问题是所有的基准测试只展示了 < 1m 记录的快速插入速度。我看到的是插入速度似乎与表大小成反比。

问题

我的用例需要在链接表中存储 500m 到 1b 元组( [x_id, y_id, z_id] )数年(100 万行/天)。这些值都是 1 到 2,000,000 之间的整数 ID。 z_id 上有一个索引.

前 10m 行的性能很好，大约 35k 次插入/秒，但是当表有大约 20m 行时，性能开始受到影响。我现在看到大约 100 个插入/秒。

table 的大小不是特别大。对于 20m 行，磁盘大小约为 500MB。

该项目是用 Perl 编写的。

题

这是 SQLite 中大表的现实，还是有什么秘诀可以为超过 1000 万行的表保持高插入率？

如果可能，我想避免的已知解决方法

删除索引，添加记录，然后重新索引:作为一种解决方法，这很好，但是当数据库在更新期间仍然需要可用时不起作用。使数据库在 x 分钟/天内完全无法访问是行不通的

将表分解为较小的子表/文件:这将在短期内起作用，我已经对其进行了试验。问题是我需要能够在查询时从整个历史记录中检索数据，这意味着最终我将达到 62 个表附件限制。在临时表中附加、收集结果以及每个请求分离数百次似乎需要大量的工作和开销，但如果没有其他选择，我会尝试一下。

套装 SQLITE_FCNTL_CHUNK_SIZE :我不会 C (?!)，所以我不想为了完成这件事而学习它。但是，我看不到使用 Perl 设置此参数的任何方法。

更新

关注 Tim's suggestion一个指数正在引起越来越多的
尽管 SQLite 声称它有能力，但插入时间很慢
在处理大型数据集时，我与以下进行了基准比较
设置:

插入的行数:1400 万

提交批量大小:50,000 条记录

cache_size编译指示:10,000

page_size编译指示:4,096

temp_store pragma: 内存

journal_mode pragma: 删除

synchronous pragma: 关闭

在我的项目中，如下面的基准测试结果，创建了一个基于文件的临时表和 SQLite 的内置支持
用于导入 CSV 数据。然后附加临时表
到接收数据库并插入 50,000 行的集合 insert-select声明。因此，插入时间不反射(reflect)
文件到数据库插入次数，而是表到表插入
速度。考虑到 CSV 导入时间会降低速度
25-50%(一个非常粗略的估计，导入不需要很长时间
CSV 数据)。

显然，随着表大小的增加，索引会导致插入速度变慢。



从上面的数据可以很明显地看出，正确答案可以分配给 Tim's answer而不是 SQLite 无法处理它的断言。显然，如果索引该数据集不是您的用例的一部分，它可以处理大型数据集。我一直在使用 SQLite 作为日志系统的后端，一段时间以来它不需要被索引，所以我对我经历的放缓感到非常惊讶。

结论

如果有人发现自己想要使用 SQLite 存储大量数据并将其编入索引， using shards可能是答案。我最终决定使用 MD5 的前三个字符散列 z 中的唯一列确定分配给 4,096 个数据库之一。由于我的用例本质上主要是归档，因此架构不会改变，查询也永远不需要分片遍历。数据库大小有限制，因为极旧的数据将被减少并最终被丢弃，所以这种分片、pragma 设置甚至一些非规范化的组合给了我一个很好的平衡，基于上面的基准测试，将保持插入速度至少 10k 次插入/秒。

Answer 1

如果您的要求是查找特定的 z_id和 x_ids和 y_ids链接到它(与快速选择范围 z_ids 不同)，您可以查看非索引哈希表嵌套关系数据库，它可以让您立即找到特定的 z_id为了得到它的y_ids和 x_ids -- 没有索引开销以及随着索引增长而在插入过程中随之而来的性能下降。为了避免结块(又名桶冲突)，请选择一种 key 散列算法，该算法对 z_id 的数字施加最大权重。变化最大(右加权)。

附言例如，使用 b 树的数据库最初可能比使用线性散列的数据库更快，但随着 b 树上的性能开始下降，插入性能将与线性散列保持一致。

P.P.S.回答@kawing-chiu 的问题:这里相关的核心特征是这样的数据库依赖于所谓的“稀疏”表，其中记录的物理位置由散列算法确定，该算法将记录键作为输入。这种方法允许直接查找表中记录的位置，而无需索引的中介。由于不需要遍历索引或重新平衡索引，插入时间保持不变，因为表变得更加密集。相比之下，使用 b 树，插入时间会随着索引树的增长而降低。具有大量并发插入的 OLTP 应用程序可以从这种稀疏表方法中受益。记录分散在整个表格中。分散在稀疏表的“苔原”中的记录的缺点是收集具有共同值(例如邮政编码)的大量记录可能会更慢。散列稀疏表方法经过优化以插入和检索单个记录，并检索相关记录的网络，而不是具有某些共同字段值的大型记录集。

嵌套关系数据库允许在一行的列中包含元组。