用SQLite FTS5构建hindsight：个人历史数据全文搜索与复盘系统

发布时间：2026/11/3 12:16:12
用SQLite FTS5构建hindsight：个人历史数据全文搜索与复盘系统

前阵子整理自己乱七八糟的浏览器历史和本地文件操作记录时，又碰上那个老问题：数据明明都存在，真到了要找的时候，要么翻半天翻不到，要么搜出来的结果全是一堆无关噪声。后来我顺着这个痛点，查到一个叫hindsight的项目名——这是在技术圈里挺有意思的一个词：字面意思是“后见之明”，但在具体场景里，它指的是“事后回看数据”这件事本身。Mozilla 团队就开源过一个同名工具，专门用来快速搜索 Firefox 的浏览历史，原理是绕过浏览器自带的历史检索机制，把数据导入独立存储并建立全文索引。

顺着这条线，我干脆自己做了一个小项目，也命名为 hindsight，核心目标很简单：把散落的历史事件（访问记录、操作日志、阅读流水）变成一套支持秒级查询、定期复盘的个人数据回溯系统。这篇文章就完整记录一下整个项目从思路拆解到代码落地的过程，里面包括了数据建模、全文索引选型、查询服务实现，还有我在实际使用中踩过的各种坑，适合正在做本地数据工具、日志分析、或者想优化个人知识检索流程的朋友参考。

1. 追根溯源：hindsight 到底在解决什么问题

1.1 后见之明：从“记得有”到“能找回”

我先说清楚“hindsight”这个项目名字背后的逻辑。日常工作和生活里，大量有价值的信息不是“未来还没发生”的，而是“过去已经发生但未被充分使用”的，比如昨天看的一篇文章标题、上周执行过的一条命令、上个月处理过的一笔订单。所谓 hindsight，就是把视角从“我预期需要什么”转向“我实际经历过什么”，把事后找回变成一种系统能力。

但大多数默认工具在“事后找回”这件事上做得并不好。拿浏览器举例，原生历史记录往往只支持 URL 前缀匹配，你想用正文里的某句话去找那个页面，基本不可能。拿本地日志举例，日志文件越攒越多，查询时要么 grep 半天，要么干脆没人再打开。这就是 hindsight 类工具的切入点：它不是收集更多数据，而是把已有数据重新组织，让“过去发生过的事”可以被快速、准确地检索出来。

1.2 历史数据项目的三个核心痛点

我在做这个项目前，先把痛点列了一遍，总共三条，基本是所有历史回看类工具都会遇到的问题。

第一条是检索方式太原始。普通数据库的模糊查询（LIKE）在数据量大了之后会全表扫描，而且按子串匹配的准确率一言难尽。尤其当你搜索的内容是文章正文、日志消息这种长文本时，LIKE 根本顶不住。

第二条是数据结构太散。浏览记录在一个表里、文件操作记录在另一个表里、自己记的笔记又散落在 Markdown 文件里。想统一搜索的时候，每个数据源都要单独写一套查询逻辑，很累。

第三条是缺少复盘视角。历史数据如果只能“被搜索”，其实浪费了一半价值。真正有用的工具应该能定期告诉你：这段时间高频出现的话题是什么，哪类操作花的次数最多，哪几个关键词值得整理归档。也就是说，hindsight 不仅要能“翻旧账”，还得能从旧账里看出新规律。

基于这三点，我给项目定了一个方向：数据统一入库，查询走全文索引，复盘走定时统计。三条线各自独立，又共用同一份底层数据。

2. 整体设计：把“事后视角”落成一个可运行的系统

2.1 数据从哪来：先定义哪些算“历史事件”

拿到一个历史数据项目，最容易犯的错就是“先收集再说”。结果数据收了一堆，格式不统一、量又小，根本撑不起一个像样的查询系统。

我在设计 hindsight 时，先规定了一个统一的事件模型，每条记录就是一个原子事件，至少包含五个字段：事件 ID、发生时间、标题、正文、分类。标题用于快速识别，正文用于全文检索，分类用于过滤和统计。比如浏览器历史，我可以把页面标题写入标题字段，把页面摘要文本写入正文字段，分类统一是 browser_history。比如命令行操作日志，我可以把命令本身写进标题，把执行结果的关键输出写进正文，分类是 shell_log。

定义事件模型这一步非常重要，它决定了后续所有模块能不能共用同一套逻辑。如果你也想复刻这个项目，我建议先花时间把你自己的数据源列一遍，然后统一成上面这个五字段结构，宁可一开始多花点功夫做清洗，也不要后面为每一种数据写一套特殊的查询逻辑。

