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

生徒

「MySQLでインデックスを作ると検索が速くなるって聞いたんですが、本当に使われているかどうかはどうやって確認するんですか？」

先生

「いいところに気が付きましたね。MySQLでは、クエリを実行したときにインデックスが使われているかどうかを確認する方法があります。代表的なのはEXPLAINというSQLです。」

生徒

「EXPLAINって何ですか？難しそうです…」

先生

「EXPLAINは『このSQLはどんな方法でデータを探しますか』という実行計画を教えてくれる機能です。データベースがどうやって検索しているかを確認できるので、インデックス最適化やSQLパフォーマンス改善にとても重要なんですよ。」

1. MySQLインデックスとは何か？検索を高速化する仕組み

MySQLのインデックスとは、データベース検索を高速化するための仕組みです。データベースには大量のデータが保存されています。そのため、条件検索をするときに毎回すべてのデータを確認していたら、非常に時間がかかってしまいます。

そこで使われるのがインデックスです。インデックスは本の「索引」のようなものです。本の最後にある索引を見ると、目的のページをすぐに見つけることができます。MySQLでも同じように、インデックスを作ることで目的のデータをすばやく見つけることができます。

ただし、インデックスを作っても必ず使われるとは限りません。そのため、実際にSQL実行時にインデックスが利用されているかを確認することが重要になります。

2. EXPLAINでインデックス使用状況を確認する方法

MySQLでインデックスが使用されているか確認する基本的な方法はEXPLAINコマンドです。 EXPLAINはSQLの実行計画を表示し、どのインデックスが使われるかを確認できます。

まずは次のような会員テーブルを用意します。


id | name       | age | email
---+------------+-----+-----------------------
1  | 山田太郎   | 25  | taro@example.com
2  | 佐藤花子   | 19  | hanako@example.com
3  | 鈴木一郎   | 30  | ichiro@example.com
4  | 高橋健     | 28  | ken@example.com
5  | 伊藤優子   | 22  | yuko@example.com

次に、年齢で検索するSQLを書きます。


EXPLAIN
SELECT *
FROM users
WHERE age = 25;

実行すると、MySQLがどのように検索するかを表示します。


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

この結果ではkeyがNULLになっています。これはインデックスが使われていないことを意味します。

3. インデックスを作成して使用状況を確認する

次にageカラムにインデックスを作成してみます。インデックスを作成することで検索の高速化が期待できます。


CREATE INDEX idx_age
ON users(age);

インデックス作成後に再度EXPLAINを実行します。


EXPLAIN
SELECT *
FROM users
WHERE age = 25;


id | select_type | table | type | key     | rows
-------------------------------------------------
1  | SIMPLE      | users | ref  | idx_age | 1

ここでkey列にidx_ageが表示されています。これはMySQLがインデックスを利用して検索していることを意味します。

4. SHOW INDEXでテーブルのインデックス一覧を確認する

MySQLではテーブルにどんなインデックスが作られているかを確認することも重要です。そのときに使うSQLがSHOW INDEXです。


SHOW INDEX
FROM users;

実行結果の例です。


Table | Key_name | Column_name
--------------------------------
users | PRIMARY  | id
users | idx_age  | age

この結果から、idには主キーインデックス、ageには追加したインデックスがあることが確認できます。

主キーとは、テーブルの中で一意にデータを識別するための特別なインデックスです。

5. possible_keysとkeyの違いを理解する

EXPLAINの結果にはpossible_keysとkeyという項目があります。初心者が混乱しやすい部分ですが、意味を理解するとインデックスチューニングに役立ちます。

possible_keysは「使用できる可能性のあるインデックス」、keyは「実際に使用されたインデックス」です。


EXPLAIN
SELECT name
FROM users
WHERE age = 22;


possible_keys | key
--------------------
idx_age       | idx_age

この結果では、MySQLはidx_ageインデックスを利用して検索していることが分かります。

6. type列で検索効率を確認する

EXPLAIN結果の中で特に重要なのがtypeという項目です。これはMySQLがどのような検索方法を使っているかを表しています。

代表的な検索タイプを簡単に説明します。

ALL：全件検索（インデックス未使用）
ref：インデックス検索
const：主キー検索

例えば主キー検索の場合は次のようになります。


EXPLAIN
SELECT *
FROM users
WHERE id = 1;


id | select_type | table | type  | key
--------------------------------------
1  | SIMPLE      | users | const | PRIMARY

constは非常に高速な検索方法で、主キーを使った検索でよく表示されます。

7. インデックスが使われない原因を確認する

インデックスを作成してもMySQLが必ず使うとは限りません。インデックスが使われない理由はいくつかあります。

例えば次のようなSQLです。


SELECT *
FROM users
WHERE age + 1 = 26;

このようにカラムに計算を加えると、インデックスが使えない場合があります。

データベースは「そのままの値」でインデックスを管理しているため、計算が入ると検索が複雑になり、結果として全件検索になる可能性があります。

そのため、SQLパフォーマンス改善やMySQLチューニングでは、EXPLAINを使って実際にインデックスが使われているかを確認することが非常に重要です。

特に大規模データベースや大量レコードを扱うシステムでは、インデックス使用状況の確認がシステム速度に大きく影響します。

まとめ

MySQLインデックス使用状況確認の重要ポイント

