MySQL実行計画を学ぶ

1. 実施計画の概要

実行プランとは、MySQL クエリ オプティマイザーによって最適化された後の SQL ステートメントの特定の実行方法を指します。 MySQL では、実行プランに関する関連情報を取得するための EXPLAIN ステートメントが提供されています。 EXPLAIN ステートメントは、関連するステートメントを実際に実行するのではなく、クエリ オプティマイザーを通じてステートメントを分析し、最適なクエリ ソリューションを見つけて、対応する情報を表示することに注意してください。

実行プランは通常、SQL パフォーマンス分析、最適化、その他のシナリオで使用されます。 Explain 結果を通じて、データ テーブルのクエリ順序、データ クエリ操作の操作タイプ、ヒットできるインデックス、実際にヒットするインデックス、各データ テーブルでクエリされるレコードの行数などの情報を知ることができます。

explain 実行プランは、SELECT、DELETE、INSERT、REPLACE、および UPDATE ステートメントをサポートします。通常、選択クエリ ステートメントを分析するために使用します。

2. 実行計画の実践

次のクエリ ステートメントの実行プランを簡単に見てみましょう。

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+-----------+-----------+---------+-----------------+----------+---------+----------+-----------+------------+-------------+
| id | select_type | テーブル | パーティション | タイプ | 可能なキー | キー | キー長 | ref | 行 | フィルター済み | 追加 |
+----+-------------+-----------+-----------+---------+-----------------+----------+---------+----------+-----------+------------+-------------+
| 1 | PRIMARY | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | where の使用 |
| 2 | サブクエリ | dept_emp | NULL | インデックス | PRIMARY、dept_no | PRIMARY | 16 | NULL | 331143 | 100.00 | インデックスを使用 |
+----+-------------+-----------+-----------+---------+-----------------+----------+---------+----------+-----------+------------+-------------+

ご覧のとおり、実行プランの結果には 12 列あります。各列の意味は次の表にまとめられています。

実行プランの重要な列をいくつか見てみましょう。

id:

SELECT識別子。これはクエリ内の SELECT の序数です。行が他の行の結合結果を参照する場合、値は NULL になることがあります。 ID が同じ場合は実行順序は上から下になりますが、ID が異なる場合は ID 値が大きいほど優先度が高くなり、先に実行されます。

選択タイプ:

クエリのタイプ。一般的な値は次のとおりです。

• SIMPLE: 単純なクエリ。UNION またはサブクエリは含まれません。
• PRIMARY: クエリにサブクエリまたはその他の部分が含まれている場合、外側の SELECT は PRIMARY としてマークされます。
• SUBQUERY: サブクエリ内の最初の SELECT。
• UNION: UNION ステートメントでは、UNION の後に表示される SELECT。
• DERIVED: FROM に現れるサブクエリは DERIVED としてマークされます。
• UNION RESULT: UNION クエリの結果。

テーブル：

クエリに使用されるテーブル名を示します。各行には対応するテーブル名があります。通常のテーブルに加えて、テーブル名は次の値になることもあります。

• <unionM,N>: この行は、ID M と N の行の UNION 結果を参照します。
• <derivedN>: この行は、ID N のテーブルによって生成された派生テーブルの結果を参照します。 FROM 句のサブクエリから派生テーブルを作成することが可能です。
• <subqueryN>: この行は、ID N のテーブルによって生成されたマテリアライズド サブクエリの結果を参照します。

タイプ：

クエリ実行のタイプ。クエリがどのように実行されるかを説明します。すべての値の最高から最低までの順序は次のとおりです。

システム > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

いくつかの一般的なタイプの具体的な意味は次のとおりです。

• system: テーブルで使用されるエンジンがテーブル内の行数を正確にカウントし (MyISAM など)、テーブルに行が 1 つしかない場合、アクセス メソッドは system になります。これは const の特殊なケースです。
• const: テーブルには一致する行が最大 1 つあり、1 つのクエリで見つけることができます。主キーまたは一意のインデックスのすべてのフィールドをクエリ条件として使用する場合によく使用されます。
• eq_ref: 結合されたテーブルをクエリする場合、前のテーブルの行に対応する行は現在のテーブルに 1 つだけあります。これは、system と const 以外では最適な結合方法で、主キーまたは一意のインデックスのすべてのフィールドを結合条件として使用する場合によく使用されます。
• ref: クエリ条件として共通インデックスを使用します。クエリ結果には、条件を満たす複数の行が見つかる場合があります。
• index_merge: クエリ条件で複数のインデックスが使用される場合、インデックス マージ最適化が有効になっていることを意味します。この場合、実行プランのキー列には、使用されるインデックスがリストされます。
• range: インデックス列に対して範囲クエリを実行します。実行プランのキー列は、使用されるインデックスを示します。
• index: クエリはインデックス ツリー全体をトラバースします。これは ALL と似ていますが、インデックスがスキャンされ、インデックスが通常はメモリ内にあるため、より高速です。
• ALL: テーブル全体をスキャンします。

可能なキー:

possible_keys 列は、クエリを実行するときに MySQL が使用する可能性のあるインデックスを示します。この列が NULL の場合、使用できるインデックスがないことを意味します。この場合、WHERE 句で使用されている列をチェックして、これらの列の 1 つ以上にインデックスを追加することでクエリのパフォーマンスを向上できるかどうかを確認する必要があります。

鍵：

キー列は、MySQL で実際に使用されるインデックスを示します。 NULL の場合、インデックスは使用されません。

キー長さ:

key_len 列は、MySQL で実際に使用されるインデックスの最大長を示します。結合インデックスが使用される場合、複数の列の長さの合計になる場合があります。ニーズを満たす限り、短ければ短いほど良いです。 key 列に NULL が表示される場合、key_len 列にも NULL が表示されます。

行:

行の列は、テーブルの統計と選択条件に基づいて、検索されるレコード数または読み取られる行数の概算を表します。値が小さいほど、優れています。

余分な：

この列には、MySQL がクエリを解析する方法に関する追加情報が含まれています。この情報は、MySQL がクエリを実行する方法をより正確に理解するのに役立ちます。一般的な値は次のとおりです。

• filesort の使用: ソートには外部インデックス ソートが使用され、テーブルの内部インデックスはソートには使用されません。
• 一時テーブルの使用: MySQL では、クエリ結果を格納するために一時テーブルを作成する必要があります。これは、ORDER BY および GROUP BY でよく使用されます。
• インデックスの使用: クエリがカバーリング インデックスを使用し、テーブルを返す必要がないため、クエリの効率が非常に高いことを示します。
• インデックス条件の使用: クエリ オプティマイザーがインデックス条件プッシュダウン機能を使用することを選択したことを示します。
• where: を使用すると、クエリは条件付きフィルタリングに WHERE 句を使用することを示します。これは通常、インデックスが使用されていない場合に発生します。
• 結合バッファの使用 (ブロックネストループ): これはテーブル結合クエリ方法です。駆動テーブルがインデックスを使用しない場合、MySQL は最初に駆動テーブルを読み取って結合バッファに格納し、次に駆動テーブルと駆動テーブルをトラバースしてクエリを実行します。

Extra 列に Using filesort または Using temporary が含まれている場合、MySQL のパフォーマンスに問題が発生する可能性があるため、可能な限り回避する必要があることに注意してください。

以上がMySQL実行計画の学習の詳細な内容です。MySQL実行計画の詳細については、123WORDPRESS.COMの他の関連記事に注目してください。