MySQL/MariaDB データベース専門家

---

name: mysql description: MySQL/MariaDB に特化した深いデータベース専門知識 — 正しいデータ型と文字セット/照合順序（utf8mb4、金額には DECIMAL、適切なサイズの VARCHAR/INT、ENUM のトレードオフ）、InnoDB vs MyISAM、インデックス設計と EXPLAIN 駆動のクエリチューニング（複合インデックスの順序、カバリングインデックス、sargable 述語、キーセットページネーション）、トランザクション分離とロック（REPEATABLE READ、ギャップロック、デッドロック）、および MySQL 固有の正しさの落とし穴。package.json に mysql2 または mysql がリストされている場合、ORM の dialect/provider が mysql に設定されている場合（Prisma の provider = "mysql"、Sequelize の dialect: 'mysql'、TypeORM の type: 'mysql'/'mariadb'）、docker-compose サービスが mysql/mariadb イメージを実行している場合、mysql:// 接続文字列が存在する場合、またはタイ語/英語で schema/query を正しく効率的に書く/レビューするよう直接依頼された場合に使用します。database-schema-dev のエンジン非依存の移行ワークフローや backend-dev のアクセス層コードの代わりにはなりません — これらは、エンジンが実際に MySQL/MariaDB であるときに両方が参照する MySQL 固有の専門知識です。

MySQL

database-schema-dev と backend-dev の MySQL 固有の専門知識レイヤー： これらのエージェントはエンジン非依存であり、プロジェクトがすでに持っている移行ツール/ORM に合わせます — このスキルは、エンジンがたまたま MySQL またはその互換性の高いフォークである MariaDB である場合に、それらが参照する実際の MySQL 知識です。MariaDB が異なる動作をする場合は、その旨を明記します — 高度な機能について完全な互換性を想定しないでください。

ステップ 1 — エンジンが実際に MySQL/MariaDB であることを確認する

package.json で mysql2/mysql、ORM の dialect/provider（Prisma の provider = "mysql"、Sequelize の dialect: 'mysql'、TypeORM の type: 'mysql'/'mariadb'）、docker-compose.yml で mysql/mariadb イメージを実行するサービス、または mysql:// 接続文字列を確認します。これらのいずれも該当しない場合は、停止してください — database-schema-dev/backend-dev のエンジン非依存のデフォルト、または代わりに一致するエンジン固有のスキルを使用してください。

ステップ 2 — 文字セット、照合順序、ストレージエンジン

• デフォルトの文字セットは utf8mb4 であり、utf8 ではありません — MySQL の utf8 は最大 3 バイトのエイリアスであり、ほとんどの絵文字/特殊文字を保持できません — 明示的な照合順序を指定します（MySQL 8+ では utf8mb4_0900_ai_ci、MariaDB には 0900 照合順序はないため、両方をターゲットにする場合は utf8mb4_unicode_ci を使用します）。
• InnoDB を使用します。ただし、MyISAM に特定の理由がある場合（トランザクションなし、行ロックなし、FK なし）は除きます — MySQL 5.5/MariaDB 10.x 以降デフォルトであるため、通常は何も上書きしないことを確認するだけです。
• _ci 照合順序では '[email protected]' が '[email protected]' に一致します — 大文字小文字を区別して比較する必要があるもの（トークン、シークレット）には _bin/_cs（またはハッシュ化されたカラム）を使用します。

ステップ 3 — 正しいデータ型

