MySQLメモ

はじめに

参考にした本

  • 実践ハイパフォーマンスMySQL

サーバーアーキテクチャ

  • レイヤー1
    • 接続処理、認証、セキュリティなど
    • その他ネットワークベースの一般的なクライアント/サーバーツールで必要となるサービスを含む
  • レイヤー2
    • クエリ解析、分析、最適化、キャッシュ、組み込み関数(日付、時間、算術演算、暗号化など)
    • Stored Procedure, Trigger, View
  • レイヤー3
    • ストレージエンジン
    • ストレージエンジンAPI
      • ストレージエンジン間の違いを吸収→クエリレイヤで大部分を透過的に
      • 「トランザクション開始」「この主キーを持つ行の取得」といった操作を実行する低レベル関数
    • SQL解析や相互通信はしない
    • あくまでサーバーからのリクエストに応えるだけ

最適化と実行

  • MySQLはクエリを解析して内部構造(解析ツリー)を作成後、さまざまな最適化を適用する
    • クエリの書き換え
    • テーブル読み取り順序
    • 使用インデックスの選択
  • オプティマイザ
    • ストレージエンジンにその能力と特定操作のコスト、テーブルデータを統計的に問い合わせ
      • ストレージエンジンは特定クエリに役立つインデックスをもつ

並行性の制御

  • サーバレベルとストレージエンジンレベルの２つのレベルで行う必要あり
Read/Write Lock
  • Read Lock: 共有ロックで相互にノンブロッキング
  • Write Lock: 排他ロックで、Read Lockと他のWrite Lockをブロック
Lockの粒度
  • ロック操作のオーバーヘッド
    • ロックの取得/解除
    • ロックが空いてるかどうかのチェック
  • ほとんどのRDBは行レベルロック(クエリに対応するレコードのみロック)
  • MySQLは幾つか選択肢がある
    • テーブルロック
      • オーバヘッドが少ない
      • 基本は上で述べたRead/Write Lockをテーブル全体に適応させる
      • Write LockはRead Lockより優先度が高いので、Read Lockをキュー内で飛び越えて前方に配置される
      • READ LOCALテーブルロックを使うと、同時書き込み操作を許可することができる
      • 例: ISAM, MyISAM
    • 行ロック
      • 並行性が最も高い
      • オーバヘッドが最も高い
      • 行ロックはサーバーではなくストレージエンジンで実装される
      • 例: InnoDB, Falcon

トランザクション

分離レベル(ACIDのI)
  • SQL規格では4つの分離レベルを定義
  • 分離レベルが低いほど並行性が高くなり、オーバヘッドが低くなる
  • 4つの分離レベルまとめ
分離レベル Dirty Read Non-Repeatable Read Phantom Read Read Lock
READ UNCOMMITED ◯ ◯ ◯ ×
READ COMMITED × ◯ ◯ ×
REPEATABLE READ × × ◯ ×
SERIALIZABLE × × × ◯
  • READ UNCOMMITED
  • READ COMMITED
    • 分離レベルの単純定義を満たす
    • Non-Repeatable Read発生
      • 同じ文を2回実行すると異なるデータが返される可能性
    • MySQLを除くほとんどのRDBのデフォルト分離レベル
  • REPEATABLE READ
    • ファントムリード発生
      • ある範囲の行を選択後、別のトランザクションがその範囲に新しい行を挿入し、その後に同じ範囲を選択したときに新しいファントム(幻の)行が現れるという問題
      • InnoDBやFalconではMVCCに基づいてこれを解決できる
      • MySQLのデフォルト分離レベル
  • SERIALIZABLE
    • 競合しないようトランザクションを強制的に順序付けする
    • 読み取る全ての行にロックを設定

デッドロック

