跳至主要內容

《姜承尧的 MySQL 实战宝典》笔记

钝悟2025/3/17大约 10 分钟笔记数据库数据库MySQL

《姜承尧的 MySQL 实战宝典》笔记

拉勾教育教程 - 姜承尧的 MySQL 实战宝典 学习笔记

00 开篇词 从业务出发，开启海量 MySQL 架构设计

课程设计：

  • 模块一、表结构设计：字段类型的选型、表结构设计、访问设计、物理存储设计
  • 模块二、索引设计：索引原理、索引的创建和优化、索引的设计与调优（如多表 JOIN、子查询、分区表等）
  • 模块三、高可用架构设计
  • 模块四、分布式架构设计：分布式架构概述、分布式表结构设计、分布式索引设计、分布式事务
  • 模块五、拓展：热点更新、数据迁移

01 数字类型：避免自增踩坑

数字类型

整型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

浮点类型：FLOAT、DOUBLE，不推荐生产使用（精度问题，MySQL 8.0.17 后使用会抛警告）

DECIMAL 高精度类型，适合存储精确小数（如金额），但计算效率低，不推荐海量并发场景

业务表结构设计

自增主键：

  • 推荐使用 BIGINT 而不是 INT 作为自增主键，因为 INT 的上限（4 个字节）在海量业务中容易被突破。
  • 自增主键在 MySQL 8.0 版本前存在回溯问题（自增值不持久化），可能导致主键冲突。

Unsigned 属性的问题：

  • 不推荐使用 UNSIGNED 属性，因为在进行减法运算时可能导致错误。
  • 可以通过设置 sql_mode='NO_UNSIGNED_SUBTRACTION' 来避免减法运算的错误。

不认同此观点，业务场景中未必需要使用无符号整型进行相减，且即使需要相减，大多数情况下不需要在 SQL 中执行。

资金字段设计：余额字段推荐 BIGINT 类型，按分存储（如 1 元存储为 100）

  • DECIMAL 是变长字段，存储不如整型紧凑
  • DECIMAL 通过二进制编码实现，计算效率远不如整型

02 字符串类型：不能忽略的 COLLATION

字符串类型

  • CHAR(N)：固定长度字符，N: 0~255
  • VARCHAR(N)：可变长度字符，N: 0~65536

字符集

  • 推荐 UTF8MB4（支持 emoji 等更多字符）
  • 修改字符集用 ALTER TABLE … CONVERT TO …
  • 排序规则：_ci（不区分大小写）、_cs（区分大小写）、_bin（二进制比较）
  • UTF8MB4 下 CHAR 和 VARCHAR 底层都是变长存储

业务表结构设计

用户性别：使用 TINYINT 存储性别表达不清，推荐 CHECK 约束（MySQL 8.0.16+）限制取值范围

不认同使用 ENUM 类型，不利于通用和移植。

隐私数据加密：使用动态盐 + 非固定加密算法

密码存储格式：$salt$encryption_algorithm$value

CREATE TABLE User (
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  sex CHAR(1) NOT NULL,
  password VARCHAR(1024) NOT NULL,
  regDate DATETIME NOT NULL,
  CHECK (sex = 'M' OR sex = 'F'),
  PRIMARY KEY(id)
);

密码存储格式：$salt$cryption_algorithm$value，其中 $salt 是动态盐值，$cryption_algorithm 是加密算法，$value 是加密后的值。

03 日期类型：TIMESTAMP 可能是巨坑

日期类型

类型存储特点
DATETIME8 字节不涉及时区，支持毫秒精度 DATETIME(6)
TIMESTAMP4 字节支持时区转换，存在 2038 年问题

业务建议

  • 推荐 DATETIME：性能稳定，适合大多数场景
  • 不推荐 TIMESTAMP：存在 2038 年问题，默认系统时区需调用 __tz_convert() 可能引起性能抖动
  • 不推荐 INT：可读性差
  • 建议核心表增加 DATETIME 类型的 last_modify_date 字段，设置 ON UPDATE CURRENT_TIMESTAMP

04 非结构存储：用好 JSON 这张牌