• 金額/正確な小数：DECIMAL(p,s)、決して FLOAT/DOUBLE ではありません — バイナリ浮動小数点はほとんどの 10 進数の分数を正確に表現できません。
• 実際の範囲に基づいて整数を適切なサイズにします（TINYINT/SMALLINT/INT/BIGINT）— 習慣ですべて BIGINT にしないでください。小さい型は小さいインデックスを意味します。UNSIGNED は id カラムに無料のヘッドルームを提供しますが、これは負になることはありません。
• タイムゾーン変換を意図しない壁時計の値には DATETIME を使用します。TIMESTAMP（UTC 正規化、セッションタイムゾーンに応じて自動変換、2038 年範囲制限）は実際の瞬間にのみ使用します — デフォルトにしないでください。
• VARCHAR(n) は実際の制約に基づいてサイズ設定し、反射的に VARCHAR(255) にしないでください。本当に上限のないコンテンツには TEXT/BLOB を使用します — どちらもデフォルト値や完全（プレフィックス以外）インデックスを取れません。
• ENUM は固定され、めったに変更されないセットにのみ使用します — 値は定義順にソートされ、アルファベット順ではありません（ORDER BY で驚きを引き起こします）。MySQL 8 では値を追加するのはメタデータのみですが、MariaDB/古い MySQL ではテーブルの書き換えが発生する可能性があります。セットが成長したりメタデータが必要になったりすると、ルックアップテーブルの方がうまくスケールします。
• ネイティブ JSON（MySQL 5.7.8+/MariaDB 10.2+ — MariaDB の JSON は実際には LONGTEXT に CHECK を加えたものであり、バイナリ型ではないため、MySQL のバイナリストレージ/部分更新の利点は適用されません）は、真にスキーマレスのデータにのみ使用します — 定期的にフィルタリングまたは結合されるものは、実際のカラムに属します。

ステップ 4 — インデックス設計

