小巧玲珑的SQLite

SQLite是桌面端和移动端的不错选择

前言

SQLite身影无处不在。
SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至今已经有14个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

SQLite的能力

SQLIte数据库最大支持128TiB(140 terabytes, or 128 tebibytes, or 140,000 gigabytes or 128,000 gibibytes).
在7G文件大小下，6千万条记录的有索引某个表中，查询条件如果是索引字段，不管什么位置，查询都不要1ms时间。

案例1

插入：
插入100万行数据的产生数据时间就有100万次for循环。并且表除了主键，没有其它索引。
插入100万行 40s. 每行有19个字段，数据库文件占225M
插入1000万行，1个小时没完成，测试程序卡死。占用内存90%。经过分析，其实1000万行数据在30分钟左右时就插入完毕。可能是内存不足，导致响应特别慢。数据库文件占空间2.5G。由于笔记本内存不足，接下来不再测试1000万行的情况。
插入300万行，122910ms，即123秒，其中产生数据的时间和释放数据内存的时间占了96秒。接着，第二次插入300万行，128910ms，即
129秒。第三次插入300万行，125575ms，即126秒。cpu保持在47%、内存保持在36%，其中数据本身占的内存23%。

查询:
查询性能情况：
查询语句 select * from 表名 where cardid=‘4600331’
未建立索引的情况
1000万行查询1.5秒
建立索引后，1毫秒内
在两个字段上建立索引后，插入数据时间变长。插入300万行，122910ms，即123秒，其中产生数据的时间和释放数据内存的时间占了96秒。
接着，第二次插入300万行，128910ms，即129秒。
第三次插入300万行，290575ms，即290秒。
第四次插入100万行，279031ms，即279秒。
建立索引后1000万行数据占空间2.8G。
更新单行数据133ms。删除单行数据104ms。

注意：建立索引后，插入数据时间倍增。第一次插入300万行约2分钟，接着第二次插入300万行5分钟，第三次约7分钟。

SQLite的使用场景

对于重要程度不是特别高的场合，可以大量使用SQLite库保存历史数据，平时用不到的时候只占硬盘，不占内存。
SQLite 文档还贴心地指出了什么时候用 client/server SQL 数据库（如MySQL）：

• Is the data separated from the application by a network? → choose client/server
• Many concurrent writers? → choose client/server
• Big data? → choose client/serverOtherwise → choose SQLite!

sqlcipher

安装：

brew install SQLCipher
pip3 install pysqlcipher3

代码示例

import peewee
from playhouse.sqlcipher_ext import SqlCipherDatabasedb = SqlCipherDatabase('testsqlcipher.db', passphrase=get_good_passphrase())
db_proxy = peewee.Proxy()
db_proxy.initialize(db)class Model(peewee.Model):class Meta:database = db_proxyclass Person(Model):name = peewee.CharField(primary_key=True)age = peewee.IntegerField()def __unicode__(self):return u"{0} (age {1})".format(self.name,self.age)Person.create_table()
Person("alice",17)
Person("bob",23)

常用技巧

两个Sqlite库之间导数据

attach DataBase '/opt/data/my.db' as db2;
INSERT into StockBaseInfo select S.* from db2.StockBaseInfo as S
detach database db2;