mysql

MySQL — 关系型数据库管理系统

MySQL 是最流行的开源关系型数据库管理系统（RDBMS），以 InnoDB 存储引擎为核心，支持 ACID 事务、外键约束和多种复制架构。

Workflow — 使用流程

遇到 MySQL 需求时，按以下顺序决策:

1. 明确场景
├── 建库建表 / 设计 schema?    → DDL 与数据类型参考 (references/05)
├── 复杂查询 / 报表分析?        → DML + 聚合/窗口函数 (references/03)
├── 性能慢 / 优化 SQL?          → 索引与执行计划 (references/06)
├── 备份 / 恢复?                → 备份与恢复 (references/08)
├── 主从 / 高可用?              → 复制与高可用 (references/07)
├── 存储过程 / 分区 / 事务?     → 高级特性 (references/09)
├── 字符串/日期/JSON 函数?       → 函数参考 (references/01-04)
└── 实战配置 / 搭建?             → 示例 (examples/)

2. 引擎选择: InnoDB (99% 场景) → MyISAM (只读归档) → MEMORY (临时表)
3. 索引设计: 主键先 → 查询/排序/JOIN 列建索引 → 检查最左前缀
4. 生产措施: 开启慢查询 → 配置主从复制 → 制定备份策略

When to Use / When NOT to

✅ Use When	❌ Skip When
需要 ACID 事务保障的业务系统（订单、支付、账户）	高频 KV 存取（<1ms 延迟，用 Redis）
数据结构固定、关系明确的 OLTP 场景	文档型非结构化数据（用 MongoDB）
需要复杂 JOIN/子查询的报表分析	海量日志/时序数据（用 ClickHouse）
中小规模到中大规模 OLTP（百万~亿级）	超大规模分布式事务（用 TiDB）
主从复制读写分离架构	图关系数据（用 Neo4j）
需要丰富内置函数和存储过程	全文搜索引擎为主（用 Elasticsearch）

Boundary — 能力边界

✅ 完全适用	⚠️ 有条件适用	❌ 不适用（替代方案）
OLTP 业务系统	单表过亿行（需分库分表）	纯内存缓存 → Redis
ACID 事务一致性	跨分片分布式事务（XA/Seata）	文档存储 → MongoDB
复杂 SQL（JOIN/子查询/聚合）	实时流计算（MySQL + Flink）	全文搜索 → Elasticsearch
主从复制读写分离	强一致多主写入（Galera/PXC）	时序大数据 → ClickHouse
mysqldump/XtraBackup 备份	JSON 深度查询（不如 MongoDB）	分布式强一致 → TiDB
分区表（RANGE/LIST/HASH）	高并发写入 > 1万 TPS	图数据库 → Neo4j

SQL 语法速查

类别	核心语法	详情参考
DDL	CREATE/ALTER/DROP TABLE，数据类型、约束	references/05-sql-ddl-types.md
DML	INSERT/UPDATE/DELETE，ON DUPLICATE KEY UPDATE	同上
DQL	SELECT/JOIN/GROUP BY/HAVING/UNION，CTE，子查询	references/03, 09
事务	START TRANSACTION/COMMIT/ROLLBACK/SAVEPOINT	references/09-advanced-features.md
分页	LIMIT/OFFSET（小表），WHERE id > :last 游标（大表）	references/06-index-optimization.md

函数速查

类别	最常用函数	详情参考
字符串	CONCAT, SUBSTRING, REPLACE, LPAD, GROUP_CONCAT, LENGTH	references/01-functions-string.md
日期时间	NOW, DATE_FORMAT, DATEDIFF, DATE_ADD, TIMESTAMPDIFF	references/02-functions-date.md
聚合	COUNT, SUM, AVG, MAX, MIN, GROUP_CONCAT	references/03-functions-aggregate-window.md
窗口 (8.0+)	ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE	references/03-functions-aggregate-window.md
JSON (5.7+)	JSON_EXTRACT, JSON_SET, JSON_CONTAINS, JSON_TABLE	references/04-functions-json.md
条件	IF, IFNULL, COALESCE, CASE WHEN	references/05-sql-ddl-types.md

高级特性索引

