データベースのパフォーマンス改善で最も効果的な手法がインデックスの最適化です。しかし「とりあえずインデックスを貼る」というアプローチでは、期待した効果が得られないばかりか、逆にパフォーマンスを悪化させることもあります。
この記事では、インデックスの仕組みから実践的な設計パターンまで、現場で使える知識を体系的に解説します。
インデックスとは何か
インデックスは、書籍の索引と同じ役割を果たします。本の中から特定の単語を探すとき、1ページ目から順番に読むのではなく、巻末の索引を使えば目的のページにすぐたどり着けます。データベースのインデックスも同様に、目的のデータへの「近道」を提供します。
MySQLのInnoDBエンジンでは、インデックスはB+Tree構造で実装されています。この構造により、数百万件のデータからでもO(log n)の計算量で目的のレコードを見つけられます。
インデックスが必要なケースを見極める
すべての列にインデックスを貼るのは間違いです。インデックスは読み取りを高速化する代わりに、書き込み時にインデックスの更新コストが発生します。以下の条件に該当する列がインデックスの候補になります。
WHERE句で頻繁に検索条件として使われる列、JOIN条件で使われる外部キー列、ORDER BYやGROUP BYで使われる列が主な対象です。一方、更新頻度が極めて高い列や、カーディナリティ（値の種類）が低い列はインデックスの効果が薄くなります。
単一カラムインデックスの基本
最もシンプルなインデックスは、単一の列に対して作成するものです。
CREATE INDEX idx_users_email ON users(email);このインデックスにより、WHERE email = 'test@example.com'のようなクエリが高速化されます。EXPLAINで効果を確認しましょう。
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';結果のtype列がrefやconstになっていれば、インデックスが使われています。ALLのままであればフルテーブルスキャンが発生しており、インデックスが効いていません。
複合インデックスの設計原則
実際のアプリケーションでは、複数の条件を組み合わせたクエリが一般的です。このような場合、複合インデックス（Composite Index）が威力を発揮します。
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);複合インデックスで最も重要なのは列の順序です。この順序を決める指針が「左端プレフィックスルール」と「等価条件優先の原則」です。
左端プレフィックスルール
複合インデックス(A, B, C)は、以下のクエリで利用できます。
WHERE A = ?WHERE A = ? AND B = ?WHERE A = ? AND B = ? AND C = ?
しかし、以下のクエリではインデックスが効きません。
WHERE B = ?（Aがない）WHERE B = ? AND C = ?（Aがない）
インデックスは左端の列から順番に使われるため、途中の列だけを条件にしても効果がありません。
等価条件を先に、範囲条件を後に
<em>-- よくあるクエリパターン</em>
SELECT * FROM orders
WHERE user_id = 123
AND status = 'completed'
AND created_at > '2024-01-01';
<em>-- 最適なインデックス</em>
CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);複合インデックスの列順序を決めるもう一つの重要な原則があります。等価条件（=）で使われる列を先に、範囲条件（>、<、BETWEEN、LIKE 'prefix%'）で使われる列を後に配置します。
範囲条件の列より後ろの列は、インデックスのフィルタリングに使われません。そのため、範囲条件の列は最後に配置するのがベストプラクティスです。
カバリングインデックスでさらに高速化
通常、インデックスで目的のレコードを特定した後、実際のデータを取得するためにテーブル本体へアクセスします。この追加アクセスを「テーブルルックアップ」と呼びます。
カバリングインデックスは、SELECT句で取得したい列もすべてインデックスに含めることで、テーブルルックアップを不要にする手法です。
<em>-- このクエリに対して</em>
SELECT user_id, status, created_at FROM orders
WHERE user_id = 123 AND status = 'completed';
<em>-- カバリングインデックスを作成</em>
CREATE INDEX idx_orders_covering
ON orders(user_id, status, created_at);EXPLAINの結果でExtra列にUsing indexと表示されれば、カバリングインデックスが効いています。大量データの集計クエリなどで劇的な効果を発揮します。
インデックスが効かないパターンを知る
インデックスを貼っても効果がないケースを理解しておくことは、効果的なインデックスを設計する上で不可欠です。
関数や演算を使った条件
<em>-- インデックスが効かない</em>
WHERE DATE(created_at) = '2024-01-01'
WHERE YEAR(created_at) = 2024
WHERE price * 1.1 > 1000
<em>-- インデックスが効く書き方</em>
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE price > 909列に対して関数や演算を適用すると、インデックスのB+Tree構造を活用できなくなります。
暗黙の型変換
<em>-- user_idがINT型の場合、インデックスが効かない可能性</em>
WHERE user_id = '123'
<em>-- 正しい型で指定</em>
WHERE user_id = 123否定条件とNULL
<em>-- インデックスが効きにくい</em>
WHERE status != 'deleted'
WHERE status IS NULL
<em>-- 可能であれば肯定条件に書き換え</em>
WHERE status IN ('active', 'pending', 'completed')前方一致以外のLIKE
<em>-- インデックスが効く</em>
WHERE name LIKE 'tanaka%'
<em>-- インデックスが効かない</em>
WHERE name LIKE '%tanaka'
WHERE name LIKE '%tanaka%'実践的なインデックス設計の進め方
新しいインデックスを追加する際は、以下の手順で進めましょう。
まず、スロークエリログやAPMツールから問題のあるクエリを特定します。次に、EXPLAINでクエリの実行計画を確認し、フルテーブルスキャンやソートのボトルネックを把握します。
インデックス案を作成したら、本番相当のデータ量を持つ環境でテストします。インデックス追加前後で実行時間を比較し、効果を数値で確認しましょう。
<em>-- 実行時間の計測</em>
SET profiling = 1;
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';
SHOW PROFILES;不要なインデックスの削除も重要
インデックスは追加するだけでなく、定期的な棚卸しも必要です。使われていないインデックスは、書き込み性能の低下とストレージの無駄遣いにつながります。
MySQLではperformance_schemaを使って、インデックスの使用状況を確認できます。
SELECT
object_schema,
object_name,
index_name,
count_read,
count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database'
ORDER BY count_read + count_write;count_readが0のインデックスは、削除の候補として検討しましょう。
まとめ
インデックスの効果的な設計には、仕組みの理解と実際のクエリパターンの分析が不可欠です。闇雲にインデックスを追加するのではなく、EXPLAINで効果を確認しながら、必要最小限のインデックスで最大の効果を得ることを目指しましょう。
重要なポイントを振り返ると、複合インデックスでは列順序が決定的に重要であること、等価条件の列を先に・範囲条件の列を後に配置すること、カバリングインデックスでテーブルルックアップを回避できること、そしてインデックスが効かないパターンを避けることです。
パフォーマンス改善は継続的な取り組みです。定期的にスロークエリを分析し、インデックスを最適化していきましょう。
参考文献
書籍
1. 『MySQL運用・管理［実践］入門 安全かつ高速にデータを扱う内部構造・動作原理を学ぶ』 著者：yoku0825、坂井 恵、鶴長 鎮一、とみたまさひろ 他 出版社：技術評論社（2024年5月）
MySQLサーバーの稼働状況の確認方法といった基礎知識から、アカウントの権限の評価順序や認証プラグイン、正規化プロセスの基本、CRUDを支える仕組み、ロックの仕組みといった内部構造について解説。多くのクエリ実行計画を読み解くことで、オプティマイザの考え方がわかるようになります。
2. 『達人に学ぶDB設計徹底指南書 第2版』 著者：ミック 出版社：翔泳社（2024年8月）
論理設計の基本から、正規化、パフォーマンスなど、押さえておくべき基礎知識やポイントを幅広く体系的に解説。やってはいけないアンチパターン、注意すべきグレーノウハウも丁寧に解説されており、「なぜ」を理解して実務で自信を持って使える知識が身につきます。
3. 『MySQL徹底入門 第4版 MySQL 8.0対応』 著者：日本MySQLユーザ会 出版社：翔泳社（2020年7月）
MySQL 8.0に対応した全面改定版。グループレプリケーション機能、デフォルトでのUnicode対応、ウィンドウ関数、CTE（共通表形式）など、重要な機能について解説。運用・データ型・レプリケーション・文字コードなどの実務で役立つ内容もバランスよく解説されています。
公式ドキュメント
4. MySQL 8.0 Reference Manual – Optimization and Indexes https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html
MySQLの公式リファレンス。インデックスの追加はクエリを高速化する一方、INSERT・UPDATE・DELETE時にインデックスの更新コストが発生することが解説されています。
コメント