回答

收藏

提高 SQLite 的每秒插入性能

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

优化 SQLite 很棘手。C 应用程序的批量插入性能可以从每秒 85 插入到每秒 96以上,000 插入不等!
7 e* E4 m+ {- }( h, @& O! M# K背景:我们使用 SQLite 作为桌面应用程序的一部分。在 中存储了大量的配置数据XML 文件中的这些数据被分析并加载到 SQLite 数据库,以便在应用程序初始化时进一步处理。SQLite 非常适合这种情况,因为它速度快,不需要特殊配置,数据库作为单个文件存储在磁盘上。
* h+ d7 d. m3 b7 z( J  n1 c+ l2 ~9 I理由:    一开始,我对我看到的表现感到失望。事实证明,SQLite 的性能可能会有很大的不同(批量插入和选择),这取决于数据库的配置和您使用 API 的方式。找出所有的选项和技术都不是小事,所以我认为创建这个社区 wiki 条目以和 Stack Overflow 读者分享结果以节省同样调查的麻烦是明智的。# |: H) M" B5 o6 c' y& \
实验:与其简单谈一般意义上的性能技能(即“使用事务!”),我觉得最好写一些  C 代码并实际测量各种选项的影响。我们将从一些简单的数据开始:* L1 o$ K* h4 k7 ?6 i+ T, ~, t
一个 28 MB 的 TAB 分隔的文本文件(约865,000 条记录),包括多伦多市完整的交通时间表
. @8 r% @, w* n; m4 h& U" w我的测试机正在运行 Windows XP 的 3.60 GHz P4。
, A- o! D, Y8 Z( ]4 l" h8 ?& r该代码使用Visual C   2005 编译成完全优化(/Ox) 和 Favor Fast Code (/Ot) 发布。- D3 V, ?4 }8 f; ?/ e
我正在使用直接编译到我的测试应用程序中的 SQLite合并。我碰巧拥有 SQLite 版本有点旧(3.6.7),但我怀疑这些结果将与最新版本相当(如果您不这么认为,请发表评论)。
让我们写一些代码!
# L' U1 W6 I1 p/ q代码:一个简单的 C 程序,逐行读取文本文件,将字符串分成值,然后将数据插入 SQLite 数据库。数据库是在这个基线版本的代码中创建的,但我们实际上不会插入数据:% [0 L- j# J7 ?5 H9 t$ i
    /*************************************************************    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;}' g' a: d! I6 B2 l, i. H) ~/ o+ Y6 L
“控制”实际上,按原始操作代码不执行任何数据库操作,但它会让我们了解原始 C 文件 I/O 和字符串处理有多快。0 J5 x5 X* U9 n6 k; _! Y! s/ L# i- ~
0.94秒导入864913条记录
4 r: C5 ~$ ^; {/ r" P
伟大的!如果我们实际上没有插入 ,我们可以每秒插入 920,000 次:-)4 i; y: J& y; O( }5 d! K4 t0 B
最坏情况我们将使用从文件中读取的值生成 SQL 字符串,并使用 sqlite3_exec 调用该 SQL 操作:
9 R$ y; T8 v7 J4 @
    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);
    2 V1 z0 ^4 }2 b/ k& ]2 p. S
这会很慢,因为每次插入 SQL 都会被编译成 VDBE 代码,每次插入都会发生在自己的事务中。有多慢?7 |1 o+ f; }' y8 Y
在 9933. 864913 条记录在61 秒内进口7 s- H' @  ]8 ]7 x+ \4 o: I
哎呀!2小时45分钟!每秒插入只有85 次。/ Y  X& t" T9 ~+ A" l& I
使用事务默认情况下,SQLite 将评估唯一事务中的每一个 INSERT / UPDATE 语句。如有大量插入,建议在事务中包装您的操作:5 C9 p! F. ]0 c# d9 p
    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);
    ' R* f& [  Q- j, ]$ G
在 38. 864913 0 z. C! A5 J5 d! k% a' i0 G
这样更好。我们所有的插入包装都可以在事务中提高我们的性能每秒 23,000 次插入。
8 n& O. u. Y& Q! I! K! s使用准备好的句子使用事务是一个巨大的改进,但如果我们一次又一次地使用相同的 SQL,然后重新编译 SQL 句子毫无意义。让我们用它。sqlite3_prepare_v一次编译我们的  SQL 句子,然后使用以下命令将我们的参数绑定到句子中sqlite3_bind_text:9 i7 u# {& g+ p
    /* 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;
    ; q/ l& e  W8 K* m5 w* F
16.864913记录27秒导入8 x2 |: K% c! ^  s: k4 ^+ Z
好的!还有一点代码(别忘了调用)sqlite3_clear_bindingsand sqlite3_reset),但是我们的性能提高了一倍多,达到了每秒 53,000 次插入。3 Q4 G- [  o! c& z7 Q1 v) P
PRAGMA 同步 = 关闭默认情况下,SQLite 将在发出操作系统级别的写入命令后暂停。这确保数据被写入磁盘。通过设置synchronous = OFF,我们指示 SQLite 简单地将数据交给操作系统,然后继续。如果计算机在数据写入磁盘之前崩溃(或电源故障),数据库文件可能会损坏:
3 j0 q7 e/ W0 V' g! _* _* U
    /* 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);
    & I/ u+ a( K% i+ d2 {) H2 m' d& C; @- c
12.864913记录导入41秒
8 ]4 F# g* P+ ]1 X3 W
现在改进更小了,但我们每秒最多可以插入 69,600 次。, s3 [* ~% a7 d, L  [
PRAGMA journal_mode = MEMORY回滚日志通过评估存储在内存中PRAGMA journal_mode = MEMORY。您的事务会更快,但如果您在事务期间停电或程序崩溃,您的数据库可能会因事务部分完成而损坏:
9 J, _" e, ~9 ?
    /* 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);
    # ^, T$ k3 x( {% f* |
13.864913记录50秒导入
9 o8 {9 D& `5 w7 P/ C. T1 m
比以前的优化慢一点,每秒 64,000 次插入。
2 d$ u% T, R5 B4 Q- G, X  GPRAGMA synchronous = OFF andPRAGMA journal_mode = MEMORY让我们结合前两个优化。这有点危险(崩溃时),但我们只导入数据(不是银行):
- `( y5 r/ s" E5 [! g. S  e
    /* 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);. j, Z- Z% V7 M
12.00 秒导入 864913 条记录7 t) i# B8 ]5 m* \1 B
极好的!我们每秒可插入72000 次。
, w( P- {/ c5 L/ v: O: `使用内存数据库只是为了好玩,让我们在之前所有优化的基础上重新定义数据库文件名,让我们完全在 RAM 中工作:
8 Q2 U/ Q' m! V. U8 a8 }" D  A
    #define DATABASE ":memory:"! {, X) }9 g0 H3 O: A% j( b4 j
10.94秒导入864913条记录
5 d, L3 J2 J  y# M9 b
将我们的数据库存储在 RAM 不是很实用,但令人印象深刻的是我们79,000 可以每秒插入一次。$ m: |" v4 \# ]5 g* T" u7 C: R9 K
重构 C 代码虽然不是专门的 SQLite 改进,但我不喜欢循环char*额外赋值操作。while让我们快速重构代码,输出 strtok()直接传输到sqlite3_bind_text()让编译器试着为我们加速:0 T- r; C6 m5 o
    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);1 A% v/ ^8 c" u& g- x" E* O. U
注:我们回到使用真实的数据库文件。内存数据库速度快,但不一定实用
, S; X# v: _: V. y8.94秒导入864913条记录! W5 I1 ~2 {& ?  Y8 z8 b* n0 q
我们可以轻微重构参数绑定中使用的字符串处理代码每秒插入 96,700 次。我想我可以肯定地说很快。当我们开始调整其他变量(即页面大小、索引创建等)时,这将是我们的基准。
4 K) P; Y5 g* h# R2 Z摘要(到目前为止)我希望你还在我身边!之所以开始走这条路,是因为 SQLite 的批量插入性能变化如此之大,需要做哪些改变来加速我们的操作并不总是很明显。使用相同的编译器(和编译器选项)和相同版本的 SQLite 我们优化了我们的代码和相同的数据SQLite 的使用,从每秒 85 次插入的最坏情况到每秒 96以上,000 次插入!4 |( E) h. H& s
CREATE INDEX 然后 INSERT 与 INSERT 然后 CREATE INDEX我们开始衡量SELECT在性能之前,我们知道我们将创建索引。下面的答案之一建议在批量插入时更快地创建索引(而不是先创建索引,然后插入数据)。让我们试试:
; z; o6 _  z, E5 c! t% D创建索引然后插入数据
+ u" `# D# `) t6 y# R2 c. h! @# S! W
    sqlite3_exec(db,"CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);sqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);...
    ( u1 [& m1 k- F& E4 V
18.13秒导入864913条记录6 A! `; ]0 I+ ?" H4 H) ?0 s$ T
插入数据,然后创建索引/ Q+ ?* Y+ C( E8 \
    ...sqlite3_exec(db,"END TRANSACTION",NULL,NULL,&sErrMsg);sqlite3_exec(db,"CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);
    & K& e8 W7 H# z& ^  J- q
13.864913记录导入66秒/ V# M) D1 N0 I; o$ Y
正如预期的那样,如果为列建立索引,批量插入会更慢,但如果在插入数据后创建索引,它确实会有所不同。我们的无索引基线每秒 96,000 次插入。首先创建索引,然后每秒插入数据47700 ,然后每秒插入633300 。+ k/ K; M- P* Y+ h( F& F. e% Q
我很高兴为其他场景提供建议......而且很快就会为 SELECT 查询类似数据的编译。# `: G$ P+ q6 o; Q1 J
                                                               
* G% N7 i4 L+ p8 L+ O    解决方案:                                                                ! _- }8 e- z2 T# x. y* B
                                                                几个提示:' w* }" r2 L1 c. K* s9 {( i8 @
[ol]在事务中插入/更新。7 W! \7 f, P+ H$ g! P
旧版 SQLite - 考虑一种不那么偏执的日志模式 ( pragma journal_mode)。有NORMAL,然后有OFF,如果您不担心操作系统崩溃会损坏数据库,则可以显著提高插入速度。如果您的应用程序崩溃,数据应该没有问题。请注意,在更新版本中OFF/MEMORY应用程序级崩溃的设置是不安全的。
+ W6 p: X* K) E使用页面的大小也会有所不同(PRAGMA page_size)。由于更大的页面保存在内存中,,因为更大的页面保存在内存中。请注意,您的数据库将使用更多的内存。# F( N' H" h" K. p
如果您有索引,请考虑CREATE INDEX在完成所有插入后调用。这比创建索引然后进行插入要快得多。
$ Q# k# _. L6 J- S2 S% T% l0 q如果您对 SQLite 有并发访问权,必须非常小心,因为当写入完成时,整个数据库被锁定,尽管可能有多个读取器,但写入将被锁定。在更新的 SQLite 在版本中本中WAL,这已经改善了。+ b5 l5 t8 A" L3 e
节省空间…较小的数据库运行得更快。例如,如果您有正确的键值,请尽量将键设置为 an INTEGER PRIMARY KEY,唯一隐含在替换表中的行号列。7 j9 w3 K; l4 S
如果使用多个线程,可以尝试使用共享页面缓存,允许加载页面在线程之间共享,避免昂贵 I/O 调用。
* S, H* D  A. r: p. X不要使用!feof(file)![/ol]
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则