この記事は、MySQL の実行計画をすばやく理解するのに役立ちます。

通常、低速クエリ SQL ステートメントをクエリする場合、EXPLAIN コマンドを使用して SQL の実行計画を表示します。 ステートメント (スルー) 返された情報は、Mysql オプティマイザーが SQL ステートメントをどのように実行するかを理解するのに役立ち、分析は最適化のアイデアを提供するのに役立ちます。

1. Explain 関数

Explain コマンドは、主に SQL ステートメントの実行計画を表示するために使用されます。このコマンドは、オプティマイザーによる SQL クエリーステートメントの実行をシミュレートすることができ、 SQL の作成と最適化にご協力ください。それでは、SQl の最適化に役立つ具体的な情報として、 Explain からどのような情報が得られるでしょうか?

• テーブル読み取りシーケンス

• データ読み取り操作の操作タイプ

• 使用できるインデックス

• 実際に使用されるインデックス

• テーブル間の参照

• 各テーブルの行数テーブルはオプティマイザによってクエリされます

2. 使用方法の説明

使用法: 実行される SQL の説明

Explain は、実行される SQL の実行プランのリストを返します。リストには 12 個のフィールドが含まれます。フィールドは合わせて、SQL が実行されるメソッドを説明します。実行計画を実行します。次のリストでは、実行計画テーブルのフィールドの意味を詳しく説明します。

#フィールド名説明idselect ステートメント クエリのシーケンス番号により、テーブルの読み取り順序が決まります。 select_typeタイプクエリの操作タイプ、つまりデータ読み取り操作の操作タイプtableクエリ テーブル名パーティションtable PartitiontypeAccess typepossible_keys使用できるインデックス。クエリに関係するフィールドにインデックスがある場合、そのインデックスはリストされますが、実際にはクエリで使用されない場合があります。このフィールドが null であってもフィールド キーが null ではない場合、この状況は、検索時に使用できるセカンダリ インデックス ツリーがないことを意味しますが、セカンダリ インデックスにはクエリが必要なフィールドが含まれているため、クラスター化インデックス (クラスター化インデックス) ) は検索されなくなりました。クラスター インデックスは比較的大きい)、代わりにセカンダリ インデックス ツリーがスキャンされます (セカンダリ インデックス ツリーは比較的小さい)。このとき、一般的なアクセス タイプはインデックスであり、インデックス ツリー全体がスキャンされます。 。 key実際のスキャンに使用されるインデックス。 null の場合、インデックスは使用されません。クエリでカバー インデックスが使用されている場合、インデックスはキー リストにのみ表示されます。key_len が使用されます。インデックス内のバイト数。クエリで使用されるインデックスの長さは、この列を通じて計算できます。精度を損なうことなく、長さは短いほど良いです。key_len によって表示される値は、実際に使用される長さではなく、インデックス フィールドの可能な最大長です。つまり、key_len はテーブルに基づきます。定義はテーブルから取得されるのではなく、計算されます。ref は、インデックスのどの列が使用されるかを示します。可能であれば、これは定数であり、インデックス列の値を検索するために使用される列または定数です; rowsテーブルの統計とインデックスの選択によると、必要なレコードを見つけるために読み取る必要がある行数を大まかに見積もります。filtered検索条件でフィルタリングした後に残っているデータの割合。 #Extra

他の列には収まらない重要な追加情報が含まれます

#3. キーフィールド分析

(1)id

select ステートメント クエリのシーケンス番号 (一連の数値を含む)。select が実行されることを示します。クエリ句または操作テーブルの順序には、次の 3 つの状況があります。

タイプ名##id ​​は同じです実行順序は上から下ですid ​​は異なりますサブクエリでは、ID のシリアル番号がインクリメントされます。ID の値が大きいほど優先度が高く、最初に実行されます。#ID は同じですが異なり、存在します。 idが同じ場合は上から順にグループとみなし以下の順序で実行 各グループ内でidの値が大きいほど優先度が高くなります実行が早ければ早いほど

(2) select_type

はデータ読み込み操作の操作タイプで、以下のタイプがあります。 name

説明

