技術共有

Mysql Explain文の詳細説明と表示例

2024-07-12

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

まず、SQL について簡単に説明します。

SQL 言語は、データクエリ言語 DQL、データ操作言語 DML、データ定義言語 DDL、およびデータ制御言語 DCL の 4 つのカテゴリに分類されます。

1. データクエリ言語 DQL
データ クエリ言語 DQL の基本構造は、SELECT 句、FROM 句、および WHERE 句で構成されるクエリ ブロックです。 SELECT <フィールド名テーブル> FROM <テーブルまたはビュー名> WHERE <クエリ条件>

2. データ操作言語 DML
データ操作言語 DML には、主に 3 つの形式があります。
1) 挿入: 挿入
2) 更新: 更新
3) 削除: 削除

3. データ定義言語 DDL
データ定義言語 DDL は、データベース内にさまざまなオブジェクト (テーブル、ビュー、インデックス、シノニム、クラスタなど) を作成するために使用されます。たとえば、 CREATE TABLE / VIEW / INDEX / SYN / CLUSTER | テーブル ビュー インデックス シノニム クラスタ | 。 DDL 操作は暗黙的に送信されます。ロールバックできません

4. データ制御言語 DCL
データ制御言語 DCL は、データベースにアクセスするための特定の権限を付与または取り消し、データベース操作トランザクションの時間と効果を制御し、データベースを監視するために使用されます。のように：
1) GRANT: 許可。
2) ROLLBACK [WORK] TO [SAVEPOINT]: 特定のポイントまでロールバックします。ロールバック---ROLLBACK ロールバック コマンドは、データベースの状態を最後に送信された状態に戻します。その形式は次のとおりです。 SQL>ROLLBACK;
3) コミット [作業]: 送信します。データベースの挿入、削除、および変更操作中、トランザクションはデータベースに送信されたときにのみ完了します。トランザクションがコミットされる前は、データベースを操作している人だけが何が行われたかを確認する権利を持ち、他の人は最後のコミットが完了した後にのみ確認できます。

認可ステートメントについて

データベース認証コマンド:

テーブル名 (または列名) に対する GRANT<permission> をユーザーに与える

正解選択肢 B: 挿入、選択: 権限テーブル名: ユーザー ユーザー: nkw

補足ナレッジポイント - リサイクル権限

REVOKE <permission> に対するテーブル名 (または列名) FROM ユーザー

ステートメントの導入を説明する

実行計画を表示するためのステートメントとして使用される Explain ステートメントは誰もがよく知っていると思います。 Explain は SQL 最適化分析でよく使用されます。

ここで注目すべき点は、explain は実際にステートメントを実行するのではなく、実行計画を表示するだけであるということです。

どのような情報が見られますか?

  • テーブルの読み取り順序
  • データ読み出し動作の動作種類
  • 使用できるインデックス
  • 実際に使用されるインデックス
  • テーブル間の参照
  • オプティマイザによってクエリされる各テーブルの行数

基本的な構文の紹介

  1. EXPLAIN SELECT select具体语句
  2. 如：
  3. EXPLAIN SELECT * FROM userpro

各列の機能を説明します

リスト説明する補充する
id各 SELECT キーワードは ID に対応します

選択タイプ

SELECTキーワードに対応するクエリタイプ
テーブルテーブル名
パーティション一致するパーティション情報
タイプ単一テーブルへのアクセス方法
可能なキー可能なインデックス
鍵実際に使用されるインデックス
キーの長さ実際に使用されるインデックスの長さ
参照インデックス列等価クエリを使用すると、インデックス列と一致するオブジェクト情報が等価になります。
行読み取られるレコードの推定数
フィルタリング検索条件でフィルタリングした後に残っているレコードの割合

    

余分な

追加情報

               

EXPLAINの各コラムを詳しく紹介

1、id

各 SELECT キーワードは ID に対応します

id の値が大きいほど優先度が高く、最初に実行されます。

ID が同じ場合はグループとみなされ、上から下へ順番に実行されます。

各 ID 番号は独立したクエリを表します。SQL ステートメント内のクエリの数は少ないほど優れています。

2、選択タイプ

カテゴリー説明する
単純単一テーブル クエリ。サブクエリや UNION クエリはありません。
主要なクエリ内の最も外側の SELECT ステートメント。
サブクエリサブクエリは WHERE 句で使用されます。
派生FROM 句に含まれるサブクエリの場合、MySQL はそれを DERIVED (派生) としてマークし、外部クエリで使用する結果セットの一時テーブルを生成します。
連合UNION クエリの 2 番目以降のクエリ ステートメント。
連合の結果UNION クエリの結果セット。
従属サブクエリサブクエリの結果は外側のクエリの値によって異なり、サブクエリは外側のクエリの行ごとに 1 回実行されます。
従属連合UNION クエリの 2 番目以降のクエリ ステートメントと、結果は外側のクエリの値に依存します。
キャッシュ不可能なサブクエリサブクエリはキャッシュできず、参照されるたびに実行されます。

