MySQL × LaravelのLIKE検索と全文検索のパフォーマンスを比較してみた

この記事ではMySQLにおけるLIKE検索と全文検索のパフォーマンスについてまとめます。

LIKE検索よりも速いと言われている全文検索ですが、実際にどのくらい違うのかを確かめたくなったためいろいろなパターンを実施し比較してみました。

結論だけ先にお伝えすると、条件にもよりますがやっぱり全文検索は速かったです。

最後に今回の検証結果の考察を書いているのでぜひ最後まで読んで見てください。

この記事に書いてあること

実施環境

前提として、今回はあいまい検索を対象に記事を書いています。

MySQLの検索

MySQLで検索をする時には大きく2パターンが存在します。

LIKE検索と全文検索です。
それぞれどういったものか簡単に説明します。

LIKE検索

SQLのLIKE演算子を使用してカラムの値に対するパターンマッチングを行う検索です。

検索したいテーブルの対象カラムにLIKEの後に指定した文字列が含まれるかを検証します。
「％」ワイルドカードを利用することで部分一致の検索が可能です。

//完全一致検索
SELECT * FROM {table} WHERE {column} LIKE '文字列'

//末尾一致検索
SELECT * FROM {table} WHERE {column} LIKE '%文字列'
 
//前方一致検索
SELECT * FROM {table} WHERE {column} LIKE '文字列%'
 
//部分一致検索（あいまい検索：どこかに含まれている）
SELECT * FROM {table} WHERE {column} LIKE '%文字列%'

Laravelだと下記のような書き方になります。

//完全一致検索
{Model}::where('{column}', 'like', "文字列");

//末尾一致検索
{Model}::where('{column}', 'like', "%文字列);

//前方一致検索
{Model}::where('{column}', 'like', "文字列%");

//部分一致検索（あいまい検索：どこかに含まれている）
{Model}::where('{column}', 'like', "%文字列%");

全文検索

キーワードを文章の初めから終わりまでを検索することです。
すごいシンプルですね。

インデックスを利用できるのでLIKE検索に比べて高速に検索することが可能です。

全文検索の詳細については下記の記事がわかりやすかったです。

誰でもわかる全文検索入門

zenn.dev

MySQL5.6では日本語の全文検索はサポートしていましたが、N-gramパーサーが標準でサポートされていなかったため、設定などに手間がかかり使い勝手がよくありません。

バージョン5.7以降はN-gramが標準でサポートされたことによって簡単に導入できるようになりました。

N−gramパーサーによってキーワードを自動的にN-gramの形に変換してインデックスを作成してくれます。

Docker・Laravelでの全文検索設定

テーブル作成

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
        DB::statement('ALTER TABLE users ADD FULLTEXT search_index(name) WITH PARSER ngram');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::table('users', function ($table) {
            $table->dropIndex('search_index');
        });
        Schema::dropIfExists('users');
    }
}

インデックスを設定する際にWITH PARSER ngramを指定しないとN-gramが有効にならないため検索が引っかかりませんでした。

my.cnfの設定変更

innodb_ft_min_token_size=2
ft_min_word_len=2
ngram_token_size=2

ここの設定によってN-gramでどのサイズの文字数でぶつ切りにするかを指定します。
今回は2文字を設定しました。

はじめに設定する必要があるのか以上です。

最後に全文検索で取得する方法を記載します。

検索ワードからの取得

$users = User::whereRaw('MATCH(name) AGAINST(? IN BOOLEAN MODE)', ['検索ワード'])
    ->get();

IN BOOLEAN MODEは検索ワードを含むか含まないかを0or1で判断します。
また、修飾子によって細かく条件を指定できるのも特徴です。

MySQLの詳しいモードについてはこちらをご確認ください。

MySQLのLIKE検索と全文検索の比較

検証方法

検索ワードに部分一致するデータを取得するのにかかった時間を計測します。

対象はusersテーブルのレコードが1000件、1万件、10万件の3パターンです。
また、それぞれのパターンで検索対象カラム数を1つ、3つ、5つと分けてLIKE検索と全文検索を検証しました。

• カラム数=1 「name」のみ
• カラム数=3 「address」「company」追加
• カラム数=5 「email」「phone_number」追加

検索ワードはすべて「田中」という文字列を指定します。

結果

1000件

どちらもほとんど差はないですね。

1万件

1つのカラムであればあまり変わりはありませんが、検索対象のカラムが増えると2倍近い差が出ていることがわかります。

10万件

どのカラム数においても全文検索の方が3倍以上速いことがわかりますね。

結論

考察

今回の調査で知ることができてよかったことは1000件程であればLIKE検索で問題ないという点です。
例えば検索する対象のテーブルが1000件ほどで、今後増加しないのであればLIKE検索で十分かなと思いました。

ただサービスにもよりますが、ユーザーテーブルなどは人気なサービスであるほどレコードが増加し続けるため、利用者が1万人を超える想定であれば設計時点で全文検索を視野に入れておくべきかなと思います。

とは言うものの利用者がどれくらい増えるかはサービスをリリースしてみないとわかりません。
全文検索に切り替えるのはそれほど手間ではないのというのを感じたため、初めはLIKE検索で実装して状況によって全文検索に切り替えていくというのもありなのかなと思いました。

しかし、全文検索だと登録・更新時にインデックスと登録し直すため処理が重くなってしまうというデメリットもあるため他の要因も考えて設計する必要があるなと思いました。

もう一つの選択肢として、ElasticSearchなど外部の全文検索エンジンを導入するなどの検討もする必要があると思います。

今回は実施できませんでしたが、もう少し調査したかった点で言うと100万件のパターンと1文字だけで検索するパターンです。

100万件は実施しようと思ったのですが、重すぎてSeeder作成中に落ちてしまったため別の機会にリベンジしたいと思います。

日本語の場合、名前など漢字一文字で検索することも考えられます。
LIKE検索はおそらく結果が変わらないと思うのですが、全文検索の場合N-gramでぶつ切りにする数が増えるため、その分処理が遅くなってしまうのではないかと考えています。

このあたりも再度実施し、結果を追記したいと思います。

最後に

今まで検索機能を実装するときは無思考でLIKE検索を使っていたのですが、パフォーマンスのことを考え始めてからはもっと速くできないかと考え始めました。

どれくらい違うのかが気になったため自由研究的に調査をしましたが、自分の手で動かして確認できたことは良かったです。

今回の結果が何かの参考になれば幸いです。

考察に対するご意見があればコメントいただけますと喜びます。

最後までご覧いただきありがとうございました。