python - 插入数百万行的性能

Question

我正在尝试使用 Python 脚本来解析维基百科文件。 (是的，我知道。)当然:

• 维基百科 XML:45.95 GB
• 可用内存:16 GB

这会阻止将文件加载到内存中，而进入虚拟内存也不会好到哪里去。因此，为了处理数据，我决定将必要的信息解析到 SQLite 数据库中。对于 XML 解析，我使用了 ElementTree 库，它的性能非常好。我确认只运行 XML 解析(只是注释掉数据库调用)它是线性运行的，在遍历文件时没有减速。

问题在于尝试将数百万行插入 SQLite 数据库(每篇维基百科文章一个)。我用于测试的表格的简单版本如下:

CREATE TABLE articles(
    id INTEGER NOT NULL PRIMARY KEY,
    title TEXT NOT NULL UNIQUE ON CONFLICT IGNORE);

所以在这个初始阶段我只有 id 和一个文本字段。当我通过以下方式开始添加行时:

INSERT OR IGNORE INTO articles(title) VALUES(?1);

它一开始表现不错。但在大约 800 万行时，速度开始急剧下降，下降一个数量级或更多。

当然需要一些细节。我将 cur.executemany() 与在插入语句之前创建的单个游标一起使用。对该函数的每次调用都有大约 100,000 行的批处理。在插入所有超过一百万行之前，我不会调用 db.commit() 。根据我的阅读，只要只有 INSERT 语句，executemany() 就不应在 db.commit() 之前提交事务。

正在读取的源 XML 和正在写入的数据库位于两个不同的磁盘上，我也尝试过在内存中创建数据库，但无论如何我都看到了减速。我还尝试了 isolation_level=None 选项，添加 BEGIN TRANSACTION 和 COMMIT TRANSACTION 在开头和结尾调用自己(所以整个解析序列是一次交易)，但它仍然无济于事。

一些 other questions on this site建议索引是问题所在。我的表上没有任何索引。我确实尝试删除 UNIQUE 约束并将其限制为 id INTEGER PRIMARY KEY 和 title TEXT NOT NULL 但这也没有效果。

在 SQLite 中对大型数据集执行这些类型的插入的最佳方法是什么？当然，这个简单的查询只是众多查询中的第一个；还有其他查询会更复杂，涉及外键(此表中文章的 ID)以及嵌入选择的插入语句(在插入期间从文章表中选择一个 ID)。这些必然会遇到同样的问题，但会大大加剧 - 文章表的行数少于 1500 万行，而其他表的行数可能超过 10 亿行。因此，这些性能问题更加令人担忧。

Answer 1

插入时发生的一件“不可见”的事情是更新表的索引(并检查与索引相关的约束，例如 UNIQUE)。由于您无论如何都忽略了 UNIQUE 违规，您可能会发现在加载表时禁用表上的索引很有用，如果您确实需要它们，请在加载完成后构建一次索引。

但也要注意，SQLite 对小数据的闪电般的速度来自某些隐含的假设，当您处理大数据时，这些假设会越来越多地被违反。对于您当前硬件上的当前问题，它可能不是合适的工具。