SQLite数据库设计基础点

原文链接:http://www.jianshu.com/p/6a1ebd08f003

这篇文章是很久之前写了一半,今天继续把它写完。自己整理过的知识点,吸收效果会更好。

监控(DB Hook)

  • 主线程的DB操作以及多余的DB操作
    根据业务需要,检查是否DB操作一定要在主线程执行,能异步执行的优先考虑异步。另外利用Instrument 检查是否有多余的 DB操作,例如:滚动列表,每次都执行一次数据库查询,这里显然是cache没做好。

  • 短时间内是否有对同一个表有多次事务操作,没有使用显式的事务
    使用sqlite3_commit_hook等Api 实现监控频繁的事务操作。例如: 数据从网络回来做持久化的时候,多条数据的保存,尽量使用一次事务,避免隐式的多次事务提交。

  • 监控超时的DB操作
    应用上线之后,对程序的稳定性进行监控是很有必要的。用户基数大就可以暴露一些平时开发过程中难以发现的问题。因此,DB的操作可以按需进行监控。

异常

  • DB损坏自动修复
    SQLite 是以B树结构存储的,如果某一个节点发生损坏,可能导致无法读取数据。损坏的原因很多,最普遍的就是手机突然断电、文件系统错误、硬盘损坏等。恢复的方法下面会详细说明。
  • 空间不足
    16 GB的手机在市面上还是很多,并且目前看来32G也不够用了。空间不足是一个必须处理的问题。处理的方式有多种:设置自动清理逻辑,如果还行,就提示用户进行手动清除,如果还是不行,将App还原至初装状态。

存取

  • 读写分离

  • 分表

  • 并发管理

  • WAL优化


下面会重点学习一下:

  • DB的监控
  • DB损坏的自我修复
  • DB的存取,分表,WAL优化

DBHook

sqlite提供了一些函数,用户执行数据库操作后的回调:

每次数据写入wal 的时候都会回调注册的callback
SQLITE_API void *sqlite3_wal_hook(
  //数据库句柄
  sqlite3*,  

  //回调函数,参数一是在sqlite3_wal_hook传入的参数三的copy,参数三是
  //数据库的名字,最后一个参数写入wal的page数量
  int(*)(void *,sqlite3*,const char*,int),  

  //需要在回调函数传入的参数,userInfo
  void*
);
void registerWalHook(const UserInfo *userInfo, void *info)
{
      sqlite3_wal_hook(
            (sqlite3 *) m_handle,
            [](void *p, sqlite3 *, const char *, int pages) -> int {
                UserInfo * tempUserInfo = (UserInfo *) p;
                tempUserInfo->walCommited(tempUserInfo->handle, pages,
                                             tempUserInfo->info);
                return SQLITE_OK;
            },
       & userInfo);
}

期望通过hook的方式,实现数据库DB操作耗时统计,目前demo还在写。先略过。。。

DB损坏的自我修复

手机突然断电、文件系统错误、硬盘损坏等都有可能造成数据的损坏,SQLite 提供了检查数据库完整性的命令

PRAGMA integrity_check

关于数据修复,WCDB这篇文章写得详细,这里就在重复了。

DB的存取,分表,WAL优化

SQLite本身提供了多线程机制,要注意的是必须确保一个connection同一时间只能在一个线程内调用,开启多线程模式之后,就可以实现真正的并发了,当然,只是读与读、读与写之间的并发,写与写之间还是要串行执行。

设置线程模型

SQLite 可以通过以下三种方式进行线程模型的设置:

  • 编译期设定 通过 SQLITE_THREADSAFE 这个参数进行编译器的设定来选择线程模型
  • 初始化设定 通过调用 sqlite3_config() 可以在 SQLite 初始化时进行设定
  • 运行时设定 通过调用 sqlite3_open_v2() 接口指定数据库连接的数据库模型

另外需要注意的是要确保同一个句柄同一时间只有一个线程在操作,选择日志模式:WAL,这种方式可以提高事务的并发性。写操作会先 append 到 WAL 文件末尾,而不是直接覆盖旧数据。而读操作开始时,会记下当前的 WAL 文件状态,并且只访问在此之前的数据。这就确保了多线程读与读、读与写之间可以并发地进行,WAL适合写多读少

读写分离,一写一读

读操作所需要的share 锁是可以共享的,因此,读操作建议一个线程或以上进行只读操作。具体是多少要根据具体业务分析,像微信这种DB操作这么频繁的app ,一写多读应该是正常的。

2301467-8b7f1b51cc7cffad.png

写操作依然是需要串行执行,一次默认配置的写过程如下图:

2301467-e754dd54177d0fd1.png
一次事务.png

