SQLite 导入选项卡文件 : is . 导入每行插入一次或将它们与事务分组？

Question

我正在从选项卡文件中导入数百万行，而 SQLite .import .mode 选项卡非常慢。我有三个索引，所以缓慢可能来自索引。但首先我想检查一下 .import 是否将分组批次/所有这些行添加到单个提交中。我找不到 .import 是如何工作的文档。有人知道吗？。如果索引是问题(我之前在使用 mysql 时遇到过这个问题)，我如何禁用它并在 .import 结束时重新索引？

[更新1]

关注@sixfeetsix 评论。

我的架构是:

CREATE TABLE ensembl_vf_b36 (
        variation_name  varchar(20),
        chr     varchar(4),
        start   integer,
        end     integer,
        strand  varchar(5),
        allele_string    varchar(3),
        map_weight      varchar(2),
        flags           varchar(50),
        validation_status       varchar(100),
        consequence_type        varchar(50)
);
CREATE INDEX pos_vf_b36_idx on ensembl_vf_b36 (chr, start, end);

数据:

rs35701516      NT_113875       352     352     1       G/A     2       NULL    NULL    INTERGENIC
rs12090193      NT_113875       566     566     1       G/A     2       NULL    NULL    INTERGENIC
rs35448845      NT_113875       758     758     1       A/C     2       NULL    NULL    INTERGENIC
rs17274850      NT_113875       1758    1758    1       G/A     2       genotyped       cluster,freq    INTERGENIC

有 15_608_032 此表中的条目

这些是统计数据

 $  time sqlite3 -separator '   ' test_import.db '.import variations_build_36_ens-54.tab ensembl_vf_b36'

real    29m27.643s
user    4m14.176s
sys     0m15.204s

[更新2]

@sixfeetsix 有一个很好的答案，如果您正在阅读本文，您也会对

Faster bulk inserts in sqlite3?

Sqlite3: Disabling primary key index while inserting?

[update3] 30 分钟 -> 4 分钟的解决方案

即使进行了所有优化(请参阅已接受的答案)仍然需要近 30 分钟，但如果未使用索引并在最后添加索引，则总时间为 4 分钟:

-- importing without indexes:
       real    2m22.274s
       user    1m38.836s
       sys     0m4.850s

 -- adding indexes
     $  time sqlite3 ensembl-test-b36.db < add_indexes-b36.sql

     real    2m18.344s
     user    1m26.264s
     sys     0m6.422s

Answer 1

我相信缓慢确实来自随着越来越多的记录被添加而建立索引。根据您拥有的 RAM，您可以告诉 sqlite 使用足够的内存，以便所有这些索引构建事件都在内存中完成(即没有所有 I/O，否则会在内存较少的情况下发生)。

对于 15M 记录，我认为您应该将缓存大小设置为 500000。

您还可以告诉 sqlite 将其事务日志保存在内存中。

最后，您可以将同步设置为 OFF，这样 sqlite 从不等待写入提交到磁盘。

使用这个，我能够将导入 15M 记录所需的时间除以 5(14 分钟减少到 2.5)，记录由随机 GUID 组成，分为 5 列，使用中间的三个列作为索引:

b40c1c2f    912c    46c7    b7a0    3a7d8da724c1
9c1cdf2e    e2bc    4c60    b29d    e0a390abfd26
b9691a9b    b0db    4f33    a066    43cb4f7cf873
01a360aa    9e2e    4643    ba1f    2aae3fd013a6
f1391f8b    f32c    45f0    b137    b99e6c299528

因此，要尝试此操作，我建议您将所有说明放在某个文件中，例如 import_test:

pragma journal_mode=memory;
pragma synchronous=0;
pragma cache_size=500000;
.mode tabs
.import variations_build_36_ens-54.tab ensembl_vf_b36

那就试试吧:

time sqlite3 test_import.db < import_test

编辑

这是在此答案之后对巴勃罗(OP)评论的回复(作为评论太长了):
我(受过教育的)猜测是:

因为 .import 本身不是 sql，
没什么关系
交易，我什至倾向于
以为是写去
甚至比你拥有这一切还要快
在一次“正常”交易中完成；
和，

如果你有足够的内存
分配，然后设置
我建议的环境，真实的
(时间) pig 在这里读公寓
文件，然后写入最终内容
的数据库，因为什么
发生在两者之间 非常发生
快速地;即足够快
没有多少时间通过优化来获得
当你比较这种潜力时
通过(可能)在磁盘 I/O 上花费的不可压缩时间获得 yield 。

如果我错了，尽管为了我自己的利益，我很高兴听到为什么。

编辑 2

我在 .import 期间设置索引和在 .import 完成后立即添加索引之间进行了比较测试。我使用相同的技术生成由拆分的随机 UUID 组成的 15M 记录:

import csv, uuid
w = csv.writer(open('bla.tab', 'wb'), dialect='excel-tab')
for i in xrange(15000000):
    w.writerow(str(uuid.uuid4()).split('-'))

然后我测试了导入前后创建的索引(这里的索引是在之后创建的):

pragma journal_mode=memory;
pragma synchronous=0;
pragma cache_size=500000;
create table test (f1 text, f2 text, f3 text, f4 text, f5 text);
CREATE INDEX test_idx on test (f2, f3, f4);
.mode tabs
.import bla.tab test

所以这里是添加索引的时间 之前 :

[someone@somewhere ~]$ time sqlite3 test_speed.sqlite < import_test 
memory

real   2m58.839s
user   2m21.411s
sys    0m6.086s

添加索引时 后 :

[someone@somewhere ~]$ time sqlite3 test_speed.sqlite < import_test 
memory

real   2m19.261s
user   2m12.531s
sys    0m4.403s

您看到“用户”时间差(~9 秒)如何不考虑完整时间差(~40 秒)？ I 对我来说，这意味着之前创建索引时会发生一些额外的 I/O，因此我错误地认为所有操作都在内存中完成而没有额外的 I/O。

结论:之后创建索引，您将拥有更好的导入时间(正如 Donal 所提到的)。