中級 MySQL 完全攻略

① 主キー設計 — AUTO_INCREMENT vs UUID

InnoDB はClustered Index です。つまり主キーの順番でテーブルそのものが物理的に並びます (Section 19 で深掘り)。これにより、主キーの選び方が書き込み性能・ディスク領域・index サイズに直結します。

-- ① 推奨: BIGINT AUTO_INCREMENT
CREATE TABLE users (
  id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email  VARCHAR(255) NOT NULL,
  ...
);

-- ② UUID を使うなら BINARY(16) + v7 (時系列順)
CREATE TABLE events (
  id     BINARY(16) PRIMARY KEY,
  ...
);
INSERT INTO events (id, ...)
  VALUES (UUID_TO_BIN(UUID(), 1), ...);
--                              ↑
--   v1 の TS 部分を先頭にスワップ → B+Tree 末尾挿入になる
--   (8.0+ の UUID_TO_BIN(x, 1) の swap_flag)

② データ型選定 — 小さく・固定長を優先

InnoDB の 16 KB Page にできるだけ多くの行を詰めるのがインデックス性能の鉄則です。必要十分な最小サイズの型を選びます。

③ created_at / updated_at の設計

ほぼ全テーブルに付ける監査カラムです。MySQL では DATETIME と TIMESTAMP のどちらを使うかが古典的論点。

CREATE TABLE users (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL,
  status      TINYINT UNSIGNED NOT NULL DEFAULT 1,
  -- 監査カラム (DATETIME(6) でマイクロ秒精度)
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at  DATETIME(6) NOT NULL
              DEFAULT CURRENT_TIMESTAMP(6)
              ON UPDATE CURRENT_TIMESTAMP(6),
  deleted_at  DATETIME(6) NULL,   -- ソフトデリート
  INDEX idx_created_at (created_at),
  INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

④ 外部キー (FK) — 使うか使わないか

InnoDB は外部キーをサポートしますが、使うか使わないかは組織のポリシーで分かれます。

⑤ 正規化と非正規化 — JSON カラムの使いどころ

MySQL 5.7+ で導入されたJSON 型は、本来正規化すべき情報を 1 カラムにまとめる非正規化テクニックとして使えます (Section 40 で深掘り)。

命名規則 — 後から泣かないために

命名規則は早い段階で決めておくと、10 年後の自分が救われます。「全テーブルで一貫している」が最重要。

設計パターン集 — 中間テーブル・ポリモーフィック・履歴

-- users と roles の M-N 関係
CREATE TABLE users_roles (
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  granted_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (user_id, role_id),     -- 複合 PK
  KEY idx_role_id (role_id),           -- 逆引き用
  CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;
-- PK の左端 user_id で「ある user の roles 一覧」が高速
-- idx_role_id で「ある role の users 一覧」が高速

-- comments が posts にも events にもつくケース
CREATE TABLE comments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  commentable_type VARCHAR(50) NOT NULL,  -- 'Post' or 'Event'
  commentable_id BIGINT UNSIGNED NOT NULL,
  body TEXT,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  KEY idx_commentable (commentable_type, commentable_id)
) ENGINE=InnoDB;
-- ⚠ FK が張れない (動的に参照先が変わる)
-- ⚠ Rails の polymorphic と同じ設計だが MySQL では整合性が DB レベルで保証されない
-- → アプリ層で慎重に / Trigger で整合性チェック

CREATE TABLE orders_audit (
  audit_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  action ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  changed_by VARCHAR(100),
  changed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  old_data JSON,    -- 変更前
  new_data JSON,    -- 変更後
  KEY idx_order_id (order_id),
  KEY idx_changed_at (changed_at)
) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(changed_at)) (
  PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01')),
  PARTITION p2025 VALUES LESS THAN (TO_DAYS('2026-01-01')),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);
-- パーティション化して古いデータの一括削除を高速に (Section 30)
-- TRIGGER で自動記録 or アプリ層で明示的に INSERT

-- 論理削除: deleted_at NULL を非削除扱い
deleted_at DATETIME(6) NULL,
KEY idx_deleted_at (deleted_at)
-- アプリは WHERE deleted_at IS NULL を常に付ける
-- ★ 全インデックスに deleted_at の影響を考慮する必要あり

-- 物理削除 + 別テーブルに退避
CREATE TABLE users_deleted LIKE users;
-- DELETE FROM users WHERE id=X → INSERT INTO users_deleted ... + DELETE
-- ★ users テーブルの肥大化を防げる / FK 整合性は崩れる

-- 選び方:
-- ・GDPR 等で完全削除が必要 → 物理削除 + 別テーブル
-- ・「うっかり戻したい」用途が多い → 論理削除
-- ・両立したいなら、論理削除 + 定期バッチで物理削除

型選定の決定木

「文字列を保存したい」
  ├ 長さ固定？             → CHAR(n) (使わない方が多い)
  ├ 長さ < 255 文字？      → VARCHAR(n)
  └ 本文 / コメント        → TEXT / MEDIUMTEXT / LONGTEXT

「整数を保存したい」
  ├ 0/1 のフラグ           → TINYINT(1)
  ├ ステータス (列挙的)    → TINYINT UNSIGNED
  ├ サロゲートキー          → BIGINT UNSIGNED
  └ 一般的な ID             → INT UNSIGNED

「小数を保存したい」
  ├ 金額                    → DECIMAL(p, s)  ※ 絶対に FLOAT を使わない
  ├ センサーデータ等の近似値 → FLOAT / DOUBLE
  └ パーセント (0-100)      → TINYINT UNSIGNED or DECIMAL(4,2)

「日時を保存したい」
  ├ アプリで UTC 統一前提  → DATETIME(6)
  ├ レガシー TZ 互換が必要  → TIMESTAMP (※ 2038 年問題)
  ├ 日付のみ                 → DATE
  └ 時刻 / 経過時間          → TIME

「半構造化データ」
  ├ 検索しない              → JSON (Section 40)
  ├ 検索する                 → JSON + Generated Column + Index
  └ スキーマが決まる         → 別カラムに正規化

「バイナリ」
  ├ 数 KB まで              → VARBINARY(n) / BLOB
  ├ 数 MB まで              → MEDIUMBLOB
  └ それ以上                 → S3 などに置いて URL を保存

InnoDB Page の構造 (16 KB)

InnoDB の最小 I/O 単位は 16 KB の Page です (デフォルト)。テーブルもインデックスも Undo もすべて 16 KB Page の集合体として保存されます。

Row Format — DYNAMIC / COMPRESSED / COMPACT / REDUNDANT

1 つの Page 内に行 (Record) をどう詰め込むかのフォーマット。8.0 のデフォルトは DYNAMIC です。

Overflow Page (TOAST 相当)

1 行のサイズが Page (16 KB) の半分を超えると、可変長カラム (VARCHAR / TEXT / BLOB / JSON) が別の Overflow Page に追い出されます。PostgreSQL の TOAST に相当する仕組みです。

行のサイズを見る

-- テーブルサイズと行サイズの目安
SELECT
  TABLE_NAME,
  TABLE_ROWS,
  ROUND(DATA_LENGTH / 1024 / 1024, 1)  AS data_MB,
  ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_MB,
  ROUND(DATA_LENGTH / TABLE_ROWS, 1)    AS avg_row_bytes
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY DATA_LENGTH DESC
LIMIT 20;

-- Row Format を確認
SELECT TABLE_NAME, ROW_FORMAT
FROM information_schema.tables
WHERE table_schema = 'myapp';

-- 1 ページあたり何行詰まっているかの目安
-- 16384 byte / avg_row_bytes ≈ 1 Page に詰まる行数

Record の中身 — NULL bitmap と可変長ヘッダ

1 行 (Record) の中身は「可変長ヘッダ + NULL bitmap + 5 byte レコードヘッダ + 実データ」という構造です。

行サイズ上限 — 65,535 byte と Overflow の関係

1 行のサイズには絶対上限 65,535 byte という制約があります (8 KB の Page で半分使う計算)。これは Overflow とは別の制約。

-- ① 列数が多いテーブルで起きやすい
CREATE TABLE wide (
  col1 VARCHAR(255),
  col2 VARCHAR(255),
  ...
  col300 VARCHAR(255)
);
-- ERROR 1118: Row size too large (> 65535)

-- ② 計算式:
--   各 VARCHAR(n) は (n * 4 byte_per_char_utf8mb4) + 2 byte (長さ)
--   utf8mb4 で VARCHAR(255) は 255 * 4 + 2 = 1022 byte
--   65535 / 1022 ≈ 64 列が上限

-- ③ 解決策:
-- A) ROW_FORMAT=DYNAMIC で大きな VARCHAR を Overflow に
ALTER TABLE wide ROW_FORMAT=DYNAMIC;
-- → 20 byte の参照ポインタだけ行内に残る

-- B) TEXT / BLOB に変換 (これも overflow される)
ALTER TABLE wide MODIFY col1 TEXT;

-- C) テーブル分割 (1:1 リレーション)
CREATE TABLE wide_basic   (id BIGINT PRIMARY KEY, col1-50 ...);
CREATE TABLE wide_details (id BIGINT PRIMARY KEY, col51-300 ...);

-- ④ Page あたり「最低 2 行入る必要」 → 16 KB / 2 = 8 KB が実質上限
-- → 65,535 はあくまで理論値・実運用ではもっと小さく抑える

3 部品の関係

🎮 MVCC タイムラインシミュレータ — Repeatable Read の不思議

REPEATABLE READ の中で「なぜ同じ行を 2 回読んでも同じ値が返るのか」を、TX-A (Reader) と TX-B (Writer) の並行シナリオで step-through します。Read View の中身と Undo Log Chain の動きを観察してください。

読み取り判定アルゴリズム (Read View が行を見えるか判定する手順)

# トランザクション T が行 R を読もうとする時の判定:

trx_id = R.DB_TRX_ID  # 行を最後に書いた TX の ID

if trx_id < view.m_up_limit_id:
    → ✓ 見える (Read View 作成より前にコミット済み)

elif trx_id >= view.m_low_limit_id:
    → ✗ 見えない (Read View 作成より後の TX)
    → DB_ROLL_PTR を辿って前バージョンを取得

elif trx_id in view.m_ids:
    → ✗ 見えない (Read View 作成時点でまだアクティブだった TX)
    → DB_ROLL_PTR を辿って前バージョンを取得

else:
    → ✓ 見える (m_up_limit_id ≤ trx_id < m_low_limit_id かつ m_ids に含まれない)

# Undo を遡る場合、再帰的に同じ判定を繰り返して
# 最終的に自分に見えるべきバージョンを再構築する

例: 2 つの TX が同じ行を読み書きする

具体例で挙動を追います。REPEATABLE READ (MySQL のデフォルト) を仮定。

時刻  TX_A (REPEATABLE READ)        TX_B (REPEATABLE READ)
─────────────────────────────────────────────────────────────────
T1    START TRANSACTION;
      SELECT name FROM u WHERE id=1; → "alice"  (V0)
      # ★ ここで Read View が作られる
T2                                    START TRANSACTION;
                                      UPDATE u SET name="bob"
                                        WHERE id=1;
                                      # → V0 を Undo へ、現行を V1 に
                                      COMMIT;
T3    SELECT name FROM u WHERE id=1; → "alice"  (V0)
      # ★ TX_A は Read View が T1 のままなので
      #    DB_TRX_ID(TX_B) > Read View 可視範囲 → Undo を辿る
      COMMIT;
T4    START TRANSACTION;
      SELECT name FROM u WHERE id=1; → "bob"  (V1)
      # ★ 新しい Read View で TX_B は可視範囲内

REPEATABLE READ vs READ COMMITTED

MySQL のデフォルトは REPEATABLE READ。PostgreSQL や Oracle のデフォルトは READ COMMITTED。違いは「いつ Read View を作るか」です。

MVCC の代償 — 長時間トランザクション問題

MVCC を実現するために旧バージョンを Undo Log に蓄える 必要があります。アクティブな最古の Read View より新しい undo は捨てられません (= Purge できない)。

そのため、長時間トランザクションを放置すると、Undo が肥大化して disk・メモリを圧迫 し始めます。最悪、ディスク満杯で mysqld が止まります。

-- ① History List Length (HLL) を見る — Undo が溜まっている指標
SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS セクション内の "History list length" を見る
-- 100万以上になっていたら要注意

-- ② Performance Schema でも確認
SELECT NAME, COUNT FROM information_schema.innodb_metrics
WHERE NAME='trx_rseg_history_len';

-- ③ アクティブな長時間 TX を特定
SELECT
  trx_id,
  trx_state,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
  trx_query,
  trx_mysql_thread_id
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

Redo Log と Binary Log の役割分担

Redo Log — クラッシュリカバリの仕組み

Redo Log は 「Buffer Pool 上で変更した内容を、ディスクに反映できる前にクラッシュしても失わないため」 に存在します。WAL (Write-Ahead Logging) の MySQL 版です。

innodb_flush_log_at_trx_commit — 永続化の強度

Redo Log の永続化強度を決めるのが innodb_flush_log_at_trx_commit です。0 / 1 / 2 の 3 段階で、性能と耐久性のトレードオフが変わります。

Binary Log — レプリケーション + PITR の基盤

Binary Log は「サーバ層」のログです。InnoDB だけでなく MyISAM などすべてのエンジンの変更が記録されます。役割は 2 つ。

Two-Phase Commit (XA) — Redo と Binary の同期

ここで疑問: 「InnoDB の Redo Log と Server 層の Binary Log って、別々に書かれるなら、片方だけ書けて片方が書けてなかったらどうなる?」 答えは Two-Phase Commit (内部 XA)。

COMMIT が呼ばれたとき:

Phase 1 (Prepare):
  ① InnoDB が Redo Log に "PREPARE" マークを書いて fsync
  ② Binary Log にトランザクション全体を書いて sync_binlog 設定に従い fsync

Phase 2 (Commit):
  ③ InnoDB が Redo Log に "COMMIT" マークを書いて fsync

クラッシュリカバリ時:
  - PREPARE + COMMIT がある → 正常完了 → 何もしない
  - PREPARE のみ・binlog にも記録あり → COMMIT を補完 (前進)
  - PREPARE のみ・binlog にも記録なし → ROLLBACK (後退)

→ InnoDB と binlog が必ず同じ状態になることが保証される

LSN (Log Sequence Number) — Redo Log の住所

LSN は Redo Log の中での「先頭から何 byte 目か」を表す単調増加カウンタです。すべての変更ログには LSN が振られ、Page にも「この Page を最後に変更したときの LSN」が記録されます。これが InnoDB の時間軸。

-- LSN の状態を見る
SHOW ENGINE INNODB STATUS\G

# LOG セクション
Log sequence number          1234567890   ← 現在の LSN (Buffer まで反映済)
Log buffer assigned up to    1234567890   ← Log Buffer に割当て済み
Log buffer completed up to   1234567000
Log written up to            1234566500   ← ファイルに書き出し済
Log flushed up to            1234566400   ← fsync 済 (= Durability の境界)
Pages flushed up to          1234560000   ← データファイルに反映済
Last checkpoint at           1234555000   ← Sharp Checkpoint の起点

-- 値の意味:
-- Log sequence number     = 今 Buffer Pool で「ここまで変更した」
-- Log flushed up to       = ディスクで「ここまで永続化された」
-- Last checkpoint at      = データファイルに「ここまで書き戻した」

-- クラッシュリカバリは:
-- Last checkpoint LSN から Log flushed LSN までを Redo Log から再適用

-- 差分が大きい = recovery 時間が長い → checkpoint を進める設定が必要

① Doublewrite Buffer — Torn Page を防ぐ

OS のディスク書き込みは原子的とは限りません。InnoDB Page は 16 KB だが、OS のファイルシステム block は通常 4 KB なので、16 KB の書き込みは 4 つの 4 KB 書き込みに分解されます。途中でクラッシュすると4 KB だけ更新された壊れた Page (= Torn Page) が残ります。

Doublewrite Buffer はこれを防ぎます。「同じデータを 2 回書く」ことで、片方が壊れても他方で復元できる仕組み。

② Change Buffer — Secondary Index 書込の高速化

Secondary Index (PK 以外の index) への変更は、対象のインデックス Page を Buffer Pool に読み込んでから書き換える必要があります。Buffer Pool にない場合は毎回ディスクから読まないといけない。

Change Buffer はこれを延期します。Secondary Index への変更を一時的にバッファに溜めて、後で対象 Page が他の理由で Buffer Pool に乗ったときにまとめて反映します (= Merge)。

③ Adaptive Hash Index (AHI) — B+Tree を Hash で高速化

B+Tree は深さ 3〜4 段が普通で、各段で二分探索を行います。同じ key で何度も検索すると同じ Page を辿るので無駄です。InnoDB は頻繁にアクセスされる Page を自動で Hash 化して O(1) に近い参照にできます。これが Adaptive Hash Index。

-- AHI の状態を見る
SHOW ENGINE INNODB STATUS\G

# INSERT BUFFER AND ADAPTIVE HASH INDEX セクション
Hash table size 4425293, node heap has 31 buffer(s)
139.21 hash searches/s, 215.55 non-hash searches/s
# hash / total = 39% → Hash 経由の検索の割合

-- 設定 (8.0 デフォルト OFF・5.x は ON)
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index_parts';  -- 8 (分割数)

8.0.20+ — Doublewrite Buffer のセパレートファイル化

5.x までの Doublewrite Buffer は ibdata1 内に固定で置かれていました。8.0.20 から独立したファイルに分離され、並列性とチューニング可能性が大幅に向上しました。

-- ファイル名: #ib_<page_size>_<n>.dblwr (data dir 配下)
ls -la /var/lib/mysql/'#innodb_redo'
ls -la /var/lib/mysql/'#ib_16384_0.dblwr'   -- 16K page サイズ用 #0

