Sqlite3：插入时禁用主键索引？ [英] Sqlite3: Disabling primary key index while inserting?

问题描述

我有一个带有表的Sqlite3数据库和一个由两个整数组成的主键，我正在尝试向其中插入大量数据（即大约1GB左右）

I have an Sqlite3 database with a table and a primary key consisting of two integers, and I'm trying to insert lots of data into it (ie. around 1GB or so)

我遇到的问题是创建主键还隐式创建了一个索引，在我的情况下，在一些提交之后将插入陷入爬行（这可能是因为数据库文件在NFS上。 叹息）。

The issue I'm having is that creating primary key also implicitly creates an index, which in my case bogs down inserts to a crawl after a few commits (and that would be because the database file is on NFS.. sigh).

所以，我想以某种方式临时禁用该索引。到目前为止，我最好的计划是删除主键的自动索引，但似乎SQLite不喜欢它并且如果我尝试这样做会抛出错误。

So, I'd like to somehow temporary disable that index. My best plan so far involved dropping the primary key's automatic index, however it seems that SQLite doesn't like it and throws an error if I attempt to do it.

My第二个最佳计划将涉及应用程序在网络驱动器上制作透明的数据库副本，进行修改然后将其合并。请注意，与大多数SQlite / NFS问题相反，我不需要访问并发。

My second best plan would involve the application making transparent copies of the database on the network drive, making modifications and then merging it back. Note that as opposed to most SQlite/NFS questions, I don't need access concurrency.

这样做的正确方法是什么？

What would be a correct way to do something like that?

更新：

我忘了指定我已使用的标志：

I forgot to specify the flags I'm already using:

PRAGMA synchronous = OFF
PRAGMA journal_mode = OFF
PRAGMA locking_mode = EXCLUSIVE
PRAGMA temp_store = MEMORY

更新2：
我实际上是在插入物品批量，但是每个下一批都比前一批更慢（我假设这与索引的大小有关）。我尝试了10k到50k元组之间的批处理，每个元组是两个整数和一个浮点数。

UPDATE 2: I'm in fact inserting items in batches, however every next batch is slower to commit than previous one (I'm assuming this has to do with the size of index). I tried doing batches of between 10k and 50k tuples, each one being two integers and a float.

推荐答案

1. 你无法删除嵌入索引，因为它是行的唯一地址。


2. 在单个长键中合并你的2个整数键=（key1<< 32）+ key2;并将其设置为youd架构中的INTEGER PRIMARY KEY（在这种情况下，您将只有1个索引）


3. 设置新数据库的页面大小至少为4096


4. 删除除主要内容之外的任何其他索引


5. 按SORTED顺序填写数据，以便主键增长。


6. 重用命令，不要每次从字符串创建它们


7. 将页面缓存大小设置为您剩余的内存量（请记住缓存大小是页数，但不是字节数）


8. 提交每50000件商品。


9. 如果您有其他索引 - 仅在表格中所有数据创建后

1. You can't remove embedded index since it's the only address of row.
2. Merge your 2 integer keys in single long key = (key1<<32) + key2; and make this as a INTEGER PRIMARY KEY in youd schema (in that case you will have only 1 index)
3. Set page size for new DB at least 4096
4. Remove ANY additional index except primary
5. Fill in data in the SORTED order so that primary key is growing.
6. Reuse commands, don't create each time them from string
7. Set page cache size to as much memory as you have left (remember that cache size is in number of pages, but not number of bytes)
8. Commit every 50000 items.
9. If you have additional indexes - create them only AFTER ALL data is in table

如果你能够合并密钥（我认为你使用32位，而sqlite使用64位，那么它是可能的）并按排序顺序填充数据我打赌你会填写在你的第一个Gb中，性能与第二个相同，两者都足够快。

If you'll be able to merge key (I think you're using 32bit, while sqlite using 64bit, so it's possible) and fill data in sorted order I bet you will fill in your first Gb with the same performance as second and both will be fast enough.

这篇关于Sqlite3：插入时禁用主键索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！