達人に学ぶDB設計を読んだので、MariaDBでインデックスのデメリットを検証してみた③

DBチューニングの記事のアイキャッチ DB設計

はじめに

こちらの記事は前回の続きとなっております。前回も見ていただけると嬉しいです。

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

前回は100万件のデータを用意し、インデックスの効果と実行計画を検証しました。その検証でインデックスの恩恵を感じることができたかと思います。しかし、インデックスは万能ではありません。デメリットも存在するので、今回は、デメリットを実際に検証してみます。

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

今回も前回と同じリポジトリを使っています。また、テーブル構成も前回と同じです。

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

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

検証(UPDATE文とINSERT文)

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.030 sec)

MariaDB [development]>

前回の続きのままなので、インデックスもそのままになっています。先に追加インデックスがない状態で、INSERTやUPDATEを実行したいので、emailとprefectureのインデックスを削除します。

MariaDB [development]> ALTER TABLE users DROP INDEX users_email_unique;
Query OK, 0 rows affected (0.111 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [development]> ALTER TABLE users DROP INDEX idx_prefecture;
Query OK, 0 rows affected (0.046 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      |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.022 sec)

MariaDB [development]>

単一レコードの更新をします。

MariaDB [development]> UPDATE users SET email = 'test_update_1@example.com' WHERE id = 1;
Query OK, 1 row affected (0.007 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [development]> UPDATE users SET name = 'Test User Updated' WHERE id = 1;
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

複数レコードの一括更新

MariaDB [development]> UPDATE users SET email = CONCAT('bulk_', id, '_', email) WHERE id <= 100000;
Query OK, 100000 rows affected (6.236 sec)
Rows matched: 100000  Changed: 100000  Warnings: 0

単一レコードの挿入

MariaDB [development]> INSERT INTO users (
    ->   name, email, email_verified_at, password, remember_token, 
    ->   created_at, updated_at, age, prefecture, status, gender, score
    -> ) VALUES (
    ->   'Test User', 'new_user_bench@example.com', NOW(), 
    ->   '$2y$12$ieHzZLX.B7DhQ0bHxui3zO/aDnkos4CXjhqLKhBFJls6hIRBeBLC.', 'token12345', 
    ->   NOW(), NOW(), 30, 'Tokyo', 'active', 'male', 50.00
    -> );
Query OK, 1 row affected (0.007 sec)

複数レコードの挿入

MariaDB [development]> INSERT INTO users (
    ->   name, email, email_verified_at, password, remember_token,
    ->   age, prefecture, status, gender, score, created_at, updated_at
    -> )
    -> SELECT 
    ->   name, 
    ->   CONCAT('bulk_ins_', UUID_SHORT(), '@example.com'),
    ->   email_verified_at,
    ->   password,
    ->   remember_token,
    ->   age, 
    ->   prefecture, 
    ->   status, 
    ->   gender, 
    ->   score, 
    ->   NOW(), 
    ->   NOW()
    -> FROM users 
    -> LIMIT 100000;
Query OK, 100000 rows affected (1.938 sec)
Records: 100000  Duplicates: 0  Warnings: 0

MariaDB [development]>

先ほど削除したインデックスを再作成します。

MariaDB [development]> ALTER TABLE users ADD UNIQUE INDEX users_email_unique (email);
Query OK, 0 rows affected (13.759 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [development]> ALTER TABLE users ADD INDEX idx_prefecture (prefecture);
Query OK, 0 rows affected (13.569 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         |     1085782 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          0 | users_email_unique |            1 | email       | A         |     1085782 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| users |          1 | idx_prefecture     |            1 | prefecture  | A         |           6 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.015 sec)

MariaDB [development]>

インデックスを再度作成したので、先ほどのUPDATE文や INSERT文を実行します。

単一レコードの更新

MariaDB [development]> UPDATE users SET email = 'test_update_2@example.com' WHERE id = 1;
Query OK, 1 row affected (0.009 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [development]> UPDATE users SET name = 'Index User Updated' WHERE id = 1;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

複数レコードの更新

MariaDB [development]> UPDATE users
    -> SET email = CONCAT('bulk_idx_', id, '_', email)
    -> WHERE id <= 100000;
Query OK, 100000 rows affected (13.036 sec)
Rows matched: 100000  Changed: 100000  Warnings: 0

追加インデックスがない場合と比べてかなり遅いですが、テーブルのデータだけではなくemailインデックスも更新する必要があります。

単一レコードの挿入

MariaDB [development]> INSERT INTO users (
    ->   name, email, email_verified_at, password, remember_token, 
    ->   created_at, updated_at, age, prefecture, status, gender, score
    -> ) VALUES (
    ->   'Test User Index', 'new_user_bench_idx@example.com', NOW(), 
    ->   '$2y$12$ieHzZLX.B7DhQ0bHxui3zO/aDnkos4CXjhqLKhBFJls6hIRBeBLC.', 'token12345', 
    ->   NOW(), NOW(), 30, 'Tokyo', 'active', 'male', 50.00
    -> );
Query OK, 1 row affected (0.020 sec)

MariaDB [development]>

複数レコードの挿入

MariaDB [development]> INSERT INTO users (
    ->   name, email, email_verified_at, password, remember_token,
    ->   age, prefecture, status, gender, score, created_at, updated_at
    -> )
    -> SELECT 
    ->   name, 
    ->   CONCAT('bulk_idx_ins_', UUID_SHORT(), '@example.com'),
    ->   email_verified_at,
    ->   password,
    ->   remember_token,
    ->   age, 
    ->   prefecture, 
    ->   status, 
    ->   gender, 
    ->   score, 
    ->   NOW(), 
    ->   NOW()
    -> FROM users 
    -> LIMIT 100000;
Query OK, 100000 rows affected (6.215 sec)
Records: 100000  Duplicates: 0  Warnings: 0

INSERTでは、テーブルへのレコード追加に加えて、設定されているインデックスにも新しい値を追加する必要があります。

最後に(検証結果まとめ)

※検証結果はあくまでも私の環境での検証結果となります。CPU使用率やハードウェアによっては変動することもあるため、参考程度でお願いいたします。

検証結果としては、下記の通りです。単一レコードの場合は、そこまで影響はありませんが、レコード数が増えるほど、その差は顕著になります。

操作内容追加インデックスなしインデックスあり差分 / 影響度
単一INSERT (1件)0.007 sec0.020 sec約2.8倍
バルクINSERT (10万件)1.938 sec6.215 sec約3.2倍遅い
単一UPDATE (対象カラム: email)0.007 sec0.009 secわずかに遅延
単一UPDATE (非対象カラム: name)0.004 sec0.002 sec誤差範囲
バルクUPDATE (10万件 email)6.236 sec13.036 sec約2.1倍遅い

このように、インデックスは検索性能を向上させる一方で、INSERTやUPDATEなどの書き込み処理では、インデックス自体の更新コストが発生します。そのため、インデックスは多ければよいというものではなく、検索性能と書き込み性能のバランスを考慮して設計する必要があります。

なお、実際の現場では、大量のレコードを一度に更新すると負荷が大きくなる場合もあるため、インデックスの有無にかかわらず、バッチ処理などで分割して更新するケースもあります。

最後まで見ていただきありがとうございました。次回は複合インデックスの検証をしてみたいと思います。

コメント

タイトルとURLをコピーしました