MySQLのインデックス確認・管理・最適化完全ガイド

1. はじめに

なぜインデックスの確認が重要なのか？

• インデックスが適切に作成されていない → 検索が遅くなる原因になる
• 不要なインデックスが存在する → 更新・挿入の速度低下につながる
• どのインデックスが使われているのか分からない → 不要なインデックスを削除する判断ができない

この記事で学べること

• MySQLのインデックスの基本的な仕組み
• 現在のインデックスの確認方法（SQLコマンドを使用）
• インデックスの管理と最適化の方法
• インデックスの使用状況を分析するテクニック

2. MySQLのインデックスとは？

インデックスの基本概念

MySQLのインデックスの種類

1. PRIMARY KEY（主キーインデックス）

• 各テーブルに1つだけ設定できる
• テーブルの一意性を保証する
• クラスタードインデックスとして機能する

1. UNIQUE インデックス

• 指定したカラムの値が重複しないように制約する
• NULL値は許容される（複数のNULLはOK）

1. INDEX（一般的なインデックス）

• 検索を高速化するために使用される
• 重複データがあっても問題ない

1. FULLTEXT インデックス（テキスト検索用）

• テキスト検索を効率化する
• MATCH ... AGAINST 構文と組み合わせて使用する

1. SPATIAL インデックス（地理情報用）

• 空間データ（GISデータ）向け

インデックスのメリットとデメリット

メリット

• クエリの検索速度が向上する
• JOIN処理やWHERE句のパフォーマンスが改善される
• 特定のデータ取得が効率的になる

デメリット

• インデックスが増えるとデータの追加・更新・削除が遅くなる
• ディスクスペースを消費する
• インデックスが適切でないと逆にパフォーマンスが低下する

3. MySQLのインデックスを確認する方法

SHOW INDEX コマンド（基本的な確認方法）

基本的な構文

SHOW INDEX FROM テーブル名;

実行例

SHOW INDEX FROM users;

結果の例

INFORMATION_SCHEMA.STATISTICS を活用（詳細な情報取得）

特定のテーブルのインデックスを確認

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

データベース全体のインデックスを取得

SELECT TABLE_NAME, COLUMN_NAME, INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name';

mysqlshow コマンド（CLI環境での確認）

コマンドの実行方法

mysqlshow -u ユーザー名 -p パスワード データベース名 テーブル名

実行例

mysqlshow -u root -p my_database users

インデックスがない場合の対処法

新しくインデックスを作成する

CREATE INDEX idx_column ON users (email);

主キー（PRIMARY KEY）を設定する

ALTER TABLE users ADD PRIMARY KEY (id);

不要なインデックスを削除する

ALTER TABLE users DROP INDEX idx_column;

4. インデックスの使用状況を確認する方法

EXPLAIN を使ったクエリ分析

基本構文

EXPLAIN SELECT * FROM テーブル名 WHERE 条件;

実行例

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

実行結果の例

ポイント

• type = ALL の場合は フルテーブルスキャン を実施しているため、インデックスを適用する必要がある
• key にインデックス名が表示されていれば、そのインデックスが使用されている
• rows の値が大きすぎる場合、クエリの最適化が必要

パフォーマンススキーマの活用

クエリの実行統計を取得

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

特定のテーブルのインデックス使用状況を確認

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

インデックスが使われていない場合の対処法

1. クエリの見直し

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

SELECT * FROM users WHERE email = 'test@example.com';

2. インデックスの再作成

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. 統計情報の更新

ANALYZE TABLE users;

5. インデックスの管理

インデックスの作成

基本構文

CREATE INDEX インデックス名 ON テーブル名(カラム名);

実行例

CREATE INDEX idx_email ON users(email);

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

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

ユニークインデックス

CREATE UNIQUE INDEX idx_unique_email ON users(email);

主キー（PRIMARY KEY）の設定

ALTER TABLE users ADD PRIMARY KEY (id);

インデックスの削除

基本構文

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

実行例

ALTER TABLE users DROP INDEX idx_email;

不要なインデックスの特定と削除

未使用インデックスの確認

SELECT * FROM sys.schema_unused_indexes;

