MySQLのインデックスってどれくらい効果があるの？試してみた

はじめに

MySQLのインデックスについて調べていて、仕組みはわかったけど実際にどれくらい効果があるんだ？と思ったので、実際に試してみました。
以下にリポジトリを公開しているので、ぜひお手元で試してみてください！！
https://github.com/mshr0969/mysql-index-test

今回はインデックスの内部構造やアルゴリズムの詳しい話はしません。以下の記事が詳しくまとまっているので、興味がある方は読んでみてください！
https://zenn.dev/calloc134/articles/4f96b0fe093489

データ準備

テーブル作成

今回は以下のようなシンプルなテーブルを作成しました。salaryはランダムな数値、isDeletedは削除フラグです。

CREATE TABLE IF NOT EXISTS employees
(
    id        VARCHAR(36) PRIMARY KEY,
    name      VARCHAR(255) NOT NULL,
    salary    INT,
    isDeleted BOOLEAN DEFAULT FALSE
);

データ挿入

10万件のランダムなデータを挿入します。1件だけ既知の値を入れて、残りはランダムな値を入れています。salaryは5万から100万のランダムな数値、isDeletedはランダムでTRUEかFALSEを入れています。

データ挿入のSQL

DELIMITER //
CREATE PROCEDURE insert_random_employees()
BEGIN
    DECLARE i INT DEFAULT 1;

    -- 知っているUUIDを指定してレコードを挿入
INSERT INTO employees (id, name, salary, isDeleted)
VALUES ('123e4567-e89b-12d3-a456-426614174000', -- 既知のUUID
        'Test Test', -- 固定の名前
        75000, -- 固定の給与
        FALSE);
-- isDeletedはFALSE固定

-- 残りのレコードをランダムなUUIDで挿入
WHILE i <= 99999
        DO
            -- ランダムに名前を選ぶ (プレフィックスとサフィックスを組み合わせる)
            INSERT INTO employees (id, name, salary, isDeleted)
            VALUES (UUID(), -- ランダムなUUID
                    CONCAT(
                            ELT(FLOOR(1 + (RAND() * 8)), -- ランダムにプレフィックスを選ぶ
                                'John', 'Jane', 'Mike', 'Lisa', 'Chris', 'Sara', 'Tom', 'Emma'
                            ),
                            ' ',
                            ELT(FLOOR(1 + (RAND() * 8)), -- ランダムにサフィックスを選ぶ
                                'Smith', 'Doe', 'Johnson', 'Brown', 'Wilson', 'Moore', 'Taylor', 'Anderson'
                            )
                    ),
                    FLOOR(50000 + (RAND() * 950000)), -- ランダムな給与 (50000 ~ 1000000)
                    ELT(FLOOR(1 + (RAND() * 2)), TRUE, FALSE) -- ランダムでTRUEまたはFALSEを選ぶ
                   );
            SET i = i + 1;
END WHILE;
END//
DELIMITER ;

-- ストアドプロシージャの実行
CALL insert_random_employees();

最初に既知の値を入れるとインデックスの効果が適切にわからないのでは？というのはあるかもしれませんが、あくまでインデックスの効果を確認するためのテストなので多目に見てください。

検証してみる

実際にインデックスを作成して、クエリの実行時間を比較していきます！
データベースを立ち上げて、SQL文を実行していきます。詳しくはリポジトリのREADMEを参照してください。

インデックスのパフォーマンスは入っているデータに大きく依存するので、検証内容は参考までにお願いします！！

数値を単一で検索

インデックスなしの場合

profillingを有効にして、計測していきます。

mysql> SET profiling = 1;

給料が75000の人を検索します。ちょうど1人だけヒットしました。

mysql> SELECT * FROM employees WHERE salary = 75000;
+--------------------------------------+---------+--------+-----------+
| id                                   | name    | salary | isDeleted |
+--------------------------------------+---------+--------+-----------+
| 123e4567-e89b-12d3-a456-426614174000 | John Do |  75000 |         0 |
+--------------------------------------+---------+--------+-----------+

以下のコマンドで実行時間を確認できます。これを見ると、0.05541825秒かかっていることがわかります。

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        1 | 0.05541825 | SELECT * FROM employees WHERE salary = 75000 |
+----------+------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

インデックスありの場合

次に、salaryにインデックスを作成します。

mysql> CREATE INDEX idx_salary ON employees (salary);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

同様に、給料が75000の人を検索します。

mysql> SELECT * FROM employees WHERE salary = 75000;

実行時間を確認すると、0.00263000秒となりました。約20倍速くなっていることがわかります！(インデックスの作成クエリって意外と時間かかるんですね)

mysql> SHOW PROFILES;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.05541825 | SELECT * FROM employees WHERE salary = 75000  |
|        2 | 0.15437175 | CREATE INDEX idx_salary ON employees (salary) |
|        3 | 0.00263000 | SELECT * FROM employees WHERE salary = 75000  |
+----------+------------+-----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

EXPLAINを使って、クエリの実行計画を確認すると、keyをidx_salaryとしたインデックスを使っていることがわかります。

