[移动开发]SQLite优化

1、索引

简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。

优点：大大加快了数据库检索的速度

缺点：索引的创建和维护存在消耗，索引会占用物理空间，且随着数据量的增加而增加。在对数据库进行增删改时需要维护索引，所以会对增删改的性能存在影响。

索引分类

直接创建索引：使用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发生时建议使用索引。并且选择度越大，建索引越有优势，这里选择度指一个字段中唯一值的数量/总的数量。

经常同时存取多列，且每列都含有重复值可考虑建立复合索引。

索引使用规则

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

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

比较值避免使用NULL

使用事务

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

原子提交：原子提交意味着同一事务内的所有修改要么都完成要么都不做，如果某个修改失败，会自动回滚使得所有修改不生效

更优性能：Sqlite默认会为每个插入、更新操作创建一个事务，并且在每次插入、更新后立即提交。

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

其他优化

语句的拼接使用StringBuilder代替String

查询时返回更少的结果集及更少的字段。

少用cursor.getColumnIndex

不要在UI主线程操作数据库

对于 BLOB 或超大的 Text 列，可能会超出一个页的大小，导致出现超大页。建议将这列单独拆表，或者放大表字段的后面

定期整理或者清理无用或可删除的数据，例如删除数据库比较久远的数据，如果用户访问到这部分数据，重新从网络拉取即可

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

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

SQLite安全问题

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

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

1. 这种方式并不是彻底的加密，还是可以通过数据库查看到表结构等信息。

2. 对于数据库的数据，数据都是分散的，要对所有数据都进行加解密操作会严重影响性能。

对数据库文件加密

将整个数据库整个文件加密，这种方式基本上能解决数据库的信息安全问题。目前已有的**SQLite**加密基本都是通过这种方式实现的，常见的几种加密方式是

SQLite Encryption Extension (SEE)

事实上SQLite在设计之初是有暴露加解密接口，只是免费版本没有实现而已。而SQLite Encryption Extension (SEE)就是SQLite的加密版本，收费的

?SQLiteEncrypt

使用AES加密，其原理是实现了开源免费版SQLite没有实现的加密相关接口，SQLiteEncrypt是收费的。

SQLiteCrypt

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

sqlcipher

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

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