pikesaku’s blog

個人的な勉強メモです。記載内容について一切の責任は持ちません。

RDBMSの基本メモ

参考

プロになるための データベース技術入門 ～MySQLforWindows困ったときに役立つ開発・運用ガイド | 木村 明治 |本 | 通販 | Amazon

この本最高！

ポイントメモ

Innodbストレージエンジンの前提で記述する。

OS書き込み処理

処理 説明
同期I/O 書き込みAPIコールしAPIが完了したら制御が呼び出し元に戻る
非同期I/O 書き込みAPIコールしAPIが完了前に制御が呼び出し元に戻る
同期書き込み 書き込み要求がディスクに反映されるまで完了しない
MySQLではInnodbログとバイナリログの書き込みで利用
遅延書き込み 書き込み要求がキャッシュに反映されたら完了

トランザクション(ACID)対応

項目 和訳 説明
Atomicity 原子性 データ変更伴う一連のデータ操作が「全部成功」or「全部失敗」どちらかになることを保証する仕組み。COMMIT未完了の場合はROLLBACK
Consistency 一貫性 一連のデータ操作の前後で、各種整合性制約(ユニーク制約等)の状態を保つことを保証する仕組み。
Isolation 隔離性 一連のデータ操作が同時実行されたとき、処理が矛盾なく実行されることを保証する仕組み。select ～ for updateで行レベルロックし、該当行への後続操作をwaitさせる。MVCC(※1)でロック中の行に対してもselectのみなら実行可能。
Durability 持続性 COMMIT完了したデータの永続性を保証する仕組み。データ変更時、テーブルスペース(データ格納するファイル)への非同期IOに加え、WAL(Write Ahead Logging:ログ先行書き込み)をすることで、処理速度と持続性を両立。※2

※1
MultiVersion Concurrency Controlの略。
トランザクションによる書き込み処理が実行中に、他読み取りアクセスがあった場合、書き込み以前の状態(スナップショット)を処理結果として返す。

※2
テーブルスペースの更新はランダムIO。また同期I/O&非同期書き込みのため、ダーティページが発生し、持続性を実現できない。WALは、ログファイルに同期IO&同期書き込みで更新内容を追記(シーケンシャルIO)で記録するため、性能影響を軽減。また異常終了後の復旧時に、ログファイル内容をテーブルスペースに反映することで、データの永続性を保証する。

Innodbアーキテクチャの用語

No コンポーネント 説明
1 接続スレッド クライアント接続時に生成されるスレッド
2 クエリパーサ -
3 オプティマイザ -
4 クエリキャッシュ MySQL独自
5 Innodbバッファプール テーブルスペースのキャッシュ(メモリ)。非同期反映
6 テーブルスペース データが格納されるディスク領域
7 Innodbログバッファ Innodbログファイルへの書き込みデータを保持するメモリ領域
8 Innodbログファイル クラッシュリカバリ用の更新ログ
9 バイナリログキャッシュ バイナリログのキャッシュ
10 バイナリログ 更新ログ
11 バイナリログindex バイナリログ管理ファイル

※Innodbログバッファについて
MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.5.4 ログバッファ
DSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！

データINSERT時の動作

※バイナリログ有効時

①接続スレッド
②クエリパーサ、オプティマイザ処理
③クエリ実行
④バイナリログ(ディスク上)に書き込み(同期書き込み)
⑤Innodbログファイル(ディスク上)に書き込み(同期書き込み)
⑥Innodbバッファプール(メモリ上)に書き込み
⑦⑥のデータがテーブルスペースに非同期で反映

★コミット前の更新データも、Innodbログファイル・テーブルスペースに書き込まれる★
以下動作を確認。
①トランザクション開始しテーブルAにレコード追加
②Innodbログファイル・テーブルスペースのファイルが更新されたことを確認。※見た目では同時。
③①のトランザクションをコミット
④Innodbログファイルが更新された後に、テーブルスペースのファイルが更新されたことを確認。※1～2秒のラグ

バイナリログ有効時も確認する！

クラッシュリカバリ時の動作

「データINSERT時の動作」の⑤と⑦のデータを利用しREDO→UNDOする。
REDO・・・コミット済みでテーブルスペースに未反映データを適用
UNDO・・・未コミットデータのロールバック。

REDOログは、innodbログファイルにあり。
UNDOログは、テーブルスペースの中のロールバックセグメントにあり。

Innodbログファイルとバイナリログの違い

どちらも更新データが書き込まれるファイル。
これらはトランザクションサポートしてるRDBMSの一般的な以下機能に関係あり。

機能 MySQLの場合 Oracleの場合
ロールフォーワードリカバリ バイナリログ利用 REDOログ利用
レプリケーション バイナリログ利用 REDOログ利用
クラッシュリカバリ Innodbログファイル利用 REDOログ利用

