回答

收藏

提高 SQLite 的每秒插入性能

技术问答 技术问答 218 人阅读 | 0 人回复 | 2023-09-12

优化 SQLite 很棘手。C 应用程序的批量插入性能可以从每秒 85 插入到每秒 96以上,000 插入不等!! Y) g# V1 p1 @+ Z# C# |% Q
背景:我们使用 SQLite 作为桌面应用程序的一部分。在 中存储了大量的配置数据XML 文件中的这些数据被分析并加载到 SQLite 数据库,以便在应用程序初始化时进一步处理。SQLite 非常适合这种情况,因为它速度快,不需要特殊配置,数据库作为单个文件存储在磁盘上。( ?& }$ _# H+ P
理由:    一开始,我对我看到的表现感到失望。事实证明,SQLite 的性能可能会有很大的不同(批量插入和选择),这取决于数据库的配置和您使用 API 的方式。找出所有的选项和技术都不是小事,所以我认为创建这个社区 wiki 条目以和 Stack Overflow 读者分享结果以节省同样调查的麻烦是明智的。: G* E0 V# F( ^* U6 h
实验:与其简单谈一般意义上的性能技能(即“使用事务!”),我觉得最好写一些  C 代码并实际测量各种选项的影响。我们将从一些简单的数据开始:
9 z2 V+ b8 N; ^0 z/ a9 M% o一个 28 MB 的 TAB 分隔的文本文件(约865,000 条记录),包括多伦多市完整的交通时间表: {3 a% Q" _# g( ^0 {: w
我的测试机正在运行 Windows XP 的 3.60 GHz P4。  B! d4 n3 C7 G. p1 a, ^  }
该代码使用Visual C   2005 编译成完全优化(/Ox) 和 Favor Fast Code (/Ot) 发布。! j+ f' U5 P3 |, d$ Y# I
我正在使用直接编译到我的测试应用程序中的 SQLite合并。我碰巧拥有 SQLite 版本有点旧(3.6.7),但我怀疑这些结果将与最新版本相当(如果您不这么认为,请发表评论)。
让我们写一些代码!
5 ?% f( N2 U  D) l7 C6 d/ m- U! X代码:一个简单的 C 程序,逐行读取文本文件,将字符串分成值,然后将数据插入 SQLite 数据库。数据库是在这个基线版本的代码中创建的,但我们实际上不会插入数据:
5 v# d" A' V2 Q
    /*************************************************************    Baseline code to experiment with SQLite performance.    Input data is a 28 MB TAB-delimited text file of the    complete Toronto Transit System schedule/route info    from http://www.toronto.ca/open/datasets/ttc-routes/**************************************************************/#include #include #include #include #include "sqlite3.h"#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY,Route_ID TEXT,Branch_Code TEXT,Version INTEGER,Stop INTEGER,Vehicle_Index INTEGER,Day Integer,Time TEXT)"#define BUFFER_SIZE 256int main(int argc,char **argv)    sqlite3 * db;    sqlite3_stmt * stmt;    char * sErrMsg = 0;    char * tail = 0;    int nRetCode;    int n = 0;    clock_t cStartClock;    FILE * pFile;    char sInputBuf [BUFFER_SIZE] = "\0";    char * sRT = 0;  /* Route */    char * sBR = 0;  /* Branch */    char * sVR = 0;  /* Version */    char * sST = 0;  /* Stop Number */    char * sVI = 0;  /* Vehicle */    char * sDT = 0;  /* Date */    char * sTM = 0;  /* Time */    char sSQL [BUFFER_SIZE] = "\0";    /*********************************************   /   /   /   /   /   /    /   /   /  /   /    /   /   /   /   /   /   /   /   /   /   /   /  / /  /  / /  /  /  /  /   /     /    /  / / /  /  /   /    /     /   /  /  / / / / / / / / /  / / / / / /  /  /    /    /   /  /    /          /  / / / / / / / / / / / / / / / / / / / / / / / /  / / /   / / / /     /  / / / / / / / / / / / / / / / / / / /  / / / / / /       / / / / / / / / / /  / /  / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / /  / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / / /  / / / / / / / / * Open the Database and create the Schema */    sqlite3_open(DATABASE,&db);    sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);    /*********************************************/    /* Open input file and import into Database*/    cStartClock = clock();     pFile = fopen (INPUTDATA,"r");    while (!feof(pFile))              fgets (sInputBuf,BUFFER_SIZE,pFile);        sRT = strtok (sInputBuf,"\t");     * Get Route */          sBR = strtok (NULL,"\t");            * Get Branch */          sVR = strtok (NULL,"\t");       * Get Version */          sST = strtok (NULL,"\t");       * Get Stop Number */          sVI = strtok (NULL,"\t");       * Get Vehicle */          sDT = strtok (NULL,"\t");       * Get Date */          sTM = strtok (NULL,"\t");       * Get Time */          /* ACTUAL INSERT WILL GO HERE */          n  fclose (pFile);    printf("Imported %d records in %4.2f seconds\n",n,(clock() - cStartClock) / (double)CLOCKS_PER_SEC);    sqlite3_close(db);    return 0;}
    1 L6 q0 [  t# d7 ]* E( |
“控制”实际上,按原始操作代码不执行任何数据库操作,但它会让我们了解原始 C 文件 I/O 和字符串处理有多快。  o, q" c* ^0 O6 a
0.94秒导入864913条记录1 `' f$ C; x- J8 m) m0 C
伟大的!如果我们实际上没有插入 ,我们可以每秒插入 920,000 次:-)$ r/ n) P/ C" J* h" ^
最坏情况我们将使用从文件中读取的值生成 SQL 字符串,并使用 sqlite3_exec 调用该 SQL 操作:
1 j9 u" ?0 X9 l6 p- j
    sprintf(sSQL,"INSERT INTO TTC VALUES (NULL,'%s','%s','%s','%s','%s','%s','%s')",sRT,sBR,sVR,sST,sVI,sDT,sTM);sqlite3_exec(db,sSQL,NULL,NULL,&sErrMsg);1 n9 V3 ]  r- T1 B# _
这会很慢,因为每次插入 SQL 都会被编译成 VDBE 代码,每次插入都会发生在自己的事务中。有多慢?7 b) m( L5 R4 M1 z# c: X
在 9933. 864913 条记录在61 秒内进口
* ?% |1 ^5 {: W/ o1 v
哎呀!2小时45分钟!每秒插入只有85 次。
2 D9 X, y, e; S9 ?! \使用事务默认情况下,SQLite 将评估唯一事务中的每一个 INSERT / UPDATE 语句。如有大量插入,建议在事务中包装您的操作:
% Q; x" k5 b2 R: U
    sqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);pFile = fopen (INPUTDATA,"r");while (!feof(pFile))    ...}fclose (pFile);sqlite3_exec(db,"END TRANSACTION",NULL,NULL,&sErrMsg);
    6 ~9 Q. {1 v" R4 k0 e4 d3 D; v! _
在 38. 864913
. \  [3 V* |0 p; T7 z8 Z
这样更好。我们所有的插入包装都可以在事务中提高我们的性能每秒 23,000 次插入。
; z5 Y  W& k7 s使用准备好的句子使用事务是一个巨大的改进,但如果我们一次又一次地使用相同的 SQL,然后重新编译 SQL 句子毫无意义。让我们用它。sqlite3_prepare_v一次编译我们的  SQL 句子,然后使用以下命令将我们的参数绑定到句子中sqlite3_bind_text:9 x& L$ B+ L  F; }0 Y
    /* Open input file and import into the database */cStartClock = clock();sprintf(sSQL,"INSERT INTO TTC VALUES (NULL,@RT,@BR,@VR,@ST,@VI,@DT,@TM)");sqlite3_prepare_v2(db, sSQL,BUFFER_SIZE,&stmt,&tail);sqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);pFile = fopen (INPUTDATA,"r");while (!feof(pFile))    fgets (sInputBuf,BUFFER_SIZE,pFile);    sRT = strtok (sInputBuf,"\t");   /* Get Route */    sBR = strtok (NULL,"\t");      * Get Branch */    sVR = strtok (NULL,"\t");      * Get Version */    sST = strtok (NULL,"\t");      * Get Stop Number */    sVI = strtok (NULL,"\t");      * Get Vehicle */    sDT = strtok (NULL,"\t");      * Get Date */    sTM = strtok (NULL,"\t");      * Get Time */    sqlite3_bind_text(stmt,1,sRT,-1,SQLITE_TRANSIENT);    sqlite3_bind_text(stmt,2,sBR,-1,SQLITE_TRANSIENT);    sqlite3_bind_text(stmt,3,sVR,-1,SQLITE_TRANSIENT);    sqlite3_bind_text(stmt,4,sST,-1,SQLITE_TRANSIENT);    sqlite3_bind_text(stmt,5,sVI,-1,SQLITE_TRANSIENT);    sqlite3_bind_text(stmt,6,sDT,-1,SQLITE_TRANSIENT);    sqlite3_bind_text(stmt,7,sTM,-1,SQLITE_TRANSIENT);    sqlite3_step(stmt);    sqlite3_clear_bindings(stmt);    sqlite3_reset(stmt);    n  ;}fclose (pFile);sqlite3_exec(db,"END TRANSACTION",NULL,NULL,&sErrMsg);printf("Imported %d records in %4.2f seconds\n",n,(clock() - cStartClock) / (double)CLOCKS_PER_SEC);sqlite3_finalize(stmt);sqlite3_close(db);return 0;* [9 X* P# P( L
16.864913记录27秒导入
0 s/ `9 n" Y- y, ~4 M
好的!还有一点代码(别忘了调用)sqlite3_clear_bindingsand sqlite3_reset),但是我们的性能提高了一倍多,达到了每秒 53,000 次插入。
4 \  D1 g0 H1 C$ c* N( d) aPRAGMA 同步 = 关闭默认情况下,SQLite 将在发出操作系统级别的写入命令后暂停。这确保数据被写入磁盘。通过设置synchronous = OFF,我们指示 SQLite 简单地将数据交给操作系统,然后继续。如果计算机在数据写入磁盘之前崩溃(或电源故障),数据库文件可能会损坏:
- d& @0 `# f: J5 r' C! }- B; |
    /* Open the database and create the schema */sqlite3_open(DATABASE,&db);sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);sqlite3_exec(db,&quotRAGMA synchronous = OFF",NULL,NULL,&sErrMsg);
    : {7 e$ z; {8 S, d! [( k
12.864913记录导入41秒
+ B9 k% N9 X: F1 W% L
现在改进更小了,但我们每秒最多可以插入 69,600 次。9 D" m+ t- h; ]9 }. C% w
PRAGMA journal_mode = MEMORY回滚日志通过评估存储在内存中PRAGMA journal_mode = MEMORY。您的事务会更快,但如果您在事务期间停电或程序崩溃,您的数据库可能会因事务部分完成而损坏:3 _/ N6 }- P9 `$ n! {
    /* Open the database and create the schema */sqlite3_open(DATABASE,&db);sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);sqlite3_exec(db,&quotRAGMA journal_mode = MEMORY",NULL,NULL,&sErrMsg);
    $ ]" n( }  {' E2 U% W' E
