MySQLインデックス徹底解説：基本から設計・運用のベストプラクティス

はじめに

インデックスの基礎知識

インデックスの仕組み

1. B-Treeインデックス
B-Treeは、一般的なMySQLインデックスで使用されるデータ構造です。データが順序付けられた状態で格納され、範囲検索やソートに優れています。

使用例: PRIMARY KEY、UNIQUE、INDEX
特徴:

• 範囲検索 (BETWEEN, <, >) やソートに適している
• ツリー構造でデータを整理し、効率的に検索を実行

2. ハッシュインデックス
ハッシュ関数を使用してデータを検索するインデックスです。特定の値の検索には高速ですが、範囲検索はできません。

• 使用例: メモリストレージエンジン（MEMORY）での一部利用

3. 全文検索用インデックス（FULLTEXTインデックス）
全文検索に特化したインデックスです。自然言語検索をサポートし、大量のテキストデータを効率的に検索可能です。

• 使用例: 記事やブログの内容検索

4. 空間インデックス
地理空間データの検索を最適化するために使用されます。GIS（地理情報システム）アプリケーションで利用されます。

• 使用例: MyISAMストレージエンジンでの空間データ型（GEOMETRYなど）

インデックスの種類と特徴

1. 主キーインデックス (Primary Key Index)

• テーブルの一意性と整合性を保証します
• 自動的に作成されるクラスター化インデックスです

使用例

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));

2. ユニークインデックス (Unique Index)

• 重複を許さないインデックスです
• NULL値を複数許容可能です

使用例

CREATE UNIQUE INDEX unique_email ON users (email);

3. 通常インデックス (Index)

• 一般的なインデックスで検索速度を向上させます
• NULL値を許容し、重複も可能です

使用例

CREATE INDEX idx_name ON users (name);

4. 複合インデックス (Composite Index)

• 複数列を1つのインデックスとして設定します
• クエリの順序に応じて有効に動作します

使用例

CREATE INDEX idx_name_email ON users (name, email);

5. FULLTEXTインデックス

• 大量テキストの高速検索を実現します

使用例

CREATE FULLTEXT INDEX ft_content ON articles (content);

クエリへのインデックスの影響

1. インデックスを使用する条件

• WHERE句やJOIN条件に列を使用している場合
• ORDER BYやGROUP BYで列を参照している場合
• 範囲検索（BETWEENや<, >）などが含まれる場合

2. インデックスが無視される場合

• LIKEでワイルドカード（%）が先頭に来る場合
• 関数で列をラップした場合（例: LOWER(column)）
• 異なる文字セットやコラレーションを使用した場合

EXPLAINによるインデックスの確認

EXPLAINコマンドを使用して、インデックスが利用されているかどうかを確認します。

EXPLAIN SELECT * FROM users WHERE name = 'John';

以下はサンプル出力例です。

+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | name_index    | name | 62      | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

このクエリでは、usersテーブルのnameカラムにインデックスが設定されている場合、refタイプでインデックスを使用しています。
このような出力を基に、クエリの効率性を評価し、必要に応じてインデックスの追加やクエリの最適化を行えます。

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

1. 頻繁に検索される列にインデックスを設定

• WHERE句やJOIN句で多用される列を優先

2. 複合インデックスの順序を最適化

• クエリの順序に一致するように設計する

3. インデックスの数を制限

• 過剰なインデックス設定は挿入や更新のパフォーマンスに悪影響を与える

4. 全文検索にはFULLTEXTインデックスを活用

• 大量のテキストデータが対象の場合に有効

5. 適切なインデックスの削除

• 使用頻度が低い、または重複したインデックスを削除する

よくあるインデックス設計のミスと回避策

1. 不要なインデックスの乱用

• 回避策: EXPLAINやクエリ統計を利用して頻度を分析

2. 過剰な複合インデックス

• 回避策: 必要最小限の列に限定

3. 非効率なクエリ設計

• 回避策: 条件式や列の順序を見直し、関数の使用を最小化

インデックスのメンテナンス

1. インデックスの再構築
大量のデータ更新後、インデックスが断片化することあります。再構築を行い、性能を回復させます。

OPTIMIZE TABLE table_name;

2. 不要なインデックスの確認と削除
未使用のインデックスはperformance_schemaやsys.schema_unused_indexesを使って確認できます。

SELECT * FROM sys.schema_unused_indexes;

DROP INDEX index_name ON table_name;

まとめ

おわりに

1. MySQL公式ドキュメント - Indexes（インデックス）
MySQLインデックスの基本的な種類、使用方法、パフォーマンス向上のための最適化について記載されています。詳細はこちら:
MySQL Indexes​​

2. CREATE INDEXステートメント
新しいインデックスを作成する際の構文とオプションについて解説しています。インデックスの種類や使用できるストレージエンジンについても詳しく説明されています。詳細はこちら:
CREATE INDEX Statement​​

3. MySQLの最適化とインデックスの役割
インデックスを利用してクエリのパフォーマンスを向上させる方法や最適化のテクニックが記載されています。詳細はこちら:
Optimization and Indexes​​

4. EXPLAIN文の公式ガイド
EXPLAINコマンドを使用してクエリの実行計画を調べる方法や、各列の詳細な説明が含まれています。詳細はこちら:
EXPLAIN