※MySQLではInnodbログがクラッシュリカバリ専用。
※ロールフォワードリカバリは、バックアップしたテーブルスペースとバックアップ後のバイナリログでリカバリすること。

Innodbのインデックス実装

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.13.2 クラスタインデックスとセカンダリインデックス
MySQL で知っておきたいInnoDBのクラスタインデックスとセカンダリインデックス - Qiita
・全てのテーブルはクラスタインデックスを持つ
・主キー(PRIMARY KEY)が定義されたテーブルでは、主キーがクラスタインデックスとなる。
・主キー未定義のテーブルでは、NOT NULL の UNIQUE のインデックスがあれば、それをクラスタインデックスとする。
・主キー未定義でNOT NULL の UNIQUE のインデックスもないテーブルでは、内部的にAutoincrementなクラスタインデックスが生成される。
・上記のような内部的に自動生成されたクラスタインデックスはユーザーによる参照 & 利用は不可。
・クラスタインデックス以外はセカンダリインデックスとなる。
・クラスタインデックスの格納値はレコードのデータ
・セカンダリインデックスの格納値はクラスタインデックス(主キー)
→主キー以外で検索すると、セカンダリインデックス参照→クラスタインデックス参照の流れとなり時間がかかる。主キーで検索すべし。

トランザクションの分離レベル

トランザクションは複数のSQLで構成された一連の処理。
全て実行(COMMIT) or 全て実行されない(ROLLBACK)のどちらかになる。
分離レベルは、読み込みを含むトランザクション中に別トランザクションで更新が行われた場合、最初のトランザクションでどの時点のデータが参照されるか？を決める。
読み込み含むトランザクション実行時に、分離レベルを指定できる。
分離レベルはRDBMSの概念として一般的に種類が定義されている。
MySQLの分離レベル対応状況は以下の通り。◎はデフォルト動作の意味。
No1→5の順で分離度合が強くなる。

No 分離レベル 対応状況 参照データ 補足
1 リードアンコミッテッド 〇 クエリ実行時点の非コミットデータ(正しさが保証されない) MVCC登場前に利用された機能でOracle・PosgreSQL・Firebirdでは未サポート。
MVCC登場前は非コミットデータにアクセスしてロックによりブロックされるより、不正確なデータでも参照したいニーズがあった為、利用された。
MVCC利用により読み取りはブロックされなくなった為、必要性は低下した。
2 リードコミッテッド 〇 クエリ実行時点でコミットされたデータ 同一トランザクション内で同クエリを2回実行する場合、結果が異なる可能性あり。
3 リピータブルリード ◎ 同一トランザクション内で同クエリを2回実行する前提
1回目はリードコミッテッドと同じ。
2回目は1回目と同じデータが参照される。		 クエリの間に別トランザクションで更新されても、同じデータが参照される。
4 シリアライザブル 〇 同一トランザクション内で同クエリを2回実行する前提
リピータブルリードと同じ		 クエリに合致するレコードがクエリ間で別トランザクションにより挿入された場合に、同一データ(挿入なし)を参照させる機能。
5 一貫性のあるスナップショット 〇 トランザクション開始時にコミットされてるデータ リピータブルリードは初回クエリ実行時のデータが参照される。
この場合、複数テーブルを参照する場合は整合性が合わない可能性あり。
テーブルAに対しては、初回クエリ実行時のデータが参照され整合性確保されるが、テーブルBも参照する場合、テーブルA初回参照時のテーブルBのデータが参照される保証がない。
「一貫性のあるスナップショット」を指定することでトランザクション開始時のテーブルA・Bのコミット済みのデータが参照される。

分離レベルの違いにより発生する事象

以下いずれも、同一トランザクション内で同一の参照クエリを複数回以上実行する場合の事象。
※参照クエリ1回、または更新クエリの場合は該当せず。

事象 説明
ファジーリード
※ノンリピータブルリード		 2つの参照クエリが実行される間に別トランザクションで更新・削除された場合、クエリ間で結果が異なってしまう。
避けたい場合は、リピータブルリードにする。
ファントムリード 2つの参照クエリが実行される間に別トランザクションで参照クエリ条件に合致するレコードが挿入された場合、クエリ間で結果が異なってしまう。
避けたい場合は、シリアライザブルにする。
MySQLはリピータブルリードでも対策済み(ネクストキーロック)

Innodbのトランザクションの特性

①更新と読込が互いにブロックしない。読込と読込も。
②読込は分離レベルにより内容変わる
③更新時は、行単位ロックを取得する。分離レベルや設定によりロック範囲が変わる。
④更新と更新は後の処理がブロックされる。一定時間後にタイムアウト。
⑤デッドロック時は影響少ない方が、ロールバックされる。