-- 設定パラメータ (8.0.20+ で追加)
SHOW VARIABLES LIKE 'innodb_doublewrite%';
-- innodb_doublewrite                  ON       (有効)
-- innodb_doublewrite_dir              (空)    (data dir 配下に作る)
-- innodb_doublewrite_files            2        (ファイル数 = bp_instances * 2)
-- innodb_doublewrite_pages            128      (1 ファイルあたりの page 数)
-- innodb_doublewrite_batch_size       0       (auto)

-- 別ディスクに分離して並列度を上げる
[mysqld]
innodb_doublewrite_dir = /mnt/fast_ssd/dwb/    # 別 SSD に置く
innodb_doublewrite_files = 4
innodb_doublewrite_pages = 256                 # 大きいと flush バッチ大

-- 8.0.21+ では DETECT_AND_RECOVER モードも追加 (低レイテンシ志向)
innodb_doublewrite = DETECT_AND_RECOVER

Purge の流れ

History List Length (HLL) — Purge の遅れ指標

HLL は「まだ purge されていない undo の数」です。これが本番の最重要監視指標のひとつ。

-- ① HLL を見る方法 (3 通り)
-- (a) SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB STATUS\G
-- TRANSACTIONS セクション内の "History list length 5829"

-- (b) information_schema
SELECT NAME, COUNT
FROM information_schema.innodb_metrics
WHERE NAME='trx_rseg_history_len';

-- (c) Performance Schema
SELECT *
FROM performance_schema.global_status
WHERE VARIABLE_NAME='Innodb_history_list_length';

-- ② アクティブな長時間 TX を特定
SELECT
  trx_id,
  trx_state,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC
LIMIT 10;

-- ③ そのスレッドを kill する (最終手段)
KILL <thread_id>;

Undo Tablespace の管理 (8.0+)

8.0 から Undo Tablespace は 動的に管理 されるようになりました。それまでは ibdata1 内に閉じ込められていて、肥大すると mysqld を停止 → ibdata1 再構築という大手術が必要だったのが、ALTER で増やしたり減らしたりできるようになりました。

-- ① 現在の Undo Tablespace 一覧
SELECT TABLESPACE_NAME, FILE_NAME, INITIAL_SIZE, MAXIMUM_SIZE
FROM information_schema.innodb_tablespaces
WHERE FILE_NAME LIKE '%undo%';

-- ② 新しい Undo Tablespace を追加
CREATE UNDO TABLESPACE undo_003 ADD DATAFILE 'undo_003.ibu';

-- ③ Undo Tablespace を非アクティブにして縮小
ALTER UNDO TABLESPACE undo_001 SET INACTIVE;
-- → 既存の undo は purge されるまで残る
-- → Purge 完了後、自動で truncate される

-- ④ 完全削除
ALTER UNDO TABLESPACE undo_001 SET INACTIVE;
-- (purge 完了を待つ)
DROP UNDO TABLESPACE undo_001;

長時間 TX を防ぐアプリ設計

Purge のチューニングパラメータ

Purge が追いつかない (HLL が増える) ときに触るパラメータを整理します。

① Parse — 字句解析と構文解析

SQL 文字列が来たら、まず Lexer (字句解析器) がトークン列に分解し、Parser (構文解析器) が AST (抽象構文木) を組み立てます。MySQL は Bison ベースで実装されています。

SELECT u.name, COUNT(*) FROM users u WHERE u.country = 'JP' GROUP BY u.id;

# Lexer の出力 (トークン)
[SELECT] [IDENT:u] [.] [IDENT:name] [,]
[FUNC:COUNT] [(] [*] [)]
[FROM] [IDENT:users] [IDENT:u]
[WHERE] [IDENT:u] [.] [IDENT:country] [=] [STR:'JP']
[GROUP] [BY] [IDENT:u] [.] [IDENT:id] [;]

# Parser の出力 (AST)
SelectStmt
├─ Projection
│   ├─ TableRef("u").name
│   └─ Aggregate(COUNT, *)
├─ FromList
│   └─ TableRef("users", alias="u")
├─ Where
│   └─ Eq(TableRef("u").country, 'JP')
└─ GroupBy
    └─ TableRef("u").id

② Resolve — 名前解決と権限チェック

AST の中の名前 (テーブル名・列名・関数名) を実体に解決します。同時に権限チェックも走り、VIEW があれば展開します。

③ Optimize — 実行プランの決定

このステージがクエリ性能の 9 割を決めると言って過言ではありません。Optimizer は次のことを決めます。

④ Execute — プランを実際に走らせる

Optimizer が決めたプランをiterator (Volcano モデル) として組み立て、ルートから Read() を呼んで 1 行ずつ取り出します (Section 05 参照)。

このステージで起きる主なコストは:

⑤ Send — 結果送信

結果セットをパケット化してネットワーク経由でクライアントへ送ります。1 パケット = max_allowed_packet (デフォルト 64 MB)。大量行は連続パケットに分割。

-- 結果セットがでかすぎてつまづく場合
SET max_allowed_packet = 256 * 1024 * 1024;   -- 256 MB

-- アプリ側でも合わせる
mysql --max_allowed_packet=256M ...

-- BLOB を返すとき特にハマりやすい
-- アプリ側のドライバ (JDBC / mysqli) でも同じ値を設定

Prepared Statement と Plan Cache

同じ SQL を何度も投げる場合、毎回 Parse → Resolve → Optimize を走らせるのは無駄です。Prepared Statement で実行プランをキャッシュできます (Section 05 参照)。

-- Server-side PS の効果を見る
SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count';
SHOW GLOBAL STATUS LIKE 'Com_stmt_prepare';
SHOW GLOBAL STATUS LIKE 'Com_stmt_execute';

-- Com_stmt_execute / Com_stmt_prepare が大きい → PS が効いている
-- Com_stmt_execute が小さい → PS の旨味なし (毎回 prepare している)

EXPLAIN の 4 形式

例題で 4 形式を比較

-- 例題のクエリ
EXPLAIN SELECT u.name, COUNT(o.id) AS cnt
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'JP' AND o.status = 'paid'
GROUP BY u.id
ORDER BY cnt DESC
LIMIT 10;

-- ① 古典的 EXPLAIN (表)
+----+-------------+-------+--------+---------------+----------+---------+---------------+------+-------------------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref           | rows | Extra                   |
+----+-------------+-------+--------+---------------+----------+---------+---------------+------+-------------------------+
| 1  | SIMPLE      | u     | ref    | PRIMARY,...   | idx_co   | 4       | const         | 1234 | Using temporary; Using filesort |
| 1  | SIMPLE      | o     | ref    | idx_user_id   | idx_uid  | 8       | u.id          | 5    | Using where             |
+----+-------------+-------+--------+---------------+----------+---------+---------------+------+-------------------------+

-- ② EXPLAIN FORMAT=TREE
-> Limit: 10 row(s)
   -> Sort: cnt DESC, limit input to 10 rows per chunk
      -> Group aggregate: count(o.id)
         -> Nested loop inner join (cost=345.6 rows=617)
            -> Index lookup on u using idx_country (country='JP')  (cost=12.3 rows=1234)
            -> Filter: (o.status = 'paid')
               -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.5 rows=5)

-- ③ EXPLAIN FORMAT=JSON (抜粋)
{
  "query_block": {
    "select_id": 1,
    "cost_info": { "query_cost": "345.65" },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      ...
    }
  }
}

-- ④ EXPLAIN ANALYZE (実行する)
-> Limit: 10 row(s)  (actual time=23.456..23.467 rows=10 loops=1)
   -> Sort: cnt DESC  (actual time=23.450..23.452 rows=10 loops=1)
      -> Group aggregate: count(o.id)  (actual time=0.523..23.398 rows=782 loops=1)
         -> Nested loop inner join  (actual time=0.045..21.234 rows=4567 loops=1)
            -> Index lookup on u  (actual time=0.039..3.892 rows=1234 loops=1)
            -> Filter: (o.status = 'paid')  (actual time=0.014..0.019 rows=3.7 loops=1234)
               -> Index lookup on o  (actual time=0.011..0.014 rows=5 loops=1234)

古典的 EXPLAIN — type 列の読み方

表形式 EXPLAIN の「type」列 がアクセス方法を表します。良い順に並べると:

Extra 列で見る悪い兆候

EXPLAIN ANALYZE — 実プランの実測

8.0.18+ の EXPLAIN ANALYZE はクエリを実際に実行 して計測値を返します。Optimizer の見積もり (rows) と実測 (actual rows) の差が分かるのがありがたい。

-> Index lookup on u using idx_country  (actual time=0.039..3.892 rows=1234 loops=1)
                                          ↑ 最初の 1 行  ↑ 全体     ↑ 実rows ↑ 呼ばれた回数

-- 読み方:
-- - cost  : Optimizer が見積もったコスト (絶対値ではなく相対指標)
-- - rows  : 見積もった出力行数
-- - actual time=N..M  : 最初の行が取れるまで N ms、全部処理に M ms
-- - actual rows=R     : 実際に返した行数
-- - loops=L           : このノードが呼ばれた回数 (Nested Loop で重要)

-- ★ rows と actual rows が大きく違う → 統計情報が古い疑い
ANALYZE TABLE users;  -- 統計を更新

rows と filtered — Optimizer の見積もり精度を読む

表形式 EXPLAIN の rows と filtered 列は「Optimizer が何件返ると見積もっているか」を示します。これがズレていると変なプランを選びます。

+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+-------+
| 1  | SIMPLE      | u     | range  | idx_age       | idx_age | 4       | NULL | 1000 | 33.33    | ...   |
+----+-------------+-------+--------+---------------+---------+---------+------+------+----------+-------+

# 読み方:
# rows      = idx_age で範囲スキャンしたら 1000 行 ヒットすると見積もり
# filtered  = うち 33.33% (= 333 行) が WHERE 条件全体を通ると見積もり

# 実際の処理行数 = rows × filtered / 100 = 333 行

# ★ filtered が 100 に近い  → WHERE が ほぼ全件通る = index が効いている
# ★ filtered が 1 に近い    → WHERE で大量に除外 = 効率悪い (別 index 検討)

# 統計を更新して見積もり精度を上げる
ANALYZE TABLE users;

# Histogram で精度をさらに上げる (8.0+)
ANALYZE TABLE users UPDATE HISTOGRAM ON age WITH 32 BUCKETS;

EXPLAIN FORMAT=JSON の詳細 — cost を数値で見る

JSON 形式は出力量が多いが、Optimizer の判断材料がすべて見えます。実務で「なぜこの index を選ばなかったのか」を追うときに使う形式。

EXPLAIN FORMAT=JSON
SELECT u.name, COUNT(o.id)
FROM users u JOIN orders o ON o.user_id=u.id
WHERE u.country='JP' GROUP BY u.id\G

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "345.65"        ← クエリ全体の総コスト
    },
    "grouping_operation": {
      "using_temporary_table": true,
      "using_filesort": true,        ← 一時表 + ソート発生
      "nested_loop": [
        {
          "table": {
            "table_name": "u",
            "access_type": "ref",     ← ref (index 等価検索)
            "key": "idx_country",
            "rows_examined_per_scan": 1234,   ← 走査見積もり
            "rows_produced_per_join": 1234,
            "filtered": "100.00",      ← 100% = index で全部絞れた
            "cost_info": {
              "read_cost": "12.30",
              "eval_cost": "0.12",
              "prefix_cost": "12.42",
              "data_read_per_join": "192K"
            },
            "used_columns": ["id", "name", "country"]
          }
        },
        {
          "table": {
            "table_name": "o",
            "access_type": "ref",
            "key": "idx_user_id",
            "rows_examined_per_scan": 5,
            "rows_produced_per_join": 6170,
            "filtered": "100.00",
            "cost_info": { "read_cost": "61.70", "eval_cost": "617.00", ... }
          }
        }
      ]
    }
  }
}

# 読み所:
# - query_cost が他プランより高い → なぜそれを選んだか trace を見る
# - rows_examined_per_scan と実際の rows が違う → ANALYZE TABLE

コスト計算の式 (簡略)

総コスト = I/O コスト + CPU コスト

I/O コスト:
  Page 読込数 × COST_PER_PAGE_FETCH

CPU コスト:
  返す行数 × COST_PER_ROW_EVALUATE
+ 比較回数 × COST_PER_COMPARE
+ ソート行数 × COST_PER_SORT

定数は mysql.server_cost / mysql.engine_cost に保存:

SELECT * FROM mysql.engine_cost;
+----------+--------------------+----------+
| name     | default_value     | cost     |
+----------+--------------------+----------+
| io_block_read_cost      | 1.0000 | NULL  |
| memory_block_read_cost | 0.2500 | NULL  |  ← Buffer Pool ヒット時
+----------+--------------------+----------+

SELECT * FROM mysql.server_cost;
+--------------------------------+-------+
| name                          | cost  |
+--------------------------------+-------+
| row_evaluate_cost             | 0.10 |
| key_compare_cost              | 0.05 |
| memory_temptable_create_cost | 1.00 |
| memory_temptable_row_cost     | 0.10 |
| disk_temptable_create_cost    | 20.0 |
| disk_temptable_row_cost       | 0.50 |
+--------------------------------+-------+

統計情報 — index の cardinality

Optimizer は各 index のカーディナリティ (異なる値の数) を頼りにプランを選びます。これが古いと変なプランを選びます。

-- 統計を見る
SHOW INDEX FROM users;
-- Cardinality 列が「異なる値の数」の推定
-- これが小さい = 重複が多い (例: gender 列)
-- これが大きい = ユニークに近い (例: email 列)

-- 統計が古いと感じたら手動 ANALYZE
ANALYZE TABLE users;
ANALYZE TABLE orders;

-- 自動 ANALYZE は通常 ON (Section 14 参照)
SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';   -- ON
SHOW VARIABLES LIKE 'innodb_stats_persistent';    -- ON
SHOW VARIABLES LIKE 'innodb_stats_sample_pages';  -- 20

Histogram — カラム値の分布統計 (8.0+)

8.0 で Histogram が追加されました。index がない列でも値の分布を Optimizer に教えられます。

-- ① Histogram を作る (列に index がなくても OK)
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 16 BUCKETS;

-- ② 確認
SELECT histogram FROM information_schema.column_statistics
WHERE table_name='orders' AND column_name='status';

-- ③ 削除
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- 使いどころ: 偏った分布で index を使うかの判断が変わる場合
-- 例: status='paid' が 95%, 'pending' が 5% のような偏り
-- → status='pending' なら index 範囲スキャンが速い
-- → status='paid' なら Full Table Scan のほうが速い

Optimizer Hints — プランを強制する

Optimizer Hints でプランを強制できます (8.0 でかなり強化)。/*+ ... */ 形式。

-- ① 特定の index を使う / 使わない
SELECT /*+ INDEX(u idx_country) */ * FROM users u WHERE country='JP';
SELECT /*+ NO_INDEX(u idx_country) */ * FROM users u WHERE country='JP';

-- ② JOIN 方式を指定 (Hash Join を強制)
SELECT /*+ JOIN_ORDER(u, o) BNL(u, o) */
FROM users u JOIN orders o ON ...;

-- ③ Subquery を Semijoin に / Materialization に
SELECT /*+ SEMIJOIN(MATERIALIZATION) */ ...;
SELECT /*+ NO_SEMIJOIN() */ ...;

-- ④ Join Order を強制
SELECT /*+ JOIN_ORDER(o, u) */ FROM users u JOIN orders o ...;

-- ⑤ クエリ単位のシステム変数オーバーライド (8.0.4+)
SELECT /*+ SET_VAR(sort_buffer_size=16M) */ * FROM big_table ORDER BY x;

-- ⑥ 古い "INDEX HINT" (USE INDEX / FORCE INDEX / IGNORE INDEX)
SELECT * FROM users FORCE INDEX (idx_country) WHERE country='JP';
SELECT * FROM users IGNORE INDEX (idx_email) WHERE email LIKE '%@example.com';

optimizer_switch — Optimizer の挙動フラグ集

optimizer_switch はOptimizer 内部のアルゴリズム ON/OFF を切り替える フラグ群。本番でデフォルト挙動が変わったときの diagnostic に使います。

SELECT @@optimizer_switch\G

# 8.0 のデフォルト主要フラグ:
index_merge=on                  # 複数 index を組み合わせて使う
index_merge_union=on
index_merge_intersection=on
index_condition_pushdown=on     # ICP (後述)
mrr=on                          # Multi-Range Read (後述)
mrr_cost_based=on
batched_key_access=off          # BKA Join
hash_join=on                    # 8.0.18+
semijoin=on                     # サブクエリを Join に
materialization=on
loosescan=on
firstmatch=on
condition_fanout_filter=on
derived_merge=on
derived_condition_pushdown=on   # 8.0.22+ — 派生表に WHERE をプッシュダウン

# 個別 ON / OFF
SET SESSION optimizer_switch = 'mrr=off';

# 一時的に試したい時はクエリヒントで
SELECT /*+ SET_VAR(optimizer_switch='mrr=off') */ ...

ICP (Index Condition Pushdown) — Index 段階で WHERE を絞る

通常: index で行を見つけ → 行を読んで WHERE 評価。ICP 有効: index に存在する列に関する WHERE 条件を、行を読む前に index ノードで評価して不要な行読みを省く。

-- 例: idx_country_age = (country, age) という複合 index がある
SELECT * FROM users
WHERE country='JP' AND age > 30 AND name LIKE 'A%';

# ICP なし:
# 1. idx_country_age で country='JP' の全行を index で見つける (1000 行)
# 2. 各 PK で Clustered Index を引いて行を読む  ← 1000 回 I/O
# 3. age > 30 と name LIKE 'A%' を評価
# 4. 該当行を返す (100 行)

