KAKEHASHI Tech Blog

カケハシのEngineer Teamによるブログです。

AI在庫管理の開発チームでバックエンドエンジニアをしている沖です。今回は、AI在庫管理の医薬品検索において、MySQLの全文検索機能を使った話を紹介しようと思います。

この記事は秋の技術特集 2024の 8 記事目です。

今までの医薬品検索では満足できないユーザーがいた

AI在庫管理には、医薬品の在庫一覧画面など、医薬品名で絞り込む画面がたくさんあります。この絞り込み機能を実現するために、これまではSQLのLIKE検索を利用していました。

LIKE検索は、使い慣れたSQLを用いて部分一致検索を実現できる便利な方法ですが、前方一致検索以外ではインデックスが効かないため非常にコストのかかるクエリになってしまいます。また、複数の検索キーワードを入力された場合、入力された文字列のままでは適切に絞り込むことができません。

実際、AI在庫管理のリリース初期から、ユーザーから次のような機能を要望されていました。

例えば、"カンデサルタン錠４ｍｇ「武田テバ」" を検索する際に、"カンデサルタン　４　武田" といったキーワードで検索したい。現状、"カンデサルタン" だけで検索すると、サイズ違いやメーカー違いなどでかなりの医薬品がヒットしてしまう。

この要望を実現するため、AI在庫管理ではMySQLの全文検索機能を採用しました。ここでは、採用の理由やいくつかの検討事項についてまとめます。なお、本記事ではAurora MySQL version 3（MySQL 8.0互換）を使用しており、ストレージエンジンはInnoDB を使用しています。

なぜMySQLの全文検索機能を採用したのか

上述したユーザー要望を叶えるために、これまで通りLIKE検索を用いて実装しようとすると、そのクエリは下記のようになります。入力されたキーワードをスペースで分割してWHERE句に渡す処理は、アプリケーションで行うことを想定しています。

SELECT  *FROM medicinesWHERE  nameLIKE'%カンデサルタン%'AND nameLIKE'%４%'AND nameLIKE'%武田%';

これで期待する結果を取得することはできるのですが、元々遅かったクエリがさらに遅くなることが容易に想像できるため、他の方法を探す必要がありました。

MySQLの全文検索機能以外にも、ElasticsearchやAWSのOpenSearchといった全文検索エンジンを利用する方法もありましたが、今回は下記の理由からMySQLの全文検索機能を採用しました。

検索対象がマスタデータである医薬品のみであり、件数がさほど多くない（せいぜい数万レコード）。
検索項目が医薬品名とメーカー名だけであり、各項目の文字数も長くない。
新しく全文検索エンジンを導入すると費用面や運用面のコストが発生し、今回のユースケースとしてはオーバースペックと考えられる。

全文検索機能を導入する

それでは早速、全文検索機能を導入していきましょう。下記の3つのステップで説明していきます。

全文検索インデックスを付与したテーブルの作成方法
作成したテーブルにおける、全文検索インデックスを使用した検索方法
データを最適な状態に保つためのメンテナンス方法

全文検索インデックスを付与したテーブルを作成する

今回は、医薬品名の他にメーカー名でも検索ができるようにします。医薬品名の中にメーカー名が含まれているものもありますが、そうでない医薬品もあるため、医薬品テーブル（medicines）と医薬品メーカーのテーブル（medicine_makers）の検索対象項目を保持する新しいテーブルを用意します。

CREATETABLE medicine_search_keywords (  FTS_DOC_ID BIGINT UNSIGNEDNOTNULL AUTO_INCREMENT PRIMARY KEY,  medicine_idINTEGER(11)NOTNULLUNIQUE KEY,  medicine_nameVARCHAR(120) COLLATE utf8mb4_general_ciNOTNULL,  medicine_maker_nameVARCHAR(60) COLLATE utf8mb4_general_ci,  FULLTEXTINDEX idx_fulltext_medicine_name_medicine_maker_name    (medicine_name, medicine_maker_name)WITH PARSER ngram);

ポイントとしては下記です。

FTS_DOC_ID
- 全文検索インデックスを付与するテーブルにはFTS_DOC_ID というカラムが必要になります。定義していない場合でも自動で作成されます。
全文検索インデックス
- medicine_name,medicine_maker_name の組合せに対して全文検索インデックスを作成しています。
- 通常の複合インデックスと異なり、複数カラムに対する全文検索インデックスは、一部カラムのみを検索対象にすることはできません。そのため、例えば医薬品名のみを全文検索の対象としたい場合は、別のインデックスを作成する必要があります。
パーサー
- 全文検索インデックスのパーサーとしてngram を指定しています。パーサーについては後述します。
照合順序
- 全文検索インデックスの対象カラムについて照合順序（COLLATE utf8mb4_general_ci）を指定しています。照合順序については後述します。

パーサー

全文検索インデックスでは、検索対象の文字列をトークン化してインデックスを付与します。このトークン化をどのように行うかを決めるのがパーサーというもので、例えばデフォルトのパーサーでは "カンデサルタン錠４ｍｇ「武田テバ」" という文字列は、下記のように分割されます。

カンデサルタン錠４ｍｇ
武田テバ

これは、英語など世界の言語の大半が言葉の区切りに空白を入れる書き方（分かち書き）をするため、デフォルトのパーサーも空白や記号を区切り文字としてトークン化することが原因なのですが、これでは "カンデサルタン錠４ｍｇ" と入力しないとヒットせず、元々要望されていた入力ではヒットしません。

