MySQLのカバリングインデックス完全ガイド：仕組みから設計まで
はじめに
MySQLのパフォーマンスチューニングにおいて、インデックスは不可欠な要素です。その中でも「カバリングインデックス」は、特定のクエリを劇的に高速化できる非常に強力な手法です。
この記事では、MySQL (InnoDB) のインデックスの基本的な仕組みから、カバリングインデックスがどのように機能し、どう設計すれば良いのかを具体例と共に詳しく解説します。
MySQL (InnoDB) のインデックスの基本
カバリングインデックスを理解するには、まずInnoDBストレージエンジンにおける「プライマリインデックス」と「セカンダリインデックス」の根本的な違いを知る必要があります。
1. プライマリインデックス (クラスタ化インデックス)
プライマリインデックスは、テーブルの主キー（PRIMARY KEY）に対して自動的に作成される特別なインデックスです。
- 実データと一体化: InnoDBでは、テーブルのデータそのものがプライマリインデックスの順序に従って物理的にディスクに格納されます。つまり、プライマリインデックスのリーフノード（末端）には、行の全データが格納されています。
- 常に1つだけ: その構造上、1つのテーブルに1つしか存在できません。
- 常にカバリングインデックス: プライマリインデックスを検索すれば、そこに行の全データがあるため、追加のデータアクセスは一切不要です。そのため、プライマリインデックスは常にカバリングインデックスとして機能します。
2. セカンダリインデックス (非クラスタ化インデックス)
主キー以外に、CREATE INDEXなどで任意に作成するインデックスがセカンダリインデックスです。
- プライマリキーへのポインタを持つ: セカンダリインデックスのリーフノードには、インデックスが設定されたカラムの値と、**その行に対応する「プライマリキーの値」**が格納されています。
-
ルックアップの発生: セカンダリインデックスを使って検索を行う場合、まずインデックスを辿って目的のプライマリキーを見つけます。しかし、クエリがインデックスに含まれていない他のカラム（例:
ageやemail）を要求している場合、次に見つけたプライマリキーを使ってプライマリインデックスを再度検索し、行の完全なデータを取得しにいきます。この追加の検索処理を「ブックマークルックアップ」または単に「ルックアップ」と呼びます。
この「ルックアップ」こそが、セカンダリインデックスを使った検索でパフォーマンスのボトルネックになり得る処理です。
カバリングインデックスとは？
カバリングインデックスとは、クエリが必要とする全てのカラムを内包することで、セカンダリインデックス検索時に発生する「ルックアップ」を不要にするインデックスのことです。
クエリが必要とする情報がすべてセカンダリインデックス内に収まっているため、MySQLはテーブル本体（プライマリインデックス）にアクセスすることなく、セカンダリインデックスを読むだけで処理を完結させることができます。これを「インデックスオンリースキャン」と呼び、EXPLAINの結果ではExtra列に Using index と表示されます。
具体例で理解する
usersテーブルを例に、カバリングインデックスの効果を見ていきましょう。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(100),
age INT
);
-- nameとemailに複合セカンダリインデックスを作成
CREATE INDEX idx_name_email ON users (name, email);
ケース1: カバリングインデックスが有効になるクエリ
-- nameで検索し、nameとemailを取得する
SELECT name, email FROM users WHERE name = 'Taro Yamada';
このクエリの実行計画は以下の通りです。
-
WHERE句で使うname、SELECT句で要求するnameとemailは、すべてidx_name_emailインデックスに含まれています。 - MySQLは
idx_name_emailをスキャンするだけで必要な情報をすべて得られるため、プライマリインデックスへのルックアップは発生しません。 - これがカバリングインデックスとして機能している状態であり、非常に高速です。
ケース2: カバリングインデックスにならないクエリ
-- nameで検索し、nameとageを取得する
SELECT name, age FROM users WHERE name = 'Taro Yamada';
このクエリではどうでしょうか。
-
WHERE句で使うnameはidx_name_emailインデックスに含まれているため、高速に目的の行のプライマリキーを見つけることができます。 - しかし、
SELECT句が要求するageカラムはインデックスに含まれていません。 - そのため、MySQLはステップ1で見つけたプライマリキーを使い、プライマリインデックスへのルックアップを行い、
ageの値を取得します。 - この場合、
idx_name_emailはカバリングインデックスとしては機能しません。
メリットと注意点
メリット
- I/O操作の劇的な削減: ルックアップのためのディスクI/Oがなくなるため、クエリが非常に高速になります。
- キャッシュ効率の向上: インデックスはテーブルデータ全体よりもサイズが小さいため、メモリにキャッシュされやすく、さらなる高速化が期待できます。
注意点
- インデックスサイズの増加: 多くのカラムをインデックスに含めると、その分ディスク容量を消費します。
-
書き込み性能の低下:
INSERTやUPDATE時、インデックスも更新する必要があるため、インデックスが大きくなるほど書き込みのオーバーヘッドが増加します。 -
保守性の低下: クエリの
SELECT句が変わると、カバリングインデックスが効かなくなる可能性があります。クエリの変更に合わせてインデックスの設計を見直す必要があります。
まとめ
- プライマリインデックスは行の全データを持つため、常にカバリングインデックスです。
- セカンダリインデックスは、クエリが必要とする全カラムを含む場合にカバリングインデックスとして機能し、プライマリインデックスへのルックアップを省略することで、パフォーマンスを大幅に向上させます。
カバリングインデックスは、特に読み取り処理が多いシステムにおいて絶大な効果を発揮する最適化手法です。メリットとデメリットをよく理解し、アプリケーションのクエリ特性に合わせて戦略的に設計することが重要です。
参考
Discussion