简介

SQLite是一个轻量级的关系型数据库，运算速度快，占用资源少，很适合在移动设备上使用，不仅支持标准SQL语法，还遵循ACID(数据库事务)原则，无需账号，使用起来非常方便！

SQLite的存储类型有五类，分别是 NULL、INTEGER、REAL、TEXT和BLOB。SQLite支持列的亲和类型概念，即任何列仍然可以存储任何类型的数据。

sqlite体系结构图

数据存储（存储位置、存储形式）

SQLite通过文件来保存数据库在本地，一个文件就是一个数据库，数据库中又包含多个表格，表格里又有多条记录，每个记录由多个字段构成，每个字段有对应的值，每个值我们可以指定类型，也可以不指定类型（主键除外）。

数据结构和组织

SQLite的一个DB对应一个物理文件，文件中的数据被分成若干个大小相等的 Page 进行存储，这些 Page 之间的关系由树形结构来组织管理。SQLite 以 B+树（B+tree）的数据结构形式存储在磁盘上，最大支持 2TB 的数据库。

B/B+树广泛应用于文件系统及数据库中，如：

Window：HPFS文件系统
Mac：HFS，HFS+文件系统
Linux：ResiserFS，XFS，Ext3FS，JFS文件系统
数据库：Oracle，MySQL，SQLServer，SQLite等了解B树与B+树

SQLite3数据库总体结构

SQLite3数据库表的B+树结构

SQLite数据库页面结构

注：以上三张图来自朱清华的论文《基于Android手机SQLite的取证系统设计实现》

关于大文件究竟是直接存储在db,还是将文件持久化到本地，然后将数据索引添加到数据库呢？

官方答案：
- pageSize 为8192 或者16384 ，对于大文件的存储能达到最好的性能
- 大于100 kb 的文件建议用外部存储，小于100kb 的直接写db就好。
如何选择适合自己的 pageSize 呢？

假设有一个字段，存储数据平均大小为 M，我们只要确保尽量不要出现溢出就好。也就是 pageSize 尽量比 M 大一点，避免开辟溢出页。当存在很大的数据，那么直接用外部存储吧，数据库用来存外部数据索引就好。对于第二个问题，很简单，把经常查询需要用到的字段安排在前面，避免存到溢出页。

pageSize 默认值是 1024，理论上的设置范围是 512~65536（必须是2的幂），之所以称之为理论值，是因为它还受到 SQLITE_MAX_DEFAULT_PAGE_SIZE 的限制。需要注意的是，page_size的设置需要在数据库创建之前才能生效！

数据索引 (如何找到数据)

索引介绍

数据库的索引跟字典目录的概念是一致的。索引本质上是一个指向数据库中的数据的指针。索引是有序的，因而可以对其执行二分查找。

索引有助于加快 SELECT 查询和 WHERE 子句，但会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或者删除，但不会影响数据。

上面提及到，SQLite底层是使用B+树来存储数据的，B+树的结构特点就是：非叶子节点仅具有索引作用，所有的数据均存放在叶子节点中。每一个叶子节点都带有指向下一个节点的指针，形成了一个有序链表。

B+树示例

比如需要查询元素3：

第一次磁盘IO

第二次磁盘IO

第三次磁盘IO

索引的优点：

大大加快了数据的检索速度，这也是创建索引的主要原因
加快表和表之间的链接
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

索引的缺点：

创建索引需要耗费一定的时间
索引需要占用物理空间，特别是聚集索引，需要较大的空间（SQLite不支持聚集索引）
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度，这个是比较大的问题。

索引语句

单列索引：只基于表的一个列上创建的索引
```
CREATE INDEX index_name
ON table_name (column_name);
```
唯一索引：不允许任何重复的值插入到表中,为了性能，同时也为了数据的完整性
```
CREATE UNIQUE INDEX index_name
on table_name (column_name);
```
组合索引：基于一个表的两个或多个列上创建的索引
```
CREATE INDEX index_name
on table_name (column1, column2);
```
隐式索引：在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

数据的更新和查询

SQLite 只提供了表级锁，在并发、多线程操作上存在局限性。 SQLite支持使用 SQL语言来进行更新和查询。

SQL语句

更新

UPDATE table_name
SET column1 = value1, column2 = value2....,columnN = valueN
WHERE [condition];

查询

SELECT column1,column2,columnN
FROM table_name
WHERE [condition]

数据的备份和冗余 (防止丢失和保持可用)

SQLite可以直接备份数据库文件来进行全量备份（手动操作，恢复的时候进行数据库文件替换即可）。

sqlite> .backup 'backup.db'
sqlite> .restore 'backup.db'

数据一致性, 事务

事务是对一个数据库执行工作单元。你可以把许多的 SQLite 查询联合成一组，把这些放在一起作为事务的一部分进行执行。

事务保障了关系数据库的一致性，事务的准则是：要么全部成功，要么全部失败。

当发出begin命令时，事务将持续到调用commit或rollback,或者sql命令引起约束违反进而导致rollback。一般情况下，锁持续时间隐藏在事务持续时间内，他们总是一起结束。下图可以看到事务的声明周期和锁状态

SQLite锁状态

事务的控制主要有三条命令：

BEGIN TRANSACTIION 开始处理事务
COMMIT 保存更改，或者可以使用 END TRANSACTION 命令
ROLLBACK 回滚所做的更改

事务控制命令只与 DML 命令 INSERT、UPDATE 和 DELETE 一起使用。他们不能在创建表或删除表时使用，因为这些操作在数据库中是自动提交的。

事务详解

数据管理

数据分析

和PostgreSQL非常相似，SQLite中的ANALYZE命令也同样用于分析数据表和索引中的数据，并将统计结果存放于SQLite的内部系统表中，以便于查询优化器可以根据分析后的统计数据选择最优的查询执行路径，从而提高整个查询的效率。见如下示例：

如果在ANALYZE命令之后没有指定任何参数，则分析当前连接中所有Attached数据库中的表和索引。
```
sqlite> ANALYZE;
```
如果指定数据库作为ANALYZE的参数，那么该数据库下的所有表和索引都将被分析并生成统计数据。
```
sqlite> ANALYZE main;
```
如果指定了数据库中的某个表或索引为ANALYZE的参数，那么该表和其所有关联的索引都将被分析。
```
sqlite> ANALYZE main.testtable;
sqlite> ANALYZE main.testtable_idx2;
```