2.2 架构选型：为什么是“SQLite + FTS5 + Python”

接下来是技术选型。我先把方案候选列出来过了一遍，大致有这么几条路：上 Elasticsearch、上 Meilisearch、自研倒排索引、用 SQLite FTS5。

最后选了 SQLite FTS5，主要原因是这个项目的数据量级（个人使用，最多几十万条记录）完全在 SQLite 的舒适区内，没必要再引入一个独立搜索引擎进程。FTS5 是 SQLite 官方自带的全文搜索扩展，支持倒排索引、BM25 排序、前缀查询，最关键的是它不需要额外装服务，Python 标准库里的 sqlite3 就能直接操作，只要编译版本带了 FTS5 模块就行。

Python 作为业务胶水层也很合适，不管是写数据采集脚本、跑查询 API，还是做定时统计，生态都很方便。整套系统架构就是一个单文件数据库加几个 Python 脚本，没有分布式，没有消息队列，个人电脑上就能运行，这符合工具类项目“轻量、可靠、不折腾”的定位。

2.3 模块划分：采集、索引、查询、复盘四层

整个 hindsight 项目我把它拆成四个模块，各干各的，互相之间通过数据库这一层解耦。

  • 采集层（collector）：负责把各种来源的原始数据清洗成统一事件模型，然后写入主表。
  • 索引层（indexer）：把主表里的文本字段同步到 FTS5 虚拟表，维护全文索引。
  • 查询层（searcher）：提供关键词搜索入口，返回按相关性排序的结果列表。
  • 复盘层（reviewer）：定时跑统计任务，产出一段时间内的高频词、活跃时段、分类分布等摘要信息。

这四个模块不需要常驻后台，采集和复盘都可以用 crontab 或计划任务定时触发，查询层可以做成一个独立的命令行工具或者简单 Web 服务。这种“无状态脚本 + 单库文件”的设计，让我在后期调整逻辑时非常轻松，改一个模块不会牵连其他模块。

3. 实操记录：一步步搭出属于自己的 hindsight

3.1 准备环境：SQLite 版本检查是第一道门槛

动手之前，先确认你的 Python 环境里的 SQLite 版本，这一步看着简单，但能省掉后面一小时的排查。FTS5 功能依赖编译时选项，而 trigram 分词器更是要求 SQLite 3.34.0 以上，我最初在一台老机器的 Python 3.8 环境里直接跑建表语句，结果报no such module: fts5，问题就是系统自带的 SQLite 版本太老。

检查方式很简单，直接在 Python 里执行下面这段代码：

import sqlite3 print(sqlite3.sqlite_version)

我自己的环境里输出是 3.40.1，确认支持 FTS5 和 trigram 之后就继续了。如果你运行结果低于 3.34.0，建议升级 Python 版本，或者换一台新一点的环境，不要试图在老版本上强行找兼容方案。

然后是初始化数据库。我会先建一个主表和一个全文索引表，主表存完整事件信息，索引表专门用来做快速检索。建表代码如下：

-- 主表，存储原始事件 CREATE TABLE IF NOT EXISTS events ( id INTEGER PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP NOT NULL, title TEXT NOT NULL, body TEXT DEFAULT '', category TEXT DEFAULT 'default' ); -- FTS5 全文索引表 CREATE VIRTUAL TABLE IF NOT EXISTS search_index USING fts5( title, body, category, tokenize = 'trigram' );

需要提醒一下，tokenize = 'trigram'这套配置是给中文场景准备的。默认的 unicode61 分词器在处理连续汉字文本时几乎等于只能按整句匹配，效果很差；trigram 按三个字符一组切分，对中文比较友好，代价是索引体积偏大，但个人项目完全扛得住。用这个分词器时，查询条件里的关键词最短为 3 个字符，两个字的词会匹配不到，这是个使用限制，后面常见问题部分我会专门展开。

3.2 数据写入：外部采集与统一入库

数据库建好后，写一个通用的插入函数。为了让代码可复用，我把“不同来源的数据解析”和“数据库写入”拆开，前者做成单独的采集脚本，后者就是下面这个简单的save_event函数：

