InnoDB
ストレージエンジンの一つ
MySQL5.5以降のデフォルトのストレージエンジン
InnoDBの構成
InnoDBのメモリ、ファイル構成の概要
Buffer_pool（バッファプール） :
テーブルとインデックスのキャッシュ領域
log_buffer（ログバッファ）:
Redo logは一旦この領域に書き込まれ、COMMITのタイミングでDiskフラッシュされる
Redo log （InnoDBログ）:
Redo logファイルにシーケンシャルに変更内容を書き込むことで更新速度を向上させる。
table space:
InnoDB テーブルおよび関連付けられたインデックスのデータを保持するデータファイル
innodb_file_per_tableが有効な場合、テーブル毎に作成される
System table space:
InnoDBデータディクショナリ、Undo log、変更バッファー、二重書き込みバッファーの
ストレージ領域のメタデータを含むデータファイル
ACID準拠のトランザクション機能
InnoDBはACID準拠のトランザクション機能をもつ
トランザクション
コミットまたはロールバックされる作業単位
例)
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT; -- ROLLBACK;
ACIDとは
トランザクション処理に求められる4つの特性
以下性質の頭文字をとったもの。
- 原子性 (Atomicity)
トランザクションに含まれるタスクが全て実行されるか、全く実行されないことを保証する - 一貫性 (Consistency)
トランザクション開始と終了時に整合性を満たすことを保証する - 分離性 (Isolation)
トランザクション中に行われる操作の過程が他のトランザクションから隠蔽される - 持続性 (Durability)
トランザクション操作の完了通知をユーザが受けた時点で、
その操作は永続的となり、結果が失われない
更新処理
ACIDを実現しつつ、現実的なパフォーマンスを得るため、
「WAL」（Write Ahead Log（ログ先行書き込み））で更新される。
doublewrite_buffer（二重書き込みバッファー）:
データをデータファイルに書き込む前にdoublewrite_bufferに書き込むことで信頼性を向上させる。
データ更新時の流れ
- 更新クエリ発行、更新内容はlog_bufferにも反映される
- COMMIT時にredo log(InnoDBログファイル)に更新情報を追記（シーケンシャルライト）
- COMMIT完了を通知
- 定期的にバッファプールのデータをデータファイル（テーブルスペース）にフラッシュ
- データ保護のため、同じ内容を2回書き込む。ダブルライトバッファ、本番ページ
クラッシュリカバリ
クラッシュ時は、起動時に以下プロセスでデータ復旧される
- Redoログの適用
- 未完了のトランザクションのロールバック（Undoログで？）
- 挿入バッファーのマージ
※変更対象のIndexがディスク上にない場合、Diskデータを読まずメモリ内で更新。更新内容をマージするらしい
ロック制御
トランザクションによって照会または変更されているデータを
他のトランザクションが見たり変更したりすることを防止する
InnoDBは基本行ロックでロックを取得する。
ロックモード
- 共有(S)ロック :
ロックを保持するトランザクションによる行の読み取りが許可 - 排他(X)ロック :
ロックを保持するトランザクションによる行の更新、削除が許可 - インテンションロック
テーブルロックのみ。
処理を行いたいテーブルに対し「ロックを取ることを宣言する」ロック - インテンション共有(IS)ロック ：
Sロックの前に対象テーブルにISロックを行う - インテンション排他(IX)ロック :
Xロックの前に対象テーブルにIXロックを行う
ロックの競合と互換の関係
| X | IX | S | IS | |
|---|---|---|---|---|
| X | 競合 | 競合 | 競合 | 競合 |
| IX | 競合 | 互換 | 競合 | 互換 |
| S | 競合 | 競合 | 互換 | 互換 |
| IS | 競合 | 互換 | 互換 | 互換 |
T1: トランザクション１
T2: トランザクション２
r : 行
S : 共有ロック
X : 排他ロック
T1が行rにSロック
T2が行rにSロック -> OK
T2が行rにXロック -> Wait...
T1が行rにXロック
T2が行rにSロック -> Wait...
T2が行rにXロック -> Wait...
ロックの範囲
InnoDBではクラスタインデックス構造を採用
ロックはインデックスをもとに行われ、Index上を走査した行が対象となる
-
レコードロック（行ロック）
同じテーブルの他の行へ他のトランザクションがアクセス可能 -
ギャップロック
インデックス上のレコードとレコードの間をロック
(ファントムリードを防ぐ) -
ネクストキーロック
レコードロックとその手前のギャップロック
レコードロック（行ロック）
__9__
/ \
4 15
/\ / \
2 6 ★ 17
ギャップロック
__9__
/ \
4 15
/\ / \
2 6____12 17
↑
この間のこと
ネクストキーロック
__9__
/ \
4 15
/\ / \
2 6____★ 17
↑
この間と★のこと
トランザクション分離レベル
「待ち時間を減らすためどれだけデータの一貫性を犠牲にして良いか」を定めたもの
ANSI/ISO SQL標準で定められている分離レベル
下に行くほど一貫性が保たれる
- READ UNCOMMITTED ( 確定していないデータまで読み取る )
- READ COMMITTED ( 確定した最新データを常に読み取る )
- REPEATABLE READ ( 読み取り対象のデータを常に読み取る )
- SERIALIZABLE ( 直列化可能 )
分離レベルによって起こる現象
-
ダーティーリード :
別のトランザクションによって更新されたが、
まだコミットされていないデータを取得されてしまう -
ファジーリード/ノンリピータブル・リード :
同一トランザクション内の同一クエリで、取得タイミングにより、
異なる結果が返る
(その間にコミットしている別のトランザクションによって変更された) -
ファントムリード:
同一トランザクション内の同一クエリで、取得タイミングにより、
行が増えた結果が返る
(その間にコミットしている別のトランザクションによって挿入された)
分離レベルと起こる現象
| 分離レベル | ダーティリード | ファジーリード | ファントムリード |
|---|---|---|---|
| READ UNCOMMITED | 発生する | 発生する | 発生する |
| READ COMMITTED | 発生しない | 発生する | 発生する |
| REPEATABLE READ | 発生しない | 発生しない | 発生する (InnoDBでは発生しない) |
| SERIALIZABLE | 発生しない | 発生しない | 発生しない |
MVCC(マルチバージョン コンカレンシー コントロール)
共有(S)ロックを取得せずにSELECT可能な仕組み
他のトランザクションが更新中のデータをロックせず参照することができるため、並列度が向上する。
同じ行データに対し、複数のバージョンをUndoログに保持することで、
更新中でロックされたデータも読み取ることができる。
Undo log （ロールバックセグメント）:
トランザクションが変更中のデータの、変更前のデータを保持する領域。
別トランザクションは変更前のデータを読み込む
参考
ACID
トランザクション分離レベル
MySQLのロックについて dbstudy.info
InnoDBのREPEATABLE READにおけるLocking Readについての注意点 漢のコンピュータ道
大人のためのInnoDBテーブルとの正しい付き合い方。 漢のコンピュータ道