『High Performance MySQL 第4版』を読んだ内容を章ごとにまとめています。
普段の業務でMySQL 8を利用しており、書籍で得た知識を「実務でどう生かせるか」という視点で整理しています。

---

第1章 MySQLアーキテクチャ

概要

MySQLは、小規模なウェブサイトから大規模なエンタープライズシステムまで幅広く利用できる柔軟なアーキテクチャを持つ。本章では、その全体像を理解するために、サーバの論理構造、並行処理の仕組み、トランザクションとACID特性、MVCC、レプリケーション、データファイル構造、そしてInnoDBエンジンの特徴を解説する。
MySQLを効果的に使うためには、この設計思想を理解して「仕組みに逆らわずに」活用することが重要である。

---

MySQLの3層構造と責任範囲

1. クライアント層（接続の入り口）

責任

• 接続・認証・権限チェック

主な処理

• クライアント接続をスレッドごとに処理（スレッドプールで再利用）
• ユーザー名・接続元ホスト・パスワードで認証
• TLS証明書によるセキュリティ強化
• クエリごとのアクセス権限チェック

業務上の問題切り分け

• 「接続できない」「権限不足エラー」はこの層が原因の可能性大
• DB設定ファイル（ユーザー権限やTLS設定）やネットワーク経路を確認

---

2. サーバ層（頭脳）

責任

• クエリを理解し、最適な実行計画を決定

主な処理

• パーサ：SQLを内部構造に変換（構文エラーを検出）
  • [MEMO] なんで構文エラーメッセージは直接エラー内容を教えてくれないんだろう（... near 'name'とか）と思ってたけど、ここで先頭から順番に解釈していって「解釈できない」ってタイミングでエラー出すかららしい
• オプティマイザ：実行計画を決定（インデックス選択、JOIN順序、サブクエリ展開）
  • EXPLAIN コマンドで実行計画を確認
• 実行エンジン：ストレージエンジンに命令
• ストアドプロシージャ、トリガー、ビューなどを管理
• 内部のクエリキャッシュ：MySQL 5.7.20以降で非推奨、8.0で完全に削除。代替としてRedisやmemcachedによるアプリケーションレベルのキャッシュを推奨

業務上の問題切り分け

• 「SQLが遅い」場合はこの層の最適化不足を疑うが、I/Oやロック競合（ストレージエンジン層由来）の可能性もある

---

3. ストレージエンジン層（手足）

責任

• 実際にデータを扱い、同時実行やトランザクションを制御

主な処理

• データの読み書き（I/O）
• インデックスの維持
• トランザクション処理（REDO/UNDOログによるACID保証）
• ロック制御（テーブルロック・行ロック）
• MVCCによるスナップショット読み取り
• トランザクションログ管理（WAL）

業務上の問題切り分け

• 「ロック競合」「I/O遅延」「ディスク障害」はこの層の可能性大

補足

ACID特性

• Atomicity（原子性）: 全て完了する or 全て完了しない
• Consistency（一貫性）: 一貫した状態から次の一貫した状態に進む
• Isolation（分離性）: トランザクションの結果は完了まで他から見えない
• Durability（永続性）: コミット後は障害があっても保持される

分離レベル

• READ UNCOMMITTED: コミットされていない変更が見える（ほぼ使わない）
• READ COMMITTED: 最新のコミット済みデータを都度参照
• REPEATABLE READ: トランザクション開始時点のスナップショットを固定
  • InnoDBのデフォルト
    • 行の内容の一貫性：MVCCによるスナップショット参照にて担保
      • 一般的なDBはロックで実現することが多いが、InnoDBはMVCCで実現 → 読み取りロックが不要で性能が高い
    • 件数の一貫性：Next-Key Lockingによる範囲ロックにてファントムリードが発生しないように担保
• SERIALIZABLE: 全てにロックをかける → 安全だが並行性は低い

MVCC(InnoDBにおける仕組み)

• 読み取りと書き込みを並行させるために「ロックを取らない読み取り」を実現する仕組み
  • 行ごとに作成/削除バージョンを保持・・・バージョン番号はトランザクションが開始するたびにインクリメントされる
    • INSERT: 現在のシステムバージョン番号を新しい行の作成バージョンに記録
    • DELETE: 現在のシステムバージョン番号を削除した行の削除バージョンに記録
    • UPDATE:
      • 既存の行を直接書き換えない
      • 変更後の値で新しい行を作成し、現在のシステムバージョン番号を作成バージョンに記録
      • 現在のシステムバージョン番号を古い行の削除バージョンに記録
  • SELECTはバージョン比較で可視性を判定 → 多くのSELECTがロック不要になる
• 効果: 並行性を高めつつ整合性を守る

Next-Key Locking

