1. 项目背景为什么我放弃了 LIKE 查询1.1 一个让我下定决心换方案的真实场景大概半年前我在维护一个本地知识库工具里面存了几万条从技术文档里切出来的句子用户需要输入一个关键词立刻找出包含这个词的所有句子。最开始我图省事直接用了 SQLite 最基本的WHERE content LIKE %关键词%数据量在两三万条的时候其实还能忍最多几百毫秒。但等我把导入的数据扩展到十几万条情况开始失控——一次搜索动不动就要两三秒而且随着数据继续增长这条曲线是近乎线性的。当时我还没意识到问题出在“全表扫描”上。LIKE %关键词%这种写法以%开头数据库根本没办法走普通索引只能一行一行地把所有记录读出来做子串匹配。假设你有 10 万条句子每条平均 100 个字符那单次搜索要处理的文本量是 1000 万个字符这个成本是你无法绕过、也无法通过加索引解决的。后来我查了一圈资料发现 SQLite 其实自带了一个叫 FTS5 的全文搜索引擎模块专门就是干这个事的。它通过倒排索引把“哪些句子包含哪个词”提前记下来查询的时候直接查索引而不是把所有句子重新读一遍。我把方案改掉之后同样 10 万条数据关键词搜索的耗时从秒级降到了几十毫秒而且数据量越大优势越明显。1.2 SQLite 自带“搜索内核”只是很多人不知道很多人对 SQLite 的印象停留在“一个嵌入式数据库、存储工具”但 SQLite 的能力边界远不止SELECT、INSERT、UPDATE、DELETE。它内置的 FTS5 模块是一个完整的全文检索引擎支持倒排索引、BM25 相关性排序、布尔查询、短语查询、前缀查询甚至还能通过 trigram 分词器处理中文子串匹配。换句话说你不需要额外搭建 Elasticsearch不需要引入外部服务只要项目里已经用了 SQLite就等于自带了一个搜索引擎。这篇内容我会从 FTS5 的原理开始讲起然后给出完整的建表、插入、同步、查询代码再分享我在实际项目中踩过的坑——包括中文分词、乱码、特殊字符转义、更新删除的诡异行为等。适合正在做本地工具、桌面软件、移动端 App、嵌入式设备或者任何“数据量几十万条以内、不想为了搜索专门引入重型服务”的场景。2. 先搞清楚 FTS5 到底做了什么事2.1 倒排索引像查字典一样找关键词要理解 FTS5 为什么快得先看一个很朴素的思想实验假设你有一本 500 页的小说让你找出所有出现“黄昏”这个句子的页码你会怎么做最笨的办法是从第 1 页开始逐页翻、逐页找但如果你提前在书末尾附了一个“词-页码”对照表直接查对照表就能知道“黄昏”出现在哪些页再翻到对应页去确认就行。这就是倒排索引。FTS5 就是帮你维护这张对照表的模块。它把每条待检索文本按分词器切成一个个词条然后建立“词条 → 文档编号 词在文档中的位置”的映射。当你执行MATCH 黄昏时FTS5 直接去索引里查“黄昏”这个词拿到文档编号列表再回表取内容返回整个过程不涉及全表扫描速度自然上去了。位置信息也很重要因为它还支持短语查询。比如你搜黄昏 降临带引号FTS5 会先找同时包含“黄昏”和“降临”的文档再检查这两个词的位置是否相邻且顺序正确这样就能精确匹配连续短语这是LIKE %黄昏降临%做不到的语义层级。2.2 分词器Tokenizer决定匹配质量分词器是 FTS5 最关键的“翻译官”它的任务是把一段文本切成一个个可以索引的词条。SQLite 官方默认提供了两类核心分词器。第一类是unicode61它按 Unicode 字符类别来切分空格、标点、符号都是天然的分隔符英文和数字能很好地处理还支持大小写折叠。比如Hello, World!会被切成hello和world两个词条搜索HELLO也能命中。但对没有空格分隔的连续中文字符串它会整段当成一个词比如“黄昏降临街头”会被切成单个 token “黄昏降临街头”这意味着你搜“黄昏”反而匹配不到。第二类是trigram它把文本按每 3 个连续字符切分成重叠的 token。比如“黄昏降临街头”会被切成“黄昏降”、“昏降临”、“降临街”、“临街头”这些 3 字片段。搜索“黄昏”时查询词也会被切成“黄昏”这个不足 3 字的片段通过特殊处理也能实现任意子串匹配。由于中文字符串通常没有空格trigram是目前 SQLite FTS5 处理中文搜索最实用的方案。除了官方分词器FTS5 还允许加载外部分词器比如通过fts5_unicode61的 tokenchars 参数把某些特殊字符纳入 token 范围。不过在我实际项目中英文场景用unicode61中文混合场景直接用trigram基本能覆盖 95% 的需求没必要一开始就上自定义分词。提示trigram有两个明显限制一是至少 3 个字符才能有效查询1~2 个字符的搜索词可能匹配不到二是索引体积比unicode61大不少因为生成的 token 数量更多。后面第 5 章我会专门讲取舍。2.3 BM25 排序好结果排在前面很多人在用 FTS5 时只关注“能不能搜到”忽略了“搜出来的顺序是否合理”。FTS5 默认有一套名为 BM25 的相关性打分算法它的核心思想是一个词在某个文档中出现频率越高、这个文档越相关但这个“高频词”如果在整个语料里几乎每个文档都有那它对区分度就没有贡献打分会被压低。BM25 的计算公式里有两个关键参数FTS5 默认是k11.2和b0.75这个组合是经过大量语料验证的通用默认值。实际使用时你不需要手动算分ORDER BY rank就能按相关性从高到低排序每次匹配会在结果中自动生成一个rank列。如果默认排序不满足需求还可以在查询语句里覆盖参数ORDER BY bm25(fts_table, 3, 1)这里面的数字是每一列的权重。我自己的经验是默认 BM25 排序在句子检索场景下已经足够好用。搜索“SQLite 数据库”时包含完整短语、同时出现两个词、出现次数更多的句子会排前面这比LIKE查询只能按行号返回的结果体验好太多。2.4 核心对比FTS5 比 LIKE 快多少我针对同一批测试数据做了一次专门的对比实验一张普通表存 20 万条句子一张 FTS5 虚拟表存同样内容分别执行关键词搜索。普通表用的是WHERE content LIKE %sqlite%FTS5 用的是WHERE content MATCH sqlite。测试环境就是普通开发机数据放在本地 SSD 上。查询方式数据量返回结果条件平均耗时LIKE 全表扫描20 万条包含 sqlite 子串1900~2500 msFTS5 倒排索引20 万条包含 sqlite token20~50 msFTS5 trigram20 万条包含 sqlite 子串80~150 ms这个差距已经不是一个量级了而是几十倍的差距。最夸张的是当搜索词是多个关键词组合时LIKE需要同时匹配多个子串条件耗时叠加而 FTS5 只需要分别查倒排索引再求交集几乎不增加额外扫描成本。如果你的项目现在正被LIKE慢查询困扰换 FTS5 是成本最低、收益最明显的优化路径。3. 动手实现建表、插入、同步数据3.1 建一张带 FTS5 索引的虚拟表FTS5 在 SQLite 里以虚拟表的形式存在建表语法很简单。最基础的做法是单独建一张 FTS5 表然后把原始数据复制一份进去-- 启用 FTS5 扩展部分编译版本默认开启无需执行 CREATE VIRTUAL TABLE sentence_fts USING fts5(content, tokenize unicode61);这条语句创建了一张虚拟表表里只有一列content存入的文本会被自动建索引。如果有多列需要分别检索可以直接列出多个列名例如CREATE VIRTUAL TABLE document_fts USING fts5(title, body, tokenize unicode61);需要注意的是FTS5 虚拟表有隐藏列rowid、rank。rowid默认自增是每条记录的唯一标识rank是每次查询时动态生成的排序分数。如果你想把 FTS5 表和业务原表关联起来一般会在原表里保留业务主键同时在 FTS5 表里存一份这个主键值或者把原表主键直接作为虚拟表的rowid。如果想验证当前 SQLite 版本是否支持 FTS5直接执行下面这条语句能返回结果就说明支持SELECT * FROM pragma_compile_options WHERE compile_options LIKE ENABLE_FTS5;3.2 数据插入与增量同步虚拟表的插入和普通表一样直接使用INSERTINSERT INTO sentence_fts(rowid, content) VALUES (1, SQLite FTS5 是一个全文搜索引擎);我推荐每次插入时显式指定rowid这样可以用原表主键作为关联键后续做同步、去重、定位原始记录都方便。假如原表主键是自增的插入时可以一一对应。如果是一次性导入大量历史数据建议包在事务里提交不要一条一条自动提交否则写入速度会差很多。我测试过10 万条记录按单条提交大约要 30 秒放进同一个事务后只需要 1~2 秒。import sqlite3 conn sqlite3.connect(example.db) conn.execute(CREATE VIRTUAL TABLE IF NOT EXISTS sentence_fts USING fts5(content)) with conn: for i in range(1, 100001): content f这是第{i}条句子用于测试全文检索 conn.execute( INSERT INTO sentence_fts(rowid, content) VALUES (?, ?), (i, content) )这里我用了 Python 的with conn它会自动管理事务结束时统一提交性能和稳定性都有保障。3.3 用触发器维护索引的三种方案FTS5 虚拟表本身是独立存储的它不会感知原表的变化。如果你在原表里新增、删除、修改了数据索引表不会自动更新。常见的维护方案有三种。方案一应用层双写。在原表写入成功后再写一次 FTS5 表。优点是逻辑显式可控缺点是有可能出现两个操作不一致的情况需要额外处理失败重试。方案二数据库触发器。在业务原表上创建AFTER INSERT、AFTER DELETE、AFTER UPDATE触发器自动同步变化到 FTS5 表这是最推荐的方式因为数据库层面保证一致性应用层无感。-- 假设原表叫 sentence主键是 id文本列是 content CREATE TRIGGER trg_sentence_insert AFTER INSERT ON sentence BEGIN INSERT INTO sentence_fts(rowid, content) VALUES (new.id, new.content); END; CREATE TRIGGER trg_sentence_delete AFTER DELETE ON sentence BEGIN INSERT INTO sentence_fts(sentence_fts, rowid, content) VALUES(delete, old.id, old.content); END; CREATE TRIGGER trg_sentence_update AFTER UPDATE ON sentence BEGIN INSERT INTO sentence_fts(sentence_fts, rowid, content) VALUES(delete, old.id, old.content); INSERT INTO sentence_fts(rowid, content) VALUES (new.id, new.content); END;方案三定期重建。如果数据量不大、同步要求不严可以直接DELETE FROM sentence_fts后重新从原表全量导入。适合导入脚本、夜间批处理等场景。3.4 外部内容表external content与无内容表contentless优化当你有原表需要同步时可以考虑用 FTS5 的外部内容表功能——索引不存正文副本只在索引时从原表按 rowid 读取内容。创建虚拟表时添加content 原表名CREATE VIRTUAL TABLE sentence_fts USING fts5(content, content sentence, content_rowid id);这样 FTS5 表里不实际保存文本必须配合触发器维护索引。优点是省存储空间对内存占用和索引体积都有好处缺点是索引表一旦损坏恢复困难而且部分操作语法限制更多。如果连原表都想省掉还有contentless模式CREATE VIRTUAL TABLE sentence_fts USING fts5(content, contentless);这种模式只存索引不存文本适合只需要“知道哪些文档匹配”的场景。但代价是无法从 FTS5 表回查原始内容也不能用DELETE直接删除任意行。我个人建议大部分场景先用普通 FTS5 表把逻辑做通、跑顺了再根据瓶颈决定要不要走content或contentless方案。4. 高效查询从单关键词到复杂检索4.1 基础 MATCH 查询与参数化写法FTS5 的查询不是用而是使用专门的MATCH运算符。最基本的用法是SELECT rowid, content, rank FROM sentence_fts WHERE sentence_fts MATCH sqlite ORDER BY rank;这里有几个关键点。第一MATCH后面的字符串会被 FTS5 查询语法解析不能直接用参数占位符绑定但可以先用参数传进来再拼接成查询串比如在 Python 里keyword hello sql SELECT rowid, content FROM sentence_fts WHERE sentence_fts MATCH ? for row in conn.execute(sql, (f{keyword},)): pass第二当虚拟表名为sentence_fts时写法是sentence_fts MATCH ?也就是把表名放在MATCH左边。如果表名有特殊字符需要用双引号包裹。第三加了ORDER BY rank才能按相关性排序默认的返回顺序虽然也是可用的但通常不如 BM25 排序直观。4.2 组合查询AND、OR、NOT、NEARFTS5 查询语法支持布尔操作符可以直接写出类似搜索引擎的关键词组合。比如要查同时包含“sqlite”和“database”的句子SELECT rowid, content FROM sentence_fts WHERE sentence_fts MATCH sqlite AND database ORDER BY rank;要查包含“sqlite”但不包含“database”SELECT rowid, content FROM sentence_fts WHERE sentence_fts MATCH sqlite NOT database;多个关键词之间空格隔开默认是 OR 关系。比如MATCH sqlite database等价于sqlite OR database。如果你习惯看到空格就当“并且”这里需要格外注意。还有个很有用的操作符是 NEAR它要求两个词在文档中距离比较近默认 10 个词以内。比如MATCH sqlite NEAR database会优先返回两个关键词出现在相邻区域的句子这种语义特别适合搜索“一句话里同时提到两个概念”的场景。4.3 前缀、短语与通配查询FTS5 对前缀查询的支持非常友好你可以在词条后面加星号-- 匹配 sqlite、sqlites、sqlite3 等以 sqlite 开头的词 SELECT rowid, content FROM sentence_fts WHERE sentence_fts MATCH sqlite*;短语查询则是把连续多个词用双引号包裹要求这些词在原文本里顺序相邻。例如-- 匹配 full text search 这个连续短语 SELECT rowid, content FROM sentence_fts WHERE sentence_fts MATCH full text search;注意短语查询在unicode61分词器下才能正常工作因为分词器能正确分出单词边界但用trigram分词器时短语查询行为略有差异建议实际测试后再决定是否使用。星号只能放在词条尾部不支持头部通配或中间通配这是 FTS5 的一个固有约束。4.4 排序优化BM25 与自定义排序FTS5 查询会自动生成rank列ORDER BY rank按 BM25 分数升序注意这里 rank 越小相关性越高和直觉相反。如果你想给不同列分配不同权重可以这样写SELECT rowid, content FROM sentence_fts WHERE sentence_fts MATCH sqlite ORDER BY bm25(sentence_fts, 3.0, 1.0);其中第一个参数是虚拟表名之后的数字依次是各列的权重。比如一个标题列存了title正文列存了body可以调高标题的权重让标题命中的结果排在前面。如果你的排序规则和文本相关性无关比如还要求按创建时间倒序、按点击量排序那直接在ORDER BY后面加额外字段即可FTS5 会先根据MATCH过滤出子集再按照你指定的字段排序。这里要注意性能如果结果集非常大非 rank 排序会消耗额外内存建议配合 LIMIT 使用。5. 让全文检索在真实项目里落地的关键细节5.1 中文分词的坑与 trigram 方案这是我在实际使用中踩过的最大一个坑。刚开始我用默认的unicode61分词器建了一张表导入中文句子发现搜索中文几乎匹配不到。原因是unicode61对中文没有按词切分的能力它把一长串没有空格的汉字当成一个 token搜索词和索引 token 完全对不上。解决方式有两种。一种是建表时指定tokenize trigramCREATE VIRTUAL TABLE sentence_fts USING fts5(content, tokenize trigram);trigram会把连续汉字切分成三字符片段这样搜索任意子串都能命中。我测试过“黄昏降临街头”不管搜“黄昏”“降临”“街头”还是搜“昏降”“临街”都能找到对应句子。另一种方式是引入外部中文分词器比如通过一系列自定义函数先把中文句子按词切分好再用空格拼接成新文本存入 FTS5。这种方式检索精度更高能识别“数据库”是一个词而不是“数据”和“库”的拼接但实现成本也更高。对于几十万条数据量级的句子检索trigram的粗粒度匹配已经足够好用如果你对查全率有极致要求外部分词是更优解。5.2 乱码问题连接参数与编码检查热词里出现“delphi sqlite 乱码”不是没有原因的。SQLite 本身存储的是 UTF-8 编码但很多开发语言在连接 SQLite 时默认的客户端编码不一定是 UTF-8尤其是老牌 Windows 桌面开发环境。最常见的情况是把中文写入 SQLite 后用官方命令行工具看是乱码或者用第三方工具打开能显示但自己程序读出来是乱码。排查路径分三步走。第一确认写入前字符串在程序内部是 Unicode 格式而不是本地代码页编码比如 Delphi 的AnsiString里存的中文在旧版本可能默认是 GBK/GB2312。第二检查数据库连接串是否指定了PRAGMA encoding UTF-8对于新建库最好在建库后立刻设置。第三用独立的查看工具验证数据内容比如大家常用的 DB Browser for SQLite它自带的连接配置和多编码支持能帮你快速判断到底是程序层问题还是数据库层问题。对 FTS5 来说编码问题还会带来一个隐蔽后果即使你用MATCH查询某中文关键词时报错或查不到但普通SELECT查询看起来一切正常。我在一次项目里就遇到过后来发现是每条文本写入前被转成了 UTF-8 后又追加了一层 ANSI 转换导致实际存储的是乱码FTS5 索引内容自然也乱。所以解决 FTS5 中文匹配问题前先确认最基础的一步用SELECT hex(content) FROM sentence_fts WHERE rowid 1打印原始字节检查是否能还原成你预期的中文。5.3 特殊字符转义与安全过滤FTS5 的MATCH查询语法里有一些字符是有特殊含义的比如AND、OR、NOT、*、、(、)、:、^。如果你的搜索关键词本身包含这些字符直接拼到MATCH里会导致语法错误或语义改变。我建议写一个转义函数把关键词里的特殊字符用双引号包裹。FTS5 提供的标准做法是如果词条里有特殊字符就把它整体放进双引号里双引号内部的双引号再写一次。例如用户输入C C你需要把它转成C C。Python 示例def fts_escape(term): term term.replace(, ) return f{term}在拼接复杂查询时尽可能只把你自己的关键词用转义函数包裹操作符由程序内部固定拼接这样既能满足用户输入特殊字符的需求又能避免注入类的查询语法干扰。5.4 更新与删除的坑FTS5 虚拟表对UPDATE的支持比较特殊。你可以直接执行UPDATE sentence_fts SET content 新内容 WHERE rowid 1这在大多数情况能工作但实际底层是先把旧文档删除再插入新文档并不像普通表的就地更新。如果触发器维护方案里已经做了“delete insert”就不要在应用层再执行UPDATE否则会出现重复或脏数据。删除操作也有限制。在标准 FTS5 表中DELETE FROM sentence_fts WHERE rowid ?是正确的因为 rowid 是虚拟表的内部主键。但如果你用了contentless模式就不支持按任意条件直接删除只能用专门语法INSERT INTO sentence_fts(sentence_fts, rowid, content) VALUES(delete, old.rowid, old.content)。这两种差异很容易踩坑我在迁移旧数据时就曾因为误用DELETE导致 FTS5 报了 “unsupported operation” 错误。6. 常见问题与排查实录6.1 查询结果为空先查这几个地方我自己调试 FTS5 时最常见的问题就是“明明库里有数据为什么MATCH搜不到”。按这个顺序排查基本能解决 90% 的疑难杂症症状可能原因排查方法MATCH 语句执行报语法错误搜索词包含特殊字符用转义函数包裹搜索词结果集为空但没有报错分词器不匹配比如英文搜索中文内容或 unicode61 搜中文子串用SELECT rowid, content FROM sentence_fts WHERE sentence_fts MATCH test测试英文词条中文搜索不命中建表时没有用 trigram重建表指定tokenize trigram能查到旧数据新插入的数据查不到触发器没建或触发器绑定错了表检查原表上是否有 AFTER INSERT 触发器排序不符合预期未使用 rank 排序显式ORDER BY rank6.2 性能下降的常见原因FTS5 虽然查询快但索引体积和写入速度是有代价的。如果你发现随着数据增长查询越来越慢先检查索引大小和碎片情况。SQLite 没有内置的 FTS5 优化命令但提供了optimize指令专门用来合并索引段INSERT INTO sentence_fts(sentence_fts) VALUES(optimize);这条命令会把多个索引段合并成一个大段像磁盘碎片整理一样能明显改善查询性能。我一般在每次批量导入数据之后都会执行一次。另外如果表里有很多更新删除操作索引段会变得很碎建议定期执行。写入变慢则是另外一个维度。每次插入都会更新倒排索引词条越多写入成本越高。如果业务是“高频写入 低频查询”可以考虑写缓冲先把大量文档攒到普通表每天定时批量灌入 FTS5 表并执行optimize这种异步方案能大幅降低对主流程的影响。6.3 快速验证的 SQL一条语句看穿分词器行为调试时我经常用一条 SQL 来理解 FTS5 到底是怎么分词的SELECT * FROM sentence_fts WHERE sentence_fts MATCH 黄昏;如果能返回结果看rank和snippet函数输出。snippet是个很实用的辅助函数它能从匹配到的文档里截取关键词前后的一小段上下文方便你确认命中的位置。用法SELECT snippet(sentence_fts, 0, [, ], ..., 10) FROM sentence_fts WHERE sentence_fts MATCH 黄昏;如果连分词器的具体切分行为都想观察可以用 FTS5 提供的fts5_tokenize表值函数部分编译版本支持直接查看分词结果SELECT token, start, end FROM fts5_tokenize(trigram, 黄昏降临街头);这条语句会返回这个分词器把输入串切成了哪些词条以及对应的位置偏移能帮你判断某个查询词到底能不能被分词器识别。如果分词器切不出来后面所有优化都是白搭。7. 我在实际项目中的最终体会经过这次改造我把一个原本被LIKE慢查询拖累的小工具彻底从“能忍”改成了“流畅”。最重要的改变不是把 SQL 换成MATCH而是理解了 FTS5 的设计哲学把昂贵的全文匹配操作提前处理成索引把搜索从“反复扫描原文”变成“直接查词表”这和所有现代搜索引擎的思路一脉相承。如果你只是在做一个几千条数据的临时工具用不用 FTS5 其实无所谓LIKE完全够用。但一旦数据量到了几万条以上或者你的搜索场景变成了“多关键词组合”“短语匹配”“结果按相关性排序”FTS5 的性价比就会非常明显地凸显出来。SQLite 在所有主流语言的驱动库里基本都内置了这个模块你不需要额外安装任何东西只需要改变心智模型和几条 SQL 语句。最后再分享一个小技巧如果你在 Windows 或者移动端调试 FTS5 效果建议装一个 DB Browser for SQLite 这类可视化工具可以在图形界面里直接执行MATCH查询、查看倒排索引的加载情况。很多问题我在程序里调试半天找不到原因换到图形工具里一条EXPLAIN QUERY PLAN就能看清逻辑。工具选对了排查问题的效率能翻倍。希望这篇内容能帮你少走一些弯路尤其是在中文分词和触发器维护索引这两个环节——这是 FTS5 项目里最容易出错、也最影响成败的地方。