3. テーブル列は、テーブルのテーブル名を表します (実際のテーブル名ではなく、略語である場合もあります)。

4. パーティション (オプション)

5.タイプ☆

一般的なタイプは次のとおりです

タイプ説明する
システム通常、テーブルには 1 つの行しかありません。 SELECT ... FROM DUAL クエリの最適化。
定数クエリはインデックスによって 1 回検出され、結果は 1 行のみ (定数テーブル) になります。
等価参照一意のインデックスまたは主キーを使用して、別のテーブルから行を検索します。
参照別のテーブルから 1 つ以上の行を検索するには、一意でないインデックスを使用します。
範囲インデックスを使用して行の範囲を返します。
索引テーブル全体をスキャンするのではなく、インデックスを完全にスキャンして行を検索します。
全てフルテーブルスキャンでは、テーブル内のすべての行がチェックされます。

一般に、すべてを避けるようにしてください

6， キーと possible_keys

  1. 鍵：

    • key フィールドには、クエリで使用される実際のインデックスが表示されます。このフィールドの値がNULL 、インデックスが使用されていないことを示します。このフィールドに値がある場合は、MySQL が指定されたインデックスを使用してクエリを実行したことを意味します。

  2. 可能なキー：

    • possible_keys フィールドには、MySQL が使用できるインデックスのリストが表示されます。これらのインデックスは、クエリで考慮される可能性はありますが、実際には使用されない可能性があるインデックスです。いつもの、possible_keys にリストされているインデックスは、クエリ条件とテーブル構造に基づいて決定されます。

  • もし key フィールドにはインデックス名があり、possible_keys に複数のインデックス名がリストされており、MySQL が選択したことを示しています。key フィールドがリストされているインデックスはクエリの実行に使用され、他のインデックスはpossible_keys ミディアム氏は、それも検討される可能性はあるが、最終的には使用されないと述べた。

  • もし key フィールドは、NULL、そして possible_keys に複数のインデックス名がリストされています。これは、クエリの実行時に MySQL がインデックスを使用しないことを示しています。その結果、テーブル全体のスキャンやその他のインデックスが最適化されていないアクセス方法が実行される可能性があります。

7、キー長