トランザクション実現の仕組み

行単位ロックとMVCCで実現

行単位ロックのポイント

・ロックは主キーもしくはセカンダリインデックスに対して行われる。→走査された行全てにロックがかかる。インデックススキャンされずテーブルスキャンされた場合、全行がロック対象になってしまう。
・ロックがかかる操作をする場合、インデックススキャンされるようケア必要
・ロックは以下2種類あり。

種類 説明
共有ロック 更新を禁止するロック。読込はブロックしない。
排他ロック 更新 & 読込どちらブロック

・ロックされる対象は以下3種類あり。
　該当レコードのみ
　直前のギャップのみ
　該当レコードと直前のギャップ
　※ギャップはインデックスとインデックスの間にかかるロック。後述。
・行単位ロックは、種類と対象により6種類(2×3)と、INSERT時の特殊なギャップロックの合計7種類あり。

ギャップロックについて

　【MySQL】InnoDBの共有ロックと排他ロックの概要と挙動検証 | Enjoy IT Life
　良く分かるMySQL Innodbのギャップロック - Qiita
　LT：MySQLのギャップロックとネクストキーロックについて - Speaker Deck

　ギャップロック=インデックスとインデックスの間にかけられるロック
　この意味を動作確認で把握する。環境は以下の通り。

> CREATE TABLE test (id int(11) NOT NULL DEFAULT 0, data varchar(255) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> INSERT INTO test (id, data) VALUES (2, 'bbb'), (3, 'ccc'), (4, 'ddd'), (5, 'eee'), (10, 'fff'), (20, 'ggg');
> select * from test;
+----+------+
| id | data |
+----+------+
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
|  5 | eee  |
| 10 | fff  |
| 20 | ggg  |
+----+------+

上記環境に対し、2つのトランザクションで動作を確認。
片方でbegin; select * from test where [idで条件指定] for updateクエリを発行し、もう片方でinsert/updateでデータを登録できるか？でプロックの範囲を確認。

※上記表の確認方法は以下の通り。
例)条件式がid=0で、insert・updateの値が1の場合
①トランザクション1で以下SQLを実行

begin; select * from test where id=0 for update;

②トランザクション2で以下SQLを実行

INSERT INTO test (id, data) VALUES (1, 'XXX');

※上記SQLでロック待ちになるか？でロックを確認。
※1の部分をいろいろ変える。

例)条件式がid=2で、insert・updateの値が2の場合
①トランザクション1で以下SQLを実行

begin; select * from test where id=2 for update;

②トランザクション2で以下SQLを実行

update test set data='XXX' where id=2;

※条件式で指定した値と、対象レコードの値が同じ時は、updateでロック確認。

ギャップロックは指定した条件に対して直観的ではない。
ギャップロック=インデックスとインデックスの間にかけられるロック

SQLが空振りしても、ロックかかる！※空振り＝select該当行なし

このように直感的でない動きは、デフォルトのリピータブルリードの為。※上述の通りmysqlはネクストキーロック付

上記を理解しておくこと！

MVCCについて

Multi Version Concurrency Controlの略
MVCCを実現するには、
・トランザクションでコミットされるまで、変更前データを保持しておく必要がある。※上述の通り、コミットされる前に、テーブルスペースは更新される。
・またコミットされ、テーブルスペースへの反映が完了した後でも、トランザクションの分離レベルを維持する為、変更前のレコードを保持しておく必要がある。
これらを実現するため、Innodbはテーブルスペースのロールバックセグメントに複数のUNDOログ(行データのバージョン)を持っている。

ロックタイムアウトとデッドロック

MySQLはグローバル設定 & プラグインによりセッション単位で設定できる。
Oracleはクエリ単位で設定できる。
Innodbでは、ロックタイムアウトが発生した場合、ロールバックされるのは、該当クエリのみ。トランザクション全体をロールバックさせるには、明示的にROLLBACKステートメント実行するか、innodb_rollback_on_timeoutを設定する。

Innodbではデッドロックが発生すると、すぐに検知しシステムに影響が少ない方をロールバックする。このロールバックは該当クエリだけでなくトランザクション単位で行われる。

一般的なRDBMSでデッドロックを発生させないためにすべきこと

・トランザクションを頻繁にコミットする。
・決まった順でテーブルにアクセスする。
　例) トランザクション1は、テーブルA→テーブルBの順でアクセス。トランザクション2は、テーブルB→テーブルAの順でアクセス。トランザクションによってアクセスする順を変えない。

Innodbでデッドロックを発生させないためにすべきこと

