SQLite --- 数据写入优化实践

怎样才能提高数据写入数据库的效率呢，有没有一种方案，适用所有场景，一套配置一劳永逸。 很明显 ，是没有的！ 一种场景一种特定的配置，充分发挥SQLite 的功能。

下面分两种场景讨论：

• 1000 条小数据插入
• 1000 条小数据批量插入

测试设备：iPhone 5s ,系统10.3.2 。通过配置Synchronous ,JournalMode, PageSize 实现数据的插入。

Synchronous

• FULL 最安全，最慢
• NORMAL 这种方式比FULL模式快，但是存在极小概率在系统掉电或故障时数据库遭到损坏
• OFF 最快！ SQLite将数据提交给系统之后不会等待结果，直接继续执行，也就是说在一次事务的过程中会少了两次Flush文件操作。对于事务不熟悉的童鞋可以查看前一篇文章

JournalMode

多次事务插入

| Insert Data Size | JournalMode | Synchronous| Exec Time| Local DB File Size|
| :------| :------: | :------: |
| 5kb | DELETE | OFF |9.956748|12mb
| | TRUNCATE | OFF |1.466521|12mb
| | PERSIST | OFF |3.736435|12mb
| | WAL | OFF |0.927830|10mb
| | MEMORY | OFF |0.810265|12mb

| Insert Data Size | JournalMode | Synchronous| Exec Time| Local DB File Size|
| :------| :------: | :------: |
| 30kb | DELETE | OFF |13.454739|68mb
| | TRUNCATE | OFF |6.121738| 68mb
| | PERSIST | OFF |7.216433| 68mb
| | WAL | OFF |5.895529| 68mb
| | MEMORY | OFF |5.352473| 68mb

一次事务内批量插入

| Insert Data Size | JournalMode | Synchronous| Exec Time| Local DB File Size|
| :------| :------: | :------: |
| 5kb | DELETE | OFF |1.174607|12mb
| | TRUNCATE | OFF |0.932761|12mb
| | PERSIST | OFF |0.899149|12mb
| | WAL | OFF |1.195897|12mb
| | MEMORY | OFF |0.898593|12mb

| Insert Data Size | JournalMode | Synchronous| Exec Time| Local DB File Size|
| :------| :------: | :------: |
| 30kb | DELETE | OFF |5.130694|68mb
| | TRUNCATE | OFF |5.126468|68mb
| | PERSIST | OFF |5.029102|68mb
| | WAL | OFF |7.960080|68mb
| | MEMORY | OFF |5.118012|68mb

• TRUNCATE方式则是不删除文件，直接将文件内容清空（在很多系统上，这种清空比删除文件要快）
• PERSIST方式也不会删除文件，而是将文件头中长度字段置为0，在某些平台上这种方式会优于DELETE 和 TRUNCATE。
• Memory ，这种方式无疑是最快的，但是缺点也很明显，一旦死机，数据可能被破坏无法恢复。
• WAL 在批量小数据，多次事务的上，速度比Memory稍微慢点。但是结合安全点，WAL 是比较适合大量小数据的写入。WAL有个比较重要的概念： checkpoint 。关于它后面会详细介绍。简单一句，它不适合用来处理大数据 ，要也可以，需要调整checkpoint.
• 在默认模式DELETE下，多次事务和一次事务，差别非常大，建议多使用显式事务。

在一次事务内批量倒入，各模式的差别不大，因为IO次数有限。

PageSize

| Insert Data Size | PageSize | Synchronous| Exec Time| Local DB File Size|
| :------| :------: | :------: |
| 10kb | 1024 | FULL |2.121762|23mb
| | 2048 | FULL |2.019415|24mb
| | 4096 | FULL |1.975309|24mb
| | 8192 | FULL |1.851635|24mb
| | 65536 | FULL |2.227348|32mb
| 10kb | 1024 | OFF |1.570002|23mb
| | 2048 | OFF |1.649653|24mb
| | 4096 | OFF |1.631648|24mb
| | 8192 | OFF |1.896880|24mb
| | 65536 | OFF |2.124153|32mb

| Insert Data Size | PageSize | Synchronous| Exec Time| Local DB File Size|
| :------| :------: | :------: |
| 30kb | 1024 | FULL |6.009211|68mb
| | 2048 | FULL |5.508767|68mb
| | 4096 | FULL |5.577342|68mb
| | 8192 | FULL |5.131815|68mb
| | 65536 | FULL |5.399648|73mb
| 30kb | 1024 | OFF |5.592201|68mb
| | 2048 | OFF |5.113762| 68mb
| | 4096 | OFF |5.025970| 68mb
| | 8192 | OFF |4.574784|68mb
| | 65536 | OFF |5.305481|73mb

• PageSize 的大小从 1024 ,2048,4096,8192, 在同一种Synchronous模式下，执行时间也随PageSize增加而减少。pageSize 为8192 或者16384 ，对于大文件的存储能达到最好的性能。

• 以上数据均是通过显式事务的方式，多次事务的方式之所以不列出来，因为怕读者会看着睡着。LOL ， 其实多次事务的方式效率会比显式事务的方式慢很多。

结论

• 多使用显式事务。
• 选择合适的PageSize,详细请看SQLite 知识摘要 --- 存储。
• 操作频繁 ，大量小记录（不使用显式事务），建议使用JournalMode :WAL 搭配 Synchronous: OFF。
• 操作少 ，数据量大，使用默认的方式就好。看数据是否重要，可搭配 ：JournalMode : TRUNCATE | PERSIST 和 Synchronous: OFF。
• 数据量大，但是数据可以重建，建议使用JournalMode :Memory ，搭配 Synchronous: OFF。HttpURLCache 就属于这种。