MySQL インデックス最適化の説明

日常業務では、実行に時間のかかる SQL ステートメントを記録するために、スロー クエリを実行することがあります。これらの SQL ステートメントを見つけても、作業が完了したわけではありません。SQL ステートメントがインデックスを使用しているかどうか、および完全なテーブル スキャンが実行されているかどうかを確認するために、explain コマンドを使用してこれらの SQL ステートメントの実行プランを表示することがよくあります。これは、explain コマンドで表示できます。したがって、MySQL のコストベースのオプティマイザについて深く理解でき、また、オプティマイザが考慮する可能性のあるアクセス戦略や、SQL ステートメントを実行するときにオプティマイザが採用すると予想される戦略に関する多くの詳細も取得できます。

-- 実際の SQL、ユーザー名が Jefabc である従業員を検索します。select * from emp where name = 'Jefabc';
-- SQL がインデックスを使用するかどうかを確認します。その前に explain を追加するだけです。select * from emp where name = 'Jefabc'; 

expainからの情報には、id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extraの10の列があります。

概要説明:
id: 識別子を選択
select_type: クエリのタイプを示します。
テーブル: 出力結果セットのテーブル
パーティション: 一致するパーティション
タイプ: テーブルの接続タイプを示します
possible_keys: クエリ時に使用できるインデックスを示します
キー: 実際に使用されるインデックスを示します
key_len: インデックスフィールドの長さ
ref: 列とインデックスの比較
行数: スキャンされた行数（推定行数）
フィルタリング: テーブル条件によってフィルタリングされた行の割合
追加：実行の説明と説明

これらのフィールドの表示例については、次のとおりです。

1. ID

SELECT識別子。これはSELECTのクエリシーケンス番号です

私の理解では、それは SQL が実行される順序です。SQL は大きいものから小さいものの順に実行されます。

1. IDが同じ場合、実行順序は上から下になります

2. サブクエリの場合はID番号が増加します。ID値が大きいほど優先度が高くなり、早く実行されます。

3. ID が同じ場合はグループとみなして上から下へ実行します。すべてのグループで、ID 値が大きいほど優先度が高くなり、早く実行されます。

-- R&D 部門で勤務し、名前が Jef で始まる従業員を表示します。クラシック クエリ explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = 'R&D Department'; 

2. 選択タイプ

クエリ内の各選択句のタイプを示します

（１）SIMPLE（UNIONやサブクエリなどを使用しない単純なSELECT）
（2）PRIMARY（サブクエリ内の最も外側のクエリ。クエリに複雑なサブパーツが含まれている場合、最も外側の選択はPRIMARYとしてマークされます）
（３）UNION（UNION内の２番目以降のSELECT文）
(4)DEPENDENT UNION（UNION内の2番目以降のSELECT文は外側のクエリに依存する）
(5)UNION RESULT（UNIONの結果、UNION文の2番目のSELECTから始まる後続のすべてのSELECT）
（6）SUBQUERY（サブクエリの最初のSELECT、結果は外部クエリに依存しない）
(7)従属サブクエリ（外部クエリに依存するサブクエリ内の最初のSELECT）
（８）DERIVED（派生テーブルSELECT、FROM句のサブクエリ）
（9）キャッシュ不可能なサブクエリ（サブクエリの結果はキャッシュできず、外部リンクの最初の行を再評価する必要がある）

3. 表

このステップでアクセスしたデータベースのテーブル名を表示します（この行のデータがどのテーブルに関するものかを示します）。実際のテーブル名ではなく、上記の e や d などの略語である場合や、ステップの実行結果の略語である場合もあります。

タイプ

テーブル アクセス メソッドは、MySQL がテーブル内の必要な行を検索する方法を示し、「アクセス タイプ」とも呼ばれます。

一般的なタイプは、ALL、index、range、ref、eq_ref、const、system、NULL です (左から右へ、パフォーマンスは悪い順から良い順)

ALL: フルテーブルスキャン。MySQLはテーブル全体をスキャンして一致する行を検索します。

index: フル インデックス スキャン。index と ALL の違いは、インデックス タイプはインデックス ツリーのみをトラバースすることです。

範囲: インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。

ref: 上記のテーブルの結合一致条件、つまりインデックス列の値を見つけるために使用される列または定数を示します。

eq_ref: ref と似ていますが、使用されるインデックスが一意のインデックスであるという点が異なります。インデックス キー値ごとに、テーブル内で一致するレコードは 1 つだけです。簡単に言えば、複数のテーブルを結合する場合、結合条件として主キーまたは一意のキーを使用します。