key_len インデックスキーの長さを記述するフィールドです。これは、MySQL が特定のインデックスを使用してクエリを実行するときのインデックスの使用法とインデックス キーの長さを示します。

  1. 単一列インデックス：

    • 単一列インデックスが使用され、列タイプが固定長の場合 (例: INT）、しかし key_len 値は列の長さです。
    • 可変長フィールドが使用されている場合 (例: VARCHAR）、しかし key_len の値はフィールドの最大長です。

  2. 複合インデックス：

    • 複合インデックス (つまり、複数の列を含むインデックス) の場合、key_len インデックス内のすべての列の合計長を表します。

  3. 複合インデックス：

    • 複数の列の結合インデックスがクエリで使用されている場合、key_len 結合インデックス内のすべての列の合計長です。

  4. インデックスプレフィックス：

    • 場合によっては、MySQL はインデックスの一部のみを使用することがあります。たとえば、インデックスのプレフィックスをインデックスの一部として使用してクエリを実行できます。この場合、key_len 実際に使用されるインデックス部分の長さが表示されます。

8、参照

ref テーブル間の接続条件を記述したり、一意でないインデックスを使用して検索したりするフィールドです。これは、クエリの実行時に MySQL がテーブルにアクセスするためにどの結合条件またはどのインデックスを使用するかを示します。

9，行

rows クエリの実行時にアクセスまたは検査される行数の推定値です。

  • シンプルな SELECT お問い合わせください、rows 返される行の推定数を表します。
  • 結合クエリの場合 (JOIN) またはサブクエリ、rows 結合操作中にアクセスされた行の数を表す場合があります。
  • テーブルスキャン（フルテーブルスキャンまたはインデックススキャン）の場合、rows スキャンされた行数を表す場合があります。

10、フィルター済み

WHERE 条件とインデックス条件に基づいてフィルタリングされた行の割合を表します。 filtered は、オプティマイザーによって推定されたクエリ最適化効果を反映します。

いつ filtered 100% に近い場合は、条件を満たさない行のほとんどがクエリ条件によって効果的にフィルタリングされたことを意味し、これは通常、適切な最適化指標となります。

逆に、もし filtered 値が低い場合は、クエリ条件が十分に正確ではないか、オプティマイザがデータをフィルタリングするためにインデックスを効果的に使用していないことを示している可能性があります。

11、エクストラ

追加情報フィールド

一般的なものをいくつか示します Extra フィールドとその意味:

  1. インデックスの使用：

    • クエリがカバー インデックスを使用することを示します。つまり、テーブルの実際のデータ行にアクセスせずに、クエリの結果を完全にインデックスを通じて返すことができます。

  2. where の使用：

    • MySQL サーバーが、インデックス内で条件付きフィルタリングを完了するのではなく、ストレージ エンジンが行を取得した後に条件付きフィルタリングを実行することを示します。

  3. 一時的な使用：

    • MySQL がクエリを処理するためにメモリ内に一時テーブルを作成したことを示します。ソート操作または集計関数を含むクエリでよく使用されます。

  4. ファイルソートの使用：

    • MySQL がクエリを処理するためにファイルの並べ替えを実行したことを示します。これは通常、インデックスを使用して並べ替えを実行できない場合に発生します。

  5. 各レコードでチェックされる範囲 (インデックス マップ: ...)：

    • MySQL がインデックスを使用して、各レコードが指定された範囲内にあるかどうかを確認することを示します。これは通常、次の場合に発生しますrange クエリタイプ。

  6. NULLキーのフルスキャン：

    • MySQL がインデックス内でテーブル全体のスキャンを実行して、NULL 値に一致する行を検索したことを示します。

  7. 明確な：

    • 最初に一致する行を見つけた後、MySQL が重複行の検索を停止することを示します。

  8. 結合バッファの使用（ブロックネストループ）：

    • MySQL が接続操作を処理するために接続バッファを使用していることを示します。これは通常、接続テーブルの数が多いか、接続テーブルのサイズが大きい場合に発生します。

  9. 不可能な場所：

    • MySQL オプティマイザが WHERE 句の条件を満たすことができないため、行がスキャンされないと判断したことを示します。

  10. テーブルは使用されていません：

    • クエリにテーブルが含まれていないことを示します。たとえば、 SELECT NOW()。

実際の実行画面を説明する

データの準備

テーブルs1を作成する

  1. CREATE TABLE s1 (
  2. id INT AUTO_INCREMENT,
  3. key1 VARCHAR(100),
  4. key2 INT,
  5. key3 VARCHAR(100),
  6. key_part1 VARCHAR(100),
  7. key_part2 VARCHAR(100),
  8. key_part3 VARCHAR(100),
  9. common_field VARCHAR(100),
  10. PRIMARY KEY (id),
  11. INDEX idx_key1 (key1),
  12. UNIQUE INDEX idx_key2 (key2),
  13. INDEX idx_key3 (key3),
  14. INDEX idx_key_part(key_part1, key_part2, key_part3)
  15. ) ENGINE=INNODB CHARSET=utf8;

テーブルs2を作成する

  1. CREATE TABLE s2 (
  2. id INT AUTO_INCREMENT,
  3. key1 VARCHAR(100),
  4. key2 INT,
  5. key3 VARCHAR(100),
  6. key_part1 VARCHAR(100),
  7. key_part2 VARCHAR(100),
  8. key_part3 VARCHAR(100),
  9. common_field VARCHAR(100),
  10. PRIMARY KEY (id),
  11. INDEX idx_key1 (key1),
  12. UNIQUE INDEX idx_key2 (key2),
  13. INDEX idx_key3 (key3),
  14. INDEX idx_key_part(key_part1, key_part2, key_part3)
  15. ) ENGINE=INNODB CHARSET=utf8;

データは自分で用意します。

サンプルの実行と結果

単純なクエリ

単一テーブルクエリ

EXPLAIN SELECT * FROM `s1`;

結合クエリ

EXPLAIN SELECT * FROM `s1` INNER JOIN `s2`;

サブクエリ

  1. EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field
  2. = 'a');

特別な展示

選択タイプを表示
  1. #Union 去重
  2. EXPLAIN SELECT * FROM `s1` UNION SELECT * FROM `s2`;
  3. #Union 全查
  4. EXPLAIN SELECT * FROM `s1` UNION ALL SELECT * FROM `s2`;

最後のステップは重複排除なので、一時テーブルが使用されます。ただし、UNION ALL はすべてに対するクエリであるため、一時テーブルのクエリ メッセージは表示されません。

型クラス

const (インデックスを 1 回検索し、結果は 1 行のみ)

EXPLAIN SELECT * FROM s1 WHERE id = 10002;

eq_ref (一意のインデックスまたは主キーを使用して別のテーブルから行を検索)

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

ref (一意でないインデックスを使用して、別のテーブルから 1 つ以上の行を検索します)

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

range (インデックスを使用して行の範囲を返します)

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

インデックス (インデックスを完全にスキャンして行を検索します)

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

その他のディスプレイ

フィルター処理された 10 進数の時間

EXPLAIN SELECT * FROM s1 WHERE key1 > 'za' AND common_field = 'la'

個人プロフィール

彼は 30 年以上テクノロジーの研究に専念しており、Java、linux、javascript、php、css などのさまざまな言語に堪能であり、オープンソース分野で多くの貢献を行っています。将来の参考のために技術開発におけるいくつかの問題を共有する開発者ドキュメント ステーション。

私の連絡先情報

郵便