MySQL

作成日時：2025-01-24
更新日時：2025-01-24

コマンド

mysql -u $user
use $database

UPSERT

REPLACEとON DUPLICATE KEY UPDATEがある。
前者はdelete-insert、後者はupdate。

InnoDB

基本的にPKのクラスタインデックスでデータを保持している。
リーフノードに実データを持っている。

PK以外のインデックスはセカンダリインデックス。
リーフに主キーの値が格納されている。
しかしセカンダリ⇒プライマリの2段階アクセスなのでパフォーマンスが問題。
つまりインデックスを2回見ている。
この主キー検索を高速化するためにアダプティブハッシュインデックスがある。
イメージとしてはkeyがセカンダリインデックスの検索値のハッシュ、valueがレコードへのポインタ。
ハッシュなので等価検索のときにのみ使われる。
バッファプール上に作られる。

ギャップロックとネクストキーロック

InnoDBのやつ。

格納されているレコードはx = {10,20,30}の3件とする。

SELECT * FROM tbl WHERE x = 20 FOR UPDATE;

= で検索して、存在する場合はレコードロック。
その行のみロックする。
上記の場合は20のレコードのみ。

SELECT * FROM tbl WHERE x = 19 FOR UPDATE;

= で検索して、存在しない場合はギャップロック。
実レコードの間をロックする。
上記の場合は11-19をロックする。

SELECT * FROM tbl WHERE x BETWEEN 18 AND 21 FOR UPDATE;

範囲で検索した場合、実レコード間のギャップと次の実データをロックする。
上記の場合は11-30をロックする。

ギャップロック同士は競合しない。
ギャップロックとネクストキーロックは競合する。
ロックに対して更新等は出来ない。

これにより、MySQLはREPEATABLE READでもファントムリードは発生しない。(関係ないけどPostgreSQLも)
検索した範囲内におけるデータ操作を禁止しているから。

ネクストキーロックはギャップ領域の拡大を防ぐためという解釈。
実存するインデックスデータでロックを管理しているのだから、データを消されるとギャップを管理できなくなる。

REPEATABLE READ以上かつFOR UPDATEなどのクエリで初めてこのロックが発生する。

MVCC⇒完全にファントムリードを防ぐ
ネクストキーロック⇒ロックのついでにファントムリードも実質防ぐ。

まあ、FOR UPDATEには気をつけとけ。ネクストキーロックが発生しうる。

GROUP BY (MySQL)

基本、一時ファイルが作成される。
関連：GROUP BYにおけるインデックススキャン