Descriptionsimple単純な選択クエリ。クエリにはサブクエリやユニオンは含まれません。primaryクエリに複雑なサブクエリが含まれている場合、最も外側のクエリがマークされます; subqueryサブクエリは select または where リストに含まれています; 依存サブクエリサブクエリの最初の SELECT は外部クエリに依存します。つまり、サブクエリは外側のクエリの結果に依存します。 derivedfrom リストに含まれるサブクエリは DERIVED (派生テーブル) としてマークされており、MySQL はこれらのサブクエリを再帰的に実行し、結果を一時テーブルに格納します。 ;union2 番目の select が Union の後に出現する場合は、union としてマークされます。union が from 句のサブクエリに含まれる場合、外側の層の select DERIVED としてマークされます;union resultunion テーブルから選択クエリの結果を取得します (つまり、union マージされた結果セット);meterizedマテリアライズド テーブル。サブクエリがクエリに関連付けられている場合、サブクエリの結果はマテリアライズされた一時テーブルに格納され、次に、一時テーブル内のデータ。 表示されるクエリ テーブルの名前。クエリがエイリアスを使用する場合、エイリアスがここに表示されます。データ テーブルに対する操作が含まれない場合、これは次のようになります。 null または次のいずれかとして表示されます:

#依存ユニオン	UNION の 2 番目以降のクエリ ステートメントは外部クエリに依存します
	(3) table

#タイプ名

説明

は、これが一時テーブルであることを示し、次の N は実行プラン内の ID であり、結果がこのクエリから生成されることを示します。 ##M,N>N> ;同様に、これは一時テーブルでもあり、この結果がユニオン クエリの ID M および N を持つ結果セットからのものであることを示します。 >

(4)partitions

クエリはレコードのパーティションと一致します。値 NULL は、パーティション化されていないテーブルに使用されます。

(5)type

最良から最悪まで:

system>const >eq_ref>ref>ref_or_null>range>index> ALL

all に加えて、index_merge を除く他のすべての type はインデックスを使用できます。他の type はインデックスを 1 つだけ使用できます。

