MySQL の FULLTEXT とは

2021-11-16

こんにちは

どうも、僕です。

MySQL（MariaDB）に搭載されてる最強な検索、FULLITEXT INDEX について書きます。

前回のブログ

で、検索をアプリケーション側で実装する方法について書きましたが、今回はそれのデータベース側からのアプローチです。

このレイヤーでデータを操作するのは賛否が分かれる部分だとは思いますが、簡単に実装してみたのでやっていきます。

そもそも何

インデックスです。

全文検索をデータベースのレイヤーで実装できないか見ていたら出てきました。（参考：[https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html](https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html)）

これを使うと、ストップワードとして定義されている単語が省略されることでとても速い検索が実現できます。

構文は以下のようになっています。

SELECT columns FROM table WHERE MATCH(index, ...) ... AGAINST('search target word');

また、定義する際には以下のようになります。（ドキュメントから抜粋）

CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;

FULLTEXT は、CREATE TABLE の際に追加することも、ALTER TABLE の際に追加することもできます。

文章分割の方法

MySQL5.6 から利用可能で、5.6までは形態素解析を用いた文章の分割、5.7.3以降はn-gramを使用した文章の分割をサポートしています。

n-gram のパーサについては [ここ](https://dev.mysql.com/doc/refman/8.0/ja/fulltext-search-ngram.html) で説明されていて、n-gram の n分割の値も指定できるようです。

弱点

弱点もあります。通常のインデックスを貼る動作と同じで、read は速くなりますが、それ以外の操作が遅くなります。これはしょうがないと言えるでしょう。

対象のデータ

以下のようなテーブルを定義します。

CREATE TABLE IF NOT EXISTS `table_name` (
    `id`        int unsigned NOT NULL AUTO_INCREMENT,
    `name`   varchar(255) NOT NULL,
    `text` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    FULLTEXT idx (name, text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ベンチマーク

table_name という名前のテーブルを先ほど定義したので、そこに雑なデータを突っ込みます。

ここでいう雑なデータとは、頑張ってコピペして作ったランダムテキストが入ったデータを150万レコード格納しました。（for文回しただけ）

その中に、takurinton というレコードを1つ追加してみました。ということで、takurinton というワードを含むデータを検索してみます。

LIKE句による検索

お馴染みの検索です。

テキストは takurinton だけですが、一応ワイルドカードで検索をしてみます。

mysql> SELECT count(*) FROM table_name WHERE text LIKE '%takurinton%' OR name LIKE '%takurinton%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (1.64 sec)

やはり、データ数が多いので、検索に時間がかかります。これがもっとたくさんのレコードにヒットするようなデータだとさらに検索に時間がかかるかと思います。

FULLTEXT INDEX による検索

次に、今回の本題である FULLTEXT 検索です。

mysql> SELECT count(*) FROM table_name WHERE MATCH(name ,text) AGAINST('+takurinton' IN BOOLEAN MODE);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

爆速です。0秒です。すごい。

これだと僕の前回のブログで書いた内容はなくてよかったことになり、この世から消えるべき存在と言えます。（実装の意図やユースケースによりますが...。）

これだけ速いとは思いませんでした。びっくり。

EXPLAIN で中身を除く

これだけ速いと中身が気になるので見ます。

LIKE句による検索だと、1480001レコードしっかり確認していることがわかります。

mysql> EXPLAIN SELECT count(*) FROM table_name WHERE text LIKE '%takurinton%' OR name LIKE '%takurinton%';
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | table_name | ALL  | NULL          | NULL | NULL    | NULL | 1480001 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

逆に、FULLTEXT による検索だと、インデックスが効いていて高速に検索をすることができていることがわかります。

1回しか見てなかったらそれは速いに決まってる...。

mysql> EXPLAIN SELECT count(*) FROM table_name WHERE MATCH(name ,text) AGAINST('+takurinton' IN BOOLEAN MODE);
+----+-------------+------------------+----------+---------------+------+---------+------+------+-------------+
| id | select_type | table            | type     | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------------+----------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | table_name | fulltext | idx           | idx  | 0       | NULL |    1 | Using where |
+----+-------------+------------------+----------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

まとめ

とても速いです。

検索対象のテーブルが関連を持っている時は検索用のテーブルを定義して、夜間にバッチなどを走らせて更新してってやるのがいいかなと思いました。（リアルタイム性が求められない場合に限る）

データベースの検索周り、自分で実装できたら楽しそうなので、今更ながら RDBMS を自作する機運かなと思ってきました。やるぞ〜( ^ω^ )