MySQLクエリのパフォーマンスを分析する方法

クエリの最適化、インデックスの最適化、テーブル設計の最適化は密接に関連しています。 MySQL クエリステートメントの作成経験が豊富な場合は、効率的なクエリをサポートするためにテーブルとインデックスを設計する方法がわかります。同様に、テーブル設計を知ることは、テーブル構造がクエリステートメントにどのように影響するかを理解するのにも役立ちます。したがって、テーブル設計とインデックス設計が適切であっても、クエリステートメントが適切に記述されていない場合は、クエリのパフォーマンスは低下します。

高速なクエリを記述する前に、速度は応答時間で測定されることを覚えておくことが重要です。クエリステートメントは複数のサブタスクで構成される大きなタスクであり、各サブタスクは時間を消費します。クエリを最適化するには、サブタスクの数をできるだけ減らすか、サブタスクの実行速度を速める必要があります。注: 場合によっては、クエリがシステム内の他のクエリに与える影響も考慮する必要があります。この場合、リソースの消費を可能な限り削減する必要もあります。 _ 一般的に、クエリライフサイクルは、クエリステートメントの解析、クエリの計画、実行プロセス、クライアントに返されるデータなど、クライアントからサーバーへの対話シーケンスダイアグラム全体を通じて実行されるものと考えることができます。実行はクエリプロセスの最も重要な部分であり、ストレージエンジンからデータ行を取得するための多数の呼び出しや、グループ化や並べ替えなどのデータの後処理が含まれます。

これらのタスクをすべて完了した後、クエリは、ネットワーク転送エラー、CPU 処理、データ統計と戦略計画、ロックの待機、ストレージエンジンからのデータ行の取得にも時間がかかります。これらの呼び出しは、メモリ操作、CPU 操作、および I/O 操作で時間を消費します。いずれの場合も、これらの操作が乱用されたり、実行回数が多すぎたり、実行速度が遅すぎたりすると、追加の時間オーバーヘッドが発生します。クエリ最適化の目的は、操作を排除または削減するか、操作の実行速度を上げることによって、このような状況を回避することです。

クエリライフサイクルの正確な図を描くことはできないことに注意することが重要です。私たちの目的は、クエリライフサイクルを理解し、これらのステップにどれだけの時間がかかるかを考えることの重要性を示すことです。この基礎を基に、クエリステートメントの最適化を開始できます。

スロークエリの基礎: データ取得の最適化

クエリのパフォーマンスが低下する最も根本的な理由は、処理するデータが多すぎることです。一部のクエリでは大量のデータをフィルタリングする必要があり、最適化することはできません。しかし、これは正常な状況ではありません。ほとんどの不適切なクエリは、アクセスするデータを減らすことで最適化できます。パフォーマンスが低いクエリを分析するには、次の 2 つの手順が役立ちます。

アプリが必要なデータ以外のデータにアクセスしていないか確認します。通常、これはアプリケーションが取得するデータの行または列が多すぎることを意味します。
MySQL サーバーが必要以上に多くの行を解析しているかどうかを確認します。

データベースから不要なデータが要求されていないか確認する

一部のクエリは、データベースサーバーから必要なデータを要求し、それを破棄します。これにより、MySQL サーバーの作業が増加し、ネットワーク負荷が増加し、アプリケーションサーバーのメモリと CPU リソースの消費量が増加します。典型的な間違いをいくつか挙げます。

不要な行の取得: MySQL は結果セット全体を計算して返すのではなく、必要な結果のみを提供すると想定するのはよくある誤解です。通常、この間違いは、他のデータベースシステムに精通している人に発生します。これらの開発者は、多数の行を返す SELECT ステートメントを使用し、最初の N 行を抽出し、返された結果セットを使用しないことに慣れています (たとえば、情報 Web サイトから最新の 100 件の記事を取得し、そのうちの 10 件だけをフロントエンドに表示します)。 MySQL は 10 行のデータを取得した後にクエリを停止すると思われますが、実際には MySQL は完全なデータセットを取得します。その後、クライアントはすべてのデータを取得し、その大部分を破棄します。最善の解決策は、クエリに LIMIT 条件を追加することです。
複数テーブル結合クエリですべての列を取得します。映画「Age of Dinosaurs」のすべての俳優を取得する必要がある場合、次のように SQL ステートメントを記述しないでください。

sakila.actor から * を選択
INNER JOIN sakila.file_actor USING(actor_id)
INNER JOIN sakila.file USING (film_id)
ここで、sakila.film.title = 'アカデミー ダイナソー';

