SQLite FTS5 做全文搜尋就夠了

我自己跑了一個叫 Dev Signal 的 RSS 聚合器，整合了 17 個以上的開發者領導力相關來源——電子報、部落格、Podcast 筆記之類的。技術棧是 Next.js 14 搭 SQLite，日常自用加上小團隊分享。

文章累積到 5 萬筆之後，LIKE '%keyword%' 的搜尋開始明顯慢了。更大的問題是根本沒有相關性排序，搜出來的結果順序很隨機。所以我終於花時間來認真加全文搜尋。

第一個念頭是「直接用 Algolia 不就好了」，或者架一個 Elasticsearch。但對一個說穿了是個人工具的東西來說，這兩個選項都太重了。本來就在用 SQLite（透過 better-sqlite3），後來發現 SQLite 內建 FTS5，一個全文搜尋擴充模組，其實滿夠用的。

FTS5 能給你什麼

FTS5 是 SQLite 的虛擬表模組。你建一張 shadow table 來索引你的文字欄位，就能用 MATCH 運算子搜尋，而且內建 BM25 相關性排名。沒有外部 process、沒有網路 round-trip、沒有任何基礎設施要維護。

對我的使用場景來說：

5 萬筆資料查詢在 10ms 以內
BM25 排名讓相關結果真的能浮到前面
零新依賴（本來就有 better-sqlite3）

怎麼設定

先建 FTS5 虛擬表。content=articles 這個參數告訴 FTS5 以 articles 作為 backing table，它只存索引不存原始文字，所以不會讓儲存空間翻倍。

CREATE VIRTUAL TABLE IF NOT EXISTS articles_fts
USING fts5(
  title,
  content,
  title_zh,
  content_zh,
  content=articles,
  content_rowid=id
);

然後從現有資料建立初始索引：

INSERT INTO articles_fts(rowid, title, content, title_zh, content_zh)
SELECT id, title, content, title_zh, content_zh FROM articles;

用 Trigger 保持同步

麻煩的地方在於：articles 表有異動時，FTS 索引不會自動更新。需要自己寫 trigger。

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
  INSERT INTO articles_fts(rowid, title, content, title_zh, content_zh)
  VALUES (new.id, new.title, new.content, new.title_zh, new.content_zh);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
  DELETE FROM articles_fts WHERE rowid = old.id;
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
  UPDATE articles_fts
  SET title = new.title,
      content = new.content,
      title_zh = new.title_zh,
      content_zh = new.content_zh
  WHERE rowid = old.id;
END;

有點囉嗦，但這是 FTS5 content table 的標準做法。寫一次就好。

用 BM25 排名查詢

Node.js 搭 better-sqlite3 的查詢長這樣：

const search = db.prepare(`
  SELECT
    a.id,
    a.title,
    a.published_at,
    a.source,
    bm25(articles_fts) AS rank
  FROM articles_fts
  JOIN articles a ON a.id = articles_fts.rowid
  WHERE articles_fts MATCH ?
  ORDER BY rank
  LIMIT 20
`);

const results = search.all(query);

SQLite FTS5 的 BM25 分數是負數（越負越相關），所以 ORDER BY rank 不加 DESC 才能把最相關的排在前面。第一次用的時候踩到這個坑。

哪裡不夠用

幾個實際碰到的限制：

沒有模糊比對。 搜 “indexing” 不會自動對到 “indexes”，除非你用 prefix 運算子（index*）。Elasticsearch 的 stemming 處理好很多。

中文斷詞很痛苦。 FTS5 預設 tokenizer 是以空白分詞，碰到中文直接壞掉。我的解法是在寫入資料庫前先用 jieba 風格的斷詞器預處理中文內容，把詞與詞之間插入空白，讓 tokenizer 有東西可以切。這是個 workaround，但以我的流量規模夠用了。

不支援分散式搜尋。 不過我跑在一台 VPS 上、一個 SQLite 檔案，這根本不在考慮範圍內。

WAL Mode 很重要

順便提一件事：如果有並發讀取的需求（Next.js API route 會平行打 DB），記得開 WAL 模式：

PRAGMA journal_mode=WAL;

沒開的話，一個寫入會鎖住整個 database，讀者就卡住了。開了 WAL 之後讀寫不會互相 block，在同時觸發多個查詢的頁面載入上差異很明顯。

整個 FTS5 設定大概花了一個下午。對這種規模的個人專案來說，真的夠用了——沒有新的基礎設施，沒有 API key，就是 SQL 而已。