SQlite 优化和不常用语法

2020-11-10 本文已影响0人 zcwfeng

SQLite PRAGMA

SQLite 的 PRAGMA 命令是一个特殊的命令，可以用在 SQLite 环境内控制各种环境变量和状态标志。一个 PRAGMA 值可以被读取，也可以根据需求进行设置。

要查询当前的 PRAGMA 值，只需要提供该 pragma 的名字：

PRAGMA pragma_name;

要为 PRAGMA 设置一个新的值，语法如下：

PRAGMA pragma_name = value;

设置模式，可以是名称或等值的整数，但返回的值将始终是一个整数

journal_mode Pragma

journal_mode Pragma 获取或设置控制日志文件如何存储和处理的日志模式。语法如下：:

PRAGMA journal_mode;
PRAGMA journal_mode = mode;
PRAGMA database.journal_mode;
PRAGMA database.journal_mode = mode;

这里支持五种日志模式：

Pragma 值	描述
DELETE	默认模式。在该模式下，在事务结束时，日志文件将被删除。
TRUNCATE	日志文件被阶段为零字节长度。
PERSIST	日志文件被留在原地，但头部被重写，表明日志不再有效。
MEMORY	日志记录保留在内存中，而不是磁盘上。
OFF	不保留任何日志记录。
WAL	write ahead log，3.7.0引入，日志中记录修改页，提交时只需刷修改页

在3.7.0以后，WAL(Write-Ahead Log)模式可以使用，是另一种实现事务原子性的方法。

WAL的优点
1. 在大多数情况下更快
2. 并行性更高。因为读操作和写操作可以并行。
3. 文件IO更加有序化，串行化（more sequential）
4. 使用fsync()的次数更少，在fsync()调用时好时坏的机器上较为未定。
缺点
1. 一般情况下需要VFS支持共享内存模式。(shared-memory primitives)
2. 操作数据库文件的进程必须在同一台主机上，不能用在网络操作系统。
3. 持有多个数据库文件的数据库连接对于单个数据库时原子的，对于全部数据库是不原子的。
4. 进入WAL模式以后不能修改page的size。
5. 不能打开只读的WAL数据库(Read-Only Databases)，这进程必须有"-shm"文件的写权限。
6. 对于只进行读操作，很少进行写操作的数据库，要慢那么1到2个百分点。
7. 会有多余的"-wal"和"-shm"文件
8. 需要开发者注意checkpointing

SQLite优化

索引

简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

优点：大大加快了数据库检索的速度，包括对单表查询、连表查询、分组查询、排序查询。经常是一到两个数量级的性能提升，且随着数据数量级增长。
缺点：索引的创建和维护存在消耗，索引会占用物理空间，且随着数据量的增加而增加。在对数据库进行增删改时需要维护索引，所以会对增删改的性能存在影响。

索引分类

直接创建索引：使用sql语句创建，Android中可以在SQLiteOpenHelper的onCreate或是onUpgrade中直接excuSql创建语句

create index id_index on user(id);

唯一性索引：保证在索引列中的全部数据是唯一的，对聚簇索引和非聚簇索引都可以使用

create unique index id_cindex on user(id);

单个索引：索引建立语句中仅包含单个字段，如上面的普通索引和唯一性索引创建示例
复合索引：又叫组合索引，在索引建立语句中同时包含多个字段

create index cx_index on user(id,name);

使用场景

当某字段数据更新频率较低，查询频率较高，经常有范围查询(>, <, =, >=, <=)或order by、group by发生时建议使用索引。并且选择度越大，建索引越有优势，这里选择度指一个字段中唯一值的数量/总的数量。
经常同时存取多列，且每列都含有重复值可考虑建立复合索引。

索引使用规则

对于复合索引，把使用最频繁的列做为前导列(索引中第一个字段)。如果查询时前导列不在查询条件中则该复合索引不会被使用。

create index complex_index on user(id,name);
select * from user where id >3;--使用了索引
select * from user where name like 'z*'; --未使用索引
--检验是否使用了索引
sqlite> explain query plan select * from user where id >3;
QUERY PLAN
`--SEARCH TABLE user USING INDEX sqlite_autoindex_user_1 (id>?)
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> explain query plan select * from user where name like 'z*';
QUERY PLAN
`--SCAN TABLE user
Run Time: real 0.001 user 0.000000 sys 0.000000

避免对索引列进行计算，对where子句列的任何计算如果不能被编译优化，都会导致查询时索引失效

 select * from user where cast(id as char) ='3';
 --检验
