|
优化 SQLite 很棘手。C 应用程序的批量插入性能可以从每秒 85 插入到每秒 96以上,000 插入不等!4 ?, i5 k( W- f
背景:我们使用 SQLite 作为桌面应用程序的一部分。在 中存储了大量的配置数据XML 文件中的这些数据被分析并加载到 SQLite 数据库,以便在应用程序初始化时进一步处理。SQLite 非常适合这种情况,因为它速度快,不需要特殊配置,数据库作为单个文件存储在磁盘上。
% t8 I* e* k9 W0 T" f/ Z7 G理由: 一开始,我对我看到的表现感到失望。事实证明,SQLite 的性能可能会有很大的不同(批量插入和选择),这取决于数据库的配置和您使用 API 的方式。找出所有的选项和技术都不是小事,所以我认为创建这个社区 wiki 条目以和 Stack Overflow 读者分享结果以节省同样调查的麻烦是明智的。# ?: M5 }4 Q6 P* L) I# u* k
实验:与其简单谈一般意义上的性能技能(即“使用事务!”),我觉得最好写一些 C 代码并实际测量各种选项的影响。我们将从一些简单的数据开始:
; o8 x7 s% V8 I一个 28 MB 的 TAB 分隔的文本文件(约865,000 条记录),包括多伦多市完整的交通时间表
9 C. p+ U: G* f% r# m我的测试机正在运行 Windows XP 的 3.60 GHz P4。# t- l/ L% @& W' |7 q! m
该代码使用Visual C 2005 编译成完全优化(/Ox) 和 Favor Fast Code (/Ot) 发布。1 F* z0 D/ K' p0 B/ }" c
我正在使用直接编译到我的测试应用程序中的 SQLite合并。我碰巧拥有 SQLite 版本有点旧(3.6.7),但我怀疑这些结果将与最新版本相当(如果您不这么认为,请发表评论)。让我们写一些代码!
) u+ o; C) X% `9 l" L8 ?0 z2 I代码:一个简单的 C 程序,逐行读取文本文件,将字符串分成值,然后将数据插入 SQLite 数据库。数据库是在这个基线版本的代码中创建的,但我们实际上不会插入数据:
; O. ^: f- ` N1 E7 h( M/************************************************************* 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 D" o4 c0 {9 ]9 u
“控制”实际上,按原始操作代码不执行任何数据库操作,但它会让我们了解原始 C 文件 I/O 和字符串处理有多快。- |0 e! R; O- Y
0.94秒导入864913条记录5 Z R i& J( U
伟大的!如果我们实际上没有插入 ,我们可以每秒插入 920,000 次:-)# R) z0 n! r$ h- p4 k* K4 L( F
最坏情况我们将使用从文件中读取的值生成 SQL 字符串,并使用 sqlite3_exec 调用该 SQL 操作:
2 U1 T/ ~! S! ^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);7 g4 z" v, f j) v9 ^3 P* a, B
这会很慢,因为每次插入 SQL 都会被编译成 VDBE 代码,每次插入都会发生在自己的事务中。有多慢?
9 b+ X9 V# _" p, C0 U& i n" ]! }8 \在 9933. 864913 条记录在61 秒内进口
# ]8 m+ A% ~' I3 e0 u3 [哎呀!2小时45分钟!每秒插入只有85 次。 _9 M7 q5 Z& n" A1 @
使用事务默认情况下,SQLite 将评估唯一事务中的每一个 INSERT / UPDATE 语句。如有大量插入,建议在事务中包装您的操作:
1 O% F# a# J- w* J/ w# e- lsqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);pFile = fopen (INPUTDATA,"r");while (!feof(pFile)) ...}fclose (pFile);sqlite3_exec(db,"END TRANSACTION",NULL,NULL,&sErrMsg);7 L2 ?1 C" F7 N+ Q5 R( K! P
在 38. 864913
& w m* K$ A+ K' ]: N$ n这样更好。我们所有的插入包装都可以在事务中提高我们的性能每秒 23,000 次插入。% h8 O3 c4 a" S& l0 J" {" o% s; V
使用准备好的句子使用事务是一个巨大的改进,但如果我们一次又一次地使用相同的 SQL,然后重新编译 SQL 句子毫无意义。让我们用它。sqlite3_prepare_v一次编译我们的 SQL 句子,然后使用以下命令将我们的参数绑定到句子中sqlite3_bind_text:6 e5 i; ^0 M f
/* 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;" n# i6 {( u0 G9 R5 l
16.864913记录27秒导入. S- M7 {+ ^$ F0 U9 k7 G
好的!还有一点代码(别忘了调用)sqlite3_clear_bindingsand sqlite3_reset),但是我们的性能提高了一倍多,达到了每秒 53,000 次插入。
# i, t# n: v s0 JPRAGMA 同步 = 关闭默认情况下,SQLite 将在发出操作系统级别的写入命令后暂停。这确保数据被写入磁盘。通过设置synchronous = OFF,我们指示 SQLite 简单地将数据交给操作系统,然后继续。如果计算机在数据写入磁盘之前崩溃(或电源故障),数据库文件可能会损坏:
/ e/ Y: ]1 [6 V# x; k9 A' S% i/* Open the database and create the schema */sqlite3_open(DATABASE,&db);sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);sqlite3_exec(db," RAGMA synchronous = OFF",NULL,NULL,&sErrMsg);" g/ N. H2 S3 Y& U
12.864913记录导入41秒" J8 @' R( D3 T6 g, A1 {3 b
现在改进更小了,但我们每秒最多可以插入 69,600 次。: }# R% d" Z" v) a
PRAGMA journal_mode = MEMORY回滚日志通过评估存储在内存中PRAGMA journal_mode = MEMORY。您的事务会更快,但如果您在事务期间停电或程序崩溃,您的数据库可能会因事务部分完成而损坏: K5 S9 Y1 ^' V! R4 p5 ^
/* Open the database and create the schema */sqlite3_open(DATABASE,&db);sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);sqlite3_exec(db," RAGMA journal_mode = MEMORY",NULL,NULL,&sErrMsg);: F2 z, h( @3 o& U
13.864913记录50秒导入2 W" o9 D. @. @' p- t) r4 _
比以前的优化慢一点,每秒 64,000 次插入。
& l+ L8 i" h9 N! o9 F- e8 f6 NPRAGMA synchronous = OFF andPRAGMA journal_mode = MEMORY让我们结合前两个优化。这有点危险(崩溃时),但我们只导入数据(不是银行):' O" d0 }4 q- c3 B; c
/* Open the database and create the schema */sqlite3_open(DATABASE,&db);sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);sqlite3_exec(db," RAGMA synchronous = OFF",NULL,NULL,&sErrMsg);sqlite3_exec(db," RAGMA journal_mode = MEMORY",NULL,NULL,&sErrMsg);$ ^6 O6 h6 l& M, c5 E
12.00 秒导入 864913 条记录) A; N/ O% N. {5 C
极好的!我们每秒可插入72000 次。
$ E! w- Q4 B* a( H {5 [ `7 R使用内存数据库只是为了好玩,让我们在之前所有优化的基础上重新定义数据库文件名,让我们完全在 RAM 中工作:
0 c0 J) z' K1 v- u9 C' Q! T#define DATABASE ":memory:"6 f* S# ?0 W7 S: h. W
10.94秒导入864913条记录1 D+ E# v6 T* T! {8 h
将我们的数据库存储在 RAM 不是很实用,但令人印象深刻的是我们79,000 可以每秒插入一次。+ C( A. y, r( i9 A0 L3 A
重构 C 代码虽然不是专门的 SQLite 改进,但我不喜欢循环char*额外赋值操作。while让我们快速重构代码,输出 strtok()直接传输到sqlite3_bind_text()让编译器试着为我们加速:
' I# W; q! t3 ?' b& w# Z) q7 GpFile = 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);' u" b6 C& K! h
注:我们回到使用真实的数据库文件。内存数据库速度快,但不一定实用- K" O( \: G" ?! u+ b) x$ A/ k
8.94秒导入864913条记录
0 c8 p0 ?, ]4 b我们可以轻微重构参数绑定中使用的字符串处理代码每秒插入 96,700 次。我想我可以肯定地说很快。当我们开始调整其他变量(即页面大小、索引创建等)时,这将是我们的基准。- N( Y* Z, z, Q# g
摘要(到目前为止)我希望你还在我身边!之所以开始走这条路,是因为 SQLite 的批量插入性能变化如此之大,需要做哪些改变来加速我们的操作并不总是很明显。使用相同的编译器(和编译器选项)和相同版本的 SQLite 我们优化了我们的代码和相同的数据SQLite 的使用,从每秒 85 次插入的最坏情况到每秒 96以上,000 次插入!! ~* g" ~0 N4 }' g
CREATE INDEX 然后 INSERT 与 INSERT 然后 CREATE INDEX我们开始衡量SELECT在性能之前,我们知道我们将创建索引。下面的答案之一建议在批量插入时更快地创建索引(而不是先创建索引,然后插入数据)。让我们试试:6 [( J* B/ ?' M$ C; F
创建索引然后插入数据: a2 y8 p! r3 t5 w
sqlite3_exec(db,"CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);sqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);..., I: t8 d( }# U3 g! R8 a
18.13秒导入864913条记录4 P& {. l6 O0 B
插入数据,然后创建索引2 ~7 m- l& ], b' c
...sqlite3_exec(db,"END TRANSACTION",NULL,NULL,&sErrMsg);sqlite3_exec(db,"CREATE INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);
4 P. s2 V( T4 s3 a: F" l 13.864913记录导入66秒
9 s" P1 _' b6 V" _! V/ k, H$ L正如预期的那样,如果为列建立索引,批量插入会更慢,但如果在插入数据后创建索引,它确实会有所不同。我们的无索引基线每秒 96,000 次插入。首先创建索引,然后每秒插入数据47700 ,然后每秒插入633300 。
5 p, Z$ J1 k* O0 [# C# I我很高兴为其他场景提供建议......而且很快就会为 SELECT 查询类似数据的编译。
2 n7 F9 q- _* x- M* j0 C " M) N3 s2 ?4 b) E0 B! M
解决方案:
1 Z" N# \$ s1 m+ |9 Q( y: h 几个提示:7 w1 t ?# f/ b
[ol]在事务中插入/更新。2 i. E, r0 E/ O
旧版 SQLite - 考虑一种不那么偏执的日志模式 ( pragma journal_mode)。有NORMAL,然后有OFF,如果您不担心操作系统崩溃会损坏数据库,则可以显著提高插入速度。如果您的应用程序崩溃,数据应该没有问题。请注意,在更新版本中OFF/MEMORY应用程序级崩溃的设置是不安全的。' @4 _0 J2 |/ N) F- x
使用页面的大小也会有所不同(PRAGMA page_size)。由于更大的页面保存在内存中,,因为更大的页面保存在内存中。请注意,您的数据库将使用更多的内存。1 m) M/ q8 ^7 E
如果您有索引,请考虑CREATE INDEX在完成所有插入后调用。这比创建索引然后进行插入要快得多。( d" p% t. r' _- q
如果您对 SQLite 有并发访问权,必须非常小心,因为当写入完成时,整个数据库被锁定,尽管可能有多个读取器,但写入将被锁定。在更新的 SQLite 在版本中本中WAL,这已经改善了。
# Q. `# W+ v# @* p节省空间…较小的数据库运行得更快。例如,如果您有正确的键值,请尽量将键设置为 an INTEGER PRIMARY KEY,唯一隐含在替换表中的行号列。: A4 @- B3 X/ B, l7 q X$ ?
如果使用多个线程,可以尝试使用共享页面缓存,允许加载页面在线程之间共享,避免昂贵 I/O 调用。$ Y2 f; m( |7 u n, b: j
不要使用!feof(file)![/ol] |
|