MySQL InnoDB の全文検索機能をサクッと試してみました

こんにちは、GMOアドマーケティングのK.Mです。

最近は久しぶりにMySQLを使ってます。

そういえばMySQLといえば、バージョン5.7からInnoDBの全文検索機能に日本語パーサーが搭載されとても使いやすくなったと聞いていたので、本日はそれを試してみたいと思います。

以前はサービスで本格的な全文検索をやりたいと思ったら、Elasticsearchなど専用の全文検索エンジンを立てたりとミドルウェア構成が一段リッチになるような印象もありましたが、もう少しお手軽に、既存RDBMSからSELECTしてくるくらいのイメージでスモールスタートしたいようなケースも結構ありそうです。

そういったときに検討できる一つの選択肢になるんじゃないかと思っています。

既存テーブルを検索してみます

MySQL5.7から日本語パーサーとしてN-gramとMeCabが使えます。特にN-gramの場合、デフォルトで有効になっているため特に準備など必要なく使い始めることができます。

こんな感じのテーブルがあるとします。

CREATE TABLE items (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  body TEXT
) ENGINE=InnoDB CHARACTER SET utf8mb4;

INSERT INTO items (body) VALUES
  ('東京都で遊ぶ'),
  ('京都で遊ぶ'),
  ('大阪で遊ぶ'),
  ('京都と大阪で遊ぶ');

CREATE TABLE items (

FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

body TEXT

) ENGINE=InnoDB CHARACTER SET utf8mb4;

INSERT INTO items (body) VALUES

('東京都で遊ぶ'),

('京都で遊ぶ'),

('大阪で遊ぶ'),

('京都と大阪で遊ぶ');

bodyカラムにN-gramパーサーを指定したFULLTEXT INDEXを貼ってみます。

ALTER TABLE items ADD FULLTEXT INDEX ngram_idx (body) WITH PARSER ngram;

1 2	ALTER TABLE items ADD FULLTEXT INDEX ngram_idx (body) WITH PARSER ngram;

実践的にはここで、検索対象データを別の検索用テーブルへ抜き出して、ソーステーブルの更新をトリガーに検索用テーブルを更新するような構成にするのも良さそうです。特に更新が多いテーブル・カラムを検索対象としたいような場合は、ソーステーブルのパフォーマンスに悪影響を出さずに済み良さそうです。

それでは検索してみます。

SELECT * FROM items WHERE MATCH (body) AGAINST ('東京' IN BOOLEAN MODE);
+------------+--------------------+
| FTS_DOC_ID | body               |
+------------+--------------------+
|          1 | 東京都で遊ぶ       |
+------------+--------------------+
SELECT * FROM items WHERE MATCH (body) AGAINST ('京都' IN BOOLEAN MODE);
+------------+--------------------------+
| FTS_DOC_ID | body                     |
+------------+--------------------------+
|          1 | 東京都で遊ぶ             |
|          2 | 京都で遊ぶ               |
|          4 | 京都と大阪で遊ぶ         |
+------------+--------------------------+
SELECT * FROM items WHERE MATCH (body) AGAINST ('+京都 -東京' IN BOOLEAN MODE);
+------------+--------------------------+
| FTS_DOC_ID | body                     |
+------------+--------------------------+
|          2 | 京都で遊ぶ               |
|          4 | 京都と大阪で遊ぶ         |
+------------+--------------------------+
SELECT * FROM items WHERE MATCH (body) AGAINST ('+京都 +大阪 -東京' IN BOOLEAN MODE);
+------------+--------------------------+
| FTS_DOC_ID | body                     |
+------------+--------------------------+
|          4 | 京都と大阪で遊ぶ         |
+------------+--------------------------+
SELECT * FROM items WHERE MATCH (body) AGAINST ('+京都 大阪 -東京' IN BOOLEAN MODE);
+------------+--------------------------+
| FTS_DOC_ID | body                     |
+------------+--------------------------+
|          4 | 京都と大阪で遊ぶ         |
|          2 | 京都で遊ぶ               |
+------------+--------------------------+

SELECT * FROM items WHERE MATCH (body) AGAINST ('東京' IN BOOLEAN MODE);

+------------+--------------------+

| FTS_DOC_ID | body |

+------------+--------------------+

| 1 | 東京都で遊ぶ |

+------------+--------------------+

SELECT * FROM items WHERE MATCH (body) AGAINST ('京都' IN BOOLEAN MODE);

+------------+--------------------------+

| FTS_DOC_ID | body |

+------------+--------------------------+

| 1 | 東京都で遊ぶ |

| 2 | 京都で遊ぶ |

| 4 | 京都と大阪で遊ぶ |

+------------+--------------------------+

SELECT * FROM items WHERE MATCH (body) AGAINST ('+京都 -東京' IN BOOLEAN MODE);

+------------+--------------------------+

| FTS_DOC_ID | body |

+------------+--------------------------+

| 2 | 京都で遊ぶ |

| 4 | 京都と大阪で遊ぶ |

+------------+--------------------------+

SELECT * FROM items WHERE MATCH (body) AGAINST ('+京都 +大阪 -東京' IN BOOLEAN MODE);

+------------+--------------------------+

| FTS_DOC_ID | body |

+------------+--------------------------+

