はじめに
こちらの記事は前回の続きとなっております。前回も見ていただけると嬉しいです。
前回の10万件のデータを用意し、インデックスの効果と実行計画を検証しました。今回は100万件のデータで検証を行なっていきます。
今回も前回と同じリポジトリを使っています。また、テーブル構成も前回と同じです。
検証に使うテーブルの構成です。
users
│
├── id (PK)
├── name
├── email (UNIQUE) ← 高カーディナリティ
├── email_verified_at
├── password
├── remember_token
├── created_at
├── updated_at
├── age ← 範囲検索用
├── prefecture ← 低カーディナリティ（7種類）
├── status ← 低カーディナリティ（3種類）
├── gender ← 低カーディナリティ（3種類）
└── score ← 範囲検索・ソート用データ作成
早速データを作っていきます。前回は totalRecords が10万件だったので、100万件に修正しています。そして、レコード作成にかかる時間を測定するために、 microtime を使っています。
public function run(): void
{
$totalRecords = 1000000;
$chunkSize = 5000;
$this->command->info("Starting to seed {$totalRecords} users...");
$start = microtime(true);
for ($i = 0; $i < $totalRecords; $i += $chunkSize) {
$users = [];
$chunkUsers = User::factory()->count($chunkSize)->raw();
$now = Carbon::now();
foreach ($chunkUsers as $user) {
$user['created_at'] = $now;
$user['updated_at'] = $now;
$users[] = $user;
}
User::insert($users);
$inserted = $i + $chunkSize;
$this->command->info("Inserted {$inserted} / {$totalRecords} users.");
}
$time = microtime(true) - $start;
$this->command->info(
"Finished in " . round($time, 2) . " sec."
);
$this->command->info(
"Peak memory: " .
round(memory_get_peak_usage(true) / 1024 / 1024, 2)
. " MB"
);
}そして、最後に実行時間とピーク時のメモリサイズを出力します。
artisan コマンドを用いて、データを作成します。
php artisan migrate:fresh --seed実行結果
....省略
Inserted 995000 / 1000000 users.
Inserted 1000000 / 1000000 users.
Finished in 189.09 sec.
Peak memory: 78 MB//念のため、確認
MariaDB [development]> select count(*) from users;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.262 sec)
MariaDB [development]> 私の環境では100万件のレコードを作成するのに、約3分ほどかかりました。5000ユーザずつ作成しているため、メモリサイズはいい感じに抑えられていますね。
Total records : 1,000,000
Seed Time : 189.09 sec
Peak Memory : 78 MB
Chunk Size : 5,000検証
さて、実際に検証をしていきます。インデックスを貼る前に、users テーブルのディスクサイズを確認しておきます。
MariaDB [development]> SHOW TABLE STATUS LIKE 'users';
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| users | InnoDB | 10 | Dynamic | 988861 | 200 | 197918720 | 0 | 80740352 | 4194304 | 1000001 | 2026-06-28 10:12:50 | 2026-06-28 10:15:59 | NULL | utf8mb4_unicode_ci | NULL | | | 0 | N |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
1 row in set (0.011 sec)
MariaDB [development]>Rows が 988861件になっていますが、Rows は推定値です。テーブル全体を数えているわけではなく、統計情報から推定しているので、多少ズレます。(InnoDBではRowsは実際の件数ではなく統計情報から算出された推定値です。そのためCOUNT(*)とは一致しない場合があります。)
現在のインデックスも確認します。
MariaDB [development]> 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 | Ignored |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users | 0 | PRIMARY | 1 | id | A | 988861 | NULL | NULL | | BTREE | | | NO |
| users | 0 | users_email_unique | 1 | email | A | 988861 | NULL | NULL | | BTREE | | | NO |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.003 sec)
MariaDB [development]>特に今はインデックスを貼っていません。この状態で実行計画を確認します。こちらは、10万件のデータの時と変わらないですね。
MariaDB [development]> EXPLAIN SELECT * FROM users WHERE prefecture = 'Tokyo';
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 988861 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.013 sec)
MariaDB [development]> 10万件のインデックスを貼る前の実行計画
MariaDB [development]> EXPLAIN SELECT * FROM users WHERE prefecture = 'Tokyo';
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | users | ALL | NULL | NULL | NULL | NULL | 98690 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.012 sec)
MariaDB [development]> SELECT * FROM users WHERE prefecture = 'Tokyo';
143113 rows in set (20.383 sec)前回だと、prefecture に対して、インデックスを貼りましたが、カーディナリティが低く、インデックスを貼っても、type ALLのままでした。そのため、100万レコードになっても、割合自体は変わっていないため、10万件と同じ結果になると予想しています。
MariaDB [development]> CREATE INDEX idx_prefecture ON users(prefecture);
Query OK, 0 rows affected (10.020 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [development]> 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 | Ignored |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| users | 0 | PRIMARY | 1 | id | A | 987264 | NULL | NULL | | BTREE | | | NO |
| users | 0 | users_email_unique | 1 | email | A | 987264 | NULL | NULL | | BTREE | | | NO |
| users | 1 | idx_prefecture | 1 | prefecture | A | 6 | NULL | NULL | | BTREE | | | NO |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.019 sec)
MariaDB [development]> EXPLAIN SELECT * FROM users WHERE prefecture = 'Tokyo';
+------+-------------+-------+------+----------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+----------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | users | ALL | idx_prefecture | NULL | NULL | NULL | 987264 | Using where |
+------+-------------+-------+------+----------------+------+---------+------+--------+-------------+
1 row in set (0.007 sec)
MariaDB [development]> インデックスを貼りました。10秒ぐらいかかっています。そして、100万件のレコード数でも実行計画はtype ALL のままですね。今回の検証では100万件まで増やしてもオプティマイザの判断は変わりませんでした。インデックスを辿る → 約14万件分のROW IDを取得 → テーブルから14万件読み直す よりも、全部読む → Tokyo だけを返すの方が早いと判断したのですかね。
フルスキャンと強制的にインデックスを使わせて実行した場合の時間を比較してみます。(この測定は試行回数が少ないため、参考程度にお願いします。また、キャッシュの影響を受けるため、複数回実行すると結果が変わる場合があります。)
MariaDB [development]> SELECT * FROM users FORCE INDEX (idx_prefecture) WHERE prefecture = 'Tokyo';
143113 rows in set (10.374 sec)
143113 rows in set (9.311 sec)
143113 rows in set (9.398 sec)
143113 rows in set (9.373 sec)
143113 rows in set (9.081 sec)MariaDB [development]> SELECT * FROM users WHERE prefecture = 'Tokyo';
143113 rows in set (9.728 sec)
143113 rows in set (9.482 sec)
143113 rows in set (11.350 sec)
143113 rows in set (10.634 sec)
143113 rows in set (9.416 sec)5回の平均では強制的にインデックスを使用した方が早いですね。ただ、試行回数が少ないため、あまり参考にならないと思います。また、実行環境によっても左右されます。約0.6秒差 で 強制的にインデックスを貼った方が早いですが、この結果は下記によっても変わると思います。
- Buffer Pool の状態
- OSのファイルキャッシュ
- CPUの他プロセス
- Docker環境
- ディスクI/O
最後に高カーディナリティのカラムで検索した時の実行計画を確認します。こちらは、type が const になっていますね。高カーディナリティのカラムもインデックスを使った場合と使わなかった場合の時間の測定をします。
MariaDB [development]> EXPLAIN SELECT * FROM users WHERE email = 'pparker_X5Kve_6a4075cf9142d@example.com';
+------+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------+
| 1 | SIMPLE | users | const | users_email_unique | users_email_unique | 1022 | const | 1 | |
+------+-------------+-------+-------+--------------------+--------------------+---------+-------+------+-------+
1 row in set (0.015 sec)
MariaDB [development]>
MariaDB [development]> SELECT * FROM users WHERE email = 'pparker_X5Kve_6a4075cf9142d@example.com';
+--------+---------------+-----------------------------------------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+-----+------------+-----------+--------+-------+
| id | name | email | email_verified_at | password | remember_token | created_at | updated_at | age | prefecture | status | gender | score |
+--------+---------------+-----------------------------------------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+-----+------------+-----------+--------+-------+
| 999958 | Stephon Hauck | pparker_X5Kve_6a4075cf9142d@example.com | 2026-06-28 01:15:59 | $2y$12$ieHzZLX.B7DhQ0bHxui3zO/aDnkos4CXjhqLKhBFJls6hIRBeBLC. | pylWmT0qbL | 2026-06-28 01:15:59 | 2026-06-28 01:15:59 | 43 | Tokyo | suspended | female | 71.14 |
+--------+---------------+-----------------------------------------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+-----+------------+-----------+--------+-------+
1 row in set (0.001 sec)
//強制的にindexを使わずに検索
MariaDB [development]> SELECT * FROM users IGNORE INDEX (users_email_unique) WHERE email = 'pparker_X5Kve_6a4075cf9142d@example.com';
+--------+---------------+-----------------------------------------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+-----+------------+-----------+--------+-------+
| id | name | email | email_verified_at | password | remember_token | created_at | updated_at | age | prefecture | status | gender | score |
+--------+---------------+-----------------------------------------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+-----+------------+-----------+--------+-------+
| 999958 | Stephon Hauck | pparker_X5Kve_6a4075cf9142d@example.com | 2026-06-28 01:15:59 | $2y$12$ieHzZLX.B7DhQ0bHxui3zO/aDnkos4CXjhqLKhBFJls6hIRBeBLC. | pylWmT0qbL | 2026-06-28 01:15:59 | 2026-06-28 01:15:59 | 43 | Tokyo | suspended | female | 71.14 |
+--------+---------------+-----------------------------------------+---------------------+--------------------------------------------------------------+----------------+---------------------+---------------------+-----+------------+-----------+--------+-------+
1 row in set (8.829 sec)
MariaDB [development]> 高カーディナリティの場合は、圧倒的ですね。
今回検証に使用した項目がprefecture(低カーディナリティ) とemail(高カーディナリティ) です。検証結果をまとめておきます。
| 条件 | インデックス | 実行時間 |
|---|---|---|
prefecture='Tokyo' | 使用されない | 約9〜10秒 |
prefecture='Tokyo' | FORCE INDEX | 約9〜10秒 |
email='...' | インデックスあり | 0.001秒 |
email='...' | IGNORE INDEX | 8.829秒 |
最後に
今回100万件までデータを増やして検証しましたが、「インデックスを貼れば必ず高速化する」というわけではないことを改めて確認できました。MariaDBのオプティマイザは、検索条件やデータの分布を考慮した上で、インデックスを利用するかフルスキャンするかを自動で判断しています。一方で、高カーディナリティの列ではインデックスの効果は非常に大きく、今回の検証でも約0.001秒と約8.8秒という大きな差が確認できました。
低カーディナリティ（prefecture）
- 約14万件ヒット
- テーブルの約14%を読む
- インデックスを辿るメリットがほぼ無い
- オプティマイザは正しくALLを選択
高カーディナリティ（email）
- 1件だけヒット
- インデックスなら一瞬
- フルスキャンすると100万件全部確認するので約9秒
次回は複合インデックス や UPDATE文 などの更新時にどのように影響するかを検証したいと思います。最後まで見ていただき、ありがとうございました。
コメント