MySQL5.6で全文検索やったよ

はじめに

題名のとおり、MySQL5.6で全文検索をやったので、またやるときに困んないようにメモを残します。
や、実は全文検索やったの2回目だったんだけど、細かいところどころか大本さえ忘れてて困ったんですよこの前。。。

あと、念のためお断り。

• やったのはMySQL5.6です。5.7の各種パーサーの話は出ません。
  • すでにMySQL5.6が入ってる環境だったんで仕方ない。
• 今回は素のMySQLをつかってます。なので、Mroongaの話は出ません。
  • 正直、実装当時は知らなかったのもあって調べてもいない。。
    • プラグインみたいに追加インストールって出来るのかしら？
• 今回はファンクションやトリガーを使いません。
  • メンドかったから(´・ω・｀)　まぁ、プログラム側で出来るしね。

my.cnfの設定

何はなくとも、まずは設定をいれないと話にならない。
my.cnfに以下を記述する。

[mysqld]
# 全文検索オプション
ft_min_word_len = 2
innodb_ft_min_token_size = 2
innodb_ft_enable_stopword = OFF

• ft_min_word_lenはMyISAMでの単語最小文字数を設定する項目
  • MySQL5.5ではMyISAMもInnoDBもこの指定が必要だったと思う
    • ていうかinnodb_ft_min_token_sizeがなかったんじゃなかったっけ？
• innodb_ft_min_token_sizeはInnoDBでの単語最小文字数を設定する項目
• innodb_ft_enable_stopwordはストップワードの使用有無を設定する項目
  • ストップワードは、全文検索のインデックスを作らない対象ワードを指す（is、theとかだったかな）
  • ↑ではOFF（無効）にしてるけど、自前のテーブルを指定することも可能（たぶん後述）

この設定を追記したあと、MySQLを再起動する。
再起動わすれて「反映されない・・・・」とか嘆かないこと。

テーブルとインデックス

今回はこんな感じのテーブルとインデックスで。

