ストレージエンジンとInnoDB — クラスタ化インデックスの仕組み
この章の目次開く
文字コードと照合順序までで、テーブルへ保存する値と文字列の扱いを決められるようになりました。SHOW CREATE TABLE を見ると、定義の末尾にはもう1つ重要な指定があります。
ENGINE=InnoDBSELECT や JOIN の書き方は SQLとデータベースの基礎 で学んだ内容がそのまま通用します。この章では、MySQLがデータを実際に保存する仕組みに絞ります。
ストレージエンジンとは
MySQLは、SQLを解析するサーバー部分と、データを保存・検索するストレージエンジンを分けています。
ストレージエンジンは、トランザクション・ロック・インデックス・ディスク保存を担当するMySQL内部の実装です。利用できるエンジンは SHOW ENGINES で確認できます。
SHOW ENGINES;代表的なものだけ整理します。
| エンジン | 主な用途 |
|---|---|
InnoDB | 通常の業務テーブル。トランザクション・行ロック・外部キーに対応 |
MEMORY | メモリ上の一時的なデータ。サーバー停止で消える |
MyISAM | 古い非トランザクション型エンジン。新規採用は通常しない |
MySQL 8.4の既定は InnoDB です。
SELECT @@default_storage_engine;+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
テーブルごとに明示することもできます。
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
total DECIMAL(10, 2) NOT NULL
) ENGINE=InnoDB;
学習者既定がInnoDBなら、ストレージエンジンを意識しなくてもよさそうですが……。
普段はそれで動きます。ただし、主キーやインデックスの設計がディスク上の保存方法へ直結するため、仕組みを知っているかどうかで性能設計が変わります。
InnoDBが既定である理由
Webアプリのデータ保存に必要な機能を、InnoDBは一通り備えています。
| 機能 | 何を守るか |
|---|---|
| トランザクション | 複数の更新をまとめて確定・取り消しできる |
| 行レベルロック | 別の行への更新をできるだけ同時進行させる |
| MVCC | 更新中でも、通常のSELECTが一貫したスナップショットを読める |
| 外部キー | 関連するテーブル間の参照整合性を守る |
| クラッシュリカバリ | 障害後にログを使って整合した状態へ復旧する |
これらは独立した機能ではありません。トランザクション中の変更をログへ記録し、MVCCで読み取りと書き込みの競合を減らし、必要な範囲だけロックすることで、複数ユーザーからの同時アクセスを処理します。
トランザクションとロックの具体的な挙動は トランザクションとロック で扱います。
主キーが行データの置き場所になる
InnoDBの各テーブルには、クラスタ化インデックスが1つあります。通常は主キーがそのままクラスタ化インデックスになります。
一般的な「索引」は、索引から別の場所にある行を探すイメージです。InnoDBの主キーは違い、B-treeの末端に行データそのものが格納されます。
主キー検索が速いのは、索引をたどった先に目的の行がそのままあるためです。
SELECT * FROM orders WHERE id = 1000;主キーがない場合
InnoDBは次の順でクラスタ化インデックスを決めます。
- 明示された
PRIMARY KEY - 全列が
NOT NULLの最初のUNIQUEインデックス - どちらもなければ、内部の6バイト行IDによる
GEN_CLUST_INDEX
内部行IDはSQLから安定して参照できません。主キーなしでもテーブルは作れますが、アプリケーションが行を一意に扱いにくく、レプリケーションや更新の調査も難しくなります。
セカンダリインデックスは主キーを持つ
主キー以外のインデックスを、InnoDBではセカンダリインデックスと呼びます。
CREATE INDEX idx_orders_created_at ON orders (created_at);このインデックスの末端には、created_at と対応する主キー値が保存されます。主キー以外の列まで必要なら、取得は2段階です。
- セカンダリインデックスで主キーを見つける
- 主キーのクラスタ化インデックスをたどって行を読む
この2段階目を、一般にテーブルルックアップや主キーへのルックアップと呼びます。
先生すべてのセカンダリインデックスに主キーが付いてくる。だから主キーが長いと、インデックスを増やすたびに影響が広がるんだ。
主キーは短く・安定させる
クラスタ化インデックスの仕組みから、主キーの判断基準が導けます。
| 性質 | 理由 |
|---|---|
| 短い | すべてのセカンダリインデックスに含まれるため |
| 一意 | 行を特定するため |
NOT NULL | 主キーの必須条件 |
| 変更しない | 値の変更が行の移動と全インデックスの更新につながるため |
| できるだけ単調に増える | ランダムな位置への挿入とページ分割を減らしやすいため |
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEYこの形がよく使われるのは、短く、安定し、末尾方向へ増えるからです。
UUIDを主キーにする設計が常に間違いというわけではありません。複数システムで事前採番できる利点があります。ただし、36文字の文字列表現をそのまま主キーにすると、クラスタ化インデックスと全セカンダリインデックスが大きくなります。採用するなら BINARY(16) などの保存形式やUUIDの生成方式まで含めて検討します。
テーブルのエンジンを確認・変更する
現在のエンジンは SHOW TABLE STATUS または SHOW CREATE TABLE で確認できます。
SHOW TABLE STATUS LIKE 'orders'\G
SHOW CREATE TABLE orders\G既存テーブルをInnoDBへ変える構文は次の通りです。
ALTER TABLE legacy_orders ENGINE=InnoDB;これは単なる設定値の書き換えではなく、テーブルをInnoDB形式で作り直す処理です。大きなテーブルでは実行時間・ディスク空き容量・ロック・外部キーの互換性を確認してから行います。
よくあるハマりどころ
PRIMARY KEYを付けずにAUTO_INCREMENTだけ使う
AUTO_INCREMENT 列にはインデックスが必要ですが、それだけで良い主キー設計になるとは限りません。行を一意に参照する目的を明確にし、通常は PRIMARY KEY と組み合わせます。
メールアドレスを主キーにする
メールアドレスは長く、変更される可能性があります。主キーを更新するとクラスタ化インデックス上の行とセカンダリインデックスも影響を受けます。メールには UNIQUE を付け、主キーは別の短いIDにする設計が扱いやすくなります。
行ロックだから1行しかロックしないと思う
インデックスがなければ広い範囲を走査し、多数のインデックスレコードをロックすることがあります。ロック範囲を考えるときも、実行計画とインデックスを確認します。
ENGINEの変更を小さな設定変更だと思う
エンジン変換はデータ全体の再構築になり得ます。本番で直接試さず、同程度のデータ量で時間と容量を測ります。
ちゃんと使うためのポイント
- MySQLは保存処理をストレージエンジンへ委ねる
- 通常の業務テーブルではInnoDBを使う
- InnoDBはトランザクション、行ロック、MVCC、外部キー、クラッシュリカバリに対応する
- 主キーのB-tree末端に行データが保存される
- 主キーがなければUNIQUE NOT NULL、さらに無ければ隠し行IDが使われる
- セカンダリインデックスには主キー値も保存される
- 主キーは短く、変更せず、可能なら単調に増える値にする
- エンジン変換はテーブル再構築として計画する
インデックスとEXPLAINでは、B-treeの列順をどう決め、MySQLが実際にどのインデックスを選んだかを確認する方法へ進みます。
参考リンク
- MySQL 8.4 Reference Manual — Introduction to InnoDB
- MySQL 8.4 Reference Manual — Clustered and Secondary Indexes
- MySQL 8.4 Reference Manual — The InnoDB Storage Engine