#テーブル情報 (後のデモ用):
mysql> show create table t_a;
------+
| t_a | CREATE TABLE `t_a` (
`id` bigint(20) NOT NULL DEFAULT '0',
`age` int(20) DEFAULT NULL,
`code` int(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code` (`code`),
KEY `age_key` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------
------+
1 row in set (0.03 sec)
SIMPLE: 単純な SELECT (UNION やサブクエリなどを使用しない)
mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
PRIMARY: クエリをネストする場合の最も外側のクエリ
mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)
UNION: UNION の 2 番目以降の SELECT ステートメント
mysql> explain select * from t_a where id =9 union all select * from t_a;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 2 | UNION | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.04 sec)
DEPENDENT UNION: 外部 QUERY
mysql> explain select * from t_a where id in (select id from t_a where id >8 union all select id from t_a where id =5);
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t_a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | 100.00 | Using where; Using index |
| 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.08 sec)
に応じて、UNION の 2 番目以降の SELECT ステートメントUNION RESULT: UNION の結果
mysql> explain select num from t_a where id = 3 union select num from t_a where id =4;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 2 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)
SUBQUERY: サブクエリの最初の選択
mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)
DEPENDENT SUBQUERY: サブクエリの最初の選択 (外部クエリに応じて)
mysql> explain select * from t_a where num in(select num from t_a where id = 3 union select num from t_a where id =4);
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
4 rows in set, 1 warning (0.12 sec)
DERIVED: 派生テーブル (サブクエリで生成された一時テーブル)
mysql> explain select a.id from (select id from t_a where id >8 union all select id from t_a where id =5) a;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 2 | DERIVED | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where; Using index |
| 3 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.12 sec)
2.3 table
このデータ行がどのテーブルを参照しているかを示します。実際のテーブル名である場合もあれば、次のような結果になる場合もあります。
- : ID を参照します。M 行と N 行の和集合結果です。
- < ;derivedN>: この行は、ID 値が n の行の派生テーブルの結果を参照します。たとえば、派生テーブルは from 句のサブクエリから取得される場合があります。
- : この行は、ID 値が n の行の実体化されたサブクエリの結果を参照します。
2.4 パーティション
クエリされたレコードが属するパーティション。パーティション化されていないテーブルの場合、この値は NULL です。
2.5 type
接続に使用されるカテゴリとインデックスが使用されるかどうか。一般的に使用されるタイプは次のとおりです: system、const、eq_ref、ref、range、index、ALL (左から)詳細については、「EXPLAIN 結合タイプ」を参照してください。
NULL: MySQL は最適化プロセス中にステートメントを分解し、実行中にテーブルやインデックスにアクセスする必要さえありません。インデックス列の最小値は、別のインデックスを通じて選択できます。 検索が完了しました。
システム: このテーブル (クエリ対象の一時テーブルである場合もあります) には、データ (= システム テーブル) が 1 行だけあります。これは const
const の特殊なケースです: テーブルには一致する行が 1 つだけあり、クエリの先頭で読み取られます。行が 1 つしかないため、この行の列値はオプティマイザーの残りの部分によって定数として扱われます。 const テーブルは一度だけ読み取られるため高速です。 const は、条件が PRIMARY KEY または UNIQUE インデックスであり、定数値と比較される場合、クエリのすべての部分に使用されます。
次のクエリでは、tbl_name を const テーブルに使用できます。
SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；
--例子
mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.07 sec)
eq_ref: 前のテーブルの行の組み合わせごとに、このテーブルから 1 行を読み取ります。 system と const に加えて、これが最適な接続タイプです。これは、結合でインデックスのすべての部分が使用され、インデックスが主キーまたは一意の非 null インデックスである場合に使用されます。 eq_ref は、= 演算子を使用して比較したインデックス付き列で使用できます。比較値には、定数、またはこのテーブルの前に読み取られたテーブルの列を使用する式を指定できます。
次の例では、MySQL は eq_ref 結合を使用して ref_table を処理できます:
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
--例子（t_b为t_a的复制表，表结构相同）
mysql> explain select * from t_a,t_b where t_a.code=t_b.code;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| 1 | SIMPLE | t_a | NULL | ALL | uk_code | NULL | NULL | NULL | 9 | 100.00 | NULL |
| 1 | SIMPLE | t_b | NULL | eq_ref | uk_code | uk_code | 4 | test.t_a.code | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.03 sec)
ref 前のテーブルの行の組み合わせごとに、一致するインデックス値を持つすべての行がこのテーブルから読み取られます。 。結合でキーの左端のプレフィックスのみが使用される場合、またはキーが UNIQUE キーまたは PRIMARY KEY ではない場合 (つまり、キー クエリに基づいて結合の結果が 1 行にならない場合)、ref を使用します。この結合タイプは、少数の行のみに一致するキーを使用している場合に適しています。 ref は、= 演算子または <=> 演算子を使用してインデックス付き列で使用できます。
次の例では、MySQL は ref 結合を使用して ref_table を処理できます:
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
--例子（t_b为t_a的复制表，表结构相同）
mysql> explain select * from t_a,t_b where t_a.age=t_b.age;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
| 1 | SIMPLE | t_a | NULL | ALL | age_key | NULL | NULL | NULL | 9 | 100.00 | Using where |
| 1 | SIMPLE | t_b | NULL | ref | age_key | age_key | 5 | test.t_a.age | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)
fulltext: FULLTEXT インデックスを使用して結合を実行します
ref_or_null: 結合タイプ ref は似ていますが、NULL 値を含む行を特別に検索する MySQL 機能が追加されました。この結合タイプの最適化は、サブクエリを解決する際によく使用されます。
次の例では、MySQL は ref_or_null 結合を使用して ref_table を処理できます。
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
--例子
mysql> explain select * from t_a where t_a.age =3 or t_a.age is null;
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t_a | NULL | ref_or_null | age_key | age_key | 5 | const | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)
index_merge: この結合タイプは、インデックス マージ最適化メソッドが使用されることを示します。この場合、key 列には使用されるインデックスのリストが含まれ、key_len には使用されるインデックスの最長のキー要素が含まれます。
SELECT * FROM ref_table
WHERE idx1=expr1 OR idx2 =expr2;
--例子
mysql> explain select * from t_a where t_a.code =3 or t_a.age = 3;
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| 1 | SIMPLE | t_a | NULL | index_merge | uk_code,age_key | uk_code,age_key | 4,5 | NULL | 2 | 100.00 | Using union(uk_code,age_key); Using where |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
1 row in set, 1 warning (0.03 sec)
unique_subquery: この型は、次の形式の IN サブクエリの ref を置き換えます:
value IN (SELECT Primary_key FROM single_table WHERE some_expr)
unique_subquery は次の形式です。 a インデックス検索関数はサブクエリを完全に置き換えることができ、より効率的です。 index_subquery: この結合タイプは unique_subquery に似ています。 IN サブクエリは置換できますが、次の形式のサブクエリの一意でないインデックスに限ります:
value IN (SELECT key_column FROM single_table WHERE some_expr)range: 指定された値のみを取得しますインデックスを使用して行を選択する、行の範囲。キー列には、どのインデックスが使用されたかが表示されます。 key_len には、使用されるインデックスの最長のキー要素が含まれます。この型では ref 列は NULL です。 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN または IN 演算子を使用する場合、キー列を定数と比較するときに range# を使用できます。 #
mysql> explain select * from t_a where id > 8;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)
index: この結合タイプは、インデックス ツリーのみがスキャンされることを除いて、ALL と同じです。通常、インデックス ファイルはデータ ファイルよりも小さいため、これは通常 ALL より高速です。 MySQL は、クエリで単一のインデックスの一部であるカラムのみを使用する場合に、この結合タイプを使用できます。
ALL: 前のテーブルの行の組み合わせごとに完全なテーブル スキャンを実行します。これは、テーブルが const とマークされていない最初のテーブルである場合には通常良くありません。その場合は、通常は良くありません。通常、ALL を使用せずにインデックスを追加して、前のテーブルの定数値または列の値に基づいて行を取得できるようにすることができます。
2.6 possible_keys
possible_keys 列は、MySQL がテーブル内の行を検索するために使用できるインデックスを示します。この列は、EXPLAIN 出力に表示されるテーブルの順序とは完全に独立していることに注意してください。これは、 possible_keys 内の一部のキーは、生成されたテーブルの順序では実際には使用できないことを意味します。
列が NULL の場合、関連するインデックスはありません。この場合、WHERE 句をチェックして、インデックス付けに適した特定の列を参照しているかどうかを確認することで、クエリのパフォーマンスを向上させることができます。その場合は、適切なインデックスを作成し、再度 EXPLAIN でクエリ
2.7 key
# を確認してください。key 列には、MySQL が実際に使用することを決定したキー (インデックス) が表示されます。インデックスが選択されていない場合、キーは NULL になります。 MySQL に possible_keys カラムのインデックスの使用または無視を強制するには、クエリで FORCE INDEX、USE INDEX、または IGNORE INDEX を使用します。
2.8 key_len
key_len 列には、MySQL が使用することを決定したキーの長さが表示されます。キーが NULL の場合、長さは NULL になります。
使用されるインデックスの長さ。精度を損なうことなく、長さは短いほど良いです。
2.9 ref
ref 列は、テーブルから行を選択するためにキーとともに使用される列または定数を示します。
2.10 rows
rows 列には、クエリの実行時に MySQL が検査する必要があると考える行数が表示されます。
2.11 Extra
この列には、以下で詳しく説明するように、MySQL がクエリをどのように解決したかの詳細が含まれています。
- 区別: MYSQL が行に一致する行を見つけたらユニオン、もう検索しません
- 存在しません: MYSQL は LEFT JOIN を最適化します。LEFT JOIN 条件に一致する行が見つかると、検索しなくなります
- それぞれについて範囲がチェックされます: 理想的なインデックスが見つかりません。したがって、前のテーブルの行の組み合わせごとに、MYSQL はどのインデックスが使用されたかをチェックし、それを使用してテーブルから行を返します。これは、インデックスを使用した接続の中で最も遅い接続の 1 つです。
- ファイルソートの使用: これが表示された場合は、クエリを最適化する必要があります。 MYSQL では、返された行をソートする方法を見つけるために追加の手順が必要です。接続タイプと、ソートキー値を格納する行ポインター、および条件に一致するすべての行に基づいてすべての行をソートします。
- インデックスの使用: 実際のデータを読み取ることなく、インデックス内の情報のみを使用して列データが取得されます。アクション テーブルが返されます。これは、テーブルの要求されたすべての列が同じインデックスの一部である場合に発生します。
- 一時的な使用: これが表示された場合、クエリを最適化する必要があります。ここで、MYSQL は結果を保存するための一時テーブルを作成する必要があります。これは通常、GROUP BY ではなく、ORDER BY が別の列セットに対して実行されるときに発生します。
- WHERE の使用: WHERE 句は、どの行を保存するかを制限するために使用されます。次のテーブルと比較されるか、テーブルが照合されるか、ユーザーに返されます。テーブル内のすべての行を返したくない場合で、接続タイプが ALL またはインデックスの場合、これが発生するか、クエリに問題があります。
[関連する推奨事項:MySQL ビデオ チュートリアル #]