・テーブルに適切にインデックスを付与し、ロック範囲の指定でインデックスが利用されるようにする。※行レベルロックの為。
・可能ならトランザクション分離レベルをリードコミッテッドにする。
・ロック読み取り(select for update)を不要に使わない。

トランザクションべからず集

・autocommitを使わない。
　例) ストアドプロシジャで大量レコード登録時、1レコードづつコミットされ時間がかかる。
　　書籍記載の事例だと10万レコード登録で、autocommit有効の場合は150秒で、1万レコード毎にコミットする場合は7秒。
・大量処理を1トランザクション内で行わない。
　大量データ削除等をすると、大量のUNDOログが生成されテーブルスペースが肥大化する。UNDOログ削除されても、テーブルスペースのサイズは減らない。
・更新せずselectを実行するだけのトランザクションでも早期に終わらせる。リピータブルリードだと、初回select実行後、トランザクション終了までUNDOログが残る。
・トランザクション中に対話処理を入れる。

トランザクション動作確認

トランザクションは、
・トランザクション中に実行した更新操作が全て成功or失敗を制御する
上記だけでなく、
・分離レベルにより、トランザクション中の処理が扱うデータの整合性も制御する。
→一貫性のあるスナップショットなら、トランザクション内の処理は、開始時点のデータを、参照する。

上記踏まえ、以下の動作を検証する。
①トランザクションaを一貫性のあるスナップショットで開始し、テープルaをセレクト
②トランザクションbでテーブルaにレコード追加しコミット
③トランザクションaでテーブルaをセレクトし、②のレコードか存在しないことを確認。②で追加したレコードを登録
→予想。登録は成功、コミット時にエラー？

データ型

4バイトの文字列型で整数を表現する場合、0～9999の9999通り。
同じく4バイトの整数型で整数を表現すると、0～2の32乗で42億ちょい通りを表現できる。また計算処理もしやすい。
→データ型により、格納サイズ・処理の最適化ができる。

データ型 説明
CHAR(N) 固定長文字列型
※MySQLの場合癖があり。
固定長に満たないデータを入れると、空白が自動付与される。しかし取り出し時は、空白が除かれる。
※Nはバイト数の意味。PostgreSQLの場合は文字数。
VARCHAR 可変長文字列型
TEXT
※ラージオブジェクト文字列型		 varcharで収まらない場合に利用
INT 整数を表現。符号つけることで負数の表現も可能。
SIGNED=符号あり(負数表現可)。UNSIGNED=符号なし(負数表現不可)。
INTは4バイト、BIGINTは8バイト。
FLOAT/DOUBLE
※浮動小数点		 小数点が使える。INTよりも大きな数値を扱える。近似値を格納する為、演算で誤差が出る。
FLOATは4バイト、DOUBLEは8バイト。
NUMERIC/DECIMAL(P,S)
※固定小数点		 SQL標準ではNUMERIC/DECIMALは異なるがMySQLでは同じ。Pは全桁数、Sは少数部分の桁数を意味する。デフォルトPは10でSは0。P、Sともに最大65。整数だけでなく小数点も使え、誤差なくBIGINT以上の数値を扱える。

※文字列型は、VARCHAR→TEXT→CHARの順で検討する。CHARは他DBからの移行等で互換性を維持する等、特別なニーズがない限り使わない方がよい。
※BIGINT以上の整数を誤差なく利用するには、NUMERIC型を使う。
※autoincrement列や主キーにはBIGINTを検討すべき。テストデータ出し入れでも、消費されるので、多めにとるべし。
※扱える数字の大きさの点では、BIGINT(19桁)→NUMERIC(65桁)→DOUBLE(308桁・誤差あり)

スレッドキャッシュとコネクションプーリングの違い

・クライアントからの接続要求毎にスレッドが生成される。
・スレッドキャッシュはMySQLサーバの機能で、生成されたスレッドをキャッシュし、次の接続時に再生成処理不要で利用可能にするもの。
・コネクションプーリングは、MySQL接続自体をプーリングする機能で、APサーバ側で実装される機能。接続認証はコネクション確立時のみでOK

Raw Deviceについて

ファイルシステムを使わずアクセスする方法
ファイルシステムオーバーヘッドやキャッシュを回避できるため高速化が可能
しかしデータの利用率や移動ができない。またファイルシステムの高速化に伴い必要性は低下
MySQLではテーブルスペースで利用可能。

データベースオブジェクトとは？

テーブルやビュー、トリガ、ストアドプロシジャのこと

スキーマとは

一般的なDBでは、データベースのこと。ユーザー名のスキーマが作成され、それが接続時のデフォルトDBになったりする。MySQLはこの概念がなく、use等でデータベースの指定が必要。