MySQL における explain の役割の詳細な説明

1. MYSQLインデックス

インデックス: MySQL がデータを効率的に取得するのに役立つデータ構造。検索効率を向上させるために使用され、辞書に例えることができます。これは、ソートされた高速検索データ構造として簡単に理解できます。

インデックスの役割:クエリとソートを容易にします (したがって、インデックスを追加すると、where ステートメントと order by ソートステートメントに影響します)。

データベースは、データに加えて、特定の検索アルゴリズムを満たし、何らかの方法でデータを参照するデータ構造も維持します。これにより、これらのデータ構造に高度な検索アルゴリズムを実装できるようになります。これらのデータ構造はインデックスです。

インデックス自体も非常に大きく、すべてをメモリに保存することは不可能であるため、インデックスはインデックスファイルの形式でディスクに保存されることがよくあります。

インデックスについて話すとき、特に指定がない限り、通常は B ツリーインデックスです。 (クラスター化インデックス、複合インデックス、プレフィックスインデックス、および一意のインデックスはすべて、デフォルトでは B+ ツリーインデックスです)。B ツリーインデックスに加えて、ハッシュインデックスもあります。

アドバンテージ：

A. データ取得効率を向上させ、データベースIOコストを削減する
B. インデックス列でデータをソートすると、データのソートコストと CPU 消費が削減されます。

欠点:

A. インデックスは、主キーとインデックスフィールドを格納し、エンティティテーブルのレコードを指すテーブルでもあるため、インデックスもスペースを占有します。
B. テーブルに対して INSERT、UPDATE、または DELETE 操作を実行すると、MYSQL はデータを更新するだけでなく、更新されるたびにインデックスファイルに追加されたインデックス列フィールドの対応する情報も保存します。

実際の運用環境では、段階的に分析し、最適なインデックスを最適化して確立し、クエリ条件を最適化する必要があります。

インデックス分類:

1. 単一値インデックス: インデックスには 1 つのフィールドのみが含まれ、テーブルには複数の単一列インデックスを含めることができます。
2. ユニークインデックス列の値は一意である必要がありますが、null 値も許可されます。
3. 複合インデックス: インデックスには複数の列が含まれます

テーブルに作成するインデックスは 5 つ以下にすることをお勧めします。

文法：

1. myTable に [UNIQUE] INDEX indexName を作成します (columnName(length));
2. ALTER myTable に [UNIQUE] INDEX [indexName] ON (columnName(length)) を追加します。

削除: DROP INDEX [indexName] ON myTable;

ビュー: SHOW INDEX FROM table_name\G;

2. EXPLAINの役割

EXPLAIN: MySQL オプティマイザーが SQL クエリステートメントを実行する方法をシミュレートし、MySQL が SQL ステートメントをどのように処理するかを知ることができます。クエリステートメントまたはテーブル構造のパフォーマンスのボトルネックを分析します。

mysql> tb_user から * を選択します。
+----+-------------+---------+-------+---------------+-------+-------+------+------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------+-------+---------------+-------+-------+------+------+
| 1 | シンプル | tb_user | すべて | NULL | NULL | NULL | NULL | 1 | NULL |
+----+-------------+---------+-------+---------------+-------+-------+------+------+

(a) id列:

（１）同じIDの実行順序は上から下へ

mysql>説明 
  -> tb_order tb1 から * を選択
  -> tb_product tb2 を tb1.tb_product_id = tb2.id に左結合します
  -> tb_user tb3 を tb1.tb_user_id = tb3.id に LEFT JOIN します。
+----+-------------+-------+--------+---------------+----------+---------+------------------------+-------+-------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+--------+---------------+----------+---------+------------------------+-------+-------+
| 1 | シンプル | tb1 | すべて | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | シンプル | tb2 | eq_ref | プライマリ | プライマリ | 4 | product.tb1.tb_product_id | 1 | NULL |
| 1 | シンプル | tb3 | eq_ref | プライマリ | プライマリ | 4 | product.tb1.tb_user_id | 1 | NULL |
+----+-------------+-------+--------+---------------+----------+---------+------------------------+-------+-------+

