MySQLの全文検索機能を試す

前提

• MySQL徹底入門 第4版 4章の内容に基づきます
• 社内勉強会用の資料なので基本箇条書き、かつ口頭で補足する内容は省いています
• 勉強会を実施した際の講師からのコメントを都度書き足しています

全文検索とは

• 複数の文書群から特定の文字列を検索すること
• 「複数文書にまたがる」こと、「全文を対象とする」ことがポイント
  • https://ja.wikipedia.org/wiki/%E5%85%A8%E6%96%87%E6%A4%9C%E7%B4%A2

全文検索の技術

走査型（grep型）

• 文書の中身それ自体を愚直に検索する
• インデックスなど冗長なデータが不要で手軽に使える
• 検索対象の増加に比例して検索速度が低下するため、大量の文書の検索に向いていない（要するに遅い）

MySQLで走査型の検索を行う

• MySQL公式サンプルのworldデータベース¹で走査型検索を試した例

SELECT * FROM world.city WHERE name LIKE '%Lake%';

+------+----------------+-------------+----------+------------+
| ID   | Name           | CountryCode | District | Population |
+------+----------------+-------------+----------+------------+
| 3903 | Salt Lake City | USA         | Utah     |     181743 |
| 3938 | Lakewood       | USA         | Colorado |     144126 |
+------+----------------+-------------+----------+------------+
2 rows in set (0.00 sec)

索引型（インデックス）

• あらかじめ検索対象となる文書を走査して文章の索引（インデックス）を作成しておく
  • インデックスは特定のキーワードがどの文書にあるかという情報
  • 文書内の位置も含めて保持する場合もある（後述）
• インデックスを使うことで効率的に高速に検索が可能
• インデックス作成の負荷、ディスクやメモリなどのリソース消費がある

単に「全文検索」というと通常は索引型を指すので、この記事でも以降そのように呼称する。
また、これ以降は索引型の全文検索のみを扱う。

講師コメント

• 本の中から特定のキーワードを探す様子をイメージするとわかりやすい
  • 1ページ目から全部見ていって探すのが走査型
  • 索引を見て探すのが索引型

全文検索のために使用するミドルウェア

• 以下のようなものが有名
  • Groonga
  • Apache Solr
  • Elasticsearch
• 簡単なものであればMySQLでも可能
  • ストレージエンジンによるが、デフォルトのInnoDBが全文検索に対応していて通常はそれを使う
  • 上記ツールは画面や登録機能などもセットで提供されるが、MySQLの全文検索機能はコア機能だけ提供しているようなイメージ

この記事ではMySQLのInnoDBの全文検索について扱う。

講師コメント

• Groongaは純粋にデータストアを提供するカラム指向のエンジン
• SolrやElasticsearch（というかElastic Stack）は画面などの付属機能も提供する

インデックスについて

トークン分割

• インデックスによる検索はトークン単位で行われるため、文書をトークンで分割する必要がある
  • トークンとは、意味を成す最小単位である字句のこと²
• トークンで分割するルールが必要
  • 英語などでは単語を空白で区切って書くため、単純に空白で分割して単語をトークンとするのが普通
  • 日本語など単語を空白で区切らない言語ではそう単純にいかないため、複数のアルゴリズムがある

英文のトークン分割の例

MySQL is a database management system.

• 以下のように分割される
  • MySQL
  • is
  • a
  • database
  • management
  • system
  • .

日本語の文書をトークンで分割する主なアルゴリズム

N-gram

• 文字列の意味は考慮せずに、単純に文字列を連続したN個の文字で分割する
• N = 1の場合はユニグラム、2の場合はバイグラム、3の場合はトライグラムと呼ぶ
• MySQLでは特に追加設定なしでN-gramパーサーを使える（デフォルトではバイグラム）

バイグラムによるトークン分割の例

東京都は、日本の首都である。

以下のように分割される。

• 東京
• 京都
• 都は
• は、
• 、日
• 日本
• 本の
• の首
• 首都
• 都で
• であ
• ある
• る。

形態素解析

• 日本語の辞書をベースにキーワードを抽出する
• 形態素解析パーサは様々な種類があるが、MySQLではMeCabを使用可能

形態素解析によるトークン分割の例

東京都は、日本の首都である。

以下のように分割される。（パーサによって結果が異なる可能性があるが、以下はオンラインで試せるkuromojiを使用）³

• 東京
• 都
• は
• 、
• 日本
• の
• 首都
• で
• ある
• 。

講師コメント

• 形態素解析パーサーによって結果はかなり違う
• kuromojiは非常に優秀で、Elasticsearchなどでも使うことが多い