一連のテーブルを自分で作成して実験してみましょう:

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `id` int(11) NOT NULL,
  `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, 'sn123456', '衣服');

-- ----------------------------
-- Table structure for sku
-- ----------------------------
DROP TABLE IF EXISTS `sku`;
CREATE TABLE `sku`  (
  `id` int(11) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `deleted` int(11) NOT NULL,
  `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `index_2`(`name`) USING BTREE,
  INDEX `index_1`(`goods_id`, `status`, `deleted`, `barcode`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sku
-- ----------------------------
INSERT INTO `sku` VALUES (1, 1, 1, 0, 'kt123456', '黑色');

SET FOREIGN_KEY_CHECKS = 1;

system

テーブルにはレコードが 1 行だけあります (システム テーブルと同じです)。これは const 型の特殊なケースです。通常は表示されないため、無視できます。

const

# はインデックスを通じて 1 回見つかったことを意味し、const は主キーまたは一意のインデックスを比較するために使用されます。レコードの 1 行のみが照合されるため、非常に高速です。 where リストに主キーを配置すると、mysql はクエリを定数に変換できます;

EXPLAIN SELECT * FROM sku WHERE id=1;复制代码

eq_ref

unique性的インデックス スキャン。インデックス キーごとにテーブル内の 1 つのレコードのみがそれに一致し、主キーまたは一意のインデックス スキャンによく使用されます。このタイプは通常、複数のテーブルの結合同等クエリに表示されます。つまり、前のテーブルの各結果に対して、 , 後者のテーブルの結果は 1 行のみ照合できるため、クエリ効率が高くなります。

EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;

ref

非一意のインデックス スキャンは、単一の値に一致するすべての行を返します。これは本質的にインデックス アクセスです。単一の値に一致するすべての行を返しますが、複数の一致する行が見つかる可能性があるため、検索とスキャンのハイブリッドである必要があります;

EXPLAIN SELECT * FROM sku WHERE goods_id=1;

ref_or_null

セカンダリ インデックスの等しい値の比較も null に制限されます。

EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;

range

インデックスを使用して行を選択し、指定された範囲内の行のみを取得します。キー列はどの行を示すかを示します。インデックスが使用され、通常、 between、、in などのクエリが where ステートメントに表示されます。この範囲インデックス スキャンは、テーブル内の特定の時点で開始するだけでよいため、フル テーブル スキャンよりも優れています。 1 つのポイント、すべてのインデックスをスキャンする必要はありません;

EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;

index

インデックスの違いそしてすべては、インデックス タイプがインデックス ツリーのみをトラバースするということです。これは、インデックス ファイルが通常データ ファイルより小さいため、通常は all より高速です。つまり、all とインデックスはテーブルの読み取りと書き込みの両方を行いますが、インデックスはインデックスから読み取ります。ハードディスクからのすべての読み取り;

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;

##all# はフルテーブルスキャン;

EXPLAIN SELECT * FROM sku WHERE deleted=0;

(6) possible_keys

クエリで使用できるインデックスがここにリストされます。

(7) key

は実際に使用されるインデックスをクエリします。

select_type

が index_merge の場合、2 つのエラーが発生する可能性があります。複数のインデックスの場合、他の select_type のうちの 1 つだけがここに表示されます。

(8)key_len

key_len は、列計算クエリで使用されるインデックスの長さを示します。例:

SELECT * FROM table where age = 1 and name like 'xx'

(age は int 型で null は不可、name は varchar(20) 型で null 可) と仮定します。エンコードはutf8です。これら 2 つのフィールドがインデックス クエリとして使用される場合、key_len の値は 4 3 * 20 2 1 = 67 になります。具体的な計算ルールを次の表に示します。

# #N	>
<subquery>N</subquery>	この行は、実体化されたサブクエリを持つ行の結果 ID の値 N を参照します。

#値の種類値の名前説明文字列CHAR(n)n バイト長#数値型TINYINT1バイト2 バイト3 バイト4 バイト ##BIGINTDATETIMESTAMPDATETIME

(9)ref

定数等価クエリの場合はここにconstが表示され、接続クエリの場合は実行計画が表示されます。駆動テーブル ドライバ テーブルの関連フィールドがここに表示されます。条件で式または関数が使用されている場合、または条件列が内部で暗黙的な変換を受ける場合、func と表示されることがあります。

(10)rows

これは、実行計画内のスキャン行の推定数であり、正確な値ではありません。

（11）filtered

この列は、explain extend を使用するときに表示されます。5.7# 以降のバージョンではデフォルトで利用可能です。 ## このフィールドには explain extend を使用する必要はありません。このフィールドは、ストレージ エンジンから返されたデータがサーバー層でフィルタリングされた後、クエリを満たす残りのレコードの割合を示します。これは特定のレコード数ではなくパーセンテージであることに注意してください。

(12)追加

この列には多くの情報が表示されます。情報は数十あります。一般的に使用されるものは次のとおりです。

1、distinct: distinct キーワードは select 部分で使用されます

2、テーブルは使用されません: # なし##from クエリまたは From デュアル クエリ。 not in() フォーム サブクエリまたは notexists() 演算子を使用してクエリを結合します。これはアンチ結合と呼ばれます。つまり、一般的な結合クエリは最初に内部テーブルをクエリし、次に外部テーブルをクエリしますが、アンチ結合クエリは最初に外部テーブルをクエリし、次に内部テーブルをクエリします。

3. filesort

の使用: 説明 mysql は、テーブル内のインデックスの順序でデータを読み取るのではなく、外部インデックスを使用してデータを並べ替えます。 mysql のインデックスを使用して完了できない並べ替え操作は、「ファイルの並べ替え」と呼ばれます。これは、ソート中にインデックスが使用できない場合に発生します。 order by ステートメントでよく見られるのですが、できるだけ早く最適化する必要があります

4.インデックス

を使用する: テーブルに戻る必要はありません。クエリを実行し、クエリ データはインデックスを介して直接取得できます。

5. 結合バッファー (ブロック ネスト ループ) の使用、結合バッファー (バッチ化されたキー アクセス) の使用

: 5.6.x 以降のバージョンは関連クエリを最適化します BNL 、BKA の機能。主な目的は、内部テーブルのループ数を減らし、クエリを順番にスキャンすることです。

6. sort_union の使用、union の使用、intersect の使用、sort_intersection の使用:

##intersect の使用:

と

の各インデックスの使用を示します。条件を使用した場合、処理結果から共通部分を求めることを示します
• using Union: or
を使用してインデックスを使用して条件を結合することを示します
• using sort_union と using sort_intersection: 前の 2 つと似ていますが、and
と
• or を使用して実行する場合に表示される点が異なります。大量の情報をクエリするには、まず主キーをクエリしてから続行します。レコードを読み取って返すことができるのは、並べ替えとマージを行った後でのみです。
7. usingtemporary

: 中間結果を保存するために一時テーブルが使用されることを示します。一時テーブルはメモリ一時テーブルまたはディスク一時テーブルです。実行プランでは確認できません。

status 変数、used_tmp_table、used_tmp_disk_table を確認する必要があります。 ＃＃＃ それを見るために。 order by およびグループ クエリ group by でよく見られます。 group by作成されたインデックスの順序と数に従ってください。できるだけ早く最適化する必要があります 8. where を使用: ストレージ エンジンから返されたすべてのレコードがクエリ条件を満たしているわけではないため、

server# でフィルターする必要があることを示します。 ## レイヤー。クエリ条件は制限条件と検査条件に分かれています。

5.6 より前は、ストレージ エンジンはデータをスキャンして制限条件に従って返すことしかできず、その後、server レイヤーがそれに応じてフィルタリングしていました。検査条件に合わせて実際に条件を満たしたデータを返す データを問い合わせます。 5.6.x 以降、ICP 機能 (インデックス条件プッシュダウン、インデックス プッシュダウン) がサポートされ、チェック条件をストレージ エンジン層までプッシュダウンすることもできます。チェック条件と制限は直接読み取りません。これにより、ストレージ エンジンによってスキャンされるレコードの数が大幅に減少します。 extra列表示インデックス条件を使用9, firstmatch(tb_name): 5.6.x

から導入されたオプティマイザ 1 つクエリの新機能は、

in() タイプのサブクエリを含む where 句で共通です。内部テーブルのデータ量が比較的大きい場合、この問題が発生する可能性があります。10、loosescan(m..n): 5.6.x## の後に導入された最適化サブクエリ# 新しい機能の 1 つは、

in()

型のサブクエリで、サブクエリが重複レコードを返すときに、この問題が発生する可能性があることです。4. 主な懸念事項の説明 一般に、結果のいくつかの列に注目するだけで済みます:

##VARCHAR(n)	の場合utf8 エンコードの場合は 3 n 2 バイト、utf8mb4 エンコードの場合は 4 n 2 バイトです。
SMALLINT
MEDIUMINT
INT
8 バイト		時間タイプ
3バイト
4 バイト
#8 バイト	#フィールド属性	NULL 属性は 1 バイトを占めます。フィールドが NOT NULL の場合、そのフィールドは占有されていません。

#列名備考typeこのクエリ テーブル 接続タイプここから、このクエリのおおよその効率を確認できます。key最終的に選択されたインデックス。インデックスがない場合、通常、このクエリの効率は非常に低くなります。 #key_lenrows小さいほど良いExtra#追加の追加情報。主に表示されるかどうかを確認するためです##注意が必要な Extra

このクエリの結果フィルタリングに使用されるインデックスの実際の長さ
estimate スキャンする必要があるレコードの数、スキャンする必要があるレコードの推定数
Extra	filesort を使用する 、一時ファイルを使用する これら 2 つの場合

列を見てみましょう。いくつかの状況:

キーワード備考#ファイルソートの使用 結果をインデックス順に並べるのではなく、外部の並べ替えを使用します。データが小さい場合は、ファイルソートの順序から並べ替えられます。それ以外の場合は、ディスク上で並べ替えを完了する必要があり、非常にコストがかかります。 適切なインデックスを追加する必要があります 結果を保存するために一時テーブルを作成する必要があります。これは通常、インデックスのない列に対して GROUP BY ORDER BY意味MySQL typeindex タイプと混同しないように注意してください。完全なインデックス スキャン後、WHERE適切なインデックスを追加する必要がありますYesWherewhere 1=0#一部の集計関数を使用してインデックスのあるフィールドにアクセスする場合、オプティマイザはインデックスを介して必要なデータ行を直接見つけて、全体の処理を完了します。 MIN()\MAX ()[関連する推奨事項: mysql ビデオチュートリアル＃＃＃]＃＃＃

	一時的なものを使用する
を実行する場合、または	のすべての列がインデックスに含まれていない場合に発生します。 適切なインデックスを追加する必要がありますインデックスの使用
テーブル全体のスキャンを避けるためにカバーインデックスを使用しますまた、テーブル内のデータを 2 回検索する必要がなく、これはより良い結果の 1 つです。	Using where is a full table/ の
句を使用して結果のフィルタリングを完了します。	不可能 WHERE
句の判定結果は常に false となり、データは選択できません (例:	)。あまり注意する必要はありません最適化されたテーブルの選択
などのクエリ、これもより良い結果の 1 つです

以上がこの記事は、MySQL の実行計画をすばやく理解するのに役立ちます。の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。