13.864913记录50秒导入& ^& H' T% I$ E, X& n4 S- U
比以前的优化慢一点,每秒 64,000 次插入。
7 V) J/ C9 v4 o! y" y0 cPRAGMA synchronous = OFF andPRAGMA journal_mode = MEMORY让我们结合前两个优化。这有点危险(崩溃时),但我们只导入数据(不是银行):
, O. k1 \' M+ B( p
    /* Open the database and create the schema */sqlite3_open(DATABASE,&db);sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);sqlite3_exec(db,&quotRAGMA synchronous = OFF",NULL,NULL,&sErrMsg);sqlite3_exec(db,&quotRAGMA journal_mode = MEMORY",NULL,NULL,&sErrMsg);
    2 U3 f, V. T2 |1 T0 j
12.00 秒导入 864913 条记录! t& k# ]) K& v3 v1 s; j6 t
极好的!我们每秒可插入72000 次。  \; T( `; D% o; g
使用内存数据库只是为了好玩,让我们在之前所有优化的基础上重新定义数据库文件名,让我们完全在 RAM 中工作:: t% }& V  H9 D5 V
    #define DATABASE ":memory:"4 _" _8 \- V1 |+ J! }2 f1 Y7 u
10.94秒导入864913条记录5 b" [( b2 B$ K/ G" v. H
将我们的数据库存储在 RAM 不是很实用,但令人印象深刻的是我们79,000 可以每秒插入一次。
5 K" g& [7 k; G( F" {6 v! T重构 C 代码虽然不是专门的 SQLite 改进,但我不喜欢循环char*额外赋值操作。while让我们快速重构代码,输出 strtok()直接传输到sqlite3_bind_text()让编译器试着为我们加速:
1 V; ~2 h: I0 x/ _: W; a( J  G9 a; f
    pFile = fopen (INPUTDATA,"r");while (!feof(pFile))    fgets (sInputBuf,BUFFER_SIZE,pFile);    sqlite3_bind_text(stmt,1,strtok (sInputBuf,"\t"),-1,SQLITE_TRANSIENT); /* Get Route */    sqlite3_bind_text(stmt,2,strtok (NULL,"\t"),-1,SQLITE_TRANSIENT);    /* Get Branch */    sqlite3_bind_text(stmt,3,strtok (NULL,"\t"),-1,SQLITE_TRANSIENT);    /* Get Version */    sqlite3_bind_text(stmt,4,strtok (NULL,"\t"),-1,SQLITE_TRANSIENT);    /* Get Stop Number */    sqlite3_bind_text(stmt,5,strtok (NULL,"\t"),-1,SQLITE_TRANSIENT);    /* Get Vehicle */    sqlite3_bind_text(stmt,6,strtok (NULL,"\t"),-1,SQLITE_TRANSIENT);    /* Get Date */    sqlite3_bind_text(stmt,7,strtok (NULL,"\t"),-1,SQLITE_TRANSIENT);    /* Get Time */    sqlite3_step(stmt);      * Execute the SQL Statement */    sqlite3_clear_bindings(stmt);    /* Clear bindings */    sqlite3_reset(stmt);      * Reset VDBE */    n  ;}fclose (pFile);
    : C- S1 ^( K( ?7 h& n; ~
注:我们回到使用真实的数据库文件。内存数据库速度快,但不一定实用" L2 C$ `8 F8 N6 ^4 T
8.94秒导入864913条记录
( @9 @! D/ `3 d
我们可以轻微重构参数绑定中使用的字符串处理代码每秒插入 96,700 次。我想我可以肯定地说很快。当我们开始调整其他变量(即页面大小、索引创建等)时,这将是我们的基准。3 m+ w2 n( C4 K# K/ \- w
摘要(到目前为止)我希望你还在我身边!之所以开始走这条路,是因为 SQLite 的批量插入性能变化如此之大,需要做哪些改变来加速我们的操作并不总是很明显。使用相同的编译器(和编译器选项)和相同版本的 SQLite 我们优化了我们的代码和相同的数据SQLite 的使用,从每秒 85 次插入的最坏情况到每秒 96以上,000 次插入!6 S3 w1 v8 |  q% N+ O; H/ }
CREATE INDEX 然后 INSERT 与 INSERT 然后 CREATE INDEX我们开始衡量SELECT在性能之前,我们知道我们将创建索引。下面的答案之一建议在批量插入时更快地创建索引(而不是先创建索引,然后插入数据)。让我们试试:4 u+ J) @3 w% U1 g& F4 B. t8 P
创建索引然后插入数据/ s" [2 e  s; D% N! T& l+ y6 T& G5 j
    sqlite3_exec(db,"CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);sqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);...
    ( T4 p+ ~" D+ G% P
18.13秒导入864913条记录# [/ }5 I. N$ o
插入数据,然后创建索引
& w, B2 `$ s4 V
    ...sqlite3_exec(db,"END TRANSACTION",NULL,NULL,&sErrMsg);sqlite3_exec(db,"CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);1 z) J7 A  F: p$ x; E' p* y6 g7 e
13.864913记录导入66秒+ R+ k. S8 T6 V+ P
正如预期的那样,如果为列建立索引,批量插入会更慢,但如果在插入数据后创建索引,它确实会有所不同。我们的无索引基线每秒 96,000 次插入。首先创建索引,然后每秒插入数据47700 ,然后每秒插入633300 。- w) d8 V# j* t& Y2 y
我很高兴为其他场景提供建议......而且很快就会为 SELECT 查询类似数据的编译。
: s' e1 H  P$ Q* Y) k9 B! `                                                                ; C. U, N! C8 j7 E) Z0 H- U
    解决方案:                                                               
4 Y' w. v! u& U9 f                                                                几个提示:
1 g# z: d  m( v+ T[ol]在事务中插入/更新。
8 D0 Q5 L* d0 i* o7 v/ X旧版 SQLite - 考虑一种不那么偏执的日志模式 ( pragma journal_mode)。有NORMAL,然后有OFF,如果您不担心操作系统崩溃会损坏数据库,则可以显著提高插入速度。如果您的应用程序崩溃,数据应该没有问题。请注意,在更新版本中OFF/MEMORY应用程序级崩溃的设置是不安全的。% v9 E% _$ d; n9 e% E
使用页面的大小也会有所不同(PRAGMA page_size)。由于更大的页面保存在内存中,,因为更大的页面保存在内存中。请注意,您的数据库将使用更多的内存。! A4 d1 Q: y% ?& v/ r; ]9 N3 H9 z& x
如果您有索引,请考虑CREATE INDEX在完成所有插入后调用。这比创建索引然后进行插入要快得多。
7 [. t  Y5 ?% |( y  b+ Y* |% o6 d如果您对 SQLite 有并发访问权,必须非常小心,因为当写入完成时,整个数据库被锁定,尽管可能有多个读取器,但写入将被锁定。在更新的 SQLite 在版本中本中WAL,这已经改善了。3 I0 H- `2 m' a: e! n
节省空间…较小的数据库运行得更快。例如,如果您有正确的键值,请尽量将键设置为 an INTEGER PRIMARY KEY,唯一隐含在替换表中的行号列。
+ }6 B* W9 n; P5 m如果使用多个线程,可以尝试使用共享页面缓存,允许加载页面在线程之间共享,避免昂贵 I/O 调用。
2 M% |5 }" S' x! o/ h3 x, Z1 g不要使用!feof(file)![/ol]
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则