这里面还涉及到日志模式,磁盘同步方式等的配置,详细可看SQLite 知识摘要 --- 事务

对于每一条查询指令,都可以使用explain query。它是用来向开发人员解释在数据库内部一条查询语句是如何进行的。在 SQLite 数据库内部,一条查询语句可能的执行方式是多种多样的。它有可能会扫描整张数据表,也可能会扫描主键子表、索引子表,或者是这些方式的组合。具体的关于 SQLite 查询的方式可以参看官方文档 Query Planning

简单的说,SQLite 对主键会按照平衡多叉树理论对其建树,使其搜索速度降低到 Log(N)。

任务优先级

数据持久化到本地,可能存在这样的场景。假设在使用今日头条的时候,用户刷新了当前频道的信息。数据从网络回来,需要马上写盘。但是由于后台一些耗时的任务在执行,导致高优先级的任务需要等待低优先级的任务结束后才能执行(与UI操作相关的优先级高,后台任务优先级最低),那么如何让高优先级的任务优先执行是一个问题

默认把跟UI关系紧密的DB请求或者核心业务场景的请求分配到高优先队列里面,把一些后台的耗时任务放到低优先级队列里,保证UI相关操作能及时响应,同时也能减少其他任务的空闲等待。

并发带来的问题

并发带来性能优化的同时也引起了不少问题,微信公开了自己的解决方案。通过查阅微信iOS SQLite源码优化实践可知。优化点如下:

  • 优化Busy retry
  • 禁用文件锁,内存统计锁
  • I/O 性能优化(保留 WAL 文件大小,mmap 优化)

详细的可以看微信iOS SQLite源码优化实践

分库分表

根据实际情况进行分库分表的设计,可以提高性能。下面以微信mac 版为例,看一下微信是怎么做的。打开Activity monitor,查看微信的信息,通过Activity monitor可以知道微信打开了什么文件,然后定位到涉及DB 的文件路径。如下图所示:

2301467-a26c8d70186d6f8a.png
2301467-c6fcfbdea3616cd5.png

每个业务都有一个单独DB文件,唯独message那里不一样,DB操作最频繁的也是message了。它的结构如下:

2301467-4256d857222cd471.png

目前还没弄清楚它是怎样的结构。

  • 假设用一张表保存所有聊天记录,那么因为SQLite 中所有的表信息都是通过 B+ 树进行存储,在数据量大的情况下,会导致整个树深度过大,增加多余的 I/O 操作。所以是不可能单表存所有信息的。(只是为了说明问题才有这个一看就不可能的假设)

  • 假设一个联系人一张表,因为每个表在物理上被划分为多个区间,在空间上被内聚到邻近磁盘区间,数据内聚,减少 I/O 操作,加快查询速度。分表的优点是很明显的,缺点缺不那么明显,但是一出现就很难处理。

分表的缺点

Limits In SQLite里面最后一条记录:

**Maximum Number Of Tables In A Schema**
Each table and index requires at least one page in the database file. An "index" in the previous sentence means an index created explicitly using a [CREATE INDEX](https://sqlite.org/lang_createindex.html) statement or implicit indices created by UNIQUE and PRIMARY KEY constraints. Since the maximum number of pages in a database file is 2147483646 (a little over 2 billion) this is also then an upper bound on the number of tables and indices in a schema.
Whenever a database is opened, the entire schema is scanned and parsed and a parse tree for the schema is held in memory. That means that database connection startup time and initial memory usage is proportional to the size of the schema.
  • 当表数量超过千这个数量级后,对于内存和启动速度都会有较大影响。原因在于当数据库被打开后,整个 schema 都会被扫描并进行解析,最终形成一棵树结构并保存在内存中

  • 加载的schema越多,这块内存的占用也会越大

  • 表越多,建表也越慢,导致操作响应慢;

但是在能够控制表数量的前提下,分表是有效的。一旦表数目超过千万级别后,分表带来的启动速度变慢将难以接受,启动时将扫描 sqlite_master 并解析,最终形成内存中的树结构。在其中由于 SQLite 默认的 rehash 实现中对桶数量有限制,而使得插入大量数据时效率从哈希表退化为链表插入效率,可以通过修改 SQLITE_MALLOC_SOFT_LIMIT 放大限制。

// SQLITE_MALLOC_SOFT_LIMIT
#if !defined(SQLITE_MALLOC_SOFT_LIMIT)  
# define SQLITE_MALLOC_SOFT_LIMIT 1024  
#endif  

通过修改SQLITE_MALLOC_SOFT_LIMIT 可以缓解启动速度的问题,但肯定不是最好的方案。

Ref

SQLite 分表
Limits In SQLite
微信iOS SQLite源码优化实践

展开阅读全文

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