MySQL CREATE INDEX チュートリアル：MySQL クエリの最適化

はじめに

データベースの最適化において、インデックスの作成はクエリのパフォーマンスを向上させる基本的な手法です。このガイドでは、インデックスを作成する理由と方法を解説し、MySQL クエリをできる限り効率的に実行できるようにします。インデックスを効果的に使う方法を理解すれば、特に大量のデータを持つデータベースで、クエリの実行時間を大幅に短縮できます。

本題に入る前に、モダンで高速な MySQL データベースマネージャーである Beekeeper Studio をご紹介させてください。テーブル構造エディターでは、リストからカラムを選ぶだけで複合インデックスを含むインデックスを作成できます。MySQL では無料でお使いいただけます。こちらからダウンロードしてください。

MySQL のインデックスを理解する

MySQL のインデックスとは、データベーステーブルからのデータ取得を高速化するデータ構造です。その代わりに、インデックスのデータ構造を維持するための追加の書き込みとストレージ容量が必要になります。インデックスを使うと、テーブルにアクセスするたびに全行を検索しなくても、目的のデータをすばやく見つけられます。

MySQL でインデックスを作成する方法

基本的なインデックスの作成

MySQL でインデックスを作成するには、CREATE INDEX ステートメントを使用します：

クエリ：

CREATE INDEX idx_lastname ON employees(lastname);

このコマンドは、employees テーブルの lastname 列に idx_lastname という名前のインデックスを作成し、lastname 列による検索を高速化します。

出力例：

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

ユニークインデックスの作成

ユニークインデックスは、インデックス対象の列のすべての値が重複しないことを保証します。

クエリ：

CREATE UNIQUE INDEX idx_employee_id ON employees(employee_id);

このコマンドは employee_id 列にユニークインデックスを作成し、同じ employee_id を持つ行が2つ以上存在しないようにします。

出力例：

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

複合キーでのインデックスの使用

複数の列にインデックスを作成すると、それらの列でフィルタリングやソートを行うクエリを最適化できます。

クエリ：

CREATE INDEX idx_name_department ON employees(lastname, department_id);

このコマンドは lastname 列と department_id 列の両方を含むインデックスを作成します。これは両方のフィールドを扱うクエリに役立ちます。

出力例：

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

実践的なユースケース

クエリパフォーマンスの向上

インデックスは、特に大規模なデータセットを扱う SELECT ステートメントで、クエリのパフォーマンスを大幅に向上させます。

インデックス作成前：

lastname 列にインデックスがない状態で、employees テーブルに SELECT クエリを実行する場合を想定します。

インデックス作成後：

idx_lastname を作成した後に、同じ SELECT クエリを実行します。

クエリ：

SELECT * FROM employees WHERE lastname = 'Doe';

インデックスなしの出力例：

/* Takes significantly longer, e.g., 1.5 seconds */

インデックスありの出力例：

/* Takes significantly less time, e.g., 0.02 seconds */

データ整合性の確保

ユニークインデックスは列内の値の重複を防ぎ、データの整合性を確保します。

シナリオ：

ユニークインデックスを作成した後に、重複する employee_id を挿入しようとした場合。

出力例：

ERROR 1062 (23000): Duplicate entry '12345' for key 'idx_employee_id'

まとめ

MySQL でのインデックス作成は、データベースクエリを最適化するための重要な手法です。インデックスを作成する列、特に検索条件や JOIN 句で頻繁に使われる列を適切に選ぶことで、大幅なパフォーマンス向上が得られます。

ただし、インデックスはデータ取得を高速化する一方で、インデックス構造を維持するオーバーヘッドにより、データの挿入や更新の処理は遅くなります。読み取りと書き込みのパフォーマンスのバランスを取るよう、慎重に使用しましょう。

これらの例をもとに、ご自身の MySQL データベースで試してインデックスの効果を確かめてみてください。計画的に導入すれば、インデックスはデータベース最適化に欠かせないツールになるはずです。

快適な最適化を！