小さな星がほらひとつ

読者です 読者をやめる 読者になる 読者になる

MySQLにおけるlike検索と全文検索のパフォーマンス比較

スポンサーリンク

以前の記事MySQL全文検索に関して書きましたが、では実際にlikeを用いた検索と比べてパフォーマンスにどの程度違いが出るのか、検証してみました。

前提

環境

Mac OS X、MySQL5.7、ストレージエンジンにInnoDBを使います。

データ

or検索用とngramによる全文検索用のテーブルを用意します。

mysql> desc fruits_for_or;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | YES  |     | NULL    |                |
| desctext | text         | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

mysql> desc fruits_for_ngram;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(255) | YES  |     | NULL    |                |
| desctext | text         | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

nameにはフルーツ名、desctextにwikipediaの説明文を下記のとおり格納します。なおfruits_for_ngramにはngram用に分割した文字列を格納してfulltext indexを作ります。

メロン(和名:メロン、英名:melon、学名:Cucumis melo)は、果実を食用にするウリ科の一年生草本植物である。また、その果実のこと。

スイカ(西瓜、学名: Citrullus lanatus)は、果実を食用にするために栽培されるウリ科のつる性一年草。また、その果実のこと。

バナナ(甘蕉、実芭蕉、学名 Musa spp.)はバショウ科バショウ属のうち、果実を食用とする品種群の総称。また、その果実のこと。いくつかの原種から育種された多年草。種によっては熟すまでは毒を持つものもある。

パイナップル(パインアップル、パインナップル、英:pineapple、学名:Ananas comosus)は、熱帯アメリカ原産のパイナップル科の多年草。単にパインと略して呼ばれることもあるほか、昔は鳳梨とも呼ばれていた。また、果実だけをパイナップルと呼び、植物としてはアナナスと呼ぶこともある。

検証

4,000件

まずは1,000件 ✕ 4=4,000件のデータを対象に検索してみます。

/* like検索 */
mysql> select count(*) from fruits_for_or where desctext like ('%メロン%');
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.02 sec)

/* 全文検索 */
mysql> select count(*) from fruits_for_ngram where match(desctext) against('メロ ロン');
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.01 sec)

0.01秒差。ほとんど変わりないです。

40,000件

次は10倍して、10,000件✕4=40,000件で検証してみます。

/* like検索 */
select count(*) from fruits_for_or where desctext like ('%メロン%');
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.16 sec)

/* 全文検索 */
mysql> select count(*) from fruits_for_ngram where match(desctext) against('メロ ロン');
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.04 sec)

0.1秒以上の差が出ました。全文検索の時間はlike検索の1/4程度です。

400,000件

最後は更に10倍の100,000件✕4=400,000件で検証します。

/* like検索 */
mysql> select count(*) from fruits_for_or where desctext like ('%メロン%');                     
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (1.30 sec)

/* 全文検索 */
mysql> select count(*) from fruits_for_ngram where match(desctext) against('メロ ロン');
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.35 sec)

やっぱり全文検索は早い!と言いたいところですが

実は上記の全文検索SQL、不備があります。
それはこのままのSQLだと「メロ」"または"「ロン」を含む文章を抽出してくるため。
例えば「"メロ"ディー」なんかも結果として得られてしまします。

正しく「メロン」を抽出するのであれば、前回の記事で書いたとおりフレーズ検索を使います。

mysql> select count(*) from fruits_for_ngram where match(desctext) against('"メロ ロン"');
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (12.25 sec)

お、遅い!

何故こんなに遅いのか?
それは次回まとめたいと思います。

※上記はあくまでNgramを利用したことでフレーズ検索が必要となっているため、これだけ秒数がかかっています。
形態素解析の場合はフレーズ検索なしに「メロン」で検索できるためlike検索よりもパフォーマンスは良くなると思われます。
ただし形態素解析でも「メロンは美味しい」のようなキーワード検索は、キーワードが品詞に分解されるため結局フレーズ検索が必要となります。

言語処理のための機械学習入門 (自然言語処理シリーズ)

言語処理のための機械学習入門 (自然言語処理シリーズ)

現場で使える MySQL (DB Magazine SELECTION)

現場で使える MySQL (DB Magazine SELECTION)

MySQL徹底入門 第3版 ~5.5新機能対応~

MySQL徹底入門 第3版 ~5.5新機能対応~