画面が遅いからといって、データベースが問題であるとも、インデックスが解決策であるとも限りません。同じ体感上の遅延は、PHPコード、接続枯渇、外部HTTP呼び出し、レスポンスのシリアライズ、ロック、または返却データが多すぎるクエリに起因し得ます。PHPの遅いクエリを調査する方法とは、スキーマを変更したり、書き込みコストを増やしかねない最適化を追加したりする前に、根拠を構築することです。
アプリケーションのレイテンシとデータのレイテンシを分ける
まず、リクエストの合計時間を分解します。識別可能なルートまたはコマンド、開始時刻と終了時刻、実行されたクエリ、その実行時間、および外部依存関係を記録します。平均時間を測定するだけでは不十分です。APIは健全に見えても、特定のフィルタ、顧客、または深いページでは失敗する可能性があります。
インシデントごとに、次を区別することが重要です。
- PHPの時間:コレクションの変換、ループ、JSONシリアライズ、ドキュメント生成、または過剰なメモリ使用。
- データベースの時間:各クエリの実行時間、ロック待機、接続確立、転送された行数。
- ネットワークと依存関係の時間:リモートキャッシュ、サードパーティAPI、ファイルストレージ、キュー、またはIDサービス。
- キューの時間:PHPワーカー、利用可能な接続、またはデータベースリソースを待つリクエスト。
リクエスト識別子を含むトレースまたは構造化ログを使用してください。単独では20msのクエリでも、同じレスポンス内で数百回実行されれば数秒の問題になり得ます。逆に、500msのクエリであっても、プロセスが外部サービスを数秒待機しているなら、主因ではない可能性があります。
コード変更前に根拠を収集する
パラメータ化されたクエリと、代表的なパラメータを分けて取得します。シークレット、完全な個人データ、または事例の再現に不要な値を記録しないでください。頻出するステータスによる検索は、一意の識別子による検索と同じ挙動ではありません。都合のよいケースだけを評価すると、誤った判断につながります。
最低限の根拠には、次を含める必要があります。
- 影響を受けるルート、非同期ジョブ、またはコマンドと、その頻度。
- 観測された実行時間、高いパーセンタイル、および発生時点。
- SQL、型付きパラメータ、およびリクエストあたりの実行回数。
- 返却行数と、可能であれば読み取りまたは検査された行数。
- テーブルのおおよそのサイズと、フィルタ値の分布。
- 並行性、同時書き込み操作、および関連するロック。
データベースエンジンのスロークエリログは候補の発見に役立ちますが、アプリケーショントレースの代わりにはなりません。通常、どのエンドポイントがクエリを組み立てたか、何回繰り返されたかは示されません。MySQLとPostgreSQLでは、この情報を接続、CPU、I/O、待機時間のメトリクスと組み合わせ、劣悪な実行計画と一時的に飽和したインフラを混同しないようにしてください。
コストを増幅するパターンを特定する
複雑な文を分析する前に、頻出パターンを探します。N+1は、一覧が主な行を取得した後、各リレーションごとに追加クエリを実行する場合に発生します。個々のクエリが高速でも、データベースへの往復、プランニング処理、競合の量はページサイズとともに増加します。
非常に一般的なステータスのような選択性の低いフィルタ、フィルタ対象カラムに適用される関数、暗黙の型変換、先頭ワイルドカードを伴う検索、大きな集合のソート、高いOFFSETによるページネーションも疑わしい要因です。SELECT *を要求すると、実行計画がすでにインデックスを使用している場合でも、転送量、メモリ、読み取り処理を増やすことがあります。
修正が常に単一クエリとは限りません。リレーションを制御された形でロードすればN+1を解消できますが、制限のないeager loadingは巨大なクエリまたはレスポンスを生む可能性があります。ルートが実際に必要とするリレーションを定義し、そのカラムを制限して、想定ページサイズで影響を測定してください。
実データで実行計画を解釈する
提案された計画を把握するためにEXPLAINを実行し、環境に対して安全かつ適切な場合は実際の実行を含むバリアントを使用します。PostgreSQLでは、EXPLAIN ANALYZEはクエリを実行します。変更文を、その影響を理解せずにこの方法で分析してはいけません。MySQLでは利用可能なモードはバージョンと設定に依存しますが、目的は同じです。見積もりを実作業と比較することです。
シーケンシャルスキャンが自動的に悪いわけではありません。クエリが小さいテーブルまたは選択性の低いテーブルの大部分を必要とする場合、インデックスとテーブルを行き来するよりも全体を走査する方が低コストなことがあります。一方、計画が推定よりはるかに多い実行行数、コストの高いソート、一時読み取り、広い集合に対するjoin、または何度も実行される内部ループを示す場合は調査してください。
計画レビューで役立つ質問
- オプティマイザは何行を想定し、実際には何行を処理したか。
- どのノードに最も多くの時間、読み取り、または反復が集中しているか。
- フィルタは早い段階で適用されるか、それとも大きな集合を結合した後か。
- ソートはレスポンスに必要な行数より多くの行に対して行われているか。
- 統計情報は現在のデータ分布を反映しているか。
見積もりと実際の差異には、統計情報の更新や型・条件の見直しが必要な場合があり、直ちにインデックスを作成すべきとは限りません。計画は、特定のパラメータと負荷の下での実行を説明するものであり、自動的な変更指示ではありません。
書き換え、ページネーション、データアクセス、インデックスを選ぶ
まず不可避な作業を減らします。必要なカラムだけを選択し、妥当な上限を適用し、未使用のリレーションを除外し、完全な履歴をPHPへ転送してからフィルタリングすることを避けます。ユースケースが増え続ける履歴の探索を求めるなら、深いページネーションをカーソルまたはキーによるページネーションに置き換えてください。たとえば、OFFSETで数千行を捨てるのではなく、日付と識別子の安定した組み合わせから継続します。
joinとフィルタを見直し、互換性のあるカラムを比較し、条件を明確に表現するようにします。リレーションをバッチで問い合わせる方がよい場合もあれば、適切に限定された単一クエリが望ましい場合もあります。判断は普遍的なルールではなく、カーディナリティ、レスポンス量、頻度に依存します。
複合インデックスは、アクセスパターンに一致する場合に役立ちます。その順序は重要です。通常、等価条件かつ選択性の高いカラムは、範囲またはソートに使用するカラムより先にフィルタリングを容易にすべきですが、結果は具体的なクエリとエンジンにより決まります。インデックスが互換性のある順序をカバーしていればソート回避にも役立ちますが、すべてのWHEREとORDER BYの組み合わせで可能なわけではありません。
条件に現れるという理由だけでカラムにインデックスを作成しないでください。インデックスは領域を占有し、メモリを消費し、INSERT、UPDATE、DELETEに作業を追加します。冗長または有用性の低いインデックスは、書き込みの多いシステムを悪化させる可能性があります。必要なカラムを追加読み取りなしに取得できるかも確認しますが、コストを測定せずにカバリング用カラムを追加しないでください。
仮想例:操作履歴
アカウント、ステータス、日付ごとに操作を表示する一覧を想定します。履歴が増えると、200ページ目が劣化します。最初の仮説は日付にインデックスを作成することかもしれません。しかし、トレースを見ると、主クエリの後に担当ユーザーを取得するため操作ごとに1クエリが続いています。これはN+1です。さらに主クエリの計画は、OFFSETで先行行を捨てるために多くの行を読み取っています。
妥当な順序は、担当者をグループ化してロードするか必要なカラムに限定したjoinで取得し、深いページネーションをcreated_atとidに基づくカーソルに置き換え、再測定することです。その後で初めて、アカウントフィルタ、安定した順序、カーソルに整合するインデックスを評価します。アクセス変更は単独のインデックスより多くの作業を削減でき、最終的なインデックスは新規操作の作成に対しても確認する必要があります。
負荷下で検証し、書き込みの回帰を監視する
代表的なパラメータ、データ分布、並行性で変更前後を比較します。実行時間、処理行数、読み取り、CPU使用率、PHPのメモリ、レスポンスサイズ、リクエストあたりのクエリ数を測定してください。新しいインデックスについては、影響を受ける書き込み操作のレイテンシと処理能力も測定します。
明示的な受け入れ基準を定義してください。たとえば、作成または更新時間を許容できないほど増加させずに、ルートの高パーセンタイルを検証可能な形で削減することです。空のケース、非常に頻出する値、まれなフィルタ、最初と最後のページ、およびデータ範囲を変える権限をテストしてください。
観測可能かつ可逆的に変更をデプロイする
可能な場合は、コードのデプロイとインデックス作成を分離します。インデックス作成は、エンジン、操作、環境に応じてリソースを競合させたりロックを取得したりする可能性があります。タイミングを計画し、データベースがサポートする方法を確認し、実行時間、エラー、I/O負荷を監視してください。
アーキテクチャが許すなら新しいクエリを段階的に導入し、明確なロールバックを維持します。以前のクエリを復元する、代替アクセスパスを無効化する、または有害であると判明したインデックスを削除する、といった対応です。ロールバックはバックアップやマイグレーションのレビューに取って代わるものではありませんが、予期しない挙動にさらされる時間を短縮します。
再現可能な改善のためのチェックリスト
- ルート、症状、および再現するパラメータを特定する。
- PHP、データベース、ネットワーク、外部依存関係の時間を分離する。
- 繰り返し回数、返却行数、処理行数を定量化する。
- N+1、深いページネーション、選択性の低いフィルタ、ソートを探す。
- 計画を確認し、その見積もりを実際の実行と照合する。
- インデックス作成前に、データ削減、書き換え、ページネーション変更を試す。
- フィルタ、ソート、書き込みの完全なパターンに従ってインデックスを設計する。
- 代表的な負荷、可観測性、ロールバックで読み取りと書き込みを検証する。
