由于索引导致记录数量增加，SQLite插入速度变慢

 

原始问题

背景

众所周知，SQLite需要进行微调，以实现每秒5万次插入的插入速度。 这里有很多关于插入速度慢以及大量建议和基准的问题。

还有人声称SQLite可以处理大量的数据，50+ GB的报告不会对正确的设置造成任何问题。

我遵循这里和其他地方的建议来实现这些速度，我对35k-45k插入/秒感到满意。 我遇到的问题是所有的基准测试都只能证明插入速度快于1m记录。 我所看到的是插入速度似乎与桌子大小成反比。

问题

我的用例需要在链接表中存储500m到1b元组（ [x_id, y_id, z_id] ）几年（1m行/天）。 值是1到2,000,000之间的所有整数ID。 z_id上有一个索引。

对于前10m行，性能非常好，大约35k个插入/秒，但是当表格有大约20m行时，性能开始下降。 我现在看到大约100个插入/秒。

桌子的大小不是特别大。 行20米，磁盘大小约为500MB。

该项目是用Perl编写的。

题

这是SQLite中大表的现实吗？还是有任何秘密来维持具有> 10m行的表的高插入率？

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

  • 删除索引，添加记录并重新索引：作为解决方法，这是很好的方法，但在更新过程中数据库仍需要使用时不起作用。 它不会使数据库在x分钟/天内完全无法访问
  • 将表格分成更小的子表格/文件：这将在短期内起作用，并且我已经尝试过。 问题是，我需要能够从整个历史记录中检索数据，查询哪种方式最终会达到62个表附件的限制。 附加，在临时表中收集结果，并且每个请求分离数百次似乎是很多工作和开销，但是如果没有其他替代方法，我会尝试它。
  • 设置SQLITE_FCNTL_CHUNK_SIZE ：我不知道C（ SQLITE_FCNTL_CHUNK_SIZE ），所以我宁愿不知道它只是为了完成这个。 尽管如此，我看不出用什么方法来设置这个参数。

    • UPDATE

    尽管SQLite声称它能够处理大型数据集，但Tim依然认为索引导致插入时间越来越慢，我使用以下设置执行基准比较：

  • 插入行数：1400万
  • 提交批量大小：50,000条记录
  • cache_size编译指示：10,000
  • page_size编译指示：4,096
  • temp_store pragma：内存
  • journal_mode pragma：删除
  • synchronous附注：关闭

    • 在我的项目中，如下面的基准测试结果所示，创建了一个基于文件的临时表，并且使用SQLite对导入CSV数据的内置支持。 然后将临时表附加到接收数据库，并使用insert-select语句插入50,000行的集合。 因此，插入时间不会反映文件到数据库的插入时间，而是表格到表格的插入速度。 考虑到CSV导入时间会将速度降低25-50％（这是一个非常粗略的估计，导入CSV数据并不需要很长时间）。

    随着表格大小的增加，显然有索引会导致插入速度下降。

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

    结论

    如果任何人发现自己想要使用SQLite存储大量数据并将其编入索引，则使用分片可能是答案。 我最终决定使用MD5散列的前三个字符作为z一个唯一列，以确定分配给4,096个数据库中的一个。 由于我的用例主要是归档，所以模式不会改变，查询永远不会需要碎片走。 数据库大小有限，因为极其旧的数据将会减少并最终被丢弃，所以这种分片，编译指示设置以及一些反常化的组合给了我一个很好的平衡，根据上面的基准测试，可以保持插入速度为至少10k插入/秒。

    如果你的要求是找到一个特定的z_id和与之链接的x_ids和y_ids（与快速选择一系列z_ids不同），你可以查看一个非索引散列表嵌套关系数据库，它可以让你立即找到你的方式来获得它的y_ids和x_ids - 特定的z_id - 没有索引开销和随着索引的增长插入期间伴随的性能下降。 为了避免聚集桶碰撞，请选择一个密钥散列算法，该算法将z_id的数字赋予最大权重（右权重）。

    PS使用B树的数据库最初可能比使用线性散列的数据库更快，但插入性能将与线性散列保持一致，因为b树上的性能开始下降。

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

    嵌套关系数据库是一个允许行内列的元组的数据库。

    伟大的问题和非常有趣的后续行动！

    我只想做一个简短的评论：你提到将表格分成较小的子表格/文件并在以后附加它们不是一种选择，因为你很快就会达到62个附加数据库的硬性限制。 虽然这是完全正确的，但我不认为你已经考虑过一个中途选项：将数据分成几个表，但仍然使用相同的单个数据库（文件）。

    我做了一个非常粗略的基准，以确保我的建议确实对性能产生影响。

    架构： 

    CREATE TABLE IF NOT EXISTS "test_$i"
