MySQL でのクエリの最適化: 上位のクエリと遅いクエリの最適化

MySQL やその他のリレーショナル データベースを使用する場合、パフォーマンスの最適化は「遅いクエリ」の特定と修正に関連することがよくあります。これらは、実行に時間がかかりすぎるクエリであり、通常はインデックス作成が不十分であること、結合が複雑であること、またはデータ セットが大規模であることが原因です。ただし、遅いクエリだけに焦点を当てることは、アプリケーションの全体的なパフォーマンスを最適化するための最も効果的な戦略ではない可能性があります。

この記事では、大量のシステム リソースを消費する高頻度のクエリ (「上位クエリ」と呼ばれる) を最適化する方が、遅いクエリだけに焦点を当てるよりも大きなメリットが得られる理由を探っていきます。

クエリには主に次の 2 つの理由で問題が発生する可能性があることに留意することが重要です。

1. 大量のシステム負荷を引き起こすクエリ: これらは高頻度のクエリであり、単独で効率的に実行されますが、その頻度によりシステムに大きな負荷がかかります。
2. 許容できない応答時間を持つクエリ: これらは、特に対話型アプリケーションで遅延を引き起こす可能性がある遅いクエリですが、バッチ ジョブではそれほど問題にならない可能性があります。

1. 遅いクエリが必ずしも最大の問題ではない理由

クエリが遅いと、個々のユーザーに遅延が発生し、タイムアウトやユーザー エクスペリエンスの低下につながる可能性があるため、問題が発生します。通常、これらのクエリはまれに発生し、総リソース消費量は比較的少ないことがよくあります。バッチ処理ジョブなどの特定のケースでは、遅いクエリによってまったく問題が発生しない場合があります。ただし、ユーザーが高速な応答を期待する対話型アプリケーションでは、実行に 10 秒かかるクエリは一般に受け入れられません。

さらに、同時実行性の高い環境では、頻度の低い遅いクエリでもシステム全体の問題を引き起こす可能性があります。たとえば、不適切に作成されたクエリが 1 日に 5 回実行されても、大きな問題には思えないかもしれませんが、重要なテーブルのロックが発生すると、最大接続枯渇 が発生し、他のクエリの実行ができなくなる可能性があります。このドミノ効果は最終的に次のような結果をもたらす可能性があります。

• データベースでの接続の枯渇: ロックが解除されるのを待ってクエリが蓄積されると、利用可能なすべての接続が消費されます。
• 他のシステム層での障害: Web サーバー、アプリ サーバー、キュー システムもワーカー/接続の制限を使い果たし、連鎖的な障害を引き起こす可能性があります。
• 自動スケーリングの制限: システムが自動スケーリングするように設計されている場合でも、限られた量の負荷しか処理できません。さらに、特に中心的な問題が未加工の CPU 負荷ではなくロック競合である場合、自動スケーリングは失敗を防ぐほど迅速に反応しない可能性があります。

そのような場合、単一の遅いクエリが同時実行性の高いシステムで重大な問題を引き起こす可能性があり、それらに対処することがシステムの安定性を維持するために重要です。

2. 上位のクエリの影響を理解する

遅いクエリと上位のクエリの違いを強調する例を見てみましょう。 2 つのクエリがあると想像してください:

• クエリ A: 1 日あたり 1,000,000 回実行され、各実行には 20 ミリ秒 (ms) かかります。
• クエリ B: 1 日に 5 回実行されますが、各実行には 10 秒かかります。

一見すると、クエリ B は待ち時間が長いため、より差し迫った問題のように見えるかもしれません。ただし、クエリ A はより頻繁に実行され、より多くのシステム リソースを消費します。クエリ A の各実行は比較的高速ですが、その頻度が高いため、1 日あたりの合計 CPU 時間は 5.5 時間 を超えます。一方、クエリ B の場合はわずか 50 秒です。

