sqlite学习笔记(1)

sqlite是高性能使用方便小型数据库,不仅一般用来作为数据库用,有时还会用来持久化数据,或者跨进程通信数据用,都很方便。

一、基本使用方法

1、最简单的用法。打开数据库 sqlite3_open,执行命令 sqlite3_exec,关闭数据库 sqlite3_close。

2、sqlite3_exec 其实是对一系列操作的封装, sqlite3_prepare(), sqlite3_step(), sqlite3_column(), and sqlite3_finalize()。 这时需要了解一个基本的概念 sqlite3_stmt, The prepared statement object,一个预处理对象?这个玩意似乎是数据库里面的一个通用概念,代表要执行的一系列操作。
a、sqlite3_prepare,创建一个预处理对象
b、sqlite3_bind_xxx 绑定参数
c、循环执行 sqlite3_step 获取数据
d、sqlite3_column 从数据中取出每一段
e、释放 sqlite3_finalize
详见 :https://www.sqlite.org/cintro.html

3、事务。批量写操作,记得用事务提交,可以数量级的缩短操作时间。

二、字段类型

sqlite中有2个概念,一个是存储类型,一个是数据亲和类型。如果一定要问数据类型的话,可以认为是 数据亲和类型 这个概念。

存储类型,常规使用时可以忽略。sqlite是一个动态类型的数据库。他的值的类型就是跟这个值相关,而不是他所属的列。sqlite认为他的动态类型更有优势。其中存储类型有 :NULL、INTEGER(最大64位)、REAL 浮点类型 64位、TEXT、BLOG,二进制,一列中可以存储任何存储类型。

关于数据亲和类型,sqlite为了与其他数据库兼容,引入了这个概念。 每个列都赋予了一个数据亲和类型,就是说,存储到这些列中的数据优先转换为这种类型。类型包括:TEXT、NUMERIC、INTEGER(跟 NUMERIC 差别不大)、REAL 浮点数、BLOB。 其他数据库的类型都会转换为这些亲和类型。 比如类型定义中如果有“INT”,亲和类型就是 INTEGER 。 所以,sql语句中无论是 INT、INTEGER、TINYINT、INT2、INT8、INT64,都被sqlite认为是 INTEGER 。

详见 : https://www.sqlite.org/datatype3.html

三、多进程与多线程


1、sqlite有个busy time的东西,如果处于默认不设置状态的话,多进程或者多线程访问时,在一个表被锁住的情况下,会直接返回 SQLITE_BUSY 的调用失败。这。。。。。。。。。。。大部分人都是愿意等,而不是要失败的吧,所以,使用的时候还是设置下比较好。
详见:https://www.sqlite.org/c3ref/busy_timeout.html

2、sqlite有三种线程模式, Single-thread、 Multi-thread、 Serialized。
其实可以忽略第二个 Multi-thread,Serialized才是一般所说的多线程支持,sqite默认编译也是这种。 多线程模式分为编译时和运行时几种设置,如果编译时不支持多线程的话,运行时也无法设置。 关于sqlite的锁,要详细了解的话,其实还是比较复杂的。一般使用的时候,知道支持多线程访问就好了。
详见:https://www.sqlite.org/threadsafe.html

3、至于多进程,只关注windows客户端本地(注意是本地,网络共享例外)使用的话,sqlite默认是支持多进程访问的。
详见:https://www.sqlite.org/faq.html

四、关于多线程和多进程的性能

1、同库同表操作
10W条记录的表中。单进程内的操作,读不到0.1毫秒,写的操作在十几毫秒。
但是一旦有了读写并发操作,会把读的操作也拖累到写的那个量级。
多进程操作同样,而且,多进程跟单进程内的操作耗时差不多。
测试环境及方法:
I7 CPU,固态硬盘。
读操作时,随机访问,一半访问有的记录,一半访问没有的记录。
写操作采用update操作,全部都update已有记录,保证表里面始终保持10W条记录。没有使用事务。
数据:
a、单进程单线程读, 0.039 ms
b、单进程单线程写, 15 ms
c、单进程双线程读,0.08ms。(其实单位时间内的次数还是不变的,因为有2个线程同时操作)
d、单进程双线程写,30ms,浮动在20-40ms之间(同上)。
e、单进程,一个线程读,一个线程写, 写操作均值 14ms 左右,读操作均值 15 ms。会出现100%左右的浮动。
f、两个进程并发去读,每次访问约 0.052ms
h、双进程写,波动很大,从 14-214ms都有,测试了三次,平均约27ms
i、一个进程读,一个进程写。读均值3ms,写倒是比较稳定,在15 ms左右。(怀疑数据异常,但也懒得深究)。
测试代码:

2、同库异表。
单进程内操作一个库里面不同的表,读操作被拖累到了20左右。多进程操作一个库里面不同的表,读操作也被拖累到了毫秒级,虽然不如同表受到的拖累大, 也是受到了拖累。

3、编码建议
a、读写并发时,采用隔一段时间用事务提交的方式。减少因为写操作导致的读操作被拖累,对于未提交数据的查询,可以先放内存里面自己来查询。
b、读写并发时,考虑是否能够接受写入操作时的耗时,如果不能接受,外部保存下状态,写操作进行时跳过数据查询
c、同库内多个表,如果没有关联的话,建议分库。
d、虽然多进程从数据上来看,对性能的影响跟单进程内多线程访问时一样的,但是考虑到上面 a 中未提交数据的查询问题,可以考虑采用一个进程作为服务进程,其他进程作为客户端进程来查询。当然,这里还要考虑到进程通信的耗时,实际情况实际处理吧。

sqlite自身的锁机制其实是比较复杂的,网上有不少关于这个东西的资料,也有同事分享过,但是我忘记了。。。。。。。。。。

五、踩过的坑

1,坑A:使用过程中发现过数据损坏的问题,具体损坏原因未知,猜测是硬盘的原因导致数据库文件错了。曾经存储过一段sha,结果发现读取与写入的不一致的情况,所以关键数据建议加上简单的校验。我那个sha就是加的CRC校验。
2,坑B:打开数据库的时候注意路径是否宽字符,他的open也是可以创建的数据库的。有一次我用错了,本来是想打开一个数据库的,结果他创建了一个,调试半天才发现问题。

参考资料:
sqlite官网:https://www.sqlite.org/cintro.html
一个国内的网站:http://www.runoob.com/sqlite/sqlite-tutorial.html

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

*