MySQLについていろいろ書いてみる
まずはRDB共通なものを書いてMySQLのことを挟んでいく。自分用のメモとして眠っていたものを転載。
ACIDとトランザクション
- ACIDとは
- 原子性 (Atomicity)
- 一貫性 (Consistency)
- 分離性 (Isolation)
- 持続性 (Durability)
- ACIDはトランザクションの概念と密接に結びついている
- トランザクションを保証する概念
- トランザクションは、独立していて、確定すると継続され、ALL OR NOTHINGで一貫しているもの
- 特に複数のテーブルを更新しているときには矛盾が発生する危険がある
トランザクション分離レベル
- ACIDのIのこと
- MySQL(InnoDB)がサポートしているのは以下のレベル
- ただし、分離レベルによらず、SQL文の実行以外、整合性制約をチェックするときのスキーマ定義の暗黙の読み込みや、参照整合性制約によって実行される参照動作の実行において、P1〜P3(後述)は発生しない
- 上に行くほど、並列性が低い代わりにデータの一貫性、結果の再現性が高い
- 詳細は以下の通り
- ただし、InnoDBは
REPEATABLE READでもファントムリードが発生しないことになっている
| ダーティリード | ファジーリード | ファントムリード | |
|---|---|---|---|
| READ UNCOMMITTED | 発生する | 発生する | 発生する |
| READ COMMITTED | 発生する | 発生する | |
| REPEATABLE READ | 発生する | ||
| SERIALIZABLE |
ここが詳しい https://qiita.com/PruneMazui/items/4135fcf7621869726b4b
- それぞれの分離レベルで
SERIALIZABLEから離れれば離れるほど読み取り時に不整合が発生しやすい - それぞれの読み取り不整合がなんなのか
ダーティリード(P1)
ファジーリード(P2)
- 別のトランザクションで更新されたデータを読むことにより、一貫性がなくなる現象
- つまり自分がコミットする前に、更新されたデータが更新前後で異なる値として取得される
ファントムリード(P3)
他にも
ダーティーライト(P0)
- トランザクションT1がデータを更新しトランザクションT2がT1がコミットかロールバックを実行する前に同じデータを更新する。このとき、T1とT2のどちらかがロールバックを実行すると正しいデータの値は何であるべきか不明確になる
失われた更新(ロストアップデート)(P4)
- 例外的
- トランザクションT1がデータを読み出す。T2がそのデータを更新する。最後にT1がT2が更新した値の前に基づいてデータを更新しコミットする。T2の更新が消える。
CURSOR STABILITY分離レベル
- というものが存在しているが、
READ COMMITTEDのSQLカーソルに対するロック動作なので割愛
ロック
- MySQLには、というよりDBにはロック機構が存在している。理由は、マルチユーザーで単一のリソースにアクセスするのでリソース競合が発生するため。正しく更新(INSERT,UPDATE,DETELE)されるようにするための仕組みがロック
- MySQLのロック
- ロックの種類( https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html )
- レコードロック
- クラスタインデックスを使って取得されるロック
- 更新対象のレコードのみロックできれば最も理想的なロックになる
- ギャップロック
- インデックスレコード間のギャップ、先頭インデックスレコードの前や末尾インデックスレコードのあとのギャップをロックする仕組み
- キーを指定して以上とかでロックをかけるとほぼテーブルロックとかになる怖いやつ
- ネクストキーロック
- ロッキングリードにおいてファントムを防ぐために用いられる仕組み
- これは行ロックとギャップロックを組み合わせたもの
- ロック対象の次の値までをロックすることでファントムリードを防ぐ(ファントムリードは別トランザクションで挿入されたデータが見えてしまう現象のため)
select ~ for updateでロックした場合、データが挿入できなければファントムリードは防げる。ただし、並列性はSERIALIZABLEほどではないがプレーンなREPEATABLE READよりは下がる
- レコードロック
- 専有レベル(lock mode)
- ロックの種類( https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html )
MVCC(multi version concurrency control)
- 読み取り専用のトランザクションにおいて、若干の時差を許容することで一貫性のある読み取りを実現するための仕組み
- InnoDBの専売特許ではなく、Oracle、PostgreSQLにも存在するらしい( https://ja.wikipedia.org/wiki/MultiVersion_Concurrency_Control )
- InnoDBでは、
REPEATABLE READ、READ COMMITTEDで利用されている - InnoDBではこれがあるため、ファントムリードが起きないことになっている
- ただし、このことが別の問題を引き起こしている
ノンロッキングリードとロッキングリード
- MVCCはノンロッキングリードでしか用いられない
- ノンロッキングリードとは？
- ノンロッキングとは行にロックをかけないという意味
- ロッキングは行にロックをかける
- これは、
select ~ for updateもしくはselect ~ lock in share modeを使うか否かによって取得される結果が異なるということである - ノンロッキングリードで読み取られるのは古いバージョンのデータなので、最新のデータが何であろうと、ロックされていようがいまいが構わない。そのような場合でしか、MVCCは用いられない。
- つまりは、MVCCはリード性能を高めるための仕組みであって更新時には問題を起こす可能性がある
- ファントムリード(別のトランザクションで挿入されたデータが見えること)が防げているのはノンロッキングリードのときのみでロックありでリードするロッキングリードの場合はMVCCの対象から外れて最新のデータが見える(つまりはファントムリードが発生する)
インデックス
- InnoDBはクラスタインデックスという構造を採用している
- クラスタインデックスはデータがインデックスのリーフノード上に格納されている
- Oracleみたいにインデックスとデータ領域が分かれているわけではない(今は知らん)
- 主キーのインデックス上にデータが存在している。そのため、主キー検索すると爆速(一回の走査でデータも取れちゃうため)
- あとはセカンダリインデックスというものがある。
- これがいわゆるインデックスというもの。
- 独自に指定して作成するインデックス(
create index ~で作成されるインデックス)はすべてこれ - セカンダリインデックスを使う場合は、セカンダリインデックスを走査してからクラスタインデックスを走査するので効率が悪い
- とは言ってもそういう仕組みのDBだってあるしそこまで問題にはならないのでは？
- 複合インデックスを用いたカバリングインデックスとかを使うと効率上がる(インデックスだけ見れば良いので)
- とは言え、インデックス設定しまくるのは良くないのでご利用は計画的に。(インデックスショットガン)
オンラインDDL
- MySQL5.6から一部のDDLがオンラインで実行できるようなった(書き込みでロックが発生しなくなった)
- 少なくともインデックス作成はオンラインでできる。ただし、IO負荷やCPU負荷は考慮すること
- 力尽きたのであとは以下のリンクを参照してください
