提升SQLite数据插入效率低、速度慢的方法

Sqlite 前端之家
前端之家收集整理的这篇文章主要介绍了提升SQLite数据插入效率低、速度慢的方法，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

本文转载自：http://blog.csdn.net/chenguanzhou123/article/details/9376537#，谢谢原作者分享


0 前言

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. @H_404_49@create@H_404_49@tablet1(id@H_404_49@integer,x404_49@integer，weight@H_404_49@real)@H_301_71@

2慢速——最粗暴的方法

sqlite的API中直接执行sql的函数是：
[cpp] copy
    intsqlite3_exec(sqlite3*,@H_404_49@constchar*sql,int(*callback)(@H_404_49@void*,87); background-color:inherit; font-weight:bold">int,87); background-color:inherit; font-weight:bold">char**,87); background-color:inherit; font-weight:bold">char**),87); background-color:inherit; font-weight:bold">char**errmsg)@H_301_71@
直接使用INSERT语句的字符串进行插入，程序部分代码（完整代码见后文），如下：
copy
    for(inti=0;i<nCount;++i)@H_301_71@
  1. {@H_301_71@
  2. std::stringstreamssm;@H_301_71@
  3. ssm<<@H_502_165@"insertintot1values("<<i<<@H_502_165@","<<i*2<<@H_502_165@","<<i/2<<@H_502_165@","<<i*i<<@H_502_165@")";@H_301_71@
  4. sqlite3_exec(db,ssm.str().c_str(),0);@H_301_71@
  5. }@H_301_71@
这个程序运行的太慢了，我已经没时间等待了，估算了一下，基本上是 7.826条/s

3中速——显式开启事务

所谓”事务“就是指一组sql命令，这些命令要么一起执行，要么都不被执行。在sqlite中，每调用一次sqlite3_exec()函数，就会隐式地开启了一个事务，如果插入一条数据，就调用该函数一次，事务就会被反复地开启、关闭，会增大IO量。如果在插入数据前显式开启事务，插入后再一起提交，则会大大提高IO效率，进而加数据快插入速度。
开启事务只需在上述代码的前后各加一句开启与提交事务的命令即可：
[cpp] view plain copy
  1. sqlite3_exec(db,@H_502_165@"begin;",0);@H_301_71@
  2. inti=0;i<nCount;++i)@H_301_71@
  3. {@H_301_71@
  4. std::stringstreamssm;@H_301_71@
  5. ssm<<@H_502_165@"insertintot1values("<<i<<@H_502_165@","<<i*i<<@H_502_165@")";@H_301_71@
  6. sqlite3_exec(db,0);@H_301_71@
  7. }@H_301_71@
  8. 502_165@"commit;",0);@H_301_71@

显式开启事务后，这个程序运行起来明显快很多，估算效率达到了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。在代码中，设置方法就是在打开数据库之后，直接插入以下语句：

copy
    502_165@"PRAGMAsynchronous=OFF;",0);@H_301_71@
此时，经过测试，插入速度已经变成了 41851条/s，也就是说，插入100000000条数据，需要2389秒 = 39.8分。 5极速——执行准备

虽然写同步设为off后，速度又有小幅提升，但是仍然较慢。我又一次踏上了寻找提高sqlite插入效率方法的道路上。终于，我发现，sqlite执行sql语句的时候，有两种方式：一种是使用前文提到的函数sqlite3_exec()，该函数直接调用包含sql语句的字符串；另一种方法就是“执行准备”（类似于存储过程）操作，即先将sql语句编译好，然后再一步一步（或一行一行）地执行。如果采用前者的话，就算开起了事务，sqlite仍然要对循环中每一句sql语句进行“词法分析”和“语法分析”，这对于同时插入大量数据的操作来说，简直就是浪费时间。因此，要进一步提高插入效率的话，就应该使用后者。

“执行准备”主要分为三大步骤：

1.调用函数

copy
    intsqlite3_prepare_v2(sqlite3*db,87); background-color:inherit; font-weight:bold">char*zsql,87); background-color:inherit; font-weight:bold">intnByte,sqlite3_stmt**ppStmt,87); background-color:inherit; font-weight:bold">char**pzTail);@H_301_71@
并且声明一个指向sqlite3_stmt对象的指针，该函数对参数化的sql语句zsql进行编译，将编译后的状态存入ppStmt中。