# ICP あり:
# 1. idx_country_age で country='JP' の行を index で走査
# 2. ★ index に age が含まれているので、age > 30 を index ノードで評価
#    → age <= 30 の行は最初から読み飛ばす  ← 100 回 I/O
# 3. 残った 100 行を Clustered Index で読む
# 4. name LIKE 'A%' を評価

# EXPLAIN の Extra に "Using index condition" → ICP 有効

# 確認: ICP を一時無効化
SET SESSION optimizer_switch = 'index_condition_pushdown=off';
EXPLAIN SELECT * FROM users WHERE country='JP' AND age > 30;
-- Extra: Using where  (ICP は使われていない)

MRR (Multi-Range Read) — 範囲スキャンを順番アクセス化

Secondary Index で範囲スキャン → 各行を PK で引く、というアクセスはPK が飛び飛び なのでランダム I/O になります。MRR は取得すべき PK を一旦ソート してから読むことで、シーケンシャル I/O に近づけます。

-- MRR なし:
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
# 1. idx_created_at で範囲スキャン → PK 一覧 (1000 件)
# 2. 各 PK を順番に Clustered Index で引く
#    → PK は時系列順に飛ぶので毎回ランダム I/O

-- MRR あり:
# 1. idx_created_at で範囲スキャン → PK 一覧 (1000 件)
# 2. ★ PK をソート (read_rnd_buffer_size 上で)
# 3. ソート済み PK 順に Clustered Index を読む
#    → PK 順 = ディスク順なのでシーケンシャル I/O に近づく

# EXPLAIN の Extra に "Using MRR"

# 設定
SET SESSION optimizer_switch = 'mrr=on,mrr_cost_based=off';
# mrr_cost_based=off にすると Optimizer のコスト判断ではなく強制 ON

# read_rnd_buffer_size を増やすと多くの PK をソートできる
SET SESSION read_rnd_buffer_size = 1024 * 1024;  -- 1 MB

Loose Index Scan — GROUP BY を index で一気に

特定条件下では、GROUP BY のためにindex の全ノードを舐めずに飛び飛びに読んで結果を得る最適化。

-- 例: idx_country_city = (country, city) という複合 index
SELECT country, MAX(city) FROM users GROUP BY country;

# Tight Index Scan (通常):
# - 全 index エントリを順に読む → MAX を計算

# Loose Index Scan:
# - 各 country の先頭エントリだけを読む
#   ('JP', 'Aichi'), ('JP', 'Akita'), ... の中から JP の最後を直接ジャンプ
# - 大規模で 100 倍以上速いことがある

# EXPLAIN の Extra に "Using index for group-by"

# 適用される条件 (制約強い):
# 1) 集約関数は MIN / MAX / COUNT(DISTINCT) のみ
# 2) GROUP BY 列が複合 index の左端から連続している
# 3) WHERE は index 列の等価条件のみ

インデックス種別の比較

使い分けマップ — どんなクエリにどの index?

Index Merge — 複数 index を組み合わせる

Optimizer は時々複数の index を同時に使って 結果を合成します。WHERE col1 = ? OR col2 = ? や複数条件の AND で発火します。

-- idx_email と idx_phone が別々にある
SELECT * FROM users
WHERE email = 'a@e.com' OR phone = '090-1234-5678';

-- EXPLAIN: type=index_merge, Extra=Using union(idx_email,idx_phone)
-- → 両方の index で見つけて結果を UNION

-- 3 種類の Index Merge:
-- index_merge_union:        OR 条件で結果を合成 (重複除去)
-- index_merge_intersection: AND 条件で交差を取る
-- index_merge_sort_union:    PK でソートしてから合成

Index を作るべきでない時 — トレードオフ

Index は書き込みコストを増やすことを忘れがち。常に「効果があるか」を判断してから追加します。

Index のサイズを計算する

-- ① テーブル + index のサイズを見る
SELECT
  TABLE_NAME,
  ROUND(DATA_LENGTH / 1024 / 1024, 1)  AS data_MB,
  ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_MB,
  ROUND((INDEX_LENGTH / DATA_LENGTH) * 100, 1) AS index_pct
FROM information_schema.tables
WHERE table_schema='myapp'
ORDER BY INDEX_LENGTH DESC LIMIT 10;

-- ② 個別 index のサイズ
SELECT
  STAT_NAME,
  STAT_VALUE,
  ROUND(STAT_VALUE * 16 / 1024, 1) AS size_MB   -- 16K Page × Page 数
FROM mysql.innodb_index_stats
WHERE database_name='myapp' AND table_name='users'
  AND STAT_NAME='size';

-- 目安:
-- ・index_pct > 100% → index が data より大きい (要見直し)
-- ・index 全体が Buffer Pool に乗らない → index が常時 disk hit
-- ・Secondary index 1 件あたり = PK 値 + key 値 + 5-6 byte の overhead

Clustered Index = テーブルそのもの

カバリングインデックス — Secondary Index だけで完結

Secondary Index で引いて、必要なカラムがすべてその index 自体に含まれて いれば、PK 側を引かなくて済みます (= Covering Index)。EXPLAIN の Extra に Using index と出ます。

-- ① idx_email = (email, name) という複合 index を作る
CREATE INDEX idx_email_name ON users (email, name);

-- ② name しか欲しくないクエリ
SELECT name FROM users WHERE email = 'al@ex.com';
-- → idx_email_name の葉に email + name + PK が入っている
-- → name もそこから取れる → Clustered Index を引かなくて済む
-- EXPLAIN: Extra="Using index"  (高速)

-- 一方
SELECT name, address FROM users WHERE email = 'al@ex.com';
-- → idx_email_name に address は入っていない
-- → Clustered Index を引いて address を取得 (2 段階探索)

ページ分割 — INSERT で起きること

16 KB Page が満杯になると、InnoDB はページ分割 (Page Split) を行います。これがコストの正体。

ページ分割 (Page Split) の絵 — INSERT で起きること

16 KB Page が満杯になったときに何が起きるか。「ランダム PK は遅い」の理由がこの絵で分かります。

FULLTEXT — 全文検索

InnoDB の FULLTEXT は転置インデックス (inverted index) の構造を持ち、文章を単語に分解 (Tokenize) して「単語 → その単語が出てくるドキュメント ID 一覧」を index に持ちます。

-- ① FULLTEXT index を作る
CREATE TABLE articles (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT KEY ft_title_body (title, body) WITH PARSER ngram
) ENGINE=InnoDB CHARSET=utf8mb4;

-- ② 検索
SELECT id, title, MATCH(title, body) AGAINST('MySQL') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL')
ORDER BY score DESC LIMIT 10;

-- ③ Boolean Mode (AND / OR / NOT)
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL +index -PostgreSQL' IN BOOLEAN MODE);

-- ④ 自然言語モード (デフォルト) はランキング付き
-- Boolean モードは厳密な条件指定

日本語の全文検索 — ngram parser

MySQL デフォルトの parser はスペース区切りを前提とした英語向け。日本語はそのままでは index されないので、WITH PARSER ngram を指定します。

Spatial — 地理空間 (R-Tree)

地図上の「この範囲に含まれる点を全部取得」 のようなクエリは B+Tree では困難。Spatial Index は R-Tree で実装され、矩形のネストで範囲検索を効率化します。

-- ① テーブル定義 (8.0+ では SRID 必須)
CREATE TABLE stores (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100),
  location POINT NOT NULL SRID 4326,   -- WGS84 (緯度経度)
  SPATIAL INDEX idx_location (location)
) ENGINE=InnoDB;

-- ② INSERT
INSERT INTO stores VALUES
  (1, 'Tokyo Tower', ST_GeomFromText('POINT(35.6586 139.7454)', 4326));

-- ③ 範囲検索 (東京駅から 5km 以内)
SELECT id, name,
  ST_Distance_Sphere(location, ST_GeomFromText('POINT(35.6812 139.7671)', 4326)) AS dist_m
FROM stores
WHERE ST_Within(
  location,
  ST_Buffer(ST_GeomFromText('POINT(35.6812 139.7671)', 4326), 0.05)
);

-- ④ K-Nearest Neighbor (最寄り 10 件)
SELECT id, name
FROM stores
ORDER BY ST_Distance_Sphere(
  location,
  ST_GeomFromText('POINT(35.6812 139.7671)', 4326)
) ASC LIMIT 10;

① Prefix Index — 文字列の先頭 N 文字だけ

-- email カラム (VARCHAR(255)) の先頭 16 文字だけで index を作る
CREATE INDEX idx_email_prefix ON users (email(16));

-- これで OK だけど…
-- ・index サイズが 1/16 に
-- ・等価検索 (email = ?) は実用上問題なし
-- ・LIKE 'al%' のような前方一致もそのまま効く
-- ・ただし ORDER BY email では Using filesort が出ることがある

② Functional Index (8.0.13+) — 式の結果を index

-- ① LOWER(email) で大文字小文字無視の検索を高速化
CREATE INDEX idx_email_lower ON users ((LOWER(email)));

-- 効くクエリ
SELECT * FROM users WHERE LOWER(email) = 'al@ex.com';

-- ② JSON 内の値を index
CREATE INDEX idx_attr_color
  ON products ((CAST(attr->>'$.color' AS CHAR(20))));

-- ③ 日付からの抽出
CREATE INDEX idx_year ON orders ((YEAR(created_at)));
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

③ Descending Index (8.0.14+) — DESC で並ぶ index

-- ① 降順 index
CREATE INDEX idx_created_desc ON orders (created_at DESC);

-- 効く: ORDER BY created_at DESC LIMIT 10
-- → 末尾から逆順走査で済む。Using filesort が消える

-- ② 複合で混合
CREATE INDEX idx_country_created
  ON orders (country ASC, created_at DESC);

-- 効く: WHERE country='JP' ORDER BY created_at DESC LIMIT 10

④ 複合 (Composite) Index — 左端優先のルール

複数カラムを束ねた index は「左端から順に」使われます。これを 「leftmost prefix」ルールと呼びます。

CREATE INDEX idx_cc ON orders (country, city, created_at);

-- ○ 効く: 左端から順に使える
WHERE country='JP'                                       -- ◎
WHERE country='JP' AND city='Tokyo'                       -- ◎
WHERE country='JP' AND city='Tokyo' AND created_at > ?    -- ◎

-- △ 部分的に効く
WHERE country='JP' AND created_at > ?                     -- country のみ index、created_at は filter

-- × 効かない
WHERE city='Tokyo'                                       -- 左端 country がないので使われない
WHERE created_at > ?                                     -- 同上

⑤ Multi-Value Index (8.0.17+) — JSON 配列を index

JSON 配列の各要素を個別の index エントリ として index する仕組み。「配列内のいずれかに該当値がある」検索が高速になります。

-- JSON 配列を持つテーブル
CREATE TABLE articles (
  id BIGINT PRIMARY KEY,
  title VARCHAR(200),
  tags JSON,    -- ["mysql", "database", "8.0"]
  INDEX idx_tags ((CAST(tags->'$.tags[*]' AS CHAR(50) ARRAY)))
  --                                              ↑ ARRAY が肝
);

INSERT INTO articles VALUES
  (1, 'MySQL 8.0', JSON_OBJECT('tags', JSON_ARRAY('mysql', 'database'))),
  (2, 'PostgreSQL', JSON_OBJECT('tags', JSON_ARRAY('postgres', 'database')));

-- 配列に "database" を含む記事を検索 — Multi-Value Index が使われる
SELECT * FROM articles
WHERE 'database' MEMBER OF (tags->'$.tags');

-- IN で複数値を OR
SELECT * FROM articles
WHERE JSON_OVERLAPS(tags->'$.tags', JSON_ARRAY('mysql', 'postgres'));

-- すべてを含む
SELECT * FROM articles
WHERE JSON_CONTAINS(tags->'$.tags', JSON_ARRAY('mysql', 'database'));

# EXPLAIN
+----+-------------+----------+-----+----------+--------+
| id | type        | table    | key | rows     | Extra  |
+----+-------------+----------+-----+----------+--------+
| 1  | range       | articles | idx_tags | 1 | Using where; ... |
+----+-------------+----------+-----+----------+--------+

Invisible Index — "外す前に試す"

index を実際には残したまま、Optimizer から見えなくする機能。消したらクエリが遅くなるかもという不安を、本番で安全に検証できます。

-- ① 既存 index を見えなくする
ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

-- → Optimizer は idx_email を無視してプランを立てる
-- → 重要クエリのレイテンシを観察
-- → 大丈夫そう → 本当に DROP
ALTER TABLE users DROP INDEX idx_email;

-- → 駄目だったら戻す
ALTER TABLE users ALTER INDEX idx_email VISIBLE;

-- ② 新規 index を最初 invisible で作る (運用反映を切り離せる)
CREATE INDEX idx_new ON users (col) INVISIBLE;
-- 必要なときに
ALTER TABLE users ALTER INDEX idx_new VISIBLE;

-- ③ デバッグ・テスト時にだけ無視する (セッション単位)
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN ...;

Index が効かない 10 大アンチパターン

断片化とその検出

-- 断片化率を見る (data_free が大きいほど断片化)
SELECT
  TABLE_NAME,
  ROUND(DATA_LENGTH / 1024 / 1024, 1) AS data_MB,
  ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_MB,
  ROUND(DATA_FREE / 1024 / 1024, 1) AS free_MB,
  ROUND(DATA_FREE * 100 / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE), 1) AS frag_pct
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY DATA_FREE DESC
LIMIT 20;

-- frag_pct が 20% 超え → OPTIMIZE 候補

OPTIMIZE TABLE — 王道の再構築

InnoDB 標準の方法。テーブル全体を作り直して断片を解消します。

-- ① OPTIMIZE TABLE
OPTIMIZE TABLE users;

-- → 内部的には ALTER TABLE ... FORCE; ANALYZE TABLE; と同等
-- → InnoDB では Online DDL なので<Em>ほぼ停止なし</Em>
-- → ただし大規模テーブルでは Redo Log / Undo を大量に書く
-- → 本番ではメンテ時間外に避けて打つ

-- ② ALTER TABLE ... FORCE (同等)
ALTER TABLE users FORCE, ALGORITHM=INPLACE, LOCK=NONE;

-- ③ Online DDL の制御
ALTER TABLE users FORCE,
  ALGORITHM=INSTANT,   -- 8.0+ 最速 (列追加など)
  LOCK=NONE;

ALGORITHM=INSTANT (8.0+) — 革命的高速化

8.0 で追加された ALGORITHM=INSTANT は、ALTER TABLE 系操作の一部を瞬時に 完了させます。データを書き換えず、メタデータだけ更新する仕組み。

pt-online-schema-change / gh-ost — 無停止スキーマ変更

本当に重い ALTER (CHANGE TYPE / 主キー変更) を本番で打つときは、サードパーティツールを使うのが定石。

gh-ost の cut-over — 一瞬の切替を理解する

gh-ost が真に評価されるのはcut-over (テーブル名 swap) の挙動です。Atomic な RENAME で外部から見て不可分に切り替えます。

# 通常運用フェーズ:
#   元: users (元テーブル)
#   _users_gho (新スキーマで作った shadow)
#
#   binlog を読んで users への INSERT/UPDATE/DELETE を _users_gho にも適用
#   既存行を copy
#
# Cut-over フェーズ (cut-over=atomic):
#   1. _users_old という magic lock テーブルを作る
#   2. lock _users_gho WRITE; create _users_old ... LIKE users;
#   3. RENAME TABLE users TO _users_old, _users_gho TO users;
#      ↑ アトミック (両 RENAME がトランザクショナル)
#   4. 残った binlog イベントを apply して完了
#
# つまり users への INSERT を投げ続けたまま、瞬間的にスキーマが変わる
# アプリは「テーブル名は users のまま」「中身が変わった」を体験する

# gh-ost 実行例
gh-ost \
  --host=primary \
  --user=ghost --password=xxx \
  --database=myapp --table=users \
  --alter="ADD COLUMN status TINYINT DEFAULT 0 AFTER name" \
  --max-load='Threads_running=25' \
  --critical-load='Threads_running=100' \
  --chunk-size=1000 \
  --throttle-control-replicas='replica1,replica2' \
  --cut-over=atomic \
  --switch-to-rbr \
  --execute

# postponed cut-over: いつでも投入準備完了 → 手動コマンドで切替
gh-ost ... --postpone-cut-over-flag-file=/tmp/ghost.postpone --execute

MyDumper / Myloader — 並列ダンプ・ロードの定番

mysqldump より圧倒的に速い OSS ダンプツール。util.dumpInstance (Section 51) が 8.0 で公式化される前のデファクト。

# ダンプ
mydumper \
  --host=primary \
  --user=backup --password=xxx \
  --database=myapp \
  --threads=8 \
  --rows=1000000 \
  --compress \
  --trx-consistency-only \        # InnoDB のみのスナップショット (高速)
  --outputdir=/backup/dump

# ロード (別環境)
myloader \
  --host=target \
  --user=admin --password=xxx \
  --database=myapp \
  --threads=16 \
  --queries-per-transaction=1000 \
  --directory=/backup/dump

# 比較:
# - 100 GB DB
# - mysqldump 単一スレッド: 数時間
# - MyDumper 8 並列: 30 分前後
# - util.dumpInstance (8.0): 20 分前後 (zstd 圧縮で更に小)

🎮 分離レベル × アノマリ インタラクティブマトリクス

マトリクスのセルをクリックすると、その組合せで何が起きるかの解説が表示されます。★ マークは MySQL InnoDB が SQL 標準を超えて防いでいる組合せです。

分離レベル × 起きうるアノマリ

4 つのアノマリの定義

MySQL の REPEATABLE READ — Phantom Read を防ぐ仕組み

SQL 標準では REPEATABLE READ は Phantom Read を防がない、とされています。しかし InnoDB はGap Lock (次節 Section 25) を組み合わせることで Phantom Read を防止しています。これが MySQL の独自性。

