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

By 佐川 直弘 | Naohiro Sagawa (運営者)

※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　

1. はじめに

MySQLは多くのWebアプリケーションやデータ管理システムで利用されているリレーショナルデータベースです。データの検索速度を向上させるために「インデックス」という仕組みが存在します。しかし、適切に管理しないと、逆にパフォーマンスが低下することもあります。

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

データベースにおけるインデックスは、書籍の索引のようなものです。適切に設計されたインデックスは、検索クエリの実行速度を向上させます。しかし、以下のような問題が発生することもあります。

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

この記事で学べること

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

これから、MySQLのインデックスに関する知識を体系的に学び、データベースのパフォーマンス向上に役立てていきましょう。

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

インデックスは、データベースのパフォーマンスを向上させるための重要な機能です。ここでは、インデックスの基本概念と種類、メリット・デメリットについて解説します。

インデックスの基本概念

データベースのインデックスは、特定のカラムの値を素早く検索できるようにするための仕組みです。例えば、大量のデータが格納されているテーブルで特定のレコードを検索するとき、インデックスがないと全レコードをスキャンする必要があります（フルテーブルスキャン）。インデックスを適用すると、データ検索が効率的になり、処理速度が大幅に向上します。

MySQLのインデックスの種類

MySQLにはいくつかの異なるタイプのインデックスがあり、それぞれ特定の用途に適しています。

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

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

1. UNIQUE インデックス

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

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

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

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

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

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

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

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

メリット

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

デメリット

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

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

MySQLでインデックスを適切に管理するためには、現在のテーブルにどのインデックスが設定されているかを確認することが重要です。本セクションでは、SHOW INDEX コマンド、INFORMATION_SCHEMA.STATISTICS、mysqlshow コマンドを使ってインデックスを確認する方法を解説します。

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

MySQLでは、SHOW INDEX コマンドを使用すると、特定のテーブルに設定されているインデックスの一覧を取得できます。このコマンドを利用すれば、インデックスの名前、適用されているカラム、ユニーク制約の有無 などの詳細情報を確認できます。

基本的な構文

SHOW INDEX FROM テーブル名;

実行例

例えば、users テーブルに設定されているインデックスを確認する場合、以下のSQLを実行します。

SHOW INDEX FROM users;

結果の例

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

MySQLのシステムテーブル INFORMATION_SCHEMA.STATISTICS を利用すると、SHOW INDEX コマンドと同じ情報をより柔軟に取得できます。

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

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環境での確認）

MySQLのコマンドラインツールを使用してインデックス情報を取得することも可能です。特に MySQLサーバーにSSH接続して作業する場合 に便利です。

コマンドの実行方法

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

実行例

mysqlshow -u root -p my_database users

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

SHOW INDEX コマンドや INFORMATION_SCHEMA.STATISTICS を実行してもインデックスが表示されない場合は、テーブルに適切なインデックスが設定されていない可能性があります。その場合、必要に応じてインデックスを作成することで、検索パフォーマンスを向上させることができます。

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

CREATE INDEX idx_column ON users (email);

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

ALTER TABLE users ADD PRIMARY KEY (id);

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

ALTER TABLE users DROP INDEX idx_column;

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

MySQLでインデックスが正しく機能しているかを確認することは、データベースのパフォーマンス最適化において重要なステップです。本セクションでは、EXPLAINコマンドとパフォーマンススキーマ（Performance Schema） を活用し、クエリがどのインデックスを使用しているかを確認する方法を解説します。

EXPLAIN を使ったクエリ分析

EXPLAIN コマンドは、指定したSQLクエリがどのように実行されるのかを可視化するために使用されます。使用されるインデックス、検索方法、実行計画 などを分析できるため、インデックスが適切に機能しているかを確認できます。

基本構文

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

実行例

例えば、users テーブルで email カラムを条件に検索する場合：

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

実行結果の例

ポイント

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

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

MySQLの performance_schema を使用すると、クエリ実行時にどのインデックスがどの程度使用されているかを詳細に分析できます。

クエリの実行統計を取得

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';

→ LOWER(email) により、email カラムのインデックスが無視される。

✅ 修正後（関数を使用しない）

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. インデックスの管理

MySQLのインデックスは、データ検索のパフォーマンスを向上させる重要な要素ですが、適切に管理しないと逆にデータベースのパフォーマンスを低下させる ことがあります。本セクションでは、インデックスの作成、削除、不要なインデックスの特定と管理について詳しく解説します。

インデックスの作成

適切なインデックスを作成することで、データ検索の高速化が可能になります。MySQLでは CREATE INDEX または ALTER TABLE を使用してインデックスを追加できます。

基本構文

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

実行例

users テーブルの email カラムにインデックスを追加する場合：

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 インデックス名;

実行例

例えば、idx_email というインデックスを削除する場合：

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 を実行して統計情報を更新するのも推奨されます。

ANALYZE TABLE users;

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

インデックスを適切に管理することで、MySQLのクエリパフォーマンスを大幅に向上させることができます。しかし、単にインデックスを作成するだけではなく、適切な設計・管理・監視 を行うことで、最適なパフォーマンスを維持することが可能です。

適切なインデックス設計

MySQLのインデックスは適切に設計することで、検索速度を大幅に向上させます。

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

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

スロークエリログを活用

スロークエリログ（Slow Query Log）を利用すると、実行時間が長いクエリを特定し、どのインデックスが適用されていないかを分析できます。

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

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（よくある質問）

MySQLのインデックスは、データベースのパフォーマンスを向上させる重要な仕組みですが、適切に管理しないと逆効果になることもあります。本セクションでは、MySQLのインデックスに関してよくある質問（FAQ）とその回答をまとめました。

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

A. 必ずしもそうとは限りません。

インデックスは検索クエリの速度を向上させるためのものですが、増やしすぎると逆にデータベースのパフォーマンスを低下させる可能性があります。

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

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

A. 以下のようなカラムにインデックスを適用するのが効果的です。

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

A. 一部のインデックスは自動で作成されますが、手動で追加する必要がある場合もあります。

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

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のインデックスの基本から、確認方法、管理、最適化、よくある質問までを包括的に解説しました。ここでは、各セクションの要点を振り返り、MySQLのインデックス管理を最適化するためのベストプラクティス をまとめます。

記事の要点の振り返り

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 を実行し、削除や更新で生じた断片化を解消する。

今後のステップ

✅ 実践チェックリスト
✅ SHOW INDEX で現在のインデックスを確認したか？
✅ スロークエリログを有効化し、遅いクエリを特定したか？
✅ EXPLAIN でクエリの実行計画を分析したか？
✅ 不要なインデックスを削除し、適切なインデックスを作成したか？
✅ ANALYZE TABLE を実行し、統計情報を更新したか？

最終まとめ

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

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

この知識を活用して、より高速で効率的なデータベース運用を目指してください。💡🚀