MySQLインデックスの習得によるクエリパフォーマンスの高速化

MySQLのインデックスは、データベースパフォーマンスを最適化するための極めて重要なテクニックです。インデックスを戦略的に作成することで、データの取得にかかる時間を劇的に短縮し、アプリケーションの応答時間を高速化し、システム全体の効率を高めることができます。本ガイドでは、MySQLインデックスの基本、さまざまなインデックスタイプの解説、作成と分析のためのベストプラクティスを紹介し、この必須のパフォーマンスチューニングスキルを習得するお手伝いをします。

テーブルを適切にインデックス化する方法を理解することは、動作の鈍いデータベースと超高速なデータベースを分ける鍵となります。インデックスがない場合、MySQLは多くのクエリでテーブル全体のスキャンを実行しなければならず、データが増加するにつれてこれは非常に非効率になります。本記事は、インデックスの機会を特定し、効果的なインデックスを作成し、その影響を検証するための知識を提供することを目的としています。

MySQLインデックスとは？

MySQLインデックスは、データベーステーブル上のデータ取得操作の速度を向上させるデータ構造です。これは書籍の索引のようなものだと考えてください。特定のトピックを見つけるために本全体を読むのではなく、索引を見て、正確なページ番号を知ることができます。同様に、データベースインデックスにより、MySQLはテーブル全体をスキャンすることなく、特定のクエリ条件に一致する行を素早く見つけることができます。

テーブルに対してクエリを実行する際、MySQLはインデックスを使用して、すべての行を検査する場合よりもはるかに高速に関連する行を見つけることができます。これは、行数が多いテーブルや、フィルタリング（WHERE句）、テーブル結合（JOIN句）、またはソート（ORDER BY句）を伴うクエリにとって特に有益です。

インデックスの仕組み

MySQLは通常、インデックスにB-treeデータ構造を使用します。B-treeは、データをソートされた状態に保ち、効率的な検索、挿入、削除を可能にするバランスの取れたツリー構造です。1つ以上のカラムにインデックスを作成すると、MySQLはこのB-tree構造を構築します。この構造では：

葉ノードには実際のデータポインタが含まれるか、クラスタ化インデックス（InnoDBの主キーなど）の場合はデータ行そのものが含まれます。
内部ノードには、正しい葉ノードを見つけるためにツリーをナビゲートするのに役立つキーが含まれます。

クエリがインデックス付きカラムを使用すると、MySQLはB-treeをたどって目的の行へのポインタをすばやく見つけます。この対数時間計算量（O(log n)）は、テーブル全体を線形スキャン（O(n)）するよりも大幅に高速です。

MySQLインデックスの種類

MySQLは様々な種類のインデックスをサポートしており、それぞれに独自の強みとユースケースがあります。

1. PRIMARY KEY

PRIMARY KEY制約は、カラムの値がすべて一意であり、NULLであってはならないことを保証します。これは暗黙的にインデックスが設定されます。
テーブルにはPRIMARY KEYを1つしか持つことはできません。
InnoDBテーブルは、プライマリキーによって物理的に順序付けられます（クラスタ化インデックス）。

例:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. UNIQUE Index

UNIQUEインデックスは、インデックスが設定されたカラムの値がすべて異なることを強制します。NULL値を許可しますが、複数のNULL値が許可されます（ただし、そのカラムがPRIMARY KEYや他のUNIQUE制約の一部でない場合）。
プライマリ識別子ではないものの、一意性が求められるデータ整合性を確保する場合に役立ちます。

例:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX (または KEY)

非一意インデックスとも呼ばれる標準的なインデックスです。
データ取得を高速化するために使用されます。一意性は強制しません。

例:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. FULLTEXT Index

CHAR、VARCHAR、TEXTカラムに対する全文検索に使用されます。
大きなテキストフィールド内での複雑なキーワード検索を可能にします。
MyISAMとInnoDBストレージエンジンでのみサポートされています。

例:

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. SPATIAL Index

空間データ型（例：ポイント、ライン、ポリゴン）のインデックス作成に使用されます。
カラムがNOT NULLとして定義されている必要があります。
MyISAMとInnoDB（特定のデータ型を使用する場合）でのみサポートされています。

6. HASH Index（限定的な用途）

MySQLのMEMORYストレージエンジンはHASHインデックスをサポートしています。これらは等価性ルックアップ（O(1)）では非常に高速ですが、範囲クエリやソートには役立ちません。
ほとんどの一般的なシナリオにおける汎用的なインデックスタイプではありません。

インデックスの作成と管理

インデックスの作成方法

インデックスは、テーブル作成時、または既存のテーブルを変更することで作成できます。

1. テーブル作成時:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. 既存のテーブルの変更:

-- 単一カラムインデックスの追加
ALTER TABLE customers
ADD INDEX idx_email (email);

-- 一意インデックスの追加
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- 複数カラム（複合）インデックスの追加
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

インデックスの削除方法

インデックスが不要になった場合、またはパフォーマンスに悪影響を及ぼしている場合（例：書き込み時）、削除できます。

-- 標準インデックスの削除
ALTER TABLE customers
DROP INDEX idx_email;

-- 一意インデックスの削除
ALTER TABLE users
DROP INDEX uidx_username;

複数カラム（複合）インデックス

複合インデックスは2つ以上のカラムに対して作成されます。複合インデックス内のカラムの順序は極めて重要です。

(col1, col2)に対する複合インデックスは、col1単独、またはcol1とcol2の両方でフィルタリングするクエリに使用できます。
col2のみでフィルタリングするクエリには、通常使用されません。

例:

(customer_id, order_date)のインデックスを検討します。このインデックスは、次のようなクエリで最も効果的です。

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

SELECT * FROM orders WHERE order_date = '2023-10-27';には、あまり役に立たない可能性があります。

MySQLインデックスのベストプラクティス

1. `WHERE`句、`JOIN`句、`ORDER BY`句で使用されるカラムにインデックスを付ける

これらは、インデックスが大幅なパフォーマンス向上をもたらす最も一般的な場所です。

WHERE句: フィルタ条件はインデックスの主要な使用例です。
JOIN条件: JOINステートメントのON句で使用されるカラムにインデックスを付けると、テーブル結合が劇的に高速化されます。
ORDER BY句およびGROUP BY句: インデックスは、MySQLがソート操作を回避するのに役立ちます。

2. 複合インデックスを賢く使う

順序が重要: クエリで頻繁に一緒に使用されるカラムがあれば、最も選択性の高いカラム（最も一意な値を持つカラム）をインデックス定義の先頭に配置します。
「