MySQL で日本語の文章を部分一致で探すとき、まず思いつくのは LIKE ‘%語%’ です。手軽な書き方ですが、前に % が付くとインデックスが使えず、行が増えるほど遅くなります。そこで使うのが全文検索の FULLTEXT インデックスと、日本語を2文字ずつに区切る ngram パーサーです。この記事では、FAQ のテーブルを例に、インデックスの作り方と MATCH … AGAINST の2つの検索モードを比べます。「京都」で「東京都」まで出てしまうような、ngram ならではの落とし穴も、実行結果といっしょに載せました。
以下の結果は MySQL 8.0 で実際に実行したものです。
このシナリオで考える：通販サイトの FAQ を検索する
通販サイトのよくある質問（FAQ）を、タイトルと本文から探す場面を考えます。テーブルを作るときに、title と body の2列をまとめた FULLTEXT インデックスを、WITH PARSER ngram を付けて作っておきます。
CREATE TABLE faqs (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
body TEXT NOT NULL,
FULLTEXT INDEX ft_title_body (title, body) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO faqs (title, body) VALUES
('パスワードを忘れたとき', 'ログイン画面の「パスワードを忘れた方」から再設定できます。'),
('ログインできないとき', 'パスワードを5回まちがえると30分ロックされます。'),
('東京都内の配送日数', '東京都内は注文の翌日に届きます。'),
('京都府への配送', '京都府は注文から2日で届きます。'),
('領収書の発行', 'マイページの注文履歴から領収書をPDFで発行できます。'),
('支払い方法の変更', 'クレジットカードからコンビニ払いへの変更は、発送前なら受け付けます。'),
('退会の手続き', '退会するとポイントと注文履歴は消えます。'),
('配送先の変更', '発送前であれば、マイページから配送先を変更できます。'),
('メールアドレスの変更', 'Gmailなどほかのメールアドレスに変えられます。');ngram パーサーは、文章を決まった文字数ごとに区切って索引に入れます。区切る文字数は ngram_token_size という設定で決まり、初期値は2です。
SHOW VARIABLES LIKE 'ngram_token_size';+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| ngram_token_size | 2 |
+------------------+-------+2のままなら、「パスワード」は「パス」「スワ」「ワー」「ード」の4つに分かれて索引に入ります。日本語は単語の間に空白がないので、空白で区切るふつうの全文検索では探せません。決まった長さで機械的に切る ngram なら、単語の辞書がなくても日本語を扱えます。
LIKE ‘%語%’ が遅い理由
前に % が付いた LIKE では、ふつうの B-tree インデックスが使えず、全件を読むことになります。EXPLAIN で見ると、type が ALL（全件読み）です。
SELECT id, title FROM faqs WHERE body LIKE '%パスワード%';
EXPLAIN SELECT id, title FROM faqs WHERE body LIKE '%パスワード%';+----+------------------------+
| id | title |
+----+------------------------+
| 1 | パスワードを忘れたとき |
| 2 | ログインできないとき |
+----+------------------------+
（EXPLAIN の主な列）
+------+---------------+------+------+-------------+
| type | possible_keys | key | rows | Extra |
+------+---------------+------+------+-------------+
| ALL | NULL | NULL | 9 | Using where |
+------+---------------+------+------+-------------+9行なら一瞬で終わります。ところが数十万行の問い合わせ履歴や商品説明で同じことをすると、検索のたびにすべての本文を読みます。インデックスが使えない書き方は「インデックスが効かない7つのパターンと直し方（MySQL）」でもまとめています。
MATCH … AGAINST の基本と、自然言語モードの思わぬヒット
全文検索は WHERE MATCH(列) AGAINST(‘検索語’) と書きます。MATCH に書く列は、FULLTEXT インデックスを作った列の組み合わせと同じにしてください。ずれるとエラーになります（落とし穴の節で実際に見ます）。
モードを書かないと、自然言語モード（NATURAL LANGUAGE MODE）で検索されます。SELECT にも MATCH を書くと、どれだけ合っているかを表すスコアが取れます。
SELECT id, title,
ROUND(MATCH(title, body) AGAINST('パスワード'), 4) AS score
FROM faqs
WHERE MATCH(title, body) AGAINST('パスワード')
ORDER BY score DESC;+----+------------------------+--------+
| id | title | score |
+----+------------------------+--------+
| 1 | パスワードを忘れたとき | 3.0154 |
| 2 | ログインできないとき | 1.5077 |
| 6 | 支払い方法の変更 | 0.2276 |
+----+------------------------+--------+id 6 の「支払い方法の変更」に、パスワードという言葉はありません。それでも出てきたのは、本文の「クレジットカード」に「ード」が含まれるからです。自然言語モードでは、検索語を2文字ずつに分けたうえで、どれか1つでも含む行を返します。スコアの順に並べれば下に来るものの、件数を数えたり絞りこみに使ったりするには向きません。
BOOLEAN MODE で「その並びの文字」を探す
日本語の検索では、IN BOOLEAN MODE を付けるのが基本になります。BOOLEAN MODE では、「パスワード」が「パス スワ ワー ード」の順に並んだフレーズとして探されます。結果は LIKE と同じ2行でした。
SELECT id, title
FROM faqs
WHERE MATCH(title, body) AGAINST('パスワード' IN BOOLEAN MODE);+----+------------------------+
| id | title |
+----+------------------------+
| 1 | パスワードを忘れたとき |
| 2 | ログインできないとき |
+----+------------------------+同じ SELECT に EXPLAIN を付けると、type が fulltext になり、ft_title_body インデックスが使われています。
（EXPLAIN の主な列）
+----------+---------------+---------------+------+-----------------------------------+
| type | possible_keys | key | rows | Extra |
+----------+---------------+---------------+------+-----------------------------------+
| fulltext | ft_title_body | ft_title_body | 1 | Using where; Ft_hints: no_ranking |
+----------+---------------+---------------+------+-----------------------------------+BOOLEAN MODE では、語の前に記号を付けて条件を組み合わせられます。よく使うのは次の4つです。
| 書き方 | 意味 | 例 |
|---|---|---|
| +語 | 必ず含む | +配送 +変更 |
| -語 | 含まない | +発送前 -マイページ |
| 記号なし | 含まなくてもよい（含む行はスコアが上がる） | 配送 変更 |
| 語* | その文字で始まる | 都* |
SELECT id, title
FROM faqs
WHERE MATCH(title, body) AGAINST('+配送 +変更' IN BOOLEAN MODE);
SELECT id, title
FROM faqs
WHERE MATCH(title, body) AGAINST('+発送前 -マイページ' IN BOOLEAN MODE);+----+--------------+
| id | title |
+----+--------------+
| 8 | 配送先の変更 |
+----+--------------+
+----+------------------+
| id | title |
+----+------------------+
| 6 | 支払い方法の変更 |
+----+------------------+「発送前」は id 6 と id 8 の本文にあります。-マイページ を付けたので、マイページという言葉を含む id 8 が外れました。
よくある落とし穴
「京都」で「東京都」も出る
ngram は文字の並びしか見ていないので、単語の切れ目はわかりません。「京都」で探すと、「東京都」を含む行も返ります。
SELECT id, title
FROM faqs
WHERE MATCH(title, body) AGAINST('京都' IN BOOLEAN MODE);+----+--------------------+
| id | title |
+----+--------------------+
| 3 | 東京都内の配送日数 |
| 4 | 京都府への配送 |
+----+--------------------+LIKE ‘%京都%’ でも同じ結果になるので、全文検索だけの問題ではありません。外したい言葉がはっきりしているなら、条件を足します。
SELECT id, title
FROM faqs
WHERE MATCH(title, body) AGAINST('京都' IN BOOLEAN MODE)
AND CONCAT(title, body) NOT LIKE '%東京都%';+----+----------------+
| id | title |
+----+----------------+
| 4 | 京都府への配送 |
+----+----------------+ngram_token_size より短い語は1件も出ない
ngram_token_size が2のとき、1文字の「都」で探すと、自然言語モードでも BOOLEAN MODE でも結果は0行でした。索引には2文字のかたまりしか入っていないためです。
-- どちらも0行
SELECT id, title FROM faqs WHERE MATCH(title, body) AGAINST('都');
SELECT id, title FROM faqs WHERE MATCH(title, body) AGAINST('都' IN BOOLEAN MODE);
SELECT id, title FROM faqs WHERE MATCH(title, body) AGAINST('都*' IN BOOLEAN MODE);+----+--------------------+
| id | title |
+----+--------------------+
| 3 | 東京都内の配送日数 |
| 4 | 京都府への配送 |
+----+--------------------+1文字で探したいときは、BOOLEAN MODE で 都* と書きます。「都」で始まる2文字（「都内」「都府」）を含む行が返りました。ngram_token_size はサーバーの起動時にしか変えられません。変えるなら設定ファイルの [mysqld] に書いて再起動し、FULLTEXT インデックスを作り直すことになります。
MATCH の列がインデックスとずれるとエラーになる
SELECT id, title FROM faqs WHERE MATCH(body) AGAINST('パスワード' IN BOOLEAN MODE);ERROR 1191 (HY000): Can't find FULLTEXT index matching the column listインデックスは (title, body) の2列で作ったので、body だけの MATCH には使えません。body だけで探したいなら、body だけの FULLTEXT インデックスを別に作ります。
コミット前の行は検索に出ない
InnoDB の全文インデックスは、トランザクションをコミットした時点で更新されます。
BEGIN;
INSERT INTO faqs (title, body) VALUES ('ギフト包装', 'ギフト包装は1点110円で承ります。');
SELECT COUNT(*) FROM faqs WHERE MATCH(title, body) AGAINST('ギフト' IN BOOLEAN MODE); -- 0
COMMIT;
SELECT COUNT(*) FROM faqs WHERE MATCH(title, body) AGAINST('ギフト' IN BOOLEAN MODE); -- 1同じトランザクションの中で、入れた直後に検索して確かめるテストを書くと0件になります。テストはコミットしてから検索するように組んでください。
あとから追加するとテーブルが作り直される
FULLTEXT インデックスのないテーブルに ALTER TABLE で追加すると、InnoDB は FTS_DOC_ID という隠れた列を足すためにテーブルを作り直します。行の多いテーブルでは時間がかかるので、アクセスの少ない時間に行えば利用者への影響を小さくできます。ALTER TABLE をサービスを止めずに行う方法は「ALTER TABLEを止めずに行う：INSTANTとINPLACE」で説明しています。
まとめ
日本語の部分一致を速くしたいときは、FULLTEXT インデックスを WITH PARSER ngram で作り、MATCH … AGAINST に IN BOOLEAN MODE を付けて使います。自然言語モードは2文字のかけらが1つ合うだけで行を返すので、日本語では関係のない行が混ざります。ngram は単語を知らないため、「京都」と「東京都」のような重なりは条件を足して外してください。1文字の検索は 語* で補い、MATCH の列はインデックスと同じ組み合わせにそろえます。
もっと学ぶなら【PR】
- MySQL徹底入門 第5版：MySQL の機能を広く解説した本。日付関数などの細かい動きを調べたいときに
- SQLアンチパターン 第2版：テーブル設計やクエリでやりがちな失敗と、その直し方をまとめた本
※Amazonのアソシエイトとして、当サイトは適格販売により収入を得ています。
参考リンク
- MySQL 8.0 Reference Manual: ngram Full-Text Parser
- MySQL 8.0 Reference Manual: Boolean Full-Text Searches
- MySQL 8.0 Reference Manual: InnoDB Full-Text Indexes
アイキャッチ画像: Photo by CHUTTERSNAP on Unsplash
