プロダクトに日本語の全文検索機能を加えたい、検索インデックスのリアルタイム更新もほしいというときの手軽な方法 #MySQL

プロダクトオーナーやユーザからの要望によって 検索機能 の必要性に迫られたとき、 ありふれた方法 でかつ速攻で実装する方法です

要件

日本語文章 などのデータに対して、全文検索できる
検索リクエストに対して、十分高速にレスポンスを返す
検索インデックスはリアルタイム更新され、実データと相違がでない
検索対象のデータが追加／更新／削除されたら検索結果にも即反映される

希望

できるだけ速く実装／提供したい
できるだけ運用コストも小さくしたい

おすすめな方法

もしもプロダクトがMySQL互換のデータベースを使っていたら、要件を満たして 明日にも提供できる かもしれません（気持ちです、フロント周りの実装など事情によって当然コストが変わります :P）
ごく平凡な知識で実現できるという点で、この方法の利点は大きいと思います

MySQL ( 5.7 以上 ) の全文検索インデックスを作成し、全文検索関数で問い合わせる

MySQL FULLTEXTインデックス：https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html
MySQL 全文検索：https://dev.mysql.com/doc/refman/5.6/ja/fulltext-search.html

SQL で like を使って問い合わせることはあまりに一般的だと思いますが、実装コストはこれと同程度です

日本語文章データが格納されたテーブル／カラムに対して N-Gram の検索インデックスを生成して追加し、それに対して検索文字列をマッチさせるという動きをするため、当然 like での部分一致より 断然高速 です

手順 1. FULLTEXT INDEX の追加

すでに運用しているテーブルに Index 追加するのが楽ですが、検索対象データが複数テーブルに分散している場合などでは、全文検索用に新規テーブルを作成するとよいでしょう

例として、新規に全文検索用のテーブルを作成してみます
※ N-gram はデフォルトで 2-gram (bigram) が適用されます
※ 文字コードは utf8(utf8mb4) を指定します（日本語を扱っている時点で utf8 を使っていると思います）

sample_create_table.sql

mysql> USE test;

mysql> CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT,
      FULLTEXT (title,body) WITH PARSER ngram
    ) ENGINE=InnoDB CHARACTER SET utf8mb4;

mysql> SET NAMES utf8mb4;

Index のみ追加する場合は、既存のテーブルに Alter 文などで Index を追加します

alter_table_add_index.sql

CREATE TABLE articles (
      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
      title VARCHAR(200),
      body TEXT
     ) ENGINE=InnoDB CHARACTER SET utf8;

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;

# Or:

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;

手順 2. 検索対象データのロード

MySQL の INSERT 文でデータを流し込みます
（※手順1. で既存テーブルに Alter Add Index した場合は、すでにデータはインデクシングされています）

insert_into_table.sql

INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...'),
    ('日本語の全文検索機能','プロダクトオーナー や ユーザからの要望によって ...'),
    ('リアルタイム更新','検索機能 の必要性に迫られたとき、ありふれた方法で ...'),
    ('数据库管理','在本教程中我将向你展示如何管理数据库'),
    ('数据库应用开发','学习开发数据库应用程序');

mysql> SET GLOBAL innodb_ft_aux_table="test/articles";
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;

既存のデータベースにすべての検索対象データが入っていれば、それらのデータのSELECT結果を検索用テーブルにINSERTします
（SELECT文は自由にかけます、複数テーブルに分散している場合には JOIN で繋ぐなどした結果を検索用テーブルに INSERT するとよいでしょう）

insert_into_as_select.sql


INSERT INTO articles (title,body)
  SELECT title, body FROM articles_source

手順 3. 全文検索インデックスに対して、任意の検索文字列で検索実行する

SQL の全文検索関数 MATCH() AGAINST() を使って、検索文字列を問い合わせます
https://dev.mysql.com/doc/refman/5.6/ja/fulltext-natural-language.html

select_natural_language_mode.sql


mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('検索' IN NATURAL LANGUAGE MODE);
+----+--------------------+---------------------------------------------+
| id | title              | body                                        |
+----+--------------------+---------------------------------------------+
|  7 | 日本語の全文検索機能 | プロダクトオーナー や ユーザからの要望に ...    |
|  8 | リアルタイム更新     | 検索機能 の必要性に迫られたとき、ありふれた ... |
+----+--------------------+---------------------------------------------+

検索文字列に対して、対象レコードがどのくらい関連しているかを示すスコアを取得することもできます
SELECT 句に MATCH() AGAINST() を加えて出力させると、関連性スコアを数値で受け取れます

select_score.sql


