MySQLで全文検索機能を使う際のパフォーマンスについて調査してみた

こんにちは、CTOの森です。iimonは今回が初のアドベントカレンダー参加です！本記事はiimonアドベントカレンダー1日目の記事となります。

はじめに
検証した環境
MySQL/mecabのインストール
大量のデータを入れる
- 1レコードのINSERTにかかった時間
検索してみる
- 検索文字列が「出来事」の場合
- 検索文字列が「チューリングはロンドンのリッチモンドに住み」の場合
まとめ
参照したサイト
最後に

はじめに

今回はMySQLで全文検索機能を使う際のパフォーマンスについて書こうと思います！全文検索をちゃんと使うのであればElasticsearchやSolrなど使うべきだとは思いますが、トランザクション内で処理をどうしても行いたい時や別のデータベースを使えない場合に、MySQLでどの程度パフォーマンスが出るのか知っておきたかったので調査してみました。

検証した環境

AWS EC2のm5.largeのインスタンスを使用して下記EBSをアタッチしています。 EBS：gp2, IOPS:300, サイズ:100GB

MySQL/mecabのインストール

# apt-get update -y
# sudo apt-get upgrade -y
# apt-get install -y mysql-server mysql-client mecab mecab-ipadic

MySQLにmecab プラグインの設定

# cp /etc/mecabrc /etc/mysql/mecabrc

/etc/mysql/my.cnf に設定を追加

[mysqld]
loose-mecab-rc-file=/etc/mysql/mecabrc
innodb_ft_min_token_size=2

MySQLの設定適用

# systemctl restart mysql

MeCab プラグインのインストール

mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
mysql> show plugins;
+---------------------------------+----------+--------------------+-------------------+---------+
| Name                            | Status   | Type               | Library           | License |
+---------------------------------+----------+--------------------+-------------------+---------+
| binlog                          | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| sha256_password                 | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| caching_sha2_password           | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
| sha2_cache_cleaner              | ACTIVE   | AUDIT              | NULL              | GPL     |
| daemon_keyring_proxy_plugin     | ACTIVE   | DAEMON             | NULL              | GPL     |
| CSV                             | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| MEMORY                          | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| InnoDB                          | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| INNODB_TRX                      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP                      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP_RESET                | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMPMEM                   | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMPMEM_RESET             | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP_PER_INDEX            | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CMP_PER_INDEX_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_BUFFER_PAGE              | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_BUFFER_PAGE_LRU          | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_BUFFER_POOL_STATS        | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_TEMP_TABLE_INFO          | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_METRICS                  | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_DEFAULT_STOPWORD      | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_DELETED               | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_BEING_DELETED         | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_CONFIG                | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_INDEX_CACHE           | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_FT_INDEX_TABLE           | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_TABLES                   | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_TABLESTATS               | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_INDEXES                  | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_TABLESPACES              | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_COLUMNS                  | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_VIRTUAL                  | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_CACHED_INDEXES           | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE   | INFORMATION SCHEMA | NULL              | GPL     |
| MyISAM                          | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| MRG_MYISAM                      | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| PERFORMANCE_SCHEMA              | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| TempTable                       | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| ARCHIVE                         | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| BLACKHOLE                       | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| FEDERATED                       | DISABLED | STORAGE ENGINE     | NULL              | GPL     |
| ngram                           | ACTIVE   | FTPARSER           | NULL              | GPL     |
| mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL              | GPL     |
| mysqlx                          | ACTIVE   | DAEMON             | NULL              | GPL     |
| auth_socket                     | ACTIVE   | AUTHENTICATION     | auth_socket.so    | GPL     |
| mecab                           | ACTIVE   | FTPARSER           | libpluginmecab.so | GPL     |
+---------------------------------+----------+--------------------+-------------------+---------+
47 rows in set (0.00 sec)

一覧にmecab プラグインが追加されているのが分かります。

次にデータベースとテーブルを作成します。カラムはtitleとbodyというカラムをそれぞれ用意して、プライマリキー以外何もインデックスをつけていないテーブルをno_index、bodyにN-gram(bi-gram)のインデックスを付けたテーブルをbigram_index、bodyにMeCabのインデックスを付けたテーブルをmecab_indexとして作成しています。

