2020.04.07

MySQL5.7の全文検索を試してみた

こんにちは。次世代システム研究室のS.Iです。

今回、担当しているシステムで全文検索の導入を検討する機会があったため、MySQLでの全文検索を試してみましたので、構築方法と性能検証の一部をご紹介します。

全文検索とは

全文検索(ぜんぶんけんさく、英: Full text search)とは、コンピュータにおいて、複数の文書(ファイル)から特定の文字列を検索すること。「ファイル名検索」や「単一ファイル内の文字列検索」と異なり、「複数文書にまたがって、文書に含まれる全文を対象とした検索」という意味で使用される。(Wikipediaより抜粋)

全文検索エンジンとしてはApache Solr、Elasticsearchなどが非常に有名ですが、システム要件的にそこまで大きな仕組みを用意するほどでもない場合は、構築やメンテナンスにかけるコストが大きく、コスト以上のメリットを享受しづらい場面があります。そんな時はMySQLでの全文検索を検討してみるとよいかもしれません。

MySQLと全文検索

MySQL5.6までは日本語のように単語の初めと終わりが明確ではない言語の場合は自分でN-gramを実装してDBに格納したり、検索時に文字列をN-gramして検索するなど、手間が掛かりました。
MySQL5.7ではN-gramが標準サポートされ、簡単にパーサーを指定できる構文のサポートや検索クエリの実行時に自分で実装する必要があった部分がなくなり手軽に導入ができるようになりました。

構築してみる

では、実際に環境を構築して性能を検証してみましょう。今回は国税庁で提供している法人データを利用して会社名の検索をする環境を作って検証します。なお、今回はより簡単に導入できるGCPのCloudSQLを利用するためMroongaなどのプラグインを利用するものは検討の対象外としました。

 

1.DBインスタンスの生成

今回はdb-n1-standard-1、vCPU 1、メモリ 3.75 GBのMySQL 5.7を選択しました。

2.テーブルの作成

通常と同じなので割愛します。

3.インデックスの作成

以下のように検索対象カラムにFullTextインデックスとパーサの指定をします。
ALTER TABLE `sample_table` ADD FULLTEXT INDEX ngram_idx (sample_column) WITH PARSER ngram;

4.データの投入

国税庁からダウンロードした法人データのCSVを投入します。今回はGCPを利用しているため、一旦、ColoudStorageにアップロードして以下のようにDBにインポートするのが一番簡単にデータを投入することができます。

検証してみる

約500万件のデータで、RDBで通常利用するLike検索と全文検索で性能を比較してみました。検索時に指定している「IN BOOLEAN MODE」では検索条件を調整することができます。詳しくは以下に書かれているので興味のある方はご覧ください。https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html
mysql> SELECT count(*) FROM sample_table;
+----------+
| count(*) |
+----------+
| 4853180 |
+----------+
1 row in set (0.70 sec)

mysql> SELECT count(*) FROM sample_table WHERE sample_column like '%GMO%';
+----------+
| count(*) |
+----------+
| 90 |
+----------+
1 row in set (11.33 sec)

mysql> SELECT count(*) FROM sample_table WHERE MATCH (sample_column) AGAINST ('GMO' IN BOOLEAN MODE);
+----------+
| count(*) |
+----------+
| 90 |
+----------+
1 row in set (0.06 sec)

まとめ

今回の検証ではLike検索の「11.33 sec」に対して全文検索では「0.06 sec」と、188倍の性能が得られることが分かりました。MySQLでも十分な性能得られ、フルマネージドなクラウド環境であれば非常に簡単に構築することができました。Apache Solr、Elasticsearchなどの利用で迷われている方は、MySQLを利用した全文検索も検討してみてはどうでしょうか。

最後に

次世代システム研究室では、Webアプリケーション開発や設計を行うアーキテクトを募集しています。Webアプリケーション開発者の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ 募集職種一覧 からご応募をお願いします。

Pocket

関連記事