コドモンのカレンダー｜Advent Calendar 2023-Qiitaの15日目の記事です。
こんにちは。コドモンプロダクト開発部の青木です。好きなアンパンマンのキャラクターはナットーマンで、好きなセリフは「ねばねばギブアップ」です。
今回はデータベースのクエリチューニングで苦心し、そこで得た教訓を紹介させていただきます。
開発を行っていたサービス内容
コドモンのプロダクトには園児／児童の保護者が使用する「保護者アプリ」があります。出欠連絡を保護者から送信したり、施設からのお知らせを確認できたり、保育・教育施設と保護者双方に欠かせないツールです。今回はその保護者アプリで閲覧できる新たなサービス開発を行っていました。
保護者アプリは潜在的に下記のような課題がありました。
- 複数の記事を同時に取得して表示する必要があり、取得期間も広い。
- 保育・教育施設が情報を開示する時間が一定期間に集中する傾向にあり、特定の時間帯に負荷が高くなりやすい。
- ユーザー数も多く、閲覧回数が増える施策は特にサーバー負荷の観点で注意が必要。
教訓
- その要求は叶えられますか？ 現状の仕様を理解し、実現可能性を検討しましょう。
- 基本に忠実に。カーディナリティ、データの分布を意識しましょう。
- クエリの実行計画だけを信用しない。本番相当のデータ量で負荷試験をしましょう。
- 要求が変われば構成も変わる。テーブルは適宜チューニングしましょう。
その要求は叶えられますか？ 現状の仕様を理解し、実現可能性を検討しましょう。
- 表示させるデータのテーブル構成を把握していますか？
- 表示するデータはどのような条件で絞り込まれますか？
- 表示するデータは今後どの程度の頻度で更新がされ、どのように増えていきますか？
- 実現可能性を鑑みて仕様を検討する必要があれば早めに相談をしましょう。
既存のテーブルに対して新しいselectを追加する場合、上記を確認しましょう。
提供したいサービスが魅力的であるほど「実現可能であることが前提」で話が進みがちです。早く提供したいという熱にかられて火に飛び込みがちです。はいそうです。私もそうでした。今回は閲覧回数が多いページの施策であったため、既存の機能理解が特に重要でした。
基本に忠実に。カーディナリティ、データの分布を意識しましょう。
カーディナリティとは？
データベースの列に格納される値のユニークな数の指標です。種類が多いほど「カーディナリティが高い」。少ないほど「カーディナリティが低い」と表現されます。https://dev.mysql.com/doc/refman/8.2/en/glossary.html#glos_cardinality
インデックスを作成する場合、まずはカーディナリティが高いカラムから検討しましょう。
データの分布とは？
カーディナリティが高いカラムだったとしても、データに偏りあった場合、インデックスの効果が小さい可能性があります。例えば1〜100の数値が10000レコード存在していたとして、9900レコードはすべて1で、残りのレコードが他の数値だった場合、1で検索するときと他の数値で検索するときとでは速度が大きく変わる可能性があります。
既存のインデックスの意味を読み解きましょう。新たに抽出したいデータの検索条件とマッチしていますか？ データ量が少ない値を検索しているがゆえに、実行計画が良好に見えている可能性はありませんか？ はいそうです。私もそうでした。その実行計画は一時的なものです。データ量が多い値に切り替えてexplainを実行し直してみましょう。
クエリの実行計画だけを信用しない。本番相当のデータ量で負荷試験を行いましょう。
explainの結果は良好。テスト環境で行った負荷試験でも大きな異変は見受けられない。よしよし。もう大丈夫、と思っていませんか？ はいそうです。私もそうでした。
- テスト環境のデータ量は本番環境と同等ですか？
- テスト環境は数百レコードで、本番環境は数万レコード、ということはないですか？
- リクエストを受け取るwebサーバーのインスタンススペックは本番と同等ですか？
- データベースのスペックは本番と同等ですか？
- 本番環境で想定されるリクエスト数はどの程度でしょうか？
上記を考慮して、実施する環境を確認しましょう。必要であればスペックを調節し、どの程度の負荷をかけるか検討しましょう。 負荷試験については別のブログ記事もありますので、そちらも是非ご覧ください。 tech.codmon.com
要求が変われば構成も変わる。テーブルは適宜チューニングしましょう。
既存の構成は、新たに追加したい表示形式やフィルターから逸脱していませんか？ インデックス追加だけで対応できますか？ これは最初の教訓「その要求は叶えられますか？ 」と紐づいてきます。必要に応じて新しいテーブルを追加し、長期的なデータ増加に耐えられる構成にしましょう。
今回の該当テーブルは元々クラス単位のデータ取得と表示が前提になっており、施設単位の取得は考慮されていませんでした。施設単位の取得を新たに考慮に加えてインデックスを追加し一定の性能を担保することができましたが、潜在的な取得期間の問題に踏み込んだ恒久的な解決には残念ながら至りませんでした。
おわりに
どの教訓も特別なものは一つもなく一般的なものばかりです。当たり前のことを一ずつ丁寧に対応していくことが大切なんだなと、しみじみ感じました。また一見簡単に思えるサービス開発も、それ単体で考えることはできないと改めて実感した体験となりました。
最後まで読んでいただきありがとうございました。 この記事が何かの気づきにつながれば幸いです。
