转自：http://blog.csdn.net/majiakun1/article/details/46607163

前言

SQLite由于其简单性、灵活性、轻量级和开源性，数据库越来越多地应用于中小型应用程序。有人甚至说，SQLite完全可以替代c语言中的文件读写操作。因此，我最近在写遥感数据处理相关的程序时，我也SQLite引入进来，以提高数据的结构化程度，并且提高大数据的处理能力（SQLite最高支持2PB大小数据）。但最初，我发现使用SQL语句的插入效率简直令人震惊。后来，在不断寻找文件资料后，发现了一条“数据插入”的快速路径。本文以插入数据为例，整合在线和参考书的各种改进SQLite提高效率的方法SQLite提高数据插入效率的完整方法。（不要喷，伟大的神）

1 数据

我正在使用的计算机是Win7 64位系统， 使用VC2010编译，SQLIte版本为3.7.15.2 ，电脑CPU为二代i3处理器、内存6G。

在实验之前，建立一个表以将数据插入其中:

[sql] view plain copy

1. create table t1 (id integer , x integer , y integer ， weight real )

2 慢速 ——最粗糙的方法

SQLite的API直接执行SQL的功能是：

[cpp] view plain copy

1. int sqlite3_exec(  sqlite3, const char sql, int (callback)( void , int , char , char ), void *, char **errmsg)

直接使用INSERT插入语句的字符串，程序部分代码（见后面的完整代码）如下：

[cpp] view plain copy

1. for ( int i=0;i<nCount;++i)
2. {
3. std::stringstream ssm;
4. ssm<< "insert into t1 values(" <<i<< "," <<i2<< "," <<i/2<< "," <<ii<< ")" ;
5. sqlite3_exec(db,ssm.str().c_str(),0,0,0);
6. }

这个程序运行太慢，我没有时间再等了。估算后，基本上是 7.826 条/s

3 中速 ——明确开仓交易

所谓“交易”是指一组SQL命令，这些命令要么一起执行，要么不执行。留SQLite在，每个电话sqlite3_exec()该函数将隐式启动事务。如果插入一段数据，该函数将被调用一次，事务将被反复打开和关闭，增加IO数量。如果事务在插入数据之前显式打开，然后在插入后一起提交，它将大大提高IO效率反过来又提高了数据插入速度。

要启动事务，只需在上述代码之前和之后添加一个命令来启动和提交事务：

[cpp] view plain copy

1. sqlite3_exec(db, "begin;" ,0,0,0);
2. for ( int i=0;i<nCount;++i)
3. {
4. std::stringstream ssm;
5. ssm<< "insert into t1 values(" <<i<< "," <<i2<< "," <<i/2<< "," <<ii<< ")" ;
6. sqlite3_exec(db,ssm.str().c_str(),0,0,0);
7. }
8. sqlite3_exec(db, "commit;" ,0,0,0);

显式打开事务后，该程序的运行速度明显更快，并且估计的效率已达到 34095 条/s与原始方法相比，大约改进了5000倍。

4 高速 ——写入同步(synchronous)

我需要使用遥感处理算法来处理10000*10000图像中间有一个步骤需要插入100000000如果数据在开始事务后以最快的速度传输到数据库34095条/s，则需要100000000÷34095 = 2932秒 = 48.9我仍然无法接受这个分数，所以我继续寻找提高速度的方法。最后，在相关解释中SQLite在配置信息中，您可以看到“写入同步”选项。

在SQLite在中，数据库配置的参数由编译（pragma）要实现，其中synchronous选项有三种可选状态，即full、normal、off。 这篇博客 以及 官方文档 有这三个参数设置的详细说明。简单地说，full写入速度最慢，但它确保数据安全，不受断电、系统崩溃等影响off它可以加速数据库的某些操作，但如果系统崩溃或断电，数据库可能会损坏。

SQLite3此选项的默认值为full如果我们将数据更改为off，它将提高效率。如果只有SQLite作为临时数据库，无需将其设置为full。在代码中，设置方法是在打开数据库后直接插入以下语句：

[cpp] view plain copy

1. sqlite3_exec(db, "PRAGMA synchronous = OFF; " ,0,0,0);

此时，经过测试，插入速度已经变成了 41851 条/s也就是说，插入100000000数据片段，必填2389秒 = 39.8分。

5 极速 ——执行准备