• 複合インデックス：等価フィルタリングされるカラムを最初に、範囲/ソートカラムを最後に配置します — (status, created_at) は WHERE status = ? ORDER BY created_at に役立ちます。逆の順序では役立ちません。
• 左端プレフィックスルール：(a, b, c) のインデックスは (a) および (a, b) のルックアップにも役立ちます — それに加えて冗長な (a) インデックスを追加しないでください。
• カバリングインデックス（選択されたすべてのカラムを含む）により、MySQL はインデックスのみから回答できます（EXPLAIN で Using index）— ホットで狭いクエリには価値がありますが、デフォルトですべてのクエリに使用するわけではありません。
• 長い VARCHAR/TEXT にはプレフィックスインデックス（col(20)）を使用します — COUNT(DISTINCT LEFT(col, n)) を COUNT(DISTINCT col) と比較して n のサイズを決定します。
• 深いページネーション：LIMIT 20 OFFSET 100000 は毎回 100,000 行をスキャンして破棄します。キーセットページネーション（WHERE (created_at, id) < (:last_created, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20）は同じ複合インデックスを再利用し、カーソルに直接シークします — OFFSET の代わりに、無限スクロールや深い API ページネーションに使用します。
• すべてのインデックスは書き込み時間とディスク容量を消費します — 実際のクエリパターン（database-schema-dev ステップ 1）に基づいてインデックスを追加し、カラムごとに推測で追加しないでください。

ステップ 5 — EXPLAIN 駆動のクエリチューニング

• クエリが高速であると信じる前に、EXPLAIN（または EXPLAIN ANALYZE、MySQL 8.0.18+/MariaDB 10.4+）を実行します — クエリの形状だけで判断しないでください。
• type: ALL はフルテーブルスキャンです。ref/range/const はインデックスが実際に使用されていることを意味します — 最適化する価値のあるクエリは、テーブルサイズが自明な場合を除き、ALL を示すべきではありません。
• Extra: Using filesort/Using temporary は、MySQL がインデックスの外部でソート/マテリアライズしたことを意味します — 通常は、ORDER BY/GROUP BY に一致するインデックスで修正されます。
• インデックス付きカラムに対するラッパー関数または暗黙的なキャスト（WHERE YEAR(created_at) = ...、文字列カラムと数値の比較）はインデックスを無効にします — 代わりにベアカラム範囲として書き換えます（created_at >= '2024-01-01' AND created_at < '2025-01-01'）。
• ワイドテーブルでの SELECT * はカバリングインデックスを無効にし、誰も要求していない TEXT/BLOB カラムを取得します — 使用するものだけを選択してください。

ステップ 6 — トランザクションとロック

• InnoDB はテーブルではなく行をロックしますが、デフォルトの分離レベル REPEATABLE READ は、UPDATE/DELETE/ロッキング SELECT の範囲スキャンで ギャップロック も取得します — 範囲条件は、一致した行よりも多くのものをブロックする可能性があります。READ COMMITTED は、反復可能読み取りが必要でない場合、ギャップロックを回避します。
• そのスナップショットはプレーンな SELECT のみをカバーします — 同じトランザクション内のロッキングリード（SELECT ... FOR UPDATE、UPDATE、DELETE）は、代わりに最新のコミット済みデータを読み取るため、スナップショットが表示しなかった行を参照する可能性があります。スナップショット分離がロッキングリードも保護するとは想定しないでください。
• トランザクションは短く保ちます — オープントランザクションはそのロックを保持し、REPEATABLE READ 下では、InnoDB による古い行バージョンのパージもブロックします。
• SELECT ... FOR UPDATE を使用して、1 つのトランザクションで読み取り後に書き込まれる行をロックします（残高確認後の引き落とし）。コードベース全体で一貫した行順序でロックを取得し、デッドロックを回避します。
• デッドロック（ERROR 1213、SQLSTATE 40001）は実際の競合下で予想されます — InnoDB は一方を自動ロールバックします。そのコードをキャッチしてリトライし、トランザクションを長くして「修正」しないでください。SHOW ENGINE INNODB STATUS → LATEST DETECTED DEADLOCK は、各側が何を保持/待機していたかを示します。
• デフォルトでは接続ごとに自動コミットがオンになっています — 複数ステートメントの原子性には明示的なトランザクション（または ORM のラッパー）が必要であり、コード内での隣接性だけでは不十分です。

ステップ 7 — MySQL 固有の落とし穴と MariaDB の相違点

• sql_mode には STRICT_TRANS_TABLES（MySQL 5.7/MariaDB 10.2 以降デフォルト）を含める必要があります — これがないと、範囲外/間違った型の値はエラーにならずに静かに切り詰められます。緩和されていないことを確認してください。
• AUTO_INCREMENT 値にはギャップがないわけではありません（ロールバックでも 1 つ消費されます）— id が連続していると決して想定しないでください。
• セッション time_zone は TIMESTAMP 変換に影響しますが、DATETIME には影響しません — 接続ごとにタイムゾーンが混在するプールは実際の「時刻が間違っている」バグの原因になります。明示的に固定してください。
• MariaDB には MySQL に相当するものがない独自の拡張機能があります（CREATE SEQUENCE、書き込み時の RETURNING）— MySQL もターゲットの場合は使用しないでください。ウィンドウ関数/CTE には MySQL 8.0+/MariaDB 10.2+ が必要です。最初に固定バージョンを確認してください。
• MariaDB のオプティマイザと EXPLAIN 出力は MySQL のものと同じエンジンではありません — 一方で調整された計画を信頼するのではなく、使用中の実際のエンジンで EXPLAIN を再実行してください。

ステップ 8 — 完了報告前の自己チェック

新規/変更されたすべてのカラムに、意図的な型と文字セットがあり、デフォルトが utf8mb4 にフォールバックすることを確認します。新しいインデックスが実際のクエリパターンによって裏付けられており、EXPLAIN 出力で使用されていること、および深い/無制限のリストエンドポイントが OFFSET ではなくキーセットを使用していることを確認します。複数ステートメントの書き込みが明示的なトランザクションでラップされ、ロッキングリードの分離動作が意図的であり、想定されていないことを確認します。コンポーズファイル/CI が MariaDB を固定している場合、MySQL-8 のみの機能を追加したものがないことを確認します。

やってはいけないこと

• utf8 文字セットや金額に FLOAT/DOUBLE を使用しないでください — utf8mb4 と DECIMAL が正しいデフォルトです。
• 背後にクエリパターンがないインデックスを追加したり、EXPLAIN を確認せずにクエリを「高速」と呼んだりしないでください。
• 深くページングしたり無制限に実行できるリストに OFFSET ページネーションを使用しないでください — キーセットを使用します。
• トランザクションを、保護する作業よりも長く開いたままにしないでください。特に REPEATABLE READ のギャップロック下では注意が必要です。
• 正しさのために AUTO_INCREMENT の連続性や暗黙的な型変換に依存しないでください。
• MariaDB ターゲットに MySQL-8 のみの機能（ネイティブ JSON、utf8mb4_0900_ai_ci）があると、固定バージョンを確認せずに想定しないでください。
• エンジン非依存の移行/アクセス層コードを自分で書かないでください — それは database-schema-dev/backend-dev の役割です。このスキルは、それらが上に適用する MySQL 知識です。