MySQL5.7 InnoDB のN-gram全文検索を検証＆サービス導入した

※ この記事は以前Mediumで公開した記事 の転載です

MySQL5.7・InnoDB・N-gram という環境下で全文検索の挙動やパフォーマンスについて検証を行った。FULLTEXT INDEXは以前はMyISAMでしか利用できなかったが、 5.6.4からInnoDBでのサポートが始まっていた。

InnoDBの全文検索は5.7、特に5.7.6以降でいわゆるCJK（中国語・日本語・韓国語）がN-gramで標準サポートされ始め、 CREATE TABLE文で簡単にパーサーを指定できる構文のサポート、 設定やクエリの組立で考えないといけない事が減った事で導入障壁がかなり下がっている。 ※4.1と5.0でサービス導入経験がある私の個人的な比較感想です。

FULLTEXT INDEXも他のINDEXと同様にデータ更新・削除の際にINDEXのrebuildが走るので更新時の負荷には注意が必要で、FULLTEXT INDEXの場合は「rebuild負荷が列に含まれる単語数に比例する」「rebuildでの断片化が起こりやすい」という固有の注意点もあり、「件数が多い」「FULLTEXT INDEXを貼ったカラムの更新頻度が激しい」という条件が揃ったテーブルへの導入は十分な検証の上で実施した方が良さそう。

下記に書いた検証を経て開発したサービスでも導入してみたが、結論だけ書くと「（当たり前だが）用法用量さえ守れば十分使える。バックエンドのDBがMySQLなら、リッチな全文検索機構を導入する前段階なんかは活用場面としてオススメできる」と感じた。

やること

国税庁が公開している法人番号データを使って検証してみる。データ件数が100万件強程度になるように東京・大阪・海外 の3種類をマージしたデータを使った。用意したデータとテーブルについてはgithubにupしている。charsetをutf8mb4にしているのは、utf8だと登録時にエラーになる文字を含んだ法人が複数存在する為。collationはデフォルトであるuft8mb4_general_ciを使用する。

1
2
3
4
5
6
7
8

> show collation like 'utf8mb4%' \G;
*************************** 1. row ***************************
Collation: utf8mb4_general_ci
  Charset: utf8mb4
       Id: 45
  Default: Yes
 Compiled: Yes
  Sortlen: 1

今回付けたFULLTEXT INDEXは会社名検索・住所検索を想定したものにしている。

 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
34
35
36
37

-- 法人番号データ投入用テーブルのCREATE文
CREATE TABLE `corporate_nums` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sequence_number` varchar(8) NOT NULL,
  `corporate_number` char(13) DEFAULT NULL,
  `process` char(2) DEFAULT NULL,
  `correct` char(1) DEFAULT NULL,
  `update_date` char(10) DEFAULT NULL,
  `change_date` char(10) DEFAULT NULL,
  `name` varchar(150) DEFAULT NULL,
  `name_image_id` char(8) DEFAULT NULL,
  `kind` char(3) DEFAULT NULL,
  `prefecture_name` varchar(10) DEFAULT NULL,
  `city_name` varchar(20) DEFAULT NULL,
  `street_number` varchar(300) DEFAULT NULL,
  `address_image_id` char(8) DEFAULT NULL,
  `prefecture_code` char(2) DEFAULT NULL,
  `city_code` char(3) DEFAULT NULL,
  `post_code` char(7) DEFAULT NULL,
  `address_outside` varchar(300) DEFAULT NULL,
  `address_outside_image_id` char(8) DEFAULT NULL,
  `close_date` char(10) DEFAULT NULL,
  `close_cause` char(2) DEFAULT NULL,
  `successor_corporate_number` char(13) DEFAULT NULL,
  `change_cause` varchar(500) DEFAULT NULL,
  `assignment_date` char(10),
  `latest` char(1) DEFAULT NULL,
  `en_name` varchar(300) DEFAULT NULL,
  `en_prefecture_name` varchar(9) DEFAULT NULL,
  `en_city_name` varchar(600) DEFAULT NULL,
  `en_address_outside` varchar(600) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cn_i1` (`sequence_number`),
  KEY `cn_i2` (`corporate_number`),
  FULLTEXT KEY `cn_fti1` (`name`, `en_name`) WITH PARSER ngram,
  FULLTEXT KEY `cn_fti2` (`prefecture_name`, `city_name`, `street_number`, `en_prefecture_name`, `en_city_name`) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

件数は↓この通り

1
2
3
4
5
6

mysql> select count(*) from corporate_nums;
+----------+
| count(*) |
+----------+
|  1369037 |
+----------+

検証環境

InnoDBのFULLTEXT INDEXに関連するパラメータは全てデフォルトのままで変更はしない。単語分割の単位は通常 innodb_ft_min_token_size と innodb_ft_max_token_size で設定するのだが、N-gramの場合は無視されて代わりに ngram_token_size というパラメータの内容が適用される。デフォルト値は2なので、今回の検証では 2-gram で分割されたwordで転置インデックスが登録される。例として「富士山」というデータの場合は「富士」「士山」の2通りのwordで登録される。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

> show variables like 'innodb_ft%';
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| innodb_ft_aux_table             |                |
| innodb_ft_cache_size            | 8000000        |
| innodb_ft_enable_diag_print     | OFF            |
| innodb_ft_enable_stopword       | ON             |
| innodb_ft_max_token_size        | 84             |
| innodb_ft_min_token_size        | 3              |
| innodb_ft_num_word_optimize     | 2000           |
| innodb_ft_result_cache_limit    | 2000000000     |
| innodb_ft_server_stopword_table |                |
| innodb_ft_sort_pll_degree       | 2              |
| innodb_ft_total_cache_size      | 640000000      |
| innodb_ft_user_stopword_table   |                |
+---------------------------------+----------------+
> show variables like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