トランザクションログ

  • ログ先行書き込み
    1. データのメモリ上のコピーを変更
    2. トランザクションログへの書き込み
      • ログイベントの追加はシーケンシャルI/Oなので比較的高速
    3. テーブルを更新
  • ほとんどのストレージエンジンが採用
  • リカバリ方法はストレージエンジンごとによって異なる

MySQLのトランザクション

AUTOCOMMIT
  • AUTOCOMMITをONにすると、各クエリを別々のトランザクションで実行
  • OFFの場合はCOMMIT/ROLLBACKを発行するまでは常にトランザクションの中にいて、
  • COMMIT/ROLLBACKが発行された時点で新しいトランザクションを開始する
  • 「SET TRANSACTION ISOLATION LEVEL」により分離レベルを設定可能
トランザクションでの複数ストレージエンジンの併用
暗黙的/明示的ロック
  • InnoDB
    • 暗黙的
      • トランザクション中はいつでもロック取得可能だが、COMMIT/ROLLBACKまで開放されない
      • すべてのロックを同時に開放する
      • 分離レベルに基づいて自動的にロック処理を行う
    • 明示的
      • SELECT ... LOCK IN SHARE MODE
      • SELECT ... FOR UPDATE

マルチバージョンの並行性制御(MVCC)

  • InnoDB,Falcon,PBXT
    • ×:単純な行ロックメカニズム
    • ◯:行レベルロック+MVCC
  • MVCC
    • ある時点で存在していたデータのスナップショットを作成という仕組みで動作
    • トランザクション長に関わらず、参照可能データを一貫させる
    • 複数トランザクションが同じテーブル内の異なるデータを同時参照可能
  • MVCCのメリット
    • 多くの場合でロックの必要性を完全排除し、オーバヘッドの多くを解消
    • ロックを使用しない読み取りを許可し、かつ書き込み操作では必要なレコードのみをロックすることができる
  • MVCCの実装
    • ストレージエンジンごとに違う
    • Optimistic/Pessimisticな実装がある
InnoDBのMVCC
  • 行ごとに2つの隠れた値を格納
    • 行が作成されたタイミング
    • 行が期限切れ(削除)されたタイミング
  • イベントが発生した実時間ではなく、システムバージョン番号を記録
  • 各トランザクションは開始時点でのシステムバージョン番号を記録
  • クエリは、それぞれ、各行のバージョン番号とトランザクションのバージョンとを照合しなければならない。
  • 照合条件は分離レベルで異なる
    • MVCCはREPEATABLE READ/READ COMMITEDのみ対応
  • 本にはREPEATABLE READの時のCRUDクエリそれぞれにおいて条件が書かれている
ロック方式 並行性 オーバーヘッド エンジン
テーブルレベル 最低 最低 MyISAM,Merge,Memory
行レベル 高 高 NDB Cluster
MVCCを備えた行レベル 最高 最高 InnoDB,Falcon,PBXT,solidDB

ストレージエンジン

  • MySQL6.xの時点で13種類ある
    • MyISAM,MyISAM Merge,Memory,InnoDB,Falcon,Archive,CSV,Blackhole,Federated,NDB Cluster,PBXT,solidDB,Maria
    • 主なものについて下にまとめる
Engine MySQL Version Transaction Lock粒度 usage 使用すべきでない状況
MyISAM ALL × 同時挿入可能なテーブル SELECT,INSERT,一括READ Read/Writeの混在
Memory ALL × テーブル 中間計算、静的lookup 大きなデータセット、永続化
InnoDB ALL ◯ MVCC+行レベル Transaction なし
Falcon 6.0 ◯ MVCC+行レベル Transaction なし
Archive 4.1 ◯ MVCC+行レベル Log,集計 ランダムアクセス,更新,削除
NDB Cluster 5.0 ◯ 行レベル 高可用性 典型的な用途
PBXT 5.0 ◯ MVCC+行レベル Transaction,Log クラスタ化インデックスの必要性
solidDB 5.0 ◯ MVCC+行レベル Transaction なし