# Mysql Mastery

> MySQL 8.0 生产级架构设计、性能调优与并发锁治理技能。 基于阿里 Java 开发手册(数据库篇)、美团慢查优化与高性能 MySQL 最佳实践。 涵盖 InnoDB 建表与字段规约 (强制 utf8mb4/NOT NULL/无符号自增主键)、索引设计黄金军规 (最左前缀/覆盖索引/EXPLAIN 调优)、 事务与并发锁治理 (间隙锁 Next-Key Lock/死锁排查/长事务防范)、深分页延迟关联优化及大表无锁 DDL 规范。

- Skill: `garfield247/mysql-mastery` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add garfield247/mysql-mastery`
- Raw SKILL.md: https://api.skillmd.com/api/skills/garfield247/mysql-mastery/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: Garfield247 (https://skillmd.com/u/garfield247)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/garfield247/mysql-mastery

---


# MySQL 生产级数据库设计与高并发治理技能 (MySQL Mastery Skill)

## 概述 (Overview)

本技能定义了基于 **MySQL 8.0 / 5.7 (InnoDB 存储引擎)** 进行生产级数据库设计、SQL 性能调优、高并发锁治理与数据一致性维护的权威标准。
对标《阿里巴巴 Java 开发手册（数据库篇）》、美团技术团队慢查询优化标准及《高性能 MySQL》，深刻践行 **“建表防御先行，索引覆盖避回表；事务短平快绝，行锁间隙防死锁；慢查零容忍度，大表热迁不锁表”** 的工业级工程哲学。

---

# 1. 建表与字段设计黄金军规 (Schema Design Standards)

表结构是系统的数据骨架，设计一旦失误，后期重构与迁移成本极高。必须严格遵循以下硬性标准：

```mermaid
graph TD
    A["新建数据库表"] --> B["1. 存储引擎: 强制 InnoDB"]
    A --> C["2. 字符集与校对规则: utf8mb4 / utf8mb4_unicode_ci"]
    A --> D["3. 主键铁律: 无符号自增整型 bigint unsigned (聚簇索引)"]
    A --> E["4. 字段强约束: 强制 NOT NULL + 业务默认值 DEFAULT"]
    A --> F["5. 审计三件套: id, created_at, updated_at"]