(
    "i" integer NOT NULL,
    "md5" text(32) NOT NULL
);

    数据 - 200万行：

  • i = 1..2,000,000
  • md5 = MD5十六进制的消化i

    • 每笔交易= 50,000 INSERT s。

    数据库：1; 表格：1; 索引：0 

    0..50000 records inserted in 1.87 seconds
50000..100000 records inserted in 1.92 seconds
100000..150000 records inserted in 1.97 seconds
150000..200000 records inserted in 1.99 seconds
200000..250000 records inserted in 2.19 seconds
250000..300000 records inserted in 1.94 seconds
300000..350000 records inserted in 1.94 seconds
350000..400000 records inserted in 1.94 seconds
400000..450000 records inserted in 1.94 seconds
450000..500000 records inserted in 2.50 seconds
500000..550000 records inserted in 1.94 seconds
550000..600000 records inserted in 1.94 seconds
600000..650000 records inserted in 1.93 seconds
650000..700000 records inserted in 1.94 seconds
700000..750000 records inserted in 1.94 seconds
750000..800000 records inserted in 1.94 seconds
800000..850000 records inserted in 1.93 seconds
850000..900000 records inserted in 1.95 seconds
900000..950000 records inserted in 1.94 seconds
950000..1000000 records inserted in 1.94 seconds
1000000..1050000 records inserted in 1.95 seconds
1050000..1100000 records inserted in 1.95 seconds
1100000..1150000 records inserted in 1.95 seconds
1150000..1200000 records inserted in 1.95 seconds
1200000..1250000 records inserted in 1.96 seconds
1250000..1300000 records inserted in 1.98 seconds
1300000..1350000 records inserted in 1.95 seconds
1350000..1400000 records inserted in 1.95 seconds
1400000..1450000 records inserted in 1.95 seconds
1450000..1500000 records inserted in 1.95 seconds
1500000..1550000 records inserted in 1.95 seconds
1550000..1600000 records inserted in 1.95 seconds
1600000..1650000 records inserted in 1.95 seconds
1650000..1700000 records inserted in 1.96 seconds
1700000..1750000 records inserted in 1.95 seconds
1750000..1800000 records inserted in 1.95 seconds
1800000..1850000 records inserted in 1.94 seconds
1850000..1900000 records inserted in 1.95 seconds
1900000..1950000 records inserted in 1.95 seconds
1950000..2000000 records inserted in 1.95 seconds

    数据库文件大小：89.2 MiB。

    数据库：1; 表格：1; 索引：1（ md5 ） 

    0..50000 records inserted in 2.90 seconds
