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.htmlmysql> SELECT count(*) FROM sample_table;
+----------+
| count(*) |
+----------+
| 4853180 |
+----------+
1 row in set (0.70 sec)
mysql> SELECT count(*) FROM sample_table WHERE sample_column like '%ＧＭＯ%';
+----------+
| count(*) |
+----------+
| 90 |
+----------+
1 row in set (11.33 sec)
mysql> SELECT count(*) FROM sample_table WHERE MATCH (sample_column) AGAINST ('ＧＭＯ' IN BOOLEAN MODE);
+----------+
| count(*) |
+----------+
| 90 |
+----------+
1 row in set (0.06 sec)