mysql> CREATE DATABASE fulltextsearch_perf;
mysql> USE fulltextsearch_perf;
mysql> create table no_index (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL,  body LONGTEXT NOT NULL);
mysql> create table bigram_index (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, body LONGTEXT NOT NULL, FULLTEXT (body) WITH PARSER ngram);
mysql> create table mecab_index (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, body LONGTEXT NOT NULL, FULLTEXT (body) WITH PARSER mecab);

これでno_index, bigram_index, mecab_indexの３つのテーブルができました。

大量のデータを入れる

今回大量の文章データで検証したいのでWikipediaのデータを用意します。https://dumps.wikimedia.org/jawiki/latest/ に最新のデータがあるのでこの中からjawiki-latest-pages-articlesという本文データをダウンロードします。XMLのデータなのでwikiextractorというツールを使ってJSON形式に変換した後データをDBに投入していきます。

wget https://dumps.wikimedia.org/jawiki/latest/jawiki-latest-pages-articles.xml.bz2
pip install wikiextractor
wikiextractor --json -o output jawiki-latest-pages-articles.xml.bz2

output以下にAA~CUまで73個のディレクトリとそれ以下にそれぞれ100個のファイルが出来ました、そのファイル内に更に複数のデータが入っています。今回データ量が多すぎて検証に時間がかかりすぎたためAから始まるディレクトリ以下のファイルのみを使用します。データ数は481,051個、合計文字数は1,322,887,356文字でした（約13.2億文字）

データの投入にPyMySQLを使用しているのでライブラリをインストールします。

pip install PyMySQL

下記のようなコードを用いてそれぞれのテーブルにインサートする部分だけコメントアウトを外して順番に検証していきました。time.txtというファイルにINSERTにかかった時間を書き出して後から集計しています。

import json
import glob
import pymysql
import time

connection = pymysql.connect(host='localhost', port=3306, user='root', password='', db='fulltextsearch_perf')
cursor = connection.cursor()

time_file = open("time.txt", "a")

def insert_item(item):
    start = time.time()
    cursor.execute("INSERT INTO no_index (title, body) VALUES (%s, %s)", (item["title"], item["text"]))
    #cursor.execute("INSERT INTO bigram_index (title, body) VALUES (%s, %s)", (item["title"], item["text"]))
    #cursor.execute("INSERT INTO mecab_index (title, body) VALUES (%s, %s)", (item["title"], item["text"]))
    connection.commit()
    time_diff = time.time() - start
    time_file.write(str(time_diff) + "\n")

def write_db(file_path):
    with open(file_path, encoding='utf-8') as input:
        for line in input:
            json_data = json.loads(line)
            insert_item(json_data)

paths = glob.glob("./output/A*/*", recursive=True)

for path in paths:
    print(path)
    write_db(path)

connection.close()

1レコードのINSERTにかかった時間

1レコードをINSERTするのにかかった平均の時間を計測してみました（Pythonのオーバーヘッドも入っています）

テーブル名	秒
no_index	0.00582
bigram_index	0.11542
mecab_index	0.16659

no_indexが最も早く、bigram_indexはno_indexの約20倍、mecab_indexはno_indexの約29倍とおそらくインデックスの更新に時間がかかっていることが分かりました。MeCabパーサーの方がインデックスの更新する個数は多いはずなので早いかと思っていたのが予想外でした。パーサーの実行時間分余計にかかっているのかもしれません。ただし、１件ごとにかかる時間はmecab_indexでも0.17秒程度と一度に大量に挿入するわけでなければ問題はなさそうな時間でした。

検索してみる

今回は短い文字列「出来事」と長くて特定の１つの文章にしか入っていない「チューリングはロンドンのリッチモンドに住み」といういう文字列を使用して検証していきます。

まずは、「出来事」から検証していきます。

検索文字列が「出来事」の場合

インデックスなし

no_indexテーブルにはインデックスがかかっていないためLIKE検索で検証します。

SELECT id FROM no_index WHERE body LIKE "%出来事%";

10回測った平均が7.938秒でした。EXPLAINも一応見ておくとtypeがALLになっておりフルスキャンになっていることが分かります。

mysql> EXPLAIN SELECT id FROM no_index WHERE body LIKE "%出来事%";
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | no_index | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 609236 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