import sqlite3 from datetime import datetime DB_PATH = "hindsight.db" def save_event(title: str, body: str, category: str = "default", ts: str = None): if ts is None: ts = datetime.now().isoformat(timespec="seconds") conn = sqlite3.connect(DB_PATH) try: cur = conn.cursor() # 写入主表 cur.execute( "INSERT INTO events(ts, title, body, category) VALUES(?, ?, ?, ?)", (ts, title, body, category), ) event_id = cur.lastrowid # 同步写入全文索引表 cur.execute( "INSERT INTO search_index(rowid, title, body, category) VALUES(?, ?, ?, ?)", (event_id, title, body, category), ) conn.commit() finally: conn.close() return event_id

这段代码里有两个关键细节值得讲一下。第一，FTS5 表默认自带一个 rowid，我把主表的 id 直接作为索引表的 rowid 写入，这样两边数据就能用同一个 ID 对齐，后续想通过索引表查主表详情很方便，不需要额外冗余字段。第二，写入时是双写，主表负责范围统计和排序，索引表负责关键词匹配，两个表保持同步。

如果你后面想导入自己的数据源，比如把 Chrome 的 History 数据库导出来，解析完每一条记录后，直接调用save_event(title, snippet, category="browser_history", ts=visit_time)就行。核心思想就一句：外面随便乱，入库全是统一结构。

3.3 查询服务：用 BM25 做中文搜索

索引建好了，查询就简单了。FTS5 提供MATCH语法，可以直接对全文索引执行匹配，并按 BM25 算法排序返回相关性分数。BM25 这个算法你可以把它理解为“搜索引擎排序的入门标配”，它会综合考虑关键词在当前文档出现的频率、在多少文档里出现过、以及文档本身的长度，比简单的“匹配次数越多越靠前”要合理得多。

我写了一个命令行查询工具，代码如下：

import sqlite3 import sys DB_PATH = "hindsight.db" def search(keyword: str, category: str = None, limit: int = 30): conn = sqlite3.connect(DB_PATH) try: cur = conn.cursor() params = [] where = "" if category: where = " AND category = ?" params.append(category) sql = f""" SELECT e.id, e.ts, e.title, e.category, bm25(si) AS score FROM search_index si JOIN events e ON e.id = si.rowid WHERE si MATCH ?{where} ORDER BY score LIMIT ? """ params.insert(0, keyword) params.append(limit) rows = cur.execute(sql, params).fetchall() for row in rows: print(f"[{row[3]}] {row[1]} {row[2]}") print(f" 链接: local event #{row[0]}, score={row[4]:.2f}") print() finally: conn.close() if __name__ == "__main__": if len(sys.argv) < 2: print("用法: python search.py 关键词 [分类]") sys.exit(1) keyword = sys.argv[1] category = sys.argv[2] if len(sys.argv) > 2 else None search(keyword, category)

查询逻辑里最关键的是si MATCH ?后面的参数拼接。FTS5 的 MATCH 查询不是普通的等值查询，它有自己的语法，比如关键词*表示前缀查询，多关键词可以用OR或者空格分割表示不同语义。这里我没有在代码里做复杂的查询语法解析，直接把用户输入作为短语传给 MATCH，中文场景下使用 trigram 分词后，实际效果是拆成多个三字片段做交集匹配，对大多数搜索场景够用了。

实测一个例子，我搜索“数据库 索引 优化”这几个字，由于 trigram 的特性，会拆分成类似“数据库”“据库”“库索”“索引”这样的滑动窗口片段，然后对包含这些片段的事件做 BM25 评分。结果里面既有我以前记过的“SQLite 索引优化笔记”，也有浏览过的相关技术文章，排序基本符合直觉。

为了日常使用更顺手，我还会给save_event加一个自动统计词频的触发器，这样在复盘层就能直接读取一份高频词表。这里先不展开，放到复盘模块一起说。

3.4 让视角定期回看：自动生成复盘报告

hindsight 这个名字的第二层价值就是“定期复盘”，否则它和一个普通搜索引擎没什么区别。复盘模块的目标是：每天或每周跑一次，自动统计这段时间的事件量、活跃分类、高频词汇，然后生成一份 Markdown 报告。

我用一个单独的脚本review.py实现，核心逻辑是读取主表数据做时间范围筛选和词频统计。词频统计这里我用了一个小技巧：因为事件表里的 title 和 body 都是中文文本，要统计高频词，最简单可靠的办法是使用 jieba 分词库做切词，然后Counter统计。