mysql> SELECT id, body, 
      MATCH (title,body) AGAINST ('全文検索' IN NATURAL LANGUAGE MODE) AS score
    FROM articles 
    WHERE 
      MATCH (title,body) AGAINST ('全文検索' IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  7 | プロダクトオーナー や ユーザからの ... | 1.5219271183014 |
|  8 | 検索機能 の必要性に迫られたとき、 ...  | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

全文検索の実行モードには2種類あり、 BOOLEAN MODE で実行することもできます
（検索文字列を論理式に整形して、検索実行させる方法です）
https://dev.mysql.com/doc/refman/5.6/ja/fulltext-boolean.html

select_boolean_mode.sql


mysql> SELECT * FROM articles WHERE MATCH (title,body)
    AGAINST ('+検索 -プロダクトオーナー' IN BOOLEAN MODE);
+----+-----------------+---------------------------------------------+
| id | title           | body                                        |
+----+-----------------+---------------------------------------------+
|  8 | リアルタイム更新 | 検索機能 の必要性に迫られたとき、ありふれた ... |
+----+-----------------+---------------------------------------------+

基本的には BOOLEAN MODE のほうが挙動を制御しやすい ため、検索結果もイメージに近いものになります
検索文字列を論理式に仕立てる手間がかかりますが、検索精度をとるなら BOOLEAN MODE で実装すると良いと思います

ここまでで、検索インデックスの作成と問い合わせが実現できました
実際の問い合わせ処理はプロダクトのコードに書き起こす必要がありますが、先ほども述べたように SELECT で like 問い合わせをするのと変わらない手間なので、すぐ実装できる ~~（経験豊富な皆さんなら瞬殺 :P）~~ はずです

手順 4. フロントエンドの実装とつなぎこみ

多様なフロントエンドがありうるので割愛しますが、必要な実装をして検索問い合わせできるようにします
一般的には、検索文字列のほか検索オプションやページングのパラメータなどを受け渡して、挙動を制御することになると思います

手順 5. 検索インデックスのリアルタイム更新

検索用に新規にテーブルを作成した場合（検索ソースとなるデータが入るテーブルと、検索インデックスを付与したテーブルが別の場合）、データソースの更新を自動的に検知して検索インデックスに反映させる必要があります
※手順1.で既存テーブルに FULLTEXT INDEX を追加しただけであれば、すでにリアルタイム更新される状態になっています

MySQLでは CREATE TRIGGER 文でトリガーが作成でき、あるテーブルへの更新を検知して、所定の処理を実行させることができます（ INSERT/UPDATE/DELETE ごとに作成します）
https://dev.mysql.com/doc/refman/5.6/ja/trigger-syntax.html

create_trigger.sql


delimiter |

CREATE TRIGGER insert_articles AFTER INSERT ON articles_source
  FOR EACH ROW
  BEGIN
    INSERT INTO articles SET title = NEW.title, body = NEW.body;
  END;

CREATE TRIGGER update_articles AFTER UPDATE ON articles_source
  FOR EACH ROW
  BEGIN
    UPDATE articles SET title = NEW.title WHERE id = NEW.id;
  END;

CREATE TRIGGER delete_articles AFTER DELETE ON articles_source
  FOR EACH ROW
  BEGIN
    DELETE FROM articles WHERE id = NEW.id;
  END;

|

delimiter ;

※データソースが複数テーブルにまたがる場合などでトリガーが複雑になる可能性がありますが、データソースが揃った時点で検索テーブルが更新されるよう、工夫してトリガーを設定します

試しに、データソースを追加したときに、検索用テーブルにも自動的に追加されることをみてみます

insert_into_source_table.sql


INSERT INTO articles_source (title,body) VALUES
    ('Python SQLAlchemy +','MySQL に構築した 全文検索インデックス に対して ...');

select_natural_language_mode.sql


mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('検索' IN NATURAL LANGUAGE MODE);
+----+---------------------+-------------------------------------------------+
| id | title               | body                                            |
+----+---------------------+-------------------------------------------------+
|  7 | 日本語の全文検索機能  | プロダクトオーナー や ユーザからの要望に ...       |
|  8 | リアルタイム更新      | 検索機能 の必要性に迫られたとき、ありふれた ...    |
| 11 | Python SQLAlchemy + | MySQL に構築した 全文検索インデックス に対して ... | <- 新レコード
+----+---------------------+-------------------------------------------------+

データソースに入れたレコードが、自動的に検索インデックスのある検索用テーブルにも反映されています

まとめ

これでプロダクトには日本語文章に対する検索機能が追加されました、
検索インデックスもリアルタイム更新されています

検索の実装というと Solr や Elasticsearch など検索エンジンの利用を考えがちですが、小さなプロダクトであればデータサイズは小さく、かつデータはすべてデータベース上にあるというケースは多いと思います．実装コストを抑え要件を満たす費用対効果の高い策として、MySQLの全文検索インデックスは有力な選択肢だと思います

プロダクトオーナーの方に連絡をとって、希望通り速攻で作りましたよ と教えてあげましょう！ :P

余談

ユーザからの入力文字列を扱うので、文字列の正規化やサニタイズは（検索に関わらず）必要です
すでに稼動しているプロダクトであれば実装があると思うので、共通の正規化処理をかませるとよいでしょう
もし対象のプロダクトを Python で実装していて、データとの接続には SQLAlchemy をつかってる！という奇遇な方は、ぜひ Python SQLAlchemy + MySQL で複数フィールドに対する全文検索：MATCH AGAINST（Boolean Mode）を実行するもあわせてご覧くださいmm

参考資料

MySQL FULLTEXTインデックス : https://dev.mysql.com/doc/refman/5.6/ja/innodb-fulltext-index.html
MySQL 全文検索 : https://dev.mysql.com/doc/refman/5.6/ja/fulltext-search.html
MySQL ngram Full-Text Parser : https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html
MySQL トリガー構文と例 : https://dev.mysql.com/doc/refman/5.6/ja/trigger-syntax.html
Python SQLAlchemy + MySQL で複数フィールドに対する全文検索：MATCH AGAINST（Boolean Mode）を実行する

プロダクトに日本語の全文検索機能を加えたい、検索インデックスのリアルタイム更新もほしい というときの手軽な方法

要件

希望