対象となった件数は4959件です。

mysql> SELECT COUNT(*) FROM no_index WHERE body LIKE "%出来事%";
+----------+
| COUNT(*) |
+----------+
|     4959 |
+----------+
1 row in set (7.91 sec)

N-gram(bi-gram)

全文検索を使用する場合、MATCH AGAINST関数を使用しますが、AGAINST()の引数にIN NATURAL LANGUAGE MODE、IN BOOLEAN MODE、WITH QUERY EXPANSION、IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION(WITH QUERY EXPANSIONと同じ)の４種類を渡すことが出来ますが、今回はIN NATURAL LANGUAGE MODE、IN BOOLEAN MODEを使っていきます。

IN NATURAL LANGUAGE MODE

IN NATURAL LANGUAGE MODEとは類似性を計算して高いものを返します。なので完全一致ではないデータも返します。デフォルト値です。今回出来事という文字を検索してみましたが完全一致の場合4,959件ですが24,370件となっております。

mysql> SELECT COUNT(*) FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|    24370 |
+----------+
1 row in set (0.03 sec)

少し話がずれてしまったので戻すと、IN NATURAL LANGUAGE MODEでの測定をした結果は0.612秒でした。試したクエリは下記です。

SELECT id FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE);

EXPLAINも見ておくとちゃんとtypeがfulltextになっておりインデックスが使われている事が分かります。

mysql> EXPLAIN SELECT id FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE);
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
| id | select_type | table        | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | bigram_index | NULL       | fulltext | body          | body | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.02 sec)

補足をしておくと、完全一致の場合と同じ個数になるようにLIMITを付けた場合はかかった時間の平均が0.27秒となりIN BOOLEAN MODEの時にかかった時間よりも短くなりました。

SELECT id FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE) LIMIT 4959;

mysql> EXPLAIN SELECT id FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE) LIMIT 4959;
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+---------------------------------------------+
| id | select_type | table        | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                                       |
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | bigram_index | NULL       | fulltext | body          | body | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted, limit = 4959 |
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.03 sec)

IN BOOLEAN MODE

IN BOOLEAN MODEは厳密に複数の条件をつけて検索を行うことができます。詳細は省きますが今回は完全一致の条件のみ使って測定します。

完全一致なのでLIKE検索と同じ4,959件となっています。

mysql> SELECT COUNT(*) FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|     4959 |
+----------+
1 row in set (0.21 sec)

下記のクエリで測定すると0.356秒で結果が返ってきました。

SELECT id FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN BOOLEAN MODE);

mysql> EXPLAIN SELECT id FROM bigram_index WHERE MATCH(body) AGAINST ("出来事" IN BOOLEAN MODE);
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table        | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | bigram_index | NULL       | fulltext | body          | body | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+--------------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
1 row in set, 1 warning (0.21 sec)

MeCab

MeCabパーサーを使った場合のパフォーマンスについて調べました。

IN NATURAL LANGUAGE MODE

mysql> SELECT COUNT(*) FROM mecab_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|     4959 |
+----------+
1 row in set (0.01 sec)

件数についてはMeCabパーサー&IN NATURAL LANGUAGE MODEの場合は完全一致の場合と同じく4,959件となりました（4959のLIMITを付けてもかかった時間はほぼ変わらず）

SELECT id FROM mecab_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE);

こちらのクエリにて10回測った平均は0.164秒でした。EXPLAINはこちらです。

mysql> EXPLAIN SELECT id FROM mecab_index WHERE MATCH(body) AGAINST ("出来事" IN NATURAL LANGUAGE MODE);
+----+-------------+-------------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
| id | select_type | table       | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-------------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | mecab_index | NULL       | fulltext | body          | body | 0       | const |    1 |   100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------------+------------+----------+---------------+------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.00 sec)

IN BOOLEAN MODE