50000..100000 records inserted in 11.64 seconds
100000..150000 records inserted in 10.85 seconds
150000..200000 records inserted in 10.62 seconds
200000..250000 records inserted in 11.28 seconds
250000..300000 records inserted in 12.09 seconds
300000..350000 records inserted in 10.60 seconds
350000..400000 records inserted in 12.25 seconds
400000..450000 records inserted in 13.83 seconds
450000..500000 records inserted in 14.48 seconds
500000..550000 records inserted in 11.08 seconds
550000..600000 records inserted in 10.72 seconds
600000..650000 records inserted in 14.99 seconds
650000..700000 records inserted in 10.85 seconds
700000..750000 records inserted in 11.25 seconds
750000..800000 records inserted in 17.68 seconds
800000..850000 records inserted in 14.44 seconds
850000..900000 records inserted in 19.46 seconds
900000..950000 records inserted in 16.41 seconds
950000..1000000 records inserted in 22.41 seconds
1000000..1050000 records inserted in 24.68 seconds
1050000..1100000 records inserted in 28.12 seconds
1100000..1150000 records inserted in 26.85 seconds
1150000..1200000 records inserted in 28.57 seconds
1200000..1250000 records inserted in 29.17 seconds
1250000..1300000 records inserted in 36.99 seconds
1300000..1350000 records inserted in 30.66 seconds
1350000..1400000 records inserted in 32.06 seconds
1400000..1450000 records inserted in 33.14 seconds
1450000..1500000 records inserted in 47.74 seconds
1500000..1550000 records inserted in 34.51 seconds
1550000..1600000 records inserted in 39.16 seconds
1600000..1650000 records inserted in 37.69 seconds
1650000..1700000 records inserted in 37.82 seconds
1700000..1750000 records inserted in 41.43 seconds
1750000..1800000 records inserted in 49.58 seconds
1800000..1850000 records inserted in 44.08 seconds
1850000..1900000 records inserted in 57.17 seconds
1900000..1950000 records inserted in 50.04 seconds
1950000..2000000 records inserted in 42.15 seconds

    数据库文件大小：181.1 MiB。

    数据库：1; 表格：20（每100,000个记录一个）; 索引：1（ md5 ） 

    0..50000 records inserted in 2.91 seconds
50000..100000 records inserted in 10.30 seconds
100000..150000 records inserted in 10.85 seconds
150000..200000 records inserted in 10.45 seconds
200000..250000 records inserted in 10.11 seconds
250000..300000 records inserted in 11.04 seconds
300000..350000 records inserted in 10.25 seconds
350000..400000 records inserted in 10.36 seconds
400000..450000 records inserted in 11.48 seconds
450000..500000 records inserted in 10.97 seconds
500000..550000 records inserted in 10.86 seconds
550000..600000 records inserted in 10.35 seconds
600000..650000 records inserted in 10.77 seconds
650000..700000 records inserted in 10.62 seconds
700000..750000 records inserted in 10.57 seconds
750000..800000 records inserted in 11.13 seconds
800000..850000 records inserted in 10.44 seconds
850000..900000 records inserted in 10.40 seconds
900000..950000 records inserted in 10.70 seconds
950000..1000000 records inserted in 10.53 seconds
1000000..1050000 records inserted in 10.98 seconds
1050000..1100000 records inserted in 11.56 seconds
1100000..1150000 records inserted in 10.66 seconds
1150000..1200000 records inserted in 10.38 seconds
1200000..1250000 records inserted in 10.24 seconds
1250000..1300000 records inserted in 10.80 seconds
1300000..1350000 records inserted in 10.85 seconds
1350000..1400000 records inserted in 10.46 seconds
1400000..1450000 records inserted in 10.25 seconds
1450000..1500000 records inserted in 10.98 seconds
1500000..1550000 records inserted in 10.15 seconds
1550000..1600000 records inserted in 11.81 seconds
1600000..1650000 records inserted in 10.80 seconds
1650000..1700000 records inserted in 11.06 seconds
1700000..1750000 records inserted in 10.24 seconds
1750000..1800000 records inserted in 10.57 seconds
1800000..1850000 records inserted in 11.54 seconds
1850000..1900000 records inserted in 10.80 seconds
1900000..1950000 records inserted in 11.07 seconds
1950000..2000000 records inserted in 13.27 seconds

    数据库文件大小：180.1 MiB。

    正如你所看到的，如果你将数据分成几个表格，插入速度几乎保持不变。

    不幸的是，我会说这是SQLite中大表的限制。 它不适用于大规模或大容量数据集。 虽然我知道它可能会大大增加项目的复杂性，但您可能更适合研究适合您需求的更复杂的数据库解决方案。

    从你链接的所有东西，它看起来像表大小访问速度是一个直接的折衷。 不能同时拥有。

    链接地址: http://www.djcxy.com/p/3339.html

    上一篇: SQLite insert speed slows as number of records increases due to an index

    下一篇: How do I check in SQLite whether a table exists?