概要
とある案件のバックエンド開発時にAurora Serverless V2 for MySQLにて挑戦として、FULLTEXT INDEXによる全文検索を実装することになった際に苦労した2点を備忘録として、残しておこうと思います。
FULLTEXT INDEXとは（簡単に）
通常のインデックスではカラムの値全体にインデックスが貼られるため、全文検索ではそれらが適用されることはなく速度向上に期待ができません。
加えて、全文検索を行う際にはMySQLではMATCH関数を使用しますが、FULLTEXT INDEXを貼ることが条件となります。
FULLTEXT INDEXはカラムの値をn-gramという設定値の文字数で分割して、インデックスを作成します。
※例：「テストです」→「テス, スト, トで, です」
そして、LIKE検索ではFULLTEXT INDEXは適用されないため、検索速度的にもこちらが
有利というメリットがあります。
実現したかったこと
ユーザが自由に文字を検索ワードを入力し、そのワードを含む検索結果を返すという単純な全文検索要件になります。
ここでは、FULLTEXT INDEXは IN BOOLEAN MODEを使用する形になります。
IN BOOLEAN MODEでの検索挙動は、検索ワードと完全一致するワードが含まれるレコードが返されます。
SELECT * FROM members WHERE MATCH (name) AGAINST ('テスト' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
| 1 | テスト太郎 |
+----+--------------------------------------------+
| 2 | 田中テスト |
+----+--------------------------------------------+
※ その他各種MODEに関しての説明は省きます。
苦労１（検索ワード「Aa」だとヒットしない)
全文検索対象のレコードは下記とします。
+----+--------------------------------------------+ | id | name | +----+--------------------------------------------+ | 8 | AaBbCcDdEeFfGgHhliJjKkLIMMNnOoPpQgRrSsTt | +----+--------------------------------------------+
普通に検索します。
SELECT * FROM members WHERE MATCH (name) AGAINST ('Aa' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
これでは、ヒットしません。はぁ？ですね。
ただ、下記の場合だとヒットします。余計はぁ？です。
SELECT * FROM members WHERE MATCH (name) AGAINST ('Bb' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
| 8 | AaBbCcDdEeFfGgHhliJjKkLIMMNnOoPpQgRrSsTt |
+----+--------------------------------------------+
なぜ、どちらも値として含まれているのに「Aa」ではヒットせず「Bb」ではヒットするのか。
もう少し踏み込むとこれは、「Bb」だからヒットするのではなく「Aa」だからヒットしません。
ちなみに、照合順序はデフォルトの「utf8mb4_0900_ai_ci」を使用してます。
そのため、「A」と「a」は区別されません。
原因はMySQLの「ストップワード」が起因しています。
ストップワードとは、設定されたワードに対してのインデックス作成をスキップします。
MySQLがデフォルトで「INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD」というテーブルに対象ワードを設定しています。
下記がストップワード一覧になります。
a / about / an / are / as / at / be / by / com / de / en / for / from / how / i / in / is / it / la / of / on / or / that / the / this / to / was / what / when / where / who / will / with / und / the / www
対象ワードを見ると、英語圏だとかなり頻繁に使用されるワードが設定されており、インデックスが肥大化して検索パフォーマンスを落とさないための設定だということがわかります。
親切心ですが、今回の場合はありがた迷惑です。
そして、こちらの一覧見て照合順序を考えると、先ほど「Aa」ではヒットせず「Bb」でヒットした原因がわかるかと思います。
そうです、照合順序によって「a」と「A」は区別されないため、同一の文字での検索と判断された上でストップワードに「a」が設定されています。
これにより検索ワードの「Aa」はインデックスが作成されず、検索が無視されました。
解決策として、今回はAWSのAurora Serverless を使用しているため、パラメータグループにて「innodb_ft_enable_stopword」に0を設定することで、ストップワードでのインデックス制御をOFFにできます。
現状のストップワードの設定状態は下記コマンドで確認できます。
SHOW VARIABLES LIKE 'innodb_ft_enable_stopword';
苦労2（各種記号付き検索）
まずテーブルには下記のデータが入ってます。
+----+--------------------------------------------+ | id | name | +----+--------------------------------------------+ | 1 | テスト太郎! | +----+--------------------------------------------+
今回の全文検索は2文字から検索可能としています。
それを前提として下記のような検索をしたとします。
SELECT * FROM members WHERE MATCH (name) AGAINST ('郎!' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
そう、これではヒットしないのです。
そこで、検索文字を3文字にするとヒットしました。
SELECT * FROM members WHERE MATCH (name) AGAINST ('太郎!' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
| 1 | テスト太郎! |
+----+--------------------------------------------+
文字数の設定がおかしいのかと最初は思いましたが、そうではなく全文検索では記号が無視されているようです。勘弁して欲しいですね。
なので、最初の「郎!」という検索文字は、実際には「郎」という1文字での検索と判断されています。
そして、3文字で検索した「太郎!」は実際には「太郎」と2文字での検索扱いです。
さらにこの現象は、半角全角記号どちらでも起こる問題でした。
では、どのように記号を含めて検索を可能とするか。
方法1 (アスタリスクへの変換)
下記のように、記号を検知してアスタリスクに変換する方法です。
今回使用しているBOOLEAN MODEではこれが可能です。
ただ、ワイルドカードになるので検索漏れは回避できても、余分な検索結果が出力されます。
記号が複数あれば、それはなおさらです。
そのため、こちらは却下です。
SELECT * FROM members WHERE MATCH (name) AGAINST ('太郎*' IN BOOLEAN MODE);
方法2(プログラム側で検索文字から記号を除く)
バックエンド側で検索文字列を受け取り次第、リクエストの文字列から記号を取り除くという手法です。
下記テーブルデータです。
+----+--------------------------------------------+ | id | name | +----+--------------------------------------------+ | 1 | テスト(太郎) | +----+--------------------------------------------+
検索文字列：テスト(太郎)
記号除外：テスト太郎
SELECT * FROM members WHERE MATCH (name) AGAINST ('テスト太郎' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
はい、ヒットしません。。もうええてって感じです。
検索文字列では、記号は無視されますが、テーブルデータの記号は無視されないからですね。
そのため、下記の場合はヒットします。
下記テーブルデータです。
+----+--------------------------------------------+ | id | name | +----+--------------------------------------------+ | 1 | テスト太郎) | +----+--------------------------------------------+
検索文字列：テスト太郎)
記号除外：テスト太郎
SELECT * FROM members WHERE MATCH (name) AGAINST ('テスト太郎' IN BOOLEAN MODE);
+----+--------------------------------------------+
| id | name |
+----+--------------------------------------------+
| 1 | テスト太郎) |
+----+--------------------------------------------+
こちらも検索文字列から記号を取り除いた状態の連続した文字が、検索対象であることがヒット条件になるので却下です。
方法3 (もうLIKE文使っちゃう)
はい、最初に言いますがこれが採用した方法です。
なんやねんと思いますよね。全文検索使ってるんですもん。わかります。
ただ、目を瞑って欲しいです。
LIKEを使うと言っても全文検索を辞めるわけではありません。
バックエンド側で、下記のように記号検知に使用する正規表現を作成します。
r"[!！\"”＂＃#＄$％%＆&'’（()）=＝\-\～〜~＾^￥¥|｜@＠`｀\[\]［］{}｛｝「」,、。＜＞?？/／＿;；+＋
:：*＊\.\\]"
そして、バックエンド側で記号を検知した場合はLIKE検索に流すという手法です。
SELECT * FROM members WHERE name like '%テスト(太郎)%' +----+--------------------------------------------+ | id | name | +----+--------------------------------------------+ | 1 | テスト(太郎) | +----+--------------------------------------------+
これで、なんとか記号検索はクリアしました。
めでたしです。
もしLIKEに流す以外の記号検索の対処法があればぜひ知りたいです！
最後に
初めて扱う全文検索でしたので、かなりヒーヒーは言わされましたが、新しい知見として、自身の力になったかと思います。
