MySQL ベストプラクティス

MySQL（主に 8.0 / 8.4 LTS 以降、InnoDB 前提）を設計・運用するうえでの実践的な指針をまとめたものです。

目次

1. スキーマ設計
2. データ型の選び方
3. インデックス設計
4. クエリの書き方
5. トランザクションとロック
6. 文字コード・照合順序
7. サーバー設定（my.cnf）
8. セキュリティ
9. バックアップとリカバリ
10. レプリケーションと高可用性
11. 監視とパフォーマンス分析
12. スキーマ変更（マイグレーション）
13. アプリケーション側の注意点
14. チェックリスト

1. スキーマ設計

基本方針

• ストレージエンジンは InnoDB を使う。 トランザクション、行ロック、クラッシュリカバリに対応しており、MyISAM を新規に選ぶ理由はほぼない。
• すべてのテーブルに主キー（PRIMARY KEY）を定義する。 InnoDB は主キー順にデータを格納する（クラスタインデックス）。主キーがないと内部的な隠しキーが使われ、レプリケーションや運用ツールで問題になる。
  • sql_require_primary_key = ON で強制できる。
• 正規化を基本とし、非正規化は計測に基づいて行う。 性能のための非正規化は、ボトルネックが確認できてから。

主キーの選び方

• UUID を使う場合は CHAR(36) ではなく BINARY(16) に格納する（UUID_TO_BIN(uuid, 1) / BIN_TO_UUID()）。
• 主キーはセカンダリインデックスすべてに含まれるため、主キーは小さいほど全体が軽くなる。

命名規則

• テーブル名・カラム名は小文字の snake_case に統一する（OS によって大文字小文字の扱いが異なるため）。
• 予約語をカラム名に使わない（order, key, group など）。
• 外部キーカラムは user_id のように 参照先_id 形式にそろえる。

制約

• NOT NULL をデフォルトにし、NULL が意味を持つ場合のみ許可する。NULL は比較・集計で直感に反する挙動をする。
• 外部キー制約は整合性を保証するが、ロックの範囲が広がり大量更新や分割時に制約となる。整合性重視なら使う、超高負荷・シャーディング前提ならアプリ側で担保、と方針を決めておく。
• CHECK 制約（8.0.16 以降で有効）も活用できる。

監査用カラム

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)

2. データ型の選び方

原則：要件を満たす最小の型を選ぶ。 小さい型はメモリ（バッファプール）とディスク I/O の効率に直結する。

DATETIME と TIMESTAMP

• TIMESTAMP はタイムゾーン変換が入り、2038年問題（上限 2038-01-19）がある。
• 新規設計では DATETIME を使い、アプリ側で UTC に統一して保存するのが無難。

TEXT / BLOB

• 大きな列は行外に格納され、一時テーブルがディスクに落ちやすくなる。
• 頻繁に読まない大きなデータは別テーブルに分離するか、オブジェクトストレージに置いてパスだけ保存する。

3. インデックス設計

基本

• WHERE・JOIN・ORDER BY・GROUP BY で使う列にインデックスを検討する。
• インデックスは読み込みを速くする代わりに書き込みとディスクを消費する。不要なものは削除する。
• 選択性（カーディナリティ）の低い列単独のインデックス（性別、フラグなど）は効果が薄い。

複合インデックス

• 左端一致の原則（leftmost prefix）：(a, b, c) は a、a,b、a,b,c の条件で使えるが、b 単独では使えない。
• 列の順序の目安：
  1. 等価条件（=）の列を先に
  2. 範囲条件（>, BETWEEN, LIKE 'x%'）の列は後ろに（範囲条件以降の列は絞り込みに使われにくい）
  3. ORDER BY の列を続けてソートを省略させる
• (a) と (a, b) の両方がある場合、(a) は冗長なことが多い。

カバリングインデックス

クエリが必要とする列をすべてインデックスに含めると、テーブル本体を読まずに済む（EXPLAIN の Extra に Using index）。

-- SELECT status, created_at FROM orders WHERE user_id = ? ORDER BY created_at DESC;
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at, status);

インデックスが使われなくなるパターン

-- NG: 列に関数・演算を適用
WHERE DATE(created_at) = '2026-10-29'
-- OK: 範囲で書く
WHERE created_at >= '2026-10-29' AND created_at < '2026-10-30'
 
-- NG: 前方ワイルドカード
WHERE name LIKE '%tanaka'
 
-- NG: 暗黙の型変換（文字列列に数値を渡す）
WHERE phone = 09012345678
-- OK
WHERE phone = '09012345678'

• 関数を使う必要がある場合は 関数インデックス（8.0.13 以降）や生成列を使う。

便利な機能（8.0 以降）