時刻  TX_A (REPEATABLE READ)         TX_B
─────────────────────────────────────────────────────────────────
T1    START TRANSACTION;
      SELECT * FROM orders
        WHERE user_id = 1 FOR UPDATE;
      → 0 件
      # ★ user_id=1 の "範囲" に Gap Lock 確保
T2                                    START TRANSACTION;
                                      INSERT INTO orders
                                        (user_id, ...) VALUES (1, ...);
                                      # ★ Gap Lock 待ち (ブロック)
T3    SELECT * FROM orders
        WHERE user_id = 1 FOR UPDATE;
      → 0 件 (Phantom なし)
      COMMIT;
T4                                    # ↑ Gap Lock 解放されたので
                                      # INSERT 続行 → COMMIT

分離レベルの設定

-- 現在の分離レベル
SELECT @@global.transaction_isolation;   -- グローバル
SELECT @@session.transaction_isolation;  -- セッション

-- セッション単位で変更 (次 TX から)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- このトランザクションだけ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
  ...
COMMIT;

-- グローバル (要 SUPER 権限)
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- my.cnf
[mysqld]
transaction_isolation = READ-COMMITTED

ロックの種類 — 全体図

Gap Lock — REPEATABLE READ の主役

users テーブルに id = 10, 20, 30 の 3 行があるとします。SELECT * FROM users WHERE id BETWEEN 15 AND 25 FOR UPDATE でかかる Next-Key Lock の範囲:

ロックの確認方法 (8.0+)

-- ① 現在のロック状況 (8.0+ の決定版)
SELECT * FROM performance_schema.data_locks;

-- カラム:
-- ENGINE_LOCK_ID    : ロック ID
-- ENGINE_TRANSACTION_ID : TX ID
-- THREAD_ID         : 持っているスレッド
-- OBJECT_NAME       : テーブル名
-- INDEX_NAME        : ロックがかかる index
-- LOCK_TYPE         : RECORD / TABLE
-- LOCK_MODE         : S / X / IS / IX / S,GAP / X,GAP / S,REC_NOT_GAP / ...
-- LOCK_STATUS       : GRANTED / WAITING
-- LOCK_DATA         : ロックされている値

-- ② Lock 待ち情報
SELECT * FROM performance_schema.data_lock_waits;

-- ③ デッドロック情報 (最後の 1 件)
SHOW ENGINE INNODB STATUS\G
-- "LATEST DETECTED DEADLOCK" セクション

-- ④ 5.7 以前は information_schema.innodb_locks (廃止)
-- 8.0 では data_locks に置き換え

Metadata Lock (MDL) — DDL が止まる典型原因

ALTER TABLE が「Waiting for table metadata lock」で止まるのは、別の TX がそのテーブルに対して MDL を取っているからです。

-- 確認方法
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE='TABLE';

-- 典型シナリオ
-- TX_1: SELECT してから BEGIN しっぱなしで放置
-- TX_2: ALTER TABLE → MDL 待ちで永久に停止
-- → TX_1 を kill する必要

-- アラート設定
-- lock_wait_timeout で ALTER を諦めさせる
SET SESSION lock_wait_timeout = 5;  -- 5秒で諦める

data_lock_waits — 「誰が誰を待っているか」を 1 SQL で見る

実務で「今ブロックされている TX を全部出して」と言われたら、これ。8.0 で追加された data_lock_waits はロックの待ちグラフ を直接見せてくれます。

-- ① 待ち関係を一覧表示 (待っている side / 持っている side)
SELECT
  r.trx_id          AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query       AS waiting_query,
  b.trx_id          AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query       AS blocking_query,
  TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_age_sec
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON w.REQUESTING_ENGINE_TRANSACTION_ID = r.trx_id
JOIN information_schema.innodb_trx b ON w.BLOCKING_ENGINE_TRANSACTION_ID = b.trx_id;

-- ② ロックの種別と対象も詳しく
SELECT
  w.REQUESTING_THREAD_ID AS waiting_thread,
  w.BLOCKING_THREAD_ID  AS blocking_thread,
  rl.OBJECT_NAME,
  rl.INDEX_NAME,
  rl.LOCK_MODE          AS waiting_mode,
  bl.LOCK_MODE          AS blocking_mode,
  rl.LOCK_DATA
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks rl
  ON w.REQUESTING_ENGINE_LOCK_ID = rl.ENGINE_LOCK_ID
JOIN performance_schema.data_locks bl
  ON w.BLOCKING_ENGINE_LOCK_ID  = bl.ENGINE_LOCK_ID;

-- ③ sys schema の便利ビュー (人間向け)
SELECT * FROM sys.innodb_lock_waits;

-- ④ 一気に問題スレッドを kill するスクリプト材料
SELECT CONCAT('KILL ', blocking_pid, ';') AS kill_cmd
FROM sys.innodb_lock_waits
WHERE wait_age_secs > 30;

🎮 デッドロック発生から解消までを step-through で見る

下のボタンで 1 step ずつ進めて、2 つのトランザクションがどうやってデッドロックに至るか、InnoDB がどうやって検出・解消するかを観察してください。

古典的デッドロック例

時刻   TX_A                              TX_B
─────────────────────────────────────────────────────────
T1     UPDATE u SET ... WHERE id=1;
       # TX_A が id=1 の Record Lock 取得
T2                                       UPDATE u SET ... WHERE id=2;
                                         # TX_B が id=2 の Record Lock 取得
T3     UPDATE u SET ... WHERE id=2;
       # TX_A は id=2 を待つ
T4                                       UPDATE u SET ... WHERE id=1;
                                         # TX_B は id=1 を待つ
                                         # → デッドロック!

T5     ← InnoDB デッドロック検出器が発火 (1 秒以内)
       ← より新しい / 影響範囲の小さい TX を選んで ROLLBACK

T6     ERROR 1213 (40001): Deadlock found
       when trying to get lock; try restarting transaction

SHOW ENGINE INNODB STATUS の読み方

SHOW ENGINE INNODB STATUS\G

# 出力の "LATEST DETECTED DEADLOCK" セクション

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-06-12 10:30:45 0x7f8b...
*** (1) TRANSACTION:
TRANSACTION 412938, ACTIVE 0.123 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 24, OS thread handle 0x7f...,
  query id 482 host=10.0.0.4 user=alice updating
UPDATE u SET ... WHERE id=2

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 41 page no 5 n bits 80 index PRIMARY
of table myapp.users trx id 412938 lock_mode X locks rec
but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 412939, ACTIVE 0.087 sec starting index read
...
UPDATE u SET ... WHERE id=1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 41 page no 5 n bits 80 index PRIMARY
of table myapp.users trx id 412939 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
...

*** WE ROLL BACK TRANSACTION (2)

本番でよくあるデッドロックパターン

デッドロックを避けるアプリ設計

本番で実際に使うクエリ集

-- ① 重い SQL TOP 10 (合計実行時間でソート)
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1e9 / 1000, 1) AS total_sec,
  ROUND(AVG_TIMER_WAIT / 1e9, 2)        AS avg_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- ② スロークエリ TOP 10 (1回平均で遅いもの)
SELECT
  DIGEST_TEXT,
  COUNT_STAR,
  ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
  ROUND(MAX_TIMER_WAIT / 1e9, 2) AS max_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE AVG_TIMER_WAIT > 1e9 * 100  -- 100 ms 以上
ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;

-- ③ 待ち時間の長いイベント TOP (I/O 待ち / Mutex 待ち等)
SELECT
  EVENT_NAME,
  COUNT_STAR,
  ROUND(SUM_TIMER_WAIT / 1e9 / 1000, 1) AS total_sec
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE COUNT_STAR > 0
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

-- ④ ファイル別 I/O 統計 (どのテーブルの I/O が支配的?)
SELECT
  FILE_NAME,
  COUNT_READ + COUNT_WRITE AS total_io,
  ROUND(SUM_NUMBER_OF_BYTES_READ / 1024/1024, 1) AS read_MB,
  ROUND(SUM_NUMBER_OF_BYTES_WRITE / 1024/1024, 1) AS write_MB
FROM performance_schema.file_summary_by_instance
WHERE FILE_NAME LIKE '%.ibd'
ORDER BY total_io DESC LIMIT 10;

sys schema — 人間向けビュー

-- ① 重い SQL (sys.statement_analysis)
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

-- ② 効いていない (= rows_examined / rows_sent が大きい) クエリ
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- ③ 使われていない index
SELECT * FROM sys.schema_unused_indexes;

-- ④ 重複 / 冗長な index
SELECT * FROM sys.schema_redundant_indexes;

-- ⑤ Buffer Pool ヒット状況をテーブル別に
SELECT * FROM sys.x$innodb_buffer_stats_by_table LIMIT 10;

-- ⑥ 接続別ステータス (誰が今 mysqld を使っているか)
SELECT * FROM sys.processlist
WHERE conn_id IS NOT NULL
ORDER BY trx_started ASC LIMIT 20;

setup_instruments / setup_consumers — Performance Schema を有効化する

Performance Schema はデフォルトで一部の計測しか有効化されていません。本格的なパフォーマンス解析には個別の計測項目を有効化する必要があります。

-- ① 計測対象 (instrument) を有効化
SELECT name, enabled, timed
FROM performance_schema.setup_instruments
WHERE name LIKE 'wait/io/%';
-- enabled=NO のものを ON にする
UPDATE performance_schema.setup_instruments
SET enabled='YES', timed='YES'
WHERE name LIKE 'wait/io/%';

-- ② 消費先 (consumer) を有効化
SELECT * FROM performance_schema.setup_consumers;
-- events_statements_history_long などが OFF だと長期保存されない
UPDATE performance_schema.setup_consumers
SET enabled='YES'
WHERE name LIKE 'events_%_history%';

-- ③ Memory instruments (8.0+ で重要)
UPDATE performance_schema.setup_instruments
SET enabled='YES'
WHERE name LIKE 'memory/%';

-- ④ Threads の計測 (thread-level performance)
UPDATE performance_schema.setup_consumers
SET enabled='YES'
WHERE name = 'thread_instrumentation';

-- ⑤ 一括有効化マクロ
CALL sys.ps_setup_enable_consumer('events_statements_history_long');
CALL sys.ps_setup_enable_instrument('%');

-- ⑥ 永続化: my.cnf
[mysqld]
performance-schema-instrument='wait/%=ON'
performance-schema-instrument='memory/%=ON'
performance-schema-consumer-events-statements-history-long=ON
performance-schema-consumer-events-waits-history-long=ON

slow_query_log の設定

-- 動的に有効化
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1.0;            -- 1 秒以上を記録
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- index 未使用も
SET GLOBAL log_slow_admin_statements = 'ON';  -- DDL も

-- my.cnf
[mysqld]
slow_query_log     = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time    = 1.0
log_queries_not_using_indexes = ON

-- ログ出力例
# Time: 2026-06-12T10:30:45.123456Z
# User@Host: alice[alice] @ [10.0.0.4]  Id: 482
# Query_time: 3.456789  Lock_time: 0.000123 Rows_sent: 1234 Rows_examined: 567890
SET timestamp=1718189445;
SELECT * FROM orders WHERE country='JP' AND created_at > '2026-01-01';

pt-query-digest — スロークエリの集計

スロークエリログを SQL のパターン (digest) ごとに集計するツール。Percona Toolkit に含まれます。

# インストール (Ubuntu)
apt install percona-toolkit

# 解析
pt-query-digest /var/log/mysql/slow.log > report.txt

# 出力例
# Rank Query ID                          Response time  Calls
# ==== ================================= ============== =====
# 1    0x6F50A0...                       234.5s  45%    1234
#                                       avg 190ms
# Tables: orders, users
# SELECT * FROM orders o JOIN users u ON o.user_id=u.id ...

# Performance Schema からも集計可能
pt-query-digest --type=processlist h=localhost,u=root,p=xxx

auto_explain が無い問題への対処

PostgreSQL には遅いクエリの実行プランを自動でログに残す auto_explain 拡張がありますが、MySQL には無い。代わりに以下のアプローチを取ります。

log_slow_extra (8.0+) と高度なフィルタリング

8.0 からlog_slow_extra でスロークエリログに大量の追加情報が記録できるようになりました。pt-query-digest が拾える情報が圧倒的に増えます。

-- ① log_slow_extra で詳細出力
SET GLOBAL log_slow_extra = 'ON';

-- ② スロークエリログの出力例 (extra ON)
# Time: 2026-06-12T10:30:45.123Z
# User@Host: alice[alice] @ [10.0.0.4]
# Thread_id: 482 Schema: myapp QC_hit: No
# Query_time: 3.456789  Lock_time: 0.000123
# Rows_sent: 1234 Rows_examined: 567890
# Rows_affected: 0 Bytes_sent: 245678
# Tmp_tables: 1 Tmp_disk_tables: 0   ← ★ 一時表が disk に
# Tmp_table_sizes: 16384
# Sort_rows: 1234 Sort_scan_count: 0 Sort_range_count: 1
# Sort_merge_passes: 0
# Filesort: Yes  Filesort_on_disk: No
# Innodb_io_r_ops: 1234 Innodb_io_r_bytes: 20180992 Innodb_io_r_wait: 1.234567
# Innodb_rec_lock_wait: 0.000000 Innodb_queue_wait: 0.000000
# Innodb_pages_distinct: 5678
SET timestamp=...; SELECT ...;

-- ③ スロークエリログの追加フィルタ
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_throttle_queries_not_using_indexes = 60;
  -- 1 分間に 60 件まで (ノイズ抑制)
SET GLOBAL min_examined_row_limit = 100;
  -- 検査行数が 100 未満のクエリは除外

-- ④ クエリ単位でフィルタ
SELECT /*+ SET_VAR(long_query_time=0) */ ... ;   -- 強制ログ
SELECT /*+ SET_VAR(long_query_time=999) */ ... ; -- 強制除外

本番チューニング 5 大設定

その他の重要パラメータ

my.cnf — 本番標準テンプレ (16 GB / SSD)

[mysqld]
# --- InnoDB Core ---
innodb_buffer_pool_size       = 12G
innodb_buffer_pool_instances  = 8
innodb_log_file_size          = 2G
innodb_log_buffer_size        = 64M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity            = 2000
innodb_io_capacity_max        = 4000
innodb_flush_neighbors        = 0       # SSD では 0 が良い

# --- I/O Threads ---
innodb_read_io_threads        = 8
innodb_write_io_threads       = 8
innodb_page_cleaners          = 8

# --- Replication / binlog ---
server-id                     = 1
log-bin                       = /var/log/mysql/binlog
binlog_format                 = ROW
sync_binlog                   = 1
gtid_mode                     = ON
enforce_gtid_consistency      = ON

# --- Connections ---
max_connections               = 500
thread_cache_size             = 50
wait_timeout                  = 28800

# --- Charset ---
character-set-server          = utf8mb4
collation-server              = utf8mb4_0900_ai_ci

# --- Slow Log ---
slow_query_log                = ON
slow_query_log_file           = /var/log/mysql/slow.log
long_query_time               = 1.0

Buffer Pool warmup — dump/load で再起動後を一瞬で

mysqld を再起動すると Buffer Pool が空になり、しばらく性能が出ません (= cold cache)。Buffer Pool dump/load で「停止前の状態の Page」を停止後すぐに復元できます。

-- ① 設定 (デフォルト ON が多い)
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_%';
-- innodb_buffer_pool_dump_at_shutdown  ON    -- 停止時に自動 dump
-- innodb_buffer_pool_dump_now           OFF   -- 即時 dump フラグ
-- innodb_buffer_pool_dump_pct           25    -- 上位 25% の Page だけ

SHOW VARIABLES LIKE 'innodb_buffer_pool_load_%';
-- innodb_buffer_pool_load_at_startup    ON
-- innodb_buffer_pool_load_now            OFF
-- innodb_buffer_pool_load_abort          OFF

-- ② dump ファイルの場所
ls -la /var/lib/mysql/ib_buffer_pool   -- 数百 KB 〜数 MB

-- ③ ファイルの中身は <space_id, page_no> のリストだけ (実データではない)
-- → mysqld 起動時にこのリストを元に該当 Page を ディスクから Buffer Pool に読み込む

-- ④ 手動 dump (運用中の状態を保存しておきたい)
SET GLOBAL innodb_buffer_pool_dump_now = ON;

-- ⑤ 進捗確認
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
-- "Buffer pool(s) load completed at 2026-06-12 10:30:45"

-- ⑥ 起動を待たずに別途 warmup したい
SET GLOBAL innodb_buffer_pool_load_now = ON;

4 種のパーティション方式

RANGE パーティションの実例

