SQLパフォーマンスチューニングの始め方
こんにちは、iCAREサーバーサイドエンジニアの寺井(@krpk1900_dev)です。
私は今まで新規機能の開発を担当することがほとんどで、既存機能のパフォーマンス改善に取り組むときに何から手を着けて良いか分からなかったため、今回はSQLパフォーマンスチューニングについて調べた内容を記事にしたいと思います。
全体の流れ
ざっくり調べてみた内容をフローチャートで整理してみました。
このフローチャートには含めませんでしたが、根本的な解決策としてそもそものロジックやデータ構造を見直すという方法もあります。
1. レスポンスに時間がかかっている箇所とその原因を特定する
まずはDatadogのAPMなどでレスポンスに時間がかかっているリクエストを特定します。
APM(Application Performance Monitoring)とは、アプリケーションの性能を管理したり監視するための機能です。
今回はp50 LATENCYでソートし、中央値より下位に含まれるリクエストを対象にしました。
レスポンスに時間がかかっているリクエストが特定できたら、次はそのリクエストがなぜ遅いのかを調べます。
大きく分けて、クエリの数が多くて時間がかかっているのか、そもそも1つのクエリが遅いのかのどちらに該当しているかを判断します。
2. クエリの発行回数が多いとき
一番下の行が費やした時間の内訳を最も詳細に分割したものですが、クエリの発行回数が多い場合は、下の画像のように1回のリクエストの中でたくさんの同じ幅のスパンが連続して見られます。
これを「櫛(くし)が見られる」と言ったりするそうです。
さらに紫の1つのスパンにカーソルを当てると実際に発行されているクエリが確認できるため、連続して発行されているクエリがSELECT文なのかINSERT文なのかを判別します。
2-1. SELECT文の場合
連続して発行されているクエリがSELECT文の場合は、N+1問題が発生している可能性が高いです。
もしN+1問題が発生していれば、該当箇所でpreloadまたはeager_loadを利用するように修正してN+1問題を解決します。
preloadとeager_loadの使い分けは、前島さんがこちらの記事で書いてくださっているように
、基本的にはpreloadを使ってJOINが必要なときだけeager_loadを使うという方針が良いと思います。
2-2. INSERT文の場合
連続して発行されているクエリがINSERT文の場合は、each文でレコードの更新を大量に行っている可能性が高いです。
その場合はgemのactiverecord-importか、Rails 6.0から使えるようになったinsert_allやupsert_allを使うことでクエリの発行回数を抑えることができます。
insert_allやupsert_allを使うときはバリデーションやコールバックが行われないので注意が必要です。
3. 1つのクエリが遅いとき
そもそも1つのクエリが遅いときは、連続したスパンではなく下の画像のような横長の大きなスパンが見られます。
アプリケーションの処理時間を表している黄色のスパンは一番下の行にはほとんど見られず、紫色のスパンのDBの処理時間が大半を占めていることが分かります。
この場合は、DBに対する対応を行う必要があります。
3-1. indexを張る
闇雲にindexを張る行為は、SQLアンチパターンの第12章 「インデックスショットガン」でアンチパターンとして紹介されていました。
indexを張った代償として、データの更新処理の際にindex変更のオーバーヘッドが発生するためです。
ではどんなときにindexを張ることを検討すると良いのかというと、達人に学ぶDB設計 徹底指南書では以下のような指針が示されています。
①大規模なテーブルに対して作成する
②カーディナリティが高い列に作成する
③SQL文でWHERE句の選択条件、または結合条件に使用されている列に作成する
②で出てきたカーディナリティとは、カラムに格納されているデータの種類がどのくらいあるかを表しています。
例えば、性別カラムのカーディナリティは男性と女性の2や、男性と女性と不詳の3程度になります。
一方で作成日時カラムのカーディナリティはほとんどレコード数と同じくらい大きくなります。
性別よりも作成日時の方がカーディナリティが高いので、②の指針に従うと作成日時にindexを張るのが良いということになります。
この理由について分かりやすいように考えてみると、そもそもindexとは「索引」や「総目録」という意味の単語です。
本の一番後ろによくあるページですね。
カーディナリティが低い性別とカーディナリティが高い作成日時を本の索引として見てみると、このようになります。
カーディナリティが低い性別では、男というキーワードが載っているページが多すぎて索引としてあまり役に立たなさそうです。
一方、カーディナリティが高い作成日時では調べたい日時から一発で該当のページに飛ぶことができ、有用な索引になっています。
しかし、値に100個の種類があるカラムでも実際のデータのうち90%が1個の値に集中していると、その値で絞り込むときはインデックスが使われないことがあるので、その点には気をつける必要があります。
indexを張った後はそのindexが効いているかを実行計画をとって確かめる必要があります。
実行計画はDBのEXPLAINコマンドを使って確認することができますが、RailsでもActiveRecord::Relationにexplainメソッドが実装されています。
ActiveRecordにexplainメソッドをチェインして実行するだけで、発行されたSQLクエリの実行計画を取ることができます。
ただし、Railsのexplainメソッドを使うときは、直接SQLでEXPLAINコマンドを実行するときとは異なって実際にクエリを発行するため、重いクエリの調査をするときには注意が必要です。
3-2. パーティショニングを行う
3.1で紹介したindexを張る対応が本筋ですが、それでも解決されないときの1つの手段としてパーティショニングがあります。
パーティショニングとは、巨大なテーブルを複数のテーブル（パーティション）に分割することです。
分割された複数のパーティションはアプリケーションからは1つのテーブルとして見ることができます。
テーブルを分割するときにどんな規則に従うかによって、いくつかの方法があります。
- レンジパーティショニング
- リストパーティショニング
- ハッシュパーティショニング
- キーパーティショニング
例えば「東京支社」、「大阪支社」、「名古屋支社」などの支社ごとでパーティショニングされたテーブルに対して大阪支社で絞り込むようなSQLクエリを発行した場合、フルスキャンせずに大阪支社パーティションのみを探索することで実行時間を短縮することができます。
ただし、パーティショニングを行う際にはパーティショニングキーと外部キーの制約などいくつかのデメリットも存在するため、基本的にはindexを張る対応をまず最初に考えるのが良いと思います。
終わりに
今回はSQLパフォーマンスチューニングの始め方についてまとめてみました。
この記事では具体的には触れませんでしたが、そもそものロジックやデータ構造を見直すということも根本的な解決策なので、バックエンドエンジニアとして常にその考えも持っておく必要があると思いました。
Carelyもユーザー数やテーブル数の増加に伴ってパフォーマンスが問題になっているフェーズにすでに入っているため、新規機能の開発を進めながらパフォーマンス改善に取り組んでいきたいです。