N-gramのメリット、デメリット

• 🙆‍♂️検索漏れが生じにくい
• 🙅逆に検索ノイズが生じやすい
• 🙅インデックスサイズが比較的大きい

形態素解析のメリット、デメリット

• 🙆‍♂️検索ノイズが生じにくい
• 🙆‍♂️インデックスサイズが比較的小さい
• 🙅検索漏れが生じやすい（辞書に載っていない言葉、複数解釈が可能な文、壊れている文など）

インデックスの構造

• 転置インデックスと呼ばれる構造になっている

転置インデックス

• MySQLでは単語単位転置インデックスが使用される
• テーブル形式のデータで、単語、含まれる文書の番号、文書内の位置といった情報がカラムとして表現される
• 単語の出現ごとにレコードが作られる
• 文書ごとにレコードを作って単語ごとにカラムを作ると非常に使いづらいので逆になっている（転置）

ストップワード

• インデックスの作成対象としない単語
• 一般的すぎておそらく検索に使いたいと思わない「a」「the」などの単語をストップワードとすることで、インデックスサイズの肥大化を抑制する
• MySQLではデフォルトで設定されているストップワードがあり、それ以外にも追加可能⁴

更新コメント

• ストップワードはインデックスサイズの抑制という目的の他、検索ノイズの除去という目的もある

トークンサイズ

• トークンの最小文字数や最大文字数を設定可能
• それに合致しないトークンはインデックス化されない

MySQLにおける全文検索の使用方法

• 文書を管理するテーブルを作る際に特別な定義を行えば、INSERT時にMySQLがインデックスを自動的に作成してくれる（インデックスを管理するテーブルも自動作成される）
  • FTS_DOC_ID列を定義する
  • FULLTEXT INDEXインデックスを定義する
• SELECT時、特別な構文を使えばインデックスを使った検索ができる

全文検索の実行例

• テーブル作成

create table articles (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT INDEX idx_fulltext_title_body (title, body)
);

• データのINSERT

INSERT INTO articles (title, body)  VALUES
  ('Windows Functions in MySQL 8.0', 'We introduced Windows Functions in the MySQL ...'),
  ('New JSON Functions in MySQL 8.0', 'We added new JSON Functions in the MySQL ...'),
  ('New Spatial Functions in MySQL', 'We added new Spatial Functions in the MySQL ...'),
  ('MySQL Document Store', 'MySQL Document Store can easily handle JSON documents ...');

• この際にインデックスが自動作成される

インデックス定義を確認する

SELECT table_id, name, space FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE 'test/articles';

+----------+---------------+-------+
| table_id | name          | space |
+----------+---------------+-------+
|     1068 | test/articles |     3 |
+----------+---------------+-------+

SELECT index_id, name, table_id, space FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE table_id = 1068;

+----------+-------------------------+----------+-------+
| index_id | name                    | table_id | space |
+----------+-------------------------+----------+-------+
|      158 | PRIMARY                 |     1068 |     3 |
|      186 | idx_fulltext_title_body |     1068 |     3 |
|      187 | FTS_DOC_ID_INDEX        |     1068 |     3 |
+----------+-------------------------+----------+-------+

SHOW CREATE TABLE articles\G

*************************** 1. row ***************************
       Table: articles
