※ MySQLの全文検索に真正面から取り組んだ話です。ケーススタディとして、ご参考になれば幸いです。
依頼を受けた
突然依頼を受けました。主な依頼内容はこちらです。
- テキストのデータが大量にあるので、これを素早く検索できるようにしてほしい。
テキストは、スペース区切りになっているものがある。日本語英語は混じっている。
数十文字の文章もあれば、
「沖縄 ホテル」のような短いテキストもある。
単語一致、部分一致で検索したい。
AND/OR条件、除外条件にも対応してほしい。
MySQLを使ってリアルタイムに（数秒で）レスが返ってきてほしい。
「B'z」を検索してもちゃんと返ってくる。
突然の依頼ではありましたが、依頼主とは常日頃BAD COMMUNICTION、、あ、いやいや、大変Goodで円滑なコミュニケーションを取れていたので、要望はきっちり聞き出せました。
なんで急にB'z？って話ですが、私は根っからのB'zファンです。なのでよく「B'z」でググったりするわけですが、「B'z」って、よくよく考えると特徴的なワードですよね。英字・記号・英字の3文字。ちゃんと「'」をエスケープしてないと大体の場面でエラーになっちゃうので、テストにも最適なワードです。是非とも「B'z」を素早く検索したい！ 最先端から加速したい・・・！！！
要件の整理
部分一致はいいとして、ここでの単語一致とは、スペース区切りの単語で見たら完全一致になるような条件、ということです。「'沖縄'の単語一致」→「沖縄 ホテル」は引っかかるが「沖縄県」は引っかからない。部分一致は、この場合どちらも引っかかります。
「テキスト」「MySQL」「速く」、、的なワードでググってみると、どうも「全文検索」というのが良いらしいです。
全文検索とは、テキストの検索に適した特殊なインデックス（FULLTEXTインデックス）を張ったカラムに対してかける検索手法のこと。もともと英文への適用が前提のため、スペース区切りの単語ごとにインデックスを張る、みたいなやり方ができ、日本語に対しても「n-gramパーサー」を使い、n文字区切りでインデックスを張ることができるため、日本語の高速なテキスト探索が可能になります。
しかも今回の場合、テキストはスペース区切りになっているものが多いので、通常のFULLTEXTインデックスを張れば「単語一致検索」が可能になりそうです。すばらしい！
というわけで、以下の方針で実装を進めることにしました。これでB'zを検索できる・・・
実装案
- InnoDBのFULLTEXTインデックスを用いる
- textとtext_ngramという2種類のカラムを作り、それぞれ内容は同じだが、インデックスの張り方だけ変える
- 通常のFULLTEXTインデックスを張ったカラムに対する全文検索により単語一致を実現する。
- ngramパーサーのFULLTEXTインデックスを張ったカラムに対する全文検索により部分一致を実現する。
- 全文検索のboolean modeを用い、AND・OR・除外を表現する。
日本語において通常のFULLTEXTインデックスが有効という、レアなケースかもしれません。
通常のFULLTEXTインデックスとngramの方を別カラムにしたのは、
「A」の単語一致 かつ 「B」の部分一致
みたいな特殊な検索にも対応できるようにするためです。
natural language mode と boolean mode
詳しくは公式リファレンスをご参照いただければと思いますが、こちらでもまとめてみます。
(MySQL5.6のリファレンスをリンクさせていますが、8.0のリファレンスもほぼ同内容だったため、日本語のページをリンクさせています)
natural language mode (自然言語検索)
リファレンスによると、
デフォルトの場合や IN NATURAL LANGUAGE MODE 修飾子が指定された場合は、MATCH() 関数は、テキストコレクションに対して文字列の自然言語検索を実行します。コレクションは、FULLTEXT インデックスに含まれる 1 つ以上のカラムのセットです。検索文字列は、AGAINST() への引数として指定されます。MATCH() は、テーブルの行ごとに関連性の値を返します。つまり、検索文字列と、MATCH() リストで名前が指定されたカラムの該当行のテキスト間で類似性が評価されます。
つまり、検索文字列を多く含むテキストが類似度上位として検索されます。
match(text) against ('沖縄' in natural language mode)
とすると、単語「沖縄」をたくさん含むテキストが優先的に選択されます。
ngramパーサーのFULLTEXTインデックスの場合、gram数で区切った文字列が検索対象となります。
match(text_ngram) against ('ダイエット' in natural language mode)
とすると、gram数=2のとき、「ダイ」「イエ」「エッ」「ット」をたくさん含むテキストが優先的に選択されます。
テキストに「ダイエット」が入っていると、上記2文字のフレーズがたくさん含まれますので、必然的にそのテキストが上位に来ます。
ただ、仮に「ット」をすご～くたくさん含むテキストがあったとしたら、それも上位に現れます。"あいまい検索"と言えるかもしれません。
boolean mode (ブール検索)
こちらもリファレンスを参照すると、
IN BOOLEAN MODE 修飾子を使用することでブール全文検索を実行できます。この修飾子を使用すると、検索文字列の先頭または末尾にある特定の文字が特別な意味を持ちます。
こちらは、検索文字列を含むか含まないかの0/1で判断します。また、修飾子を用いてより細かく条件を指定できるのも特長です。例えば
match(text) against ('A' in boolean mode) → 「A」を含むテキスト
match(text) against ('A B' in boolean mode) → 「A」「B」どちらかを含むテキスト
match(text) against ('+A +B' in boolean mode) → 「A」「B」両方含むテキスト
match(text) against ('A -B' in boolean mode) → 「A」を含み、「B」を含まないテキスト
match(text) against ('"A B"' in boolean mode) → 「A」「B」がその順に並んだフレーズを含むテキスト
今回は、厳密に単語一致・部分一致を実現したいため、boolean modeを使うこととします。
FULLTEXTインデックスを張る前に
早速データに適用したいところですが、いろいろ事前に設定しておきます。
パラメータの調整
collation_connection=utf8mb4_general_ci
default_collation_for_utf8mb4=utf8mb4_general_ci
今回のデータは日本語を含みます。MySQL8.0を使うのですが、文字コードでutf8mb4を使うとき、MySQL8.0ではデフォルトのcollationがutf8mb4_0900_ai_ciです。これだと「ハハパパ問題」が発生してしまいますので、変更します。collationについては こちらも大変参考になります。
ft_min_word_len=1
innodb_ft_min_token_size=1
これらパラメータで、検索できる最低文字数を設定します。春とか扉とか、日本語には1文字で重要な意味を持つ単語がたくさんありますので、1文字の検索を許容します。
innodb_ft_enable_stopword=0
AとかTHEとか、検索で引っかかっても分析にあまり寄与しない単語がストップワードとして事前に登録されています。デフォルト設定が英文に最適化したものなのでそうなっているのですね。日本語がメインのデータベースで、これらを除外したいという強い要件も無いので、今回はストップワードも検索対象とします。「IN THE LIFE」とかが検索できなくなりますし。。。
ngram_token_size=2
ngramは、日本語の全文検索では一般的なn=2で対応します。これは、1文字での部分一致検索ができなくなることを意味しますが、パフォーマンスの観点から致し方ありません（ngram=1も試したが、かなり重くなってしまった）。
データ投入
データの投入の際もひと工夫があるとよいです。
インデックスを張る → データをインポート
より、
データをインポート → インデックスを張る
の方が速いケースが多いです。
先にインデックスを張ってしまうと、レコードが増えるたびにインデックスのデータの更新が入るような挙動が見られ、レコードの増加と共に、それにかかる時間がどんどん増えてしまいます。
データを入れ替える際も、
truncate
→ drop index
→ insertなりload dataなり
→ add fulltext
とすると幾分か速いです。
テーブルはこんな構成です：
CREATE TABLE data_fulltext (
text varchar(384) COLLATE utf8mb4_general_ci NOT NULL,
text_ngram varchar(384) COLLATE utf8mb4_general_ci NOT NULL,
FULLTEXT KEY ft (text),
FULLTEXT KEY ftn (text_ngram) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
全文検索の性能検証
速さ
データをインポートできたら、早速検索してみます。全文検索は match(カラム) against ('ワード') という、変わった構文で行います。
まずは単語一致から。
select count(1) from data_fulltext
where match(text) against ('沖縄' in boolean mode);
+----------+
| count(1) |
+----------+
| 12442 |
+----------+
1 row in set (0.15 sec)
速い！ 続いて部分一致。
select count(1) from data_fulltext
where match(text_ngram) against ('沖縄' in boolean mode);
+----------+
| count(1) |
+----------+
| 18558 |
+----------+
1 row in set (0.03 sec)
速いです。
これがlikeだとだいぶかかります。
select count(1) from data_fulltext
where text like '%沖縄%';
+----------+
| count(1) |
+----------+
| 18558 |
+----------+
1 row in set (1 min 33.25 sec)
90秒以上かかってしまいました。なお、通常のインデックスを張っていませんが、likeで部分一致する際インデックスは利かないので、FULLTEXTインデックスを使わないと最速でこれだけ、ということになります。全文検索すごいです。
90秒かかるところ1秒未満になるのだから、性能としては十分でしょう。もちろん、レコード数が多ければその分時間はかかりますし、count(1)じゃなくselect * from ~~~と全件出力する場合ももう少しかかります。が、全文検索の方がダントツで速いです。
B'z
さて、あとは「B'z」が素早く検索できさえすればもうこちらの勝利です。
select count(1) from data_fulltext
where match(text) against ('B\'z' in boolean mode);
+----------+
| count(1) |
+----------+
| 12790 |
+----------+
1 row in set (0.03 sec)
相変わらずの速さ。一応中身も確認しておきましょう。
select * from data_fulltext
where match(text) against ('B\'z' in boolean mode) limit 3;
+---------------------+---------------------+
| text | text_ngram |
+---------------------+---------------------+
| #B'Z | #B'Z |
| &Z | &Z |
| / B'Z | / B'Z |
+---------------------+---------------------+
3 rows in set (0.01 sec)
（データはもちろんダミーです）
お～B'zが拾えてきてます！ ・・・が、何かおかしい。B'zじゃないものもありますね。
まず「#B'Z」は単語一致ではないし、「&Z」は、一体何なんでしょう。
何かミエナイチカラがはたらいているようです・・・
全文検索において記号は無視される
公式のリファレンスによると、
MySQL FULLTEXT の実装では、トゥルーワード文字 (文字、数字、およびアンダースコア) のシーケンスが単語とみなされます。そのシーケンスには、アポストロフィー (「'」) も含めることはできますが、1 行に 1 つまでです。
とあります。
「B'z」の「'」はアポストロフィーなわけですが、どうやら「'」も無視されてしまっているようです。
「#B'Z」が拾われているのも、「#」はトゥルーワード文字ではないので無視された、ということになります。
では「&Z」は？？？ これは、検索した文字列「B'z」の「'」が無視され、where match(text) against ('B z' in boolean mode)と同義になり、
「B」と「z」のいずれかを含む
という条件で拾われてしまったようです（かつ、「&Z」の「&」も無視）。
「B'z」で検索して、「#B'Z」は許せるにしても「&Z」は許せない。。。そこで、検索時、「B'z」を「"」で囲ってみます。
「"」で囲うと、囲った単語がその順に並んだテキストに合致します。
select * from data_fulltext
where match(text) against ('"B\'z"' in boolean mode) limit 3;
+---------------------+---------------------+
| text | text_ngram |
+---------------------+---------------------+
| #B'Z | #B'Z |
| / B'Z | / B'Z |
| B Z | B Z |
+---------------------+---------------------+
3 rows in set (0.01 sec)
狙い通り、「B'z」がたくさん出るようになりました。
しかし、これでもまだ「記号が無視される」仕様により「B Z」というテキストが拾われてしまいます。
（そもそも「'」は単語の一部として認められるはずなんですが・・・謎です）
これはもうどうしようもないのかなあ、、、と諦めかけましたが、ちょっと考え方を変えてみることにしました。
データを見直す
お気づきの方もいらっしゃるかもしれませんが、selectされたテキスト、「B'z」じゃなくて「B'Z」なんですよね。likeで当ててみても、「B'z」という文字が出てこない。で、依頼者に問い合わせたところ、
「あ～アルファベットは全て大文字に正規化してるんですよ」
なるほど、表記ゆれを丸めたかったのか、そういう前処理を施していたようです。つまり、このデータにアルファベット小文字は存在しない。。。
若干狂気じみたことを思いつきまして。記号を全てアルファベット小文字に置換してしまえばいいじゃないか！ 「'」は「sq」（single quote）、「"」は「dq」（double quote）のように、アルファベット小文字2文字に置換してやります。ただこれだと、記号が複数文字、例えば2文字並んだとき、アルファベット小文字が4文字並ぶわけですが、2,3文字目のアルファベット小文字が別の記号を意味するようなことがあってはいけません。そこで、記号を表すアルファベットの先頭は必ずyとし、続いて、記号に対してユニークなアルファベット小文字2文字をつなげる(ただしその2文字にyは含めない)とすることにしました。これなら、記号が複数文字並んだとしても、記号を表すのは「y」から始まるアルファベット小文字3文字になるので、ダブることもありません。
記号を英小文字に置換し、全文検索で引っかかるようにする
というわけで、データを一から作り直します。
以下のような置換処理を行いました：
| 記号 | 置換後 | 由来 |
|---|---|---|
| ・ | ybp | bulltet point |
| ! | yex | exclamation mark |
| / | ysl | slash |
| ( | ylr | left round bracket |
| ' | ysq | single quotation |
| ・・・ | ・・・ | ・・・ |
（ちなみに「y」は、記号の英訳「s y mbol」からとってます・・・「s」は結構頭文字として頻繁に出てくるので。。。）
さらに、Collationがutf8mb4_general_ciのままだとアルファベット大文字と小文字が区別されませんので、utf8mb4_binに変更します。
さて、これで「B'z」を検索してみます。「'」は・・・「ysq」ですね。
select * from data_fulltext
where match(text) against ('"BysqZ"' in boolean mode) limit 3;
+--------------+--------------+
| text | text_ngram |
+--------------+--------------+
| ysl BysqZ | ysl BysqZ |
| BysqZ | BysqZ |
| 2018 BysqZ | 2018 BysqZ |
+--------------+--------------+
3 rows in set (0.01 sec)
遂に「BysqZ」を検索することができました・・・！
BysqZファンとしては感無量です・・・。ライブ行きたくなってきました。
おっと忘れてました。今のは単語一致。部分一致はいかがでしょう。
select * from data_fulltext
where match(text_ngram) against ('"BysqZ"' in boolean mode);
Empty set (13.01 sec)
あれ？ 割と時間がかかった挙句、0件・・・
どれだけ頑張りゃいい・・・
FULLTEXTインデックス × utf8mb4_bin にバグがある
いろいろ調べてみたんですが、どうも下記の条件が揃うと結果が0件になってしまうという、MySQLのバグのようです。
- collationがutf8mb4_bin
- ngramのfulltext index
- 英字を含むワードの検索
- boolean mode
関連記事：https://bugs.launchpad.net/percona-server/+bug/1689268
まさにB'zの部分一致検索の条件そのままです。しかもそのバグ、まだfixされていないようです・・・（このあたり、なにか知見がありましたらコメントいただけると嬉しいです！）
natural language modeなら大丈夫
boolean modeでなく、natural language modeだとEmpty setを回避できました。ただしこのままだと「あいまい検索」になるため、部分一致検索を実現するためには、全文検索かつlikeでもあてる、というトリッキーなクエリにします。
select * from data_fulltext
where match(text_ngram) against ('"BysqZ"') and text_ngram like '%BysqZ%' limit 3;
+--------------+--------------+
| text | text_ngram |
+--------------+--------------+
| ynmBysqZ | ynmBysqZ |
| ysl BysqZ | ysl BysqZ |
| BysqZ | BysqZ |
+--------------+--------------+
3 rows in set (0.01 sec)
※ ynm = 「#」
BysqZファンの私でもちょっとこれはさすがに、と思ってしまうクエリになってしまいましたが、FULLTEXTインデックスのメリットも享受しつつ、B'zの部分一致が実現できました。
B'z恐るべし
これでなんとかB'zの単語一致・部分一致ともに実現できました。上記の通り変わったクエリになってしまいましたので、気になる場合は、natural language modeで十分なケースもあるかと思いますので、そこは要件次第かなと思います。
さらに、Collation=utf8mb4_binはバグも残っており、その点注意を要します（まだ見ぬバグを踏むかもしれない）。utf8mb4_general_ciに戻し、「B'z」等の記号を含むワードの検索時はlikeであてる、というルールを課す、でもよいかもしれません。消去法でイケることもあるらしいですから、先方の要望とも天秤を掛けて決めたいところですね。
おわりに
というわけで、真面目にMySQLの全文検索に取り組んでみて、非常に有用であることがわかりました。まだバグがちらほら残っていたりして発展途上な面はありますが、活用できる場面は多いのではと思います。制約も少なくないので、B'zの検索等をご検討の方はご注意ください。
なお、記号を含むワードの検索について、今回はデータの特性を生かした解決策を導きましたが(それでもだいぶパワープレイ)、他にやりようがありそうでしたら是非コメントいただきたいです。
![valuesccg](data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" width="60" height="60"><rect fill-opacity="0"/></svg>)