• 不可視インデックス：ALTER TABLE t ALTER INDEX idx INVISIBLE; で削除前に影響を安全に確認できる。
• 降順インデックス：(a ASC, b DESC) のような混在ソートに対応。
• 未使用・重複インデックスの検出：sys.schema_unused_indexes、sys.schema_redundant_indexes。

4. クエリの書き方

基本

• SELECT * を避け、必要な列だけ取得する。 転送量削減とカバリングインデックスの活用のため。
• 必ずプレースホルダ（プリペアドステートメント）を使う。 SQL インジェクション対策。
• N+1 問題を避ける。 ループ内でクエリを発行せず、JOIN や IN (...) でまとめて取得する。
• 大量 INSERT は複数行 INSERT やバッチ化でまとめる。

EXPLAIN を習慣にする

EXPLAIN SELECT ...;
EXPLAIN FORMAT=TREE SELECT ...;
EXPLAIN ANALYZE SELECT ...;   -- 実際に実行して実測値を表示（8.0.18 以降）

注目ポイント：

ページネーション

OFFSET が大きいと読み飛ばす行もすべて読むため遅くなる。キーセット（シーク）ページネーションを使う。

-- NG: 深いページで遅い
SELECT id, title FROM posts ORDER BY id DESC LIMIT 20 OFFSET 100000;
 
-- OK: 前ページ最後のIDを基準にする
SELECT id, title FROM posts WHERE id < :last_id ORDER BY id DESC LIMIT 20;

その他

• COUNT(*) は大きなテーブルでは重い。概算でよければ統計情報やキャッシュ済みカウンタを使う。
• OR が多い条件は UNION ALL に書き換えるとインデックスが効く場合がある。
• IN のサブクエリより EXISTS や JOIN が速いこともある。EXPLAIN で比較する。
• 大量の UPDATE / DELETE は 小さなバッチ（例：数千行ずつ）に分割し、ロック時間とレプリケーション遅延を抑える。

5. トランザクションとロック

基本方針

• トランザクションは短く保つ。 長いトランザクションはロック保持・undo ログ肥大化（履歴リスト長の増大）を招く。
• トランザクション内で外部 API 呼び出しやユーザー入力待ちをしない。
• autocommit の挙動を理解し、ドライバ／ORM のデフォルトを確認する。

分離レベル

• InnoDB のデフォルトは REPEATABLE READ。ギャップロック／ネクストキーロックによりデッドロックが増えることがある。
• 多くの Web アプリでは READ COMMITTED に変えることでロック競合が減る（その場合 binlog_format は ROW が必要）。
• どちらにするかはアプリの整合性要件で決め、チーム内で統一する。

デッドロック対策

• 複数テーブル・複数行を更新する順序を統一する（例：常に ID 昇順）。
• 更新対象の検索条件にインデックスを効かせ、ロック範囲を狭める（インデックスがないと広範囲がロックされる）。
• デッドロックは起こりうる前提で、アプリ側にリトライ処理を実装する。
• 調査には SHOW ENGINE INNODB STATUS の LATEST DETECTED DEADLOCK、または innodb_print_all_deadlocks = ON でエラーログに記録する。

行ロックの取得

SELECT ... FOR UPDATE;              -- 排他ロック
SELECT ... FOR SHARE;               -- 共有ロック（8.0 以降の書き方）
SELECT ... FOR UPDATE SKIP LOCKED;  -- ジョブキューなどでロック済み行を飛ばす
SELECT ... FOR UPDATE NOWAIT;       -- 待たずに即エラー

6. 文字コード・照合順序

• utf8mb4 を使う。 utf8（= utf8mb3）は絵文字など4バイト文字を格納できず、非推奨。
• 8.0 以降のデフォルト照合順序は utf8mb4_0900_ai_ci（アクセント・大文字小文字を区別しない）。
• 日本語を扱う場合は要件に応じて選ぶ：

  照合順序	特徴
  utf8mb4_0900_ai_ci	「ハ」と「パ」、全角半角などを同一視する場合がある
  utf8mb4_ja_0900_as_cs	日本語向け。清音・濁音・半濁音や大小文字を区別
  utf8mb4_bin	バイナリ比較。完全一致

• サーバー・DB・テーブル・接続の文字コードを揃える。 接続時も utf8mb4 を指定する。
• 照合順序が異なる列同士の JOIN はインデックスが効かなかったりエラーになったりするので統一する。

7. サーバー設定（my.cnf）

数値は環境依存のため、目安として扱い、負荷試験と監視で調整する。

[mysqld]
# --- 文字コード ---
character-set-server = utf8mb4
collation-server     = utf8mb4_0900_ai_ci
 
# --- InnoDB メモリ ---
innodb_buffer_pool_size      = 12G     # DB専用サーバーなら物理メモリの 60〜75% 程度
innodb_buffer_pool_instances = 8       # バッファプールが大きい場合に分割
 
