MySQL 5.7 において InnoDB の全文検索機能が日本語に対応するようになって約 3 年が経ちました。今では InnoDB で日本語の全文検索ができることは広く知られるようになってきたのではないでしょうか。そんな中、今回は MySQL 8.0 においてもまだ修正されていない、全文検索の制限について確認していきたいと思います。
MySQL 5.7 の日本語全文検索とは
まず、MySQL 5.7 において何が変わったのかについて簡単におさらいしたいと思います。
MySQL 5.6 まではデフォルトで FULLTEXT パーサーが使用されていたため、日本語のように単語の初めと終わりが明確でない言語で FULLTEXT インデックスを使用しようとした場合、単語を区切ったり N-gram で分割した状態で DB に格納するなど、使用するためには DB のデータを変形して格納する必要がありました。
※なお、WITH PARSER 句で全文パーサーを指定することはできたため、N-gram や MeCab などを用いた全文検索パーサーを独自に準備すれば、プラグインとして追加することは可能でした
MySQL 5.7 では、日本語対応の全文検索パーサーとして N-gram がインストールされており、MeCab もプラグインとして使用できるように準備されているため、パラメータを追加すればすぐにインストールすることが可能になりました。
全文検索機能の制約について
MySQL 5.7 において非常に使いやすくなった全文検索機能ですが、innodb_ft_result_cache_limit というパラメータの存在に注意する必要があります。
引用:14.14 InnoDB Startup Options and System Variables
The InnoDB full-text search query result cache limit (defined in bytes) per full-text search query or per thread. Intermediate and final InnoDB full-text search query results are handled in memory. Use innodb_ft_result_cache_limit to place a size limit on the full-text search query result cache to avoid excessive memory consumption in case of very large InnoDB full-text search query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when a full-text search query is processed. If the result cache size limit is reached, an error is returned indicating that the query exceeds the maximum allowed memory.
The maximum value of innodb_ft_result_cache_limit for all platform types and bit sizes is 2**32-1.
このパラメータは「実行中を含んだ全文検索クエリーの結果(Intermediate and final InnoDB full-text search query results)」に対してのキャッシュ上限を制御しています。
どういうことなのか、実際に検証して確認していきます。
検証環境
- MySQL 5.7.23 (innodb_ft_min_token_size=1 以外はデフォルト値)
- 以下のテーブルを用意します
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> SHOW CREATE TABLE ft_test\G *************************** 1. row *************************** Table: ft_test Create Table: CREATE TABLE `ft_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `comment` text, PRIMARY KEY (`id`), FULLTEXT KEY `fulltext_idx` (`comment`) /*!50100 WITH PARSER `ngram` */ ) ENGINE=InnoDB AUTO_INCREMENT=770686 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SHOW TABLE STATUS LIKE 'ft_test'\G *************************** 1. row *************************** Name: ft_test Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 630230 Avg_row_length: 265 Data_length: 167411712 Max_data_length: 0 Index_length: 9977856 Data_free: 6291456 Auto_increment: 770686 Create_time: 2018-09-11 15:37:24 Update_time: 2018-09-11 17:11:54 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) |
※ comment 列にはランダム文字列ではなく、青空文庫などから文章をインポートしています
検証内容
ngram_token_size = 2 のとき
まずは、実際に FULLTEXT インデックスがどのように格納されているかを確認します。MySQL 5.7 のデフォルトでは ngram_token_size の値は 2 であるので、2 文字区切り(bigram)でインデックスが作成されていることがわかります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> SET GLOBAL innodb_ft_aux_table = 'test/ft_test'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id, position LIMIT 10; +--------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +--------+--------------+-------------+-----------+--------+----------+ | ペデ | 3 | 91 | 17 | 3 | 0 | | ディ | 3 | 16179 | 60 | 3 | 3 | | ィア | 3 | 17028 | 83 | 3 | 6 | | ア・ | 3 | 12928 | 23 | 3 | 9 | | ・プ | 3 | 66 | 4 | 3 | 12 | | プロ | 3 | 16630 | 33 | 3 | 15 | | ロジ | 3 | 13055 | 18 | 3 | 18 | | ジェ | 3 | 12709 | 10 | 3 | 21 | | する | 3 | 17023 | 2020 | 3 | 24 | | です | 3 | 17028 | 1333 | 3 | 24 | +--------+--------------+-------------+-----------+--------+----------+ 10 rows in set (1 min 33.50 sec) |
続いて、全文検索関数を使用して全文検索クエリーを実行します。
|
1 2 3 |
mysql> SELECT * FROM ft_test WHERE MATCH(comment) AGAINST('東京' IN BOOLEAN MODE); (...) 5440 row in set (0.04 sec) |
このとき、innodb_ft_result_cache_limit はデフォルト値である 2000000000 になっているため、このクエリーには 2GB までのキャッシュが使用できることになります。
上記クエリーが実行できる閾値を探したところ、約 4MB のところで実行できなくなりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SET GLOBAL innodb_ft_result_cache_limit=3852022; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ft_test WHERE MATCH(comment) AGAINST('東京' IN BOOLEAN MODE); (...) 5440 row in set (0.05 sec) mysql> SET GLOBAL innodb_ft_result_cache_limit=3852021; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ft_test WHERE MATCH(comment) AGAINST('東京' IN BOOLEAN MODE); ERROR 188 (HY000): FTS query exceeds result cache limit |
このとき、実際のクエリー結果も 3.5MB 程であったため、結果にそこまで乖離はないと考えられます。
|
1 2 3 |
[root@Bolas ~]# mysql -uroot -e "SELECT * FROM ft_test WHERE MATCH(comment) AGAINST('東京' IN BOOLEAN MODE);" test > ft_test_result.txt [root@Bolas ~]# ls -l ft_test_result.txt -rw-r--r-- 1 root root 3408490 9月 11 20:54 ft_test_result.txt |
ngram_token_size = 1 のとき
続いて、ngram_token_size を 1 にして同様の検証をおこないます。ngram_token_size は動的変数ではないので、変更後は mysqld を再起動して、FULLTEXT インデックスを再作成する必要があります。
|
1 2 3 4 5 6 7 |
mysql> DROP INDEX fulltext_idx ON ft_test; Query OK, 0 rows affected (0.52 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ft_test ADD FULLTEXT KEY fulltext_idx (comment) WITH PARSER ngram; Query OK, 0 rows affected (5 min 26.00 sec) Records: 0 Duplicates: 0 Warnings: 0 |
また、innodb_optimize_fulltext_only を一時的に有効にして OPTIMIZE TABLE を実行することで、INNODB_FT_INDEX_TABLE にインデックス情報を直ちにディスクに書き込むことができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; Query OK, 0 rows affected (0.00 sec) mysql> OPTIMIZE TABLE ft_test; +--------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+----------+----------+----------+ | test.ft_test | optimize | status | OK | +--------------+----------+----------+----------+ 1 row in set (0.04 sec) mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL innodb_ft_aux_table = 'test/ft_test'; Query OK, 0 rows affected (0.07 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id, position LIMIT 10; +------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------+--------------+-------------+-----------+--------+----------+ | ペ | 3 | 544235 | 3680 | 3 | 0 | | デ | 3 | 544746 | 8352 | 3 | 3 | | る | 3 | 27157 | 12243 | 3 | 6 | | ィ | 3 | 544774 | 8352 | 3 | 6 | | ア | 3 | 179434 | 16150 | 3 | 9 | | で | 3 | 30046 | 12283 | 3 | 12 | | で | 3 | 30046 | 12283 | 3 | 12 | | ・ | 3 | 285418 | 14970 | 3 | 12 | | 要 | 3 | 544756 | 12608 | 3 | 12 | | ん | 3 | 43368 | 13519 | 3 | 15 | +------+--------------+-------------+-----------+--------+----------+ 10 rows in set (37.24 sec) |
このとき、先ほどと同じクエリーを実行してエラーになる閾値を確認すると、11MB 程でした。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> SET GLOBAL innodb_ft_result_cache_limit=11395798; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ft_test WHERE MATCH(comment) AGAINST('東京' IN BOOLEAN MODE); (...) 5440 row in set (0.08 sec) mysql> SET GLOBAL innodb_ft_result_cache_limit=11395797; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM ft_test WHERE MATCH(comment) AGAINST('東京' IN BOOLEAN MODE); ERROR 188 (HY000): FTS query exceeds result cache limit |
クエリーの結果自体は ngram_token_size = 2 と同じく 3.5MB 程であるため、実際の結果より多くのキャッシュを使用していることがわかります。
この他、ngram_token_size = 3 および MeCab パーサーを使用したときの innodb_ft_result_cache_limit の閾値を調査したところ、以下のようになりました。
| Full-Text Parser | ngram_token_size | innodb_ft_result_cache_limit |
|---|---|---|
| N-gram | 1 | 11395798 |
| N-gram | 2 | 3852022 |
| N-gram | 3 | 1000000(最小値) |
| MeCab | – | 3693430 |
区切り文字が短くなる(検索の精度があがる)程、クエリーの実行に必要なキャッシュが大きくなっているのがわかります。
問題点
innodb_ft_result_cache_limit における問題点は主に 2 点あると考えられます。
パラメータの上限値
Bug #86036においても指摘されていますが、このパラメータは上限値が 2**32-1(約4GB) しかありません。
そのため、全文検索を使用したくなるような行数の多いテーブルにおいては、クエリーの実行に必要なキャッシュが innodb_ft_result_cache_limit の上限値を超えてしまいやすくなるため、肝心なときに使用できない、といった事態が起こりやすくなります。
メモリーの使用量
innodb_ft_result_cache_limit の閾値は、クエリーを実行している時セッションが実際に使用しているメモリー量と等しくなります。
そのため、Bug #84195においても指摘されていますが、たとえば innodb_ft_result_cache_limit を 3GB にしないと実行できないような全文検索クエリーは、クエリの実行中 3GB のメモリーを使用してしまいます。そのため、全文検索クエリーが同時に実行されるような環境では、事前にクエリーの負荷を確認しておかないと、メモリーの急騰に耐え切れなくなる恐れがあります。
まとめ
上記の問題点は、MySQL 8.0.12 現在、まだ修正されていません。また、innodb_ft_result_cache_limit = 4000000000 を設定しても実行できない(「FTS query exceeds result cache limit」となる)クエリは、InnoDB の全文検索機能を使用できないため、サードパーティ製の全文検索エンジン(Mroonga)などで代替する必要があります。
それでも、InnoDB を使用したまま全文検索がカジュアルにおこなえるのは他にはない利点ですので、今後、全文検索機能がどのように発展していくのか、注目していきたいと思います。
