達人に学ぶDB設計を読んだので、MariaDBでインデックスと実行計画を検証してみた①

はじめに
テストデータ作成
検証
最後に

はじめに

この度、達人に学ぶDB設計徹底指南書を読んだので、復習も兼ねて、備忘録として残しておきます。本記事では、実際に10万件のデータを作成し、EXPLAINを使いながらインデックスの効果を確認していきます。

達人に学ぶDB設計徹底指南書第2版
本書は、プロのDBエンジニアである著者が、DB設計の基礎と実践ノウハウをやさしく手ほどきする『達人に学ぶDB設計徹底指南書』の改訂書籍です。第2版では、初期構成を活かしつつ内容を最新化するだけでなく、クラウドにも対応できるようにしました。【…
www.amazon.co.jp

今回チューニングを学ぶ上で、お試し環境を構築しました。実際に使用したリポジトリがこちらになります。

GitHub – yu-corder/db-tunig-lab: A hands-on database tuning lab for learning and validating indexing, query optimization, EXPLAIN analysis, and database performance techniques.
A hands-on database tuning lab for learning and validating indexing, query optimization, EXPLAIN analysis, and database …
github.com

今回使用したリポジトリはあくまでも検証用のリポジトリとなりますので、docker-compose.yml やDockerfile 内の記述は参考程度にお願いいたします。そして、実際にテスト用のデータを作成するにあたって、PHPフレームワークのLaravel を使用しています。理由としては、私自身がLaravel を使いなれており、以前、Docker で Laravel プロジェクトを構築していたので、それを流用するのが環境を作る上で早いからです。そして、Laravel はテスト用のデータを作成する機能が充実しているため、Laravel を使用しました。今回はあくまでも、チューニングや実行計画の確認などがメインとなりますので、Laravel のコマンドなどの詳細については、あまり触れません。

テストデータ作成

Docker で Laravel プロジェクトを作成する手順については、今回は省略させていただきます。機会があれば、構築手順なども備忘録として投稿しようかと思います。

さて、今回チューニングとして使用するテーブルは Users にします。 Laravel では標準で Users テーブルのマイグレーションファイル、Factory や Seeder があるので、それを少し修正しました。

Schema::create('users', function (Blueprint $table) {
          $table->id();
          $table->string('name');
          $table->string('email')->unique();
          $table->timestamp('email_verified_at')->nullable();
          $table->string('password');
          $table->rememberToken();
          $table->timestamps();
          $table->unsignedTinyInteger('age');
          $table->string('prefecture');
          $table->string('status', 20);
          $table->string('gender', 10);
          $table->decimal('score', 8, 2);
 });

カラムを追加してます。カーディナリティが高いものだけでなく、低いものにインデックスを貼るとどうなるかも確認したかったので、status や gender を追加しています。そして、範囲検索やソートで age や score を使用して検証もしていきます。

User::factory()->count(100000)->create(); を試しましたが、私の環境では20分以上経過しても終了しませんでした。

標準のコードだと作成できなかったので、コードを修正します。 create() は1件ずつINSERTを実行するため、大量データ投入には向いていない可能性があります。

User::factory()->count(100000)->create();

修正後のSeeder(factory()->raw() と insert() を組み合わせることで、バルクインサートを行うように変更しました。)

    public function run(): void
    {
        $totalRecords = 100000;
        $chunkSize = 5000;

        $this->command->info("Starting to seed {$totalRecords} users...");

        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.");
        }
    }

下記コマンドでテストデータを作成していきます。

php artisan migrate:fresh --seed

検証に使うテーブルの構成です。

users
│
├── id (PK)
├── name
├── email (UNIQUE)        ← 高カーディナリティ
├── email_verified_at
├── password
├── remember_token
├── created_at
├── updated_at
├── age                   ← 範囲検索用
├── prefecture            ← 低カーディナリティ（7種類）
├── status                ← 低カーディナリティ（3種類）
├── gender                ← 低カーディナリティ（3種類）
└── score                 ← 範囲検索・ソート用

検証

さて、実際に検証をしていきます。検証するにあたって、10万レコードぐらいだと、実行時間の差は実感できないと思うので、後ほど、100万レコードぐらいにします。