mysql> EXPLAIN SELECT * FROM employees WHERE salary = 75000;
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ref  | idx_salary    | idx_salary | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

数値を範囲指定で検索

先ほどのインデックスを一旦削除して、範囲指定(比較演算子、BETWEEN)で検索してみます。B+Treeはキーの範囲検索にも強いと言われているので、どれくらい効果があるのか確認してみます。
大体2000件ほどヒットするクエリを投げてみます。

比較演算子を使うパターン

mysql> SELECT * FROM employees WHERE salary >= 70000 AND salary <= 100000;

BETWEENを使うパターン

mysql> SELECT * FROM employees WHERE salary BETWEEN 70000 AND 100000;

結果

上からこのような順でクエリを投げています。

1. インデックス無しの比較演算子
2. インデックス無しのBETWEEN
3. インデックス作成
4. インデックスありの比較演算子
5. インデックスありのBETWEEN

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                              |
+----------+------------+--------------------------------------------------------------------+
|        1 | 0.05574500 | SELECT * FROM employees WHERE salary >= 70000 AND salary <= 100000 |
|        2 | 0.04372875 | SELECT * FROM employees WHERE salary BETWEEN 70000 AND 100000      |
|        3 | 0.15547275 | CREATE INDEX idx_salary ON employees (salary)                      |
|        4 | 0.02850550 | SELECT * FROM employees WHERE salary >= 70000 AND salary <= 100000 |
|        5 | 0.02688425 | SELECT * FROM employees WHERE salary BETWEEN 70000 AND 100000      |
+----------+------------+--------------------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

比較演算子、BETWEENともにインデックスを作成することで、実行時間が半分程度になりました！

データ量の話
今回は10万件のデータを使っていますが、データ量が増えるほどインデックスの効果が出ます。
一般には、レコード数が1万以下の場合はほぼ効果がないと考えていいようです。(参考：達人に学ぶDB設計 徹底指南書 ～初級者で終わりたくないあなたへ)
実際、1万件のデータで比較してみるとインデックスを入れたほうが、逆にパフォーマンスが悪くなりました(最初少ないデータでやって沼ってたのは内緒)

文字列で検索

nameカラムにインデックスを作成して、Test Testという名前で検索してみます。データ作成時に1件だけ登録していたので、1件ヒットします。

mysql> SELECT * FROM employees WHERE name = 'Test Test';

結果

0.044秒→0.01秒と、インデックスを使用することでかなり早くなりました！

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration   | Query                                            |
+----------+------------+--------------------------------------------------+
|        1 | 0.04443325 | SELECT * FROM employees WHERE name = 'Test Test' |
|        2 | 0.23849050 | CREATE INDEX idx_name ON employees (name)        |
|        3 | 0.00114200 | SELECT * FROM employees WHERE name = 'Test Test' |
+----------+------------+--------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

アンチパターンを試してみる

インデックスのアンチパターンと言われているいくつかの項目で、本当にパフォーマンスがよくならないのか試してみます。
それぞれの項目でインデックスを作成し直して比較します。ORや否定を用いるパターンもアンチパターンとしてありますが、今回は省略します。

カーディナリティが低い

isDeletedカラムにインデックスを作成してみます。isDeletedはTRUEかFALSEの2値しか持たないので、カーディナリティが低いです。
インデックスありなしで以下のクエリを実行してみます。

mysql> SELECT * FROM employees WHERE isDeleted = TRUE;

結果

isDeletedカラムにインデックスを作成しても、実行時間が早くならない、むしろ遅くなることがわかります。

mysql> SHOW PROFILES;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration   | Query                                            |
+----------+------------+--------------------------------------------------+
|        1 | 0.06491225 | SELECT * FROM employees WHERE isDeleted = TRUE   |
|        2 | 0.17902225 | CREATE INDEX idx_salary ON employees (isDeleted) |
|        3 | 0.13071900 | SELECT * FROM employees WHERE isDeleted = TRUE   |
+----------+------------+--------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

インデックスカラムに演算を行う

salaryカラムにインデックスを作成して、salary * 2のように直接演算を行います。これは先ほどかなり早くなった単一検索と検索内容は同じですね。

mysql> SELECT * FROM employees WHERE salary * 2 = 150000;

結果

インデックスカラムに演算を行うと、大してパフォーマンスが向上しないことがわかります！

mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.05377100 | SELECT * FROM employees WHERE salary * 2 = 150000 |
|        2 | 0.15746300 | CREATE INDEX idx_salary ON employees (salary)     |
|        3 | 0.04125725 | SELECT * FROM employees WHERE salary * 2 = 150000 |
+----------+------------+---------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

まとめ

簡単なデータセットでインデックスの性能評価を行いました。
インデックスを使うことで、検索速度が大幅に向上することがわかりました。また、アンチパターンを試すと、たしかにインデックスの恩恵がすくなることを確かめることができました。
実際に動かすことでインデックスへの理解が深まりました！この記事がどなたかのお役に立てると幸いです！