先生と生徒の会話形式で理解しよう

生徒

「MySQLでEXPLAINっていうコマンドを見たんですが、keyっていう列は何を意味しているんですか？」

先生

「key列は、そのSQLが実行されるときに『どのインデックスが使われたか』を教えてくれるとても重要な情報です。データベースの検索が速いか遅いかを確認するときに必ず見るポイントですね。」

生徒

「インデックスって本の目次みたいなものですよね？それが使われているかどうかが分かるんですか？」

先生

「その通りです。本でいう目次や索引のような役割です。EXPLAINのkey列を見ることで、MySQLがその目次を使って検索しているのか、それとも最初から全部読んでいるのかが分かります。」

生徒

「もしインデックスが使われていなかったらどうなるんですか？」

先生

「その場合はテーブルのすべてのデータを順番に調べることになります。これをフルスキャンと呼びます。データが多いと処理が遅くなる原因になります。」

1. MySQLのEXPLAINとは？SQLクエリ分析の基本

MySQLのEXPLAINは、SQLクエリがどのように実行されるかを確認するためのコマンドです。データベースの処理の流れを事前に確認できるため、SQLパフォーマンスチューニングやクエリ最適化に必須の機能です。

特に大きなデータベースでは、SQLの書き方によって処理速度が大きく変わります。 EXPLAINを使うことで、MySQLがどのインデックスを使うのか、どのようにテーブルを検索するのかを確認できます。

例えば次のようなSQLの前にEXPLAINをつけるだけで、クエリの実行計画を見ることができます。


EXPLAIN
SELECT *
FROM users
WHERE age = 25;

この結果には、type、key、rowsなどの重要な情報が表示されます。その中でも「key」はインデックス使用状況を確認する重要な列です。

2. インデックスとは？初心者でもわかる基本概念

インデックスとは、データを高速に検索するための仕組みです。

例えば電話帳を思い浮かべてください。名前順に並んでいるので、目的の人をすぐに見つけることができます。

もし電話帳がランダムな順番で書かれていたら、最初から全部探す必要があります。これはとても時間がかかります。

データベースでも同じで、インデックスがあると特定のデータをすばやく見つけることができます。


CREATE INDEX idx_users_age
ON users(age);

このSQLは、usersテーブルのage列にインデックスを作成しています。これにより、年齢検索のSQLが高速になる可能性があります。

3. EXPLAINのkey列とは？インデックス使用状況を確認する

EXPLAINのkey列は、MySQLが実際に使用したインデックス名を表示する列です。

つまり、SQLを実行するときに「どのインデックスが使われたか」がここに表示されます。

もしインデックスが使われていない場合、key列はNULLになります。

次の例を見てみましょう。


EXPLAIN
SELECT *
FROM users
WHERE age = 25;


id | select_type | table | type | possible_keys | key | rows
---+-------------+-------+------+---------------+-----+-----
1  | SIMPLE      | users | ref  | idx_users_age | idx_users_age | 1

この結果では、key列に「idx_users_age」と表示されています。つまり、age列のインデックスが使用されていることが分かります。

4. key列がNULLの場合の意味

EXPLAINのkey列がNULLの場合、インデックスが使われていないことを意味します。

つまりMySQLは、テーブルのすべての行を順番に確認してデータを探しています。

これを「フルテーブルスキャン」と呼びます。

例えば次のSQLです。


id | name       | age | email
---+------------+-----+-------------------
1  | 山田太郎   | 25  | taro@example.com
2  | 佐藤花子   | 19  | hanako@example.com
3  | 鈴木一郎   | 30  | ichiro@example.com
4  | 田中次郎   | 22  | jiro@example.com
5  | 高橋美咲   | 27  | misaki@example.com


EXPLAIN
SELECT *
FROM users
WHERE name = '山田太郎';


id | select_type | table | type | possible_keys | key  | rows
---+-------------+-------+------+---------------+------+-----
1  | SIMPLE      | users | ALL  | NULL          | NULL | 5

この結果ではkeyがNULLになっています。つまりname列にはインデックスがなく、全データを検索している状態です。

5. possible_keysとkeyの違い

EXPLAINには「possible_keys」という列もあります。

これは「使える可能性があるインデックス」を示しています。

一方でkey列は「実際に使われたインデックス」です。

つまり次のような違いがあります。

possible_keys：使用できるインデックス
key：実際に使用されたインデックス

次の例を見てみましょう。


EXPLAIN
SELECT *
FROM users
WHERE age = 25
AND email = 'taro@example.com';


id | select_type | table | type | possible_keys           | key           | rows
---+-------------+-------+------+------------------------+---------------+-----
1  | SIMPLE      | users | ref  | idx_users_age,idx_email | idx_users_age | 1

possible_keysには2つのインデックスが表示されていますが、実際に使われたのはidx_users_ageです。

6. key列を確認することで分かるSQLパフォーマンス

key列を見ることで、SQLのパフォーマンス改善のヒントを得ることができます。

もしkeyがNULLであれば、インデックスが使われていない可能性があります。

その場合、検索条件の列にインデックスを追加すると改善することがあります。


CREATE INDEX idx_users_name
ON users(name);

このようにインデックスを追加したあと、もう一度EXPLAINを実行します。