MariaDB [(none)]> use development;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [development]> SELECT COUNT(*) FROM users;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.087 sec)

MariaDB [development]>

まずは実行計画から、確認します。

//typeがALL になっている
//type が ALL の場合、フルテーブルスキャンを意味します。つまり users テーブル全体を走査して条件に一致するレコードを探します。

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]>

試しに、index を貼って、実行計画を確認します。

MariaDB [development]> CREATE INDEX idx_prefecture ON users(prefecture);
Query OK, 0 rows affected (0.318 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         |       98625 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          0 | users_email_unique |            1 | email       | A         |       98625 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_prefecture     |            1 | prefecture  | A         |           6 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.014 sec)

//typeがALLのまま。想定ではref になる想定でしてた
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 | 98625 | Using where |
+------+-------------+-------+------+----------------+------+---------+------+-------+-------------+
1 row in set (0.010 sec)

MariaDB [development]>

インデックスを使わない判断をオプティマイザがしている 可能性が高い？

SHOW INDEX の結果を見ると、Cardinality は6になっています。これはオプティマイザが推定したユニーク値数です。今回投入した都道府県は7種類なので、概ね近い値になっています。

Tokyo
Kanagawa
Chiba
Saitama
Osaka
Aichi
Fukuoka

10万件のデータに対して、仮に均等分布なら、100000 / 7 = 14285件が Tokyo になります。

インデックスを辿る → 1万4000件の行を探す → 実データを1万4000回取りにいくよりも

全部読む → Tokyo だけを返すの方が早いと判断したのですかね。（オプティマイザがそう判断した可能性があります）これはレコード数を増やしたあとにも検証してみます。

MariaDB [development]> EXPLAIN SELECT * FROM users WHERE email = 'dmarquardt_tyQ6q_6a374fc15a2e4@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.010 sec)

MariaDB [development]>

users_email_unique が効いて、type が const になっていますね。

ここでそれぞれのタイプを最後にまとめておきます。今回の検証では index を貼ると実行計画がどのように変わるのかを確認しました。今回の例では、10万レコードで低カーディナリティのカラムに対して、index を貼ってもまだ、ALL のままですが、、、次回はレコードを100万にして検証をしてみたいと思います。

type	意味	速度感
const	PKやUNIQUEで1件特定	最速
eq_ref	JOIN先をPK/UNIQUEで1件特定	constの次
ref	インデックスを使った等価検索	高速
range	インデックスを使った範囲検索	やや高速
index	インデックス全走査	遅い
ALL	テーブル全走査	最も遅い

最後に

index を貼っても、必ず使われるわけでない、、、奥が深いですね。(検索結果が全体の数%〜10%程度に絞り込める場合に効果を発揮します。ヒット件数が多すぎる場合、インデックス経由で1件ずつランダムアクセスするよりも、マルチブロックリードでテーブル全体を一気にスキャン（ALL）した方が早いとオプティマイザが判断するため、インデックスが無視されます。)

強制的にindex を使わせて取得をしてみました。実際にindex を使わない場合の方が早いですね。

MariaDB [development]> SELECT * FROM users FORCE INDEX (idx_prefecture) WHERE prefecture = 'Tokyo';

14263 rows in set (0.271 sec)

MariaDB [development]> SELECT * FROM users WHERE prefecture = 'Tokyo';

14263 rows in set (0.206 sec)

最後に今回の検証結果のまとめます。

– インデックスを作成しても必ず使われるわけではない
– オプティマイザはカーディナリティも考慮している
– EXPLAINを見ることでDBの判断を確認できる

次回の記事

レコード数を100万件に増やす
FORCE INDEXを試す
実際の実行時間も計測する

最後まで観ていただきありがとうございました。次回はより、濃い内容になるかと思いますので、次回も見ていただけると嬉しいです。

続きの記事を投稿しましたので、こちらも見ていただけると嬉しいです。

達人に学ぶDB設計を読んだので、MariaDBでインデックスと実行計画（EXPLAIN）を検証してみた②
前回の10万件に続き、今回は100万件のデータでMariaDBのインデックスと実行計画（EXPLAIN）を検証。データ量が増えてもオプティマイザの判断は変わるのかを確認します。
yu-syumilog.com
2026.06.28