CREATE TABLE events (
  id BIGINT,
  created_at DATETIME NOT NULL,
  body JSON,
  PRIMARY KEY (id, created_at)   -- ← パーティションキーは PK に含める必要
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01')),
  PARTITION p2025 VALUES LESS THAN (TO_DAYS('2026-01-01')),
  PARTITION p2026 VALUES LESS THAN (TO_DAYS('2027-01-01')),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- 古い年のデータを一瞬で削除 (Partition Drop)
ALTER TABLE events DROP PARTITION p2024;

-- 新しい年を追加
ALTER TABLE events REORGANIZE PARTITION pmax INTO (
  PARTITION p2027 VALUES LESS THAN (TO_DAYS('2028-01-01')),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- Partition Pruning が効くかを確認
EXPLAIN PARTITIONS SELECT * FROM events
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';
-- → partitions: p2026  (他は読まない)

パーティションの落とし穴

🎮 レプリケーション遅延シミュレータ

Primary でコミットされた TX が、Replica で適用されるまでのタイムラグ を可視化します。「+1s」ボタンを押して時間を進めてください。Seconds_Behind_Source がじわじわ伸びるのを観察できます。

レプリケーションの全体図

GTID Replication (5.6+) — 推奨方式

昔の 「binlog ファイル名 + position」方式と違い、各トランザクションにグローバルにユニークな ID (GTID) を付ける方式。フェイルオーバー時のレプリ張り直しが圧倒的に楽。

-- my.cnf (両方で)
[mysqld]
gtid_mode                = ON
enforce_gtid_consistency = ON
log-bin                  = mysql-bin
binlog_format            = ROW
server-id                = 1   # 各サーバでユニーク

-- ① レプリ設定 (8.0+ では CHANGE REPLICATION SOURCE)
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary.example.com',
  SOURCE_USER='repl',
  SOURCE_PASSWORD='xxx',
  SOURCE_AUTO_POSITION=1;   -- GTID で自動位置決め

START REPLICA;

-- ② 状態確認
SHOW REPLICA STATUS\G

-- 確認ポイント:
-- - Replica_IO_Running:  Yes
-- - Replica_SQL_Running: Yes
-- - Seconds_Behind_Source: 0    (遅延 0 が理想)
-- - Retrieved_Gtid_Set / Executed_Gtid_Set

非同期 / 準同期 / Group Replication

Multi-Source Replication と Channel — 複数 Primary から 1 Replica へ

5.7.6+ でMulti-Source Replication が公式サポート。1 つの Replica が複数の Primary から binlog を受け取れるようになりました。各レプリ接続は Channel という名前で識別されます。

-- Channel 'shard_a' から東京の Primary を購読
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='tokyo-primary',
  SOURCE_USER='repl', SOURCE_PASSWORD='xxx',
  SOURCE_AUTO_POSITION=1
  FOR CHANNEL 'shard_a';

-- Channel 'shard_b' から大阪の Primary を購読
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='osaka-primary',
  SOURCE_USER='repl', SOURCE_PASSWORD='xxx',
  SOURCE_AUTO_POSITION=1
  FOR CHANNEL 'shard_b';

-- 個別 Channel 操作
START REPLICA FOR CHANNEL 'shard_a';
STOP REPLICA FOR CHANNEL 'shard_b';

-- 状態確認
SHOW REPLICA STATUS FOR CHANNEL 'shard_a'\G

-- 全 Channel 一覧
SELECT * FROM performance_schema.replication_connection_status;

-- ★ Replication Filter で衝突回避
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (shard_a_db)
  FOR CHANNEL 'shard_a';
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (shard_b_db)
  FOR CHANNEL 'shard_b';

-- ユースケース:
-- ・複数シャードのデータを 1 つの分析 Replica に集約
-- ・地理分散した複数 Primary の Active-Active 構成
-- ・本番と参考環境を 1 Replica に両方ミラー (テスト用)

従来 Replication との違い

セットアップの概要 (3 ノード Single-Primary)

-- 各ノードの my.cnf
[mysqld]
server_id                       = 1  # 各ノードでユニーク
gtid_mode                       = ON
enforce_gtid_consistency        = ON
binlog_checksum                 = NONE
plugin_load_add                 = 'group_replication.so'

# Group Replication 設定
group_replication_group_name             = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa'
group_replication_local_address          = '10.0.0.1:33061'
group_replication_group_seeds            = '10.0.0.1:33061,10.0.0.2:33061,10.0.0.3:33061'
group_replication_bootstrap_group        = OFF
group_replication_single_primary_mode    = ON

-- 起動 (最初のノードだけ bootstrap)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;

-- 2 番目以降のノード
START GROUP_REPLICATION;

-- 状態確認
SELECT * FROM performance_schema.replication_group_members;

-- MEMBER_ID    | MEMBER_HOST  | MEMBER_STATE | MEMBER_ROLE
-- (uuid)       | host1        | ONLINE      | PRIMARY
-- (uuid)       | host2        | ONLINE      | SECONDARY
-- (uuid)       | host3        | ONLINE      | SECONDARY

Flow Control — 遅いノードがクラスタを止めないための仕組み

Group Replication では過半数の合意が必要なので、1 ノードが遅いと全体が詰まります。これを防ぐのが Flow Control: 遅延が大きいノードがあるとき、Primary 側の書込み速度を自動的に絞ります。

-- ① 設定
SHOW VARIABLES LIKE 'group_replication_flow_control_%';
-- group_replication_flow_control_mode               = QUOTA   (デフォルト)
-- group_replication_flow_control_certifier_threshold = 25000
-- group_replication_flow_control_applier_threshold   = 25000
-- group_replication_flow_control_max_quota           = 0       (無制限)
-- group_replication_flow_control_min_quota           = 0
-- group_replication_flow_control_min_recovery_quota  = 0

-- 仕組み:
-- 各 Replica が「Certifier キューに何件溜まっているか」「Applier キューに何件溜まっているか」を全員に通知
-- 閾値 (25000 件) を超えたら、Primary 側は<Em>次サイクルの書込み quota</Em> を絞る
-- → 全体としてキューが定常状態に収束する

-- ② Flow Control の状態を確認
SELECT * FROM performance_schema.replication_group_member_stats;
-- COUNT_TRANSACTIONS_IN_QUEUE: Apply 待ち
-- COUNT_TRANSACTIONS_CHECKED:  Certify 済
-- COUNT_CONFLICTS_DETECTED:    Certify 衝突

-- ③ チューニング
SET GLOBAL group_replication_flow_control_mode = 'QUOTA';
SET GLOBAL group_replication_flow_control_certifier_threshold = 50000;
-- 大きくする = 遅延を許容 / 書込み詰まりを軽減
-- 小さくする = 遅延を抑える / 書込みは絞られがち

-- ④ Flow Control を完全無効化 (本番非推奨)
SET GLOBAL group_replication_flow_control_mode = 'DISABLED';

MySQL Router vs ProxySQL

読み書き分離の挙動

# MySQL Router 設定 (mysqlrouter.conf)
[routing:rw]
bind_address                = 0.0.0.0:6446
destinations                = metadata-cache://mycluster/?role=PRIMARY
routing_strategy            = first-available

[routing:ro]
bind_address                = 0.0.0.0:6447
destinations                = metadata-cache://mycluster/?role=SECONDARY
routing_strategy            = round-robin-with-fallback

# アプリ側はポート番号で読み書き分離
# WRITE → 6446 (Primary に振られる)
# READ  → 6447 (Replica に round-robin)

ProxySQL の振分けルール — 実用例

-- ① Hostgroup を定義 (10=write, 20=read)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES
  (10, 'primary.example.com', 3306, 1000, 200),
  (20, 'replica1.example.com', 3306, 500, 200),
  (20, 'replica2.example.com', 3306, 500, 200),
  (20, 'replica3.example.com', 3306, 500, 200);

-- ② Query Rule で振り分け
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
  (1, 1, '^SELECT.*FOR UPDATE$',                    10, 1),  -- ロック取る SELECT は Primary
  (2, 1, '^SELECT.*FOR SHARE$',                     10, 1),
  (3, 1, '^SELECT @@(version|hostname|server_id)',  20, 1),  -- メタクエリは Replica
  (4, 1, '^SELECT.*FROM information_schema',        20, 1),
  (5, 1, '^SELECT.*FROM performance_schema',        20, 1),
  (6, 1, '^SELECT',                                  20, 1),  -- 通常 SELECT は Replica
  (7, 1, '^',                                        10, 1);  -- それ以外は Primary

-- ③ ロード時間ベースのキャッシュ (重い集計を ProxySQL でキャッシュ)
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, cache_ttl) VALUES
  (100, 1, '^SELECT COUNT.*FROM big_aggregate_table', 20, 60000);  -- 60 秒キャッシュ

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

フェイルオーバー時の挙動 — Connection Multiplexing

ProxySQL はConnection Multiplexing という仕組みで「アプリ接続 N 個 → MySQL 接続 1 個」のように多重化できます。Primary 障害時の挙動の鍵になります。

-- ① モニタリング設定
SET mysql-monitor_writer_is_also_reader = 0;
SET mysql-monitor_history = 600000;       -- 10 分の履歴
SET mysql-monitor_connect_interval = 60000;
SET mysql-monitor_ping_interval = 10000;

-- ② Primary failover 時の挙動:
-- Step 1: ProxySQL が Primary の ping 失敗を検出 (~10 秒)
-- Step 2: 該当ノードを SHUNNED (一時的に外す) に
-- Step 3: 新規接続は別 Primary に振り分け
-- Step 4: 既存接続は<Em>該当ノードから外される</Em>

-- ③ Multiplexing を切る場面
-- ・ユーザーが SET 文を発行した (セッション変数を変えた)
-- ・トランザクション開始した (BEGIN)
-- ・LOCK TABLES した
-- → これらは Pinning と呼ばれ、接続を独占する

-- ④ 監視: 詰まりを見つける
SELECT * FROM stats.stats_mysql_processlist
WHERE conn_status='PINNED' AND time_ms > 60000;

SELECT * FROM stats.stats_mysql_connection_pool;
-- Conn_used, Conn_free, Conn_OK, Conn_ERR, Queries の比率で詰まりを検知

InnoDB Cluster の構成要素

MySQL Shell でクラスタ構築

# MySQL Shell に入る
mysqlsh root@node1

# 各ノードの設定を InnoDB Cluster 向けに調整
dba.configureInstance('root@node1');
dba.configureInstance('root@node2');
dba.configureInstance('root@node3');

# クラスタ作成 (1 ノード目で実行)
var cluster = dba.createCluster('mycluster');

# 他ノードを追加
cluster.addInstance('root@node2');
cluster.addInstance('root@node3');

# 状態確認
cluster.status();

# MySQL Router を bootstrap
mysqlrouter --bootstrap root@node1:3306 --user=mysqlrouter --directory /opt/router
systemctl start mysqlrouter

# アプリは Router 経由でアクセス
mysql -h router.example.com -P 6446 -u app -p   # 書き込み
mysql -h router.example.com -P 6447 -u app -p   # 読み取り

Primary 障害時のフェイルオーバー

# シナリオ: Primary (node1) がダウン

# Step 1 — 自動検出 (数秒以内)
#   Group Replication が node1 の通信途絶を検出
#   過半数の合意で node1 を OFFLINE 扱いに

# Step 2 — 新 Primary 選出 (自動)
#   残った node2, node3 の中から GTID が最も進んでいる方を選出
#   通常は node2 が新 Primary に

# Step 3 — MySQL Router がトポロジ更新
#   metadata-cache 経由で「Primary が node2 に変わった」を検出
#   新規接続は自動的に node2 へ

# Step 4 — アプリ側の挙動
#   フェイルオーバー中の数秒間は書き込み失敗
#   再接続で自動回復

# Step 5 — node1 復旧後
cluster.rejoinInstance('root@node1');
# → Secondary として再参加

# 確認
cluster.status();

InnoDB ClusterSet (8.0.27+) — クロスサイト DR

単一データセンターの障害からの保護として、8.0.27 でInnoDB ClusterSet が追加されました。複数の InnoDB Cluster を非同期レプリケーションで結ぶ仕組みです。

# MySQL Shell で ClusterSet 構築
mysqlsh root@tokyo_node1

# 既存 Cluster を ClusterSet に昇格
> var cs = cluster.createClusterSet('myclusterset');

# Osaka 側に Replica Cluster を作る
> var replicaCluster = cs.createReplicaCluster(
    'root@osaka_node1', 'osaka_cluster'
);
> replicaCluster.addInstance('root@osaka_node2');
> replicaCluster.addInstance('root@osaka_node3');

# 状態確認
> cs.status();
# Tokyo: Primary, Osaka: Replica

# 災害時の Primary 昇格 (Tokyo 全滅シナリオ)
> cs.setPrimaryCluster('osaka_cluster');
# Forced failover (Tokyo に到達不能):
> cs.forcePrimaryCluster('osaka_cluster');

バックアップツール比較

mysqldump — 古典の安全な使い方

# 推奨オプション (本番)
mysqldump \
  --single-transaction \         # InnoDB を整合性ある状態でダンプ
  --quick \                       # 1 行ずつ取り出し (メモリ節約)
  --routines \                    # Stored Procedure も含める
  --triggers \                    # Trigger も
  --events \                      # Event Scheduler も
  --master-data=2 \               # binlog 位置をコメントで記録 (PITR 用)
  --flush-logs \                  # binlog ローテート
  --hex-blob \                    # BLOB を 16 進で出力
  --default-character-set=utf8mb4 \
  -h primary -u backup -p \
  myapp | gzip > myapp_$(date +%Y%m%d).sql.gz

# 注意: MyISAM テーブルが混在する場合は --single-transaction が効かない
# → --lock-tables も追加 (= 短時間のテーブルロック)

Percona XtraBackup — 物理バックアップの定番

# 1. フルバックアップ取得
xtrabackup --backup --target-dir=/backup/full \
  --user=backup --password=xxx

# 2. インクリメンタル (差分)
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=backup --password=xxx

# 3. リストア (フル+差分を適用)
xtrabackup --prepare --target-dir=/backup/full \
  --apply-log-only
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# 4. データディレクトリに戻す
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Backup Lock (8.0.23+)

従来の FLUSH TABLES WITH READ LOCK はテーブル全体を止めるので運用上嫌でした。8.0.23 でBACKUP_ADMIN 権限 + LOCK INSTANCE FOR BACKUP が追加され、軽量に整合性を取れるようになりました。

# 権限付与
GRANT BACKUP_ADMIN ON *.* TO 'backup'@'%';

# Backup Lock (DDL だけ止める。DML は継続)
LOCK INSTANCE FOR BACKUP;
# ... ファイルコピー or スナップショット ...
UNLOCK INSTANCE;

# XtraBackup 8.0 は自動でこれを使う

リストアの 3 シナリオ

PITR の典型例 — 誤 DELETE からの復旧

# 状況:
# - 昨夜 02:00 にフルバックアップ
# - 今朝 10:23 に DBA がうっかり DELETE FROM orders;
# - 10:24 時点に戻したい

# 1. 別環境を用意して、フルバックアップで base 復元
xtrabackup --prepare --target-dir=/backup/full
# (copy-back)

# 2. binlog から DELETE の前 (10:23 直前) まで再生
mysqlbinlog \
  --start-position=4 \           # フルバックアップ完了時の位置
  --stop-datetime="2026-06-12 10:23:00" \
  /var/log/mysql/binlog.* \
  | mysql -u root -p

# 3. 検証
SELECT COUNT(*) FROM orders;   # 戻っているはず

# 4. 該当データだけ本番にエクスポート/インポート
mysqldump --where="id IN (...)" myapp orders > recovered.sql
mysql -h primary -u admin -p myapp < recovered.sql

必須監視メトリクス

観測ツール

SLI / SLO の設計 — 何を約束するか

監視は「異常を検知する」だけでなく「SLO (サービスレベル目標) と乖離していないか」を見る役割もあります。MySQL の典型的な SLI / SLO 例:

Prometheus mysqld_exporter — 主要メトリクス

Prometheus から MySQL を観測する場合の本当に見るべきメトリクス。mysql_* プレフィックスの中から最重要なものを抜粋:

# === 接続関連 ===
mysql_global_status_threads_connected      # 現在の接続数
mysql_global_status_threads_running        # 実行中スレッド数 (= 同時実行)
mysql_global_variables_max_connections     # 上限値
mysql_global_status_aborted_connects       # 接続失敗数 (増加注意)

# === Buffer Pool ===
mysql_global_status_innodb_buffer_pool_read_requests
mysql_global_status_innodb_buffer_pool_reads
# Hit率 = 1 - (reads / read_requests)

mysql_global_status_innodb_buffer_pool_pages_dirty
mysql_global_status_innodb_buffer_pool_pages_total
# Dirty率 = dirty / total

# === Redo / Binary Log ===
mysql_global_status_innodb_os_log_pending_fsyncs   # fsync 詰まり
mysql_global_status_binlog_cache_disk_use          # binlog cache が disk へ溢れ

# === レプリケーション ===
mysql_slave_status_seconds_behind_master
mysql_slave_status_slave_io_running
mysql_slave_status_slave_sql_running

# === デッドロック / ロック待ち ===
mysql_global_status_innodb_deadlocks         # rate で見る
mysql_global_status_innodb_row_lock_waits
mysql_global_status_innodb_row_lock_time     # ms 単位

# === スロークエリ ===
mysql_global_status_slow_queries
mysql_global_status_questions                # rate(slow/questions)

# === HLL (History List Length) ===
mysql_info_schema_innodb_metrics{name="trx_rseg_history_len"}

Grafana ダッシュボードのパネル例 (PromQL)

# ① Buffer Pool Hit Rate (%)
100 * (1 -
  rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
  rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])
)

# ② QPS (Queries per second)
rate(mysql_global_status_queries[1m])

# ③ Slow Query Rate (%)
100 *
  rate(mysql_global_status_slow_queries[5m]) /
  rate(mysql_global_status_questions[5m])

# ④ レプリ遅延 (sec)
mysql_slave_status_seconds_behind_master

# ⑤ デッドロック発生レート
rate(mysql_global_status_innodb_deadlocks[5m])

# ⑥ Buffer Pool Dirty Page %
100 *
  mysql_global_status_innodb_buffer_pool_pages_dirty /
  mysql_global_status_innodb_buffer_pool_pages_total

# ⑦ 接続数の閾値 (max_connections の 80%)
mysql_global_status_threads_connected /
  mysql_global_variables_max_connections * 100

アラートルール例

# Prometheus AlertManager 用ルール