尽管写入同步设置为off之后，速度略有提高，但仍然较慢。我再次开始寻求改进SQLite在道路上插入效率方法。终于，我意识到，SQLite执行SQL语句的使用方式有两种：一种是使用前面提到的函数sqlite3_exec()此函数直接调用包含SQL语句的字符串;另一种方法是执行“准备”（类似于存储过程）操作，该操作首先设置SQL编译语句并逐步（或逐行）执行。如果采用前者，即使交易启动，SQLite仍然需要检查循环中的每个句子SQL对语句执行“词法分析”和“语法分析”只是浪费时间同时插入大量数据。因此，为了进一步提高插入效率，应使用后者。

执行的准备工作主要分为三个步骤：

1.调用函数

[cpp] view plain copy

1. int sqlite3_prepare_v2( sqlite3 db, const char zSql, int nByte,  sqlite3_stmt ppStmt, const char pzTail);

并宣布一个点sqlite3_stmt指向对象的指针，该对象是用于参数化的函数SQL语句zSql编译并存储编译状态ppStmt中。

2.调用函数 sqlite3_step() 此函数执行一个步骤（在本例中为插入一行），如果函数返回SQLite_ROW如果表示执行仍在进行中，则表示所有操作已完成;

3.调用函数 sqlite3_finalize()，关闭语句。

关于执行准备API具体语法请参考 官方文档 。本文中的执行准备c++代码如下：

[cpp] view plain copy

1. sqlite3_exec(db, "begin;" ,0,0,0);

2. sqlite3_stmt *stmt;

3. const char * sql = "insert into t1 values(?,?,?,?)" ;

4. sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0);

5. for ( int i=0;i<nCount;++i)

6. {

7. sqlite3_reset(stmt);

8. sqlite3_bind_int(stmt,1,i);

9. sqlite3_bind_int(stmt,1,i*2);

10. sqlite3_bind_int(stmt,1,i/2);

11. sqlite3_bind_double(stmt,1,i*i);

12. }

13. sqlite3_finalize(stmt);

14. sqlite3_exec(db, "commit;" ,0,0,0);

此时，插入测试数据的效率为： 265816 条/s也就是说，插入100000000数据片段，必填376秒 = 6.27分。这个速度已经很满意了。

5 总结

综上所述，SQLite高效插入数据的最快方法是： 事务 + 关闭写入同步 + 执行准备（存储过程） 如果有数据库安全要求，请启用写入同步。

参考资料：

1. SQLite公文： http://www.sqlite.org/docs.html

2.《解决sqlite3数据插入缓慢的问题： http://blog.csdn.net/victoryknight/article/details/7461703

3.《The Definitive Guide to SQLite》Apress出版： http://www.apress.com/9781430232254 （这是一本好书）

附有最终的完整代码:

[cpp] view plain copy

1. include 

2. include 

3. include 

4. include 

5. include "sqlite3.h"

6. const int nCount = 500000;

7. int main ( int argc, char ** argv)

8. {

9. sqlite3* db;

10. sqlite3_open( "testdb.db" ,&db);

11. sqlite3_exec(db, "PRAGMA synchronous = OFF; " ,0,0,0);

12. sqlite3_exec(db, "drop table if exists t1" ,0,0,0);

13. sqlite3_exec(db, "create table t1(id integer,x integer,y integer ,weight real)" ,0,0,0);

14. clock_t t1 = clock();

15. sqlite3_exec(db, "begin;" ,0,0,0);

16. sqlite3_stmt *stmt;

17. const char * sql = "insert into t1 values(?,?,?,?)" ;

18. sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,0);

19. for ( int i=0;i<nCount;++i)

20. {

21. // std::stringstream ssm;

22. // ssm<<"insert into t1 values("<<i<<","<<i2<<","<<i/2<<","<<ii<<")";

23. // sqlite3_exec(db,ssm.str().c_str(),0,0,0);

24. sqlite3_reset(stmt);

25. sqlite3_bind_int(stmt,1,i);

26. sqlite3_bind_int(stmt,2,i*2);

27. sqlite3_bind_int(stmt,3,i/2);

28. sqlite3_bind_double(stmt,4,i*i);

29. sqlite3_step(stmt);

30. }

31. sqlite3_finalize(stmt);

32. sqlite3_exec(db, "commit;" ,0,0,0);

33. clock_t t2 = clock();

34. sqlite3_close(db);

35. std::cout<< "cost tima: " <<(t2-t1)/1000.<< "s" <<std::endl;

36. return 0;

37. }