Create Table: CREATE TABLE `articles` (
  `FTS_DOC_ID` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `body` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`FTS_DOC_ID`),
  FULLTEXT KEY `idx_fulltext_title_body` (`title`,`body`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

• FTS_DOC_ID_INDEXが内部的には作成されていることがわかる

講師コメント

• 暗黙的に定義されるインデックスは全文検索に限らず他にもある（外部キーのインデックスなど）

作成された転置インデックスの内容を確認する

SET GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE articles;
SET GLOBAL innodb_ft_aux_table = 'test/articles';

SELECT word, doc_count, doc_id, position FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;

+----------+-----------+--------+----------+
| word     | doc_count | doc_id | position |
+----------+-----------+--------+----------+
| added    |         2 |      2 |       35 |
| added    |         2 |      3 |       34 |
| can      |         1 |      4 |       42 |
| document |         1 |      4 |        6 |
| document |         1 |      4 |       21 |
+----------+-----------+--------+----------+

インデックスを使って検索を実行する（全文検索）

全文検索のモード

• NATURAL LANGUAGE MODE（自然言語検索）
  • デフォルトのモード
  • 指定したキーワードが含まれる文書を検索できる（OR検索のみ）
  • 検索結果は指定したキーワードとより関連性の高い文書が上位に表示されるように自動的にソートされている
• BOOLEAN MODE（ブール検索）
  • AND検索、NOT検索、グルーピング、ワイルドカードなど、OR検索以外にも柔軟な検索条件が指定できる
  • よく使うため、以下ではこれについて説明する
• NATURAL LANGUAGE MODE WITH QUERY EXPANSION（クエリー拡張検索）
  • 内部的に検索を2回実行し、1回目の検索結果と関連の深い文書を検索できる

ブール検索の例

SELECT FTS_DOC_ID as ID, title, body FROM articles WHERE MATCH (title, body) AGAINST ('+JSON' IN BOOLEAN MODE);

+----+---------------------------------+-----------------------------------------------------------+
| ID | title                           | body                                                      |
+----+---------------------------------+-----------------------------------------------------------+
|  2 | New JSON Functions in MySQL 8.0 | We added new JSON Functions in the MySQL ...              |
|  4 | MySQL Document Store            | MySQL Document Store can easily handle JSON documents ... |
+----+---------------------------------+-----------------------------------------------------------+

• MATCH句を指定する
  • FULLTEXT INDEXを定義したカラムをすべて指定
• AGAINST句を指定
  • キーワード、モードなどを指定

演算子

• 演算子なし（OR）
• +（AND）
• -（NOT）
• ()（グループ化）
• >（関連性ランキングの貢献度をプラス）
• <（関連性ランキングへの貢献度をマイナス）
• ~（関連性ランキングへの貢献度をマイナス）
  • ほぼ除外みたいなニュアンスらしい
• *（ワイルドカード）
  • 前方一致のみ可？
• ""（フレーズ検索）
• @distance（単語間の距離を指定）
  • @7 のように数字を指定

講師コメント

• 全文検索ではトークンにスコアをつけられる
• スコアに応じて検索結果の表示順序が変わる

N-gramパーサーの使用方法

• パーサーはデフォルトで組み込まれているため、使用するために事前に特別な設定は不要
• フルテキストインデックス作成時に WITH PARSER ngram を指定する

テーブルの作成

create table articles_ngram (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT INDEX idx_fulltext_title_body (title, body) WITH PARSER ngram
);

データの登録

• デフォルトではバイグラム
• ngram_token_size で変更可能（1にすればユニグラムになる、など）

INSERT INTO articles_ngram (title, body) VALUES
('A', '東京都は、日本の首都であり ...'),
('B', '京都府は、日本の近畿地方に位置する ...');

作成された転置インデックスの内容を確認する

SET GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE articles_ngram;
SET GLOBAL innodb_ft_aux_table = 'test/articles_ngram';

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE WHERE DOC_ID = 1 ORDER BY POSITION;

+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 東京   |            1 |           1 |         1 |      1 |        2 |
| 京都   |            1 |           2 |         2 |      1 |        5 |
| 都は   |            1 |           1 |         1 |      1 |        8 |
| は、   |            1 |           2 |         2 |      1 |       11 |
| 、日   |            1 |           2 |         2 |      1 |       14 |
| 日本   |            1 |           2 |         2 |      1 |       17 |
| 本の   |            1 |           2 |         2 |      1 |       20 |
| の首   |            1 |           1 |         1 |      1 |       23 |
| 首都   |            1 |           1 |         1 |      1 |       26 |
| 都で   |            1 |           1 |         1 |      1 |       29 |
| であ   |            1 |           1 |         1 |      1 |       32 |
| あり   |            1 |           1 |         1 |      1 |       35 |
+--------+--------------+-------------+-----------+--------+----------+

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE WHERE DOC_ID = 2 ORDER BY POSITION;

+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 京都   |            1 |           2 |         2 |      2 |        2 |
| 都府   |            2 |           2 |         1 |      2 |        5 |
| 府は   |            2 |           2 |         1 |      2 |        8 |
| は、   |            1 |           2 |         2 |      2 |       11 |
| 、日   |            1 |           2 |         2 |      2 |       14 |
| 日本   |            1 |           2 |         2 |      2 |       17 |
| 本の   |            1 |           2 |         2 |      2 |       20 |
| の近   |            2 |           2 |         1 |      2 |       23 |
| 近畿   |            2 |           2 |         1 |      2 |       26 |
| 畿地   |            2 |           2 |         1 |      2 |       29 |
| 地方   |            2 |           2 |         1 |      2 |       32 |
| 方に   |            2 |           2 |         1 |      2 |       35 |
| に位   |            2 |           2 |         1 |      2 |       38 |
| 位置   |            2 |           2 |         1 |      2 |       41 |
| 置す   |            2 |           2 |         1 |      2 |       44 |
| する   |            2 |           2 |         1 |      2 |       47 |
+--------+--------------+-------------+-----------+--------+----------+

検索実行

SELECT FTS_DOC_ID as ID, title, body FROM articles_ngram WHERE MATCH (title, body) AGAINST ('+東京' IN BOOLEAN MODE);

+----+-------+---------------------------------------------+
| ID | title | body                                        |
+----+-------+---------------------------------------------+
|  1 | A     | 東京都は、日本の首都であり ...              |
+----+-------+---------------------------------------------+

SELECT FTS_DOC_ID as ID, title, body FROM articles_ngram WHERE MATCH (title, body) AGAINST ('+京都' IN BOOLEAN MODE);

+----+-------+---------------------------------------------------------+
| ID | title | body                                                    |
+----+-------+---------------------------------------------------------+
|  1 | A     | 東京都は、日本の首都であり ...                          |
|  2 | B     | 京都府は、日本の近畿地方に位置する ...                  |
+----+-------+---------------------------------------------------------+

SELECT FTS_DOC_ID as ID, title, body FROM articles_ngram WHERE MATCH (title, body) AGAINST ('+京都 -東京' IN BOOLEAN MODE);

+----+-------+---------------------------------------------------------+
| ID | title | body                                                    |
+----+-------+---------------------------------------------------------+
|  2 | B     | 京都府は、日本の近畿地方に位置する ...                  |
+----+-------+---------------------------------------------------------+

MeCabパーサーの使用方法

事前準備

• MeCabパーサーはデフォルトで組み込まれていないため、使用するには事前準備が必要

mecabrcファイルの編集

• $MYSQL_HOME/lib/mecab/etc/mecabrcファイルがある場合、それを編集する
• 上記パスにない場合、システムにMeCabがあれば /etc/mecabrcにあると思われる

mecabrc

dicdir=/var/lib/mecab/dic/ipadic-utf8

• 設定値は実際にipadic-utf8があるパスを設定する
• MySQLを直接インストールした場合は$MYSQL_HOME/lib/mecab/dic/ipadic_utf-8にあると思われる

my.cnfファイルにシステム変数を設定する

my.cnf

[mysqld]
innodb_ft_min_token_size=2
loose-mecab-rc-file=/etc/mecabrc

• innodb_ft_min_token_size
  • インデックスが付けられる単語の最小長の設定
  • デフォルト3だが、MeCab使用時は1か2にするのが推奨らしい
• loose-mecab-rc-file
  • 前節で修正したMeCabの設定ファイルへのパス
  • looseはMySQLが認識できない設定があってもMySQL起動がエラーとならないようにするための接頭辞で、MeCabプラグインをインストールする前に起動するために必要

Docker Composeを使う場合

• 上記ファイルをホストで用意してvolumesで指定すると楽かもしれない

docker-compose.yml

version: '3'
services:
  # MySQL
  db:
    platform: linux/x86_64
    image: mysql:8.0.29
    ports:
      - "3306:3306"
    container_name: mysql_host
    environment:
      MYSQL_ROOT_PASSWORD: mysql
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
      - ./db/data:/var/lib/mysql
      - ./db/my.cnf:/etc/mysql/conf.d/my.cnf
      - ./db/mecabrc:/etc/mecabrc
      - ./db/sql:/docker-entrypoint-initdb.d

MySQLの起動

• ここでMySQLを起動する（起動済だったら再起動）

MeCabパーサープラグインのインストール

INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';

MeCabパーサープラグインが有効か確認

SHOW PLUGINS;

+---------------------------------+----------+--------------------+-------------------+---------+
| Name                            | Status   | Type               | Library           | License |
+---------------------------------+----------+--------------------+-------------------+---------+
<<中略>>
| ngram                           | ACTIVE   | FTPARSER           | NULL              | GPL     |
| mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL              | GPL     |
| mysqlx                          | ACTIVE   | DAEMON             | NULL              | GPL     |
| mecab                           | ACTIVE   | FTPARSER           | libpluginmecab.so | GPL     |
+---------------------------------+----------+--------------------+-------------------+---------+

MeCabパーサーの使用

テーブル作成

• WITH PARSER mecabを指定する

create table articles_mecab (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT INDEX idx_mecab_fulltext_title_body (title, body) WITH PARSER mecab
);

データ登録

INSERT INTO articles_mecab (title, body) VALUES
('A', '東京都は、日本の首都であり ...'),
('B', '京都府は、日本の近畿地方に位置する ...');

作成された転置インデックスの内容を確認する

SET GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE articles_mecab;
SET GLOBAL innodb_ft_aux_table = 'test/articles_mecab';

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE WHERE DOC_ID = 1 ORDER BY POSITION;

+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 東京   |            1 |           1 |         1 |      1 |        2 |
| 日本   |            1 |           2 |         2 |      1 |       17 |
| 首都   |            1 |           1 |         1 |      1 |       26 |
| あり   |            1 |           1 |         1 |      1 |       35 |
| ...    |            1 |           2 |         2 |      1 |       41 |
+--------+--------------+-------------+-----------+--------+----------+

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE WHERE DOC_ID = 2 ORDER BY POSITION;

+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 京都   |            2 |           2 |         1 |      2 |        2 |
| 日本   |            1 |           2 |         2 |      2 |       17 |
| 近畿   |            2 |           2 |         1 |      2 |       26 |
| 地方   |            2 |           2 |         1 |      2 |       32 |
| 位置   |            2 |           2 |         1 |      2 |       41 |
| する   |            2 |           2 |         1 |      2 |       47 |
| ...    |            1 |           2 |         2 |      2 |       53 |
+--------+--------------+-------------+-----------+--------+----------+

検索実行

SELECT FTS_DOC_ID as ID, title, body FROM articles_mecab WHERE MATCH (title, body) AGAINST ('+東京' IN BOOLEAN MODE);

+----+-------+---------------------------------------------+
| ID | title | body                                        |
+----+-------+---------------------------------------------+
|  1 | A     | 東京都は、日本の首都であり ...              |
+----+-------+---------------------------------------------+

SELECT FTS_DOC_ID as ID, title, body FROM articles_mecab WHERE MATCH (title, body) AGAINST ('+京都' IN BOOLEAN MODE);

+----+-------+---------------------------------------------------------+
| ID | title | body                                                    |
+----+-------+---------------------------------------------------------+
|  2 | B     | 京都府は、日本の近畿地方に位置する ...                  |
+----+-------+---------------------------------------------------------+

全文検索使用時の注意事項

更新処理のパフォーマンスに問題がないか

• テーブルデータの更新時にインデックスのメンテナンス処理がバックグラウンドで実行される
• 頻繁に更新処理が発生するテーブルは向いていない
• 別途全文検索用のテーブルを作成し、バッチ処理でデータを移して全文検索するという手もある

ディスク容量/メモリ容量に余裕があるか

• フルテキストインデックスでディスクを多く消費する
  • データ削除後も即座にはサイズが縮小されない
• キャッシュによりメモリ使用量も増える
  • テーブル単位のキャッシュサイズ: innodb_ft_cache_size （デフォルト8MB）
  • MySQLサーバ全体でのキャッシュサイズ: innodb_ft_total_cache_size （デフォルト640MB）

関連するシステム変数を変更した場合の対応

• 以下のシステム変数を変更した場合はフルテキストインデックスを再構築する必要がある
  • innodb_ft_min_token_size
  • innodb_ft_max_token_size
  • innodb_ft_server_stopword_table
  • innodb_ft_user_stopword_table
  • innodb_ft_enable_stopword
  • ngram_token_size
• 再構築はDROP INDEXで削除してからADD INDEXする

フルテキストインデックスの最適化

• レコードをDELETEしてもフルテキストインデックスのサイズは即座には縮小されない
  • 削除操作を記録だけして実際の削除は遅延させるため
• すぐに反映させたい場合の対応

SET GLOBAL innodb_optimize_fulltext_only=ON;

OPTIMIZE TABLE articles;

大量データで試してみた

• 以下の「jawiki-latest-pages-articles.xml.bz2」を展開、パースしてINSERTした（Wikipedia記事データ）
  • https://dumps.wikimedia.org/jawiki/latest/
• タイトル、本文を抽出
• 件数は274万9918件になった

テーブル定義

インデックスなし

create table articles (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(2000),
  body LONGTEXT
);

N-gram

create table articles (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(2000),
  body LONGTEXT,
  FULLTEXT INDEX idx_fulltext_title_body (title, body) WITH PARSER ngram
);

MeCab

create table articles (
  FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(2000),
  body LONGTEXT,
  FULLTEXT INDEX idx_fulltext_title_body (title, body) WITH PARSER mecab
);

データ投入にかかった時間

• 登録のために書いたコードがあまり賢くないのと、データ投入中に他の作業もやったりしていたのであくまでも参考値
• ユニグラムとバイグラム、MeCabの最小トークン1と2は共存できないためMySQLサーバは2つ立てて確認

	処理時間
インデックスなし	約1時間22分
N-gram（ユニグラム）	約3時間12分
N-gram（バイグラム）	約8時間9分
MeCab（最小トークン1）	約9時間59分
MeCab（最小トークン2）	約7時間54分

インデックスのサイズ

• 上記はそれぞれ別のデータベース（スキーマ）に登録し、それ以外のテーブルもないため、スキーマの構成ファイルは全部今回登録したデータのはず

インデックスなし

# ls -lh

total 17G
-rw-r----- 1 999 999 17G Aug 13 12:45 articles.ibd

• インデックスはないので実データだけがあり、それがおよそ17GB

N-gram（ユニグラム）

# ls -lh

total 31G
-rw-r----- 1 999 999  17G Aug 16 13:25 articles.ibd
-rw-r----- 1 999 999 7.4G Aug 16 13:26 fts_0000000000000443_00000000000000c0_index_1.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_00000000000000c0_index_2.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_00000000000000c0_index_3.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_00000000000000c0_index_4.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_00000000000000c0_index_5.ibd
-rw-r----- 1 999 999 6.4G Aug 16 13:26 fts_0000000000000443_00000000000000c0_index_6.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_being_deleted.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_being_deleted_cache.ibd
-rw-r----- 1 999 999 112K Aug 16 13:26 fts_0000000000000443_config.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_deleted.ibd
-rw-r----- 1 999 999 112K Aug 16 10:07 fts_0000000000000443_deleted_cache.ibd

• articles.ibd以外は全部インデックスの構成ファイル
• 約13.8GB

N-gram（バイグラム）

# ls -lh

total 38G
-rw-r----- 1 999 999  17G Aug 15 19:23 articles.ibd
-rw-r----- 1 999 999  12G Aug 15 19:31 fts_0000000000000513_00000000000001b4_index_1.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_00000000000001b4_index_2.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_00000000000001b4_index_3.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_00000000000001b4_index_4.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_00000000000001b4_index_5.ibd
-rw-r----- 1 999 999 9.6G Aug 15 19:34 fts_0000000000000513_00000000000001b4_index_6.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_being_deleted.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_being_deleted_cache.ibd
-rw-r----- 1 999 999 112K Aug 15 19:34 fts_0000000000000513_config.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_deleted.ibd
-rw-r----- 1 999 999 112K Aug 15 11:12 fts_0000000000000513_deleted_cache.ibd

• articles.ibd以外は全部インデックスの構成ファイル
• 約21.6GB

MeCab（最小トークン1）

# ls -lh

total 33G
-rw-r----- 1 999 999  17G Aug 20 19:29 articles.ibd
-rw-r----- 1 999 999  12G Aug 20 19:39 fts_0000000000000467_00000000000000ea_index_1.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_00000000000000ea_index_2.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_00000000000000ea_index_3.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_00000000000000ea_index_4.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_00000000000000ea_index_5.ibd
-rw-r----- 1 999 999 4.6G Aug 20 19:41 fts_0000000000000467_00000000000000ea_index_6.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_being_deleted.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_being_deleted_cache.ibd
-rw-r----- 1 999 999 112K Aug 20 19:41 fts_0000000000000467_config.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_deleted.ibd
-rw-r----- 1 999 999 112K Aug 16 23:45 fts_0000000000000467_deleted_cache.ibd

• articles.ibd以外は全部インデックスの構成ファイル
• 約16.6GB

MeCab（最小トークン2）

# ls -lh

total 30G
-rw-r----- 1 999 999  17G Aug 12 20:16 articles.ibd
-rw-r----- 1 999 999 9.2G Aug 12 20:22 fts_0000000000000506_00000000000001a5_index_1.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_00000000000001a5_index_2.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_00000000000001a5_index_3.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_00000000000001a5_index_4.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_00000000000001a5_index_5.ibd
-rw-r----- 1 999 999 3.6G Aug 12 20:21 fts_0000000000000506_00000000000001a5_index_6.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_being_deleted.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_being_deleted_cache.ibd
-rw-r----- 1 999 999 112K Aug 12 20:22 fts_0000000000000506_config.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_deleted.ibd
-rw-r----- 1 999 999 112K Aug 12 12:27 fts_0000000000000506_deleted_cache.ibd

• articles.ibd以外は全部インデックスの構成ファイル
• 約12.8GB

検索にかかった時間

• 条件によっては ERROR 188 (HY000): FTS query exceeds result cache limit というエラーが出てしまう
• 対処のために以下を設定して実行している（ただし、それでもエラーになるケースもあり…）

SET GLOBAL innodb_ft_result_cache_limit = 4000000000;

• キャッシュによる影響など考えていないのであくまでも参考値

インデックスなし

SELECT count(1) FROM articles WHERE title LIKE '%東%' OR body LIKE '%東%';

+----------+
| count(1) |
+----------+
|   700705 |
+----------+
1 row in set (1 min 3.66 sec)

SELECT count(1) FROM articles WHERE title LIKE '%東京%' OR body LIKE '%東京%';

+----------+
| count(1) |
+----------+
|   354881 |
+----------+
1 row in set (1 min 18.73 sec)

SELECT count(1) FROM articles WHERE title LIKE '%北海道%' OR body LIKE '%北海道%';

+----------+
| count(1) |
+----------+
|    90928 |
+----------+
1 row in set (1 min 40.12 sec)

SELECT count(1) FROM articles WHERE title LIKE '%スリジャヤワルダナプラコッテ%' OR body LIKE '%スリジャヤワルダナプラコッテ%';

+----------+
| count(1) |
+----------+
|       52 |
+----------+
1 row in set (1 min 23.38 sec)

SELECT count(1) FROM articles WHERE title LIKE '%タウマタファカタンギハンガコアウアウオタマテアポカイフェヌアキタナタフ%' OR body LIKE '%タウマタファカタンギハンガコアウアウオタマテアポカイフェヌアキタナタフ%';

+----------+
| count(1) |
+----------+
|       19 |
+----------+
1 row in set (1 min 35.25 sec)

• 全体的に安定して遅く、キーワードが長くなっても大きな性能変動はない

N-gram（ユニグラム）

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   700705 |
+----------+
1 row in set (0.72 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東京' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   354927 |
+----------+
1 row in set (2 min 48.10 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+北海道' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|    90928 |
+----------+
1 row in set (3 min 38.40 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+スリジャヤワルダナプラコッテ' IN BOOLEAN MODE);

ERROR 188 (HY000): FTS query exceeds result cache limit

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+タウマタファカタンギハンガコアウアウオタマテアポカイフェヌアキタナタフ' IN BOOLEAN MODE);

ERROR 188 (HY000): FTS query exceeds result cache limit

• 1文字ずつトークン分割されるので1文字の場合は速い
• 2文字になった途端非常に遅くなってしまった
• さらに増えると遅い以前に検索結果が得られなかった

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+"東京"' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   354927 |
+----------+
1 row in set (2 min 25.85 sec)

• もしかしたらフレーズ検索でないと正しい結果が得られない？

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+(東 京)' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   758447 |
+----------+
1 row in set (1.33 sec)

• 一文字ずつ指定すれば速い
  • しかし、「東」と「京」を両方含むってだけの意味なのでおそらく想定通りの結果が得られてない

MeCab（最小インデックス1）

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   166244 |
+----------+
1 row in set (5.83 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東京' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   313331 |
+----------+
1 row in set (1.33 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+北海道' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|    82104 |
+----------+
1 row in set (0.16 sec)

 SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+スリジャヤワルダナプラコッテ' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|       51 |
+----------+
1 row in set (0.05 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+タウマタファカタンギハンガコアウアウオタマテアポカイフェヌアキタナタフ' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|       18 |
+----------+
1 row in set (11.05 sec)

• 少なくとも今回選定したキーワードについては、いずれも現実的な時間で結果が得られた

N-gram（バイグラム）

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|      408 |
+----------+
1 row in set (0.00 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東京' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   354881 |
+----------+
1 row in set (1.31 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+北海道' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|    90928 |
+----------+
1 row in set (47.93 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+スリジャヤワルダナプラコッテ' IN BOOLEAN MODE);

ERROR 188 (HY000): FTS query exceeds result cache limit

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+タウマタファカタンギハンガコアウアウオタマテアポカイフェヌアキタナタフ' IN BOOLEAN MODE);

ERROR 188 (HY000): FTS query exceeds result cache limit

• 2文字ずつトークン化されるので2文字だと速いが文字数が増えると遅くなり、多すぎると結果が得られない
• 1文字の場合は一瞬だがたぶん正しい結果が得られていない

MeCab（最小インデックス2）

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   166244 |
+----------+
1 row in set (0.63 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+東京' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|   313331 |
+----------+
1 row in set (0.76 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+北海道' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|    82104 |
+----------+
1 row in set (0.10 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+スリジャヤワルダナプラコッテ' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|       51 |
+----------+
1 row in set (0.03 sec)

SELECT count(1) FROM articles WHERE MATCH (title, body) AGAINST ('+タウマタファカタンギハンガコアウアウオタマテアポカイフェヌアキタナタフ' IN BOOLEAN MODE);

+----------+
| count(1) |
+----------+
|       18 |
+----------+
1 row in set (10.47 sec)

• 1文字の場合はおそらく正しい結果が得られていないが、それ以外は最小インデックス2と特に変わらない

辞書に載ってなさそうな文字列での検索結果について

• 上記の結果だと全体的にN-gramよりMeCabのほうが良い結果が出ている
  • MeCabのほうが高度なことをやっているのでそりゃそうかもしれないが
• ただしMeCabは辞書を使ってトークン分割するので、辞書に載ってないようなワードだと簡単に結果が狂うという問題がある

N-gram（バイグラム）での検索

SELECT FTS_DOC_ID as ID, title FROM articles WHERE MATCH (title, body) AGAINST ('+糸色望' IN BOOLEAN MODE) LIMIT 5;

+---------+-----------------------------------------+
| ID      | title                                   |
+---------+-----------------------------------------+
|  649822 | さよなら絶望先生の登場人物              |
|   91785 | 神谷浩史                                |
|  825096 | さよなら絶望放送                        |
|  176879 | さよなら絶望先生                        |
| 1250443 | さよなら絶望先生 (アニメ)               |
+---------+-----------------------------------------+
5 rows in set (0.02 sec)

MeCab（最小インデックス2）での検索

SELECT FTS_DOC_ID as ID, title FROM articles WHERE MATCH (title, body) AGAINST ('+糸色望' IN BOOLEAN MODE) LIMIT 5;

Empty set (0.00 sec)

• そのようなワードでは検索しないので問題ないと割り切れるケースもあるかもしれないが
• なんとかしたい場合は、検索されそうな単語も含んだ辞書を使用するとか
  • 調べられていないが、探せば辞書ファイルはたくさんありそう

感想（MySQLというより全文検索全般に対して）

• 実際に使い物になるようにするのは思ったよりも難しそう
  • インデックスサイズ、更新性能、検索性能、検索結果など多くのファクターがある
  • N-gramはインデックスサイズが大きくて遅いし、形態素解析は検索漏れの懸念があるなど厳しいトレードオフがある
  • 正しい検索結果が得られているかという検証が今回はできなかったが、難しそうな感触を得た
  • 要件に合う落とし所を見つけるには、できるだけ実際のデータを使って様々な検証をする必要がありそう
  • というか日本語を大量に扱う場合にN-gramが実用的なケースがあるとは思えなかった
• データ量が少なければ、全文検索機能を採用しないのも選択の一つ
  • 性能が良くも悪くも安定しており、字面通りの検索結果が得られる
  • とはいえ、データ量が非常に多い場合はインデックスを採用せざるを得ない
  • 今回試したのはたかだか275万件弱程度なのでLIKEでもそこまで遅くなかったが、億単位などのデータ量だと全然話にならない性能になると思われる

講師コメント

• 1分とか1秒とかでもRDBMSとしては非常に遅いので、よほどアクセスが少ないなど限られたケースでなければ通常許容されないのではないか
• 全文検索については実際、パーサーの設定をしただけでそのままリリースするということは通常ない
  • 実際のデータを使って想定通りの検索結果が出るように検証、辞書のチューニングなどを行う
  • 辞書についてはファッション関連のサイトならファッション用語など、サービス内容に応じた単語を辞書に登録していくなどの作業が必要
  • リリース後も流行語の移り変わりに応じた辞書のアップデートなどの運用をしていく必要がある
• N-gramを採用したケースは見たことがない
  • 辞書のアップデートといったことができないので、最初から運用を放棄しているような印象
• データベースで日本語を扱うのは大変で、関わっているエンジニアは苦労しているが、その分日本のエンジニアはマルチバイト文字の扱いについて外国のエンジニアに比べて一日の長があるといえる
• 大規模な全文検索はElasticsearchなどを使う事が多く、MySQLの全文検索機能はもっと小規模なものに使われることが多い（使われること自体はけっこうある）

1. https://dev.mysql.com/doc/index-other.html ↩

2. https://ja.wikipedia.org/wiki/%E3%83%88%E3%83%BC%E3%82%AF%E3%83%B3 ↩

3. https://www.atilika.com/ja/kuromoji/ ↩

4. https://dev.mysql.com/doc/refman/8.0/en/fulltext-stopwords.html ↩