频道后台导出 12 万条评论后，我把 SQLite 换成了 FTS5

上个月想给自己频道做一次评论检索：2021 年至今大约 12 万条评论，中英混排，想按关键词翻出某期视频下的讨论。我最初的做法很朴素——YouTube Studio 后台按视频导出 CSV，一个视频一个文件，最后拼成一张表塞进 SQLite，用 LIKE '%关键词%' 查。

结果第一版就卡住了。12 万行不算大，但 LIKE 全表扫一次要 1.5 秒左右，而且中文没有分词，搜「剪辑」会把「剪輯」「剪辑软件」「混剪」全带出来，排序也没法按相关度。

后来改成 FTS5 虚拟表，用 trigram 分词器，建表和导入大概是这样：

CREATE VIRTUAL TABLE comments_fts USING fts5(
  video_id, author, text,
  tokenize = 'trigram'
);

INSERT INTO comments_fts (video_id, author, text)
SELECT video_id, author, text FROM comments_raw;

查询用 MATCH，按 bm25 排序：

SELECT video_id, author, snippet(comments_fts, 2, '[', ']', '…', 12)
FROM comments_fts
WHERE comments_fts MATCH '剪辑'
ORDER BY bm25(comments_fts)
LIMIT 50;

同样的查询从 1.5 秒降到 40 毫秒上下，中文子串也能命中。代价是索引文件从 30MB 涨到 180MB 左右，导入时间翻了几倍，而且 trigram 对两字以下的查询基本帮不上忙——搜单个汉字还是退化成扫描。

我踩的坑主要在导入阶段。CSV 里有的评论带换行和引号，直接 read_csv 会把一条拆成多行，我最后是用 Python 的 csv 模块逐行读、按 video_id + comment_id 去重再入库。另一个坑是时间字段：Studio 导出的是本地时区字符串，我没统一成 UTC，导致按时间段筛选时对不上。

边界也说清楚：这套只适合个人频道量级。评论到百万级、或者要频繁增量更新，FTS5 每次重建索引会明显变慢，可能得换成外部内容表加触发器，或者干脆上专门的搜索服务。另外它只解决「我本地怎么查」，不解决平台侧的数据保留问题——删掉的评论导不出来，这是另一回事。

你们导出评论时是怎么处理重复和时区的？我目前靠 comment_id 去重，但跨视频转发的那类评论好像还是会重复入库。