MySQLパターン

MySQLパターン は何をしますか？

このスキルは、MySQLまたはMariaDBのスキーマ設計、マイグレーション、スロークエリ調査、キュー形式のトランザクション、コネクションプール、プロダクションデータベースの構成に関わる作業時に使用してください。機能固有のパターンを適用する前に、エンジンとバージョンを正確に確認することを推奨します。MySQLとMariaDBは一部のSQL詳細で分岐しています。

アクティベーション条件

• MySQLまたはMariaDBのテーブル、インデックス、制約を設計するとき
• 大規模なプロダクションテーブルに適用するマイグレーションをレビューするとき
• スロークエリ、ロック待機、デッドロック、コネクション枯渇をデバッグするとき
• キーセットページネーション、アップサート、全文検索、JSONカラム、キューを追加するとき
• アプリケーションのコネクションプール、読み取りレプリカ、TLS、スローログを設定するとき

バージョン確認

エンジンとバージョンを特定することから始めます。

SELECT VERSION();
SHOW VARIABLES LIKE 'version_comment';

構文が異なる場合、MySQLとMariaDBのガイダンスを分けて管理します。

• MySQLでは、ON DUPLICATE KEY UPDATE内でのVALUES(col)に代わるものとして行エイリアスが推奨されており、VALUES(col)は非推奨です。
• MariaDBでは、挿入された値を参照する方法としてVALUES(col)がサポートされており、クロスエンジン互換性のために使用します。
• SKIP LOCKEDはキューライクな処理にのみ適しています。ロックされた行をスキップし、一貫性のないビューを返す可能性があるため、一般的な会計処理や整合性に敏感な読み取りに使用しないでください。

