InnoDBで「Using Index」とMVCCはどう両立しているのか？
TL;DR
- MySQL（InnoDB）のオプティマイザは、セカンダリインデックスだけでクエリを解決できる場合に
Using indexを選択することがあります。 - しかし内部的には、MVCC（マルチバージョン同時実行制御）を維持するためにクラスタードインデックスも参照することができる仕組みがあります。
- セカンダリインデックスは即時更新される（UPDATE/DELETEなどの命令がコミット前でも反映される）が、実際には「delete mark（削除マーク）」を使うことで物理削除を遅延し、別トランザクションが読み取り可能なデータを正しく取得できるようになっています。
前提
| 項目 | 値 |
|---|---|
| データベース | MySQL |
| ストレージエンジン | InnoDB |
| isolation level | REPEATABLE READ |
- クラスタードインデックス: InnoDBにおける実データが格納された主キーのB+木構造を指し、各レコードにはトランザクションIDや以前のバージョンを参照する情報（ロールバックポインタ）などが隠しカラムとして保持されています。
- セカンダリインデックス: クラスタードインデックス以外の補助的なインデックス。レコードの実体は持たず、クラスタードインデックス上の主キーを参照する形になっています。
例： Using index が出るクエリ
まずは単純なテーブルを考えてみます。
CREATE TABLE sample_table (
id SERIAL PRIMARY KEY,
name VARCHAR(32),
INDEX idx_name (name)
);
このテーブルで以下のようなクエリを実行したとします。
SELECT name FROM sample_table WHERE name = "Alice";
EXPLAIN で実行計画を確認すると、たとえば次のように表示されるケースがあります。
EXPLAIN SELECT name FROM sample_table WHERE name = "Alice";
-- 結果の例: "Using index"
この結果からは、オプティマイザが「このクエリはセカンダリインデックス idx_name だけを使って完結できる」と判断していることがわかります。「クラスタードインデックスにアクセスしなくても良い」と言っているわけですね。
どうして速いのか？
セカンダリインデックスだけで完結するクエリのメリットは、
- 必要なカラムのみを直接インデックスのB+木から取得できる
- レコード自体が軽量で、メモリ上に載る可能性が高い
- クラスタードインデックスを辿る追加のIOが不要
といった理由から、高速にクエリを処理できる点です。
MVCCとの整合性はどう保っているのか？
しかし、InnoDBにはMVCC（マルチバージョン同時実行制御）があり、「あるトランザクションの読み取りは他のトランザクションによる変更の影響を受けない」 というルールがデフォルトで（REPEATABLE READレベルで）保証されています。
この仕組みは以下のように動きます。
- クラスタードインデックスの実データ（リーフノード） には、各レコードに対するトランザクションIDやロールバックポインタ（過去バージョン）などの隠しカラムが記録されている。
- あるトランザクションがレコードを参照するとき、自分のトランザクションIDとレコードが持っているトランザクションIDを比較し、「見える（コミット済みの）バージョン」を選んでデータを返す。
このように 「真のバージョン管理」や「コミット済み・未コミットの判定」はクラスタードインデックスで行われるため、セカンダリインデックスに対してクエリを投げても、最終的にはクラスタードインデックス側の隠しカラム（トランザクションIDなど）を参照しているわけです。
「Using index」は完全に嘘なのか？
Noです。
確かに上記の通り、Using index が表示されていても、実際には必要に応じてクラスタードインデックスを参照する可能性がある、というのがInnoDBの実態です。「セカンダリインデックスだけで完結」のはずが、MVCCのチェック段階で真に正確なバージョン情報を得るには、クラスタードインデックスを見に行く場合があります。
ただし、通常「毎回主キー値でクラスタードインデックスを辿っている」わけではありません。基本的にはセカンダリインデックスに格納されているメタデータだけで最低限のバージョン管理はできるようになっています。
セカンダリインデックスの即時更新と「delete mark」
では、実際のトランザクション競合シナリオで、セカンダリインデックスはどのように整合性を保っているのでしょうか。よくある例を考えます。
-
トランザクションA (未コミット) で
name="Alice"の行をDELETEする（またはUPDATEする）。 -
トランザクションB が同時に
name="Alice"を検索する。
このとき、InnoDBはコミット前であってもセカンダリインデックスを即座に更新します。つまり、セカンダリインデックスから「Alice」を物理的に削除または変更してしまうと、一見「BがAliceを見つけられなくなるのでは？」という疑問がわきます。
ここで重要なのが、セカンダリインデックスでは物理削除をすぐには行わず「delete mark（削除マーク）」をつけるという動作です。実際には下記のように制御しています。
-
DELETE/UPDATE命令が実行されると、該当するセカンダリインデックスのエントリに「削除（あるいは更新）予定」とわかるマークをつける。 - コミットが成功すると、物理的な削除が将来実行されることが確定する
- 別のトランザクションが同じセカンダリインデックスを読みに来たときは、マークを見て「このエントリは未コミットの削除予定かどうか」や「自分から見てコミット済みかどうか」を判定する。必要に応じて、さらにクラスタードインデックスにアクセスしてバージョンの可視性をチェックし、正しい結果を返す。
つまり、「delete mark」があることで、コミットされていない変更からデータが完全に消えてしまう事態を防ぎつつ、MVCCの一貫性を確保しています。これにより、Using index が選ばれるケースでも、必要とあればクラスタードインデックスにアクセスし、実際のバージョン情報を参照しているのです。
参照: deleted flagの定義
まとめ
- MySQLのオプティマイザは、セカンダリインデックスだけでクエリが解決可能と判断した場合に
Using indexを選択しますが、データの可視性・バージョン管理はクラスタードインデックス側で行われるため、最終的にはクラスタードインデックスを確認しています。 - InnoDBはMVCCを実現するために、クラスタードインデックスの各レコードにトランザクションIDやロールバック情報を保持し、読み取り時に「どのバージョンが見えるか」を制御します。
- セカンダリインデックスは「即時更新」される一方、未コミットの削除や更新は「delete mark」を使って物理削除を遅延し、ほかのトランザクションから正しいレコードが見えるようにしています。
- 結果として、REPEATABLE READレベルの分離性を維持しながらも、セカンダリインデックスのみで完結するクエリ（
Using index）の高速性を最大限活用できます。
InnoDBのMVCCやセカンダリインデックスの振る舞いは一見複雑ですが、最終的には 「クラスタードインデックスがデータのソース・オブ・トゥルース」 であり、セカンダリインデックス側はクラスタードインデックスと整合性を保つための仕組みが組み込まれている、という点を押さえると理解しやすいでしょう。
以上が、Using index で実際にクラスタードインデックスを参照しつつ、トランザクション分離レベルを守りながら高速性を発揮している理由と、セカンダリインデックスの即時更新を可能にしている仕組みの概要でした。
さいごに
Progateでは一緒に働く仲間を積極的に募集中です！
爆速で、知の高速道路を作っていきましょう！！
Discussion