sqlite 数据库性能调优

一 、完整性检查

   完整性检查 ,是保障 数据库可用 ,app可用的 ,程序启动后,优先要做的事情

   数据库损坏的表现: “database disk image is malformed”

  1 检查方式:

      (1) PRAGMA schema.integrity_check; 

        (2) PRAGMA schema.quick_check; 

        (3) select name from sqlite_master where type=‘table'

                   select 1 from %@ limit 1

  2 比较:

(1)integrity_check

 out-of-order records(乱序的记录)

 missing pages(缺页)

 malformed records(错误的记录)

 missing index entries(丢失的索引)

 UNIQUE constraint(唯一性约束)

 NOT NULL (非空约束)

耗时较多

(2) quick_check

   不检查约束条件

   不检查对索引内容与表内容匹配的校验

   耗时较短

(3)是一种不太靠谱的方式,只检查库中的表是否存在,表的部分数据正确与否。

         耗时最短 

(4)从耗时的角度来看:

 与库中的数据量 有很大关系

 当库中的数据量 <  10000 量级时 ,没啥区别。

 当达到 100000 时, 第三种的速度明显快很多。

  50万 数量级 时,效率是(1)与(2)的100倍。

 

  二、 影响数据性能的参数:

     1 底层 page_size ,table_size,cache_size

      在内存允许的情况下增加 page_size 和 cache_size 能够获得更快的查询速度。但过大的 page_size 也会造成 B-Tree 查询退化到二分查找、CPU 占用增加以及 OS 级 cache 命中率的下降的问题。

     通过反复比较测试不同组合的 page_size、cache_size、table_size、存储的数据类型以及各种可能的增删查改比例,我们发现后三者都是引起 page_size 和 cache_size 性能波动的因素。也就是说对于不同的数据库并不存在普遍适用的 page_size 和 cache_size 能一劳永逸的帮我们解决问题。

     2  使用事物 与 非事物,索引。

         批量更新数据的场景,使用数据库事务,批量更新数据是非常快的。

      3  缓存被编译后的 SQL 语句

       数据库使用的 SQL 语句也需要经过编译后才能被执行使用。SQL 语句的编译结果如果能够被缓存下来,第二次及以后再被使用时就能直接利用缓存结果,大大减少整个操作的执行时间。

      4 Full Text Search(FTS)

      5 .磁盘同步

 

PRAGMA synchronous;

PRAGMA synchronous = FULL; (2) 

PRAGMA synchronous = NORMAL; (1) 

PRAGMA synchronous = OFF; (0)

   当synchronous设置为FULL 

   SQLite数据库引擎在紧急时刻会暂停以确定  数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。 在SQLite 3中修改为FULL。

   当synchronous设置为NORMAL

   SQLite数据库引擎在大部分紧急时刻会暂停,但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。但实际上,在这种情况 下很可能你的硬盘已经不能使用,或者发生了其他的不可恢复的硬件错误。在SQLite 2中,缺省值为NORMAL.

   设置为synchronous OFF 

   SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃, 数据不会损伤,但在系统崩溃或写入数据时意外断电的情况下数据库可能会损坏。另一方面,在synchronous OFF时 一些操作可能会快50倍甚至更多。

     数据安全性不高的场景,可以考虑更改 syncchronous = off 的模式;(写数据的速度50倍+的提升)

     数据安全性较高的场景,可以考虑更改 syncchronous = nomal 的模式;(写数据的性能也有5倍+的提升)

   6  内存模式 

PRAGMA temp_store = DEFAULT; (0) 

PRAGMA temp_store = FILE; (1) 

PRAGMA temp_store = MEMORY; (2)

当temp_store设置为DEFAULT (0),使用编译时的C预处理宏 TEMP_STORE来定义储存临时表和临时索引的位置。 

当设置为FILE (1),则存放于文件中。temp_store_directory pragma 可用于指定存放该文件的目录。 

当设置为MEMORY (2),临时表和索引则存放于内存中。 

当改变temp_store设置,所有已存在的临时表,索引,触发器及视图将被立即删除。 

   

7  数据库加密

  SQLite 数据库加密对性能的损耗按照官方文档的评测大约在3%的 CPU 时间。  加密算法对性能的影响。

8  WAL 与 DEL 

       默认的是del ,

       读写操作时,DEL模式要处理各种锁。 

       写操作是独享的,写阻塞读。

      读完成的时候才能写,读阻塞写。

      数据由1万增加到2万。读100条的时候写10条,写10条数据所耗时间 。

WAL模式所耗时间为DEL模式所耗时间的1/4。

 

        数据由1万增加到2万。读100条的时候写10条。读100,数据所耗时间。

      DEL模式所耗时间略高于WAL模式所耗时间.时间越长次数越多,WAL效率越高

 

       WAL模式下要更进一步提升性能的话,可以考虑改变 checkpoint。

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

 

9  分库与分表

        (1)随着表中数据量的减少,查询的相同数量数据的时间逐渐减少。

       例如有与100个人的聊天记录,分表后,每打开一个会话时,读数据时,对应的表的规模是分表前的 1/100。查询速度必然增加。写数据时也是如此。

        (2)在设计数据库时,我们会把一个对象的属性分成不同的列按行存储。如果属性是个数量不定的数组,切忌不要把这个数组属性放到一个新表里面。上面我们提到过数据操作最耗时的其实是访问外存上面的数据。当数据量很大时,多张表的外存访问是非常慢的。这里的做法是讲数组数据用 JSON 序列化后,已 VARCHAR 或者 BLOB 的形式存成一列,和其他的数据放在同一个数据表当中。

 

10 mmap优化

        mmap对I/O性能的提升无需赘言,尤其是对于读操作。SQLite也在OS层封装了mmap的接口,可以无缝地切换mmap和普通的I/O接口。只需配置PRAGMA mmap_size=XXX即可开启mmap。 

 

参考:

http://www.trinea.cn/android/database-performance/

http://www.cocoachina.com/ios/20161109/18030.html

http://geek.csdn.net/news/detail/96248

http://blog.163.com/niuxiangshan@126/blog/static/170596595201221411454090/

http://www.cocoachina.com/ios/20161110/18030.html   SQLite3性能深入分析

发布了133 篇原创文章 · 获赞 21 · 访问量 30万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览