特性	简介	参考
视图 (View)	存储的查询定义，简化复杂查询和权限控制	references/09-advanced-features.md
CTE (8.0+)	命名临时结果集，支持递归树形查询	同上
存储过程	封装多条 SQL 带事务控制的业务逻辑	同上
触发器	自动响应 INSERT/UPDATE/DELETE 的事件处理	同上
事务与锁	ACID、MVCC、四种隔离级别、行锁/表锁/死锁	同上
分区表	RANGE/LIST/HASH/KEY 分区，数据归档加速	同上
全文索引 (5.6+)	FULLTEXT + MATCH AGAINST 代替 LIKE 搜索	references/06-index-optimization.md
函数索引 (8.0.13+)	对表达式/函数结果建索引	同上
降序索引 (8.0+)	混合排序方向的索引优化	同上
主从复制	Binlog + Relay Log 实现数据同步	references/07-replication-ha.md
半同步复制	至少一个 Slave 确认，平衡性能与一致性	同上
InnoDB Cluster	Group Replication + MySQL Router 原生 HA	同上
XtraBackup	物理热备份，支持增量	references/08-backup-restore.md
PITR	利用 Binlog 实现时间点恢复	同上

引擎对比

特性	InnoDB	MyISAM	MEMORY
事务	✅ ACID	❌	❌
外键	✅	❌	❌
行级锁	✅ 行锁	❌ 表锁	❌ 表锁
MVCC	✅	❌	❌
崩溃恢复	✅ redo log	❌ 需 REPAIR TABLE	❌ 重启即丢
全文索引	✅ 5.6+	✅	❌
缓存	Buffer Pool（数据和索引）	Key Cache（仅索引）	全内存
适用场景	99% 场景默认首选	只读归档（极少用）	临时表
表大小限制	64TB	256TB	max_heap_table_size

注意点 — よくある落とし穴

#	アンチパターン	問題	正しい方法
1	金額に FLOAT/DOUBLE を使う	浮動小数点の精度誤差	DECIMAL(10,2) を使う
2	WHERE 句の列に関数／暗黙の型変換を使う	インデックスが使われず、全表スキャンになる	列に対する関数操作を避け、型を一致させる
3	大量データのページネーションに OFFSET を使う	OFFSET が深くなるほど遅くなる	カーソルページネーション WHERE id > :last
4	主キーのないテーブル	行レベルロックができず、レプリケーション遅延が発生する	すべてのテーブルに BIGINT 主キーを設定する
5	本番環境で SELECT * を使う	帯域幅を浪費し、カバリングインデックスを利用できない	必要な列を明示する
6	大きな列にプレフィックスインデックスを設定しない	インデックスが大きくなり、B+ ツリーの効率が低下する	プレフィックスインデックス col(N) を使う
7	長時間のトランザクションをコミットしない	undo log が肥大化し、MVCC の負荷が増える	トランザクションのサイズを抑え、速やかに COMMIT する
8	インデックスが多すぎる	書き込み性能が低下する	1 テーブルあたりのインデックスは ≤ 5-8 個にする
9	InnoDB の大規模テーブルで COUNT(*) を使う	全表スキャンが必要（MyISAM のみキャッシュされる）	近似値またはカウント用テーブルを使う
10	LIKE '%keyword%' で検索する	インデックスを利用できない	FULLTEXT + MATCH AGAINST を使う
11	NOT IN (サブクエリ)	セミジョイン最適化が行われない	NOT EXISTS を使う
12	文字セットを混在させる	文字化けや、インデックスの暗黙の型変換が起こる	utf8mb4 に統一する
13	TEXT/BLOB が多すぎる	行オーバーフローが発生し、性能が低下する	子テーブルまたは OSS に分割する
14	REPLACE に関するよくある誤解	実際には DELETE+INSERT が行われる	要件を明確にしてから ON DUPLICATE KEY UPDATE を使う
15	バックアップの検証をしない	バックアップが破損していても誰も気づかない	毎月定期的にリストア演習を行う

FAQ

