SQLite导入选项卡文件：.import是每行执行一次插入还是使用事务

发布时间：2020-12-12 18:56:18 所属栏目：百科来源：网络整理

导读：我从选项卡文件导入数百万行,而SQLite .import .mode选项卡非常慢.我有三个索引,所以可能是索引的缓慢.但首先我要检查.import是否将批次/所有这些行分组到一个提交中.我无法找到.import如何工作的文档.有人知道吗？如果索引是问题(我之前遇到过 mysql问题)如

我从选项卡文件导入数百万行,而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

解决方法

我相信,随着越来越多的记录被添加,构建索引的确很慢.根据您拥有的RAM,您可以告诉sqlite使用足够的内存,以便所有这些索引构建活动都在内存中完成(即没有所有I / O,否则会发生内存较少).

对于15M记录,我要说你应该将缓存大小设置为500000.

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

最后,您可以将synchronous设置为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

编辑

这是对Pablo(OP)评论后回答的答案(很长时间以作为评论)：
我的(受过教育的)猜测是：

>因为.import本身不是sql,
它并没有多少
交易,我甚至倾向于
认为它是写的去
甚至比你拥有这一切还快
在一次“正常”交易中完成;
和,
>如果你有足够的记忆力
????分配,你设置你的
????我建议的环境,真实的
????(时间)这里的猪正在阅读公寓
????文件,然后写最终内容
????数据库,因为什么
????发生在两者之间发生极端
????快速;即那里足够快
????没有太多时间通过优化获得
????当你比较这种潜力时
????在磁盘I / O上花费(可能)不可压缩的时间.

如果我错了,虽然我很高兴听到为什么为了我自己的利益.

编辑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

你看到“用户”时间差(~9s)如何不考虑全时差(~40s)？对我来说,这意味着在之前创建索引时会发生一些额外的I / O,所以我认为所有内容都是在没有额外I / O的内存中完成的.

结论：创建索引后,您将获得更好的导入时间(就像Donal提到的那样).

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!