Open1

High Performance MySQL, Edition4を読んでみた

tryutryu

1. MySQL Architecture

mysqlの構造

コネクション接続・認証などのネットワーク関連
・1つの接続に対し1つのスレッドを割り当て、1CPU上で動作する
・ユーザ名・パスワードをもとに認証を行い、SQLクエリを実行する権限があるかを確認する
SQLクエリのパース
・SQLクエリの解析を行い、最適化を図る
ストレージエンジン(単にサーバーからのリクエストを返す)
・ロックの処理など
ストレージエンジンAPIを介して行をやり取りすることでクエリの実行を行う
参考:
https://www.wantedly.com/companies/progrit/post_articles/326087

ロックについて

共有ロック
・複数の同時読み取りは許可(書き込みはできない)
・干渉しない(ノンブロッキング)
排他ロック
・同時に書き込みができるのは一人だけ

ロックのオーバーヘッド(リソースを消費する)と安全性はトレードオフの関係
MySQLのロックは独自のロックポリシーとロック粒度がある
テーブルロック
テーブルに対し書き込みが行われているときにテーブル全体をロックする。その際他の書き込み・読み取り操作が制限される。オーバーヘッドは小さい
行ロック
同時に異なる行を編集できるが誰がどの行を編集しているかを追う必要がありオーバーヘッドが大きい

トランザクションについて

データの不整合を防ぐための仕組みで、1-5が一貫して行われる。(3で終わることなどを防ぐ)
1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;
これを保証するための基準が、ACID
原子性(Atomicity)
トランザクション全体が適用されるか、何も実行されないかのどちらかであることを保証する。“all or nothing”
一貫性(Consistency)
データのルールや制約が常に満たされていることを保証する(「送金元 + 送金先 = 全体の金額」という関係が成り立つことを保証)
分離性(Isolation)
トランザクションの結果は他のトランザクションには見えない(分離されている)
dirty readなどを防ぐ
永続性(Durability)
コミットされればデータは永続的に保存される
参考:
https://qiita.com/song_ss/items/38e514b05e9dabae3bdb

トランザクション分離レベルについて

READ UNCOMMITTED
コミットされていないデータを読み取る可能性(dirty read)があり、ほとんど使用されない
READ COMMITTED
トランザクションAで複数回読み取りを行っている際にトランザクションBでデータを更新しコミットした際にトランザクションAで異なるデータを読み取ってしまう可能性がある(fuzzy read, nonrepeatable read)
REPEATABLE READ
同じトランザクションで「同じデータを読み込む」ことを保証する。
ただ、別のトランザクションで新たな行(“fantom row”)が挿入されたりすると不整合が発生する。=fantom read
SERIALIZABLE
強制的にトランザクションを順序付けて処理するためコンフリクトが起きない。全ての行にロックをかけるためパフォーマンスは悪く、あまり使われない。
参考:
https://qiita.com/momotaro98/items/ad859ec2934ee98540fb

DeadLock

二つのトランザクションが同じリソースに対し相互にロックの保持と要求を行い、無限ループのような状態になってしまうこと
InnnoDBではこの問題の対応としてタイムアウトを設定しエラーを返す仕組みがある。その際、自動的に片方のトランザクションをロールバックし正常に終了するようにしている

トランザクションログ

変更時にテーブルを更新するのではなくデータのメモリ内コピーを変更する。
変更の記録をDisk上のトランザクションログに書き込む。(耐久性がある)
ランダムI/OではなくシーケンショナルI/O

AUTOCOMMIT, Mixing storage engines in transactions

INSERT, UPDATE, or DELETEはデフォルトでは自動コミットされるが、これをオフにして明示的にコミットするようにすることもできる。
トランザクション分離レベルも指定することができる
異なるストレージエンジンは混在させない方が良い。ロールバックが正しく機能しなくなる可能性があるため一貫したものを使用する

Implicit and explicit locking

InnoDBのロックは自動で管理されるが明示的なロックも可能
LOCL TABLESはあまり使わない方が良い

MVCC

MySQLのストレージエンジンで使用されている
READ COMMITTED及びREPEATABLE READ分離レベルのみで機能
ロックを最小限に抑え、UNDOログとスナップショットを利用することで高い並行性する技術(オーバーヘッドが小さい)→REPEATABLE READでもファントムリードが発生しない
参考:
https://qiita.com/yatsurugi55/items/da34f0dcbdfb011d3bf8

Replication

MySQLは一つのNodeでデータ書き込みを処理し、分散処理のため書き込みを他のノード(=Replica)に複製する仕組み(=Replication)を持つ。
ソースノードはレプリカごとにスレッドを持ち、書き込みが行われるとデータ送信をする
レプリカは3つ以上必要

Datafiles Structure

MySQL 8.0以降、LRU(Least Recently Used)の導入によりI/Oが削減された
LRU: キャッシュが一杯になった際に最も長い時間アクセスされていないキャッシュ内の要素を削除(時間的局所性に基づいてキャッシュを更新するアルゴリズム)
参考:
https://zenn.dev/gorogoroumaru/articles/0a287d6c0c18d3

InnoDB Engine

MySQLのデフォルトのストレージエンジン
“tablespace”と呼ばれるデータファイルにデータを保存する
分離レベルはREPEATABLE READで、MVCCを用いて高い同時実行性を実現する(ファントムリードが発生しない)
内部で様々な最適化が行われている

MySQL 8.0以降の機能

JSON Document Support
MySQL5.7で導入されたJSONデータ型について、多値インデックスの導入によりJSONドキュメントへのクエリ速度が向上
Data Dictionary Changes
テーブルのメタデータをファイルベースからInnoDBのデータディクショナリに移行
クラッシュリカバリなどの恩恵がある
Atomic DDL
DDL(データ定義言語)の操作がAtomic性を持つように
→完全に正常終了するか、ロールバックされるかのどちらか