• クエリをアクセスした行のみをロックするのではなく、インデックス構造の隙間もロックする仕組み
• ロック範囲が広くなる → 並行性は下がる
• 例:

    -- トランザクションA
    BEGIN;
    SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE;

この時、`id=5〜10` の行に加えて「10と11の間の隙間」もロックされる
そのため、別のトランザクションBが次のように実行するとブロックされる

    -- トランザクションB
    INSERT INTO users (id, name) VALUES (10.5, 'phantom'); -- 実行待ちになる

• [MEMO] イメージわかりにくかったので掘り下げ
  • インデックスレンジスキャンは「本棚で本を探す」イメージ
  • 「5巻から10巻までください」と言われたら、本棚の5巻を見つけて → 6巻 → 7巻 … と順に10巻まで取る
  • 「この先は範囲外だ」と判断するため、10巻を取ったあとに「次の本（11巻）」もチラッと確認する
  • その「チラッと確認した次の本（11巻）」の 手前の隙間もロックするのがNext-Key Locking
    • その隙間に新しい行（例えば 10.5 巻）が挿入されたら、再検索時に結果が変わってしまうから

デッドロック

• 複数のトランザクションが互いにロックを待ち続ける状態
• InnoDBは自動検知して「巻き戻しやすい方」をロールバックする

レプリケーション

• MySQLは基本的に 1つのノード（インスタンス）が書き込み担当 ※ノード＝MySQLサーバー1台分
• 書き込みを他ノード（インスタンス）に伝播 → 障害復旧や読み取り負荷分散に利用
• 推奨構成：少なくとも3つ以上のレプリカを異なるリージョンに配置

テーブル定義とメタ情報

• MySQL 5.x時代は .frm ファイルに保存
• MySQL 8以降はデータディクショナリに移行（.sdi ファイルで管理）
• メリット：効率的かつトランザクション対応のメタデータ管理
• SHOW TABLE STATUS で確認できる情報
  • Row_format
    • Dynamic: 可変長（VARCHAR, BLOBなど）
    • Fixed: 固定長（CHAR, INTEGERなど）
    • Compressed: 圧縮されたテーブル
  • Rows: テーブル行数（InnoDBでは推定値。大きなテーブルでは実数とずれることがある）
    • [MEMO] COUNT(*) は正確だが重い。Rowsは軽いが推定値
  • Auto_increment: 次に割り当てられる AUTO_INCREMENT 値（＝次のINSERTで使われる値）
    • [MEMO] 「最後に使った値」ではなく「次に使う値」

InnoDBエンジン

• デフォルトかつ推奨のストレージエンジン
• 特徴
  • 短命なトランザクション処理に最適
  • クラッシュリカバリに強い
  • MVCCと4つの分離レベルを実装
  • クラスタ化インデックスによる高速検索
  • JSON型サポート、マルチバリューインデックス
  • オンラインDDL、Atomic DDLに対応
• 実運用での利点
  • バックアップ手法が豊富（XtraBackup、Enterprise Backup）
  • 高いトランザクション性能
  • 商用・OSS問わず幅広いサポート実績

MySQL固有のポイント

• AUTOCOMMITモード：デフォルトで各DMLが即時コミットされるので注意
• 異なるストレージエンジンを混在させるとロールバック不可 → 一貫性が崩れる危険
• 明示的ロック：SELECT ... FOR UPDATE、LOCK TABLES など

---

まとめ

• MySQLは「サーバ層（解析・最適化）」と「ストレージエンジン層（保存・取得）」に分かれ、APIで接続
• InnoDBが事実上の標準エンジンであり、性能・安全性・機能のバランスに優れる
• サブクエリの有無は性能に影響する場合があるが、MySQL 8ではJOIN同等に最適化されることも多い
• レプリケーションを活用することで可用性とスケーラビリティを確保できる
• MVCCにより読み取りは更新にブロックされにくく、安定した集計・レポート処理が可能

---

業務での活かし方

• 問題切り分けに役立つ
  • 接続エラー：クライアント層（認証・権限）
  • SQLの遅さ：SQL処理層（オプティマイザ、インデックス）＋ストレージエンジン層（I/O、ロック競合）
  • データの格納/取得速度：ストレージエンジン層（InnoDB設定やI/O）
• InnoDBを前提に設計する
  • トランザクション分離レベルやロックの挙動を理解しておくことで、同時更新時の不具合を避けられる
    • 現状、自分が関わっているプロジェクトではREAD COMMITTED設定なので意識することで問い合わせとかに役立てられるかも
• クエリキャッシュに頼らない
  • MySQL8では削除済み → 必要ならアプリケーション側でRedisなどを利用する
• インフラチームとの役割分担
  • 「どの層で問題が起きているか」を伝えられると、インフラチームに依頼しやすい

---

📘 次回：第2章 監視と信頼性エンジニアリング
→ tech-high-performance-mysql-ch02