2.调用函数 sqlite3_step() ，这个函数就是执行一步（本例中就是插入一行），如果函数返回的是sqlite_ROW则说明仍在继续执行，否则则说明已经执行完所有操作；

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

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

copy
    sqlite3_stmt*stmt;@H_301_71@
  1. char*sql=@H_502_165@"insertintot1values(?,?,?)";@H_301_71@
  2. sqlite3_prepare_v2(db,sql,strlen(sql),&stmt,108); list-style-type:decimal-leading-zero; color:inherit; line-height:20px; margin:0px!important; padding:0px 3px 0px 10px!important; list-style-position:outside!important"> @H_301_71@
  3. sqlite3_reset(stmt);@H_301_71@
  4. sqlite3_bind_int(stmt,1,i);@H_301_71@
  5. sqlite3_bind_int(stmt,i*2);@H_301_71@
  6. 301_71@
  7. sqlite3_bind_double(stmt,i*i);@H_301_71@
  8. sqlite3_finalize(stmt);@H_301_71@
  9. 301_71@
此时测试数据插入效率为： 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（这是本好书）

附最终完整代码:

copy

    #include<iostream>@H_301_71@
  1. #include<string>@H_301_71@
  2. #include<sstream>@H_301_71@
  3. #include<time.h>@H_301_71@
  4. #include"sqlite3.h"@H_301_71@
  5. @H_301_71@
  6. intnCount=500000;@H_301_71@
  7. intmain(intargc,87); background-color:inherit; font-weight:bold">char**argv)@H_301_71@
  8. {@H_301_71@
  9. sqlite3*db;@H_301_71@
  10. sqlite3_open(@H_502_165@"testdb.db",&db);@H_301_71@
  11. 502_165@"PRAGMAsynchronous=OFF;",@H_502_165@"droptableifexistst1",0);@H_301_71@
  12. 502_165@"createtablet1(idinteger,xinteger,yinteger,weightreal)",248); line-height:20px; margin:0px!important; padding:0px 3px 0px 10px!important; list-style-position:outside!important"> clock_tt1=clock();@H_301_71@
  13. 502_165@"begin;",108); list-style-type:decimal-leading-zero; color:inherit; line-height:20px; margin:0px!important; padding:0px 3px 0px 10px!important; list-style-position:outside!important"> sqlite3_stmt*stmt;@H_301_71@
  14. ;@H_301_71@
  15. sqlite3_prepare_v2(db,0);@H_301_71@
  16. inti=0;i<nCount;++i)@H_301_71@
  17. //std::stringstreamssm;@H_301_71@
  18. //ssm<<"insertintot1values("<<i<<","<<i*2<<","<<i/2<<","<<i*i<<")";@H_301_71@
  19. //sqlite3_exec(db,0);@H_301_71@
  20. sqlite3_step(stmt);@H_301_71@
  21. }@H_301_71@
  22. sqlite3_finalize(stmt);@H_301_71@
  23. clock_tt2=clock();@H_301_71@
  24. sqlite3_close(db);@H_301_71@
  25. std::cout<<@H_502_165@"costtima:"<<(t2-t1)/1000.<<@H_502_165@"s"<<std::endl;@H_301_71@
  26. return0;@H_301_71@
  27. }@H_301_71@

猜你在找的Sqlite相关文章

SQLite学习笔记
安装 在Windows上安装SQLite。 访问官网下载下Precompliled Binaries for Windows的两个压...
windows上如何安装Sqlite
一、安装 下载地址：http://www.sqlite.org/download.html 将Precompiled Binaries for Wi...
【Android数据存储】SQLite使用实例(附源码)
实例: 会员信息管理 功能：1.查看数据库 2.清空数据库 3.增加会员 4.删除会员 5.更新会员 ...
SQLite数据库管理
SQLite3常用命令
加密你的SQLite
关于SQLite SQLite是一个轻量的、跨平台的、开源的数据库引擎，它的在读写效率、消耗总量、...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite这么娇小可爱，不多了解点都不行啊
在我眼里，MySQL和Oracle是这样的 而SQLite在是这样的 所以这么萌的数据库，我真的应该多了...
SQLite 这么娇小可爱，不多了解点都不行啊
（点击上方蓝字，快速关注我们) 作者：空之境界 网址：http://supermao.cn/sqlitezhe-yao-...
SQLITE3 使用总结
SQLITE3 使用总结 投稿：hebedich 字体：[ 增加  减小] 类型：转载 时间：2014-08-19 Sql...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问