Android---SQLite优化

SQLite 是一款轻型的数据库，是遵循 ACID 关系型数据库管理系统，它包含在一个相对小的 C 库中。它是 D.RichardHipp 建立的公有领域项目。它的设计目的是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百 K 的内存就够了。它能够支持 Windows/Linux/Unix 等主流的操作系统，同时能够跟很多程序语言想结合，比如 Java、C#、PHP、Tcl 等，还有 ODBC 接口，统一比起 Mysql, PostgreSQL 者两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite 第一个 Alpha 版本诞生于 2000 年 5 月，到 2016 年已经有 16 个年头，SQLite 也迎来了一个新版本 SQLite3 ，并已经发布。

SQLite 数据类型

每个存储在 SQLite 数据库中的值都具有以下存储类型之一： 

存储类型	描述
NULL	值是一个 NULL 值。
INTEGER	值是一个带符号的整数，根据值的大小存储在1，2，3，4，6或8字节中
REAL	值是一个浮点值，存储为 8 字节的 IEEE 浮点数字
TEXT	值是一个文本字符串，使用数据库编码（UTF-8, UTF-16BE 或 UTF-16LE)存储
BLOB	值是一个 blob 数据，弯曲根据它的输入存储

SQL 基本语法

DDL 数据定义语言

命令	描述
CREATE	创建一个新的表，一个表的视图或者数据库中的其他对象
ALTER	修改数据库中某个已有的数据库对象，比如一个表
DROP	删除整个表，或者表的视图，或者数据库中的其他对象

 DML 数据库操作语言

命令	描述
INSERT	往表中插入一条记录
UPDATE	修改记录
DELETE	删除记录

DQL 数据查询语言

命令	描述
SELECT	从一个或多个表中检索某些记录

SQLite优化（面试）

索引

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

   优点：大大加快了数据库检索的速度，包括对单表查询、连表查询、分组查询、排序查询。经常是一到两个数量级的性能提升，且随着数据数量级增长。

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

索引分类

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

create index id_index on user(id)

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

create unique index id_index 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 子句

使用事务