SQLite全文検索入門：FTS5とMATCHの使い方

LIKE ではスケールしない

SQLite でテキスト検索をしたことがあるなら、まず LIKE '%word%' に手が伸びたはずです。小さなテーブルなら問題なく動きますが、大きくなった途端に破綻します。インデックスが効かないので、SQLite は全行をスキャンして小文字化し、部分文字列を地道にチェックするしかありません。単語の区切り、ランキング、複数キーワード、前方一致——どれも自前で実装する羽目になります。

その答えとして組み込まれているのが FTS5 です。指定したテキスト列に対して転置インデックスを張り、独自のクエリ構文を解釈し、BM25 で結果をランキングしてくれる仮想テーブルの一種です。SQLite に標準で同梱されているので、追加の拡張をインストールする必要はありません。

FTS5 仮想テーブルを作る

FTS5 テーブルは CREATE VIRTUAL TABLE ... USING fts5(...) で作成し、インデックスを張りたいテキスト列を列挙します。

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLiteの全文検索', 'FTS5は高速で組み込み済みです。'),
  ('SQLiteのインデックス', 'B木がSQLiteのほとんどの検索を支えています。'),
  ('なぜSQLiteを使うのか', 'プロセス内で動作し、データを1つのファイルに保存します。');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

注目してほしいポイントが3つあります。まず、カラムに型がありません。FTS5 はすべてをテキストとして扱うからです。次に、MATCH 演算子はカラムではなくテーブル名に対して使います(posts MATCH ... のように書く)。そして、クエリは大文字小文字を区別せず、トークナイズされて処理されるため、'sqlite' で検索すれば各行の SQLite もちゃんとヒットします。

MATCH クエリ言語の文法

MATCH には単語1つだけでなく、もっと複雑な式も渡せます。クエリ文字列には独自の小さな文法があります。

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('全文検索', 'FTS5はフレーズ検索と前方一致検索をサポートします。'),
  ('インデックス', 'B木インデックスは等価検索を高速化します。'),
  ('トリガー', 'トリガーを使ってFTSをソーステーブルと同期させましょう。');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

それぞれの動きはこんな感じです。

• 'fts5 AND prefix' — 両方のトークンが含まれている必要あり（順序や位置は問わない）。
• '"keep fts"' — フレーズ一致。この順序通りに並んでいること。
• 'trig*' — 前方一致検索。trigger、triggers、trigonometry などにマッチします。
• 'index NOT trigger' — index を含み、かつ trigger を含まないもの。

特定のカラムだけを対象にしたい場合は、'title:sqlite' のように column:term の形で書けます。文法としては括弧によるグループ化や OR での候補指定にも対応していて、検索エンジンでよく見るあの書き心地そのままです。

BM25 によるランキング

FTS5 は、デフォルトで各行に rank という隠しカラムを付けてくれます。中身は BM25 の関連度スコアで、値が小さいほどマッチ度が高いという仕様です。これで並べ替えれば、関連度の高い順に結果を取り出せます。

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite チュートリアル', 'SQLite をゼロから学ぶ。'),
  ('SQLite と Postgres の比較', '小規模アプリ向けに SQLite と Postgres を比較する。'),
  ('SQLite SQLite SQLite', 'SQLite に何度も言及している投稿。');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

特定のカラムを他より重く評価したい場合は、bm25() に重みを渡します。テーブル宣言の順番にカラムごとの重みを指定してください:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLiteのインデックス', 'B木に関する短いメモ。'),
  ('Postgresのメモ', '比較のためにsqliteについての段落を含みます。');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

最初の投稿が上位に来るのは、sqlite が body(重み 1×)だけでなく title(重み 10×)にも登場するからです。重みは、自分のアプリで実際にどうランク付けしたいかに合わせて決めましょう。

インデックスを元テーブルと同期させる

一番シンプルな FTS5 テーブルは、検索対象のテキストを自分自身の中に丸ごとコピーして持ちます。INSERT しかしないログ系のデータならこれで十分ですが、たいていのアプリにはすでに本物のテーブルがあって、FTS にはそれを追いかけてほしいはずです。そんなときの定番パターンが、external content の FTS テーブル + 3 つのトリガーという組み合わせです。

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

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

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('SQLiteのトリガー', 'AFTER INSERTトリガーの使い方。'),
  ('FTS5のパターン', '外部コンテンツによりデータを一箇所にまとめられる。');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' を指定すると、FTS5 はテキスト本体を保存せず、必要になったときに articles テーブルから取りに行くようになります。トリガーは書き込みを FTS インデックスにも反映させる役割です。こうすると articles が正本（source of truth）となり、articles_fts はその脇に置かれた検索用の構造でしかなくなります。

ちょっと変わった見た目の INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) は、FTS5 にインデックスからその行を削除させるための専用コマンド構文です。

スニペットとハイライト表示

検索結果には、ヒットした語を強調したプレビューを添えたいことがほとんどです。FTS5 にはそのための関数が2つ用意されています。

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('はじめに', 'SQLiteは、ディスク上の単一ファイルにデータを格納する、小さく高速な組み込みデータベースエンジンです。');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite database';

• highlight(table, column_index, open, close) はマッチしたトークンを指定タグで囲んだ、その列の全文を返します。
• snippet(table, column_index, open, close, ellipsis, token_count) はマッチ箇所を中心とした短い抜粋を返します。

カラムのインデックスは宣言順に0始まりです。検索UIではお馴染みの「ヒットした語を黄色でハイライト」を実装するための基本パーツが、このあたりの関数になります。

ハマりどころまとめ

意外とつまずくポイントをいくつか挙げておきます。

• MATCH 演算子はFTSテーブル専用です。 普通のカラムに対して MATCH は使えません。既存テーブルに対して全文検索を効かせたい場合は、前述の external-content パターンを使ってください。
• rank でのソートを忘れずに。 これを付けないと、FTS5は保存順で返してくるだけで、関連度とは無関係な並びになります。
• トークナイザの選択は重要です。 デフォルトの unicode61 はUnicodeの単語境界で区切って小文字化します。run で running もヒットさせたい(ステミング)場合は porter を指定します: USING fts5(body, tokenize='porter')。
• FTS5はあいまい検索エンジンではありません。 やってくれるのは前方一致であって、ファジー検索ではありません。「もしかして:〇〇」のような挙動が欲しければ、FTS5の上に別途レイヤーを積む必要があります。
• コンテンツレスなテーブル(content='')はサイズが小さい代わりに元テキストが取り出せません。 検索はできますが、取得できるのは rowid だけで、元の本文は復元できません。本文を別の場所に保存している構成では便利です。

次回:ウィンドウ関数

ここまででテキスト検索はひと通りカバーしました。次のページでは毛色の違う高度なクエリ — ウィンドウ関数を扱います。行を集約で潰すことなく、累計・ランキング・グループ単位の集計を計算できる機能です。