[初心者向け] MySQL のインデックスを触ってみた

2025.11.05

MySQL のインデックスについてあまり理解していなかったため、実際に触ってみようと思います。
今回は 10 万行のテーブルを準備し、インデックのあり/なしで検索が速くなるのかやってみます。

実行環境は EC2（Amazon Linux 2023）上にインストールした mysql です。
EC2 に mysql をインストールする方法は下記ブログをご参照ください。

使用した mysql のバージョンです。

$ mysql --version
mysql  Ver 8.4.6 for Linux on x86_64 (MySQL Community Server - GPL)

インデックスとは

データベースのインデックスとは、本の索引のようなものです。
技術書の巻末に A〜Z 順やあいうえお順で用語が記載されているあれです。

例えば「MySQL」という言葉がわからないとき、索引がないと 1 ページ目から順番にその言葉が載っているページを（場合によっては最後まで）めくって探す必要があります。

一方で索引がある場合、索引で「MySQL → 100ページ」と分かるので、直接そのページを開くことができます。

データベースも同じで、インデックス（索引）があると検索が劇的に速くなります。

今回は 10 万行のサンプルデータで検証してみます。

サンプルデータ準備

mysql にログインし、検証用に 10 万行のデータを持ったテーブルを作成します。

データベース作成

CREATE DATABASE sample_db;

作成したデータベースを選択

USE sample_db;

テーブル作成

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    age INT,
    country VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

テーブルにデータを挿入

INSERT INTO users (email, age, country)
SELECT 
    CONCAT('user', n, '@example.com'),
    18 + (n % 50),
    CASE (n % 5)
        WHEN 0 THEN 'Japan'
        WHEN 1 THEN 'USA'
        WHEN 2 THEN 'UK'
        WHEN 3 THEN 'Germany'
        ELSE 'France'
    END