groups:
- name: mysql
  rules:
  - alert: MySQLDown
    expr: mysql_up == 0
    for: 1m
    labels: { severity: critical }
    annotations:
      summary: "MySQL is down on {{ $labels.instance }}"

  - alert: MySQLHighConnections
    expr: mysql_global_status_threads_connected /
          mysql_global_variables_max_connections > 0.8
    for: 5m
    labels: { severity: warning }

  - alert: MySQLReplicationLag
    expr: mysql_slave_status_seconds_behind_master > 30
    for: 5m
    labels: { severity: warning }

  - alert: MySQLBufferPoolMissRate
    expr: 100 * (1 -
      rate(mysql_global_status_innodb_buffer_pool_reads[5m]) /
      rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])
    ) < 99
    for: 10m
    labels: { severity: warning }

  - alert: MySQLDeadlocksHigh
    expr: rate(mysql_global_status_innodb_deadlocks[5m]) > 0.1
    for: 10m
    labels: { severity: warning }

  - alert: MySQLHLLTooHigh
    expr: mysql_info_schema_innodb_metrics{name="trx_rseg_history_len"} > 1000000
    for: 15m
    labels: { severity: critical }
    annotations:
      summary: "Undo log not being purged - check long-running transactions"

3 つの接続プール戦略

ProxySQL — 例

# 設定 (admin port 6032)
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, 'primary.example.com', 3306),
       (20, 'replica1.example.com', 3306),
       (20, 'replica2.example.com', 3306);

# Read/Write split のルール
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (1, '^SELECT.*FOR UPDATE', 10),  # 更新ロックは Primary
       (2, '^SELECT', 20),                 # 通常 SELECT は Replica
       (3, '^', 10);                       # その他は Primary

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

# アプリは ProxySQL に繋ぐ (port 6033)
mysql -h proxysql.example.com -P 6033 -u app -p

プールサイズの計算式 — 公式の答え

「アプリ側プールサイズはいくつにすべきか?」HikariCP の作者 (Brett Wooldridge) が提唱した公式:

# プールサイズの公式
connections = ((core_count * 2) + effective_spindle_count)

# 例: 8 コア CPU + SSD (spindle=1) → (8*2 + 1) = 17 接続
# 例: 16 コア CPU + NVMe (spindle=0) → (16*2 + 0) = 32 接続

# 「もっと増やせばスループット上がる」という直感は<Em>間違い</Em>:
# ・接続数を増やすとコンテキストスイッチが増える
# ・disk seek の競合が増える
# ・ロック競合が増える
# → ある点を超えるとスループットは<Em>下がる</Em>

# ピーク時の同時接続を測る:
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections_time';

# 重要: max_connections は<Em>上限</Em>、プールサイズは<Em>常用値</Em>
# 例: max_connections=500, 各アプリのプール=20, アプリ数=10 → 平均 200 接続

HikariCP の主要設定 — Java/Kotlin 系

# application.yml (Spring Boot)
spring:
  datasource:
    hikari:
      maximum-pool-size: 20         # ★ 公式に従う (16-32 が多い)
      minimum-idle: 5                # idle 接続の最小値
      connection-timeout: 30000      # 接続取得タイムアウト (30 秒)
      idle-timeout: 600000           # idle 接続を切るまで (10 分)
      max-lifetime: 1800000          # 接続の最大寿命 (30 分)
      connection-test-query: "SELECT 1"
      validation-timeout: 5000
      leak-detection-threshold: 60000  # 接続リーク警告 (60 秒)

      # MySQL 固有
      data-source-properties:
        cachePrepStmts: true
        prepStmtCacheSize: 250
        prepStmtCacheSqlLimit: 2048
        useServerPrepStmts: true
        useLocalSessionState: true
        rewriteBatchedStatements: true
        cacheResultSetMetadata: true
        cacheServerConfiguration: true
        elideSetAutoCommits: true
        maintainTimeStats: false

# max-lifetime は MySQL の wait_timeout より<Em>短く</Em>すること
# (28800 秒) →  max-lifetime=27000 (7.5 時間)
# でないと wait_timeout で切られた接続をプールが使ってエラー</p>

ProxySQL Connection Pool — 中央集約型

ProxySQL は「アプリ接続 N 個 → MySQL 接続 M 個」(N >> M) の集約ができます。Section 33 の Multiplexing と組み合わせると劇的な接続数削減になります。

-- max_connections は Hostgroup ごと
UPDATE mysql_servers
SET max_connections = 200
WHERE hostgroup_id = 10;

-- Multiplexing 設定
SET mysql-multiplex = 1;       -- 有効化 (デフォルト)
SET mysql-connection_max_age_ms = 0;  -- 寿命無制限

-- プールの状態を確認
SELECT * FROM stats.stats_mysql_connection_pool;
-- Conn_used = ProxySQL → MySQL の実接続数
-- Conn_free = idle 接続
-- 比較: アプリ → ProxySQL の接続数は数千でも、
--       ProxySQL → MySQL は 200 で済む

-- 接続の振る舞いを監視
SELECT * FROM stats.stats_mysql_global
WHERE Variable_Name LIKE '%Conn%';
-- Stmt_Server_Active_Total: 実際に MySQL に投げているクエリ数

RDS Proxy — Lambda / サーバーレス時代の必需品

Lambda 等は関数呼び出しごとに接続を張り直すパターンで、すぐに max_connections 枯渇を引き起こします。RDS Proxy は AWS マネージドの解決策。

# 設定の主要点
- IdleClientTimeout:      1800   # idle Lambda 接続を切るまで
- MaxConnectionsPercent:  100    # RDS の max_connections の何 % まで使うか
- MaxIdleConnectionsPercent: 50

# Pinning の落とし穴 (※ Multiplexing が崩れるケース):
# - SET 文 (例: SET autocommit=0)
# - PREPARE 文 (Server-side PS)
# - LOCK TABLES
# - 一部の関数 (LAST_INSERT_ID(), FOUND_ROWS())
# - SELECT GET_LOCK()

# Pinning が起きると Lambda は接続を独占 → 次の Lambda は新規接続
# → 結局 max_connections を消費する

# ★ IAM 認証を使うと パスワードレスで Lambda 接続可
# ★ Secrets Manager 統合で認証情報をローテート

プールが詰まった時の調査

-- MySQL 側で接続状態を確認
SHOW PROCESSLIST;

-- 状態別カウント
SELECT
  COMMAND, STATE, COUNT(*) AS cnt
FROM information_schema.processlist
GROUP BY COMMAND, STATE
ORDER BY cnt DESC;

-- 長期 idle 接続
SELECT
  ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.processlist
WHERE COMMAND='Sleep' AND TIME > 3600
ORDER BY TIME DESC;

-- 長期実行クエリ
SELECT * FROM information_schema.processlist
WHERE COMMAND != 'Sleep' AND TIME > 30
ORDER BY TIME DESC;

-- 接続を強制切断
KILL CONNECTION <id>;     -- 接続切断
KILL QUERY <id>;          -- クエリだけ kill (接続は残す)

マイナー / メジャーアップグレード

メジャーアップグレード戦略

互換性チェック

# MySQL Shell で 5.7 → 8.0 の互換性チェック
mysqlsh root@5.7-server -- util checkForServerUpgrade

# 出力例:
# 1) Errors found in MySQL configuration
#    - utf8 charset is deprecated (use utf8mb4)
# 2) Schema errors
#    - Schema 'myapp' has tables with old Row Format
# 3) Reserved keywords in identifier names
#    - Column 'rank' uses MySQL 8.0 reserved word

# これらを潰してから upgrade
ALTER TABLE ... ROW_FORMAT=DYNAMIC;
RENAME COLUMN rank TO ranking;

5.7 → 8.0 で「壊れる」具体的なポイント

移行の現場で「これで動かなくなった」 が頻発するポイントを優先順に整理します。事前にチェックリスト化しておくのが鉄則。

Blue-Green Replication によるメジャーアップグレード手順

実務で最も安全なメジャーアップグレード手順。「Green に切り替えてダメなら Blue に戻せる」可逆性が最大のメリット。

# Phase 1 — 互換性チェック (1〜2 週間前)
mysqlsh root@blue_primary -- util checkForServerUpgrade

# Phase 2 — Green 環境を 8.0 で構築 (1 週間前)
# 1. 新サーバ群を 8.0 で構築
# 2. Blue (5.7 Primary) → Green (8.0 Primary 候補) にレプリ
#    ※ 5.7 → 8.0 のレプリは公式サポート
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='blue_primary',
  SOURCE_USER='repl', SOURCE_PASSWORD='xxx',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

# Phase 3 — Green でテスト
# 1. Green を Read-Only でアプリの一部に読み取りを向ける
# 2. 期待動作・性能を検証
# 3. 互換性チェックで見落とした点をパッチ

# Phase 4 — 切替 (Cut-over)
# 1. メンテ告知 (5-10 分のダウンタイム)
# 2. Blue の書き込みを止める (read_only=ON)
# 3. Green が Blue に追いついたことを確認 (GTID 比較)
# 4. Green を Primary に昇格 (read_only=OFF)
# 5. アプリの DB 接続先を Green に切替

# Phase 5 — ロールバック準備 (1〜数日)
# Green → Blue へ逆向きレプリを張る (8.0 → 5.7 は基本不可)
# 代わりに Blue を「Cold Standby」として保持
# 問題なければ Blue を撤去

# RDS / Aurora の場合
# Blue/Green Deployment 機能で同じことが GUI からできる
# (RDS: 2023年〜, Aurora MySQL: 2023年〜)

Rolling Upgrade — Replica から順にアップグレード

ダウンタイムを切り替えの数秒に抑えたい時の手順。マイナーアップグレードでよく使います。

# 構成: Primary + Replica × 3

# Step 1: Replica #3 を停止 → 新バージョンに置換 → 起動
#   レプリを再開して同期完了を待つ

# Step 2: 同じく Replica #2, #1 で繰り返す
#   この時点で 3 Replica が新バージョン、Primary は旧バージョン

# Step 3: Primary failover を発火
#   InnoDB Cluster: cluster.setPrimaryInstance('replica1')
#   通常 Replication: 手動で promote
#   → 旧 Primary が Replica になり、新バージョンの Replica が Primary に

# Step 4: 旧 Primary (今は Replica) を新バージョンに置換 → 起動

# 結果: 全 4 ノードが新バージョン、ダウンタイム = failover 時間のみ
# (通常 < 30 秒)

アップグレード後の検証ステップ

-- ① バージョン確認
SELECT VERSION();

-- ② 設定が反映されているか
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'sql_mode';

-- ③ Buffer Pool が温まっているか
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_data';
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

-- ④ レプリが正常か
SHOW REPLICA STATUS\G

-- ⑤ Performance Schema が動いているか
SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest;

-- ⑥ 重要クエリのプランが変わっていないか確認
EXPLAIN FORMAT=TREE SELECT ...;
-- 旧バージョンのプランと比較

-- ⑦ アプリのスモークテスト (主要ユースケース)

JSON の基本操作

-- ① JSON 型のテーブル
CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  attr JSON
);

INSERT INTO products VALUES
  (1, '{"color": "red", "size": "L", "tags": ["new", "sale"]}'),
  (2, '{"color": "blue", "size": "M"}');

-- ② JSON_EXTRACT と -> / ->> 演算子
SELECT JSON_EXTRACT(attr, '$.color') FROM products;
SELECT attr->'$.color' FROM products;       -- 同等 (JSON 値: "red")
SELECT attr->>'$.color' FROM products;      -- アンクオート (red)

-- ③ JSON 内検索
SELECT * FROM products WHERE attr->>'$.color' = 'red';
SELECT * FROM products WHERE JSON_CONTAINS(attr->'$.tags', '"sale"');

-- ④ JSON 配列の長さ
SELECT JSON_LENGTH(attr->'$.tags') FROM products;

Generated Column + Index で高速化

JSON カラムに直接 index は張れません。代わりにGenerated Column を作って、その列を index します。

-- ① attr->>'$.color' を Generated Column として持つ
ALTER TABLE products
  ADD COLUMN color VARCHAR(20)
    GENERATED ALWAYS AS (attr->>'$.color') VIRTUAL,
  ADD INDEX idx_color (color);

-- ② これで高速検索
SELECT * FROM products WHERE color = 'red';
-- → idx_color が使われる (高速)

-- VIRTUAL: 計算結果を保存しない (動的計算)
-- STORED:  計算結果を保存する (ディスク消費するが速い)

-- ③ 8.0.13+ なら Functional Index で直接
ALTER TABLE products ADD INDEX idx_color_func
  ((CAST(attr->>'$.color' AS CHAR(20))));

JSON_TABLE() (8.0.4+) — JSON を行に展開

JSON_TABLE() は JSON 配列やオブジェクトを「行ごとに展開」して SQL のテーブルとして扱える、8.0 の革命的機能。これがあるとアプリ側で JSON をパースする必要がなくなります。

-- データ例
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  items JSON
);
INSERT INTO orders VALUES (1, '[
  {"sku": "A1", "qty": 2, "price": 100},
  {"sku": "B2", "qty": 1, "price": 200},
  {"sku": "C3", "qty": 3, "price": 50}
]');

-- ① JSON 配列を行に展開
SELECT o.id, t.*
FROM orders o,
JSON_TABLE(o.items, '$[*]'
  COLUMNS (
    sku   VARCHAR(10) PATH '$.sku',
    qty   INT         PATH '$.qty',
    price DECIMAL(10,2) PATH '$.price'
  )
) AS t;

-- 結果:
-- +----+-----+-----+--------+
-- | id | sku | qty | price  |
-- +----+-----+-----+--------+
-- |  1 | A1  |   2 | 100.00 |
-- |  1 | B2  |   1 | 200.00 |
-- |  1 | C3  |   3 |  50.00 |
-- +----+-----+-----+--------+

-- ② 集計に使える!
SELECT o.id, SUM(t.qty * t.price) AS total
FROM orders o,
JSON_TABLE(o.items, '$[*]'
  COLUMNS (qty INT PATH '$.qty', price DECIMAL(10,2) PATH '$.price')
) AS t
GROUP BY o.id;

-- ③ NESTED で入れ子の JSON も展開
SELECT *
FROM orders o,
JSON_TABLE(o.items, '$[*]'
  COLUMNS (
    sku VARCHAR(10) PATH '$.sku',
    NESTED PATH '$.discounts[*]'
      COLUMNS (discount DECIMAL(5,2) PATH '$')
  )
) AS t;

-- ④ ERROR ハンドリング
SELECT *
FROM orders o,
JSON_TABLE(o.items, '$[*]'
  COLUMNS (
    qty INT PATH '$.qty' DEFAULT '0' ON ERROR DEFAULT '0' ON EMPTY
  )
) AS t;

Window 関数の基本

-- ① ランキング (ROW_NUMBER / RANK / DENSE_RANK)
SELECT
  country, user_id, total,
  ROW_NUMBER() OVER (PARTITION BY country ORDER BY total DESC) AS rn,
  RANK()       OVER (PARTITION BY country ORDER BY total DESC) AS rk
FROM orders;

-- 各 country ごとに「上位 3 件」だけ取る
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY country ORDER BY total DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;

-- ② 累積合計
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) AS cumsum
FROM revenue;

-- ③ 前後の行参照 (LAG / LEAD)
SELECT
  date, price,
  LAG(price)  OVER (ORDER BY date) AS prev_price,
  LEAD(price) OVER (ORDER BY date) AS next_price,
  price - LAG(price) OVER (ORDER BY date) AS diff
FROM stock_prices;

CTE と 再帰 CTE

-- ① 普通の CTE
WITH active_users AS (
  SELECT id, name FROM users WHERE deleted_at IS NULL
)
SELECT u.name, COUNT(o.id) AS cnt
FROM active_users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

-- ② 再帰 CTE — 組織図の階層展開
WITH RECURSIVE org AS (
  -- 開始: ルート (manager_id IS NULL)
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 再帰: 親を持つメンバーを追加
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org ORDER BY depth, id;

-- ③ 連番生成
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100
)
SELECT * FROM seq;

名前付き Window 句 — WINDOW 句で再利用

同じ Window 定義を複数回使うとき、WINDOW 句で名前を付けて再利用 できます。SQL が読みやすくなります。

-- ① 名前付き Window
SELECT
  country, user_id, total,
  RANK()       OVER w AS rk,
  ROW_NUMBER() OVER w AS rn,
  LAG(total)   OVER w AS prev_total
FROM orders
WINDOW w AS (PARTITION BY country ORDER BY total DESC);

-- ② 名前付き Window を継承して拡張
WINDOW
  base AS (PARTITION BY country),
  rank_w AS (base ORDER BY total DESC),
  moving_w AS (base ORDER BY created_at
                ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

再帰 CTE の停止条件と暴走防止

再帰 CTE は停止条件を間違えると無限ループ します。MySQL は安全装置として cte_max_recursion_depth (デフォルト 1000) で打ち切ります。

-- ① 暴走例 (停止条件なし)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq          -- ★ WHERE が無い → 無限再帰
)
SELECT * FROM seq;
-- ERROR 3636: Recursive query aborted after 1001 iterations.

-- ② 正しい停止条件
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000   -- ← 必須
)
SELECT * FROM seq;

-- ③ 最大深さを変更したい (本番では非推奨)
SET SESSION cte_max_recursion_depth = 10000;
-- ★ サブクエリでループしてしまうことが多いので注意

-- ④ 階層展開で典型的なパターン
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 0 AS depth, CAST(id AS CHAR(200)) AS path
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, t.depth + 1,
         CONCAT(t.path, ' > ', e.id)
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
  WHERE t.depth < 20                 -- ← 深さ制限を入れる
    AND FIND_IN_SET(e.id, t.path) = 0  -- ← 循環参照を防ぐ
)
SELECT * FROM org_tree;

-- ⑤ Materialization で再帰部分をキャッシュ
WITH RECURSIVE ...
-- 8.0 デフォルトで再帰 CTE は実体化される (パフォーマンス保証)

4 つの機能

-- ① Stored Procedure (CALL で呼ぶ)
DELIMITER //
CREATE PROCEDURE update_balance(IN uid BIGINT, IN delta DECIMAL(10,2))
BEGIN
  UPDATE accounts SET balance = balance + delta WHERE id = uid;
