提高SQLite每秒INSERT的性能

迷雾中的丑小鸭

优化SQLite是棘手的。C应用程序的大容量插入性能可以从每秒85次插入到每秒96次以上！
背景：我们将军SQLite作为桌面应用程序的一部分。存储大量配置数据XML这些文件将被分析并加载到文件中SQLite数据库中，以便在初始化应用程序时进行进一步处理。SQLite这种情况非常适合，因为它速度快，不需要特殊配置，数据库作为单个文件存储在磁盘上。
基本原理：一开始，我对我看到的性能感到失望。事实证明，这取决于数据库的配置和使用API的方式，SQLite性能可能会发生很大的变化（对于大容量插入和选择）。找出所有的选项和技术并不容易，所以我认为创建这个社区并不容易Wiki谨慎地与读者分享结果，以节省他人的麻烦。
实验:我认为最好写一些C代码并实际衡量各种选择的影响，而不是简单地谈论一般意义上的性能提示（即使用事务！)。我们将从一些简单的数据开始：
28 MB TAB分隔的文本文件(约865,000条记录)用于多伦多市的完整运输时间表
我的测试计算机正在运行Windows XP的3.60 GHz P4。
使用Visual C 2005将代码编译成发布和完全优化(/  Ox）和最爱快速代码(/ Ot）。
我使用直接编译到测试应用程序SQLite合并。我刚刚拥有的SQLite版本（3.6.7)有点旧，但我怀疑这些结果会和最新版本一样(如果你有其他意见，请发表评论)。
让我们写一些代码！
代码：简单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次插入操作：-)
最坏情况
我们将使用从文件中读取的值生成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代码，每个插入都将在自己的事务中进行。
在9933.864913条记录在61秒内导入
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.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万次。
PRAGMA同步= OFF
默认情况下，SQLite将在发出操作系统级别的写入命令后暂停。这可以确保将数据写入磁盘。通过设置synchronous = OFF，我们指示SQLite只需移交数据OS写入，然后继续。如果计算机在将数据写入磁盘之前遭受灾难性崩溃(或电源故障)，数据库文件可能会损坏:
/* Open the database and create the schema */sqlite3_open(DATABASE,&db);sqlite3_exec(db,TABLE,NULL,NULL,&sErrMsg);sqlite3_exec(db,&quot

RAGMA synchronous = OFF",NULL,NULL,&sErrMsg);在12.864913记录在41秒内导入
目前改进程度较小，但每秒最多可插入69600个刀片。
PRAGMA journal_mode =OFF
回滚日志通过评估存储在内存中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,&quot

RAGMA journal_mode = MEMORY",NULL,NULL,&sErrMsg);在13.864913记录在50秒内导入
每秒64000次插入比以前的优化慢一点。
PRAGMA synchronous = OFF and 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,&quot

RAGMA synchronous = OFF",NULL,NULL,&sErrMsg);sqlite3_exec(db,&quot

RAGMA journal_mode = MEMORY",NULL,NULL,&sErrMsg);在12.864913记录在00秒内导入
极好的！每秒可完成72000次插入。
使用内存数据库
只是为了激发人们的心，让我们根据之前的所有优化重新定义数据库文件名，这样我们就完全在那里RAM中工作：
#define DATABASE ":memory:"在10.864913记录在94秒内导入
存储我们的数据库RAM它不是很实用，但令人印象深刻的是，我们每秒可以插入7.9万次。
重构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);注：我们将回到使用真实的数据库文件。内存数据库速度快，但不一定实用
在8.864913记录在94秒内导入
稍微重建参数绑定中使用的字符串处理代码，每秒可以插入96700次。我相信这是非常快的。当我们开始调整其他变量（即页面大小、索引创建等）时，这将成为我们的基准。
摘要(到目前为止)
我希望你仍然和我在一起！我们选择这条路的原因是使用它SQLite批量插入的性能变化如此之大，为了加速我们的操作，需要做哪些改变并不总是很明显。使用相同的编译器(和编译器选项)和相同的版本SQLite我们优化了代码，优化了相同的数据SQLite使用从最坏的情况下每秒85次插入到每秒96000次以上！
先创建索引，然后插入VS.插入，然后创建索引
在开始衡量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秒内导入
插入数据，然后创建索引
...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秒内导入
出乎意料的是，如果为一列建立索引，大容量插入将缓慢，但如果在插入数据后创建索引，确实会有所不同。我们的无索引基准是每秒96000次。首先创建索引，然后插入数据，每秒可以提供47700次插入，先创建数据，300次。
为了尝试其他情况，我很高兴提供建议…，并将很快为SELECT查询类似数据的编译。

解决方案:
                                                            几个技巧：
[ol]在事务中插入/更新。
对于较旧的SQLite版本，请考虑使用较少偏执的日记模式（pragma journal_mode）。有NORMAL，然后有OFF，如果您不担心数据库因操作系统崩溃而损坏，则可以显著提高插入速度。如果您的应用程序崩溃，数据应该没有问题。请注意，在更新版本中，OFF/MEMORY应用程序级崩溃设置不安全。
播放页面的大小也会有所不同（PRAGMA page_size）。由于大页面保留在内存中，大页面大小可以使读写更快。请注意，更多的内存将用于您的数据库。
假如你有索引，请CREATE INDEX考虑在完成所有插入操作后调用。这比创建索引并插入要快得多。
若可并发访问SQLite，必须非常小心，因为整个数据库在写入后将被锁定，尽管可能有多个读取器，但写入将被锁定。通过更新SQLite版本中添加WAL，对此进行了一些改进。
利用节省空间的优势…较小的数据库运行得更快。例如，如果您有正确的键值，请尝试将键设置为a（INTEGER PRIMARY KEY如有可能)，它将替换表中隐含的唯一行号列。
如果使用多个线程，可以尝试使用共享页面缓存，允许在线程之间共享加载的页面，避免昂贵I / O调用。
不要使用!feof(file)！[/ol]

提高SQLite每秒INSERT的性能

迷雾中的丑小鸭 LV1