mysql> SELECT COUNT(*) FROM mecab_index WHERE MATCH(body) AGAINST ("出来事" IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|     4959 |
+----------+
1 row in set (0.00 sec)

SELECT id FROM mecab_index WHERE MATCH(body) AGAINST ("出来事" IN BOOLEAN MODE);

こちらのクエリにて10回測った平均は0.162秒でした。EXPLAINはこちらです。

mysql> EXPLAIN SELECT id FROM mecab_index WHERE MATCH(body) AGAINST ("出来事" IN BOOLEAN MODE);
+----+-------------+-------------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
| id | select_type | table       | partitions | type     | possible_keys | key  | key_len | ref   | rows | filtered | Extra                             |
+----+-------------+-------------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
|  1 | SIMPLE      | mecab_index | NULL       | fulltext | body          | body | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking |
+----+-------------+-------------+------------+----------+---------------+------+---------+-------+------+----------+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

検索文字列が「チューリングはロンドンのリッチモンドに住み」の場合

手順は「出来事」と同じなのでこちらは「出来事」と比較しながらさくっといきます。かっこ内の数値はIN NATURAL LANGUAGE MODEの時に完全一致検索と同じLIMITを付けた場合の数です。

条件	「出来事」(秒)	「チューリングはロンドンのリッチモンドに住み」(秒)
インデックス無しの場合(LIKE検索)	7.938	8.478
N-gram & IN NATURAL LANGUAGE MODE	0.612(0.27)	6.018(1.482)
N-gram & IN BOOLEAN MODE	0.356	2.718
MeCab & IN NATURAL LANGUAGE MODE	0.164(0.161)	0.35(0.012)
MeCab & IN BOOLEAN MODE	0.162	0.02

＊MeCabで「出来事」の方が時間がかかってしまっている様に見えるのはおそらく一致するドキュメントの数が多いからだと思われます。

まとめ

以上のことから

LIKE検索はかなり遅くデータ量が多い場合は実用性が低いが文字列の長さによって大きく時間も変わらない。

N-gramの場合はIN BOOLEAN MODEよりIN NATURAL LANGUAGE MODEの方がかなり時間がかかるが、LIMITをかけることで改善するため、予想外にレコードが出力されて時間がかかってしまうことを防ぐ為にLIMITをかけておいたほうが良い。IN NATURAL LANGUAGE MODEの場合は用途として、LIMITをかけても問題ない場合が多そうです。

長い文字列の検索の場合、N-gramではかなりパフォーマンスが悪化してしまっています。おそらく裏側でのトークンの持ち方的にN-gram(bi-gram)の方がMeCabに比べてかなり多くなっているからだと思います。

MeCabの場合は文字列が長くなっても今回パフォーマンスの悪化はほとんど見られませんでした。

条件	INSERT(秒)
インデックス無し	0.00582
N-gram	0.11542
MeCab	0.16659

INSERTについてはインデックスが付いていない場合に比べて差はかなりありましたが、そもそも一件あたりにかかる時間が短いので大量のINSERTが一度にされるという場合を除けばそこまで影響はなさそうでした。 BULK INSERTについては今回試していないので今後そのあたりも調べられたらなと思っています。

N-gramとMeCabパーサーは仕組み的に検索した際の結果が違ってくるので、常にMeCabを選んでおけば良いという訳にはいかないですが、今回の検証が使用用途とパフォーマンスを元にパーサーを選ぶ際の少しでも参考になれば幸いです。

参照したサイト

最後に

最後まで読んでくださりありがとうございます！

この記事を読んで興味を持って下さった方がいらっしゃればカジュアルにお話させていただきたく、是非ご応募をお願いします！ Wantedly / Green

次のアドベントカレンダーの記事はたいせいくんです！いつも頑張って仕事に取り組んでくれているのでどんな記事を書いてくれるのか期待しています！

iimon TECH BLOG

iimonエンジニアが得られた経験や知識を共有して世の中をイイモンにしていくためのブログです

MySQLで全文検索機能を使う際のパフォーマンスについて調査してみた

はじめに

検証した環境

MySQL/mecabのインストール

大量のデータを入れる

1レコードのINSERTにかかった時間

検索してみる

検索文字列が「出来事」の場合

インデックスなし

N-gram(bi-gram)

IN NATURAL LANGUAGE MODE

IN BOOLEAN MODE

MeCab

IN NATURAL LANGUAGE MODE

IN BOOLEAN MODE

検索文字列が「チューリングはロンドンのリッチモンドに住み」の場合

まとめ

参照したサイト

最後に