EXPLAIN
SELECT *
FROM users
WHERE name = '山田太郎';


id | select_type | table | type | possible_keys   | key           | rows
---+-------------+-------+------+----------------+---------------+-----
1  | SIMPLE      | users | ref  | idx_users_name | idx_users_name | 1

key列にインデックス名が表示されれば、MySQLがインデックスを利用して検索していることになります。

7. EXPLAINのkey列を使ったSQLチューニングの考え方

データベースのパフォーマンスチューニングでは、EXPLAINのkey列を確認することが非常に重要です。

特に次のようなポイントを確認すると、SQLクエリ最適化に役立ちます。

keyがNULLになっていないか確認する
適切なインデックスが使われているか確認する
possible_keysとkeyの違いを理解する
検索条件にインデックスがあるか確認する

MySQLのEXPLAINを使ってkey列を確認することで、 SQLの実行速度を改善するヒントを得ることができます。

データベースのパフォーマンス改善では、まずEXPLAINを確認し、インデックスが正しく使われているかをチェックする習慣をつけることが大切です。

まとめ

MySQLのEXPLAINのkey列でインデックス使用状況を確認する重要性

ここまで、MySQLのEXPLAINコマンドを使ってSQLクエリの実行計画を確認する方法や、特に重要な情報であるkey列について詳しく解説してきました。 MySQLデータベースのパフォーマンス改善やSQLクエリ最適化を行ううえで、EXPLAINの結果を理解することはとても重要です。

データベースを扱うシステム開発やWebアプリケーション開発では、SQLの処理速度がアプリケーション全体のパフォーマンスに大きく影響します。特にデータ件数が多い大規模なテーブルでは、インデックスが適切に利用されているかどうかが処理速度を大きく左右します。

MySQLのEXPLAINを使うと、SQLクエリがどのように実行されるのか、どのインデックスが使われているのか、何件のレコードを検索対象にしているのかなどを確認できます。その中でもkey列は「実際に使用されたインデックス」を示しているため、SQLパフォーマンスチューニングを行うときには必ず確認しておきたいポイントです。

key列からわかるインデックスの利用状況

EXPLAINのkey列を見ることで、MySQLがどのインデックスを利用してSQLクエリを実行しているかを確認できます。もしkey列にインデックス名が表示されていれば、そのインデックスが検索処理に利用されているという意味になります。

一方で、key列がNULLの場合はインデックスが使われていない状態です。この場合、MySQLはテーブル内のすべてのデータを順番に確認する「フルテーブルスキャン」を実行します。

フルテーブルスキャンはデータ件数が少ない場合は問題にならないこともありますが、レコード数が数万件や数百万件になるとSQLの処理速度が大きく低下する原因になります。そのため、EXPLAINの結果を確認してkey列がNULLになっていないかをチェックすることが、データベースチューニングでは重要になります。

possible_keysとkeyの違いを理解する

EXPLAINの結果にはpossible_keysという列も表示されます。 possible_keysは「使用できる可能性のあるインデックス」を示しています。

しかしpossible_keysに表示されていても、必ずしもそのインデックスが使われるとは限りません。 MySQLのクエリオプティマイザが最も効率的だと判断したインデックスが、最終的にkey列に表示されます。

つまり、possible_keysは候補のインデックス、keyは実際に使われたインデックスという関係になります。この違いを理解しておくことで、SQLクエリ分析やデータベースパフォーマンス改善をより正確に行うことができます。

インデックスが使われないSQLの例

例えば次のようなusersテーブルがあるとします。


id | name       | age | email
---+------------+-----+-------------------
1  | 山田太郎   | 25  | taro@example.com
2  | 佐藤花子   | 19  | hanako@example.com
3  | 鈴木一郎   | 30  | ichiro@example.com
4  | 田中次郎   | 22  | jiro@example.com
5  | 高橋美咲   | 27  | misaki@example.com
6  | 伊藤健一   | 35  | kenichi@example.com

この状態でname列にインデックスが存在しない場合、次のSQLではフルテーブルスキャンが発生します。


EXPLAIN
SELECT *
FROM users
WHERE name = '山田太郎';


id | select_type | table | type | possible_keys | key  | rows
---+-------------+-------+------+---------------+------+-----
1  | SIMPLE      | users | ALL  | NULL          | NULL | 6

この結果を見るとkey列がNULLになっています。つまりインデックスが使われておらず、すべてのレコードを検索している状態です。

インデックスを追加してSQLパフォーマンスを改善する

次に、name列にインデックスを作成してみます。


CREATE INDEX idx_users_name
ON users(name);

インデックスを作成したあと、もう一度同じEXPLAINを実行します。


EXPLAIN
SELECT *
FROM users
WHERE name = '山田太郎';


id | select_type | table | type | possible_keys   | key           | rows
---+-------------+-------+------+----------------+---------------+-----
1  | SIMPLE      | users | ref  | idx_users_name | idx_users_name | 1

この結果ではkey列にidx_users_nameが表示されています。つまりMySQLがname列のインデックスを使用して検索していることがわかります。

このようにEXPLAINのkey列を確認することで、SQLクエリが効率的に実行されているかを判断することができます。 SQLパフォーマンスチューニングやデータベース最適化を行うときには、まずEXPLAINを実行してkey列をチェックする習慣をつけることが重要です。