この記事では、MySQLデータベースにおけるインデックスの使用状況を確認する方法について、初心者にも理解しやすい形で解説してきました。データベースを扱うシステムでは、SQLの検索速度や処理性能がアプリケーション全体のパフォーマンスに大きく影響します。そのため、MySQLのインデックス最適化やSQLパフォーマンス改善は、データベース設計やシステム開発において非常に重要なテーマです。

MySQLインデックスとは、データベース検索を高速化するための仕組みです。本の索引と同じように、目的のデータを素早く見つけるための仕組みとして利用されます。しかし、インデックスを作成しただけでは必ず利用されるわけではありません。MySQLのクエリオプティマイザは、SQL実行時に最も効率の良い検索方法を判断して、インデックスを使用するかどうかを決定します。

そのため、実際にインデックスが使われているかを確認するためには、EXPLAINコマンドを利用することが重要です。EXPLAINはSQLの実行計画を表示する機能で、MySQLがどのような方法でデータを検索するかを確認できます。特に、インデックスチューニングやデータベースパフォーマンス最適化を行う場合には必ず使う重要なSQLです。

EXPLAINを使ったインデックス確認の基本

EXPLAINを使うことで、SQL実行時にどのインデックスが使用されているかを確認できます。MySQLのパフォーマンス改善では、EXPLAIN結果の読み方を理解することがとても大切です。特に確認するポイントは、key、possible_keys、typeなどの項目です。


EXPLAIN
SELECT *
FROM users
WHERE age = 25;

このようなSQLを実行すると、MySQLのクエリ実行計画が表示されます。ここでkey列にインデックス名が表示されていれば、そのインデックスが使用されていることになります。もしkeyがNULLであれば、インデックスが使われていないことを意味します。

インデックス未使用のSQLの例


id | name       | age | email
---+------------+-----+-----------------------
1  | 山田太郎   | 25  | taro@example.com
2  | 佐藤花子   | 19  | hanako@example.com
3  | 鈴木一郎   | 30  | ichiro@example.com
4  | 高橋健     | 28  | ken@example.com
5  | 伊藤優子   | 22  | yuko@example.com
6  | 中村誠     | 35  | makoto@example.com


EXPLAIN
SELECT *
FROM users
WHERE age = 25;


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

この結果ではtypeがALLになっています。これはMySQLがテーブル全体を検索していることを意味します。つまりインデックスが使われておらず、全件検索が行われている状態です。データ量が増えると検索速度が遅くなる原因になります。

インデックス作成後の確認例


CREATE INDEX idx_age
ON users(age);


EXPLAIN
SELECT *
FROM users
WHERE age = 25;


id | select_type | table | type | key     | rows
-------------------------------------------------
1  | SIMPLE      | users | ref  | idx_age | 1

この結果ではkeyにidx_ageが表示されています。これはMySQLがageカラムのインデックスを利用して検索していることを意味します。さらにtypeがrefになっているため、インデックス検索が実行されていることが分かります。インデックスが適切に利用されることで、SQL検索速度やデータベースパフォーマンスが大きく改善されます。

SHOW INDEXでインデックス一覧を確認する


SHOW INDEX
FROM users;


Table | Key_name | Column_name
--------------------------------
users | PRIMARY  | id
users | idx_age  | age

SHOW INDEXを利用することで、テーブルに作成されているすべてのインデックスを確認できます。データベースチューニングやMySQLパフォーマンス最適化を行う際には、現在どのインデックスが存在するのかを確認することも重要です。

インデックスが使われないSQLの注意点

SQLの書き方によっては、インデックスを作成していてもMySQLがインデックスを利用しない場合があります。例えばカラムに計算式を加えた場合や、関数を使った検索条件ではインデックスが使われないことがあります。


SELECT *
FROM users
WHERE age + 1 = 26;

このようなSQLでは、MySQLはageの値をそのまま利用できないため、インデックスが無効になり全件検索になる可能性があります。SQLチューニングを行う際には、できるだけカラムをそのまま比較する条件を書くことが大切です。

MySQLデータベースのパフォーマンス改善では、EXPLAINを使って実際のSQL実行計画を確認しながら、インデックス設計やSQL構造を見直していくことが重要です。特に大量データを扱うシステムやWebアプリケーションでは、インデックス使用状況の確認がシステム全体の速度やユーザー体験に大きく影響します。

先生と生徒の振り返り会話

生徒

今日の記事で、MySQLインデックスの使われ方を確認する方法がよく分かりました。インデックスを作るだけではなくて、EXPLAINを使って実際に使われているか確認することが大事なんですね。

先生

その通りです。データベースチューニングでは、インデックス作成と同じくらい「インデックス使用状況の確認」が重要です。EXPLAINを使えば、MySQLがどのようにデータを検索しているかが分かります。

生徒

key列を見ると実際に使われたインデックスが分かるんですよね。それとpossible_keysは使える可能性があるインデックスという意味でしたよね。

先生

よく理解できていますね。さらにtype列を見ると、検索方法の効率も分かります。例えばALLなら全件検索、refならインデックス検索、constなら主キー検索です。

生徒

SQLの書き方によってインデックスが使われなくなることもあると知って驚きました。例えばageに計算を入れるとインデックスが使えなくなる可能性があるんですね。

先生