CREATE TABLE `t_user` (
  `id` INT(12) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` VARCHAR(30) NOT NULL COMMENT '名前',
  `name_fulltext` VARCHAR(90) NOT NULL COMMENT '名前(検索用)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COLLATE `utf8_unicode_ci` COMMENT='ユーザー';

CREATE FULLTEXT INDEX `idx_t_user_1` ON `t_user` (`name_fulltext`);

日本語の単語を入れる予定なので、照合順序をutf8_unicode_ciにしておく。
照合順序に関しては簡単に言うと↓

タイプ	英：大文字小文字	日：全角半角	日：ひらがなカタカナ	日：濁点
(encode)_general_ci	区別なし	区別あり	区別あり	区別あり
(encode)_unicode_ci	区別なし	区別なし	区別なし	区別なし

・・・・て感じたぶん。

もっと詳しく知りたい方は【MySQL】照合順序とは？をご参照くださいな。

全文検索をする対象にはちゃんとFULLTEXT INDEXを張る。
当然だけど、忘れがち。

あと、これは今回知ったんだけど、なんかFULLTEXT INDEXを張るカラムってTEXT型じゃなくていいのね。。
もう妄信的にTEXT型で作ってたわ・・・・(´・ω・｀)

検索するよ

データを入れる

先のテーブルにデータを入れる。

INSERT INTO `t_user`
VALUES (null, 'Jeff Tracy', 'Jeff Tracy')
,(null, 'Scott Tracy', 'Scott Tracy')
,(null, 'Virgil Tracy', 'Virgil Tracy')
,(null, 'Alan Tracy', 'Alan Tracy')
,(null, 'Gordon Tracy', 'Gordon Tracy')
,(null, 'John Tracy', 'John Tracy')
,(null, 'Brains', 'Brains')
,(null, 'Lady Penelope Creighton-Ward', 'Lady Penelope Creighton-Ward')
,(null, 'Aloysius Parker', 'Aloysius Parker')
,(null, 'The Hood', 'The Hood');

サンダーバード。メインキャラ。
や、なんか突然おもいだしたんで、、、

検索する

で、検索する。
全文検索の構文はMATCH (カラム名) AGAINST ('キーワード')が基本。

tracyで検索

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('tracy');
+----+--------------+---------------+
| id | name         | name_fulltext |
+----+--------------+---------------+
|  1 | Jeff Tracy   | Jeff Tracy    |
|  2 | Scott Tracy  | Scott Tracy   |
|  3 | Virgil Tracy | Virgil Tracy  |
|  4 | Alan Tracy   | Alan Tracy    |
|  5 | Gordon Tracy | Gordon Tracy  |
|  6 | John Tracy   | John Tracy    |
+----+--------------+---------------+
6 rows in set (0.00 sec)

Hoodで検索

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('Hood');
+----+----------+---------------+
| id | name     | name_fulltext |
+----+----------+---------------+
| 10 | The Hood | The Hood      |
+----+----------+---------------+
1 row in set (0.00 sec)

はい、取れました。

The End...?

実際のところ、本来の全文検索は↑でお終い。

・・・・なのだけど。
我々が作ってるのは（だいたい）日本人向けのコンテンツなわけで・・・・。
そーすると当然データも日本語が入るわけで・・・・。
日本語には半角スペースで区切るなんて文化はないわけで・・・・。

このままだと何の役にも立ちませぬ・・・・orz

日本語で検索するよ

てことで、日本語で検索する方法。

ここで出てくるのがN-gramと形態素解析。
要は、検索できる形にデータを保存してやれば、英語だろーが日本語だろーが検索できるという寸法。そして、その検索できる形にデータを加工する手法がこの2つというワケ。「MySQL 全文検索」でググって出てくる技術系ブログで、よくこの2ワードが出てくるのはそういうことたぶん。

N-gram

Wikipediaせんせーい・・・・にお願いしようと思ったけど、あんま詳しいこと書いてなかった。。。orz

N-gramとは、文字を一定間隔でぶつ切りにして、それを半角スペースでくっつけたもの。
特に、1文字間隔のものをユニグラム（uni-gram）2文字間隔のものをバイグラム（bi-gram）、3文字間隔のものをトライグラム（tri-gram）という。
N-gramは、日本語やらだけでなく、英単語に対しても有効なイメージ。

例）

キーワード	ユニグラム	バイグラム	トライグラム
ルパン三世	ル パ ン 三 世	ルパ パン ン三 三世	ルパン パン三 ン三世
次元大輔	次 元 大 輔	次元 元大 大輔	次元大 元大輔
石川五エ門	石 川 五 右 エ 門	石川 川五 五右 右エ エ門	石川五 五右エ 右エ門

MySQLの場合、この間隔をさっきのft_min_word_len、innodb_ft_min_token_sizeで指定してあげる。
そして、データ投入時にこの形式で値をいれてやれば良さげ。

PHPで分割するときの例は↓

phpの例

function create_ngram_string($keyword)
{
    $keyword_length = mb_strlen($keyword);

    $ngram_byte_length = 2;	// bi-gram

    if ($keyword_length < $ngram_byte_length) {
        // 文字数が分割数より少ない場合、前方一致検索にするため * をつけて返す
        return sprintf('%s*', str_replace('"', '\"', $keyword));
    }

    $grams = [];

    for ($start = 0; $start <= ($keyword_length - $ngram_byte_length); $start += 3) {
        $target = mb_substr($keyword, $start, $ngram_byte_length);
        $grams[] = $target;
        // $grams[] = "+{$target}";
    }

    // フレーズ検索
    return sprintf('"%s"', implode(' ', $grams));
    // return sprintf('%s', implode(' ', $grams));
}

形態素解析

辞書をつかって意味のある単語にわけ、それを半角スペースでくっつけたもの。
この辞書はMeCabとかが有名。

N-gramと違い、単語の文字数がバラバラになるので、ft_min_word_len、innodb_ft_min_token_sizeには1を指定してあげた方が良さげかしら？
データ投入時、辞書にかけて単語を分け、それを投入してあげる。
また、検索時にも辞書にかけて単語に分けてやった方がいいたぶん。

・・・・ごめん、形態素解析をつかって全文検索はやったことがないので、かなーり憶測です。。。
もし後々で形態素解析つかってやることがあったら追記するってことで、これくらいで堪忍して・・・・(;-∀-)

データを入れる（N-gram）

先のテーブルをきれいにしてから、新しくデータを入れる。
なお、バイグラムでやっていきます。

DELETE FROM `t_user`;
ALTER TABLE `t_user` AUTO_INCREMENT = 1;

INSERT INTO `t_user`
VALUES (null, 'ルパン三世', 'ルパ パン ン三 三世')
,(null, '次元大輔', '次元 元大 大輔')
,(null, '石川五右エ門', '石川 川五 五右 右エ エ門')
,(null, '峰不二子', '峰不 不二 二子')
,(null, '銭形幸一', '銭形 形幸 幸一')
,(null, 'パイカル', 'パイ イカ カル')
,(null, 'ストーンマン', 'スト トー ーン ンマ マン')
,(null, '小山田マキ', '小山 山田 田マ マキ')
,(null, 'レフティ', 'レフ フテ ティ')
,(null, '青龍', '青龍')
,(null, 'マモー', 'マモ モー')
,(null, 'フリンチ', 'フリ リン ンチ')
,(null, 'クラリス', 'クラ ラリ リス')
,(null, 'カリオストロ伯爵', 'カリ リオ オス スト トロ ロ伯 伯爵')
,(null, 'ロゼッタ', 'ロゼ ゼッ ッタ')
,(null, 'マルチアーノ', 'マル ルチ チア アー ーノ')
,(null, 'コワルスキー', 'コワ ワル ルス スキ キー')
,(null, '墨縄紫', '墨縄 縄紫')
,(null, '風魔一族のボス', '風魔 魔一 一族 族の のボ ボス')
,(null, 'ジュリア・ダグラス', 'ジュ ュリ リア ア・ ・ダ ダグ グラ ラス')
,(null, 'ライズリー', 'ライ イズ ズリ リー')
,(null, 'クリス', 'クリ リス');

ルパンシリーズ。TV版はPart.IIIまで。映画は観たところまで。
どうだっていいね。

検索する（N-gram）

検索するよ。
なお、検索時にもキーワードをN-gramで分けてやる必要あり。

ルパンで検索

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('ルパ パン');
+----+-----------------+-----------------------------+
| id | name            | name_fulltext               |
+----+-----------------+-----------------------------+
|  1 | ルパン三世      | ルパ パン ン三 三世         |
+----+-----------------+-----------------------------+
1 row in set (0.00 sec)

るぱんで検索

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('るぱ ぱん');
+----+-----------------+-----------------------------+
| id | name            | name_fulltext               |
+----+-----------------+-----------------------------+
|  1 | ルパン三世      | ルパ パン ン三 三世         |
+----+-----------------+-----------------------------+
1 row in set (0.01 sec)

utf8_unicode_ciを指定してるから、カタカナでもひらがなでもちゃんととれる(´▽｀)

クラリスで検索

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('クラ ラリ リス');
+----+-----------------------------+---------------------------------------------------------+
| id | name                        | name_fulltext                                           |
+----+-----------------------------+---------------------------------------------------------+
| 13 | クラリス                    | クラ ラリ リス                                          |
| 20 | ジュリア・ダグラス          | ジュ ュリ リア ア・ ・ダ ダグ グラ ラス                 |
| 22 | クリス                      | クリ リス                                               |
+----+-----------------------------+---------------------------------------------------------+
3 rows in set (0.00 sec)

！( ﾟДﾟ)
クラリスにおてんば娘と悪い虫が・・・・！(((；ﾟДﾟ)))

これじゃあ困る、そんなときはBOOLEAN MODE

全文検索なんだからそりゃそうなんだろうけど、それでもクラリスだけ欲しーなー・・・・ということで、BOOLEAN MODEの説明。

全文検索でつかうMATCH...AGAINST...は、実はいくつかモードがありまして。

• 自然言語全文検索（なし or IN NATURAL LANGUAGE MODE）
  • MATCH...AGAINSTのデフォルト
  • 明示的に指定したい場合はMATCH (カラム名) AGAINST ('キーワード' IN NATURAL LANGUAGE MODE)
  • 乱暴にいうとOR検索
  • キーワードに指定した文字群に該当するもの関連しているとみなし、関連の強い順に取得する
• ブール全文検索
  • MATCH (カラム名) AGAINST ('キーワード' IN BOOLEAN MODE)
  • キーワードに対して演算子による細かい指定が可能
  • 関連に関しては自然言語全文検索と同じだけど、演算子によりその関連性の調整も可能
• クエリー拡張を使用した全文検索
  • MATCH (カラム名) AGAINST ('キーワード' WITH QUERY EXPANSION) か MATCH (カラム名) AGAINST ('キーワード' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)
  • キーワードに関して検索し、さらにそのキーワードに関連しているであろうデータも検索する。
  • 内部的に検索が2回走る（キーワード検索→キーワードに関連してるヤツ検索）
  • 試してみたけどうまく動かなんだ。。。何か設定が必要なのかも？

こんな感じで3つある。
先の検索は自然言語全文検索になっていたので、

1. 「クラ」と「ラリ」と「リス」でそれぞれ持っているデータを検索
2. 「クラ」と「ラリ」と「リス」の持っている件数順に表示

という動きをしていた。
これだとちょっと・・・・という今回の場合だと、ブール全文検索を使ってやればおk。

ブール全文検索その1：＋

クラリスだけ検索-1

SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('+クラ +ラリ +リス' IN BOOLEAN MODE);
+----+--------------+----------------------+
| id | name         | name_fulltext        |
+----+--------------+----------------------+
| 13 | クラリス     | クラ ラリ リス       |
+----+--------------+----------------------+
1 row in set (0.00 sec)

ブール全文検索にしつつ、キーワードがクラ ラリ リスから+クラ +ラリ +リスに変更。
+は「必ず含まれていること」という演算子で、「クラ」と「ラリ」と「リス」を持っているデータを取ってくる、という指定になる。
この場合、（誰か知らんけど）「サクラ・ラリスキー」みたいなのが入ってるとそれも入ってくる。

ブール全文検索その2：フレーズ検索

クラリスだけ検索-2

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('"クラ ラリ リス"' IN BOOLEAN MODE);
+----+--------------+----------------------+
| id | name         | name_fulltext        |
+----+--------------+----------------------+
| 13 | クラリス     | クラ ラリ リス       |
+----+--------------+----------------------+
1 row in set (0.00 sec)

同じくブール全文検索にしつつ、キーワードを"クラ ラリ リス"に変更。
"キーワード"は「キーワードそのまんま入ってるデータだけ対象」という演算子（フレーズ検索）で、「クラ ラリ リス」というキーワードを持っているデータを対象という指定。
これなら「サクラ・ラリスキー」何某がきてもクラリスだけ取れる。
が、「マクラリスク」（name_fulltextは「マク クラ ラリ リス スク」）とかいうよー分からんものがあれば取れてくる。

ブール全文検索は色々できるよ

ブール全文検索での演算子は色々あるので、一度は12.9.2 ブール全文検索をご覧あそばせ。
フツーの検索だけやりたい場合は↑だけで十分だと思うんだけどね。

とはいえ

もっとも、これらの違いはデータ取得の正確性に関わるところなので、結局のところ求められてる要件によって変えていけばよい。

実際、自分がやった案件では色々やらされた挙句試した結果「キーワードに関連してるヤツならなんでも欲しい」と先方が抜かしてきてに言われて、自然言語全文検索に戻した。
まぁ、勉強になったからいいけどさ・・・・説明したときに言いなさいよ・・・・(;・∀・)
ちなみに、先に出したPHPサンプルはこれ言われる直前までのソースを改変したもの。腹立たしいからコメントアウトはそのまんま。

この記事でも別に自然言語全文検索だのブール全文検索だの触れなくていいかなぁと思ったんだけど、なまじルパンでデータを作っただけに**「クラリスだけ欲しいなコレ」**と思ったからここまで書いた。

The End

真エンディング的な。

上記のような形で設定、実行すればとりあえず全文検索は出来るはず。
ちょっとした機能でーとか、どんなモンか試したれ的なときとかやってみるといいんじゃないでしょうかね。

とはいえ、全文検索用のサービスは色々あるので、PJの立ち上げでホントに一からつくるよってときはそっちも検討した方がよろしいかと。
AWS Elasticsearchとか、Apache Luceneとか、それころMroongaとかね。
自分はどれも試したことが無いんで、またの機会かなぁ。パフォーマンスどうなんだろ？

あと、MySQL5.7ではN-gramパーサー、MeCabパーサーが追加された（MeCabの方はプラグインだっけ？）ので、こんな記事を見ずにそっちでやった方がもっと気楽にできるんじゃなかろうか、と思う次第。

あ、間違いやより良い方法があればご教示ください。
当方、ホントにここまで書いたとおりの認識で使ってるんで、これしか知らぬ状況でございます。。。

どーでもいいけど、今回のデータ作成でWikipedia先生にちょいちょい調べにいったのが心残り。。
前はキャラ名も脇役含め分かったんだどなぁ・・・・(>_<；)

おまけ

ストップワードテーブルを指定する

忘れなかったよ(´▽｀)

ストップワードは、あらかじめMySQLのinformation_schemaに用意されている、INNODB_FT_DEFAULT_STOPWORDを使用している。
コイツには、40件足らずのデータが入っているのだけど、aとかisみたいな**どの単語でも出てくんだろがい！**て単語まで入っているせいで、割と英単語が検索できない事態が発生してしまう。

なので、先に書いた通り無効化してしまうのだけど、それはちょっと・・・・てときとか、むしろ別の単語をはじきたいんだがてときは、自前のストップワードテーブルを指定してやる。

まずは、対象となるテーブルを作る。

CREATE TEMPORARY TABLE `my_stopwords` (
  `value` varchar(18) NOT NULL DEFAULT ''
) ENGINE = INNODB DEFAULT CHARSET=`utf8`

要は、INNODB_FT_DEFAULT_STOPWORDと同形式のテーブルなら問題なし。

これをつかうってことをmy.cnf上に指定してやる。

[mysqld]
innodb_ft_enable_stopword  = ON
innodb_ft_server_stopword_table = 'test/my_stopwords';

この際、テーブル名だけではなくスキーマ名も含めて指定してやること。
再起動時に怒られまっせ。

1語で検索

N-gramでやっていても、やはりそれ以下の文字数で検索したくなるときがある。サジェストとかね。
そんなのもブール全文検索で対応できる。

青で検索

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('青*' IN BOOLEAN MODE);
+----+--------+---------------+
| id | name   | name_fulltext |
+----+--------+---------------+
| 10 | 青龍   | 青龍          |
+----+--------+---------------+
1 row in set (0.00 sec)

*は、切捨て演算子とかいうもので、頭があってれば取ってくる、て感じです。
ただ、あくまで頭が合ってるのが条件なので、後方一致はできないし、万能ではない。

一で検索

mysql> SELECT * FROM `t_user` WHERE MATCH(`name_fulltext`) AGAINST('一*' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------------+
| id | name                  | name_fulltext                             |
+----+-----------------------+-------------------------------------------+
| 19 | 風魔一族のボス        | 風魔 魔一 一族 族の のボ ボス             |
+----+-----------------------+-------------------------------------------+
1 row in set (0.00 sec)

この場合、「銭形幸一」が取れないのは、name_fulltextの値が「銭形 形幸 幸一」であり、「一」で始まる単語がないから。
これを補うために「銭形 形幸 幸一 一 」とか入れてもダメなので、諦めるより仕方ない（経験済み）。

もしかして検索

・・・・忘れた。
やったときに使ってたPCが大破（物理）したのでいま参照できるソースがない。。
たしかどっかにあったと思うんで、発掘したあとで書く。

分かったので追記。

もしかして検索は、レーベンシュタイン距離を用いた方法により実装したよ。
流れ的には、

1. 通常の全文検索を実行
2. 結果がない場合、今度は自然言語全文検索で関連がありそうなものを全取得
3. 各データからレーベンシュタイン距離を算出
4. もっとも値が大きいものを返却

・・・・という感じ。

PHPで実装したのだけど、[マルチバイト対応] レーベンシュタイン距離を求めるを参考にさせてもらって、FWに合わせて実装しただけなのでソースは割愛。

もっとも、評判はあまり良くなかったけど。。。
サジェストなんだからGoogle先生のもしかしてと比べちゃダメですよ・・・・(-_-;)

参考URL

この記事を書くにあたり参考にした記事のリンクです。

【MySQL】照合順序とは？
12.9 全文検索関数
12.9.2 ブール全文検索
12.9.6 MySQL の全文検索の微調整
[マルチバイト対応] レーベンシュタイン距離を求める
サンダーバード (テレビ番組)
ルパン三世 (TV第1シリーズ)の登場人物
ルパン三世 (TV第2シリーズ)の登場人物
ルパン三世 PARTIIIの登場人物