END //
DELIMITER ;

CALL update_balance(42, -100);

-- ② Stored Function (SELECT で使える)
DELIMITER //
CREATE FUNCTION user_total(uid BIGINT) RETURNS DECIMAL(10,2)
DETERMINISTIC READS SQL DATA
BEGIN
  RETURN (SELECT SUM(amount) FROM orders WHERE user_id = uid);
END //
DELIMITER ;

SELECT id, name, user_total(id) FROM users;

-- ③ Trigger (DML をフックする)
CREATE TRIGGER tr_orders_audit AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO orders_audit (order_id, old_status, new_status, changed_at)
VALUES (OLD.id, OLD.status, NEW.status, NOW());

-- ④ Event Scheduler (cron 相当)
CREATE EVENT ev_cleanup
ON SCHEDULE EVERY 1 DAY STARTS '2026-06-13 03:00:00'
DO DELETE FROM sessions WHERE expired_at < NOW();

-- 確認
SET GLOBAL event_scheduler = ON;
SHOW EVENTS;

DEFINER と SQL SECURITY

CREATE DEFINER = 'admin'@'localhost'
PROCEDURE update_balance(...) SQL SECURITY DEFINER
BEGIN ... END;
--           ↑                  ↑
--           DEFINER 句          実行権限の出所
--           (誰が定義したか)    DEFINER: 定義者の権限で実行 (デフォルト)
--                              INVOKER: 呼び出した人の権限で実行

-- DEFINER は VIEW でも使える (Section 44)
-- 「app ユーザーには SELECT 権限なし、VIEW 経由で限定的に見せる」設計に使う

DB にロジックを置くか?

権限の階層

-- 権限の粒度
GRANT SELECT             ON *.*           TO 'user'@'%';  -- グローバル
GRANT SELECT             ON myapp.*       TO 'user'@'%';  -- DB 単位
GRANT SELECT             ON myapp.users   TO 'user'@'%';  -- テーブル単位
GRANT SELECT (name,email) ON myapp.users  TO 'user'@'%';  -- カラム単位

-- 主要な権限
SELECT / INSERT / UPDATE / DELETE   -- DML
CREATE / ALTER / DROP / INDEX       -- DDL
CREATE ROUTINE / EXECUTE             -- Stored Procedure
CREATE VIEW / SHOW VIEW              -- VIEW
REFERENCES                           -- FK 作成
LOCK TABLES                          -- 明示ロック
TRIGGER                              -- Trigger 作成
EVENT                                -- Event Scheduler

-- 管理系 (本番アプリには付けない)
SUPER / CREATE USER / RELOAD / PROCESS / REPLICATION CLIENT / SHUTDOWN

Role (8.0+) — 権限の束を再利用

-- ① Role 作成
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- ② 権限付与
GRANT SELECT                       ON myapp.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';
GRANT ALL                          ON myapp.* TO 'app_admin';

-- ③ ユーザーに Role 付与
CREATE USER 'alice'@'%' IDENTIFIED BY 'xxx';
GRANT 'app_read' TO 'alice'@'%';
SET DEFAULT ROLE 'app_read' TO 'alice'@'%';  -- ★ これが必須

-- ④ ログイン時に有効化
mysql -u alice -p
> SELECT CURRENT_ROLE();   -- app_read
> SET ROLE app_write;       -- 切り替え可
> SELECT CURRENT_ROLE();   -- app_write

-- ⑤ 確認
SHOW GRANTS FOR 'alice'@'%' USING 'app_read';

禁忌: mysql.user テーブルの直接編集

-- ✗ 絶対やってはいけない (整合性破壊)
UPDATE mysql.user SET ... WHERE user='alice';
DELETE FROM mysql.user WHERE user='alice';

-- ○ 必ずコマンドで
ALTER USER 'alice'@'%' IDENTIFIED BY 'newpass';
DROP USER 'alice'@'%';
FLUSH PRIVILEGES;   -- 通常は不要 (DCL 文は自動反映)

Dynamic Privileges (8.0+) — SUPER の分割

5.x 時代のSUPER 権限は「実質的に全部できる」スーパー権限でした。8.0 では SUPER を用途別の Dynamic Privileges に分割し、最小権限の原則を実現しやすくしました。

-- 一覧
SELECT * FROM information_schema.user_privileges
WHERE privilege_type LIKE '%_ADMIN' ORDER BY privilege_type;

-- 主要な Dynamic Privilege:
BACKUP_ADMIN              -- LOCK INSTANCE FOR BACKUP / Clone Plugin Donor
BINLOG_ADMIN              -- binlog 管理 (PURGE BINARY LOGS など)
BINLOG_ENCRYPTION_ADMIN  -- binlog 暗号化
CONNECTION_ADMIN          -- max_connections_per_hour 等を超えて接続
CLONE_ADMIN               -- Clone Plugin (Recipient 側)
ENCRYPTION_KEY_ADMIN     -- TDE Master Key Rotation
FIREWALL_ADMIN           -- MySQL Enterprise Firewall
GROUP_REPLICATION_ADMIN  -- Group Replication 操作
INNODB_REDO_LOG_ARCHIVE  -- Redo Log archive (8.0.17+)
PERSIST_RO_VARIABLES_ADMIN  -- SET PERSIST_ONLY 系
REPLICATION_APPLIER       -- Replica が SQL を再実行
REPLICATION_SLAVE_ADMIN  -- レプリケーション設定
RESOURCE_GROUP_ADMIN     -- Resource Group 管理
ROLE_ADMIN                -- Role 管理
SESSION_VARIABLES_ADMIN  -- セッション変数の制限解除
SET_USER_ID               -- DEFINER の上書き
SHOW_ROUTINE              -- 全 Procedure 定義の閲覧
SYSTEM_USER               -- mysql.* システム DB の保護
SYSTEM_VARIABLES_ADMIN   -- 重要なシステム変数の変更
TABLE_ENCRYPTION_ADMIN  -- ENCRYPTION='Y' 等の操作
XA_RECOVER_ADMIN         -- XA トランザクション復旧

-- ★ 用例: バックアップユーザーには SUPER は不要
CREATE USER 'backup'@'%' IDENTIFIED BY 'xxx';
GRANT BACKUP_ADMIN, EVENT, PROCESS, REPLICATION CLIENT, SELECT,
      LOCK TABLES, SHOW VIEW, RELOAD
  ON *.* TO 'backup'@'%';

-- ★ レプリ用ユーザーには REPLICATION_SLAVE_ADMIN だけで足りる
CREATE USER 'repl'@'%' IDENTIFIED BY 'xxx';
GRANT REPLICATION SLAVE, REPLICATION_APPLIER ON *.* TO 'repl'@'%';

-- ★ SYSTEM_USER の重要性
-- SYSTEM_USER 保護: SYSTEM_USER を持たないユーザーは
-- SYSTEM_USER を持つ他ユーザーを編集できない
-- → DBA アカウントを「アプリ管理者」から守る

Mandatory Roles — 全ユーザーに強制適用

-- 全ユーザーに自動で付与される Role
SET PERSIST mandatory_roles = 'app_read,app_audit';

-- 確認
SHOW VARIABLES LIKE 'mandatory_roles';

-- 用途:
-- ・全アプリユーザーに監査ログ用の Role を強制
-- ・基本的な SELECT 権限を初期付与
-- ・後から個別に GRANT で追加権限を足す

VIEW + DEFINER による行制御

典型シナリオ: マルチテナント SaaS で「各テナントは自分のテナント ID のデータしか見えてはいけない」。

-- ① 元テーブル (テナント ID 列あり)
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  amount DECIMAL(10,2),
  ...
);

-- ② テナント別 VIEW (SECURITY DEFINER + セッション変数で絞り込み)
CREATE DEFINER = 'admin'@'localhost'
VIEW v_orders SQL SECURITY DEFINER AS
SELECT * FROM orders
WHERE tenant_id = CAST(@app_tenant_id AS UNSIGNED)
WITH CHECK OPTION;

-- ③ アプリ用ユーザー (元テーブルへの直接アクセスを禁止)
CREATE USER 'app'@'%' IDENTIFIED BY 'xxx';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.v_orders TO 'app'@'%';
-- ※ myapp.orders への GRANT は与えない

-- ④ アプリ側はセッション変数を設定してから VIEW を使う
SET @app_tenant_id = 42;
SELECT * FROM v_orders;   -- tenant_id=42 のデータだけ見える
INSERT INTO v_orders (id, tenant_id, amount) VALUES (1, 42, 100);
-- WITH CHECK OPTION により tenant_id=42 以外は INSERT 不可

-- 他テナント (tenant_id=99) は見えない
SET @app_tenant_id = 99;
SELECT * FROM v_orders;   -- tenant_id=99 のデータだけ

アプリ層との責任分担

DB だけで完璧な行制御は難しい。「アプリ層 + DB 層の二重防御」 がベストプラクティスです。

認証プラグイン

SSL/TLS 設定

-- ① my.cnf (サーバ側)
[mysqld]
ssl_ca   = /etc/mysql/ssl/ca.pem
ssl_cert = /etc/mysql/ssl/server-cert.pem
ssl_key  = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON   # SSL 強制 (8.0+)

-- ② 自動生成された証明書を使うことも可
-- (data dir に ca.pem 等が自動配置される)

-- ③ ユーザーごとに SSL を要求
ALTER USER 'app'@'%' REQUIRE SSL;
ALTER USER 'admin'@'%' REQUIRE X509;   -- クライアント証明書も必須に

-- ④ 確認
SHOW STATUS LIKE 'Ssl_cipher';
-- Ssl_cipher  TLS_AES_256_GCM_SHA384 など

-- ⑤ クライアント側
mysql -h primary -u app -p \
  --ssl-mode=VERIFY_IDENTITY \   # サーバ証明書を検証
  --ssl-ca=/etc/mysql/ssl/ca.pem

SSL モード

TLSv1.3 の有効化と Cipher 設定

8.0+ でTLSv1.3 がサポートされました。古い TLSv1.0 / 1.1 は脆弱性があり、本番では明示的に除外すべき。

-- ① 現在の対応 TLS バージョン
SHOW VARIABLES LIKE 'tls_version';
-- 8.0 デフォルト: TLSv1.2,TLSv1.3

-- ② TLSv1.0/1.1 を明示除外
SET PERSIST tls_version = 'TLSv1.2,TLSv1.3';

-- ③ Cipher (TLSv1.2 用)
SET PERSIST ssl_cipher =
  'ECDHE-ECDSA-AES256-GCM-SHA384:'
  'ECDHE-RSA-AES256-GCM-SHA384:'
  'ECDHE-ECDSA-CHACHA20-POLY1305:'
  'ECDHE-RSA-CHACHA20-POLY1305';

-- ④ Cipher (TLSv1.3 用)
SET PERSIST tls_ciphersuites =
  'TLS_AES_256_GCM_SHA384:'
  'TLS_CHACHA20_POLY1305_SHA256:'
  'TLS_AES_128_GCM_SHA256';

-- ⑤ 動的にリロード (再起動不要 — 8.0+)
ALTER INSTANCE RELOAD TLS;
-- 既存接続は維持 / 新規接続から新設定で

-- ⑥ 実際の接続確認
SHOW STATUS LIKE 'Ssl_version';
SHOW STATUS LIKE 'Ssl_cipher';
-- Ssl_version: TLSv1.3
-- Ssl_cipher:  TLS_AES_256_GCM_SHA384

証明書ローテーション運用

証明書は有効期限があるので必ず更新作業が発生します。本番では有効期限切れ前に ALTER INSTANCE RELOAD TLS で無停止更新 が定石。

# ① 新しい証明書を生成 (Let's Encrypt / 社内 CA / openssl 等)
openssl req -new -x509 -days 365 -nodes \
  -newkey rsa:4096 \
  -keyout /etc/mysql/ssl/server-key.new.pem \
  -out    /etc/mysql/ssl/server-cert.new.pem \
  -subj "/CN=db.example.com"

# ② 既存ファイルを上書き (権限注意)
mv /etc/mysql/ssl/server-cert.pem /etc/mysql/ssl/server-cert.old.pem
mv /etc/mysql/ssl/server-key.pem  /etc/mysql/ssl/server-key.old.pem
cp /etc/mysql/ssl/server-cert.new.pem /etc/mysql/ssl/server-cert.pem
cp /etc/mysql/ssl/server-key.new.pem  /etc/mysql/ssl/server-key.pem
chown mysql:mysql /etc/mysql/ssl/server-*.pem
chmod 600 /etc/mysql/ssl/server-key.pem

# ③ 動的リロード (mysqld 再起動なし)
mysql -uroot -p -e "ALTER INSTANCE RELOAD TLS;"

# ④ 確認 (新規接続が新証明書を使うか)
mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/mysql/ssl/ca.pem \
  -h db.example.com -u app -p \
  -e "SHOW STATUS LIKE 'Ssl_cipher';"

# ⑤ 自動化: cron で毎月チェック
# 残り日数を計算して 30 日切ったら自動ローテート
openssl x509 -in /etc/mysql/ssl/server-cert.pem -noout -enddate

運用ミス 15 選

3 つの UPSERT 構文

使い方の実例

-- ① ON DUPLICATE KEY UPDATE (推奨)
INSERT INTO product_stock (product_id, qty, updated_at)
VALUES (42, 10, NOW())
ON DUPLICATE KEY UPDATE
  qty = qty + VALUES(qty),         -- 既存 qty に加算
  updated_at = NOW();              -- 更新日時を更新

-- 8.0.20+ では VALUES() の代わりに別 alias
INSERT INTO product_stock (product_id, qty)
VALUES (42, 10) AS new
ON DUPLICATE KEY UPDATE qty = qty + new.qty;

-- ② REPLACE (注意: 既存行が消える)
REPLACE INTO sessions (user_id, token, expires_at)
VALUES (42, 'abc', NOW() + INTERVAL 1 HOUR);
-- → 同じ user_id があれば DELETE → INSERT
-- → 同じセッションでも id (PK) が新しくなる
-- → FK で参照されていると削除できずエラー

-- ③ INSERT IGNORE
INSERT IGNORE INTO whitelisted_ips (ip, source)
VALUES ('10.0.0.1', 'manual'),
       ('10.0.0.1', 'auto');       -- 2 行目は重複として無視

-- ⚠ 同時にこんな問題も無視される
INSERT IGNORE INTO users (id, age) VALUES (1, 'not a number');
-- → age に文字列を入れようとした警告も無視 → 0 が入る (sql_mode 緩い時)

判断フロー

innodb_autoinc_lock_mode — 3 モード

なぜ番号に穴が空くのか

AUTO_INCREMENT は「ユニークかつ単調増加」を保証するが、連続を保証しないのが原則です。穴が空く典型シナリオ:

8.0 の革命 — AUTO_INCREMENT が永続化

5.7 までは AUTO_INCREMENT の最新値はメモリ上にしかなく、再起動で MAX(id)+1 を再計算していました。これが大事故の原因:

-- 5.7 までの問題シナリオ
-- 1. INSERT で id=100 が採番される (採番カウンタも 101 に)
-- 2. DELETE FROM users WHERE id=100  (削除済み)
-- 3. mysqld 再起動
-- 4. 再起動後の AUTO_INCREMENT = MAX(id)+1 = 99+1 = 100
-- 5. 新規 INSERT で再び id=100 が割り当てられる!
-- 6. → 既に存在しないと思っていた古い参照が新規行を指す事故

-- 8.0 で修正
-- ・採番カウンタを Redo Log に記録
-- ・再起動後もカウンタが復元される
-- ・MAX(id)+1 のような再計算はしない

-- 確認方法
SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_schema='myapp' AND table_name='users';

-- 手動リセット (危険、使うな)
ALTER TABLE users AUTO_INCREMENT = 1000000;

BIGINT で十分か?

BIGINT UNSIGNED は 0 〜 18,446,744,073,709,551,615 (約 1844 京)。普通の使い方では絶対枯渇しない。INT (約 21 億・43 億符号無し) はEC・SNS・ログ系で意外と早く到達することがある。

-- INT 枯渇の典型例
-- 1 秒に 100 INSERT → 1 日 864 万 → 約 250 日で 21 億超え

-- BIGINT で同じ → 約 5 億 8 千万年で枯渇 (= 実質無限)

-- 既存テーブルを INT → BIGINT に拡張
ALTER TABLE events MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;
-- → 巨大テーブルでは時間がかかる (gh-ost / pt-osc 推奨)

主要 sql_mode フラグ

8.0 のデフォルト sql_mode

SELECT @@sql_mode;
-- ONLY_FULL_GROUP_BY,
-- STRICT_TRANS_TABLES,
-- NO_ZERO_IN_DATE,
-- NO_ZERO_DATE,
-- ERROR_FOR_DIVISION_BY_ZERO,
-- NO_ENGINE_SUBSTITUTION

-- これらは「現代的」「厳密」な設定で本番標準
-- ★ 古い PHP アプリ等を 5.7 から 8.0 に移行すると壊れる可能性

-- セッション単位で緩める (緊急回避)
SET SESSION sql_mode = '';   -- 全フラグ無効化 (絶対やるな)

-- グローバルでカスタム
SET GLOBAL sql_mode =
  'STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
  -- ONLY_FULL_GROUP_BY だけ外したい時など

