MySQL〜インデックス〜

今回はMySQLの機能の１つであるインデックスについて、紹介します。

MySQLの概要については、以下の記事で紹介していますので、ご参考ください。

MySQLを理解する〜概要〜
MySQLは、世界で最も広く使用されているオープンソースのリレーショナルデータベース管理システム(RDBMS)の1つです。この記事では、MySQLの概要について調べた内容を共有します。特徴オープンソース: GNU General Publi...
infnotes.com
2024.09.15

MySQLのインデックスは、データベースのパフォーマンスを向上させるための重要な機能です。
インデックスを適切に使用することで、データの検索速度を大幅に改善できます。

インデックスの基本概念

インデックスは、データベーステーブルの特定の列に対して作成され、データを特定の順序で整理するためのデータ構造です。これにより、検索やクエリのパフォーマンスが向上します。

インデックスは、通常B木（B-tree）やハッシュテーブルなどのデータ構造を使用して実装されます。

インデックスがない場合、MySQLはテーブル全体をスキャンする必要があり、これを「フルテーブルスキャン」と呼びます。

インデックスの種類

MySQLにはいくつかの異なるタイプのインデックスがあります。
主な種類は以下の通りです。

インデックス名説明
プライマリーインデックステーブルの主キーに基づいて作成され、各レコードの一意性を保証します。データはこのインデックスの順序に従って格納されます。
セカンダリーインデックスプライマリーインデックス以外の列に対して作成されるインデックスです。
セカンダリーインデックスは、プライマリーキーの参照を含むため、検索時に追加のオーバーヘッドが発生します。
複合インデックス複数の列を組み合わせて作成されるインデックスです。
特定のクエリ条件に対して最適化され、WHERE句やJOIN条件でよく使われる列に設定することで、クエリのパフォーマンスを向上させます。

インデックスの作成

インデックス作成コマンド

インデックスは、CREATE INDEX文またはテーブル作成時に指定して作成できます。
インデックスの作成コマンドは以下の通りです。

CREATE INDEXを使用する場合

CREATE INDEX インデックス名 ON テーブル名(属性);

テーブル作成時にインデックスを作成する場合

CREATE TABLE テーブル (
    属性1 データ型1 PRIMARY KEY,
    属性2 データ型2,
    属性3 データ型3,
    INDEX インデックス (属性)
);

インデックス作成実行例

具体的な実行例について、紹介します。

実行環境

実行環境は以下の通りです。

OSCentOS Stream release 9
MySQL8.4.2 MySQL Community Server
データベース名my_database
テーブル名users

テーブルusersのカラム情報は以下の通りです。

mysql> desc users;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| user_id    | int         | NO   | PRI | NULL    |       |
| name       | varchar(50) | YES  |     | NULL    |       |
| age        | int         | YES  |     | NULL    |       |
| birthplace | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

実行例

プライマリーインデックス

プライマリインデックスは、テーブル作成時にプライマリキーに指定した属性に対して、自動的に作成されます。
プライマリキーはPRIMARY KEYを指定することで、設定可能です。

mysql> CREATE TABLE users (
    ->     user_id INT PRIMARY KEY,
    ->     name VARCHAR(50),
    ->     age int,
    ->     birthplace VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
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 | user_id     | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.04 sec)

mysql>
セカンダリーインデックス

セカンダリーインデックスを作成します。
今回はnameに対して、セカンダリーインデックス：idx_nameを作成します。

実行コマンドは以下の通りです。

CREATE INDEX idx_name ON users(name);

実行結果は以下の通りです。

テーブルのインデックス確認はshow indexコマンドを利用します。
実行結果から、属性nameにインデックス：idx_nameが付与されました。

mysql> CREATE INDEX idx_name ON users(name);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
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 | user_id     | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_name           |            1 | name        | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.04 sec)

mysql>

テーブル作成時に同時に作成する場合は、以下のコマンドを実行します。
実行結果は上記と同じです。

mysql> CREATE TABLE users (
    ->     user_id INT PRIMARY KEY,
    ->     name VARCHAR(50),
    ->     age int,
    ->     birthplace VARCHAR(50),
    ->     INDEX idx_name (name)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
複合インデックス

複合インデックスを作成します。
今回はage、birthplaceに対して、セカンダリーインデックス：idx_age_birthplaceを作成します。

実行コマンドは以下の通りです。

CREATE INDEX idx_age_birthplace ON users(age, birthplace);

実行結果は以下の通りです。

テーブルのインデックス確認はshow indexコマンドを利用します。
実行結果から、属性age、birthplaceにインデックス：idx_age_birthplaceが付与されました。

mysql> CREATE INDEX idx_age_birthplace ON users(age, birthplace);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
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 | user_id     | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_age_birthplace |            1 | age         | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_age_birthplace |            2 | birthplace  | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.04 sec)

mysql>

テーブル作成時に同時に作成する場合は、以下のコマンドを実行します。
実行結果は上記と同じです。

mysql> CREATE TABLE users (
    ->     user_id INT PRIMARY KEY,
    ->     name VARCHAR(50),
    ->     age int,
    ->     birthplace VARCHAR(50),
    ->     INDEX idx_age_birthplace (age, birthplace)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>

インデックス作成の利点

  • 検索速度の向上: インデックスを使用することで、データ検索が迅速になります。特に大規模なテーブルでは、インデックスがないとパフォーマンスが大幅に低下します。
  • クエリのパフォーマンス向上: WHERE句やJOIN句でインデックスされた列を使用することで、データベースは必要なデータを迅速に取得できます。

インデックス作成の注意点

  • 書き込み速度の低下: インデックスを持つテーブルは、データの挿入や更新時にインデックスも更新する必要があるため、書き込み速度が低下することがあります。
  • ストレージの消費: インデックスは追加のストレージを必要とします。多くのインデックスを持つテーブルは、ストレージの使用量が増加します。
  • NULL値の扱い: インデックスはNULL値を除いて作成されるため、NULLが多い列ではインデックスの効果が薄れることがあります。

インデックスの削除

インデックスの削除は、DROP INDEXコマンドを利用します。

インデックス削除コマンド

実行コマンドは以下の通りです。

DROP INDEX インデックス名 ON テーブル名;

インデックス削除実行例

インデックス削除の実行例を紹介します。
先ほどインデックス作成で作成した以下のインデックスのうち、インデックス名：idx_nameを削除します。

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 | user_id     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_name           |            1 | name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_age_birthplace |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_age_birthplace |            2 | birthplace  | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.03 sec)

mysql>

実行結果は以下の通りです。
インデックス名：idx_nameが削除されていることが確認できました。

mysql> DROP INDEX idx_name ON users;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
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 | user_id     | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_age_birthplace |            1 | age         | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | idx_age_birthplace |            2 | birthplace  | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

mysql>

まとめ

今回はMySQLの機能の１つであるインデックスについて、紹介しました。

MySQLのインデックスは、データベースのパフォーマンスを最適化するための強力なツールです。
適切にインデックスを作成し、使用することで、データ検索の効率を大幅に向上させることができます。

ただし、インデックスの作成にはコストが伴うため、どの列にインデックスを作成するかは慎重に検討する必要があります。

ご参考になれば、幸いです。

以上になります。ありがとうございました。

参考

https://blog.codecamp.jp/mysql-command-commentary
https://zenn.dev/canalun/articles/all_about_mysql_index
https://rookie.levtech.jp/guide/detail/204/

コメント

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