はじめに

複合インデックス（Composite Index）は、MySQLでクエリパフォーマンスを最適化するための重要なテクニックです。これは複数の列に対して作成されるインデックスで、正しく設計することでクエリの効率を大幅に向上させることができます。ただし、複合インデックスの効果は、列の順序（Leading Column）に大きく依存します。

この記事では以下を解説します：

複合インデックスとは何か
複合インデックスを使用すべきタイミング
Leading Columnの重要性
ケーススタディを通じた具体例

データ準備

-- Create table
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT NOT NULL,
    hire_date DATE NOT NULL,
    salary INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description text
);

-- Insert 200K record
DELIMITER $$

CREATE PROCEDURE InsertEmployees()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 200000 DO
        INSERT INTO employees (department_id, hire_date, salary, name, description)
        VALUES (
            FLOOR(RAND() *20) + 1, -- Random department_id from 1 to 20
            DATE(FROM_DAYS(TO_DAYS('2000-01-01') + FLOOR(RAND()* 8035))), -- Random from 2000-01-01 to 2022-01-01
            FLOOR(RAND() * 100) * 1000 + 30000, -- Salary random from (1,100) * 1000 + 30000
            CONCAT('Employee_', i), 
            CONCAT('Employee_', i), 
            (SELECT REPEAT(
                CHAR(FLOOR(RAND() * 26) + 97), -- Random from a-z
                FLOOR(500 + RAND() * 501) -- From 500 to 1000 characters
            ))
        );
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;
CALL InsertEmployees();

以下はテーブルのステータスになります。

1. 複合インデックスとは？

複合インデックスは、テーブル内の複数列で構成されるインデックスです。同時に複数列を使用するクエリのパフォーマンスを最適化するために設計されています。

例:

CREATE INDEX idx_composite ON employees(department_id, hire_date);

この複合インデックスは、department_id と hire_date の両方をフィルタ条件に含むクエリで最も効果を発揮します。

2. 複合インデックスを使用すべきタイミング

クエリで複数列を頻繁にフィルタ（WHERE条件）やソート（ORDER BY条件）に使用する場合
テーブル内のインデックス数を減らしつつ、複数列のパフォーマンスを向上させたい場合

例：

特定の部署に所属する、特定の日以降に採用された従業員を検索する場合
カテゴリと価格で商品をソートする場合

3. Leading Columnの重要性

複合インデックスのパフォーマンスは、インデックス内の列の順序に依存します。インデックス内の最初の列は「Leading Column」と呼ばれます。

複合インデックスを使用する際のルール

クエリのフィルタ条件またはソート条件にLeading Columnが含まれる必要があります
Leading Columnが使用されない場合、インデックスは効果的に機能しません

例：

idx_composite(department_id, hire_date)というインデックスの場合：
department_id と hire_date を利用するクエリ：インデックス全体が使用される

hire_date のみを利用するクエリ：効果的に機能しない

4. ケーススタディ：具体例

4.1. 複合インデックスの作成

department_id と hire_date に対する複合インデックスを作成します。

CREATE INDEX idx_department_hire ON employees(department_id, hire_date);

4.2. クエリケーススタディ

Case 1: department_id と hire_date の両方を使用

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 3 AND hire_date > '2015-01-01';

結果：複合インデックス idx_department_hire が効果的に使用される。

Case 2: department_id のみを使用

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 3;

結果：Leading Columnであるため、インデックスが使用される。

Case 3: hire_date のみを使用

EXPLAIN ANALYZE SELECT * FROM employees WHERE hire_date > '2015-01-01';

結果：複合インデックスは使用されない。

分析：

Case 1: 両列を使用して最適化されたクエリ
Case 2: Leading Columnが使用されているため、インデックスが機能する
Case 3: Leading Columnが含まれないため、インデックスは機能しない

5. 注意点

- Leading Columnには高い選択性を持つ列を選ぶ：

高い選択性：ユニークな値の数が多い列（例：department_id）

- 複合インデックスはよく使用されるクエリに基づいて作成：

必要のないインデックスを作成しない

- インデックスの過剰作成を避ける：

インデックスが多すぎると、INSERTやUPDATE時のパフォーマンスに影響を及ぼす

6. 結論

複合インデックスは、クエリのパフォーマンスを向上させる強力なツールですが、慎重に設計する必要があります：

Leading Columnをクエリの使用順序に基づいて選択する。
EXPLAIN コマンドを使用してインデックスが正しく機能しているか確認する。
ぜひ、複合インデックスをシステムに適用してみて、コメント欄で経験を共有してください！