CPU 使用率の点では、クエリ A を最適化すると、パフォーマンスにはるかに大きな影響が生じる可能性があります。クエリ A の実行時間を 50% (20 ミリ秒から 10 ミリ秒) 短縮できれば、CPU 使用率が半分に減り、その結果、システム全体の応答性が向上し、他の操作にリソースが解放されます。

3. 高頻度のクエリの隠れたコスト

多くの開発者は、高頻度クエリは従来の低速クエリ ログでは問題として目立たないため、その影響を見逃しています。待ち時間は短いかもしれませんが、累積的な影響は非常に大きくなります。

たとえば、1 日に何百万回も実行されるクエリがシステム リソースのほんの一部でも消費する場合、次のような可能性があります。

• CPU 使用率が増加し、パフォーマンスのボトルネックを引き起こします。
• 他のクエリの速度を低下させます。これにより、全体的なレイテンシーが増加します。
• スケーラビリティを制限し、システムがより多くのユーザーやトラフィックを処理するのを困難にします。

これらの上位クエリの最適化に重点を置くことで、システム全体の負荷を軽減し、データベースの効率を向上させることができ、その結果、より高速でスケーラブルなアプリケーションが実現します。

4. 上位クエリの最適化: どこから始めるべきか

高頻度のクエリを効果的に最適化するには、まず、システム リソースを最も消費するクエリを特定することから始めます。 Releem のようなツールは、クエリの実行時間、CPU 使用率、メモリ使用量を分析して、どのクエリに焦点を当てるか優先順位を付けるのに役立ちます。簡略化されたプロセスは次のとおりです:

1. 上位のクエリを特定する - パフォーマンス監視ツールを使用して、クエリの実行頻度、合計実行時間、リソース消費量 (CPU と I/O) に関する統計を収集します。
2. クエリ パフォーマンスの分析 - インデックスの欠落、不必要なデータ取得、複雑な結合など、クエリ自体の非効率性を探します。
3. 実行計画の最適化 - クエリの実行計画を調べ、インデックスの追加または調整、クエリの書き換え、または大きなテーブルのパーティション分割を検討します。
4. 結果の監視 - 最適化を実装した後、システムを監視して、変更が望ましい効果をもたらし、システム全体の負荷が軽減され、応答性が向上していることを確認します。

5. バランスを取る: 遅いクエリと上位のクエリ

システム全体のパフォーマンスのために上位のクエリを最適化することは重要ですが、遅いクエリを完全に無視すべきではありません。重要なのは、最適化の取り組みに優先順位を付けるです。頻繁に実行される遅いクエリを最初に優先し、次に中程度の遅延を持つ高頻度のクエリを優先する必要があります。めったに実行されない遅いクエリは、後で対処することも、ユーザーに顕著なパフォーマンスの低下を引き起こす場合にのみ対処することもできます。

Releem のようなツールを使用して SQL クエリを分析および最適化すると、遅いクエリへの対処と上位のクエリの最適化とのバランスをとり、データベースとアプリケーションの最高のパフォーマンスを確保できます。

結論

データベースのパフォーマンスのチューニングでは、遅いクエリが最も明白な問題であるように見えるため、クエリに焦点が当てられがちです。 ただし、大量のシステム リソースを消費する上位のクエリは、特に頻繁に実行される場合、実際のボトルネックになることがよくあります。これらの上位クエリを最適化すると、遅いクエリだけに焦点を当てるよりも、全体的なパフォーマンスとユーザー エクスペリエンスにはるかに大きな影響を与える可能性があります。

遅いクエリと上位のクエリの違いを理解し、Releem などのツールを活用して非効率なクエリに優先順位を付けて最適化することで、CPU 使用率を削減し、スケーラビリティを向上させ、ユーザーにとってより応答性の高いアプリケーションを作成できます。

以上がMySQL でのクエリの最適化: 上位のクエリと遅いクエリの最適化の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。