# --- InnoDB ログ・耐久性 ---
innodb_redo_log_capacity       = 4G    # 8.0.30 以降（旧: innodb_log_file_size）
innodb_flush_log_at_trx_commit = 1     # 1 = 完全な耐久性（推奨）
sync_binlog                    = 1     # 1 = binlog も毎コミット同期（推奨）
innodb_flush_method            = O_DIRECT
 
# --- I/O ---
innodb_io_capacity     = 2000          # SSD/NVMe の性能に合わせる
innodb_io_capacity_max = 4000
 
# --- 接続 ---
max_connections = 500                  # 多すぎるとメモリ枯渇。接続プールで抑える
wait_timeout    = 600
 
# --- 安全性 ---
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql_require_primary_key = ON
innodb_file_per_table   = ON
 
# --- バイナリログ / レプリケーション ---
server_id                = 1
log_bin                  = mysql-bin
binlog_format            = ROW
binlog_expire_logs_seconds = 604800    # 7日
gtid_mode                = ON
enforce_gtid_consistency = ON
 
# --- ログ ---
slow_query_log      = ON
long_query_time     = 1
log_error_verbosity = 2
innodb_print_all_deadlocks = ON

ポイント

• sql_mode で STRICT モードを有効にする。 無効だと、桁あふれや不正値が警告だけで黙って切り詰められる。
• innodb_flush_log_at_trx_commit と sync_binlog を 1 以外にすると性能は上がるが、クラッシュ時にコミット済みデータを失う可能性がある。本番では原則 1。
• スワップさせない。 バッファプールを大きくしすぎてスワップが発生すると大幅に遅くなる。vm.swappiness を低めに設定する。
• 設定変更は1つずつ行い、効果を計測する。

8. セキュリティ

アカウント・権限

• 最小権限の原則。 アプリ用ユーザーには必要な DB への SELECT, INSERT, UPDATE, DELETE 程度のみ付与する。GRANT ALL ON *.* は使わない。
• 用途ごとにユーザーを分ける（アプリ用、読み取り専用、マイグレーション用、バックアップ用、監視用）。
• 接続元ホストを 'app'@'%' ではなく 'app'@'10.0.1.%' のように限定する。
• root はリモートログインを禁止し、日常利用しない。
• ロール（8.0 以降）で権限をまとめて管理する。

CREATE ROLE 'app_rw';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app_rw';
CREATE USER 'app'@'10.0.1.%' IDENTIFIED BY '...';
GRANT 'app_rw' TO 'app'@'10.0.1.%';
SET DEFAULT ROLE 'app_rw' TO 'app'@'10.0.1.%';

認証・通信

• 認証プラグインは caching_sha2_password（8.0 以降のデフォルト）。mysql_native_password は非推奨。
• 通信を TLS で暗号化する（require_secure_transport = ON）。
• パスワードポリシーを有効にする（validate_password コンポーネント）。

ネットワーク・その他

• MySQL のポート（3306）をインターネットに公開しない。プライベートネットワーク＋ファイアウォール／セキュリティグループで制限する。
• 認証情報をソースコードに直書きしない。シークレット管理サービスや環境変数を使う。
• 必要に応じて保存データの暗号化（InnoDB テーブルスペース暗号化）や監査ログを導入する。
• セキュリティパッチを含むマイナーバージョンアップを定期的に適用する。

9. バックアップとリカバリ

原則

• 「リストアできることを確認したバックアップ」だけがバックアップ。 定期的にリストア訓練を行う。
• RPO（どこまで戻せるか）と RTO（どれだけで復旧するか）を決め、それに合う方式を選ぶ。
• バックアップは別の場所（別リージョン・別アカウント） にも保管する。暗号化も行う。
• レプリカはバックアップの代わりにならない（誤った DELETE も即座に複製される）。

方式

# mysqldump で InnoDB を整合性を保ってダンプする例
mysqldump --single-transaction --routines --triggers --events \
  --source-data=2 --set-gtid-purged=ON appdb > appdb.sql

ポイントインタイムリカバリ（PITR）

• フルバックアップ＋バイナリログを保管しておけば、任意の時点まで復旧できる。
• binlog の保持期間はフルバックアップの間隔より長くしておく。

10. レプリケーションと高可用性

• GTID ベースのレプリケーションを使う。フェイルオーバーやレプリカの付け替えが容易になる。
• binlog_format = ROW を使う（非決定的なクエリでもデータが一致する）。
• レプリカには super_read_only = ON を設定し、誤書き込みを防ぐ。
• 準同期レプリケーションでデータ損失リスクを下げられる（書き込みレイテンシは増える）。
• レプリケーション遅延を監視する（SHOW REPLICA STATUS の Seconds_Behind_Source や、より正確には performance_schema のレプリケーション関連テーブル／ハートビート方式のツール）。
• 遅延対策として並列レプリケーション（replica_parallel_workers）を設定する。
• 読み取りをレプリカに振る場合、遅延による「書いた直後に読めない」問題をアプリ側で考慮する（直後の読み取りはプライマリへ、など）。
• 高可用性構成の選択肢：InnoDB Cluster（Group Replication + MySQL Router）、InnoDB ClusterSet、Orchestrator 等、またはクラウドのマネージドサービス（Amazon RDS / Aurora、Cloud SQL など）。
• フェイルオーバー手順を文書化し、定期的に訓練する。

