こんにちは。
クラシル開発部、バックエンドエンジニアの松嶋です。
delyに入社してから約3年間、私はSREチームに所属していましたが、昨年10月にバックエンドに転向しました。バックエンドに転向してからは、主にクラシルアプリの公式レシピおよびCGMコンテンツの検索機能に関する開発・改善に取り組んでいます。
クラシルは、2016年2月にサービスを開始してから、管理栄養士監修の「誰でも安全に・おいしい料理を作ることができるレシピ動画」を5万件以上提供してきました。
昨年12月には、クラシルのブランドリニューアルを行い、今後はシェフや料理研究家を中心としたクリエイターとともに多様化したユーザーの食の好みや課題解決に応えられるよう、幅広い食のコンテンツを提供するプラットフォームを目指しています。
ブランドリニューアルの詳細に関しては、こちらを御覧ください。
このような背景から、私たちはクリエイターのコンテンツもクラシルアプリで検索でき、お気に入りのレシピをストックできるように、UGC検索エンジンのMVP開発に着手することになりました。 自分たちが運営するサービスに検索機能を導入する場合、DBのLIKE検索で簡易的な検索機能を実現する、またはElasticsearchのような全文検索エンジンを導入する方法を思いつく方が多いと思います。
しかし、今回のMVP開発において私たちが選択したのはMySQLの全文検索機能です。 MySQL InnoDBのFULLTEXTインデックスは、MySQL 5.6の段階では様々な制約があり、日本語環境では使うことが難しい言われていましたが、MySQL 5.7ではそのような事情が大幅に改善され、日本語環境でも適用できるようにパーサーが変更できるようになりました。*1
昨年、クラシルが使用しているAurora MySQLのバージョンを5.6から5.7にアップグレードしたことがきっかけで、「今回のUGC検索のMVPを実施する上で使えないか？」という話が出たため、開発環境での検証を経て導入することに決定しました。（2023年6月現在、クラシルが使用しているAurora MySQLのバージョンは、既に8.0までアップグレードされています。）
この記事では、MySQL全文検索を導入・運用した経験から得られたTipsやメリット及びデメリットについて紹介したいと思います。
MySQLの全文検索とは
MySQLの全文検索は、検索対象のカラムにFULLTEXTタイプのインデックスを貼るだけで簡単に実現することが可能です。また、パーサーは、デフォルトのngramとインストール可能なMeCabのどちらかを選択できます。しかし、AWS環境でAurora MySQLやRDS MySQLを使用している場合は、ngramパーサーしか選択できないことに注意してください。
以下にngramパーサーを用いてフルテキストインデックスを貼る例を示します。
# 単一カラムにインデックス貼る場合 alter table videos add FULLTEXT index ngram_idx (title) with parser ngram; # 複数カラムにインデックス貼る場合 alter table videos add FULLTEXT index ngram_idx (title, introduction) with parser ngram;
全文検索は以下の3種類があり、これらの中から1つをmodifierとして指定できます。
- 自然言語検索：検索文字列を人間の自然な単語のフレーズとして扱う（デフォルト）
- boolean検索：完全一致検索（大文字、小文字、ひらがな、カタカナ完全区別する）、特殊演算子（+,-,*）を使ってAND検索やOR検索が可能。
- クエリ拡張検索：自然言語検索の拡張版。自然言語検索が最初に実行され、その結果、最も関連のあるレコードの単語が検索文字列に追加され、再検索を行う。
全文検索を実行する際には、以下のようにMATCH() AGAINST()シンタックスを使用します。
# title, introductionで複合インデックスを貼り、自然言語検索を実行する場合 select title from recipes where match (title, introduction) against ("フレンチトースト" in natural language mode);
全文検索の種類の選択
上述の通り、MySQLの全文検索には3種類のモードが存在します。完全一致させたいのであれば、boolean検索一択になるかと思いますが、自然言語検索も検索文字列をダブルクォートで囲むことで、検索対象カラムにその文字列が含まれているレコードをマッチさせることが可能と言われています。そのため、自然言語検索もboolean検索と同等の検索結果が得られるのはないかと考え、検索結果を比較してみました。
MATCH() 関数は、返り値として適合度（relevance value）を数値として返します。この適合度は、行（ドキュメント）内の単語数、行内のユニーク単語数、コレクション内の単語の総数、及び特定の単語を含む行数に基づいて計算されます。すなわち、適合度が高いほど検索文字列と類似性の高いレコードであることが分かります。
この適合度をscoreとして扱い、まずは自然言語検索で関連性が高いと判断されたレシピTOP10を見ていきましょう。
「ロールキャベツ」、「ピーマン 肉詰め」を検索してみましたが、検索意図に合ったレシピが上位10件に含まれており、検索結果として良さそうです。
# 「ロールキャベツ」を検索
mysql> select
title,
match (title, introduction) against ("ロールキャベツ" in natural language mode) as score
from
recipes
where
match (title, introduction) against ("ロールキャベツ" in natural language mode)
order by
score desc
limit
10;
+------------------------------------------------------------------------------+-------------------+
| title | score |
+------------------------------------------------------------------------------+-------------------+
| とっても簡単！逆ロールキャベツ | 76.14885711669922 |
| ロールキャベツの巻き方 | 70.85739135742188 |
| 旨味ぎっしり リゾット風ロールキャベツ | 56.6859130859375 |
| のせるだけ ロールキャベツ | 47.8058967590332 |
| 巻かない コーンクリームのミルフィーユロールキャベツ | 47.8058967590332 |
| 基本のロールキャベツ | 47.8058967590332 |
| ウインナーとチーズの変わり種ロールキャベツ | 47.8058967590332 |
| コンソメ味のシンプルロールキャベツ | 47.8058967590332 |
| ロールキャベツ | 47.8058967590332 |
| トマトクリームの巻かないミルフィーユロールキャベツ | 47.8058967590332 |
+------------------------------------------------------------------------------+-------------------+
# 「ピーマン 肉詰め」を検索
mysql> select
title,
match (title, introduction) against ("ピーマン 肉詰め" in natural language mode) as score
from
videos
where
match (title, introduction) against ("ピーマン 肉詰め" in natural language mode)
order by
score desc
limit
10;
+-----------------------------------------------------------------------------------------------------------------+--------------------+
| title | score |
+-----------------------------------------------------------------------------------------------------------------+--------------------+
| 【後藤シェフ】ピーマンの肉詰め＆かぼちゃのポタージュ＆ペペロンチーノライス | 66.87279510498047 |
| 【後藤シェフ】ピーマンの肉詰め | 66.87279510498047 |
| ピーマンの肉詰め | 56.90451431274414 |
| 種ごとピーマンの肉詰め | 56.90451431274414 |
| ひとくちピーマンの肉詰め | 53.58710479736328 |
| 五目春雨のピーマンカップ詰め | 50.7970085144043 |
| とろーりチーズがたまらない！まるごとピーマンの肉詰め | 50.368736267089844 |
| 大豆ミートでピーマンの肉詰め | 50.154598236083984 |
| ピーマンの肉詰め カレー風味 | 50.154598236083984 |
| ピーマンのご飯入り肉詰め | 50.154598236083984 |
+-----------------------------------------------------------------------------------------------------------------+--------------------+
続いて、私の好きな料理でもある「キッシュ」で検索したところ、約半数はキッシュのレシピが表示されましたが、下位4つはキッシュと関係のないレシピがヒットしてしまいました。おそらく、マッシュルームやラディッシュがキッシュと近しい言葉であると判断されているのでしょう。
mysql> select
title,
match (title, introduction) against ("キッシュ" in natural language mode) as score
from
videos
where
match (title, introduction) against ("キッシュ" in natural language mode)
order by
score desc
limit
10;
+--------------------------------------------------------------------------------------------------------+--------------------+
| title | score |
+--------------------------------------------------------------------------------------------------------+--------------------+
| 豆乳で和風キッシュ | 28.9677734375 |
| かぼちゃとカリフラワーのパンキッシュ | 28.9677734375 |
| 北海道アスパラガスとベーコンの簡単キッシュ | 28.9677734375 |
| 彩り夏野菜の パンキッシュ | 28.9677734375 |
| アスパラベーコンのバゲットキッシュ | 28.9677734375 |
| イングリッシュマフィンで ズッキーニのフラワーパンキッシュ | 27.980960845947266 |
| 【マッシュルームトーキョー】マッシュルームとチキンのハニーマスタード | 21.8929500579834 |
| 【マッシュルームトーキョー】マッシュルームあんかけ和風ハンバーグ | 21.8929500579834 |
| 【マッシュルームトーキョー】マッシュルームの炊き込みご飯と味噌汁 | 21.8929500579834 |
| ラディッシュとマッシュルームのバター醤油炒め | 21.8929500579834 |
+--------------------------------------------------------------------------------------------------------+--------------------+
boolean検索で同じく「キッシュ」を検索したところ、TOP10は全てキッシュのレシピであること確認できました。
mysql> select
title,
match (title, introduction) against ("キッシュ" in boolean mode) as score
from
videos
where
match (title, introduction) against ("キッシュ" in boolean mode)
order by
score desc
limit
10;
+---------------------------------------------------------------------------------------+--------------------+
| title | score |
+---------------------------------------------------------------------------------------+--------------------+
| 冷凍パスタの簡単おかず ハムカップdeパスタキッシュ | 36.209716796875 |
| 豆乳で和風キッシュ | 28.9677734375 |
| かぼちゃとカリフラワーのパンキッシュ | 28.9677734375 |
| 北海道アスパラガスとベーコンの簡単キッシュ | 28.9677734375 |
| 彩り夏野菜の パンキッシュ | 28.9677734375 |
| アスパラベーコンのバゲットキッシュ | 28.9677734375 |
| チーズたっぷり じゃがいもとベーコンのキッシュ | 28.9677734375 |
| イングリッシュマフィンで ズッキーニのフラワーパンキッシュ | 27.980960845947266 |
| キャベツの食パンキッシュ | 21.725830078125 |
| たっぷりきのこのキッシュ | 21.725830078125 |
+---------------------------------------------------------------------------------------+--------------------+
自然言語検索は、完全一致しなくとも検索文字列に近しいレコードを返してくれるため、boolean検索のデメリットを補うことができると感じました。しかし、検索文字列によっては検索意図から外れる結果になることがあるため、今回は意図通りの検索結果が得られやすいboolean検索を選択しました。 検索ヒット率を考慮する場合、最初にboolean検索を実行し、ヒットしなかった場合には自然言語検索で再検索を実行すると良いかもしれません。ただし、2回検索を実行する点でパフォーマンスが悪化してしまう懸念があります。
クエリ拡張検索でも、同じく「キッシュ」で検索してみましたが、自然言語検索及びboolean検索は0.01-0.02秒程度で検索結果が返ってくるのに対して、クエリ拡張検索では2-3分程度かかってしまい、加えて検索意図と全く異なるレコードが返ってきたため、却下しました。
mysql> select
title,
match (title, introduction) against (
"キッシュ" in natural language mode with query expansion
) as score
from
videos
where
match (title, introduction) against (
"キッシュ" in natural language mode with query expansion
)
order by
score desc
limit
10;
+--------------------------------------------------------------------------------------------+-------------------+
| title | score |
+--------------------------------------------------------------------------------------------+-------------------+
| 豆乳で作ったヨーグルトで和風アボカド冷製パスタ | 4640.31787109375 |
| 紅茶が香る りんごがのったふわふわマフィン | 4060.578369140625 |
| フライパン1つで完成！こってりたっぷり豚の角煮風 | 3647.45849609375 |
| とろうま ハーブシュリンプとナスのとろたまチリソース | 3316.906494140625 |
| さわやかな酸味 北海道の秋鮭とポテトのレモンクリーム煮 | 3312.131103515625 |
| 食べ応え抜群！ハーブシュリンプとニラのカリカリもっちりチヂミ | 3130.677490234375 |
| 【名古屋】焦がしバターでやみつき！ ガリバタ肉野菜正麺 | 3061.77734375 |
| 【仙台】焦がしバターでやみつき！ ガリバタ肉野菜正麺 | 3059.89990234375 |
| 【北海道】焦がしバターでやみつき！ ガリバタ肉野菜正麺 | 3059.4404296875 |
| 【長野】焦がしバターでやみつき！ ガリバタ肉野菜正麺 | 3058.764892578125 |
+--------------------------------------------------------------------------------------------+-------------------+
10 rows in set (2 min 35.11 sec)
FULLTEXTインデックス設計と照合順序の選択
今回のMVP開発の要件は、コンテンツのタイトル、キャプション、およびクリエイター名を検索対象のカラムとし、ひらがな、カタカナ、大文字、小文字などの表記ゆれに対応することでした。
使用するboolean検索は、大文字、小文字、ひらがな、カタカナを完全区別するため、検索ヒット率が低下する可能性があります。そのため、デフォルトの照合順序であるutf8mb4_general_ciから変更する必要がありました。既存のカラムの照合順序を変更するよりも、全文検索用のカラムを新設し、そのカラムの照合順序を変更する方がリスクが少ないと考えました。全文検索用カラムの照合順序にはutf8mb4_unicode_ciを選択しました。utf8mb4_unicode_ciは、濁音・破裂音の区別などもなくなりますが、許容範囲としました。
全文検索用のカラムは、タイトル、キャプション、及びクリエイター名のそれぞれに別々のカラムを作成して、それらをマルチインデックスにする予定でした。しかし、検索対象のカラムは増減する可能性があること、また、FULLTEXTのマルチインデックスを貼るよりも、1つのカラムにインデックスを貼った方がレスポンスが早いことが検証により判明しました。従って、新設する全文検索用のカラムは1つにし、既存のタイトル、キャプション、及びクリエイター名の各カラムの中身をCONCATしたものをコピーすることにしました。
| 回数 | 2カラムに対してインデックスを貼った場合 (s) | 1カラムに対してインデックスを貼った場合(s) |
|---|---|---|
| 1 | 1.30 | 0.35 |
| 2 | 0.55 | 0.27 |
| 3 | 0.55 | 0.27 |
| 4 | 0.39 | 0.28 |
| 5 | 0.39 | 0.25 |
Railsで照合順序の指定、FULLTEXTインデックスを貼る場合は以下のようにマイグレーションファイルを記載します。add_indexを使用してFULLTEXTタイプのインデックスを作成することもできますが、パーサーの指定ができません。そのため、直接SQL文をマイグレーションファイルに書く必要があります。
class AddColumnsToVideos < ActiveRecord::Migration[6.1] def change add_column :videos, :full_text_search, :text, collation: 'utf8mb4_unicode_ci' execute 'alter table videos add FULLTEXT index index_recipes_on_full_text_search (full_text_search) with parser ngram' end end
Railsで全文検索を実現するための実装例
ここからは、Railsで全文検索機能を実装するために考慮した点について説明していきます。
まず、全文検索に使用するモデルのインスタンスメソッドは、以下のように定義しました。
def self.full_text_search(keyword) ngram_words = generate_ngram_words(keyword) boolean_mode = 'match (search_full_text) against (? in boolean mode)' search_text = ngram_words.map { |key| "+#{key}" }.join(' ') sanitize_sql = sanitize_sql_array(["*, #{boolean_mode} as score", search_text]) where("#{boolean_mode} and #{boolean_mode} > 10", search_text, search_text).select(sanitize_sql) end def self.generate_ngram_words(keyword) keywords = keyword.split ngram_words = [] keywords.each do |item| words = item.chars words.each.with_index(1) do |word, i| ngram_words << (word + words[i]) unless words.size == i end end ngram_words end
少し複雑になっていますが、検索文字列をngram_token_sizeに合わせた文字数に分割し、AND 検索をするために +演算子を使用しています。今回は、ngram_token_size=2であるため2文字ごとに検索文字列を区切っています。
実際のMySQLのクエリに置き換えると以下のようになります。
SELECT *, MATCH (search_full_text) AGAINST ('+作り +り置 +置き +お弁 +弁当' IN BOOLEAN MODE) AS score FROM cards WHERE publish_status = 'published' AND MATCH (search_full_text) AGAINST ('+作り +り置 +置き +お弁 +弁当' IN BOOLEAN MODE) AND MATCH (search_full_text) AGAINST ('+作り +り置 +置き +お弁 +弁当' IN BOOLEAN MODE) > 10
このように検索文字列を分割して渡す理由は、MySQLのInnoDBにおけるFULLTEXTインデックスが転置インデックスの設計に基づいているためです。ngram_token_sizeで指定した文字数以外の検索文字列が渡ってきた場合、パフォーマンスが低下することが検証中に判明したため、ngram_token_sizeにあわせて分割するようにしました。その結果、2秒かかっていたクエリが0.6s程度に短縮されました。
Rspecで全文検索のテストを行うときの注意点
通常通り、Rspecでテストを書き実行すると、全文検索に関連するテストは軒並み失敗します。これは、Railsがngramに対応しておらず、マイグレーションを実行しても、schemaファイルにパーサーの記述が反映されないためです。私たちのRspecは、schemaファイルを元にしてテストDBが作成されているため、パーサーの指定が反映されず、全文検索を実行しても何のコンテンツも返ってきませんでした。
テストDBを作成する際に、schemaファイルではなく、マイグレーションファイルを元にすることもできますが、全体のSpecにも影響があるため、この方法は見送りました。
代わりに、DatabaseCleanerを使用して、全文検索のテスト前にFULLTEXTインデックスを再構築するようにしました。通常は、テスト実行前にインデックスを再構築すると、トランザクションの外側でDBデータが作成され、残ってしまうことがありますが、DatabaseCleanerを使用すると、残ってしまったデータを綺麗に削除してくれます。
実装例は以下の通りです。clean_database=trueの場合のみtruncationが実行されます。
# rails_helperの設定 config.before(:each, clean_database: true) do DatabaseCleaner.strategy = :truncation DatabaseCleaner.start end config.after(:each, clean_database: true) do DatabaseCleaner.clean end # 実際のspec context '公式レシピが存在しないとき', clean_database: true do before do recreate_indexes_with_ngram_parser end let(:params) { { query: '麻薬卵', page_size: 3, next_page_key: nil } } it 'UGCコンテンツが返ること' do subject expect(response).to have_http_status(200) expect(response_data.map { |obj| obj['type'] }.uniq.sort).to eq(["videos", "cards"]) expect(response_json['meta']['total-count']).to be >= 3 end def recreate_indexes_with_ngram_parser ActiveRecord::Base.connection.execute('drop index index_cards_on_full_text_search on cards') ActiveRecord::Base.connection.execute('drop index index_videos_on_full_text_search on videos') ActiveRecord::Base.connection.execute("alter table cards add FULLTEXT index index_cards_on_full_text_search (full_text_search) with parser ngram") ActiveRecord::Base.connection.execute("alter table videos add FULLTEXT index index_videos_on_full_text_search (full_text_search) with parser ngram") end end
運用上で発覚した課題
Auroraはinnodb_ft_result_cache_limitを変更できない
MySQL innoDBは、各全文検索クエリ、またはスレッドごとに検索結果のキャッシュ上限値 (innodb_ft_result_cache_limit) を設定しています。つまり、テーブルのレコード数が増えると、全文検索のクエリ結果も比例して大きくなり、必要なキャッシュサイズが増えていくため、メモリを過剰消費しないように制限されています。しかし、Bug#86036に記載されているように、このパラメータは上限の最大値が4GBしかないため、大規模なテーブルの場合、この上限値を超える可能性があります。*2
Auroraの場合、このパラメータのデフォルト値は2GBであり*3、変更可能なパラメータのように見えますが、Aurora MySQL 5.7のパラメータグループには存在していません。(*)AWSサポートに問い合わせたところ、Auroraではinnodb_ft_result_cache_limitを変更できないとのことでした。ただし、AuroraではなくRDS MySQLを使用している場合は、このパラメータを変更することができます。
調査段階で4GBまでの上限があることは認識していましたが、Auroraでこの値を変更できないことは把握しておらず、2GBのままだと想定より早くキャッシュエラーが返ってくるようになりました。テーブルレコード数やデータ量にも依存しますが、私たちの場合、約74万レコードに達したタイミングでこのキャッシュエラーが発生するようになりました。
ただし、エラーが発生する頻度は稀であったため、まずは暫定的な対応を実施しました。Rails側では、キャッシュエラーが発生した場合には例外処理で空の配列を返すよう修正し、エラー発生頻度は検知できる状態を維持しました。
def fetch_contents(model, keyword) begin model.display_on_public.full_text_search(keyword).map do |record| { score: record.score.to_i, record: record } end rescue ActiveRecord::StatementInvalid => e Sentry.capture_exception(e) [] end end
また、長い検索クエリが渡ってきた場合には、キャッシュサイズが大きくなり、エラーが発生しやすいことが検証によって判明したため、検索クエリの長さにも制限を追加しました。
今振り返ってみると、このキャッシュエラーをできる限り防ぐ方法として、FULLTEXTインデックスに不要な文字列をパターンマッチさせて除外する仕組みも検討すればよかったと感じています。
MySQLのFULLTEXTインデックスの実装では、true word（文字、数字、アンダースコア）のみを文字として扱うため、記号等はFULLTEXTインデックスに追加されません。*4
しかし、UGCコンテンツには、クリエイターが何かしらのURLを記載している場合など全文検索に不要な文字列もFULLTEXTインデックスに含まれてしまいます。そのため、レコードが追加・更新されるタイミングでパターンマッチで不要な文字列を取り除けば、FULLTEXTインデックスの肥大化の速度を落とすことが可能だったのではと思います。
ALTER TABLEするときにINPLACE方式が使えない
MySQLのALTER TABLEによるカラム追加や削除などのDDL操作は、通常INPLACE方式で変更できます。ただし、FULLTEXTインデックスを持つテーブルに関しては、INPLACE方式による変更はサポートされておらず、COPY方式しか利用できないため、テーブルの更新時にロックがかかってしまいます。*5
# fulltextインデックスを持つテーブルにてINPLACE方式でカラム追加しようとした場合 mysql> alter table videos add test varchar(255), LOCK=NONE, ALGORITHM=INPLACE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
MySQLの本番稼働中にロックされると、DBの負荷が高まり、障害につながる可能性があります。クラシル規模のサービスでは、サービスダウンが大きな損失につながるため、FULLTEXTインデックスを持つテーブルにカラムを追加・削除する必要が生じたタイミングで、MySQLからElasticsearchの全文検索に移行しました。Elasticsearchの導入実績が既にあったため、移行自体はスムーズに行えたと思います。
まとめ
テーブルのレコード数が想定よりも早く増加したこと、及びMySQL側の制約によって、MySQLの全文検索を使用する期間は想定よりも短くなりました。しかし、MySQLの全文検索を運用する上で必要な知識を得ることができたため、私自身とても勉強になりました。
MySQLの全文検索は、簡単に導入でき、数十万程度までのレコード数が多くないテーブルに対しては、LIKE検索よりもレスポンスが早く返ってくるので、初期フェーズの検索機能を実装する場合には有用だと思いました。ただし、現状はMySQLの全文検索を長期間運用するには向いていないと思います。そのため、Elasticsearchなどへの移行を前提に、MySQLの全文検索を導入することがベストだと感じました。
この記事が誰かの役に立てれば幸いです。
*1:詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド（奥野 幹也）｜翔泳社の本 p.168
*2:MySQL8.0でもinnodb_ft_result_cache_limitの上限の最大値は4GBから変更されていません。
*3:Amazon Aurora MySQL データベース設定のベストプラクティス | Amazon Web Services ブログ, パラメータの分類
*4:MySQL :: MySQL 5.7 Reference Manual :: 12.9.1 Natural Language Full-Text Searches
*5:MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations
