提高SQLite每秒INSERT的性能

迷雾中的丑小鸭

优化SQLite是棘手的。C应用程序的大容量插入性能可以从每秒85次插入到每秒96次以上！
背景：我们将军SQLite作为桌面应用程序的一部分。存储大量配置数据XML这些文件将被分析并加载到文件中SQLite数据库中，以便在初始化应用程序时进行进一步处理。SQLite这种情况非常适合，因为它速度快，不需要特殊配置，数据库作为单个文件存储在磁盘上。
' T" n; u: k" _# y8 g基本原理： 一开始，我对我看到的性能感到失望。事实证明，这取决于数据库的配置和使用API的方式，SQLite性能可能会发生很大的变化（对于大容量插入和选择）。找出所有的选项和技术并不容易，所以我认为创建这个社区并不容易Wiki谨慎地与读者分享结果，以节省他人的麻烦。
/ H9 e, n( P4 `8 I& t( P/ L* I实验:我认为最好写一些C代码并实际衡量各种选择的影响，而不是简单地谈论一般意义上的性能提示（即使用事务！)。我们将从一些简单的数据开始：
5 s9 S$ E6 z% B) c28 MB TAB分隔的文本文件(约865,000条记录)用于多伦多市的完整运输时间表
- F. F5 M& I3 _, h0 I我的测试计算机正在运行Windows XP的3.60 GHz P4。
# m. s" c" Z4 O* c/ L+ t# p9 H8 b8 C使用Visual C    2005将代码编译成发布和完全优化(/  Ox）和最爱快速代码(/ Ot）。
我使用直接编译到测试应用程序SQLite合并。我刚刚拥有的SQLite版本（3.6.7)有点旧，但我怀疑这些结果会和最新版本一样(如果你有其他意见，请发表评论)。
$ C* V9 G' L: l. V让我们写一些代码！
代码：简单C程序，它逐行读取文本文件，将字符串分成值，然后插入数据SQLite数据库。数据库是在这个基准版本的代码中创建的，但实际上不会插入数据:
/*************************************************************    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次插入操作：-)
  A7 [- t% A9 }+ `8 q0 Z最坏情况
我们将使用从文件中读取的值生成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代码，每个插入都将在自己的事务中进行。
! T' \/ K/ S% P* z  @在9933.864913条记录在61秒内导入
kes！2小时45分钟！每秒只插入85次。
3 _3 x3 r- F/ v! W6 f8 E使用交易
* F! v% J1 b; E( r3 N% n1 M- ^/ ~4 U默认情况下，SQLite将评估唯一事务中的每一个INSERT / UPDATE语句。若执行大量插入操作，建议将操作包装在事务中：
) _: f9 M5 Q- ^& }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秒内导入
- _. H/ t! ?5 G这样， 只需将所有插入物包装在一个事务中，就可以将性能提高到每秒2.3万个插入物。
使用准备好的句子
使用事务是一个巨大的改进，但如果我们重复使用相同的事情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万次。
: N! M4 I/ I3 ^( v+ G! f' gPRAGMA同步= OFF
默认情况下，SQLite将在发出操作系统级别的写入命令后暂停。这可以确保将数据写入磁盘。通过设置synchronous = OFF，我们指示SQLite只需移交数据OS写入，然后继续。如果计算机在将数据写入磁盘之前遭受灾难性崩溃(或电源故障)，数据库文件可能会损坏:
5 _# \, @$ J4 R8 p$ p/ F* _! J/* 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秒内导入
目前改进程度较小，但每秒最多可插入69600个刀片。
PRAGMA journal_mode =OFF
6 |6 T8 b8 O" ~! V回滚日志通过评估存储在内存中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次插入比以前的优化慢一点。
5 B3 @; }4 b3 a1 Z8 mPRAGMA synchronous = OFF and PRAGMA journal_mode = MEMORY
让我们结合前两个优化。风险更高(如果崩溃)，但我们只是导入数据(不运行银行):
1 W# P8 O( z$ D* {9 Y/* 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秒内导入
2 k$ M6 f4 B; d( M$ ^" E极好的！每秒可完成72000次插入。
使用内存数据库
9 y9 K& i& S" H" b只是为了激发人们的心，让我们根据之前的所有优化重新定义数据库文件名，这样我们就完全在那里RAM中工作：
. A# U0 G# ^/ Z* f#define DATABASE ":memory:"在10.864913记录在94秒内导入
" |) [% l- h( c+ F+ R! U8 f存储我们的数据库RAM它不是很实用，但令人印象深刻的是，我们每秒可以插入7.9万次。
& S4 K! e& T  N$ Y) s  Y! ~重构C代码
虽然没有特别的对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);注：我们将回到使用真实的数据库文件。内存数据库速度快，但不一定实用
0 G) `# D) A# t3 \/ Y) Y+ K, d( C: n在8.864913记录在94秒内导入
, K+ V' b+ G4 Y, e7 @6 D' l! Q稍微重建参数绑定中使用的字符串处理代码，每秒可以插入96700次。我相信这是非常快的。当我们开始调整其他变量（即页面大小、索引创建等）时，这将成为我们的基准。
摘要(到目前为止)
我希望你仍然和我在一起！我们选择这条路的原因是使用它SQLite批量插入的性能变化如此之大，为了加速我们的操作，需要做哪些改变并不总是很明显。使用相同的编译器(和编译器选项)和相同的版本SQLite我们优化了代码，优化了相同的数据SQLite使用从最坏的情况下每秒85次插入到每秒96000次以上！
先创建索引，然后插入VS.插入，然后创建索引
/ n- z1 K* j, j. l5 }在开始衡量SELECT性能之前，我们知道我们将创建索引。在下面的答案之一中，建议进行批量插入时，插入数据后创建索引的速度更快（与先创建索引然后插入数据相反）。我们试试看：
创建索引，然后插入数据
sqlite3_exec(db,"CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')",NULL,NULL,&sErrMsg);sqlite3_exec(db,"BEGIN TRANSACTION",NULL,NULL,&sErrMsg);...在18.864913记录在13秒内导入
插入数据，然后创建索引
& L8 k1 s, w# |) J...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秒内导入
, q& U" G% H9 B( P+ W. U( M出乎意料的是，如果为一列建立索引，大容量插入将缓慢，但如果在插入数据后创建索引，确实会有所不同。我们的无索引基准是每秒96000次。首先创建索引，然后插入数据，每秒可以提供47700次插入，先创建数据，300次。
3 R. ]; {) `( b+ I" f3 \& X7 ]" q) E为了尝试其他情况，我很高兴提供建议…，并将很快为SELECT查询类似数据的编译。
                                                               
) M; O+ _7 R( w    解决方案:                                                               
( w6 ~# }7 j2 ^) |7 W                                                                几个技巧：
[ol]在事务中插入/更新。
对于较旧的SQLite版本，请考虑使用较少偏执的日记模式（pragma journal_mode）。有NORMAL，然后有OFF，如果您不担心数据库因操作系统崩溃而损坏，则可以显著提高插入速度。如果您的应用程序崩溃，数据应该没有问题。请注意，在更新版本中，OFF/MEMORY应用程序级崩溃设置不安全。
4 g9 ]% \  f, D% u1 A$ A0 s播放页面的大小也会有所不同（PRAGMA page_size）。由于大页面保留在内存中，大页面大小可以使读写更快。请注意，更多的内存将用于您的数据库。
假如你有索引，请CREATE INDEX考虑在完成所有插入操作后调用。这比创建索引并插入要快得多。
若可并发访问SQLite，必须非常小心，因为整个数据库在写入后将被锁定，尽管可能有多个读取器，但写入将被锁定。通过更新SQLite版本中添加WAL，对此进行了一些改进。
利用节省空间的优势…较小的数据库运行得更快。例如，如果您有正确的键值，请尝试将键设置为a（INTEGER PRIMARY KEY如有可能)，它将替换表中隐含的唯一行号列。
如果使用多个线程，可以尝试使用共享页面缓存，允许在线程之间共享加载的页面，避免昂贵I / O调用。
6 \. c$ y+ q3 v) I3 r不要使用!feof(file)！[/ol]