实际业务场景中，使用 JSON 类型非常罕见。真需要存储无模式数据，更多会考虑使用 MongoDB、Elasticsearch 等专业的 Nosql。忽略

05 表结构设计：忘记范式准则

范式准则

  • 一范式：属性不可分
  • 二范式：消除部分依赖
  • 三范式：消除传递依赖

真实业务中不必严格遵守三范式，有时需要反范式设计以提升性能。

主键设计

表必须有主键。核心业务表不要用自增键做主键，原因：

  • 自增存在回溯问题（MySQL 8.0 解决）
  • 自增锁成为并发性能瓶颈
  • 只能保证实例内唯一，不能保证全局唯一
  • 公开数据值，容易引发安全问题
  • MGR、分布式架构存在问题

推荐：UUID（全局唯一但无序）或雪花算法（分布式 ID）

06 表压缩：不仅仅是空间压缩

  • MySQL 压缩基于页的压缩
  • COMPRESS 页压缩：适合性能要求不高的表（日志、监控表）
  • COMPRESS 压缩/解压双页严重影响性能
  • 推荐 TPC 压缩：性能不明显退化
  • 启用 TPC 后需执行 OPTIMIZE TABLE 立即压缩空间

07 表的访问设计：你该选择 SQL 还是 NoSQL？

MySQL 可以同时作为关系型数据库、KV 数据库和文档数据库使用，底层数据都存储在 InnoDB 引擎中。但是，

术业有专攻，MySQL 在关系型数据库以外的其他领域，并不适用。

08 索引：排序的艺术

索引原理

  • B+ 树索引是数据库最常用的索引结构
  • 树高通常 3~4 层，能在千万~亿级数据中快速定位
  • 插入时排序，提升查询速度但影响写入性能

B+ 树存储计算

InnoDB 页大小 16K，主键 BIGINT(8B) + 指针(6B) = 14B/键值对：

  • 根节点：16K / 14 ≈ 1100 个键
  • 叶子节点（记录平均 1000B）：16K / 1000 ≈ 16 条记录
  • 高度 2：约 16,100 条，2 次 I/O
  • 高度 3：约 1936 万条，3 次 I/O
  • 高度 4：约 25 亿条，4 次 I/O

优化插入性能

  • 顺序插入（自增 ID）维护代价小
  • 无序插入导致页分裂、旋转，影响性能

索引管理

  • mysql.innodb_index_stats：查看索引统计
  • sys.schema_unused_indexes：查看未使用索引
  • MySQL 8.0 支持索引不可见功能

总结

  • B+ 树高度 4 可存放约 25 亿数据，只需 4 次 I/O
  • MySQL 单表索引无个数限制
  • 通过 sys.schema_unused_indexes 和索引不可见特性清理无效索引

09 索引组织表：万物皆索引

索引组织表 vs 堆表

  • 堆表：数据无序存放，依赖索引排序，数据变更时性能差
  • 索引组织表：数据按主键排序存储，数据即索引，索引即数据

二级索引（非聚集索引）

  • 叶子节点存放索引键值 + 主键值，查询需回表获取完整记录
  • 插入顺序性影响性能：顺序插入好，随机插入差
  • 主键设计应紧凑，提升二级索引性能和存储效率

函数索引

  • MySQL 5.7+ 支持函数索引
  • 虚拟列（Generated Column）不占存储空间，在其上创建索引本质是函数索引

10 组合索引：用好，性能提升 10 倍！

组合索引：由多个列组成的 B+ 树索引，既可以是主键索引也可以是二级索引

排序与查询：

  • 组合索引 (a, b) 可优化 a = ? 和 a = ? AND b = ?，但不能优化 b = ?
  • (a, b) 和 (b, a) 排序结果完全不同

三大优势：

  1. 覆盖多个查询条件：提升查询效率
  2. 避免额外排序：如 (o_custkey, o_orderdate) 避免 filesort
  3. 覆盖索引：查询字段都在索引中，直接返回结果无需回表

