インデックスがあるのにLIKE検索が遅い理由
MySQLで検索機能を実装すると、多くの人が一度は混乱します。
「インデックスを貼ったのに遅い」
特に次のようなSQLです。
SELECT * FROM users WHERE name LIKE '%田中%';
インデックスが設定されているのに、なぜかフルスキャンになります。
EXPLAINを見ると index ではなく ALL が表示されます。
ここでよく言われる説明はこうです。
「前方一致じゃないから」
これは正しいのですが、実は不十分です。
LIKE検索が効かない理由は、単に「%が先頭にあるから」だけではありません。
本当の理由は、MySQLのインデックスの仕組みにあります。
B-Treeインデックスは辞書と同じ
MySQLの通常のインデックスはB-Treeです。
これは本の索引と同じ構造です。
例えば電話帳を想像してください。
- 「田中」で始まる人 → すぐ探せる
- 「中」を含む人 → 最初から全部見る必要がある
つまりB-Treeは「先頭から並んでいる」ことを前提にしています。
LIKE '田中%'
この場合、先頭が分かっています。
MySQLは「田中」から始まる位置へジャンプできます。
しかし、
LIKE '%田中%'
では先頭が分かりません。
どこから読むべきか判断できないため、全行確認します。
これがフルテーブルスキャンです。
なぜ英語では問題になりにくいのか
英語圏のシステムでは、この問題はそれほど深刻になりません。
理由は検索方法です。
英語検索は次の形式が多いです。
- email = 'user@example.com'
- name LIKE 'john%'
つまり前方一致が中心です。
一方、日本語検索は違います。
- 部分一致
- 曖昧検索
- キーワード検索
ユーザーは文字列の途中を検索します。
この時点でB-Treeインデックスと相性が悪くなります。
インデックスが使われないもう1つの理由
実は、先頭一致でもインデックスが使われない場合があります。
原因は照合順序（COLLATION）です。
WHERE name LIKE 'たなか%'
一見問題なさそうですが、MySQLは比較前に文字列正規化を行うことがあります。
大文字小文字、全角半角、アクセントなどを統一して比較します。
この処理が入ると、単純な範囲検索ができません。
結果としてインデックスが無効になります。
つまり、LIKE検索が効かない原因は2つあります。
- 先頭が不明
- 比較規則が複雑
EXPLAINで確認する
次のコマンドで実際の挙動が確認できます。
EXPLAIN SELECT * FROM users WHERE name LIKE '田中%';
ここで type が range ならインデックス使用、ALL なら全件検索です。
開発中は速くても、本番で遅くなることがあります。
理由はデータ量です。
数百件では差が出ませんが、数百万件で顕著になります。
よくある誤解
よく行われる対処があります。
- インデックスを増やす
- 複合インデックスにする
しかし LIKE '%文字%' では基本的に効果はありません。
どれだけインデックスを増やしても、開始位置が分からないためです。
これは設計の問題で、最適化の問題ではありません。
現実的な解決策
対策はいくつか存在します。
前方一致に変える
UIをオートコンプリートにします。
入力途中で候補表示にするだけで、検索速度は劇的に改善します。
FULLTEXTインデックス
ALTER TABLE users ADD FULLTEXT INDEX ft_name(name);
部分一致検索が可能になります。
ただし日本語ではngramパーサが必要です。
検索専用カラムを作る
例えば「ひらがな変換カラム」を用意します。
検索キーを限定することで範囲検索が可能になります。
注意点とリスク
FULLTEXT検索には重要な制限があります。
- 短い単語が検索できない
- 完全一致ではない
- スコア順になる
従来のLIKE検索と同じ結果にはなりません。
また、検索ロジックを変えると、ユーザー体験も変わります。
単に高速化だけの問題ではありません。
LIKE検索の本質
LIKE検索は「遅い機能」ではありません。
用途が限定された機能です。
B-Treeインデックスは「キー検索」に最適化されています。
文章検索には向いていません。
つまり問題はSQLの書き方ではなく、
「データベースに任せてよい検索か」という設計です。
LIKE検索が遅いとき、最適化より先に
検索の役割を整理する方が、結果として安定したシステムになります。