（２）サブクエリの場合は、id番号が自動的に増加します。id値が大きいほど優先度が高くなり、早く実行されます。

mysql>説明
  -> tb_product tb1 から * を選択します。tb1.id = (tb_order tb2 から tb_product_id を選択します。id = tb2.id =1);
+----+-------------+-------+-------+---------------+----------+-------+-------+------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+----------+-------+-------+------------+
| 1 | プライマリ | tb1 | const | プライマリ | プライマリ | 4 | const | 1 | NULL |
| 2 | サブクエリ | tb2 | ALL | NULL | NULL | NULL | NULL | 1 | where の使用 |
+----+-------------+-------+-------+---------------+----------+-------+-------+------------+

（３）イドは同じであり、異なっており、同時に存在する

mysql>説明 
  -> select * from(select * from tb_order tb1 where tb1.id =1) s1,tb_user tb2 where s1.tb_user_id = tb2.id;
+----+--------------+------------+---------+---------------+----------+-------+-------+-------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+--------------+------------+---------+---------------+----------+-------+-------+-------+
| 1 | PRIMARY | <derived2> | システム | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | プライマリ | tb2 | const | プライマリ | プライマリ | 4 | const | 1 | NULL |
| 2 | 派生 | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+--------------+------------+---------+---------------+----------+-------+-------+-------+

派生2: 派生テーブル2は、派生テーブルがID=2のテーブルtb1であることを意味します。

(ii) select_type列: データ読み取り操作の操作タイプ

1. SIMPLE: 単純な選択クエリ。SQL にはサブクエリや UNION は含まれません。
2. PRIMARY: クエリに複雑なサブクエリが含まれており、最も外側のクエリが PRIMARY としてマークされている
3. SUBQUERY: サブクエリは、SELECT リストまたは WHERE リストに含まれます。4. DERIVED: FROM リストに含まれるサブクエリは、DERIVED (派生テーブル) としてマークされます。MYSQL はこれらのサブクエリを再帰的に実行し、結果セットを一時テーブルに格納します。
5. UNION: 2番目のSELECTがUNIONの後に現れる場合、それはUNIONとしてマークされます。UNIONがFROM句のサブクエリに含まれている場合、外側のSELECTはDERIVEDとしてマークされます。
6. UNION RESULT: UNIONテーブルから結果を取得する場合に選択します

(III) 表の列: データ行はどの表を参照していますか?

4. タイプ列: アクセスタイプ、最良から最悪まで: system > const > eq_ref > ref > range > index > ALL

1. system: テーブルにはレコードが 1 つだけあります (システムテーブルと同じ)。これは const 型の特殊なケースであり、通常の業務では発生しません。
2. const: インデックスを通じてデータを 1 回検索します。このタイプは、主に主キーまたは一意のインデックスを比較するために使用されます。1 行のデータのみに一致するため、非常に高速です。主キーを WHERE ステートメントの後に配置すると、Mysql はクエリを定数に変換できます。
3. eq_ref: 一意のインデックススキャン。各インデックスキーに対して、テーブル内に一致するレコードが 1 つだけ存在します。主キーまたは一意のインデックススキャンでよく使用されます。
4. ref: 非一意インデックススキャン。単一の値に一致するすべての行を返します。これは基本的に、単一の値に一致するすべての行を返すインデックスアクセスです。つまり、複数の修飾データが見つかる可能性があるため、検索とスキャンが混在しています。
5. range: インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。キー列には、使用されたインデックスが表示されます。通常、between、<、>、in などのクエリが WHERE ステートメント内に表示されます。この指定された範囲スキャンは、完全なテーブルスキャンよりも優れています。インデックス内のあるポイントから開始して別のポイントで終了するだけでよいため、インデックス全体をスキャンする必要はありません。
6. インデックス: FULL インデックススキャンは、インデックスツリーをスキャンしてトラバースします (テーブル全体のインデックスをスキャンし、インデックスからデータを取得します)。
7. ALL フルテーブルスキャンはディスクから数百万のデータを取得します。すべてのタイプのデータは可能な限り最適化されます。

