MySQLパターン

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

このスキルは、MySQLまたはMariaDBのスキーマ設計、マイグレーション、スロークエリ調査、キュー形式のトランザクション、コネクションプール、本番データベースの設定に取り組む際に使用します。MySQLとMariaDBはいくつかのSQLの詳細が分岐しているため、機能固有のパターンを適用する前に、正確なバージョンを確認することを推奨します。

アクティベーション

• MySQLまたはMariaDBのテーブル、インデックス、制約を設計する場合
• 大規模な本番テーブルで実行する前にマイグレーションをレビューする場合
• スロークエリ、ロック待機、デッドロック、または接続枯渇をデバッグする場合
• Keysetページネーション、アップサート、フルテキスト検索、JSONカラム、キューを追加する場合
• アプリケーションのコネクションプール、読み取りレプリカ、TLS、スローログを設定する場合

バージョン確認

まずエンジンとバージョンを特定します:

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

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

• MySQLは、ON DUPLICATE KEY UPDATE内でVALUES(col)の代わりに行エイリアスを推奨しています。VALUES(col)は非推奨です。
• MariaDBは、ON DUPLICATE KEY UPDATEで挿入された値を参照する方法として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	20億行を超える可能性のあるテーブルでは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)を使用します。

Keysetページネーション

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;

1つのコマンドに標準化する前に、エンジン/バージョンを確認します。レプリカの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ページネーション	線形スキャンと遅いページ	Keysetページネーション
外部キージョインにインデックスがない	遅いジョインとロックの重い削除	FKカラムに意図的にインデックスを作成する
長いトランザクション	ロック待機と大きなundo履歴	小さな作業単位をコミットする
mysql.userへの直接DML	権限テーブル破損のリスク	CREATE USER、ALTER USER、DROP USERを使用する
管理者権限を持つアプリケーションユーザー	高い爆発半径	最小権限のランタイムユーザー
wait_timeoutより高いプールリサイクル	古いプール接続	タイムアウトより低くリサイクルし、プリピングする
書き込み後のレプリカ読み取り	古いユーザー向け状態	読み取り後書き込みフローをプライマリに固定する

出力期待値

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

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

関連

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