11 索引出错：请理解 CBO 的工作原理

  • MySQL 优化器是 CBO（基于成本的优化器），会选择成本最低的执行计划
  • 一般只对高选择度字段创建索引，低选择度字段（如性别）不创建
  • 数据存在倾斜时，可创建直方图让优化器知道数据分布，校准执行计划

12 JOIN 连接：到底能不能写 JOIN？

  • MySQL 支持 Nested Loop Join（OLTP）和 Hash Join（OLAP）
  • OLTP 业务中 JOIN 是高效的，优化器自动选择最优执行计划
  • 索引设计是确保 JOIN 性能的关键，需 SQL Review 确保索引正确

13 子查询：放心地使用子查询功能吧！

  • 子查询比 JOIN 更易于理解
  • MySQL 8.0 可以"毫无顾忌"地写子查询
  • 老版本需 Review 子查询执行计划，出现 DEPENDENT SUBQUERY 必须优化（重写为派生表 + 表连接）

14 分区表

  • 支持 RANGE、LIST、HASH、KEY、COLUMNS 分区算法
  • 分区表创建需主键包含分区列
  • 唯一索引仅在当前分区唯一，非全局唯一，推荐使用 UUID
  • 分区表不解决性能问题，非分区列查询性能更差
  • 推荐用于数据管理场景

15 MySQL 复制

  • binlog 记录所有变更操作
  • 务必配置 crash safe 参数，否则可能主从数据不一致
  • 异步复制：非核心业务，不要求强一致
  • 无损半同步复制：核心业务（银行、证券），严格保障数据一致性
  • 多源复制：汇总多个 Master 数据
  • 延迟复制：防误操作，金融行业特别考虑

16 读写分离设计

  • binlog 是逻辑日志，大事务提交慢会影响同步 → 拆分大事务为小事务
  • 配置 MTS 并行复制（推荐 MySQL 5.7 + WRITESET）
  • 监控延迟不能依赖 Seconds_Behind_Master，最好配置心跳表

17 高可用设计

  • 高可用 = 无故障服务能力，度量单位是几个 9
  • 实现基础：冗余 + 故障转移

18 金融级高可用架构

  • 核心业务必设置无损半同步复制
  • 同城容灾：三园区架构（一地三中心/两地三中心），机房网络延迟 < 5ms
  • 跨城容灾："三地五中心"，跨城距离 > 200KM，延迟 > 25ms
  • 跨城容灾仅用于读多写少业务（如用户中心）
  • 需要额外的核对程序进行逻辑核对，可使用 last_modify_date 字段

一地三中心架构

三地五中心

19 高可用套件

  • MySQL 复制本身不能实现 failover
  • VIP：同机房同网段切换
  • 名字服务（如域名）：跨机房切换
  • 常用套件：MHA、Orchestrator（通过 ssh 通信，建议不超过 20 台节点）

20 InnoDB Cluster：改变历史的新产品

从未在业务场景中见过，忽略

21 数据库备份

全量备份：

  • 逻辑备份：mysqldump（简单但慢）、mysqlpump（多线程但不保证一致）、mydumper（推荐，一致性 + 多线程）
  • 物理备份：MySQL 8.0 Clone Plugin、Xtrabackup（速度快）

增量备份：备份 binlog，结合全量备份实现基于时间点恢复，使用 mysqlbinlog

22 分布式数据库架构：彻底理解什么叫分布式数据库

略

23 分布式数据库表结构设计

  • 先选择分片键：业务经常访问的字段，且能进行单元化
  • 分片算法：海量 OLTP 推荐 HASH，不推荐 RANGE
  • 推荐不同库名表名设计，方便后续扩缩容
  • 扩容时可使用过滤复制，仅复制需要的分片数据

24 分布式数据库索引设计：二级索引、全局索引的最佳设计实践

忽略

25 分布式数据库架构选型

知名 MySQL 分布式中间件：

  • ShardingSphere：Apache 顶级项目，社区活跃，支持多种分布式事务方案
  • DBLE：爱可生开源，已用于四大行核心业务
  • TDSQL：腾讯分布式数据库产品

26 分布式设计之禅：全链路的条带化设计

忽略

27 分布式事务：我们到底要不要使用 2PC？

忽略

Copyright © 2026 钝悟