const、system: MySQL はクエリの一部を最適化し、それを定数に変換するときに、これらのタイプのアクセスを使用します。 where リストに主キーを入れると、MySQL はクエリを定数に変換できます。System は const 型の特殊なケースです。クエリ テーブルに行が 1 つしかない場合は、system を使用します。

NULL: MySQL は最適化中にステートメントを分解し、実行中にテーブルやインデックスにアクセスしません。たとえば、インデックス列から最小値を選択することは、単一のインデックス検索で実行できます。

5. 可能なキー

MySQL がテーブル内のレコードを検索するために使用できるインデックスを示します。クエリに関係するフィールドにインデックスがある場合、そのインデックスがリストされますが、クエリでは使用されない可能性があります (クエリが使用できるインデックス。インデックスがない場合は null が表示されます)

この列は、EXPLAIN 出力に表示されるテーブルの順序とは完全に独立しています。これは、possible_keys 内の一部のキーが、テーブルが生成された順序で実際に使用できないことを意味します。
列が NULL の場合、関連付けられたインデックスはありません。この場合、WHERE 句をチェックして、特定の列またはインデックス作成に適した列を参照しているかどうかを確認することで、クエリのパフォーマンスを向上させることができます。その場合は、適切なインデックスを作成し、EXPLAIN を使用してクエリを再度確認してください。

6. キー

キー列には、MySQLが実際に使用すると決定したキー（インデックス）が表示されます。これは、possible_keysに含める必要があります。

インデックスが選択されなかった場合、キーは NULL になります。 MySQL で possible_keys 列のインデックスを強制的に使用または無視するには、クエリで FORCE INDEX、USE INDEX、または IGNORE INDEX を使用します。

7. キーの長さ

インデックスで使用されるバイト数を示します。この列は、クエリで使用されるインデックスの長さを計算するために使用できます (key_len によって表示される値は、インデックス フィールドの最大可能長であり、実際に使用される長さではありません。つまり、key_len はテーブル定義に基づいて計算され、テーブルから取得されるものではありません)。

長さが短いほど、精度を失わずに良くなります。

8. 参照

列とインデックスの比較。上記のテーブルの結合一致条件を示します。つまり、インデックス列の値を見つけるためにどの列または定数が使用されるかを示します。

9. 行

結果セットの行数を推定します。つまり、MySQL はテーブル統計とインデックス選択に基づいて、必要なレコードを見つけるために必要な行数を推定します。

10. 追加

この列には、MySQL がクエリを解決する方法に関する詳細情報が含まれています。いくつかのケースがあります。

where の使用: テーブル内のすべての情報を読み取る代わりに、インデックスを通じてのみ必要なデータを取得できます。これは、テーブルに対して要求されたすべての列が同じインデックスの一部である場合に発生します。つまり、MySQL サーバーは、ストレージ エンジンが行を取得した後にフィルター処理を行います。

一時テーブルの使用: MySQL が結果セットを保存するために一時テーブルを使用する必要があることを示します。これは、クエリの並べ替えやグループ化、一般的なグループ化、順序付けでよく使用されます。

ファイルソートの使用: クエリに順序付け操作が含まれており、インデックスを使用してソート操作を完了できない場合は、「ファイルソート」と呼ばれます。

-- Test Extra のファイルソート
select * from emp order by name; を説明します。

結合バッファの使用: この値は、結合条件を取得するときにインデックスが使用されず、中間結果を格納するために結合バッファが必要であることを強調します。この値が表示される場合は、クエリの特定の状況に応じて、パフォーマンスを向上させるためにインデックスを追加する必要がある場合があることに注意してください。

不可能な where: この値は、where ステートメントの結果に該当する行がない (統計を収集しても結果が不可能) ことを強調します。

最適化されたテーブルを選択: この値は、オプティマイザーがインデックスのみを使用して集計関数の結果から 1 行のみを返す可能性があることを意味します。

テーブルが使用されていません: クエリ ステートメントは from デュアルを使用しているか、from 句を含んでいません

-- select now() from Dual; について説明します。

要約:

• EXPLAINでは、トリガー、ストアドプロシージャ、またはユーザー定義関数がクエリにどのように影響するかについてはわかりません。
• EXPLAINはさまざまなキャッシュを考慮しない
• EXPLAINは、クエリ実行時にMySQLが行う最適化作業を表示しません。
• 一部の統計は推定値であり、正確な値ではありません
• EXPALIN は SELECT 操作のみを説明できます。他の操作は SELECT として書き換えてから、実行プランを確認する必要があります。

統計を収集しても結果は得られない

これで、MySQL インデックス最適化の説明に関するこの記事は終了です。MySQL インデックス最適化の説明に関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。