11. 監視とパフォーマンス分析

監視すべき主な指標

分析ツール

• スロークエリログ ＋ pt-query-digest で重いクエリをランキング。
• Performance Schema / sys スキーマ：

  -- 合計実行時間が大きいクエリ
  SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
  -- フルスキャンしているクエリ
  SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
  -- 未使用インデックス
  SELECT * FROM sys.schema_unused_indexes;
  -- ロック待ち
  SELECT * FROM sys.innodb_lock_waits;

• 監視基盤：Percona Monitoring and Management (PMM)、Prometheus + mysqld_exporter + Grafana、Datadog など。

統計情報

• オプティマイザが誤った実行計画を選ぶ場合、ANALYZE TABLE で統計を更新する。
• 偏りのある列にはヒストグラム（8.0 以降）が有効：

  ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

12. スキーマ変更（マイグレーション）

• マイグレーションはコードとしてバージョン管理する（Flyway、Liquibase、各フレームワークのマイグレーション機能など）。
• 本番適用前に、本番相当のデータ量のステージング環境で所要時間とロックを確認する。
• 8.0 以降は多くの ALTER TABLE がオンライン DDL で実行できる。可能なら明示する：

  ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;      -- メタデータのみ変更（最速）
  ALTER TABLE t ADD INDEX idx_c (c), ALGORITHM=INPLACE, LOCK=NONE;

  指定したアルゴリズムが使えない場合はエラーになるので、意図せぬテーブルロックを防げる。
• 大きなテーブルの変更には gh-ost や pt-online-schema-change を検討する。
• オンライン DDL でも開始・終了時にメタデータロックを取る。長時間実行中のトランザクションがあると DDL が待たされ、後続の全クエリが詰まる。実行前に長いトランザクションがないか確認する。
• 後方互換を保つ段階的な変更（expand / contract パターン）：
  1. 新カラム追加（NULL 許可 or デフォルトあり）
  2. アプリを新旧両対応にデプロイ
  3. データ移行（バッチ）
  4. アプリを新カラムのみに切り替え
  5. 旧カラム削除

13. アプリケーション側の注意点

• コネクションプールを使う。 プールサイズは「アプリインスタンス数 × プールサイズ < max_connections」を守る。大きすぎるプールは逆効果。
• 接続・クエリにタイムアウトを設定する（max_execution_time やドライバのタイムアウト）。
• ORM が生成する SQL を確認する。 N+1、不要な SELECT *、意図しないフルスキャンが起きやすい。
• 頻繁に読まれ変更の少ないデータはアプリ側キャッシュ（Redis 等） を検討する（MySQL 8.0 でクエリキャッシュは廃止）。
• 全文検索や複雑な分析は、MySQL の FULLTEXT（日本語は ngram パーサ）で足りるか検討し、不足なら専用エンジン（Elasticsearch / OpenSearch、分析用 DWH）に任せる。
• タイムゾーンは DB・アプリともに UTC で扱い、表示時に変換する。
• 論理削除（deleted_at）を使う場合は、一意制約やインデックス、クエリ条件への影響を設計時に考える。

14. チェックリスト

設計

• 全テーブルが InnoDB で、主キーを持っている
• 文字コードが utf8mb4 で統一されている
• データ型が最小限かつ適切（金額は DECIMAL、日時は DATETIME）
• NOT NULL が基本になっている
• 主要クエリに対応するインデックスがあり、不要・重複インデックスがない

設定

• sql_mode が STRICT
• innodb_buffer_pool_size がメモリに見合っている
• innodb_flush_log_at_trx_commit = 1 / sync_binlog = 1
• GTID とバイナリログが有効
• スロークエリログが有効

セキュリティ

• アプリユーザーが最小権限
• 接続元ホストが限定されている
• TLS 通信が強制されている
• 3306 番ポートが外部に公開されていない

運用

• バックアップが自動取得され、リストア訓練をしている
• PITR のために binlog を保管している
• 監視・アラートが設定されている（遅延、接続数、ディスク、スロークエリ）
• スキーマ変更手順がバージョン管理・レビューされている
• バージョンアップ計画がある（サポート終了日を把握している）

補足： 設定値やバージョン固有の機能は MySQL のバージョンによって異なります。適用前に、使用中のバージョンの公式リファレンスマニュアル（https://dev.mysql.com/doc/）で確認してください。