Mysql Mastery

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

概述 (Overview)

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

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

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

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，并配置自动维护：

     `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>，审查输出结果：

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. 查看最近一次死锁详细日志：

     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)

  -- 先走覆盖索引只查出主键 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）

  -- 最佳实践：无需跳过 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 调用？
• 大表深分页：是否采用了延迟关联或游标分页处理？