(V) possible_keys 列: このテーブルに適用できる 1 つ以上のインデックスを表示します。クエリに関係するフィールドにインデックスが存在する場合、そのインデックスはリストされますが、クエリによって実際に使用されない可能性があります。

（6）キー列：実際に使用されるインデックス。 NULL の場合、インデックスは使用されません。クエリでカバーリングインデックスが使用されている場合、そのインデックスはキーリストにのみ表示されます。カバーインデックス: 選択後のフィールドは、インデックスを作成するフィールドの数と一致します。

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

（８）参照列：インデックスのどの列が使用されているかを示します（可能な場合は定数）。インデックス列の値を検索するために使用される列または定数。

9. 行列 (オプティマイザによってクエリされる各テーブルの行数): テーブル統計とインデックスの選択に基づいて、必要なレコードを見つけるために読み取る必要がある行数を大まかに推定します。

（10）追加列：拡張属性ですが、非常に重要な情報でもあります。

1. filesort の使用: MySQL はテーブル内の指定されたインデックスの順序で読み取ることができません。

 mysql> explain select order_number from tb_order order by order_money;
+----+-------------+----------+-------+---------------+-------+-------+------+----------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+-------+---------------+-------+-------+------+----------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | filesort の使用 |
+----+-------------+----------+-------+---------------+-------+-------+------+----------------+
セット内の 1 行 (0.00 秒)

注意: order_number はテーブル内の一意のインデックス列ですが、order by ではこのインデックス列がソートに使用されないため、MySQL はソートに別の列を使用する必要があります。

2. 一時テーブルの使用: MySQL は、中間結果を保存するために一時テーブルを使用します。これは、order by クエリや group by クエリでよく使用されます。

mysql> explain select order_number from tb_order group by order_money;
+----+-------------+----------+-------+---------------+-------+------+------+---------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+-------+---------------+-------+------+------+---------------------------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | temporary を使用; filesort を使用 |
+----+-------------+----------+-------+---------------+-------+------+------+---------------------------------+
セット内の 1 行 (0.00 秒)

3. インデックスを使用するということは、対応する選択操作でカバーリングインデックスが使用されることを意味し、これによりテーブルのデータ行へのアクセスが回避され、効率が向上します。

同時に「Using where」が表示される場合は、インデックスを使用してインデックスキー値の検索を実行することを示します。

where を使用しない場合、インデックスは検索を実行するのではなく、データの読み取りに使用されます。

mysql> explain select order_number from tb_order group by order_number;
+----+-------------+----------+--------+---------------------+---------------------+------+------+-------------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+----------+--------+---------------------+---------------------+------+------+-------------+-------------+
| 1 | SIMPLE | tb_order | index | index_order_number | index_order_number | 99 | NULL | 1 | インデックスを使用 |
+----+-------------+----------+--------+---------------------+---------------------+------+------+-------------+-------------+
セット内の 1 行 (0.00 秒)

4. 場所を検索する

5. 結合バッファの使用: 現在の SQL が接続キャッシュを使用していることを示します。

6. where 句が不可能: where 句は常に false となり、MySQL はデータ行を取得できません。

7. 最適化されたテーブルを選択します。

8. 異なる:

要約する

以上が、Mysql における explain の役割の詳細な説明に関するこの記事の内容のすべてです。皆様のお役に立てれば幸いです。興味のある方は、MYSQL サブクエリとネストされたクエリの最適化例の分析、いくつかの重要な MySQL 変数、ORACLE SQL ステートメントの最適化の技術的なポイントの分析などを参照してください。不足がある場合は、メッセージを残してください。編集者が返信し、適時に修正します。このサイトをサポートしてくれた友人たちに感謝します!

以下もご興味があるかもしれません: