はじめに
MySQLを勉強していてFULLTEXTインデックスというものがあることを知りました。
どのくらい速くなるのか、また、どんな条件なら差が出づらいのかを確認してみました。
MySQL 8.4 に10万件の日本語記事データを入れ、LIKE検索と FULLTEXTインデックス（ngramパーサ）の性能を実測しました。
結論（先に要点）
- LIKEはヒット件数に関係なく、ほぼ一定の時間がかかる（全行を読み切るため）
- FULLTEXTはヒット件数に比例して遅くなる（ヒットが少ないほど有利）
-
ORDER BYを付けた瞬間に差が決定的になる（LIKEの早期打ち切りが効かなくなる） - 例外は
ORDER BY id（主キー順）のみ。この場合だけLIKEが勝つことがある - 代償はインデックスサイズ。本体144MBに対してFULLTEXTは93MB（約65%）
実務の検索機能は「新着順」「関連度順」などで並べますが、その前提なら、FULLTEXTが5〜30倍速いというのが今回の実測から得られた結論です。
検証環境
| 項目 | 値 |
|---|---|
| MySQL | 8.4.11（Docker, mysql:8.4） |
| innodb_buffer_pool_size | 256MB |
| ngram_token_size | 2（既定値） |
| ホスト | macOS (Apple Silicon) / Docker Desktop |
検証データ
articles テーブルに10万件。1件あたり本文8〜14文の日本語で、合計144MBです。
CREATE TABLE articles (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
データはPHPのシードスクリプトで生成し、出現率を制御したキーワードを埋め込みました。ヒット率の違いで挙動がどう変わるかを見るためです。
| キーワード | 実際のヒット件数 | ヒット率 |
|---|---|---|
| 量子暗号 | 102件 | 0.1% |
| 機械学習 | 5,052件 | 5% |
| 最適化 | 30,164件 | 30% |
FULLTEXTインデックスは次のように作成しました。
ALTER TABLE articles ADD FULLTEXT INDEX ft_body (body) WITH PARSER ngram;
計測1：件数カウント
まずは単純な件数カウントから。
SELECT COUNT(*) FROM articles WHERE body LIKE '%機械学習%';
SELECT COUNT(*) FROM articles WHERE MATCH(body) AGAINST('"機械学習"' IN BOOLEAN MODE);
| キーワード | ヒット件数 | LIKE | FULLTEXT | 差 |
|---|---|---|---|---|
| 量子暗号 | 102件 | 0.18秒 | 0.04秒 | 4.5倍 |
| 機械学習 | 5,052件 | 0.17秒 | 0.04秒 | 4.3倍 |
| 最適化 | 30,164件 | 0.15秒 | 0.10秒 | 1.5倍 |
わかったこと
LIKEはヒット件数が300倍違っても実行時間がほぼ同じでした。
10万行すべてを取り出して1行ずつ部分文字列を照合するので、コストは「行数 × 本文長」で決まり、ヒット件数とは無関係になります。
一方FULLTEXTは、転置インデックスから該当文書IDのリストを取り出す処理量が件数に比例するため、ヒットが多いほど差が縮みます。
実行計画を見てみる
mysql> EXPLAIN SELECT COUNT(*) FROM articles WHERE body LIKE '%機械学習%';
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | articles | NULL | ALL | NULL | NULL | NULL | NULL | 93068 | 11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
ここで2つ気づいたことがあります。
-
possible_keysすらNULL。仮にbodyにB-Treeインデックスを張ってもLIKE '%...%'では使えません。B-Treeは値を先頭から順に並べた構造なので、前方一致LIKE '機械%'なら範囲を絞れますが、先頭が不定の中間一致では絞れないためです。 -
filtered=11.11はオプティマイザの決め打ち定数（1/9）。中間一致LIKEの選択率は推定できないので固定値が使われます。実際のヒット率は5%でした。
FULLTEXT側はこうなりました。
mysql> EXPLAIN SELECT COUNT(*) FROM articles WHERE MATCH(body) AGAINST('"機械学習"' IN BOOLEAN MODE);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.06 sec)
COUNT(*) の場合は転置インデックスだけで件数が確定するので、テーブルを一切読みません。
この最適化が効くのは COUNT(*) のときだけです。
実行計画を確認するときは、SELECT id, title ... のように行を返すクエリで見ないと実態を見誤ります。
計測2：LIMIT付き（実務に近い形）
次に、検索画面でよくある「先頭20件だけ取る」形で比較しました。
SELECT id, title FROM articles WHERE body LIKE '%機械学習%' LIMIT 20;
SELECT id, title FROM articles WHERE MATCH(body) AGAINST('"機械学習"' IN BOOLEAN MODE) LIMIT 20;
機械学習（ヒット率5%）
| クエリ | 実行時間 | 読んだ行数 |
|---|---|---|
| LIKE | 1.03ms | 393行 |
| FULLTEXT | 0.86ms | 20行 |
ほぼ互角でした。注目すべきは、LIKEが10万行を読んでいないこと。
ヒット率5%なので約400行読めば20件揃い、そこでスキャンを打ち切っています。
量子暗号（ヒット率0.1%）
| クエリ | 実行時間 | 読んだ行数 |
|---|---|---|
| LIKE | 41.7ms | 21,125行 |
| FULLTEXT | 0.86ms | 20行 |
こちらは48倍差。早期打ち切りは「20件見つかるまで」なので、ヒット率が低いと延々と読み続けることになります。
つまり、LIMIT の効果はヒット率に依存するということです。
計測3：ORDER BY付き（ここが決定的）
ORDER BY を付けると「全部見つけてからでないと上位20件が確定しない」ため、早期打ち切りが無効になります。
「機械学習」（5,052件）で、並び順の指定だけを変えて比較しました。
| ORDER BY | LIKE | FULLTEXT | 勝者 |
|---|---|---|---|
| なし | 1.03ms (rows=393) | 0.86ms (rows=20) | 互角 |
id DESC |
2.26ms（Sortなし） | 9.94ms (rows=5,052) | LIKE |
created_at DESC |
148ms (rows=100,000) | 5.71ms | FULLTEXT（26倍） |
body DESC |
206ms (rows=100,000) | 24.2ms | FULLTEXT（8.5倍） |
同じデータ・同じ検索語でも、並び順の指定だけで勝者が入れ替わりました。
これが今回いちばんの学びです。
なぜ ORDER BY id DESC だけLIKEが勝つのか
EXPLAIN ANALYZE を見ると理由がわかりました。
-> Limit: 20 row(s)
-> Filter: (articles.body like '%機械学習%')
-> Index scan on articles using PRIMARY (reverse) rows=451
実行計画に Sort: ノードが存在しません。
InnoDBのテーブル本体は主キー順に並んだB-Tree（クラスタ化インデックス）なので、「id降順」は逆から読むだけで並び替えが完成します。並べ替えが不要になったことで早期打ち切りが復活し、451行で済んでいます。
「主キーインデックスがあるから速い」のではなく、
ORDER BYの順序が既存の物理的な並び順と一致したので、ソート処理そのものが消滅した、と理解するのが正確です。
なぜFULLTEXTでは Sort を消せないのか
-> Sort row IDs: articles.id DESC
-> Filter: (match ...)
-> Full-text index search on articles using ft_body rows=5052
MySQLは1つのテーブルアクセスにつきインデックスを1つしか使えません。
ft_body を使って行を取り出すと決めた時点で、出てくる順序はFULLTEXTの内部順序であり主キー順ではないため、ORDER BY id には必ずソートが必要になります。
ちなみに Sort row IDs は、行全体ではなく行IDだけを並べ替える省メモリの最適化です。body のような巨大カラムを持つテーブルで効いてきます。
ただし、ヒット率が低いとLIKEの勝ち筋も消える
「量子暗号」（102件）で ORDER BY id DESC LIMIT 20 を試すとこうなりました。
| クエリ | 実行時間 | 読んだ行数 |
|---|---|---|
LIKE（ORDER BY id DESC） |
69.4ms | 21,109行 |
LIKE（ORDER BY body DESC） |
199ms | 100,000行 |
ソートは省略できても、20件揃うまで2万行読む必要があるので結局遅い、という結果です。
FULLTEXTの代償
インデックスサイズ
mysql> SELECT name, ROUND(file_size/1024/1024, 1) AS file_mb
-> FROM information_schema.innodb_tablespaces
-> WHERE name LIKE 'demo/%'
-> ORDER BY file_size DESC
-> LIMIT 12;
+----------------------------------------------------+---------+
| name | file_mb |
+----------------------------------------------------+---------+
| demo/articles | 144.0 |
| demo/fts_000000000000042f_00000000000000a4_index_6 | 52.0 |
| demo/fts_000000000000042f_00000000000000a4_index_1 | 40.0 |
| demo/users | 0.1 |
| demo/orders | 0.1 |
| demo/fts_000000000000042f_being_deleted | 0.1 |
| demo/fts_000000000000042f_being_deleted_cache | 0.1 |
| demo/fts_000000000000042f_config | 0.1 |
| demo/fts_000000000000042f_deleted | 0.1 |
| demo/fts_000000000000042f_deleted_cache | 0.1 |
| demo/fts_000000000000042f_00000000000000a4_index_2 | 0.1 |
| demo/fts_000000000000042f_00000000000000a4_index_3 | 0.1 |
+----------------------------------------------------+---------+
12 rows in set (0.01 sec)
mysql> SELECT ROUND(SUM(file_size)/1024/1024, 1) AS fts_total_mb
-> FROM information_schema.innodb_tablespaces
-> WHERE name LIKE 'demo/fts_%';
+--------------+
| fts_total_mb |
+--------------+
| 93.0 |
+--------------+
1 row in set (0.00 sec)
| 対象 | サイズ |
|---|---|
| テーブル本体 | 144MB |
| FULLTEXTインデックス | 93.0MB（本体の約65%） |
ngramで2文字ずつ刻むため、300文字の本文からは約300トークンが生成されます。本体の6割以上というのはなかなかの大きさです。
FULLTEXTインデックスは information_schema.tables の index_length に計上されません。
fts_..._index_1 〜 _6 という内部補助テーブルとして保存されるため、innodb_tablespaces を見る必要があります。
（index_1〜_6 はトークン先頭文字のコード範囲で6分割したもの。日本語は範囲が偏るので、index_1 と index_6 だけが大きくなっていました）
作成コスト
ALTER TABLE articles ADD FULLTEXT INDEX ft_body (body) WITH PARSER ngram;
-- Query OK, 0 rows affected, 1 warning (17.91 sec)
10万件で17.91秒かかりました。警告の内容は「FTS_DOC_ID カラム追加のためテーブルを再構築した」というもの。
InnoDBは全文検索用の隠しカラムを必要とするため、最初のFULLTEXT作成時だけテーブル全体を作り直します。2つ目以降のFULLTEXT追加ではこの再構築は不要です。
ハマりどころ
1. NATURAL LANGUAGE MODE は同じものを数えていない
最初、LIKEとFULLTEXTで件数が全然合わずに混乱しました。
ngramパーサは検索語も分割します。量子暗号 は 量子 子暗 暗号 に分解され、NATURAL LANGUAGE MODE ではそのORで検索されます。
SELECT COUNT(*) FROM articles WHERE body LIKE '%量子暗号%'; -- 102件
SELECT COUNT(*) FROM articles WHERE MATCH(body) AGAINST('量子暗号'); -- 57,699件
SELECT COUNT(*) FROM articles WHERE MATCH(body) AGAINST('"量子暗号"' IN BOOLEAN MODE); -- 102件
なんと566倍の差。「暗号」を含む記事をすべて拾ってしまうためです。
LIKEと公平に比較するには、ダブルクォートで囲んだBOOLEAN MODEのフレーズ検索を使う必要があります。
ちなみに 機械学習 では両モードとも5,052件で一致しました。これは検証データの語彙に 機械 学習 が「機械学習」以外の形で存在しなかったためです。
仕組み上の差が出るかどうかはデータ次第なので、差が出ないケースを見て安心できないと感じました。
2. ngram_token_size より短い語は検索できない
既定値は2なので、1文字での検索は0件になります。
SELECT COUNT(*) FROM articles WHERE MATCH(body) AGAINST('"暗"' IN BOOLEAN MODE); -- 0件
SELECT COUNT(*) FROM articles WHERE body LIKE '%暗%'; -- ヒットする
ngram_token_size はサーバ起動時にしか変更できず、変更したらFULLTEXTインデックスの作り直しも必要です。
3. 更新コスト（今回は未計測）
INSERT/UPDATE時には転置インデックスの更新が発生します。書き込みが多いテーブルでは別途計測が必要で、これは今後の宿題です。
計測で気をつけたこと
同じクエリ・同じ実行計画（rows=393）でも、1.03ms と 19.2ms のように20倍のばらつきが出ることがありました。バッファプールの状態や他プロセスの影響を受けるためです。
- 必ず複数回実行し、2回目以降の値を見る（1回目はディスクI/O込み）
- MySQL 8.4 にクエリキャッシュはない（8.0で廃止）ので、結果の使い回しは起きない
- 時間だけでなく
EXPLAIN ANALYZEのrows（実際に処理した行数） を見る。こちらは構造的な指標なのでばらつかない
EXPLAIN は推定値、EXPLAIN ANALYZE は実際に実行した実測値です。読み方は内側（インデントの深い方）から外側へ。
actual time=2.52..41.7 最初の行まで2.52ms、全部終わるまで41.7ms
rows=21125 実際に処理した行数
loops=1 その処理の繰り返し回数
実務での判断基準（自分なりのまとめ）
FULLTEXTを選ぶべき場合
- 検索結果を関連度順や新着順で並べる（
ORDER BYが付く） - ヒット率が低い検索語が多い（固有名詞、専門用語など）
- 検索が頻繁で、インデックスサイズとその更新コストを払える
LIKEで十分な場合
- データ量が小さい（数千件程度なら差は体感できない）
-
ORDER BY idのような主キー順で、かつヒット率が高い - 書き込みが非常に多く、インデックス更新コストを避けたい
- 1文字検索や部分文字列の厳密一致が要件にある
おわりに
「FULLTEXTは速い」という知識だけだと、ORDER BY id DESC のケースでLIKEに負けた理由を説明できませんでした。
実際に EXPLAIN ANALYZE で Sort ノードの有無 と 実際に読んだ行数 を追いかけたことで、
- 速さを決めているのは「インデックスの有無」ではなく「何行読むか」と「ソートが必要か」
-
LIMITによる早期打ち切りは、ヒット率とORDER BY次第で効いたり効かなかったりする
という、より本質的な理解に辿り着けたのが今回の一番の収穫です。
次は今回宿題にした書き込み時の更新コストを計測してみたいと思います。