そこで日本語においては、別のパーサーを利用することになります。有名な日本語用のパーサーとして MeCab というパーサーがありますが、Auroraでは利用することができないため、今回は N-gram パーサーを利用します。

N-gram パーサーは、指定されたトークンサイズ毎に分割してインデックスを付与するもので、N = 2 の場合は下記のように分割されます。

カン
ンデ
デサ
...
錠４
４ｍ
ｍｇ
...

この場合も、"４" というキーワードではヒットしないこととなるため、今回はトークンサイズを 1 とするようにしました。一般的に、トークンサイズを 1 とするのは、インデックスサイズが肥大化するためあまり推奨されていませんが、今回はレコード数や各項目の文字数が多くないため、許容範囲と判断しました。

照合順序と正規化

検索機能を実際にユーザーに提供する場合、例えば下記のような要望を満たす必要があります。

"カンデサルタン 4" のように、半角数字で入力した場合も "カンデサルタン錠４ｍｇ「武田テバ」"（"４" は全角）がヒットするようにしたい。
"かんでさるたん" のように、ひらがなで入力した場合もヒットするようにしたい。
"４ＭＧ" のように、大文字で入力した場合もヒットするようにしたい。

この要望を叶えるための方法として、照合順序（Collation）の変更が挙げられます。照合順序は、文字をどのように比較するのかの規則で、文字列の比較やソート処理に影響します。MySQL 8.0 のデフォルトの照合順序はutf8mb4_0900_ai_ci で、この照合順序は下記のような特徴があります。

アクセントの違いを区別しない（例：「はは」「ばば」「ぱぱ」を区別しない）
大文字/小文字の違いを区別しない（例：「びょういん」と「びよういん」を区別しない）
異体字の違いを区別しない（例：「平成」と「㍻」、「0」と「〇」（漢数字のゼロ）を区別しない）

なお、AI在庫管理でデフォルトの照合順序として使用しているutf8mb4_bin はすべての文字を区別し、utf8mb4_general_ci は英字の大文字/小文字の違いと絵文字等は区別しませんが、他の文字は区別します。

また、照合順序ではなく、事前に検索対象の文字列を正規化して対応する方法もあります。例えば、半角を全角に置換し、ひらがなをカタカナに置換することで、半角/全角やひらがな/カタカナの違いを無視して検索することができます。

AI在庫管理では下記の理由から、照合順序としては比較的厳密に区別するutf8mb4_general_ci を用いることにしました。

要件に合致した、最適な照合順序を選択することが難しい。
正規化の方が、要件に合わせてより柔軟に対応可能。

なお、一番厳密に比較するutf8mb4_bin については、連続する英大文字がヒットしないという報告があり、実際に期待する挙動とならないことを確認したため避けています。

全文検索インデックスを使用して検索する

続いて、実際に検索する場合のクエリについて確認していきましょう。MySQLの全文検索機能には、下記の3つのモードが存在します。

自然言語全文検索（NATURAL LANGUAGE MODE）
ブール全文検索（BOOLEAN MODE）
クエリー拡張全文検索（NATURAL LANGUAGE MODE WITH QUERY EXPANSION）

自然言語全文検索とクエリー拡張全文検索は、複数のキーワードを指定した場合はOR条件での検索となるなど、少しでも関連のありそうな結果を返す目的で使用されます。今回は、条件に合致するものをできるだけ絞り込みたいという要件のため、AND条件での検索が可能なブール全文検索を使用します。

ブール全文検索におけるクエリは、下記のようになります。

SELECT  *FROM  medicine_search_keywordsWHERE  MATCH (medicine_name, medicine_maker_name)  AGAINST ('+カンデサルタン +４ +武田'INBOOLEANMODE);

AGAINST 句に指定している'+カンデサルタン +４ +武田'が検索キーワードで、+ の演算子はAND条件（指定したキーワードを必ず含む）であることを意味します。なお、演算子なしの場合はOR条件（指定したキーワードのいずれかを含む）、- の演算子はNOT条件（指定したキーワードを含まない）など、他にもいくつかの演算子が存在します。

今回は検索用のテーブルを用意しており、このテーブルにはIDしか保持していないため、実際の検索では下記のようなクエリを実行することになります。なお、ユーザーが入力したキーワードをそのまま使用するのではなく、事前に正規化やスペースでの分割をした上で、クエリを構築する必要があります。

SELECT  *FROM  medicinesWHERE  idIN (SELECT      medicine_idFROM      medicine_search_keywordsWHERE      MATCH (medicine_name, medicine_maker_name)      AGAINST ('+カンデサルタン +４ +武田'INBOOLEANMODE)  );

データを最適な状態に保つために

検索用のテーブルに登録するための元データとなる医薬品マスタは定期的に更新されているため、検索用テーブルのデータも適宜最新化する必要があります。AI在庫管理では、マスタ更新処理の後に検索用テーブルの差分アップデート処理を追加することで、ほぼタイムラグなく最新化ができています。

医薬品の追加だけであればこれで完結なのですが、既存の医薬品の更新（医薬品名やメーカー名の変更）については少し注意が必要です。更新の場合でも、FTS_DOC_ID については、既存IDの削除＆新規IDの登録といった挙動となります。また、削除されたFTS_DOC_ID のインデックスは、レコードを削除したタイミングでは削除されません。そのため、次に示す最適化の手順を踏まないと、削除済みレコードに対するインデックスが半永久的に残り、パフォーマンスも劣化します。

インデックスの最適化はinnodb_optimize_fulltext_only=ON とした上で、下記のコマンドを実行します。