FROM (
    SELECT @row := @row + 1 as n
    FROM 
        (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
        (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
        (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
        (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4,
         (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
         UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t5,
        (SELECT @row := 0) r
    LIMIT 100000
) numbers;

ちなみに上記 INSERT で 100 万行のデータを作成するには

上記は 10 万行レコードの作成ですが、それを 100 万に増やしたい場合は、t6 の項目を追加し、LIMIT の部分を 1000000 (行)　に変更してください。

(SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t6,

データ確認。10 万行あるので OK です。

mysql> SELECT COUNT(*) as total_rows FROM users;
+------------+
| total_rows |
+------------+
|     100000 |
+------------+
1 row in set (0.01 sec)

ちなみに作成した users テーブルのデータ構造は以下のようになっています。

id と email は連番
age は 18〜67 を繰り返す
country は「USA -> UK -> Germany -> France -> Japan」を繰り返す

mysql> select * from users;
+--------+------------------------+------+---------+---------------------+
| id     | email                  | age  | country | created_at          |
+--------+------------------------+------+---------+---------------------+
|      1 | user1@example.com      |   19 | USA     | 2025-11-02 01:47:51 |
|      2 | user2@example.com      |   20 | UK      | 2025-11-02 01:47:51 |
|      3 | user3@example.com      |   21 | Germany | 2025-11-02 01:47:51 |
|      4 | user4@example.com      |   22 | France  | 2025-11-02 01:47:51 |
|      5 | user5@example.com      |   23 | Japan   | 2025-11-02 01:47:51 |
|      6 | user6@example.com      |   24 | USA     | 2025-11-02 01:47:51 |
|      7 | user7@example.com      |   25 | UK      | 2025-11-02 01:47:51 |
|      8 | user8@example.com      |   26 | Germany | 2025-11-02 01:47:51 |
|      9 | user9@example.com      |   27 | France  | 2025-11-02 01:47:51 |
|     10 | user10@example.com     |   28 | Japan   | 2025-11-02 01:47:51 |

...

|  99996 | user99996@example.com  |   64 | USA     | 2025-11-02 01:47:51 |
|  99997 | user99997@example.com  |   65 | UK      | 2025-11-02 01:47:51 |
|  99998 | user99998@example.com  |   66 | Germany | 2025-11-02 01:47:51 |
|  99999 | user99999@example.com  |   67 | France  | 2025-11-02 01:47:51 |
| 100000 | user100000@example.com |   18 | Japan   | 2025-11-02 01:47:51 |
+--------+------------------------+------+---------+---------------------+
100000 rows in set (0.07 sec)

クエリ実行時間を計測できるよう設定

mysql のクエリは、SHOW PROFILE 構文で時間が計測できます。詳細は下記公式ドキュメントや外部記事をご参照ください。
※SHOW PROFILE は使用自体は可能ですが現在は非推奨機能のため、予めご認識ください。今回は検証のみのためこちらで計測していきます。

計測準備のため、以下のようにプロファイリングを有効化しておきます。

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

設定できたので適当なクエリを実行してみます。
クエリ実行後、SHOW profiles; の結果から、各クエリの Duration が把握できます。

mysql> select * from users;
+--------+------------------------+------+---------+---------------------+
| id     | email                  | age  | country | created_at          |
+--------+------------------------+------+---------+---------------------+
|      1 | user1@example.com      |   19 | USA     | 2025-11-02 01:47:51 |
|      2 | user2@example.com      |   20 | UK      | 2025-11-02 01:47:51 |
...
|  99999 | user99999@example.com  |   67 | France  | 2025-11-02 01:47:51 |
| 100000 | user100000@example.com |   18 | Japan   | 2025-11-02 01:47:51 |
+--------+------------------------+------+---------+---------------------+
100000 rows in set (0.07 sec)

mysql> SHOW profiles;
+----------+------------+---------------------+
| Query_ID | Duration   | Query               |
+----------+------------+---------------------+
|        1 | 0.00017400 | SELECT @@profiling  |
|        2 | 0.07129500 | select * from users |
+----------+------------+---------------------+
2 rows in set, 1 warning (0.00 sec)

上記結果を見ると、select * from users; の実行に 0.071 秒の時間がかかっていることがわかります。

これで計測準備は完了です。

インデックスなしで検索

インデックスの効果を計測するため、まずはインデックスなしで検索してみます。

なお、現在は id カラムのみインデックスが作られている状況です。
インデックスは SHOW INDEX で確認ができます。
以下の結果より、Column_name が id のカラムにインデックスが作成されていることがわかります。

mysql> show index from users;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY  |            1 | id          | A         |       99856 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

上記の通り、id カラム以外にはインデックスが無い状態で、メールアドレスが user50000@example.com のレコードを検索してみます。

mysql> SELECT * FROM users WHERE email = 'user50000@example.com';
+-------+-----------------------+------+---------+---------------------+
| id    | email                 | age  | country | created_at          |
+-------+-----------------------+------+---------+---------------------+
| 50000 | user50000@example.com |   18 | Japan   | 2025-11-02 01:47:51 |
+-------+-----------------------+------+---------+---------------------+
1 row in set (0.04 sec)

mysql> show profiles;
+----------+------------+-----------------------------------------------------------+
| Query_ID | Duration   | Query                                                     |
+----------+------------+-----------------------------------------------------------+
...
|        9 | 0.04033900 | SELECT * FROM users WHERE email = 'user50000@example.com' |
...
+----------+------------+-----------------------------------------------------------+

上記結果より、0.04 秒かかっていることがわかります。

EXPLAIN でクエリの実行計画を確認してみます。

mysql> EXPLAIN SELECT * FROM users WHERE email = 'user50000@example.com';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 99856 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

上記より以下内容が読み取れます。

type が ALL となっており、フルテーブルスキャンとなっている
key が NULL のため、実際に使用されるインデックスが無い
rows が 99856 となり、クエリ実行のためにほぼ全件の 10 万行近くが必要と推定されている

EXPLAIN 結果の見方については以下公式ドキュメントを適宜ご参照ください。

まとめると、インデックス無しで検索を実行するとほぼ 10 万行のデータを確認する必要があると推定され、実際に確認した結果 0.04 秒の実行時間がかかっていることがわかりました。

インデックスありで検索

続いてインデックスありで検索クエリを実行します。

まず、users テーブルの email カラムにインデックス idx_email を作成します。

mysql> CREATE INDEX idx_email ON users(email);
Query OK, 0 rows affected (0.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY   |            1 | id          | A         |       99856 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_email |            1 | email       | A         |       99856 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

インデックスが作成できたので、前項と同じメールアドレスが user50000@example.com のレコードを検索してみます。

mysql> SELECT * FROM users WHERE email = 'user50000@example.com';
+-------+-----------------------+------+---------+---------------------+
| id    | email                 | age  | country | created_at          |
+-------+-----------------------+------+---------+---------------------+
| 50000 | user50000@example.com |   18 | Japan   | 2025-11-02 01:47:51 |
+-------+-----------------------+------+---------+---------------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                             |
+----------+------------+-------------------------------------------------------------------+
...
|       15 | 0.00114475 | SELECT * FROM users WHERE email = 'user50000@example.com'         |
...
+----------+------------+-------------------------------------------------------------------+

上記結果より、クエリ時間が 0.001 秒と、インデックスなしの時の 0.04 秒と比較するとかなり検索が高速になっていることがわかります。

こちらもインデックスありの時と同様にEXPLAIN で実施したクエリの実行計画を確認してみます。

mysql> EXPLAIN SELECT * FROM users WHERE email = 'user50000@example.com';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | idx_email     | idx_email | 1023    | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

上記の内容より、以下結果が読み取れます。

type が ref であり、PRIMARY KEY、UNIQUE KEYでないインデックスが使用されること
key に idx_email が指定されており、クエリ実行の際に同インデックスが使用されること
rows が 1 となり、調査される行の見積もりが　1 行であること

まとめると、users テーブルの email カラムにインデックスを作成したことで、user50000@example.comを検索するときに、1 行（ないしは数行）を見てそのレコードを特定できることがわかりました。

インデックスなしの時の 10 万行ほぼ全てをチェックするような検索処理と比べると劇的な変化で驚きました。

検証は以上です。

終わりに

今回は MySQL のインデックスがよく分からなかったため、実際にありなしで検索速度が変わるのか検証してみました。
その結果、めちゃくちゃ変わりました。インデックスがあると効率的なんだなと理解しました。

以下は今回検証してみて、user50000@example.com を見つけるまでの自分がイメージしたざっくり理解です。（内部的には B-Tree とか色々やっているとは思うのであくまでイメージとして捉えてください）

【インデックスなし】
user1@example.com ×
user2@example.com ×
user3@example.com ×
...
user50000@example.com ○ ← 50,000回ないしは全行チェックして発見！

【インデックスあり】
インデックスを見て直接ジャンプ
user50000@example.com ○ ← 1回ないしは数回のチェックで発見！

インデックスってとてもすごい仕組みですね。検証してよかったです。

本ブログがインデックスって何？と思っている方の少しでも参考になれば幸いです。

なお蛇足ですが、インデックスを学んでいるうちに、逆にインデックスありで遅くなるパターンというのもあるみたいなので、また検証してブログ化したいと思います。

[初心者向け] MySQL のインデックスを触ってみた

インデックスとは

サンプルデータ準備

クエリ実行時間を計測できるよう設定

インデックスなしで検索

インデックスありで検索

終わりに

参考情報

関連記事

AWSで探す

注目のテーマ

プロダクトやサービスで探す

特集やシリーズから探す

EVENTS