MySQLクエリ速度を最適化する方法

前の章では、高性能な MySQL に不可欠な、最適化されたデータ型の選択方法とインデックスの効率的な使用方法を紹介しました。 しかし、これだけでは十分ではなく、合理的なクエリ設計も必要です。 クエリが適切に記述されていない場合、テーブル構造が合理的でインデックスが適切であっても、高いパフォーマンスは達成できません。

MySQL のパフォーマンス最適化に関しては、クエリ最適化が最適化の源であり、システムが高速であるかどうかを示す最良の指標でもあります。 この章と次の章では、クエリ パフォーマンスの最適化に焦点を当てます。MySQL が実際にクエリを実行する方法、クエリが遅い場所、クエリを高速化する方法、高効率と低効率の理由について理解を深めるために、いくつかのクエリ最適化手法を紹介します。これにより、クエリ SQL ステートメントをより適切に最適化できるようになります。

この章は「なぜクエリの速度が遅いのか」というところから始まり、クエリが遅くなる可能性がある場所を明確に把握できるようにします。これにより、クエリをより適切に最適化し、他の人よりも一歩先を行くことができます。

1. 遅いところはどこですか?

クエリ速度の真の尺度は応答時間です。 クエリをタスクとして考えると、一連のサブタスクで構成され、各サブタスクには一定の時間がかかります。 クエリを最適化する場合、実際にはサブタスクの一部を削除するか、サブタスクの実行回数を減らすか、サブタスクの実行速度を上げるかのいずれかの方法で、サブタスクを最適化する必要があります。

MySQL がクエリを実行するとき、サブタスクとは何ですか? また、どのサブタスクに最も時間がかかりますか? これには、クエリを分析して速度が遅い箇所を特定するためのツールや方法 (実行プランなど) を使用する必要があります。

一般的に、クエリのライフサイクルは、大まかに言えば、クライアントからサーバーへ、サーバー上で解析され、実行プランが生成され、実行され、結果がクライアントに返されるという順序で考えることができます。 その中で、「実行」はライフサイクル全体の中で最も重要な段階であると考えられます。これには、データを取得するためのストレージ エンジンへの多数の呼び出しと、呼び出し後の並べ替え、グループ化などのデータ処理が含まれます。

これらのタスクを完了する際、クエリは、ネットワーク、CPU 計算、統計と実行プランの生成、ロック待機、その他の操作、特に基盤となるストレージ エンジンからデータを取得するための呼び出し操作など、さまざまな段階のさまざまな場所で時間を費やす必要があります。これらの呼び出しには、メモリ操作、CPU 操作が必要であり、大量のコンテキスト スイッチとシステム コールも生成される可能性があります。

上記のすべての操作では、多くの時間が消費され、不要な追加操作が発生することがあります。一部の操作は何度も繰り返され、一部の操作は非常に遅く実行される可能性があります。 これは実際にクエリが遅くなる可能性がある部分であり、クエリ最適化の目標はこれらの操作に費やされる時間を削減または排除することです。

上記の分析を通じて、クエリ プロセスを包括的に理解し、クエリのどこに問題があるかを明確に把握して、最終的にクエリ全体の速度低下につながるかどうかを把握し、実際のクエリ最適化の方向性を示すことができます。

つまり、クエリの最適化は次の 2 つの観点から実行できます。

• サブクエリの数を減らす
• 余分な繰り返し操作を削減

クエリのパフォーマンスが低下する一般的な原因は、アクセスするデータが多すぎることです。 データ量が少ない場合、クエリ速度は良好です。データ量が増えると、クエリ速度が劇的に変化し、人々を困惑させ、非常に悪い体験をもたらします。 クエリの最適化については、次の側面から確認できます。

• 不要なデータが照会されていますか?
• 追加のレコードがスキャンされたかどうか

2. 不要なデータをクエリしましたか?

実際のクエリでは、実際に必要なデータがクエリされ、その後冗長なデータはアプリケーションによって破棄されることが多いです。 これは MySQL の追加オーバーヘッドであり、アプリケーション サーバーの CPU およびメモリ リソースも消費します。
典型的なケースとしては、次のようなものがあります。

1. 不要なレコードをクエリする

これはよくある間違いです。MySQL は必要なデータのみを返すと誤解している人が多いのですが、実際には MySQL は計算を実行する前に結果セット全体を返します。

開発者は通常、SELECT ステートメントを使用して大量の結果をクエリし、アプリケーション クエリまたはフロントエンド表示レイヤーを使用して最初の N 行のデータを取得します。たとえば、ニュース Web サイトで 100 件のレコードがクエリされますが、ページに表示されるのは最初の 10 件だけです。

最も効果的な解決策は、必要な数のレコードをクエリすることです。通常は、クエリの後に LIMIT を追加します (つまり、ページ分割されたクエリ)。

2. 複数のテーブルが関連付けられている場合はすべての列を返す

映画「アカデミー・ダイナソー」に出演した俳優全員を検索する場合は、次の操作は行わないでください。

俳優aから*を選択
内部結合 film_actor fa.actorId = a.actorId
内部結合フィルム f f.filmId = fa.filmId
fa.title = 'アカデミー恐竜';

これにより、3 つのテーブルのすべてのデータ列が返されますが、実際の要件は俳優情報を照会することです。正しい書き方は次のとおりです。

俳優aからa.*を選択します
内部結合 film_actor fa.actorId = a.actorId
内部結合フィルム f f.filmId = fa.filmId
fa.title = 'アカデミー恐竜';

3. 常にすべての列をクエリする

