2021-06-04

MySQLの全文検索で商品検索を作ってみた

leaf

こんにちは、SmartShoppingでスマートマットライトの開発を担当している @leafです。 先日スマートマットライトの利用イメージをより掴んでいただきやすくするために、サービスサイトのリリースを実施いたしました。

今回は、サービスサイトで商品検索機能を実装するために利用したMySQLの全文検索について紹介していきたいと思います。

全文検索とは

対象の文章に対して、指定したキーワードを探し出すことです。 私の場合は、全文検索と聞くと「Elasticsearch」をイメージしていたのですが、今回は検索機能の規模が小さく、なるべく工数を少なく進めたかったため、MySQLに用意されているFULLTEXT INDEXを利用して実装を行いました。

MySQLの全文検索

MySQLではFULLTEXT INDEXというINDEXが用意されており、これを利用することでLIKE検索と比較して、高速に対象のデータを抽出することができます。 また、完全一致だけではなく類似する文章も合わせて検索し、該当率が高いレコードから返却してもらうことが可能です。

FULLTEXT INDEXについて

FULLTEXT INDEXは、MySQLに用意されているINDEXの一種で、テキストベースのカラム(CHAR,VARCHAR,TEXT)に指定することができるINDEXです。 FULLTEXT INDEXを指定したテーブルに対して、全文検索関数を利用することで対象を抽出することができます。

FULLTEXT INDEXの指定については他のINDEXと同様に、テーブル作成時にCREATE TABLEで指定するか、ALTER TABLE・CREATE INDEXを利用して追加することができます。

# CREATE TABLE で指定した場合の例
CREATE TABLE `smartshopping_products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `maker` varchar(255) DEFAULT NULL COMMENT 'メーカー名',
  `brand` varchar(255) DEFAULT NULL COMMENT 'ブランド名',
  `title` varchar(255) DEFAULT NULL COMMENT '商品名',
  `price` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '価格',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新日時',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '作成日時',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `FT_Maker_Brand_Title` (`maker`,`brand`,`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='FULLTEXT INDEX用サンプルテーブル';

全文検索関数について

全文検索関数は、下記の構文を利用することで実行することができます。 MATCHの部分には検索対象のカラムを指定し、AGAINSTには検索したいキーワードを指定します。 MATCH (col1,col2...) AGAINST (expr [search_modifier])

実際のクエリにしてみるとこんな感じです。 ※FULLTEXT INDEXで複数カラム指定している場合は、MATCHで全てのカラムを指定しないとエラーになるので注意です。

# SELECT * FROM [テーブル名] WHERE MATCH([fulltextIndexで指定した全てのカラム]) AGAINST([検索キーワード]);
SELECT * FROM smartshopping_products WHERE MATCH(`maker`,`brand`,`title`) AGAINST('炭酸水');

複数キーワードの場合

今回作成した商品検索などで、複数のキーワードに対応したい場合は下記の通りに指定することで対応することができます。

OR検索

OR検索をしたい場合は、AGAINSTで指定するキーワードを半角スペース区切りで指定することで複数のキーワードに対応することができます。

SELECT * FROM smartshopping_products WHERE MATCH(`maker`,`brand`,`title`) AGAINST('炭酸水 レモン');

AND検索

AND検索をしたい場合は、キーワードに少し追加が必要で、AGAINSTで指定するキーワードの前に「+」をつけることで対応することができます。

SELECT * FROM smartshopping_products WHERE MATCH(`maker`,`brand`,`title`) AGAINST('+炭酸水 +レモン');

ANDとORどちらも利用する

最後に条件を複合で利用したい場合のパターンです。 指定方法はAND検索、OR検索に書いたものと同じですが、まとめたい部分を括弧でまとめてあげる必要があります。 下記の場合だと、サントリー又はアサヒが含まれている炭酸水又はレモンを含む商品が抽出されるイメージです。

SELECT * FROM smartshopping_products WHERE MATCH(`maker`,`brand`,`title`) AGAINST('+(サントリー アサヒ) +(炭酸水 レモン)');

簡単な速度検証

ここまでMySQLの全文検索について書いてきましたが、実際にLIKE検索と比較してちゃんと早いのかというところにふれていきたいと思います。 今回はサンプルで作成した下記のテーブルに、10,000件のレコードを追加して全文検索とLIKE検索の速度検証を実施してみました。

CREATE TABLE `smartshopping_products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `maker` varchar(255) DEFAULT NULL COMMENT 'メーカー名',
  `brand` varchar(255) DEFAULT NULL COMMENT 'ブランド名',
  `title` varchar(255) DEFAULT NULL COMMENT '商品名',
  `price` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '価格',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT '更新日時',
  `created_at` timestamp NULL DEFAULT NULL COMMENT '作成日時',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `FT_Maker_Brand_Title` (`maker`,`brand`,`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='FULLTEXT INDEX用サンプルテーブル';