これにより、ユニオンクエリに関係する 3 つのテーブルのすべての列が返されます。より良い方法は、次のように書くことです。

sakila.actor.* から sakila.actor を選択
INNER JOIN sakila.file_actor USING(actor_id)
INNER JOIN sakila.file USING (film_id)
ここで、sakila.film.title = 'アカデミー ダイナソー';

すべてのデータ列を取得します。SELECT * のようなクエリが表示されたら、すべての列が本当に必要かどうか疑問に思ってください。おそらくそうではないでしょう。すべてのデータ列をフェッチすると、カバーするインデックスが無効になり、I/O 負荷、メモリ消費、CPU 負荷が増加します。このため、一部の DBA は SELECT * を単純に無効にしており、これにより、テーブル列を変更するユーザーによって発生する問題を軽減できます。もちろん、不要なデータを要求することは必ずしも悪いことではありません。調査の結果、このアプローチによりコードの再利用性が向上するため、開発作業を簡素化できることがわかりました。パフォーマンスに影響することがわかっている限り、それは正当な理由になります。同様に、アプリケーションで特定のキャッシュメカニズムを使用すると、キャッシュヒット率も向上します。オブジェクト全体をフェッチしてキャッシュするには、オブジェクトの一部をフェッチする複数の個別のクエリを実行すると、より適切に処理できます。
同じデータを繰り返し取得する: 注意しないと、アプリケーション内で同じデータを取得するコードを簡単に記述してしまいます。たとえば、コメントのリストにユーザーのプロフィール写真を表示する場合は、コメントごとに 1 回取得する場合があります。より効果的な方法は、最初のフェッチ後にデータをキャッシュし、コメントリストで直接使用することです。

MySQLが大量のデータを処理していないか確認する

クエリが不要なデータを取得していないことを確認したら、結果を返す前に処理するデータが多すぎるクエリを探すことができます。 MySQL では、最も単純なクエリ消費標準は次のとおりです。

応答時間
処理された行数
返される行数

これらのメトリックはいずれもクエリのパフォーマンスを完璧に測定するものではありませんが、クエリ実行時に内部処理中に MySQL が取得するデータの量と、クエリの実行速度を大まかに把握できます。これら 3 つの基準はスロークエリログに記録されるため、スロークエリログから過剰なデータ処理を伴うクエリを検出することが、クエリの最適化のベストプラクティスとなります。

応答時間まず、クエリの応答時間は私たちが目にする症状であることに注意してください。実際には、応答時間は私たちが考えるよりも複雑です。応答時間は、サービス時間とキュー時間の 2 つの部分で構成されます。サービス時間とは、サーバーが実際にクエリを処理するのにかかる時間です。キュー時間とは、サーバーが実際にクエリを実行していない時間であり、I/O 操作の完了、行ロックの解放など、何らかのリソースを待機している時間です。問題は、各部分の時間を個別に測定できない限り、応答時間をこれら 2 つの部分に正確に分割できないことですが、これは困難です。最も一般的で重要なケースは、I/O のブロックとロックの待機ですが、これが 100% 当てはまるわけではありません。

その結果、さまざまな負荷条件下では応答時間が一定にはなりません。ストレージエンジンロック、高い同時実行性、ハードウェアなどの他の要因も応答時間に影響を与える可能性があります。したがって、応答時間を確認するときは、まず応答時間の原因がこのクエリのみであるかどうかを判断する必要があります。クエリ応答時間は、Query Quick Upper Bound Estimate (QUBE) メソッドを計算することによって推定できます。QUBE メソッドでは、クエリプランと使用されるインデックスを調べ、必要な順次およびランダム I/O アクセス操作の数を決定し、その数をマシンのハードウェアが各操作を実行できる時間に掛けます。すべての時間を合計することで、クエリ応答の遅さがクエリ自体によるものか、他の理由によるものか評価できます。

処理されて返された行数クエリを分析するときは、処理された行数の観点から考えると便利です。これにより、クエリが必要なデータをどのように取得しているかを直感的に把握できるためです。ただし、これは不正なクエリを見つけるための完璧な測定ツールではありません。すべての行アクセスが同一であるわけではありません。行数が少ないほどアクセスが速くなり、メモリから行を取得する方がディスクから取得するよりもはるかに高速になります。

理想的には、処理された行と返される行は同一になりますが、実際にはそうなることはほとんどありません。たとえば、結合インデックスを使用して返される行を構築する場合、サーバーは返される行データを生成するために複数の行からデータをフェッチする必要があります。処理される行と返される行の比率は通常 1:1 から 10:1 の間と小さいですが、場合によっては桁違いに大きくなることもあります。