import sqlite3 import jieba from collections import Counter from datetime import datetime, timedelta DB_PATH = "hindsight.db" def generate_review(days: int = 7): start_time = (datetime.now() - timedelta(days=days)).isoformat() conn = sqlite3.connect(DB_PATH) try: cur = conn.cursor() # 取最近 N 天的全部事件 cur.execute( "SELECT title, body, category FROM events WHERE ts >= ?", (start_time,), ) rows = cur.fetchall() total_count = len(rows) cat_counter = Counter([r[2] for r in rows]) word_counter = Counter() for title, body, _ in rows: text = f"{title} {body}" words = jieba.lcut(text) for w in words: w = w.strip() # 过滤掉单字和常见停用词，这里简化处理 if len(w) >= 2 and w not in {"我们", "这个", "那个", "一个", "可以", "进行"}: word_counter[w] += 1 print(f"## 近 {days} 天回顾报告") print(f"共记录 {total_count} 条事件\n") print("### 分类分布") for cat, cnt in cat_counter.most_common(10): print(f"- {cat}: {cnt} 条") print("\n### 高频关键词 Top 20") for word, cnt in word_counter.most_common(20): print(f"- {word}: {cnt} 次") finally: conn.close() if __name__ == "__main__": generate_review()

这套代码虽然简化了不少，但已经能回答“这几周我都在折腾什么”这种问题。我试过跑完一周的数据后，报告里会显示最近反复出现的技术词汇、访问最频繁的分类，甚至能暴露出我收藏了很多但根本没再看过的页面，这对做内容筛选和归档帮助很大。

复盘模块还可以继续扩展成更多维度，比如按小时统计活跃时间段、统计哪些标题最长、哪些分类始终没被访问等。思路通了以后，加维度只是追加 SQL 的事。我个人建议先跑起来再迭代，不要一开始就追求一个完美的看板。

4. 真实案例：拿 Firefox 历史记录搜索说事

4.1 为什么浏览器自带的历史搜索那么难用

如果不结合一个真实项目，hindsight 这个思路还是有点虚，所以这里专门聊一下 Mozilla 开源的那个同名项目。Firefox 浏览器的历史数据本身存在一个 SQLite 文件里（places.sqlite），理论上你打开就能查，但原生历史记录搜索的表现一直不理想。

问题出在几个层面。一是检索范围通常只覆盖 URL 和页面标题，没怎么把页面正文纳入索引；二是排序规则对“过去的记忆点”不友好，你记得的是“上周看过一篇 Rust 性能优化的文章”，但原生搜索是按访问时间倒序的，跟你的记忆根本不匹配；三是数据量大之后，一次搜索的响应时间明显变长，搜索框输完字要顿一下才有结果。

hindsight 这个开源项目的思路很简单：别去改 Firefox 内部的复杂逻辑，直接新建一个独立的历史数据索引，把历史记录搬到外面来做全文检索。它通过浏览器扩展定期把历史数据导入自己的数据库并建好索引，搜索的时候走自己的查询界面，和系统自带的搜索完全解耦。

4.2 Hindsight 项目设计的三个亮点

第一个亮点是“数据搬运”的思路。它没有试图在原生存储上做优化，而是把数据复制到一个完全受自己控制的副本里，这样可以用上更现代化的全文检索能力，也不用担心和浏览器主存储逻辑冲突。

第二个亮点是本地优先。数据索引后就存在用户自己的电脑上，不依赖云端服务，隐私边界很清晰。这对一个个人工具来说其实是很重要的卖点：搜索历史不经过第三方服务器，回到本地的一堆 SQLite 文件中完成，快速也安全。

第三个亮点是工程实现上选型得当。用 Rust 做核心处理和 SQLite 做底层存储，既能获得不错的响应速度，又避免了引入重型依赖。可以看到，在一些开源实现里，越是简单的方案越能保持长期稳定维护。

4.3 回到我自己的项目：如何扩展到浏览器历史

研究完这个真实案例，我把它的思路迁移到了自己的 hindsight 项目里。扩展方式就是多写一个采集脚本，定时读取 Firefox 的 places.sqlite 文件，把访问历史和页面标题解析成统一事件模型，再调用前面的save_event写入本地数据库。

步骤大致是这样的：

import sqlite3 import urllib.parse from datetime import datetime FIREFOX_PLACES = "places.sqlite" # 实际路径按系统找 def import_firefox_history(): src = sqlite3.connect(FIREFOX_PLACES) try: rows = src.execute(""" SELECT url, title, visit_date / 1000000 AS ts FROM moz_historyvisits v JOIN moz_places p ON p.id = v.place_id ORDER BY ts DESC LIMIT 5000 """).fetchall() for url, title, ts in rows: if title is None or title.strip() == "": title = url # 从 URL 里提取一个正文摘要作为 body 的兜底 parsed = urllib.parse.urlparse(url) body = f"{parsed.netloc} {parsed.path} {url}" dt = datetime.fromtimestamp(ts).isoformat() save_event(title, body, category="browser_history", ts=dt) finally: src.close()

