SQLite

https://github.com/phiresky/sqlite-zstd
对SQLite行的透明压缩.
对于JSON存储, 可以缩小75%的体积.
https://github.com/mlin/sqlite_zstd_vfs
对SQLite文件的透明压缩.
SQLite3与大多数RDBMS不同, 使用动态类型系统, 因此表的字段无法约束值的类型.
为了与其他RDBMS兼容, SQLite3在相应语法处支持了一种名为type affinity的特性,
该特性允许用户像其他RDBMS那样声明字段类型, 但这些类型本质上只是一种建议, 实际使用时能够被忽略.
动态类型系统的主要优势是允许一个字段具有多种类型的值.
但由于大多数支持JSON类型的RDBMS也可以达到类似的目的, 这项优势已经被消除了.
从3.37.0版本开始, SQLite3支持严格表, 从而与其他RDBMS的功能保持一致.
严格表的类型名受到很大限制, 只能使用以下类型, 因此会在表达语义时不畅:
  • INTEGER
  • INT: 与INTEGER应是同一类型, 但不知为何保留了此别名.
    推荐使用INTEGER, 因为这是SQLite3内置的类型.
    此外, SQLite3的自增主键使用的也是INTEGER类型, 使用其他类型(比如INT)会产生额外的字段.
  • REAL
  • TEXT
  • BLOB
  • ANY
SQLite的WASM版本:
https://github.com/sql-js/sql.js/
需要注意的是, 受限于WASM的设计, WASM版本的SQLite永远不可能比本机版本要快.
sql.js的IndexedDB后端:
https://github.com/jlongster/absurd-sql
根据作者的测试, 由于IndexedDB本身在批量操作上太慢, 使用IndexedDB后端的sql.js反而比IndexedDB要快.
唯一的缺点是WASM版本的sql.js大小在1MB左右.
一个基于Raft的完整的SQLite RDBMS, 客户端通过HTTP API通信.
早于Dqlite.
Canonical(Ubuntu的公司)创建的基于Raft的实现高可用性SQLite的C库.
该库被用于LXD项目.
.backup 命令创建一个内容一致的数据库备份副本.
如果备份期间有很多写入活动, 则副本创建的速度可能会很慢.
sqlite3 data.db '.backup backup.db'
.dump 命令创建一个数据库内容的转储.
sqlite3 data.db '.dump' > dump
SQLite3可以在运行时进行基于文件系统的快照备份.
https://litestream.io/
一个将SQLite数据库流式复制到S3兼容的存储系统的解决方案, 也可以用于备份.
SQLite3允许同时有多个读事务, 仅允许一个写事务,
如果同时出现多个写事务, 则除了第一个执行的写事务以外都会因为SQLITE_BUSY而失败.
读事务和写事务是根据开启事务后的SQL命令决定的, 一个读事务可能被升级为写事务.
SQLite有busy_timeout和busy_handler用来自动处理SQLITE_BUSY的情况,
在busy_timeout的时限内, SQLite会自动重试事务, 如果超出时限, 则抛出错误, 让应用程序接管.
  • BEGIN DEFERRED (默认): 只在出现写入时将事务升级为写事务.
    这种行为有一个缺点, 即两个互相依赖的事务可能会陷入死锁, 因为这两个事务在开始时都只是"读事务".
    https://activesphere.com/blog/2018/12/24/understanding-sqlite-busy
  • BEGIN IMMEDIATE: 在事务开始时就升级为写事务, 如果其他数据连接已经有写事务, 可能会以SQLITE_BUSY失败.
  • BEGIN EXCLUSIVE: 在WAL模式下与IMMEDIATE相同, 在其他模式下, 会阻止其他数据库连接的读事务.
在未启用WAL时, SQLite3会在开始事务时先把原始数据做一份备份, 如果事务回滚, 则用备份覆盖修改过的数据.
在启用WAL时, SQLite3的事务会作为日志被写入WAL文件, 不需要对原数据进行备份或修改, 因此写性能比原来高很多.
SQLite3默认情况下不会回收被删除的空间, 可以手动执行 VACUUM 来缩小数据库体积, 回收已经被删除的空间.
VACUUM的原理: 直接重新创建一个数据库, 在创建完毕后替换掉当前数据库.
VACUUM在重新创建数据库之前, 还会备份当前数据库.
由于以上原因, 执行VACUUM 需要最多相当于2倍当前数据库大小的空闲存储空间.
由于VACUUM会锁定整个数据库, 因此VACUUM的时机很重要, 普遍的做法是在关闭数据库前执行VACUUM或手动VACUUM.
SQLite3的auto_vacuum并不像它的名字那样可靠, 它实际上的工作方式与VACUUM有很大区别,
并且可能加剧磁盘碎片化, 所以通常不会使用它.
由于SQLite在每次查询中只能使用一个索引, 因此像一般DBMS那样单独为字段建立索引会导致严重的性能问题.
应该针对查询使用 EXPLAIN QUERY PLAN, 以确认是否真正利用了最好的索引.
参考: https://www.sqlite.org/eqp.html
SQLite复合索引的字段可以被其子集查询使用, 并且WHERE子句的字段不需要和索引字段的顺序完全一致
(Skip-Scan Optimization).
然而, 查询似乎不会利用复合唯一索引, 因此仍然有必要设置专门的索引.
复合索引的子集在一些情况下的性能仍然非常差,
通过 EXPLAIN QUERY PLAN 可知 SQLite3 的确利用了索引, 但实际运行却表现得像是遍历了全表.
这种情况下只能通过创建专门的索引来提升性能.
应当建立一个包含AND使用到的每个字段的复合索引.
如果查询同时包含WHERE和ORDER BY, 则需要建立WHERE字段和ORDER BY字段的复合索引.
仅仅为ORDER BY中的字段建立复合索引并不会被查询优化器使用.
建议将相关索引字段设置为使用ORDER BY时相同的索引顺序.
SQLite3有3个全文搜索扩展, 名为FTS3, FTS4, FTS5.
FTS5是FTS4改进后的新版本, 因此应该首先选择它.
https://www.sqlite.org/fts5.html
FTS5是一种虚拟表, 可以用以下方式创建:
CREATE VIRTUAL TABLE posts 
 USING FTS5(
         title
       , body
       , id UNINDEXED -- 不进入倒排索引
       );
