世界一わかりやすい FULLTEXT INDEX の説明と気を付けるべきポイント
FULLTEXT INDEX とは
インデックス（索引）は、データベースの性能を向上させる方法の一つです。
しかし、通常のIndex では text ベースのカラム(CHAR型、VARCHAR型、TEXT型) から特定の文字列を検索する全文検索には向いていません。
それは、通常のIndex はカラムの値の一部ではなく、値全体に対する検索に最適化されているからです。
そのため、全文検索 (カラムの値の一部が一致している結果を取得) するには、別のインデックス FULLTEXT INDEX が必要です。
MySQL で FULLTEXT INDEX を利用するには MATCH 関数(*1)を利用する必要があります。
通常の LIKE 検索では、FULLTEXTIndex が利用されないため、速度的に不利です。(*2)
(*1 DBMSによって様々、MS SQL では CONTAIN 関数を利用)
(*2 アンチパターンの一つ)
FULLTEXT INDEX の仕組み
ここで、FULLTEXT INDEX の仕組みを簡単に説明します。
FULLTEXT INDEXを作成したとき、対象のカラムの文字列を部分分割します。
この分割した文字に対してインデックスが作られます。文字列全体に対して、INDEXを作成しているわけではありません。
部分分割の方法は、言語によって異なります。
英語は単語ごとつまりスペース区切りです。日本語や中国語はそういった区切りがないため、ngram paser で n 文字に分割します。(後述します。)
MySQL の全文検索関数
さて、FULLTEXT INDEX の説明を簡単にしたところで、MySQL の全文検索関数について見ていきます。
MySQLの全文検索関数の構文は以下の通りです。
MATCH (col1, col2, ...) AGAINST (search_modifier)
search_modifier は検索オプションであり、全文検索の種類を選択することができます。
全文検索の種類には、次の3つの種類があります。
- IN NATURAL LANGUAGE MODE
- IN BOOLEAN MODE
- WITH QUERY EXPANSION
それぞれ説明します。
また、ここからは実際にテーブルを作成し、実例を見ながら学んでいきます。(遊戯王カードという稚拙な例ですいません。これしか思いつかなかった...)
今回は 遊戯王カードテーブルを作成します。
CREATE TABLE cards (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
name TEXT,
FULLTEXT (name) WITH PARSER ngram
) DEFAULT CHARSET = utf8 ENGINE=InnoDB;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment | |
| name | text | YES | | NULL | |
+-------+------------------+------+-----+---------+----------------+
データは適当に入れます。
INSERT INTO cards (name) VALUES
('バーサーカーソウル'),
('ブルーアイズホワイトドラゴン'),
('レッドアイズドラゴン'),
('ミレニアム・アイズ・サクリファイス'),
('ブルードラゴン');
+----+-----------------------------------------------------+
| id | name |
+----+-----------------------------------------------------+
| 1 | バーサーカーソウル |
| 2 | ブルーアイズホワイトドラゴン |
| 3 | レッドアイズドラゴン |
| 4 | ミレニアム・アイズ・サクリファイス |
| 5 | ブルードラゴン |
+----+-----------------------------------------------------+
1. 自然言語の検索 (IN NATURAL LANGUAGE MOD)
まず、自然言語ってなんだ...
自然言語とは「私たち人間が日常書いたり話したりしている日本語や英語のような、自然な言語のこと」
つまり、このオプションを設定すると、検索文字列を人間の自然な言語でのフレーズとして扱います。
検索文字列から単語を抽出して検索を行い、検索対象と検索文字列との類似性を計算し、高い順に順番に返します。
SELECT * FROM cards WHERE MATCH (name) AGAINST ('ブルーアイズ' IN NATURAL LANGUAGE MODE);
+----+-----------------------------------------------------+
| id | name |
+----+-----------------------------------------------------+
| 2 | ブルーアイズホワイトドラゴン |
| 5 | ブルードラゴン |
| 3 | レッドアイズドラゴン |
| 4 | ミレニアム・アイズ・サクリファイス |
+----+-----------------------------------------------------+
類似性の計算結果を見ることもできます。
SELECT *, MATCH (name) AGAINST ('ブルーアイズ' IN NATURAL LANGUAGE MODE) AS score FROM cards ORDER BY score desc;
+----+-----------------------------------------------------+---------------------+
| id | name | score |
+----+-----------------------------------------------------+---------------------+
| 2 | ブルーアイズホワイトドラゴン | 0.9037052989006042 |
| 5 | ブルードラゴン | 0.31671249866485596 |
| 3 | レッドアイズドラゴン | 0.0984337329864502 |
| 4 | ミレニアム・アイズ・サクリファイス | 0.0984337329864502 |
| 1 | バーサーカーソウル | 0 |
+----+-----------------------------------------------------+---------------------+
2. ブール検索 (IN BOOLEAN MODE)
特殊な演算子など (+, -, *) で、厳密な条件を付けて複数の単語をAND検索やOR検索できる
ストップワードリストが適用される。(こちらも後述)
例えば NATURAL LANGUAGE MODE では似ているものであれば検索結果として返される。
SELECT * FROM cards WHERE MATCH (name) AGAINST ('ブルーアイズ' IN NATURAL LANGUAGE MODE);
+----+-----------------------------------------------------+
| id | name |
+----+-----------------------------------------------------+
| 2 | ブルーアイズホワイトドラゴン |
| 5 | ブルードラゴン |
| 3 | レッドアイズドラゴン |
| 4 | ミレニアム・アイズ・サクリファイス |
+----+-----------------------------------------------------+
ですが、IN BOOLEAN MODE では完全に一致するものしか返されません。
SELECT * FROM cards WHERE MATCH (name) AGAINST ('ブルーアイズ' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
| 2 | ブルーアイズホワイトドラゴン |
+----+--------------------------------------------+
また特別な演算子で条件を指定することができるでやんす
SELECT * FROM cards WHERE MATCH (name) AGAINST ('*アイズ*' IN BOOLEAN MODE);
+----+-----------------------------------------------------+
| id | name |
+----+-----------------------------------------------------+
| 2 | ブルーアイズホワイトドラゴン |
| 3 | レッドアイズドラゴン |
| 4 | ミレニアム・アイズ・サクリファイス |
+----+-----------------------------------------------------+
3. クエリ拡張検索 (WITH QUERY EXPANSION)
このモードでは指定した単語を元に検索し、返されたレコード内で最も関連が高い単語を再度検索し直します。 これによってより曖昧な検索をすることができます。
IN NATURAL LANGUAGE MODE と比較してみます。
SELECT * FROM cards WHERE MATCH (name) AGAINST ('アイズ' IN NATURAL LANGUAGE MODE);
+----+-----------------------------------------------------+
| id | name |
+----+-----------------------------------------------------+
| 2 | ブルーアイズホワイトドラゴン |
| 3 | レッドアイズドラゴン |
| 4 | ミレニアム・アイズ・サクリファイス |
+----+-----------------------------------------------------+
SELECT * FROM cards WHERE MATCH (name) AGAINST ('アイズ' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);
+----+-----------------------------------------------------+
| id | name |
+----+-----------------------------------------------------+
| 4 | ミレニアム・アイズ・サクリファイス |
| 2 | ブルーアイズホワイトドラゴン |
| 3 | レッドアイズドラゴン |
| 5 | ブルードラゴン |
+----+-----------------------------------------------------+
ブルードラゴンが検索結果に追加されました。
FULLTEXT INDEX の中身を確認する方法
INFORMATION_SCHEMA にはインデックスに関する情報が含まれています。
確認したい対象テーブルとそのスキーマ名をあらかじめ構成変数innodb_ft_aux_table に代入する必要があります。
スキーマ名 / テーブル名 という形で代入。
SET GLOBAL innodb_ft_aux_table = 'experiment/cards';
次に、INFOMATION_SCHEMA のINNODB_FT_INDEX_TABLE テーブルから参照できる。
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| ァイ | 5 | 5 | 1 | 5 | 42 |
| アイ | 3 | 5 | 3 | 3 | 9 |
| アイ | 3 | 5 | 3 | 4 | 9 |
| アイ | 3 | 5 | 3 | 5 | 18 |
| アム | 5 | 5 | 1 | 5 | 9 |
| イス | 5 | 5 | 1 | 5 | 45 |
| イズ | 3 | 5 | 3 | 3 | 12 |
| イズ | 3 | 5 | 3 | 4 | 12 |
| イズ | 3 | 5 | 3 | 5 | 21 |
| イト | 3 | 3 | 1 | 3 | 24 |
| ウル | 2 | 2 | 1 | 2 | 21 |
| カー | 2 | 2 | 1 | 2 | 12 |
| クリ | 5 | 5 | 1 | 5 | 33 |
| ゴン | 3 | 6 | 3 | 3 | 36 |
...
二文字に分割された文字がインデックスとして作成されている。
ngram とは
もう一回遊戯王カードテーブルを作成したクエリを見てみましょう。
CREATE TABLE cards (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
name TEXT,
FULLTEXT (name) WITH PARSER ngram
) DEFAULT CHARSET = utf8 ENGINE=InnoDB;
ここの、WITH PASER ngram は、文字を n 個に分割し、インデックスを作成するというという意味である。
通常、FULLTEXT INDEX を貼る際は、
CREATE FULLTEXT INDEX idx_t_cards_1 ON cards (name);
とできるが、日本語のFULLTEXT INDEX には対応できません。
前述したようにこれは日本語が英語のようにスペース区切りでないためです。
通常の FULLTEXT INDEX では INDEX 対象カラムの内容を スペースあるいはカンマなどの区切りで INDEX を作ることができ、英語には有効である。
だが、日本語はそういった区切りがないため、通常の INDEX では役に立ちません。
つまり、ngram parser で日本語を n 文字に分割し、辞書 (INDEX) を作成し検索をする方法が取られます。
MySQL ではデフォルトで 2文字分割となっていますが、設定で変更できます。
ストップワードテーブルでハマったこと
ブール検索 (IN BOOLEAN MODE) では、ストップワードが適用されます。
ストップワードの文字が含まれている INDEX は作成されません。
ストップワードは、MySQL の information_schema の INNODB_FT_DEFAULT_STOPWORD に40件ほどのデータがあらかじめ用意されています。 (a, I, an ...など)
MySQL のngramパーサーで アルファベットを含む文章を分割したとします。
分割した文字にストップワードの英単語が含まれている場合、インデックスが作成されません。
ngram パーサーのドキュメント和訳
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html より引用
取り敢えずの回避策は2つほどある。
デフォルトのストップワードリストを使用せず、空のテーブルを作成し、それを innodb_ft_server_stopword_table に設定する。
use test;
CREATE TABLE sample
(id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci,
FULLTEXT KEY (name) WITH PARSER ngram,
PRIMARY KEY (id));
INSERT INTO sample(name) VALUES ("anaが好き");
CREATE TABLE stopwords (value varchar(255) NOT NULL PRIMARY KEY);
SET GLOBAL innodb_ft_server_stopword_table = 'test/stopwords';
OPTIMIZE TABLE sample;
SELECT * FROM sample WHERE MATCH(name) AGAINST ('an' IN BOOLEAN MODE);
SET GLOBAL innodb_ft_aux_table = 'test/sample';
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY position;
anaが好きに対して、'ana' で検索しても結果が返るようになります。
Discussion