提高SQLite每秒INSERT的性能

迷雾中的丑小鸭

优化SQLite是棘手的。C应用程序的大容量插入性能可以从每秒85次插入到每秒96次以上！
: c' g5 {  d# W+ z+ U, u' b背景：我们将军SQLite作为桌面应用程序的一部分。存储大量配置数据XML这些文件将被分析并加载到文件中SQLite数据库中，以便在初始化应用程序时进行进一步处理。SQLite这种情况非常适合，因为它速度快，不需要特殊配置，数据库作为单个文件存储在磁盘上。
- Z$ M/ E1 e5 S+ p& z" K基本原理： 一开始，我对我看到的性能感到失望。事实证明，这取决于数据库的配置和使用API的方式，SQLite性能可能会发生很大的变化（对于大容量插入和选择）。找出所有的选项和技术并不容易，所以我认为创建这个社区并不容易Wiki谨慎地与读者分享结果，以节省他人的麻烦。
实验:我认为最好写一些C代码并实际衡量各种选择的影响，而不是简单地谈论一般意义上的性能提示（即使用事务！)。我们将从一些简单的数据开始：
7 I1 N7 F) J8 h+ _  c9 d/ Z8 q$ ]28 MB TAB分隔的文本文件(约865,000条记录)用于多伦多市的完整运输时间表
我的测试计算机正在运行Windows XP的3.60 GHz P4。
使用Visual C    2005将代码编译成发布和完全优化(/  Ox）和最爱快速代码(/ Ot）。
7 S4 L1 U# a4 }  H我使用直接编译到测试应用程序SQLite合并。我刚刚拥有的SQLite版本（3.6.7)有点旧，但我怀疑这些结果会和最新版本一样(如果你有其他意见，请发表评论)。
让我们写一些代码！
7 L) W3 }5 N0 ]7 a* k代码：简单C程序，它逐行读取文本文件，将字符串分成值，然后插入数据SQLite数据库。数据库是在这个基准版本的代码中创建的，但实际上不会插入数据:
  ?! B! k% ]+ R5 x8 ], s7 @/*************************************************************    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;}“控制”
实际上，按原始操作代码不会执行任何数据库操作，但它将使我们了解原始操作代码C文件I / O以及字符串处理的速度。
在0.864913记录在94秒内导入
伟大的！只要我们实际上不执行任何插入操作，我们每秒可以执行92000次插入操作：-)
/ s# Q! L$ P$ J4 C) N最坏情况
我们将使用从文件中读取的值生成SQL字符串，并使用sqlite3_exec调用该SQL操作：
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);因为每个插入都会很慢，SQL都将被编译成VDBE代码，每个插入都将在自己的事务中进行。
" r! G) \+ K6 X0 q3 ^在9933.864913条记录在61秒内导入
9 ~- l3 D2 w7 ~kes！2小时45分钟！每秒只插入85次。
使用交易
默认情况下，SQLite将评估唯一事务中的每一个INSERT / UPDATE语句。若执行大量插入操作，建议将操作包装在事务中：
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);在38.864913记录在03秒内导入
2 {6 d5 u7 Q3 @这样， 只需将所有插入物包装在一个事务中，就可以将性能提高到每秒2.3万个插入物。
使用准备好的句子
* P: |  ~) |- _9 s使用事务是一个巨大的改进，但如果我们重复使用相同的事情SQL，重新编译每个插入SQL句子毫无意义。让我们用它。sqlite3_prepare_v一次编译我们的SQL然后用以下命令将参数绑定到句子中sqlite3_bind_text：
/* 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;在16.864913记录在27秒内导入
好的！还有更多的代码(别忘了调用)sqlite3_clear_bindings和sqlite3_reset），但是我们的性能提高了一倍多，每秒插入5.3万次。
4 ?7 P2 X% S7 P# }* ]! B% DPRAGMA同步= OFF
默认情况下，SQLite将在发出操作系统级别的写入命令后暂停。这可以确保将数据写入磁盘。通过设置synchronous = OFF，我们指示SQLite只需移交数据OS写入，然后继续。如果计算机在将数据写入磁盘之前遭受灾难性崩溃(或电源故障)，数据库文件可能会损坏:
8 N$ g  V- r& P' l& R. x/* 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);在12.864913记录在41秒内导入
- S0 V* W  w; `' w9 @; a目前改进程度较小，但每秒最多可插入69600个刀片。
PRAGMA journal_mode =OFF
/ |% t1 E6 L' \3 K" x" a回滚日志通过评估存储在内存中PRAGMA journal_mode = MEMORY。您的事务会更快，但如果您在事务期间停电或程序崩溃，数据库可能会因部分事务而损坏：
/* 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);在13.864913记录在50秒内导入
每秒64000次插入比以前的优化慢一点。
6 q$ Y4 e7 z( }+ `6 R7 {1 P. JPRAGMA synchronous = OFF and PRAGMA journal_mode = MEMORY
让我们结合前两个优化。风险更高(如果崩溃)，但我们只是导入数据(不运行银行):
2 Y& j7 I; Y) v& A- Z/* 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);在12.864913记录在00秒内导入
% A' S8 X8 ]& a; x% k极好的！每秒可完成72000次插入。
使用内存数据库
只是为了激发人们的心，让我们根据之前的所有优化重新定义数据库文件名，这样我们就完全在那里RAM中工作：
#define DATABASE ":memory:"在10.864913记录在94秒内导入
7 g! z+ [" `- b存储我们的数据库RAM它不是很实用，但令人印象深刻的是，我们每秒可以插入7.9万次。
重构C代码
; u9 G' w6 g1 P3 O/ ?" d( h虽然没有特别的对SQLite改进，但我不喜欢循环中的额外char*赋值操作while。让我们快速重构代码，输出strtok()直接传输到sqlite3_bind_text()让编译器为我们加速:
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);注：我们将回到使用真实的数据库文件。内存数据库速度快，但不一定实用
在8.864913记录在94秒内导入
3 \+ q- I" T! G" R4 u1 L稍微重建参数绑定中使用的字符串处理代码，每秒可以插入96700次。我相信这是非常快的。当我们开始调整其他变量（即页面大小、索引创建等）时，这将成为我们的基准。
/ y( a3 F/ b4 V摘要(到目前为止)
我希望你仍然和我在一起！我们选择这条路的原因是使用它SQLite批量插入的性能变化如此之大，为了加速我们的操作，需要做哪些改变并不总是很明显。使用相同的编译器(和编译器选项)和相同的版本SQLite我们优化了代码，优化了相同的数据SQLite使用从最坏的情况下每秒85次插入到每秒96000次以上！
先创建索引，然后插入VS.插入，然后创建索引
  k1 c* y, ?, J在开始衡量SELECT性能之前，我们知道我们将创建索引。在下面的答案之一中，建议进行批量插入时，插入数据后创建索引的速度更快（与先创建索引然后插入数据相反）。我们试试看：
, J$ a6 n: p" A- n1 [6 B  \创建索引，然后插入数据
( p& I+ V. U/ u5 ^% vsqlite3_exec(db,"CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);sqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);...在18.864913记录在13秒内导入
插入数据，然后创建索引
...sqlite3_exec(db,"END TRANSACTION",NULL,NULL,&sErrMsg);sqlite3_exec(db,"CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);在13.864913记录在66秒内导入
' r, o  D6 U& t/ v: b2 h出乎意料的是，如果为一列建立索引，大容量插入将缓慢，但如果在插入数据后创建索引，确实会有所不同。我们的无索引基准是每秒96000次。首先创建索引，然后插入数据，每秒可以提供47700次插入，先创建数据，300次。
7 b9 ~: g# b2 j6 p% A' C为了尝试其他情况，我很高兴提供建议…，并将很快为SELECT查询类似数据的编译。
! o2 O3 z0 b, u, g6 y0 Q8 L                                                               
& w( r0 f  m9 K# y5 K5 V    解决方案:                                                               
                                                                几个技巧：
1 F0 ~6 `, S0 w* I5 F, ^[ol]在事务中插入/更新。
0 x2 }8 B, ~: |" Q对于较旧的SQLite版本，请考虑使用较少偏执的日记模式（pragma journal_mode）。有NORMAL，然后有OFF，如果您不担心数据库因操作系统崩溃而损坏，则可以显著提高插入速度。如果您的应用程序崩溃，数据应该没有问题。请注意，在更新版本中，OFF/MEMORY应用程序级崩溃设置不安全。
播放页面的大小也会有所不同（PRAGMA page_size）。由于大页面保留在内存中，大页面大小可以使读写更快。请注意，更多的内存将用于您的数据库。
假如你有索引，请CREATE INDEX考虑在完成所有插入操作后调用。这比创建索引并插入要快得多。
8 s7 P5 S4 `% V; Y# U若可并发访问SQLite，必须非常小心，因为整个数据库在写入后将被锁定，尽管可能有多个读取器，但写入将被锁定。通过更新SQLite版本中添加WAL，对此进行了一些改进。
. \* s. l' m' {" E5 V: w利用节省空间的优势…较小的数据库运行得更快。例如，如果您有正确的键值，请尝试将键设置为a（INTEGER PRIMARY KEY如有可能)，它将替换表中隐含的唯一行号列。
如果使用多个线程，可以尝试使用共享页面缓存，允许在线程之间共享加载的页面，避免昂贵I / O调用。
2 H7 X& \5 U& J. P: H2 v! O; h* t不要使用!feof(file)！[/ol]