select * を見るたびに、奇妙な表情で見てしまうはずです。本当にすべてのデータ列を返す必要があるのでしょうか?

ほとんどの場合、必要ありません。 Select * を実行するとテーブル全体がスキャンされ、オプティマイザがインデックス スキャンなどの最適化を完了できなくなります。列が多すぎると、サーバーの I/O、メモリ、CPU 消費量も増加します。 実際にすべての列をクエリする必要がある場合でも、* ではなくすべての列を 1 つずつリストする必要があります。

4. 同じデータを繰り返しクエリする

注意しないと、同じクエリを何度も実行し、毎回まったく同じデータを返すという間違いを犯しやすくなります。

たとえば、ユーザーのコメント領域にあるユーザーのアバターの URL を照会する必要がある場合、ユーザーが複数回コメントすると、このデータを繰り返し照会する可能性があります。 これを処理するより良い方法は、データが最初にクエリされたときにデータをキャッシュし、その後使用されるときにキャッシュから直接取得することです。

3. 追加のレコードはスキャンされますか?

クエリが必要なデータのみを検索していることを確認したら、次にクエリがスキャンするデータが多すぎないかどうかを確認する必要があります。 MySQL の場合、クエリのオーバーヘッドを測定するための最も単純な 3 つのメトリックは次のとおりです。

• 応答時間
• スキャンされた行数
• 返される行数

単一のメトリックでクエリのコストを完全に測定することはできませんが、クエリを実行するときに MySQL がアクセスする必要があるデータの量を大まかに反映し、クエリの実行にかかる実際の時間を大まかに見積もることができます。 これら 3 つのインジケーターは MySQL スロー ログに記録されるため、スロー ログ レコードを確認すると、スキャンする行数が多すぎるクエリを見つけることができます。

スロー クエリ: 応答時間がしきい値 (long_query_time、デフォルトは 10 秒) を超える MySQL のステートメントを記録し、スロー ログにスロー クエリを記録するために使用されます。 変数 slow_query_long を通じてスロー クエリを有効にすることができます。デフォルトでは閉じられています。スロー ログは、検査および分析のために slow_log テーブルまたはファイルに記録できます。

1. 応答時間

応答時間は、サービス時間とキュー時間という 2 つの部分の合計です。 サービス時間とは、データベースがクエリを処理するのに実際にかかった時間を指します。 キュー時間とは、サーバーが何らかのリソース（I/O 操作の待機、行ロックの待機など）を待機しているために、クエリを実際に実行しない時間を指します。

さまざまな種類のアプリケーション ストレス下での応答時間には、一貫したパターンや式はありません。 ストレージ エンジン ロック (テーブル ロック、行ロック)、高同時実行リソースの競合、ハードウェア応答など、多くの要因が応答時間に影響する可能性があります。したがって、応答時間は、状況に応じて、問題の結果にも原因にもなり得ます。

クエリの応答時間を確認するときに最初に自問する必要があるのは、この応答時間が妥当な値であるかどうかです。

2. スキャンされた行数と返された行数

クエリを分析する場合、クエリによってスキャンされた行数を確認したり、追加のレコードがスキャンされたかどうかを分析したりすることが役立ちます。

すべての行のアクセスコストが同じではないため、このメトリックは不正なクエリを識別するのに最適ではない可能性があります。 短い行は非常に速くアクセスされ、メモリ内の行はディスク上の行よりもはるかに速くアクセスできます。

理想的には、スキャンされる行数と返される行数は同じである必要があります。 しかし、実際には、このような美しいことは一般的ではありません。たとえば、結合クエリを実行する場合、スキャンされた行数と返される行数の比率は通常非常に小さく、通常は 1:1 から 10:1 の間ですが、この値が非常に大きくなることもあります。

3. スキャンされた行数とアクセスタイプ

クエリのオーバーヘッドを評価するときは、テーブル内のデータ行を見つけるコストを考慮する必要があります。 MySQL には、結果の行を検索して返すことができるアクセス メソッドがいくつかあります。 これらのアクセス メソッドでは、結果を返すために多くの行にアクセスする必要があり、一部のアクセス メソッドではスキャンせずに結果を返す場合があります。

実行プランの EXPLAIN ステートメントの type 列は、アクセス タイプを反映します。 アクセスには、フルテーブルスキャンからインデックススキャン、範囲スキャン、ユニークインデックス、定数インデックスなど、さまざまな種類があります。 ここでリストされている速度は遅いものから速いものまで、またスキャンされる行数は多いものから少ないものの順になっています。

クエリが適切なアクセス タイプを見つけられない場合、最善の解決策は通常、適切なインデックスを追加することです。これは、前に説明したインデックスの問題です。 これで、クエリの最適化にとってインデックスがなぜそれほど重要なのかが明らかになったはずです。 インデックスを使用すると、MySQL は最小限の行数をスキャンして、必要なレコードを最も効率的に見つけることができます。

クエリが大量のデータをスキャンしても、数行しか返さない場合は、通常、次の手法を試して最適化することができます。

• インデックス カバーリング スキャンを使用して、必要なすべての列をインデックスに格納します。これにより、ストレージ エンジンは、テーブルに戻って対応する行を取得することなく結果を返すことができます。
  
• テーブル構造を最適化します。 たとえば、別のサマリー テーブルを使用してクエリを完了します。
• MySQL オプティマイザーがより最適な方法でクエリを実行できるように、複雑なクエリを書き直します。

上記は、MySQL クエリ速度を最適化する方法の詳細です。MySQL クエリ速度の最適化の詳細については、123WORDPRESS.COM の他の関連記事に注目してください。