問題の状況#
MySQLの全文検索（FullText Search）を活用した検索APIを実装し、Postmanでテストを実施しました。
しかしテスト結果、応答時間がなんと6.85秒もかかりました。検索APIはユーザーが食事を記録するたびに呼び出される重要なAPIです。一般的に検索APIの応答時間は200〜500ms以内が適切とされていますが、6.85秒はこの基準を大きく超える数値です。
すぐに全文検索インデックスの適用状況を確認しましたが、全文検索インデックスは正常に作成されていました。
インデックスは作成されているがインデックスが正しく使われていないのでは？
という疑問が生じ、ここからその原因を追跡していきます。
APIの要件とDDL#
APIの要件として、productを検索する際に検索結果とともに各productに関連するreviewの総数も表示しなければなりません。
現在productテーブルのデータは100万件、reviewテーブルのデータは1,000万件保存されています。
各テーブルのDDLは以下のとおりです。外部キーは使用せず、代わりにインデックスは別途設定しています。
-- ngramアルゴリズムを活用する全文検索インデックスを作成
CREATE TABLE product (
product_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255) NOT NULL,
product_corp VARCHAR(255),
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
FULLTEXT INDEX fulltext_idx (product_name, product_corp) WITH PARSER ngram
);CREATE TABLE review (
review_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id BIGINT NOT NULL,
content VARCHAR(255),
rating TINYINT NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
);
CREATE INDEX idx_product_id ON review (product_id);問題のクエリ#
SELECT
p.product_id,
p.product_name,
p.product_corp,
IFNULL(review_count, 0) AS review_count
FROM product p
LEFT JOIN (
SELECT product_id, COUNT(*) AS review_count
FROM review
GROUP BY product_id
) r ON p.product_id = r.product_id
WHERE MATCH (p.product_name, p.product_corp) AGAINST ('クリスピー')
LIMIT 1, 20;
上記の要件のために作成したクエリは上記のとおりで、例として「クリスピー（クリスピー）」という単語で全文検索を実行しました。
しかし上記のクエリを適用してテストしてみたところ**3913（ms）**秒かかるほど深刻に遅いパフォーマンスを示しています。
実行計画の分析#
実行順序は赤い番号の順で、以下の基準で読むとよいです。
- インデント（->の形の矢印）が同じインデントレベルでは上に位置する行が先に実行される。（2番と6番の中で2番が先に実行される）
- インデントが異なるレベルでは最も右に位置する行が先に実行される。（2番と1番の中で1番が先に実行される）
最初の実行作業である「Full-text index search on p using fulltext_idx」を基準に実行計画の用語を説明すると：
- cost: 総クエリコストにおけるこのクエリの寄与度。
- rows: 予想される結果のレコード数。
- actual time=n..m: nは各loopで最初のレコードを読むまでの平均時間、mは最後のレコードを読むまでの平均時間。
- actual rows: 1回のloopでproduct_name=“クリスピー"を使用して実際に読んだ平均レコード数。
- actual loops: product_name=“クリスピー"を使用してproductテーブルからレコードを探す作業を繰り返した回数。
では実行計画を辿ってみましょう。
まず、productテーブルに全文検索を実行します。この部分では26件のレコードを取得するのに平均22（ms）かかっており、パフォーマンス低下に影響を与えていません。
次にreviewテーブルでCovering index scanを実行しますが、この際にテーブルから読んだrowsが**1,000万件（10e+6）で、最後のrowを取得するのにかかった時間がなんと3220（ms）**でした。
GROUP BY product_idとCOUNT()を活用して関連するreviewの件数を集計する際、前の作業ですべてのreviewのproduct_idインデックスを探索するのに時間が多くかかりました。
その後reviewの件数に対する集計演算を実行し、LEFT JOINを処理するのに大よそ**4418（ms）**の時間がかかりました。（EXPLAIN ANALYZEを使用したため実際のクエリよりも時間が少し余分にかかっています。）
1回目のクエリ修正#
問題点を確認してみたところ、reviewテーブルでindex full scanが処理されています。この問題を解決するには、全文検索で探索されたproductに関連するreviewだけを選択すれば多くのrowsの値を減らせるでしょう。
SELECT p.product_id, p.product_name, p.product_corp, COUNT(*) AS review_count
FROM product p
LEFT JOIN review r ON p.product_id = r.product_id
WHERE MATCH(p.product_name, p.product_corp) AGAINST('クリスピー')
GROUP BY p.product_id
LIMIT 1, 20;LEFT JOINの中にあったサブクエリを外に出し、reviewテーブルをジョインするように変更しました。そしてGROUP BYを全文検索の後に行うよう変更しました。
しかしそれでもクエリが**3642（ms）**程度で非常に低いパフォーマンスを示していました。
再び実行計画を分析してみたところ、予想どおり全文検索で探索されたproductのレコードに対してのみreviewテーブルを探索していることがわかります。
しかし問題があります。
全文検索で探索されて読み込まれたproductのデータが90,911件です。
product1件あたりに読み込まれたreviewレコードは平均10件で、Nested loop left joinをするときに大よそ90万件のデータを処理することになり、最後のレコードを読むのに3065（ms）かかっており依然としてパフォーマンス低下が見られます。
実行計画をもう少し詳しく見ると、908,796件のレコードをジョイン結果として得て、GROUP BYのような集計演算のために一時テーブルを作成した後、LIMIT処理を実行します。
変更前のクエリはlimit Optimizationが行われて全文検索を実行しながら必要なレコードの数だけ読んでいましたが、今回はこの作業が行われませんでした。
limit Optimization関連参考資料：
WHERE LIKE句との比較#
SELECT p.product_id, p.product_name, p.product_corp, COUNT(*) AS review_count
FROM product p
LEFT JOIN review r ON p.product_id = r.product_id
WHERE p.product_name LIKE "%クリスピー%"
GROUP BY p.product_id
LIMIT 1, 20;追加で一つ疑問が生じて、同じ構造のクエリで全文検索の代わりにWHERE LIKE句を使ってみました。すると驚くことに**35（ms）**という非常に速いパフォーマンスが出ました。
最初にproductテーブルで主キー（PRIMARY）を活用したインデックススキャンが実行されました。product_nameにはインデックスがかかっていません。（インデックスを設定してもワイルドカード（%）が前にあればインデックスを活用できません）したがって主キーを活用してレコードを読み込みながら「クリスピー」が含まれるレコードをフィルタリングします。
ここでMySQLは順次レコードをスキャンしながらlimit 20に少し余裕を持たせて22件のレコードを見つけた瞬間に探索を終了します。その過程で合計240件のレコードを探索しました。つまり、limit optimizationが行われ、別途一時テーブルの作成なしにGROUP BY集計演算が実行されました。
全文検索を活用する際はMATCH AGAINSTでマッチングされたすべてのレコードをまず評価した後、GROUP BY集計が完了してはじめてLIMITを適用できるため、順次スキャンのように途中で探索を中断するlimit optimizationが行われません。
しかしだからといってこのような方式の検索を活用しようということではありません。テーブルのレコードが非常に多く、見つけたいデータが非常に後ろにある場合、Index full scanをするときに非常に多くの時間がかかる可能性があります。
実はこのような現象はダミーデータを挿入する際にJdbcTemplateのbatch insertを使用したことでデータに規則性が生まれ、これによってlimitの件数が素早く満たされました。
また全文検索の際には、このような規則性によって一つの全文検索インデックスに関連するデータが過度に多く保存されて（例の場合90,911件）スキャン対象が増える問題が発生しました。
2回目のクエリ修正#
SELECT p.product_id,
p.product_name,
p.product_corp,
(SELECT COUNT(*) FROM review r WHERE r.product_id = p.product_id) AS review_count
FROM product p
WHERE MATCH(p.product_name, p.product_corp) AGAINST('クリスピー')
LIMIT 1, 20;ここではJOINを使用せず、Subqueryを使用しました。まず、productテーブルに全文検索結果を探索し、その結果に該当するreviewだけの件数を求めました。
実行計画を確認したところ、全文検索の結果からlimit 20に近いレコード数を読み込みました。そしてここで総クエリの実行時間は**84（ms）**程度となりました。
結果として、インデックスを修正するよりも、クエリの構造をジョインからサブクエリに変更することで実行時間が3000〜4000（ms）から100（ms）未満へ、30倍以上向上しました。
要件の追加#
実はもう一つ要件が存在していました。
各productに対する上位3つのタグを表示します。ユーザーがレビュー作成時に自由に選択したタグの中で最も多く選ばれた3つを表示します。
CREATE TABLE product_diet_tag (
product_diet_tag_id BIGINT PRIMARY KEY AUTO_INCREMENT,
product_id BIGINT,
diet_tag_id BIGINT,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
);
CREATE INDEX idx_product_id ON product_diet_tag (product_id);CREATE TABLE diet_tag (
diet_tag_id BIGINT PRIMARY KEY AUTO_INCREMENT,
diet_tag_name VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL
);diet_tagテーブルにはあらかじめ定められた10件のデータが存在し、product_diet_tagテーブルには2,000万件のデータが保存されています。
DMLは上記のように記述しましたが、要件を満たすクエリを作成する途中で複雑すぎたため、diet_tagテーブルにあったdiet_tag_name列をproduct_diet_tagテーブルに非正規化しました。
SELECT p.product_id,
p.product_name,
p.product_corp,
(SELECT COUNT(*) FROM review r WHERE r.product_id = p.product_id) AS review_count,
(SELECT GROUP_CONCAT(diet_tag_name)
FROM (SELECT diet_tag_name
FROM product_diet_tag pdt
WHERE pdt.product_id = p.product_id
GROUP BY diet_tag_name
ORDER BY COUNT(diet_tag_name) DESC
LIMIT 3) AS top3_diet_tag) AS top3_diet_tag_names
FROM product p
WHERE MATCH(p.product_name, p.product_corp) AGAINST('クリスピー')
LIMIT 1, 20;前述のクエリと同様に、ここでもSubqueryで解決しました。
クエリの実行時間は平均的に**150〜250（ms）**となりました。各作業一つ一つには大きなコストはかかりませんが、複数の過程が累積すると大よそ2〜3倍程度時間が増えました。
より良い方法はないのか？#
ここでクエリをさらに最適化する方法を探すこともできますが、果たしてこのような一発クエリが適切かどうか考える必要があります。
ここでの問題点は実行時間が増加した部分もありますが、サービスのビジネスロジックがクエリの中に溶け込んでいます。
例えば「評点4点以上のレビューのみ集計する」や「上位タグを3つから5つに変更する」という要件が生じた場合、SQLのサブクエリも一緒に修正しなければなりません。 一方、このロジックをアプリケーション層（サービスまたはドメインレイヤー）に移して — 例えばproduct検索結果を受け取った後にレビュー数とタグ情報を別途照会して加工する方式で処理するなら、ビジネスロジックの変更がSQLではなくJavaコードのみに限定されてメンテナンスがより容易になります。
またテストコードを書くことも難しくなります。リポジトリのテストコードを作成するなら、一つのテストに検索の有無、レビュー件数、tag情報のすべてを反映したテストを作成しなければならず、要件が増えるほどリポジトリのテストコードも継続して修正していかなければなりません。
次回は、クエリに溶け込んでいるビジネスロジックをドメイン領域とビジネス領域に移してクエリを改善していきます。