Sqlite3开发你一定要知道的大数据插入优化策略weixin39647415的博客-

Sqlite3

Sqlite3常用在移动端或者嵌入式设备开发中，经常担任数据持久化存储的作用，区别于MySQL，SQLServer等一些主流的数据库，Sqlite更显轻薄，运行速度更快，但是作为关系型数据库，Sqlite数据库和MySQL数据库等都对插入查询方式的不同而产生不同的效率，通过这篇博客，我们来侧重分析对于大量数据插入的效率带来的效率问题.(数据量达到千万级或者亿级别)

优化策略(默认采用user表)

我们先从插入语句入手逐步向数据库优化进发。

1. 优化策略一: 插入语句的书写方式
   (1)在测试时候,为了方便我们将下面这条插入SQL语句采用for循环方式进行插入到数据库

INSERT INTO user(name,age) VALUES ('CreaterOS','21')； 

for(NSUInteger i = 0; i < 10000000; i++){ //省略执行语句,直接写插入语句，代表插入过程  INSERT INTO user(name,age) VALUES ('CreaterOS','21')； } 

这样的插入方式,相当于是逐条写入数据库中,在Xcode根据内存和时间测试,我们来观察一下，这样的效率会有多低。

我们观察到耗时两分钟左右都还没有完全插入完成，并且内存会逐渐上升，根据实际测试，当达到11min左右，内存会增长到5GB～15GB不等，根据机器和中间做的处理不同会有不同的成功。不过,出现这种问题，我们能分析趋势，插入时间长，内存逐渐增加，直到内存溢出。
(2) 采用一条语句多条插入操作

INSERT INTO user(name,age) VALUES ('CreaterOS','21'),('CreaterOS','21')...； 

这里面的数据量是一条语句插入大概一万六千条数据，为了快速营造一千六百万条数据，这里面还是不合法的使用循环操作。

for(NSUInteger i = 0; i < 1000; i++){  INSERT INTO user(name,age) VALUES ('CreaterOS','21'),('CreaterOS','21')...； } 

及时这样使用使用循环，效率也会提高很多，假设使用一条语句直接插入一千六百万数据的效率可想而知，是最快的。
我们来看一下Xcode在上面这样插入的表现情况:

我们用计算器测算一下耗时是0.06毫秒,假设一条语句插入一千六百万数据，那样的效率会是这个的好几十倍。

根据上面两组测试，我们发现了第一个优化策略。

逐行插入的效率远远低于多行插入

开发中，我们尽量多些多行插入，避免出现逐行插入的操作。
2. 优化策略二: 插入数据库大小导致内存溢出
上面我们已经插入了一千六百万的数据量，观察一下，此时的数据库大小。

一千六百万条数据一个表，导致内存大小为704.7MB。
看到这里，我们考虑一下Sqlite3操作数据库插入操作，虽然效率会比文件更高，但是，还是从硬盘上不断的读写操作，对大文件持续读写，会增加内存的增加，怎么解决这个问题，是我们需要关心优化的问题。
小编测试，加入缓存池可以降低内存消耗。插入操作，到底是如何执行的，我们来看一下这个文件，一切就云开雾散了。

这是在插入过程中，数据被锁住(locked)后，会不断生成journal文件，通过这个文件不断写入数据库中，这样不间断的写入读取，就增加了内存消耗。
3. 优化策略三: 修改数据库配置
FULL,NORMAL,OFF对应数据库三种操作。

PRAGMA synchronous = FULL; PRAGMA synchronous = NORMAL; PRAGMA synchronous = OFF; 

这三个语句，默认Sqlite使用FULL方式，这个是最安全的操作，也是最耗时的操作。
假设不为回滚日志考虑，同步锁我们可以关闭或者降低安全等级。在前面博主写的文章中分析了提升效率。

可是，小编做了测试，时间上相差不多,基本上都是0.06毫秒,出现这样的问题不排除数据量过小，或者循环导致,当我们把数据量增加到四亿或者十亿的时候，这个差距就更加明显了。
4. 优化策略四: 开起事务(显示事务)

BEGAIN COMMIT ROLLBACK 

上面三种情况都是在，开起事务中进行操作的。事务可以保证插入数据的安全。Sqlite3中每一条插入语句就是一个事务(默认)。假设我们不显示调用，那么事务锁就会不断的开起和关闭，这对于资源消耗是毁灭性的打击。
显示调用事务可以有效的加快运行效率。

BEGAIN INSERT INTO user(name,age) VALUES ('CreaterOS','21'),('CreaterOS','21')...； COMMIT ROLLBACK 

通过这样的方式，可以取消Sqlite3默认的事务锁。从而提高效率。
5.优化策略五: 修改Sqlite WAL

改变 checkpoint。我们在把 checkpoint 改成了10000（默认是1000），checkpoint = 1000 是
日志文件 1M 的时候回写到数据库，改成 10000 就变成了 日志文件 10M 时回写数据到数据库。 demo测试性能，有2倍+的提升

PRAGMA checkpoint = 10000； 

这个方法也是很有效的一种日志回写策略。
6…优化策略六: PRAGMA修改cache_size

PRAGMA cache_size = 8000； 

修改缓存大小来提高性能，具体情况可以根据不同业务逻辑，安全性考虑进行插入优化。

总结

通过上面六点我们已经进行了Sqlite3数据库插入优化，并且每种优化策略是实际测试有效的,假设大家还有更好的优化策略，可以在评论区留言一起分析数据库性能优化的那些事。