データ行の処理と取得の種類

クエリのコストを考えるときは、テーブルから 1 行を取得するコストを考慮してください。 MySQL はさまざまな取得方法を使用してデータ行を検索し返します。複数の行を処理する必要があるものもあれば、検査なしで結果を返すものもあります。

データの取得方法は、EXPLAIN 出力結果の type 列に表示されます。完全なテーブルスキャン、インデックススキャン、範囲スキャン、一意のインデックス検索、および定数が含まれます。上記の各方法は、読み取られるデータの量が連続的に減少するため、前の方法よりも高速になります。型を取得することを覚える必要はありませんが、基本的な概念を理解する必要があります。

適切な検索タイプがない場合、問題を解決する最善の方法は適切なインデックスを追加することです。インデックスにより、MySQL は検査するデータが少なくなり、行をより効率的に取得できるようになります。たとえば、次の簡単なクエリを考えてみましょう。

EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

このクエリは 10 行のデータを返し、EXPLAIN コマンドは、MySQL が ref タイプを使用して idx_fk_film_id インデックスでクエリステートメントを実行することを示しています。

**********************1. 行************************
id: 1
選択タイプ: シンプル
テーブル:映画俳優
タイプ: ref 
可能)キー: idx_fk_film_id
キー: idx_fk_film_id
キーの長さ: 2
参照: 定数
行数: 10
余分な：

EXPLAIN コマンドは、クエリを完了するには 10 行のみをフェッチする必要があると MySQL が見積もっていることを示しています。つまり、クエリオプティマイザーは、クエリをより効率的にするためにフェッチタイプを選択する方法を知っています。クエリに適したインデックスがない場合はどうなりますか? MySQL は最適ではないフェッチタイプを使用し、テーブルインデックスを削除した後に結果を確認する必要があります。

ALTER TABLE sakila.film_actor で外部キー fk_film_actor_film を削除します。
ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;
EXPLAIN SELECT * FROM sakila.film_actor WHERE file_id=1;

**********************1. 行************************
id: 1
選択タイプ: シンプル
テーブル:映画俳優
タイプ: すべて 
可能)キー: NULL
キー: NULL
キー長さ: NULL
参照: NULL
行数: 5073
追加: where の使用

予想どおり、フェッチタイプはフルテーブルスキャン (ALL) になり、MySQL はクエリを完了するために 5073 行のデータ処理が必要であると見積もります。 Extra 列の where を使用すると、MySQL サーバーが WHERE 条件を使用して、ストレージエンジンによって読み取られた条件を満たさない他のデータを破棄することがわかります。通常、MySQL は WHERE 条件を次の 3 つの方法で使用します (最適なものから最適なものの順に)。

ストレージエンジンレイヤーで実行されるインデックス検索操作を通じて一致しないデータ行を削除します。
カバーリングインデックス ([Extra] 列に [Using index] として表示) を使用してデータ行へのアクセスを回避し、結果を取得した後に条件を満たさないデータを除外します。これはサーバーレベルで行われますが、テーブルから行を読み取る必要はありません。
データテーブルからデータ行を取得し、一致しないデータ ([Extra] 列に [Using where] として表示される) をフィルター処理します。これはサーバーレベルで行われ、データをフィルター処理する前にテーブルから行を読み取る必要があります。

次の例は、適切なインデックスを持つことの重要性を示しています。適切なインデックスは、適切なデータ取得パターンを使用し、必要な行のみを処理するのに役立ちます。ただし、インデックスを追加しても、MySQL が常に一貫した行を取得して返すわけではありません。たとえば、以下の COUNT() 集計メソッド。

SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;

このクエリは 200 行のみを返しますが、返される結果セットを構築する前に数千行を読み取る必要があります。このタイプのクエリステートメントでは、インデックスを使用しても、処理する必要があるデータ行の数を減らすことはできません。

残念ながら、MySQL では、返された結果セットを構築するためにフェッチされた行数は表示されず、フェッチされた行の合計数のみが示されます。多くの行は WHERE 条件によってフィルタリングされ、返される結果セットには影響しません。前の例では、sakila.film_actor インデックスを削除した後、クエリはデータテーブルのすべての行を取得しますが、結果セットとして 10 個のデータのみを取得します。サーバーがフェッチした行数と返した行数を理解することは、クエリ自体を理解するのに役立ちます。多数の行を取得する必要があるが、結果では数行しか使用しない場合は、次の手順を実行してこの問題を解決できます。