InnoDB での MySQL LIKE '%string%' クエリの最適化

多くのアプリケーションでは、テキスト本文内の文字列を検索する必要があります。 MySQL は、このような検索を実行するための LIKE 演算子を提供しますが、最適なパフォーマンスを得るためにこれらのクエリを最適化するにはどうすればよいですか?

問題

次の表を考えてみましょう:

CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

次のようなクエリを実行すると、

SELECT id FROM example WHERE keywords LIKE '%whatever%'

データベースが次のことを行うことが期待される場合があります。インデックスを使用して検索を高速化します。ただし、

ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);

EXPLAIN クエリを使用してキーワード列に単純なインデックスを追加すると、次のことがわかります。 MySQL は依然としてテーブル全体をスキャンする必要があります。

解決策

MySQL は検索文字列の先頭と一致するインデックスを使用します。 LIKE 'whatever%' のようなクエリの場合、文字列の先頭に 'whatever' がアンカーされているため、インデックスを使用できます。

ただし、LIKE '%whatever%' のようなクエリにはそのようなアンカーがありません。検索用語は文字列内で「浮遊」して表示されるため、MySQL はフィールド全体をスキャンする必要があります。

このようなクエリを最適化するには、フルテキスト インデックスを使用できます。これらのインデックスは、「フローティング」検索用に特別に設計されています。 InnoDB はバージョン 5.6.4 からフルテキスト インデックスをサポートしており、LIKE '%string%' クエリを最適化するための実行可能なオプションになっています。