SQLite插入速度随记录数量的增加而减慢，这是由于索引的存在。

原问题

\n

背景

\n众所周知，为了实现每秒插入50,000条记录的速度，必须对SQLite进行优化。这里有很多关于插入速度慢的问题以及丰富的建议和基准测试。\n还有一些声称SQLite可以处理大量数据的说法，有报道称50GB以上的数据不会因为正确的设置而出现任何问题。\n我已经遵循这里和其他地方的建议，以实现这些速度，我对每秒插入35,000-45,000条记录感到满意。我遇到的问题是，所有的基准测试只证明了在<1m条记录时的快速插入速度。我观察到插入速度似乎与表的大小成反比。\n

问题

\n我的用例需要在几年内（每天100万行）在一个链接表中存储5亿到10亿个元组（[x_id，y_id，z_id]）。这些值都是1到2,000,000之间的整数ID。在z_id上有一个单独的索引。\n对于前1000万行，性能很好，大约每秒插入35,000条记录，但是当表有大约2000万行时，性能开始下降。现在我每秒只能插入大约100条记录。\n表的大小并不特别大。有2000万行时，磁盘上的大小约为500MB。\n这个项目是用Perl编写的。\n

问题

\n这是SQLite中大表的实际情况，还是有什么秘诀可以保持具有>1000万行的表的高插入速率？\n

已知的解决方法，如果可能的话，我希望避免

\n

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

\n

• 将表分成较小的子表/文件：这在短期内是可行的，我已经进行了一些实验。问题是，我需要能够在查询时从整个历史记录中检索数据，这意味着最终我会达到62个表的附加限制。每次请求都会附加，收集结果到临时表，然后分离数百次似乎是很多工作和开销，但如果没有其他选择，我会尝试。

\n

• 设置SQLITE_FCNTL_CHUNK_SIZE：我不懂C（？！），所以我宁愿不学它，只是为了完成这个任务。不过，我看不到用Perl设置此参数的方法。

\n

\n

更新

\n根据Tim的建议，即使SQLite声称它能处理大数据集，索引仍会导致插入时间越来越慢，我进行了以下基准测试比较：\n

\n
• 插入行数：1400万

\n

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

\n

• cache_size pragma：10,000

\n

• page_size pragma：4,096

\n

• temp_store pragma：memory

\n

• journal_mode pragma：delete

\n

• synchronous pragma：off

\n

\n在我的项目中，与以下基准测试结果一样，我创建了一个基于文件的临时表，并使用SQLite的内置支持导入CSV数据。然后将临时表附加到接收数据库，并使用insert-select语句插入一组50,000行。因此，插入时间不反映文件到数据库的插入时间，而是表到表的插入速度。考虑到CSV导入时间，速度会降低25-50%（非常粗略的估计，导入CSV数据不需要很长时间）。\n很明显，索引导致插入速度随着表的大小增加而变慢。\n\n从上面的数据中，很明显正确的答案可以归功于Tim的答案，而不是SQLite无法处理的断言。如果索引不是您的用例的一部分，那么它显然可以处理大数据集。我一直在使用SQLite作为日志系统的后端，因为它不需要索引，所以我对我遇到的性能下降感到非常惊讶。\n

结论

\n如果有人想使用SQLite存储大量数据并对其进行索引，使用分片可能是答案。我最终决定使用MD5哈希在z中的唯一列的前三个字符来确定分配给4096个数据库中的哪一个。由于我的用例主要是归档性质的，模式不会改变，查询也不会需要分片遍历。由于极老的数据将被减少并最终被丢弃，所以这种分片、pragma设置甚至一些去规范化的组合给我提供了一个很好的平衡，根据以上基准测试，可以保持至少每秒10,000次插入的速度。