该虚拟表不支持主键, 类型, 约束, 索引等常规表的特性,
因此无法确保特定列在虚拟表中的唯一性, 如果需要唯一性, 应使用外部内容表.
该虚拟表跟一般的表一样可以进行插入和查询操作.
理论上, 在虚拟表后端的真实表上是可以创建索引的, 但不建议尝试, 因为这介入了FTS的内部实现.
FTS5可以在创建虚拟表时指定分词器, 可以同时使用多个分词器.
FTS5内置了3个分词器:
  • unicode61 基于Unicode 6.1标准, 这是FTS5默认使用的分词器.
    在用于中文时, 所有连续的中文字符都会被算作一个词,
    除非在插入数据时用空格手动分词, 否则缺乏可用性.
  • ascii 将非ASCII字符转换为ASCII形式(例如注音字符将被转换为ASCII字符).
    在用于中文时, 效果与unicode61基本上是等同的.
  • porter 实现了porter stemming算法, 用于处理英文单词的词尾删除.
用户可以自己用C语言编写分词器.
根据FTS3的文档可知, FTS表只对rowid和MATCH查询具备高性能, 其他查询都基于线性扫描.
无内容的表在插入时不会创建列的副本, 不支持UPDATE和DELETE语句, 在查找时只能得到rowid值.
删除行时, 需要使用专门的语法.
https://www.sqlite.org/fts5.html#the_delete_command
无内容表无法使用一些统计功能, 例如bm25.
由于INSERT...RETURNING在虚拟表上不可用, 因此无法在插入表时返回rowid.
虽然SQLite官方可能在未来取消此限制, 但就目前来说, 无内容表因此需要在插入时手动指定rowid值.
以下是获得表中下一个rowid值的唯一方法, 应当在插入时开启事务:
SELECT MAX(rowid) FROM fts; -- 当表为空时, 返回NULL
无内容表需要配合交叉表使用, 需要建立一个交叉表连接起FTS的rowid和其他表中实际存在的行.
删除命令要求在删除时 提供与插入内容时相同的信息, 这是为了在索引中删除对应的内容.
外部内容表不在虚拟表里存储值的副本, 而是绑定同数据库里的另一个表/虚拟表/视图.
在这种情况下实际的文本和倒排索引是分离的,
因此用户需要手动维护FTS表与绑定表之间的内容同步, 或使用触发器.
删除行时, 需要使用专门的语法.
CREATE TABLE tbl (
  id   INTEGER PRIMARY KEY
, text TEXT
);
CREATE VIRTUAL TABLE fts
 USING fts5(text, content=tbl, content_rowid=id);
-- 在查找fts时, 会同时执行此查询:
SELECT id, text FROM tbl WHERE id = ?;
当同数据库中存在另一个表/虚拟表/视图时,
可以用外部内容表+触发器(或由编程语言保证同步)来节省下存储内容副本的空间,
这是外部内容表唯一的优势.
由于这种特性, 比起专门的倒排索引表, 使用外部内容表的FTS表给人感觉更像是传统的索引.
可以用以下三种语法查询FTS表, 它们在功能上是等价的:
-- 使用MATCH运算符(推荐)
SELECT *
  FROM posts
 WHERE posts MATCH 'keyword';
SELECT *
  FROM posts
 WHERE posts = 'keyword';
SELECT *
  FROM posts('keyword');
在默认情况下, FTS5查询不区分大小写.
使用 ORDER BY rank 可以按相关性排序, 最相关的行在最前面.
q1 + q2
在查询字符串的末尾加入 * (星号)可以匹配任何以该短语开头的token.
q1*
前缀搜索需要在创建FTS表时设置前缀索引选项, 该选项指示应该将token的前多少个字符进行索引.
可以使用括号改变运算符优先级.
  • q1 AND q2
  • q1 OR q2
  • q1 NOT q2
  • q1 AND (q2 OR q3)
辅助函数的功能基本上是为建立搜索引擎服务的.
返回当前匹配项的准确度, 数值越低, 表示准确度越高.
https://www.sqlite.org/fts5.html#the_bm25_function
这是rank的默认评分标准.
以包含搜索词的数量来选择一个用于展示的片段.
让返回结果带有标记, 标记通常被用于高亮搜索结果中的搜索词.