全文検索 — LIKE '%...%' が効かなくなる日

What — インデックスは「先頭から」しか効かない

通常のインデックスは、値を順番に並べた索引です。辞書と同じで、先頭が分かれば引けるが、途中の文字では引けません。

索引（名前順に並んでいる）
...
ガーゼケット      ◀ ここから
ガーゼタオル
ガーゼハンカチ    ◀ ここまで
ガラスコップ
...

全行走査

インデックスを使わず、全部の行を順に見ること。行数に比例して遅くなる。

転置索引

「語 → その語を含む文書」の対応表。全文検索の基本的な仕組み。

形態素解析

文章を意味のある語に分けること。辞書を使う。

N-gram

文章を N 文字ずつ機械的に区切ること。辞書が要らない。

適合率と再現率

余計なものが出ないか（適合率）、取りこぼさないか（再現率）。両立しにくい。

日本語は「語の切れ目」が自明でない

英語なら gauze blanket はスペースで分かれています。日本語にはその区切りがありません。

「東京都」をどう分けるか。

形態素解析:  東京 / 都        （辞書に基づく）
2-gram:      東京 / 京都       （機械的に2文字ずつ）

2-gram だと「京都」が出てきます。 だから「京都」で検索したときに「東京都」が当たります。

新しい商品名や固有名詞は、辞書に載っていません。 形態素解析だけだと、「ガーゼケット」が1語として扱われ、「ケット」で検索しても当たらないことがあります。

実務では両方を併用することが多い選択です。

Why — 「動いている」が遅れて壊れる

件数が少ないうちは、間違いが見えない

全行走査は、行数が少なければ速いのです。

800 行     20ms   → 誰も気づかない
5,000 行   120ms  → まだ許容範囲
50,000 行  3.2秒  → 苦情が来る

設計の誤りが、時間差で表面化します。 書いた人はもういないかもしれません。

data/execution-plan で扱っている通り、EXPLAIN を見る習慣があれば、800 件の時点で type: ALL に気づけました。

「遅い」より「出てこない」のほうが重い

冒頭の事故で、遅さは分かりやすい症状でした。しかしもっと深刻だったのは、複数語の検索が機能していなかったことです。

利用者の入力: 「ガーゼ ケット」
実際のクエリ: LIKE '%ガーゼ ケット%'
結果:         0 件

利用者は「その商品は無い」と解釈します。問い合わせすら来ません。 黙って離脱します。

遅いのは測れば分かります。出てこないのは、測っていなければ分かりません。

演習 — まず自分で判断する