```

### 1.1 命名与基础规约
1. **统一小写与下划线**：表名、字段名必须全部使用小写英文字母与下划线（`snake_case`），禁止拼音、中英混杂或随意缩写；
2. **表名复数与前缀**：表名优先使用复数形式（如 `orders`, `users`, `order_items`），禁止在微服务内部滥用过长无意义的前缀；
3. **布尔/逻辑字段命名**：表达“是/否”概念的字段强制使用 `is_` 前缀（如 `is_deleted`, `is_active`），数据类型统一采用 `unsigned tinyint(1)`，`1` 表示是，`0` 表示否；
4. **严禁物理外键约束 (No Foreign Key)**：禁止使用物理外键 `FOREIGN KEY`，实体间的关联逻辑必须全部在业务应用层保证（防止并发写入级联锁表与高并发死锁）。

### 1.2 字段选型与存储优化
1. **主键设计铁律**：
   - 主键必须为单列自增整数 `bigint unsigned` 或严格时间单调递增的有序分布式 ID；
   - **绝对禁止使用无序 UUID / MD5 字符串作为主键**（无序主键会导致 InnoDB B+ 树频繁发生剧烈的页面分裂与磁盘碎片，写入性能出现断崖式下跌）。
2. **强制声明 `NOT NULL` 与默认值**：
   - **所有字段必须显式声明 `NOT NULL DEFAULT ''` 或 `DEFAULT 0`**；
   - **原因**：允许为 `NULL` 的列会占用额外的空值标记字节，导致单列索引与组合索引的统计算法失效、B+ 树范围判断复杂度上升，并且极易在聚合查询 `COUNT()` 或比较时触发意料之外的三值逻辑（`TRUE` / `FALSE` / `UNKNOWN`）。
3. **精准选择数据类型**：
   - 金额与高精度数值：**强制使用 `decimal(18, 4)` 或将单位缩小为“分/厘”存储为 `bigint`**，严禁使用 `float` 或 `double`（防浮点精度丢失）；
   - 状态与枚举：使用 `tinyint unsigned`；
   - 字符串：固定长度用 `char(N)`（如 MD5 校验码用 `char(32)`），可变长用 `varchar(N)`，长度严格按需定义，严禁动辄无脑声明 `varchar(255)`（排序与内存临时表按声明长度分配内存）；
   - 审计时间戳：统一使用 `datetime(3)` 或 `datetime`，并配置自动维护：
     ```sql
     `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
     `updated_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间'
     ```

---

# 2. 索引设计与 EXPLAIN 执行计划调优 (Indexing & Query Optimization)

索引是关系型数据库吞吐与低延迟的命脉。设计索引必须精准命中 B+ 树最左匹配原则：

### 2.1 索引设计核心军规
1. **业务唯一性唯一索引保护**：
   - 业务上具有唯一性的字段或字段组合，**必须建立唯一索引（Unique Index）**，绝不能仅仅依靠应用层的“先查后插”防并发重复，分布式环境下唯有数据库唯一索引是不可突破的最后防线；
2. **最左前缀匹配原则 (Leftmost Prefix Rule)**：
   - 联合索引 `idx_a_b_c(a, b, c)`，查询必须从最左列开始；
   - 联合索引中出现范围查询（`>`、`<`、`BETWEEN`、`LIKE 'abc%'`）时，该列右侧的所有列索引失效；
   - **字段顺序原则**：区分度最高（基数 Card 大）、等值匹配最频繁的列放在联合索引的最左侧。
3. **覆盖索引避回表 (Covering Index)**：
   - 尽量使索引树叶子节点包含 `SELECT` 需要的所有字段，直接从索引返回数据，彻底避免回表（Random I/O）查询聚簇索引；
   - **严禁 `SELECT *`**：必须显式声明所需列，为走覆盖索引创造条件。
4. **低区分度列禁止单列索引**：
   - 区分度低于 15% 的字段（如 `status`, `gender`, `is_deleted`）严禁单独建立单列索引；必须配合其他高基数列组成联合索引。

### 2.2 慢查询与 EXPLAIN 评级硬指标
排查所有业务查询时，必须在控制台运行 `EXPLAIN <SQL>`，审查输出结果：

| EXPLAIN 字段 | 达标合格线 | 🚨 致命红线 (必须阻断重构) | 调优指南 |
| :--- | :--- | :--- | :--- |
| **`type`** (访问类型) | `system` > `const` > `eq_ref` > `ref` > `range` | **`ALL`** (全表扫描)<br/>**`index`** (全索引扫描且无过滤) | 必须通过增加精准索引将 `type` 提升至 `range` 或 `ref` 级别。 |
| **`possible_keys` vs `key`** | `key` 命中预期索引 | `key` 为 `NULL` (未能使用任何索引) | 检查是否违背最左前缀，或是否触发了隐式类型转换。 |
| **`rows`** | 预估扫描行数应接近返回行数 | 扫描数十万行仅返回几条数据 | 说明过滤效率极低，需调整索引列顺序或拆分查询。 |
| **`Extra`** | `Using index` (覆盖索引)<br/>`Using index condition` (ICP下推) | **`Using filesort`** (无法利用索引排序)<br/>**`Using temporary`** (内存临时表) | 联合索引必须包含 `ORDER BY` 和 `GROUP BY` 字段以消除文件排序。 |

### 2.3 导致索引失效的七大死穴 (绝对红线)
1. **索引列上进行函数计算或表达式计算**：
   - ❌ `WHERE DATE(created_at) = '2026-09-18'`
   - ✅ `WHERE created_at >= '2026-09-18 00:00:00' AND created_at <= '2026-09-18 23:59:59'`
2. **隐式类型转换 (Type Coercion)**：
   - 字段为 `varchar` 类型的手机号，传参为整型：`WHERE phone = 13800000000`，MySQL 会自动调用 `CAST` 函数导致全表扫描；必须传字符串：`WHERE phone = '13800000000'`；
3. **前导模糊查询 (Leading Wildcard)**：
   - ❌ `LIKE '%keyword'`（索引无法定位边界，全表扫描）；
   - ✅ `LIKE 'keyword%'`（可走范围查询）；
4. **违背最左前缀法则**；
5. **范围查询右侧字段全部失效**；
6. **不等于操作（`!=` 或 `<>`）** 通常无法使用索引；
7. **`OR` 连接条件中存在无索引列**：若 `a` 有索引而 `b` 无索引，`WHERE a = 1 OR b = 2` 会导致索引完全失效。

---

# 3. 事务、并发锁治理与死锁防范 (Transactions & Deadlock Prevention)

InnoDB 的行级锁不是锁在记录上，而是**锁在索引树的记录与间隙上**：

### 3.1 事务“短平快”绝对法则
- **事务范围严格收敛**：
  - 严禁在数据库事务代码块内调用第三方 HTTP API、发送 RPC 请求、执行耗时算法或大文件读写；
  - 外部慢 I/O 会导致事务持续数十秒，瞬间耗尽数据库连接池，并引发严重的行锁等待超时（`Lock wait timeout exceeded`）；
- **锁释放时机**：行锁是在执行具体 SQL 时获取，但**直到事务提交（`COMMIT`）或回滚（`ROLLBACK`）时才释放**。在事务中，**越热点的资源更新（如扣减公共库存），越要放到事务的最后一步执行**，尽可能缩短热点锁持有时间。

### 3.2 深刻理解 Next-Key Lock 与死锁排查
- **间隙锁 (Gap Lock) 与临键锁 (Next-Key Lock)**：
  - 在可重复读隔离级别（RR, Repeatable Read）下，对于非唯一索引或范围查询，InnoDB 会锁定记录之间的间隙，防止幻读；
  - 当两个并发事务以相反顺序或者交叉插入相同间隙区间时，极易诱发死锁（Deadlock）；
- **死锁排查三板斧**：
  1. 查看最近一次死锁详细日志：
     ```sql
     SHOW ENGINE INNODB STATUS;
     ```
  2. 找到 `LATEST DETECTED DEADLOCK` 章节，剖析两个事务各自持有什么锁（Lock Mode X/S, locks gap/rec but not gap），以及正在等待什么锁；
  3. **死锁终结方案**：强制所有业务事务**按照统一的顺序访问相同的数据表和相同的行记录**；高并发秒杀场景考虑将隔离级别调整为 RC (Read Committed) 消除间隙锁。

---

# 4. 海量数据与大表运维规范 (Large Tables & High Volume)

### 4.1 深分页性能优化 (Deep Pagination)
- **痛点**：`SELECT * FROM orders WHERE uid = 100 ORDER BY id LIMIT 1000000, 20`。MySQL 会先扫描 1000020 条数据并全部回表，最后抛弃前 100 万条，耗时数秒。
- **优化方案一：子查询延迟关联 (Deferred Join)**
  ```sql
  -- 先走覆盖索引只查出主键 ID (内存中完成)，再通过内连接主键回表取 20 条完整数据
  SELECT o.* FROM orders o
  INNER JOIN (
      SELECT id FROM orders WHERE uid = 100 ORDER BY id LIMIT 1000000, 20
  ) tmp ON o.id = tmp.id;
  ```
- **优化方案二：游标分页（记录上次查询的最大 ID）**
  ```sql
  -- 最佳实践：无需跳过 100 万行，直接利用索引定位起点
  SELECT * FROM orders WHERE uid = 100 AND id > 1000000 ORDER BY id ASC LIMIT 20;
  ```

### 4.2 大表无锁 DDL 热变更规范
- 单表数据量超过 500 万行或物理文件大于 5GB 时：
  - **严禁直接在线执行 `ALTER TABLE add column/index`**（原生 DDL 会导致长时间锁表或主从复制严重延迟）；
  - 必须使用成熟的无锁热更工具：**`gh-ost`**（GitHub 开源，基于 Binlog 触发，主库零开销）或 **`pt-online-schema-change`**（Percona Toolkit，基于触发器）。

---

# 5. MySQL 规范审查 Checklist

- [ ] **表引擎与字符集**：是否强制为 InnoDB 与 `utf8mb4`？
- [ ] **主键规范**：是否为无符号整数自增主键，坚决避免无序 UUID？
- [ ] **NOT NULL**：所有字段是否均显式标注 `NOT NULL` 并具有业务默认值？
- [ ] **唯一约束**：业务唯一性字段是否已建立数据库级唯一索引？
- [ ] **索引命中**：查询是否违背最左前缀？是否排除了前导模糊、隐式类型转换与索引列函数？
- [ ] **杜绝 SELECT \***：是否显式查询必要字段以契合覆盖索引？
- [ ] **事务耗时**：事务内是否坚决移除了所有外部 HTTP/RPC 调用？
- [ ] **大表深分页**：是否采用了延迟关联或游标分页处理？