在大表上执行 .import 时，SQLite 非常慢

Question

我正在运行以下内容:

  .mode tabs
  CREATE TABLE mytable(mytextkey TEXT PRIMARY KEY, field1 INTEGER, field2 REAL);
  .import mytable.tsv mytable

mytable.tsv 大约是。 6 GB 和 5000 万行。该过程需要极长的时间(数小时)才能运行，并且它还完全限制了整个系统的性能，我猜是因为临时磁盘 IO。

我不明白为什么要花这么长时间，为什么它会如此频繁地破坏磁盘，而当我有足够的可用物理 RAM 可用于临时写入时。

我如何改进这个过程？

PS:是的，我确实搜索了以前的问题和答案，但没有找到任何帮助。

Answer 1

在 Sqlite 中，一个 normal rowid table使用 64 位整数主键。如果表定义中的 PK 不是单个 INTEGER 列，而是将其视为唯一索引，并且插入的每一行都必须更新原始表和该索引，从而使工作(并且在您的情况下有效地将存储要求加倍)。如果您改为将表格设为 WITHOUT ROWID第一，PK是真正的PK，不需要额外的索引表。仅此一项更改就应该将导入数据集所需的时间和数据库的大小大致减半。 (如果您在表上有其他索引，或者将该 PK 用作另一个表中的外键，从长远来看可能不值得进行更改，因为它可能会增加这些表所需的空间量很多给定你的 key 的长度；在这种情况下，请参阅 Schwern 的回答)。

首先在键列上对输入进行排序也有助于大型导入，因为 b 树页面的随机访问和这些页面内的数据移动较少。所有内容都进入同一页面，直到它填满并分配一个新页面并完成任何需要的重新平衡。

您还可以启用一些不安全的设置，这些设置在正常使用时不被推荐，因为它们可能会导致数据丢失或彻底损坏，但如果在导入过程中由于突然断电或其他原因而发生这种情况，您可以随时开始超过。特别是，设置 synchronous mode和 journal type 关闭。这样可以减少导入过程中的光盘写入。