下記が実際の実行結果になります。 多少のブレはありますが、全ての実行結果において全文検索の方が高いパフォーマンスで結果を取得することができています。 MySQL_Search_Logs

全文検索の精度を上げるためにやったこと

ここからは、実際に動かしてみたら思った通りに結果が取得できないことが色々とあったので紹介していきたいと思います。

INDEXサイズの調整

FULLTEXT INDEXはデフォルトだと対象の文章を2文字区切りでINDEXしておき、一部でもキーワードと一致していれば取得対象となるような仕様になっています。

例 : ソフトドリンク -> ソフ,フト,トド,ドリ,リン,ンク

そのため、検索キーワードが「水」などのように1文字で指定されてしまうとINDEXに該当がなくなってしまい、結果を取得することができませんでした。

あまりいい解決方法ではないのですが、FULLTEXT INDEXで生成されるINDEXの区切りは 「my.cnf」に「ngram_token_size」を指定してあげることで変更することが可能です。

# どうしても1文字に対応するとしたらmy.cnfに下記を追加する
ngram_token_size = 1

これで検索で1文字指定された場合でも結果が取得できるようになるのですが、単純にINDEXの数が膨大になるので、どちらを優先したいかで判断することになりそうです。

文字コードの設定で検索の幅を広げる

今回作成した商品検索のように、大文字・小文字やひらがな・カタカナが対象の文章に混ざっている場合は、それぞれを同一視したいことがあると思います。 その場合は、テーブルやカラムに対して特定の文字コードを指定することで検索に幅を持たせることができます。

utf8_general_ci

  • 大文字と小文字を同一視する

utf8_unicode_ci

  • 大文字と小文字を同一視する
  • 半角と全角を同一視する
  • ひらがなとカタカナを同一視する
  • 濁音と半濁音を同一視する

上記の通り、文字コードを指定することである程度の幅を持たせることができますが、「utf8_unicode_ci」を指定するとかなり条件が緩くなってしまうので注意が必要になりそうです。

ブール全文検索で完全一致

MySQLの全文検索では、INDEXの一部でも該当すれば対象として抽出してくれますが、検索対象の文章が多い場合は該当件数が多くなってしまい、結果の中から想定の情報を探すことが大変になってしまうことがあります。 その場合はIN BOOLEAN MODE修飾子を利用することで解決できます。

IN BOOLEAN MODEはAGAINSTの指定に追加で定義することができ、これを指定することでINDEXに対して完全一致した結果のみを取得することが可能となります。 実際のクエリは下記の通りです。

SELECT * FROM smartshopping_products WHERE MATCH(`maker`,`brand`,`title`) AGAINST('炭酸水' IN BOOLEAN MODE);

また、IN BOOLEAN MODEを利用している場合は、特定のキーワードの前に「-」をつけることで、キーワードの除外検索をすることもできるようです。

SELECT * FROM smartshopping_products WHERE MATCH(`maker`,`brand`,`title`) AGAINST('炭酸水 -レモン' IN BOOLEAN MODE);

記号に関する整備

記号に関してはMySQL FULLTEXTの仕様で、記号が無視されてしまっているようです。 一応エスケープなどを利用することである程度回避はできそうですが、可能であれば対象の文章をDBに反映する前に整備してあげた方がよさそうだと思ってます。

全文ストップワード

MySQLの全文検索にはデフォルトでストップワードが指定されており、それに該当する文字列は無視されて検索が実行されてしまいます。 登録されているストップワードは下記のクエリで参照することができます。

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

今回は特に変更しなかったのですが、ストップワードを変更したい場合は設定を変更することでストップワードの参照テーブルを変更できるそうです。

辞書でキーワードのゆらぎに対応したい

文字コードの指定である程度のゆらぎに対応することができるのですが、ひらがなと漢字や記号を含む文字列など、より柔軟に対応したいと思い探していたのですが、今回はあまりいいものが見つけられませんでした。 最終的には、検索対象とは別に辞書用のテーブルを作成し、全文検索実行前にキーワードを変換してあげることで対応しています。

これについてはもっといい方法がないか模索していければと思ってます。

まとめ

今回初めてMySQLの全文検索を利用してみましたが、細かい調整に少し時間がかかったものの基本的にはINDEXを追加するだけで検索を実現することができたので大幅に工数を削減することができたと思います。 より精度の高い検索を求める場合は別の手法を考えなければいけませんが、今回のように簡単な検索をしたい場合は選択肢に入ってくると思いました。 MySQLの全文検索もまだ利用していない設定が色々とありそうなので今後もある程度の精度を担保することができるように模索していきたいと思います。

最新の記事