explain query plan select * from user where cast(id as char) ='3';
QUERY PLAN
`--SCAN TABLE user
Run Time: real 0.001 user 0.000000 sys 0.000000

比较值避免使用NULL
多表查询时要注意是选择合适的表做为内表。连接条件要充份考虑带有索引的表、行数多的表，内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。实际多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。
查询列与索引列次序一致
用多表连接代替EXISTS子句
把过滤记录数最多的条件放在最前面

使用事务

使用事务的两大好处是原子提交和更优性能

原子提交：原则提交意味着同一事务内的所有修改要么都完成要么都不做，如果某个修改失败，会自动回滚使得所有修改不生效
更优性能：Sqlite默认会为每个插入、更新操作创建一个事务，并且在每次插入、更新后立即提交。

这样如果连续插入100次数据实际是创建事务->执行语句->提交这个过程被重复执行了100次。如果我们显示的创建事务->执行100条语句->提交会使得这个创建事务和提交这个过程只做一次，通过这种一次性事务可以使得性能大幅提升

其他优化

语句的拼接使用StringBuilder代替String
查询时返回更少的结果集及更少的字段。
少用cursor.getColumnIndex
不要在UI主线程操作数据库
对于 BLOB 或超大的 Text 列，可能会超出一个页的大小，导致出现超大页。建议将这列单独拆表，或者放大表字段的后面
定期整理或者清理无用或可删除的数据，例如删除数据库比较久远的数据，如果用户访问到这部分数据，重新从网络拉取即可

神级优化(了解)

开启WAL模式，采用多连接

DELETE模式不支持读写并发,WAL模式模式支持读写并发,不支持读写并发影响很多情况下会导致查询和写入一方会非常慢严重影响用户体验,然后即使开启了WAL模式,程序上也要相应处理不然一样不能读写并发,要做到sqlite3读写并发两大条件:1,开启WAL模式；2,要采用多连接(至少两连接,一读一写)

开启方法

 pragma journal_mode=WAL;
 --多线程并发
 pragma sqlite_threadsafe = 2;

android代码开启

SQLiteDatabase db = SQLiteDatabase.openDatabase("db_filename", 
cursorFactory,CREATE_IF_NECESSARY, myDatabaseErrorHandler);
db.enableWriteAheadLogging();//开启WAL模式

索引和链表性能

合适的索引能大幅提高性能,联表查询,大数据量表杜绝联表查询改成几个查询,由程序实现相关功能性能会高的多

分表/分库

分表分库已经是优化单个sql的效率数据库层面最终极的方案

io优化

分库减少文件体积;mmap接口;不清空wal文件等

https://github.com/Tencent/wcdb/wiki 拿到源码，自己定制
https://github.com/Meituan-Dianping/SQLAdvisor

SQLAdvisor是由美团点评公司技术工程部DBA团队（北京）开发维护的一个分析SQL给出索引优化建议的工具。它基于MySQL原生态词法解析，结合分析SQL中的where条件、聚合条件、多表Join关系给出索引优化建议。目前SQLAdvisor在美团点评内部广泛应用，公司内部对SQLAdvisor的开发全面转到github上，开源和内部使用保持一致。

QLite源码分析参考

https://huili.github.io/sqlite/sqliteintro.html

SQLite安全问题

将内容加密后再写入数据库

这种方式使用起来简单，在入库/出库的过程中只需要将字段做对应的加解密操作即可，一定程度上解决了将数据赤裸裸暴露的问题。但也有很大弊端：

这种方式并不是彻底的加密，还是可以通过数据库查看到表结构等信息。
对于数据库的数据，数据都是分散的，要对所有数据都进行加解密操作会严重影响性能。

对数据库文件加密

将整个数据库整个文件加密，这种方式基本上能解决数据库的信息安全问题。目前已有的SQLite加密基本都是通过这种方式实现的，常见的几种加密方式是 SQLite Encryption Extension (SEE) 事实上SQLite在设计之初是有暴露加解密接口，只是免费版本没有实现而已。而SQLite Encryption Extension (SEE)就是SQLite的加密版本，收费的

SQLiteEncrypt 使用AES加密，其原理是实现了开源免费版SQLite没有实现的加密相关接口，SQLiteEncrypt是收费的。
SQLiteCrypt 使用256-bit AES加密，其原理和SQLiteEncrypt一样，都是实现了SQLite的加密相关接口，SQLiteCrypt也是收费的。
SQLCipher

https://github.com/sqlcipher/sqlcipher

需要说明的是，SQLCipher是完全开源的，代码托管在Github上。SQLCipher同样也是使用256-bit AES加密，由于其基于免费版的SQLite，主要的加密接口和SQLite是相同的，但也增加了一些自己的接口。

对于大部分开发者来说，兼顾安全性和成本的同时，免费版本的SQLCipher也是我们优先采取的安全性加固方案。

并发问题

SQLite 不支持并发

SQLiteDatabaseLockedException