スキーマのデフォルト

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    status VARCHAR(32) NOT NULL,
    total DECIMAL(15, 2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_orders_account_status_created (account_id, status, created_at),
    KEY idx_orders_active (account_id, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

デフォルトの選択:

ユースケース	推奨	避けるべきもの
代理主キー	BIGINT UNSIGNED AUTO_INCREMENT	2B行を超える可能性があるテーブルでのINT
UUIDルックアップキー	変換ヘルパー付きのBINARY(16)	ホットテーブルでの VARCHAR(36) 主キー
金額と正確な数量	DECIMAL(p, s)	FLOAT または DOUBLE
ユーザー向けテキスト	utf8mb4 テーブルとインデックス	MySQLのデフォルト utf8 / utf8mb3
アプリケーションタイムスタンプ	アプリ側でUTC管理するDATETIME	タイムゾーンメタデータが格納されていると仮定したDATETIME
ソフトデリート	deleted_at DATETIME NULL とスコープ付きインデックス	インデックスなしでのソフトデリート行のフィルタリング
拡張可能なステータス値	ルックアップテーブルまたは制約付き VARCHAR	値が頻繁に変わる場合の ENUM

インデックス設計

複合インデックスの順序は、通常、等価条件のカラムを先に、次に範囲またはソートカラムを置きます。

CREATE INDEX idx_orders_account_status_created
    ON orders (account_id, status, created_at);

SELECT id, total
FROM orders
WHERE account_id = ?
  AND status = 'pending'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

インデックスを追加または変更する前に、EXPLAINを使用します。

EXPLAIN
SELECT id, total
FROM orders
WHERE account_id = 123 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

調査すべきシグナル:

フィールド	リスクシグナル
type	大規模テーブルでの ALL
key	選択的な述語が存在する場合の NULL
rows	インタラクティブなパスに対して非常に高い行推定値
Extra	Using temporary、 Using filesort、 または広範囲な Using where

インデックスを盲目的に追加しないでください。各インデックスは書き込みコスト、マイグレーション時間、バックアップサイズ、バッファプールプレッシャーを増加させます。

クエリパターン

アップサート

クロスエンジン互換の形式:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE
    setting_value = VALUES(setting_value),
    updated_at = CURRENT_TIMESTAMP;

MySQL行エイリアス形式:

INSERT INTO user_settings (user_id, setting_key, setting_value)
VALUES (?, ?, ?) AS new
ON DUPLICATE KEY UPDATE
    setting_value = new.setting_value,
    updated_at = CURRENT_TIMESTAMP;

行エイリアス形式は、ターゲットがMySQLであることが確認できた場合のみ使用してください。MariaDBまたはMySQL/MariaDB混在環境ではVALUES(col)を使用してください。

キーセットページネーション

SELECT id, name, created_at
FROM products
WHERE (created_at, id) < (?, ?)
ORDER BY created_at DESC, id DESC
LIMIT 50;

カーソルに一致するインデックスを用意します。

CREATE INDEX idx_products_created_id ON products (created_at, id);

大規模テーブルでの深いOFFSETページネーションは使用しないでください。サーバーがページを返す前に行をスキャンして破棄する原因になります。

JSONフィールド

JSONカラムは拡張データ用に使用し、リレーショナルなフィルタリングや制約が大量に必要なフィールドには使用しないでください。

CREATE TABLE events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payload JSON NOT NULL,
    event_type VARCHAR(64)
        GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.type'))) STORED,
    KEY idx_events_type (event_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

頻繁にクエリされるJSONパスについては、生成カラムを公開し、そのカラムにインデックスを作成します。外部キー、所有権、テナント分離、ライフサイクルフィールドはリレーショナルカラムとして維持してください。

全文検索

ALTER TABLE articles ADD FULLTEXT KEY ft_articles_title_body (title, body);

SELECT id, title, MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST (? IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 20;

タイポ寛容性、複雑なランキング、クロステーブルファセット、または組み込みの全文検索の動作を超える言語固有の分析が必要な場合は、外部の検索エンジンを使用してください。

トランザクション

トランザクションは短く保ち、ロックは一貫した順序で取得します。

START TRANSACTION;

SELECT id, balance
FROM accounts
WHERE id IN (?, ?)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - ? WHERE id = ?;
UPDATE accounts SET balance = balance + ? WHERE id = ?;

COMMIT;

デッドロックとロック待機のチェックリスト:

• すべてのコードパスで、ロックを決定的な順序で取得する。
• トランザクションを開始する前に、外部API呼び出しを完了させる（トランザクション内で呼ばない）。
• UPDATE、DELETE、ロック読み取りで使用される述語にインデックスを追加する。
• デッドロックが発生したら、ロールバックし、上限付きリトライポリシーでトランザクション全体を再試行する。
• デッドロック発生後、速やかにSHOW ENGINE INNODB STATUS\Gを取得する。後続のイベントで上書きされる。

キュー形式のワーカーによる取得:

START TRANSACTION;

SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

UPDATE jobs
SET status = 'processing', started_at = CURRENT_TIMESTAMP
WHERE id = ?;

COMMIT;

SKIP LOCKEDは、ロックされた行をスキップしても許容されるキューライクなワークロードにのみ使用してください。これは通常のトランザクション整合性の代替ではありません。

コネクションプール

SQLAlchemyの例:

from sqlalchemy import create_engine

engine = create_engine(
    "mysql+mysqlconnector://app:[email protected]/app",
    pool_size=10,
    max_overflow=5,
    pool_timeout=30,
    pool_recycle=240,
    pool_pre_ping=True,
    connect_args={"connect_timeout": 5},
)

Node.js mysql2の例:

import mysql from 'mysql2/promise';

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000,
});

const [rows] = await pool.execute(
  'SELECT id, total FROM orders WHERE account_id = ? LIMIT 50',
  [accountId],
);

アプリケーションのプールリサイクル時間は、サーバーのwait_timeoutより短く設定してください。サーバーがwait_timeout = 300の場合、pool_recycleは240秒程度が適切です。pool_pre_pingはネットワーク障害やフェイルオーバーからの復旧にも役立ちます。

診断

有用な初動コマンド:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS\G;
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

管理された環境でスローログを有効化:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

EXPLAIN ANALYZEは、クエリを安全に実行できる場合にのみ使用してください。ステートメントを実行するため、プロダクションサイズのデータではコストが高くなる可能性があります。

レプリケーション

読み取りレプリカは遅延する可能性があります。書き込み直後に、自分の書き込みを読むパス、チェックアウトフロー、権限チェック、冪等性キーの読み取りをレプリカにルーティングしないでください。

-- MySQLの従来用語（既存の環境では依然として一般的）
SHOW SLAVE STATUS\G;

-- サポートされている新しい用語
SHOW REPLICA STATUS\G;

コマンドを標準化する前にエンジン/バージョンを確認してください。レプリカのSQLスレッドの健全性、IOスレッドの健全性、ラグを監視します。TCP接続が生きているかどうかだけを確認しないでください。

セキュリティ

CREATE USER 'app'@'%' IDENTIFIED BY 'use-a-secret-manager';
GRANT SELECT, INSERT, UPDATE, DELETE ON appdb.* TO 'app'@'%';

ALTER USER 'app'@'%' REQUIRE SSL;

SELECT user, host
FROM mysql.user
WHERE user = '';

DROP USER IF EXISTS ''@'localhost';
DROP USER IF EXISTS ''@'%';

セキュリティレビューポイント:

• アプリケーションユーザーにALL PRIVILEGESや*.*を付与しない。
• トラフィックがホストやネットワークを越える場合、アプリケーションユーザーにTLSを要求する。
• 資格情報はプラットフォームのシークレットマネージャに保存し、サンプルコード、スクリプト、リポジトリファイルには記載しない。
• マイグレーション/管理ユーザーとランタイムのアプリケーションユーザーを分離する。
• パフォーマンスチューニングの前に、パブリックネットワークへの露出とバインドアドレスを監査する。

設定

専用データベースホスト向けの開始点の例:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

max_connections = 300
thread_cache_size = 50

wait_timeout = 300
interactive_timeout = 300
innodb_lock_wait_timeout = 10

slow_query_log = ON
long_query_time = 1
log_queries_not_using_indexes = ON

log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800

設定値は普遍的なプリセットとして扱うのではなく、レビューのためのトリガーとして扱ってください。メモリ、接続数、ログ保持期間、耐久性設定は、ワークロード、ハードウェア、バックアップポリシー、リカバリ目標に基づいてサイジングします。

アンチパターン

アンチパターン	リスク	より良いパターン
SELECT * をホットパスで使用	過剰なデータ取得と脆弱なクライアント	明示的なカラムを指定
深い OFFSET ページネーション	線形スキャンと遅いページ	キーセットページネーション
外部キー結合にインデックスなし	遅い結合とロックを伴う重い削除	FKカラムに意図的にインデックスを作成
長大なトランザクション	ロック待機と巨大なundo履歴	小さな作業単位でコミット
mysql.user への直接DML	権限テーブル破損のリスク	CREATE USER、ALTER USER、DROP USER を使用
管理者権限を持つアプリケーションユーザー	高い爆発半径	最小権限のランタイムユーザー
wait_timeout 以上のプールリサイクル	古いプール接続	タイムアウト未満でリサイクルし、pre-pingを有効化
書き込み後のレプリカ読み取り	古いユーザー向け状態	書き込み後読み取りのフローをプライマリに固定

出力期待値

このスキルをレビューに使用する場合、以下を返します。

1. エンジン/バージョンの前提条件
2. 最もリスクの高い正確性、ロック、セキュリティ、マイグレーションの問題
3. 安全なパスのための正確なSQLまたはコード変更
4. 検証計画：EXPLAIN、マイグレーションドライラン、ロック/デッドロックチェック、ロールバック基準
5. 推奨に影響を与えるMySQL/MariaDBの構文の違い

関連

• スキル: postgres-patterns - PostgreSQL固有のスキーマ・クエリパターン
• スキル: database-migrations - マイグレーション計画とロールアウトの安全性
• スキル: backend-patterns - API・サービス層パターン
• スキル: security-review - シークレット管理、認証、最小権限
• エージェント: database-reviewer - より広範なデータベースレビューワークフロー