#	質問	回答
1	DATETIME と TIMESTAMP はどのように選べばよいですか？	TIMESTAMP はタイムゾーンを自動変換します（範囲 1970-2038）。DATETIME はタイムゾーンの影響を受けません（範囲 1000-9999）
2	VARCHAR の最大長はどのくらいに設定すればよいですか？	ビジネス要件に応じた適切な値（50-200）に設定します。意味もなく 255 に設定しないでください（一時テーブルのソートでは、定義された長さに応じてメモリが割り当てられます）
3	数百万件のデータをすばやく挿入するにはどうすればよいですか？	LOAD DATA INFILE（最速）、またはバッチ INSERT（1 バッチあたり 500-1000 行）を使い、AUTOCOMMIT を無効にします
4	いつデータベースやテーブルを分割する必要がありますか？	1 テーブルが 5000 万行を超える場合、または 1 インスタンスが 2TB を超え、さらに増加する見込みがある場合
5	MySQL 8.0 と 5.7 のどちらを選べばよいですか？	新規プロジェクトでは 8.0 を選びます（ウィンドウ関数、CTE、降順インデックス、アトミック DDL、Hash Join）
6	MySQL の性能を監視するにはどうすればよいですか？	スロークエリログ + pt-query-digest + Prometheus + Grafana + performance_schema
7	レプリケーション遅延にはどう対処すればよいですか？	Slave のハードウェアを確認し、大規模なトランザクションを分割し、並列レプリケーションを有効にし、重要な読み取りはプライマリで行います
8	誤操作でデータを削除してしまった場合はどうすればよいですか？	すぐに書き込みを停止し、Binlog PITR を使って誤操作前の時点に復元します
9	InnoDB が MyISAM より優れているのはなぜですか？	トランザクション、行ロック、クラッシュリカバリ、MVCC、外部キーに対応しているためです。MyISAM はすでに時代遅れです
10	現在のデータベースのアクティブな接続を確認するにはどうすればよいですか？	SHOW PROCESSLIST; または SELECT * FROM sys.session;
11	大規模なテーブルにインデックスを安全に追加するにはどうすればよいですか？	MySQL 8.0 では ALGORITHM=INPLACE, LOCK=NONE を使います。または pt-online-schema-change を使います
12	一意インデックスと通常のインデックスはどのように選べばよいですか？	一意性制約が必要なら UNIQUE を使います。クエリの高速化のみが目的なら通常のインデックスを使います
13	おすすめの管理ツールにはどのようなものがありますか？	CLI：mysql CLI。GUI：Sequel Ace / DataGrip / Navicat。コマンドライン：Percona Toolkit
14	utf8mb4 と utf8 の違いは何ですか？	utf8 は utf8mb3（最大 3 バイト）で、絵文字をサポートしません。utf8mb4 は絵文字を含む完全な Unicode をサポートします
15	デッドロックを調査するにはどうすればよいですか？	SHOW ENGINE INNODB STATUS; を実行し、LATEST DETECTED DEADLOCK の項目を確認します

キーワード

MySQL, Database, RDBMS, SQL, DDL, DML, DQL, DCL, InnoDB, MyISAM, MEMORY, ACID, transaction, index, B-Tree, EXPLAIN, query optimization, replication, master-slave, binlog, backup, restore, XtraBackup, mysqldump, PITR, partition, view, stored procedure, trigger, CTE, window function, JSON, utf8mb4, performance_schema, slow query, connection pool, sharding, high availability, HA

参考資料

公式ドキュメント

• MySQL 8.0 リファレンスマニュアル
• MySQL 8.0 リリースノート

ツール

• Percona XtraBackup
• Percona Toolkit
• Orchestrator — MySQL の高可用性管理
• gh-ost — オンラインでのテーブル構造変更

このスキルの詳細な参考資料

• references/01-functions-string.md — 文字列関数の総覧
• references/02-functions-date.md — 日付・時刻関数の総覧
• references/03-functions-aggregate-window.md — 集約関数とウィンドウ関数
• references/04-functions-json.md — JSON 関数
• references/05-sql-ddl-types.md — DDL とデータ型の詳細
• references/06-index-optimization.md — インデックスと実行計画
• references/07-replication-ha.md — プライマリ／レプリカレプリケーションと高可用性
• references/08-backup-restore.md — バックアップと復元
• references/09-advanced-features.md — 高度な機能（ビュー／CTE／ストアドプロシージャ／トリガー／トランザクション／パーティション）

実践例

• examples/01-connection-pool.md — コネクションプールの設定
• examples/02-slow-query-optimization.md — スロークエリの最適化
• examples/03-master-slave-setup.md — プライマリ／レプリカレプリケーションの構築
• examples/04-backup-strategy.md — バックアップ戦略

使用手順

Step 1: 環境の準備

開発環境に必要な依存関係とツールがインストールされていることを確認します。

Step 2: 初期設定

プロジェクトの要件に応じて基本設定を行います。

Step 3: コア機能の使用

サンプルコードに従ってコア機能を実装します。

Step 4: テストと検証

テストを実行し、機能が正常に動作することを確認します。

Step 5: デプロイ

開発完了後、デプロイと監視を行います。