-- my.cnf
[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION

ONLY_FULL_GROUP_BY — 5.7 → 8.0 で壊れる代表例

-- 古い MySQL (sql_mode 緩い時) でこう書けた
SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id;
-- 5.7 では「name は適当な行のものを返す」謎挙動だった
-- 8.0 では:
-- ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
-- and contains nonaggregated column 'orders.name'

-- ① 正しい書き方 (集約関数を使う)
SELECT user_id, MAX(name), COUNT(*) FROM orders GROUP BY user_id;

-- ② ANY_VALUE() で「適当な行でいい」と明示
SELECT user_id, ANY_VALUE(name), COUNT(*) FROM orders GROUP BY user_id;

-- ③ JOIN で正規化された名前を取る
SELECT u.id, u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

日付の罠 — TIMESTAMP vs DATETIME と 2038 年問題

-- ① TIMESTAMP は 1970〜2038-01-19 03:14:07 UTC が上限
INSERT INTO events (ts) VALUES ('2038-01-20 00:00:00');
-- → エラー or NULL に降格 (sql_mode による)

-- ② DATETIME は 1000〜9999 年なので問題なし
ALTER TABLE events MODIFY ts DATETIME(6);

-- ③ TIMESTAMP の自動更新を意識する
-- DEFAULT CURRENT_TIMESTAMP / ON UPDATE CURRENT_TIMESTAMP は便利
-- だが <strong>明示的に値を入れたつもりが上書きされる</strong>事故あり

-- 安全な書き方: 自動更新したくない列にも明示的にデフォルト
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
updated_at DATETIME(6) NOT NULL
           DEFAULT CURRENT_TIMESTAMP(6)
           ON UPDATE CURRENT_TIMESTAMP(6),

-- ④ NO_ZERO_DATE は本番では必ず ON
-- 0000-00-00 や 2026-00-00 のような不正値を拒否

-- ⑤ タイムゾーン
SHOW VARIABLES LIKE 'time_zone';
-- 推奨: time_zone='+00:00' に固定して UTC で運用
-- アプリ側で各ユーザーの TZ に変換する設計

TDE (Transparent Data Encryption) — テーブルスペース暗号化

MySQL 5.7+ で導入されたInnoDB Tablespace Encryption。データファイル (.ibd) を AES-256 で暗号化します。クエリやアプリは透過的に動くのが TDE の本質。

-- ① keyring プラグインのロード
INSTALL PLUGIN keyring_file SONAME 'keyring_file.so';
-- 本番では keyring_vault (HashiCorp Vault) / keyring_aws 推奨

-- my.cnf
[mysqld]
early-plugin-load = keyring_file.so
keyring_file_data = /var/lib/mysql-keyring/keyring

-- ② テーブル作成時に暗号化を指定
CREATE TABLE secrets (
  id BIGINT PRIMARY KEY,
  data VARBINARY(1024)
) ENGINE=InnoDB ENCRYPTION='Y';

-- ③ 既存テーブルを暗号化
ALTER TABLE users ENCRYPTION='Y';

-- ④ Redo Log も暗号化 (8.0+)
SET GLOBAL innodb_redo_log_encrypt = ON;
SET GLOBAL innodb_undo_log_encrypt = ON;
SET GLOBAL binlog_encryption = ON;

-- ⑤ Master Key Rotation
ALTER INSTANCE ROTATE INNODB MASTER KEY;

圧縮の選択肢

Transparent Page Compression の使い方

-- ① テーブル作成時に指定
CREATE TABLE logs (
  id BIGINT PRIMARY KEY,
  message TEXT
) ENGINE=InnoDB COMPRESSION='zlib';
-- 'lz4' / 'zlib' / 'none' から選択

-- ② 既存テーブルに適用
ALTER TABLE logs COMPRESSION='lz4', ALGORITHM=INPLACE;

-- ③ 圧縮率の確認
SELECT
  NAME,
  ROUND(FILE_SIZE / 1024 / 1024, 1)        AS file_MB,
  ROUND(ALLOCATED_SIZE / 1024 / 1024, 1)   AS alloc_MB,
  ROUND((1 - ALLOCATED_SIZE / FILE_SIZE) * 100, 1) AS saved_pct
FROM information_schema.innodb_tablespaces
WHERE FILE_SIZE > 0
ORDER BY saved_pct DESC LIMIT 10;

Clone Plugin (8.0.17+) — InnoDB 物理コピーの革命

従来 Replica を新規作成するには XtraBackup でフルバックアップ → 転送 → restore → CHANGE SOURCE の手順が必要でしたが、Clone Plugin で1 コマンドで完結 するようになりました。

-- ① Donor 側 (コピー元)
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
-- clone 用ユーザー
CREATE USER 'clone_admin'@'replica_host'
  IDENTIFIED BY 'xxx';
GRANT BACKUP_ADMIN ON *.* TO 'clone_admin'@'replica_host';

-- ② Recipient 側 (コピー先 — 新規 Replica)
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
SET GLOBAL clone_valid_donor_list = 'primary_host:3306';

-- ③ 1 コマンドで Clone 実行!
CLONE INSTANCE FROM 'clone_admin'@'primary_host':3306
  IDENTIFIED BY 'xxx';

-- 内部動作:
-- 1. Donor の現在の状態をスナップショット
-- 2. Recipient のデータディレクトリを wipe
-- 3. ネットワーク経由で物理コピー (InnoDB Pages)
-- 4. GTID 情報も持ってくる → CHANGE SOURCE は不要
-- 5. Recipient を再起動 → クローン完成

-- ④ そのまま Replica として参加
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='primary_host',
  SOURCE_AUTO_POSITION=1;
START REPLICA;

MySQL Shell utilities — 8.0 の運用主役

MySQL Shell (mysqlsh) は単なるクライアントではなく、JavaScript / Python が使える運用ツール。util.* 名前空間に強力なユーティリティが揃っています。

util.dumpInstance + loadDump の実例

# mysqlsh で接続
mysqlsh root@primary

# ダンプ取得 (8 並列・最大圧縮)
> util.dumpInstance('/backup/full', {
    threads: 8,
    compression: 'zstd',
    chunking: true,
    bytesPerChunk: '128M',
    ocimds: false   // OCI MySQL Database Service 移行時は true
  })

# 別環境に ロード
mysqlsh root@target
> util.loadDump('/backup/full', {
    threads: 16,
    ignoreExistingObjects: false,
    showProgress: true
  })

# 100 GB DB が mysqldump 数時間 → util.dumpInstance 数十分に短縮

2 つの API — Classic vs X DevAPI

Document Store — MongoDB ライクな使い方

# mysqlsh で X DevAPI モードに入る
mysqlsh --uri root@localhost:33060

# Collection (= JSON ドキュメントコレクション) を作る
> var schema = session.createSchema('myapp');
> var coll = schema.createCollection('articles');

# ドキュメントを追加 (MongoDB ライク)
> coll.add({
    _id: '1',
    title: 'MySQL 8.0 入門',
    author: 'alice',
    tags: ['mysql', 'database'],
    views: 100
  }).execute();

# 検索
> coll.find('author = :a')
      .bind('a', 'alice')
      .execute();

# 更新
> coll.modify('_id = "1"')
      .set('views', 200)
      .arrayAppend('tags', 'new')
      .execute();

# 削除
> coll.remove('_id = "1"').execute();

# 内部: articles という JSON カラムを持つ InnoDB テーブル
# ↑ つまり ACID + Replication + Backup がそのまま使える

Document Store の本質 — InnoDB の上の JSON 層

Document Store はInnoDB の上に薄い JSON ラッパーを乗せたもので、内部は普通のテーブルです。SQL でも見えます。

-- Classic SQL で同じテーブルを見ると
mysql> USE myapp;
mysql> SHOW CREATE TABLE articles\G

CREATE TABLE `articles` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS
    (json_unquote(json_extract(`doc`, _utf8mb4'$._id'))) STORED NOT NULL,
  `_json_schema` json GENERATED ALWAYS AS
    (_utf8mb4'{"type":"object"}') VIRTUAL,
  PRIMARY KEY (`_id`),
  CONSTRAINT `$val_strict_...` CHECK (json_schema_valid(...))
) ENGINE=InnoDB

-- → JSON カラム + Generated Column の組合せで JSON ドキュメントを表現
-- → ACID / Replication / Backup がそのまま使える!

使いどころと現実

Hash Join (8.0.18+) — JOIN の性能革命

5.7 までは MySQL の JOIN は Nested Loop Join と Block Nested Loop Join しかありませんでした。8.0.18 でHash Join が追加され、大規模 JOIN がレベル違いに速く なりました。

-- ① 適用される典型シナリオ
EXPLAIN FORMAT=TREE
SELECT *
FROM big_table a
JOIN big_table b ON a.x = b.y;
-- index なし or index が選ばれない条件で:
-- 5.7 → Block Nested Loop で O(N×M) → 死ぬほど遅い
-- 8.0 → Hash Join で O(N+M) → 数百倍速い

-> Inner hash join (b.y = a.x)
   -> Table scan on b
   -> Hash
       -> Table scan on a

-- ② Hash Join が選ばれる条件
-- ・等価 JOIN (=) があること
-- ・どちらかのテーブルが <Em>join_buffer_size</Em> に収まること

-- ③ メモリ不足の場合
-- 8.0.18-19: メモリ不足だと Hash Join を諦めて Block Nested Loop に降格
-- 8.0.20+:   ディスクに溢れて (spill) Hash Join を継続できるように

-- ④ Hash Join を使うかどうか
SET optimizer_switch = 'hash_join=on';   -- 8.0.18+ デフォルト ON

-- ⑤ 明示的に強制
SELECT /*+ HASH_JOIN(a, b) */ ...
SELECT /*+ NO_HASH_JOIN(a, b) */ ...     -- 無効化

Optimizer Trace — 「なぜそのプランを選んだか」を覗く

Optimizer の判断過程をJSON で詳細に出力する機能。「なぜ index を使わないのか」「なぜ join order がこの順なのか」が見えます。

-- ① 有効化 (セッション単位)
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1000000;

-- ② 問題クエリを実行
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'JP'
GROUP BY u.id;

-- ③ Trace を取得
SELECT * FROM information_schema.optimizer_trace\G

-- *************************** 1. row ***************************
-- QUERY: SELECT u.name ...
-- TRACE: {
--   "steps": [
--     {"join_preparation": {...}},
--     {"join_optimization": {
--        "rows_estimation": [
--          {"table": "u", "range_analysis": {
--             "potential_range_indexes": [
--               {"index": "PRIMARY", "usable": false, "cause": "not_applicable"},
--               {"index": "idx_country", "usable": true, "key_parts": ["country"]}
--             ],
--             "best_covering_index_scan": ...
--          }}
--        ],
--        "considered_execution_plans": [...]    ← どんなプランを検討したか
--     }}
--   ]
-- }

-- ④ 無効化
SET optimizer_trace = 'enabled=off';

EXPLAIN ANALYZE for INSERT / UPDATE / DELETE (8.0.19+)

8.0.19 から DML 文も EXPLAIN ANALYZE が使えるようになりました。本当に走らせる のでトランザクション内で。

START TRANSACTION;

EXPLAIN ANALYZE
UPDATE orders SET status='shipped'
WHERE created_at < '2026-06-01' AND status='paid';

-- 実プランと実行時間 + 影響行数が見える
-- → 巨大 UPDATE の事前検証が安全にできる

ROLLBACK;  -- 結果だけ見て戻す

Index Skip Scan (8.0.13+) — 左端が違っても複合 Index を使える

複合 index (country, age) に対して WHERE age = 30 のような左端を省略したクエリ。通常は index が使えませんが、Index Skip Scan はcountry の各値を疑似的にループして index を使える場合があります。

-- INDEX (country, age) が存在
SELECT * FROM users WHERE age = 30;
-- ↑ 通常は country を絞り込んでいないので index 不可

-- 8.0.13+ の Skip Scan:
-- 1. country の distinct 値を取得 ('JP', 'US', 'CA' の 3 つ)
-- 2. 各 country について WHERE country='X' AND age=30 を index で実行
-- 3. 結果を合成
-- → country の cardinality が低いとき有効

# EXPLAIN
+----+-------------+-------+-------+---------------+---------+----------+--------------------+
| id | select_type | table | type  | possible_keys | key     | rows     | Extra              |
+----+-------------+-------+-------+---------------+---------+----------+--------------------+
| 1  | SIMPLE      | users | range | idx_co_age    | idx_co_age | 6     | Using where;       |
|    |             |       |       |               |         |          | Using index for    |
|    |             |       |       |               |         |          | skip scan          |
+----+-------------+-------+-------+---------------+---------+----------+--------------------+

-- 設定 (デフォルト ON)
SHOW VARIABLES LIKE 'optimizer_switch';
-- skip_scan=on

-- 無効化
SET SESSION optimizer_switch = 'skip_scan=off';

Semijoin / Antijoin — サブクエリを Join に変換

EXISTS / IN / NOT EXISTS / NOT IN サブクエリは内部でSemijoin / Antijoin に変換され、相関ループより速く実行されます。

-- ① Semijoin に変換される EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- → 内部的に Semijoin (orders に該当があれば users 1 件返す)

-- 戦略は 5 種類:
-- FirstMatch:       1 件見つけたらループ脱出
-- LooseScan:        index で重複を飛ばしながら処理
-- Materialization: サブクエリを一時表化
-- DuplicateWeedout: 重複除去
-- Subquery materialization with table scan

-- EXPLAIN FORMAT=JSON で戦略を見る
EXPLAIN FORMAT=JSON SELECT ...\G
-- "first_match": "..." / "materialized_from_subquery": ...

-- ② Antijoin (8.0.17+ で公式 Optimizer 変換)
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- → orders に該当がない users だけ返す

-- 戦略を制御
SET SESSION optimizer_switch = 'semijoin=on,materialization=on,firstmatch=on';

-- Hints で個別に
SELECT /*+ SEMIJOIN(@subq MATERIALIZATION) */
       * FROM ... WHERE EXISTS (SELECT /*+ QB_NAME(subq) */ 1 FROM ...);

① Lateral Derived Table (8.0.14+)

「サブクエリの中で外側の列を参照する」がJOIN として書けるようになりました。それまでは相関サブクエリで N 回ループするか、変則的な書き方が必要でした。

-- ① ユーザーごとに最新 3 件の注文を取る (定番の難問)

-- 8.0.14+ Lateral で素直に
SELECT u.id, u.name, o.*
FROM users u,
LATERAL (
  SELECT * FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) o;

-- 旧来: Window 関数 + サブクエリで頑張る
SELECT * FROM (
  SELECT u.id, u.name, o.*,
         ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.created_at DESC) AS rn
  FROM users u JOIN orders o ON o.user_id = u.id
) t WHERE rn <= 3;
-- → どちらも書けるが Lateral のほうが読みやすい

② 本物の CHECK 制約 (8.0.16+)

5.x までの CHECK 句はパーサーが受け付けるだけで実際にはチェックされない 古典的バグでした。8.0.16 でついに本当にチェックされるように。

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY,
  balance DECIMAL(10,2) NOT NULL,
  status ENUM('active', 'closed') NOT NULL,
  CONSTRAINT chk_balance CHECK (balance >= 0),
  CONSTRAINT chk_status_balance CHECK (
    NOT (status = 'closed' AND balance > 0)
  )
);

INSERT INTO accounts VALUES (1, -100, 'active');
-- ERROR 3819: Check constraint 'chk_balance' is violated.

-- 制約の確認
SELECT * FROM information_schema.check_constraints
WHERE constraint_schema = 'myapp';

-- 一時的に無効化 (移行時)
ALTER TABLE accounts ALTER CHECK chk_balance NOT ENFORCED;
ALTER TABLE accounts ALTER CHECK chk_balance ENFORCED;

③ Invisible Column (8.0.23+)

SELECT * に出てこないカラム。「Blue-Green 移行で新カラムを追加するが、まだアプリには見せたくない」「監査カラムを隠したい」用途。

-- 新カラムを INVISIBLE で追加
ALTER TABLE users ADD COLUMN new_field VARCHAR(100) INVISIBLE;

-- SELECT * では見えない
SELECT * FROM users LIMIT 1;
-- (id, name, email のみ — new_field は出ない)

-- 明示的に指定すれば見える
SELECT id, name, new_field FROM users LIMIT 1;

-- アプリの準備ができたら可視化
ALTER TABLE users ALTER COLUMN new_field SET VISIBLE;

④ Generated Invisible Primary Key (8.0.30+)

PK のないテーブルを自動で PK 付きに変換する機能。InnoDB は実は内部で 6 byte の隠し PK (DB_ROW_ID) を作っていましたが、それを可視化したのが GIPK。レガシーテーブルでよく問題になる「PK 無し」を本番無停止で解消できます。

-- 有効化
SET sql_generate_invisible_primary_key = ON;

-- PK 無しでテーブル作成
CREATE TABLE legacy_table (
  data VARCHAR(100)
);
-- → 自動で my_row_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY INVISIBLE が付く

-- 確認
SHOW CREATE TABLE legacy_table\G
-- CREATE TABLE `legacy_table` (
--   `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE,
--   `data` varchar(100) DEFAULT NULL,
--   PRIMARY KEY (`my_row_id`)
-- )

-- アプリは PK の存在に気づかない (INVISIBLE)
-- でも GTID Replication や VITESS では PK が必須なので嬉しい

マネージド MySQL 比較

Aurora MySQL の独自性 — 分散ストレージ

Aurora MySQL はStorage Engine を AWS が書き直した ことが本質的な差分です。コミット時に Redo Log を同時に 6 つの Storage Node に書き、4/6 で成功なら OK とします (= quorum)。

マネージド共通の制約と注意点

マネージド固有のメリット

Aurora 関連サービス — Serverless v2 / Global / I/O Optimized

MySQL ファミリーマップ

運用ツール — 知っておくべき定番

技術選定マトリクス

GUI クライアントツール

PlanetScale の現状 (2024 年以降)

PlanetScale は Vitess を背景にしたサーバーレス MySQL 互換 サービスとして 2021 〜 2023 年に大きな注目を集めましたが、2024 年 4 月に無料プランを廃止し、選択肢として大きく変わりました。

この記事で扱ったこと (おさらい)

この記事の 3 つの本質

明日からできる 5 つのアクション

上級者への道 — 次に何を学ぶか