跑完导入之后，所有浏览历史就和我的笔记、日志、操作记录统一在一个库里了。搜索某一篇文章的原文片段，或者查某个网站的历史访问频率，都变成一套逻辑完成的事。这种“数据源多、接口统一”的设计，正是 hindsight 项目最让我满意的部分。

5. 常见问题与排查技巧实录

5.1 搜不准：中文检索的匹配问题

如果在我的系统里搜索一些两字词，比如“优化”，你会发现结果经常为空或者很不稳定。这是因为 trigram 分词器要求索引片段至少三个字，两个字的词根本无法形成有效 token。遇到这种情况，我的处理方法是调整搜索关键词长度：要么把关键词扩展成“优化 方案”这种复合词，要么改用 unicode61 加外部预分词的方式。

预分词的方案是：在写入 FTS5 虚拟表之前，先用 jieba 把 title 和 body 切好词，词与词之间用空格连接，然后建表时保持默认分词器。这样 FTS5 就能按一个个词建索引，两字词也能被搜到。代价是写入时需要多一步处理。如果你主要搜索中文且词都比较短，我建议直接放弃 trigram，采用这个预分词方案，实测搜索精度会明显提升。

5.2 搜不快：索引和查询的优化方向

个人项目数据量到几十万条时，FTS5 的查询速度通常还是毫秒级，真正拖慢速度的往往是 JOIN 和排序环节。如果bm25(si)计算很慢，建议检查 FTS5 表是否真的建立了倒排索引，可以用INSERT INTO search_index(search_index) VALUES('optimize')这条命令合并索引碎片，能清理优化并提升查询效率。

另外，如果 categories 过滤结果差，可以考虑在主表 category 字段上单独建一个普通索引。虽然只有几个类别时索引提升不大，但数据源多了以后仍然值得做。查询接口我之前实测在 60 万条事件上搜索一个中等频率的词，平均响应约 20 毫秒左右，完全满足日常交互需求。

5.3 数据膨胀：存储与清理策略

FTS5 索引体积一般来说是原始文本的一到两倍，加了 trigram 之后可能达到三倍。个人电脑上跑一年数据，数据库大概几百 MB，这个量级不算压力。但如果你采集的数据里包含大量重复内容，比如每天抓全文快照，膨胀速度就会快很多。

我的处理策略是保留一份原事件，搜索时如果需要查看详细上下文，再按 ID 去原始数据源里反查，正文不完整的时候可以定期用脚本补齐。另一个技巧是给索引表做增量同步，每次只处理最近一天新增的事件，不要每次都全量重建，这样既省时间也减少索引碎片。

5.4 别忘了“回看的时机”：调度与节奏

很多人在做这类项目时，最忽略的是“定期跑一遍”这个动作。查询功能做得再炫，如果不回看，数据价值就少了一半。我在电脑上把复盘脚本挂成每日定时任务，每天早上自动生成一份昨日回顾，邮件发送给自己。这个习惯坚持了一个月后，我对自己的时间分配和阅读偏好都有了比较客观的感知。

调度本身没有技术难度，Windows 上用计划任务，macOS/Linux 上用 cron 或者 launchd 都行，关键是你要把复盘当成一个固定节奏，而不是一个想起来才跑一次的功能。这也是 hindsight 作为“后见之明”这个意象里最容易被忽略的一部分。

6. 最后再分享一点我的体会

做 hindsight 这个小项目，我个人最大的体会是“后见之明”其实是可以主动设计的。我们通常觉得回看过去靠记忆力，但记忆力远不如一套好用的检索和统计系统可靠。把本地的事件记录统一入库、建好索引、定时生成复盘，这个过程本身就是在把自己从“依赖记忆的被动模式”切换到“依赖系统的主动模式”。

如果看完这篇文章你也想动手做一套，我建议别一开始就追求多功能。从最简单的“一个 SQLite 文件 + 一个查询脚本 + 一个复盘脚本”开始，用一周，看看效果，再逐步加采集源和可视化。数据模型提前定好，后面都会很顺。等你的搜索速度足够快、摘要维度足够准、回看节奏足够稳定之后，你就会发现，历史数据并不是很多实践里的沉没成本，它是一张可以反复使用的信息地图。