インデックスの影響を確認

SHOW TABLE STATUS LIKE 'users';

不要なインデックスを削除

ALTER TABLE users DROP INDEX idx_unused;

ANALYZE TABLE users;

6. インデックスの最適化（パフォーマンス改善）

適切なインデックス設計

インデックスを適用すべきケース

インデックスを適用しない方がいいケース

スロークエリログを活用

スロークエリログの有効化

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- 2秒以上かかるクエリを記録

スロークエリログの確認

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

スロークエリの分析

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

インデックス適用例

CREATE INDEX idx_last_login ON users(last_login);

統計情報の更新（ANALYZE & OPTIMIZE）

ANALYZE TABLE（統計情報の更新）

ANALYZE TABLE users;

OPTIMIZE TABLE（断片化の解消）

OPTIMIZE TABLE users;

7. インデックスに関するFAQ（よくある質問）

インデックスを増やせば増やすほど、検索速度は向上するの？

• 書き込み（INSERT、UPDATE、DELETE）の負荷が増える
• インデックスが適用されるかどうかはクエリ次第
• 不要なインデックスがメモリを圧迫する

どのカラムにインデックスを設定するべき？

インデックスは自動で作成されるの？

自動で作成されるインデックス

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY インデックス
  email VARCHAR(255) UNIQUE -- UNIQUE 制約によりインデックスが自動作成
);

手動で作成するインデックス

CREATE INDEX idx_email ON users(email);

インデックスの種類（B-Tree, ハッシュ, フルテキスト）はどう使い分けるべき？

インデックスのサイズを確認するには？

SHOW TABLE STATUS LIKE 'users';

インデックスが使われているかどうかを確認する方法は？

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

インデックスを削除するべきケースは？

不要なインデックスを削除

ALTER TABLE users DROP INDEX idx_unused;

8. まとめ

記事の要点の振り返り

MySQLのインデックスとは？

• インデックスはデータ検索を高速化するための仕組み。
• B-Tree, ハッシュ, FULLTEXT インデックス などの種類がある。
• WHERE句、JOIN、ORDER BYで使用されるカラムに適用すると効果的。

MySQLのインデックスを確認する方法

• SHOW INDEX コマンドでインデックス一覧を確認。
• INFORMATION_SCHEMA.STATISTICS で詳細な情報を取得可能。

インデックスの使用状況を確認する方法

• EXPLAIN を使って、クエリがどのインデックスを使用しているかを確認。
• パフォーマンススキーマ（Performance Schema）を利用して、使用頻度を分析可能。

インデックスの管理

• CREATE INDEX で適切なカラムにインデックスを設定。
• ALTER TABLE DROP INDEX で不要なインデックスを削除。
• SHOW TABLE STATUS でインデックスサイズを確認し、最適化を行う。

インデックスの最適化（パフォーマンス改善）

• 適用すべきカラム：WHERE, JOIN, ORDER BY でよく使うカラム。
• スロークエリログを活用し、遅いクエリを特定し最適化する。
• ANALYZE TABLE と OPTIMIZE TABLE で統計情報を更新。

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

1. インデックスを適用する前に、どのクエリがボトルネックになっているのかを特定する。
2. 適切なインデックスを選択する

• 単一カラムのインデックスと複合インデックスを適切に使い分ける。
• ユニーク制約が必要なカラムには UNIQUE INDEX を使用する。

1. 定期的に不要なインデックスを削除する

• SHOW INDEX や schema_unused_indexes で未使用インデックスを特定。

1. 統計情報の更新を定期的に行う

• ANALYZE TABLE で統計情報を更新
• OPTIMIZE TABLE を実行し、削除や更新で生じた断片化を解消する。

今後のステップ

最終まとめ

• MySQLのインデックスは適切に管理することで、検索性能を大幅に向上させる。
• スロークエリログやEXPLAINを活用し、インデックスの効果を分析しながら最適化を行う。
• 統計情報の更新やテーブルの最適化を定期的に実施し、データベースのパフォーマンスを維持する。

これで、MySQLのインデックス管理に関する一連の記事は完結です。