INDEXの状態を確認しながら検証したいので、INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE テーブルが使える準備をしておく。このテーブルはInnoDB FULLTEXT INDEXの転置インデックス管理を行う為のテーブルで、2-gramで分けられたwordとそれらに振られるID・出現頻度 などが登録される。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
mysql> OPTIMIZE TABLE corporate_nums;
mysql> SET GLOBAL innodb_ft_aux_table = 'dummy/corporate_nums';   -- 'DB名/TBL名'
mysql> SELECT word, doc_count, doc_id, position FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;
+------+-----------+---------+----------+
| word | doc_count | doc_id  | position |
+------+-----------+---------+----------+
| 's   |         1 |  128141 |       82 |
| 's   |         1 | 1347655 |       54 |
| (d   |         1 |  775740 |      106 |
| (f   |         1 | 1162473 |       63 |
| (j   |         1 |  134835 |      118 |
+------+-----------+---------+----------+

結果と考察

  • INDEXが効かない部分一致LIKE
  • FULLTEXT INDEXを使っての部分一致

の2パターンで比較してみる。全文検索モードについては、検索対象が固有名詞で自然言語による曖昧検索を行うと検索結果に含まれるノイズが増えそう＝法人番号社名検索という用途には不向き という判断で、BOOLEAN MODEで厳密にマッチする検索を行う。

まず name, en_name の2カラムを対象に “コード” という文字列でLIKE検索(2カラム分のLIKE条件をORで結合）と全文検索の結果を比較してみると、前者が約1.2sec、後者が約0.2secと、全文検索によってパフォーマンスが良くなった。

 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

mysql> EXPLAIN SELECT COUNT(*) FROM corporate_nums WHERE name LIKE '%コード%' or en_name LIKE '%コード%';
+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | corporate_nums | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1273830 |    20.99 | Using where |
+----+-------------+----------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM corporate_nums WHERE name LIKE '%コード%' or en_name LIKE '%コード%';
+----------+
| COUNT(*) |
+----------+
|      566 |
+----------+
1 row in set, 1 warning (1.26 sec)
mysql> EXPLAIN SELECT COUNT(*) FROM corporate_nums WHERE MATCH(name, en_name) AGAINST('コード' IN BOOLEAN MODE);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM corporate_nums WHERE MATCH(name, en_name) AGAINST('コード' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
|      566 |
+----------+
1 row in set, 1 warning (0.20 sec)

同じような SELECT COUNT(*) なクエリによる比較調査を検索対象文字列を変えつつ試してみた結果は下記の通りとなった。文字列については「2文字 or NOT」という根拠で選んで、件数と応答速度に関連があるという予想で比較してみた。 ※応答速度は10回クエリを投げてみての平均値を採用している。

1
2
3
4
5
6
7
8
9

| 文字列       | LIKE検索応答速度 | 全文検索応答速度 | HIT件数 |
|:-----------:|---------------:|--------------:|--------:|
| ワーク        | 1.28 sec       | 0.50 sec      | 9,132   |
| コード        | 1.21 sec       | 0.20 sec      | 566     |
| アイス        | 1.27 sec       | 0.09 sec      | 254     |
| 開発        | 1.23 sec       | 0.01 sec      | 6,151   |
| 牧場        | 1.26 sec       | 0.00 sec      | 82      |
| 佐々木       | 1.24 sec       | 0.01 sec      | 569     |
| 五反田       | 1.22 sec       | 0.00 sec      | 61      |

文字数が同じでHIT件数も限りなく近い「コード」と「佐々木」が、応答速度では「佐々木」が速い（「コード」が遅い）という結果になった。「コード」に限らず、カタカナでの全文検索は速度が落ちるという結果になった ※これについてはもうちょっと深掘りして調べてみたい。

MEMO: 全文検索結果のソートについて

  • 例えばLIMITによるページングの為に結果をソートするようなケースで、適合性以外でORDER BYすると100% filesortが発生するので避ける。IDのような主キーでORDER BYしても同じ
  • 全文検索結果のソートは 適合性が高い順 がデフォルトの挙動で、MATCH AGAINST をSELECT対象にも含めると適合性が取得でき、この適合性でソートすると速い
  • ↓こういうSQLを書くと速い。ASC だと遅くなってしまう
1
2
3
4
5
6
7
8
9

SELECT
  *,
  MATCH(name) AGAINST ('東京' IN BOOLEAN MODE) AS score
FROM
  corporate_nums
WHERE
  MATCH(name) AGAINST ('東京' IN BOOLEAN MODE)
ORDER BY score  DESC  -- ASCは遅くなる
LIMIT 0,10

MEMO: FULLTEXT INDEXの挙動について

  • FULLTEXT INDEXのみ付与されたカラムに、MATCH AGAINST文以外の条件指定をした場合、INDEXは効かない
  • FULLTEXT INDEXのみ付与されたカラム + 他INDEXが付与されたカラム の複合条件を指定した場合、 「他INDEXが付与されたカラム」による絞込の方が効率的であってもそのINDEXは無視されて、FULLTEXT INDEXによる検索が実行される
  • FULLTEXT INDEXでもカバリングインデックスとしては使えない（FULLTEXT INDEXはN-gramで分けられた単語だけが登録され、実際の値はINDEXには登録されない）