SQLite的插入速度变缓的记录数增加由于指数(SQLite insert speed slows

原来的问题

背景

这是众所周知的，SQLite的需要进行微调至50K插入/ s量级上实现插入件的速度。 有关于缓慢插入速度和丰富的咨询和基准这里的许多问题。

也有人称，SQLite的能够处理大量数据 ，以不导致与正确的设置的任何问题50+ GB的报告。

我在这里和其他地方接下来的意见，以实现这些速度和我很高兴与35K-45K插入/秒。 我的问题是，所有的基准测试的唯一表现出快速插入速度与<100万的记录。 我所看到的是， 插入速度似乎是成反比表的大小 。

问题

我使用的情况下，需要存储500米1B元组（ [x_id, y_id, z_id]在一个链接表过了几年）（1M行/天）。 的值是1和200万之间的所有整数ID。 有单个索引z_id 。

性能是伟大的第一10米行〜35K插入/秒，但该表有足够的时间〜20米行，性能开始受到影响。 现在我看到约100个刀​​片/秒。

该表的大小不是特别大。 随着20米行，磁盘上的大小约为500MB。

该项目是用Perl编写。

题

这是大表的SQLite中的现实还是有什么秘密， 维持高插入速率与> 10M行的表？

已知的解决方法，我想尽可能避免其

• 删除索引，添加记录，并重新索引 ：这是罚款作为一种解决方法，但在DB仍然需要更新时使用不工作。 它不会工作，使数据库x分钟完全无法访问/天
• 破表成更小的子表/文件 ：这将在短期内的工作，我已经用它尝试。 问题是，我需要能够查询，这意味着最终我会打62表附件限制时，从整个历史中检索数据。 附加，在临时表收集结果，并分离每秒数百次的请求似乎是一个大量的工作和开销，但我会尝试，如果没有其他的替代品。
• 设置 SQLITE_FCNTL_CHUNK_SIZE ：我不知道C，所以我宁愿不学它只是为了完成这件事（？！）。 我看不到任何的方式来设置使用Perl虽然这个参数。

UPDATE

继蒂姆的建议 ，一个指数，尽管SQLite的的说法，它能够处理大型数据集导致越来越缓慢插入时间，我进行以下设置的基准进行比较：

• 插入的行1400万
• 提交的批处理大小：50,000条记录
• cache_size编译：10,000
• page_size编译：4096
• temp_store编译： 内存
• journal_mode编译： 删除
• synchronous编译： 关闭

在我的项目，如下面的基准测试结果，将创建一个基于文件的临时表和使用的SQLite的内置导入CSV数据支持。 然后，将临时表被附接到接收数据库，并插入与套50,000行insert-select语句。 因此，插入时间不反映文件数据库插入时间，而是桌到桌插入速度。 以CSV导入时间考虑将减少25-50％速度（一个非常粗略的估计，它并不需要很长时间来导入CSV数据）。

显然具有索引导致插入速度放缓如表大小增加而增加。

这是从以上的正确答案可以被分配给数据很清楚添的回答 ，而不是SQLite的只是不能处理它的声明。 显然，它可以处理大型数据集，如果索引该数据集是不是你的用例的一部分。 我一直在使用SQLite的只是，作为一个记录系统后端，有一段时间了， 并不需要建立索引，所以我在我所经历的经济放缓很惊讶。

结论

如果有人发现自己想要存储大量使用SQLite数据， 并将它编入索引， 使用碎片可能是答案。 我最终决定采用以使用MD5的前三个字符乱码唯一列z确定分配给4096级的数据库之一。 由于我使用的情况是在本质上主要是档案，该模式不会改变，查询将永远不需要碎片行走。 还有就是数据库大小的限制，因为非常旧的数据将被降低，最终被废弃，所以分片的这种组合，编译设置，甚至一些德正常化给了我一个很好的平衡，将基于上述基准，保持插入速度的至少10K插入/秒。

Answer 1:

如果您的要求是要找到一个特定的z_id和（从快速选择范围z_ids作为不同的），你可以寻找到一个非索引哈希表嵌套关系数据库链接到它的x_ids和y_ids，它会让你立即找到插入过程中没有索引开销以及随之而来的性能下降的指数增长 - 用自己的方式，以一个特定的z_id，以获取其y_ids和x_ids。 为了避免结块又名斗碰撞，选择把最重的z_id的数字与变化最大的一个关键散列算法（右加权）。

PS使用B树起初可能会比使用线性散列，比如说一个分贝快，但插入的性能将与线性哈希保持水平的性能在B树中的数据库开始下降。

PPS要回答kawing钊的问题：与此相关的核心特征是，这样一个数据库依靠其记录的物理位置是通过哈希算法取记录键输入确定的所谓“疏”表。 这种方法可以直接寻求记录的表中的位置没有索引的中介 。 至于有没有需要遍历索引或重新平衡索引，插入，一直保持不变为表变得更加密集地居住。 用B树，相比之下，插入倍降低为索引树的生长。 支持大量并发插入的OLTP应用程序可以受益于这种稀疏表的方法。 该记录被分散在整个表。 的记录的缺点被散布在所述稀疏表的“苔原”是收集大套，其具有共同的值，记录，例如邮政编码，可以慢。 散列稀疏表的方式进行了优化，以插入和检索个人纪录，并检索相关的记录，而不是大集有一些共同的域值的记录网络 。

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

Answer 2:

大问题，非常有趣跟进！

我只想做一个快速的一句话：你提到破表成更小的子表/文件，后来他们安装是不是一种选择，因为你很快就会达到62级附加的数据库的硬性限制。 虽然这是完全真实的，我不认为你已经考虑一个中途选择：数据分片成几个表，但继续使用相同的单一数据库（文件）。

---

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

架构：

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

数据 - 2万行：

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

每个交易= 50,000 INSERT秒。

---

数据库：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（每10万条记录的）; 索引：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。

---

正如你所看到的，插入速度，如果你分片数据到几个表仍然非常恒定。

Answer 3:

不幸的是我会说这是SQLite中大表的限制。 它不是设计上大规模或大容量数据集执行操作。 虽然我理解它可能大大增加项目的复杂性，你可能会更好过研发更先进的数据库解决方案适合您的需求。

从你链接的一切，它看起来像表的大小访问速度是直接权衡。 不能兼得。

Answer 4:

在我的项目，我不能分片的数据库，因为它是在不同的列索引。 为了加快插入，我已经在/ dev / shm的（= Linux的虚拟盘）创建过程中把该数据库，然后将其复制到本地磁盘。 这显然只是行之有效的一次写入，多次读取数据库。

Answer 5:

我怀疑指数的哈希值的冲突导致插入速度慢。

如果我们在一个表中有很多很多行，然后索引列的哈希值碰撞会更频繁地发生。 这意味着SQLite的引擎需要计算哈希值的两倍或三倍，或甚至四次，以获得不同的哈希值。

所以我想这是SQLite的插入缓慢的根本原因时，该表有许多行。

为什么使用碎片可能会避免这个问题这一点可以解释。 谁在S​​QLite的域名一个真正的专家，以确认或拒绝在这里我的观点？

文章来源: SQLite insert speed slows as number of records increases due to an index