| 4 | 京都と大阪で遊ぶ |

+------------+--------------------------+

SELECT * FROM items WHERE MATCH (body) AGAINST ('+京都大阪 -東京' IN BOOLEAN MODE);

+------------+--------------------------+

| FTS_DOC_ID | body |

+------------+--------------------------+

| 4 | 京都と大阪で遊ぶ |

| 2 | 京都で遊ぶ |

+------------+--------------------------+

いくつかのMODEを選択できますが、今回は最も直感的な操作でイメージに近い結果が得られたのでBOOLEAN MODEを使ってます。BOOLEAN MODEでは演算子 +: AND, -: NOT, [演算子なし]: 暗黙的にOR などが使えます。

インデックスの内容を確認してみます

さて、検索対象のテキストがどのようにパースされてインデックスに登録されているか知りたいときがあります。特にMeCabパーサー指定時などは知りたい機会が多そうです。

INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE（およびINNODB_FT_INDEX_CACHE）に転置インデックスに関する情報が含まれており、これを参照することで確認できるようです。ただし確認したい対象テーブルをあらかじめ構成変数innodb_ft_aux_tableにSETしておく必要があるようです。

SET GLOBAL innodb_ft_aux_table="fts/items";
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id, position LIMIT 30;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 東京   |            1 |           1 |         1 |      1 |        0 |
| 京都   |            1 |           4 |         3 |      1 |        3 |
| 都で   |            1 |           2 |         2 |      1 |        6 |
| で遊   |            1 |           4 |         4 |      1 |        9 |
| 遊ぶ   |            1 |           4 |         4 |      1 |       12 |
| 京都   |            1 |           4 |         3 |      2 |        0 |
| 都で   |            1 |           2 |         2 |      2 |        3 |
| で遊   |            1 |           4 |         4 |      2 |        6 |
| 遊ぶ   |            1 |           4 |         4 |      2 |        9 |
| 大阪   |            3 |           4 |         2 |      3 |        0 |
| 阪で   |            3 |           4 |         2 |      3 |        3 |
| で遊   |            1 |           4 |         4 |      3 |        6 |
| 遊ぶ   |            1 |           4 |         4 |      3 |        9 |
| 京都   |            1 |           4 |         3 |      4 |        0 |
| 都と   |            4 |           4 |         1 |      4 |        3 |
| と大   |            4 |           4 |         1 |      4 |        6 |
| 大阪   |            3 |           4 |         2 |      4 |        9 |
| 阪で   |            3 |           4 |         2 |      4 |       12 |
| で遊   |            1 |           4 |         4 |      4 |       15 |
| 遊ぶ   |            1 |           4 |         4 |      4 |       18 |
+--------+--------------+-------------+-----------+--------+----------+

SET GLOBAL innodb_ft_aux_table="fts/items";

Query OK, 0 rows affected (0.00 sec)

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id, position LIMIT 30;

+--------+--------------+-------------+-----------+--------+----------+

+--------+--------------+-------------+-----------+--------+----------+

| 東京 | 1 | 1 | 1 | 1 | 0 |

| 京都 | 1 | 4 | 3 | 1 | 3 |

| 都で | 1 | 2 | 2 | 1 | 6 |

| で遊 | 1 | 4 | 4 | 1 | 9 |

| 遊ぶ | 1 | 4 | 4 | 1 | 12 |

| 京都 | 1 | 4 | 3 | 2 | 0 |

| 都で | 1 | 2 | 2 | 2 | 3 |

| で遊 | 1 | 4 | 4 | 2 | 6 |

| 遊ぶ | 1 | 4 | 4 | 2 | 9 |

| 大阪 | 3 | 4 | 2 | 3 | 0 |

| 阪で | 3 | 4 | 2 | 3 | 3 |

| で遊 | 1 | 4 | 4 | 3 | 6 |

| 遊ぶ | 1 | 4 | 4 | 3 | 9 |

| 京都 | 1 | 4 | 3 | 4 | 0 |

| 都と | 4 | 4 | 1 | 4 | 3 |

| と大 | 4 | 4 | 1 | 4 | 6 |

| 大阪 | 3 | 4 | 2 | 4 | 9 |

| 阪で | 3 | 4 | 2 | 4 | 12 |

| で遊 | 1 | 4 | 4 | 4 | 15 |

| 遊ぶ | 1 | 4 | 4 | 4 | 18 |

+--------+--------------+-------------+-----------+--------+----------+

このように、今回指定したN-gramパーサーのデフォルト値であるbi-gramでパースされていることが確認できます。

SHOW VARIABLES like 'ngram_token_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+

SHOW VARIABLES like 'ngram_token_size';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| ngram_token_size | 2 |

+------------------+-------+

おわりに

今回は、日本語パーサーが導入されたMySQL5.7の全文検索機能を試してみました。試せてない機能もまだまだたくさんありますが、肌感的には大変便利そうな感触を得ることができました。

今後、サービスに全文検索機能を組み入れる機会などあれば検討できるように準備してみたいと思います。以上です！

K.M

既存テーブルを検索してみます

インデックスの内容を確認してみます

おわりに

おすすめ

Railsのタグ機能でN+1問題解消

Professional Scrum Master ⅠとⅡに合格しました

background-imageを活用して、アップする画像を1個で済ませたい