一航的笔记本YIHANG

Appearance

MySQL 学习笔记 ​

目录 ​

1. 核心基础概念【基础认知】 ​

1.1 什么是索引？它的本质是什么？ ​

索引是帮助 MySQL 高效获取数据的排好序的数据结构。数据库在存储数据之外，还额外维护着满足特定查找算法的数据结构（在 InnoDB 中主要是 B+ 树）。这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

类比理解：就像一本新华字典的"拼音目录"或"偏旁部首目录"。

  • 没有目录：查一个字需要从第一页翻到最后一页（全表扫描）
  • 有了目录：几次定位就能找到目标页

1.2 索引的优缺点是什么？为什么不给每个字段建索引？ ​

优点 ​

  1. 减少扫描数据量：大大减少服务器需要扫描的数据量，极大提高检索速度（降低磁盘 I/O 成本）
  2. 避免排序和临时表：索引天然有序，对 ORDER BY 和 GROUP BY 操作显著降低 CPU 消耗

缺点 ​

  1. 占用磁盘空间：索引是真实存在于磁盘上的文件
  2. 降低写操作性能：每次 INSERT、UPDATE、DELETE 时，MySQL 不仅要更新数据，还要动态维护索引结构

为什么不给所有列建索引？ ​

数据库的查询和写入是一把双刃剑。如果给所有列建索引：

  • 读虽然快了，但空间开销极其庞大（索引文件可能比数据文件还大）
  • 每次数据更新会引发大量索引树重新排序和节点分裂，导致写入性能崩溃

结论：建立索引需要寻找"读写平衡点"。

1.3 索引的开销体现在哪里？ ​

  1. 空间开销（磁盘侧）：索引需要存储在磁盘上（InnoDB 中数据和聚簇索引存在同一个 .ibd 文件里）。索引越多，表体积越大

  2. 时间开销（CPU/内存/磁盘IO侧）：主要体现在数据变更（DML操作）时。B+ 树为保持平衡和有序，插入和删除时会频繁发生"页分裂（Page Split）"和"页合并（Page Merge）"，消耗大量系统资源

2. 底层数据结构【重中之重】 ​

2.1 二叉查找树 (BST) 与红黑树 ​

问题：二叉树在极端情况下（如顺序插入主键）会退化成链表，查询时间复杂度从 O(log₂N) 退化成 O(N)

红黑树/AVL树：虽然通过自旋解决了"退化"问题，但本质还是"二叉"的，每个节点最多只有两个分支

致命缺陷：如果表有 1000 万条数据，树的高度会达到 20 多层。数据库中每一层通常对应一次磁盘 I/O，20 多次磁盘 I/O 的延迟极其可怕（可能需要几十到几百毫秒）

2.2 B 树 (B-Tree) ​

改进：把"二叉"变成"多叉"（通常叫 m 阶），树变"矮胖"了，有效降低树的高度

致命缺点（为什么 InnoDB 不用它）：

数据库读取数据的基本单位是"磁盘页"（InnoDB 默认一页 16KB）。在 B 树中，非叶子节点不仅存索引 Key，还存整行真实数据（Data）。这导致一个 16KB 的页里存不了几个索引，节点数量变多，树的高度又被拉高，最终增加了磁盘 I/O 次数

2.3 B+ 树 (B+Tree) ⭐ ​

InnoDB 的选择

特点 ​

  1. 非叶子节点极其纯粹：只存索引 Key 和指针（路标），不存 Data。这样 16KB 的节点可以存下上千个路标
  2. 高度控制：能把高度控制在 3~4 层，支撑千万级别甚至上亿的数据量。查一次数据最多只需 3~4 次磁盘 I/O
  3. 叶子节点双向链表：所有数据都在叶子节点，用指针串联成双向链表。这让 B+ 树不仅单次点查快，范围查询（Range Query）更是无敌

范围查询优势：比如查 id > 100，只要找到 100 这个节点，顺着链表往后拿数据就行，不需要回溯到上层节点

2.4 Hash 索引 ​

特点：Hash 算出的值是散列的、无序的

不支持的操作：

  • 范围查询（>, <, BETWEEN）
  • 排序（ORDER BY）
  • 模糊查询（LIKE 'abc%'）

自适应哈希索引（AHI）：InnoDB 默认用 B+ 树，但内部有自动优化机制。如果发现某个索引被非常频繁访问，会在内存中基于 B+ 树自动构建 Hash 索引来加速等值查询

3. 索引的分类与物理存储【逻辑与物理层面】 ​

分类维度 ​

分类方式类型
按数据结构B+Tree 索引、Hash 索引、Full-text 全文索引
按物理存储聚簇索引、非聚簇索引/二级索引
按字段特性主键索引、唯一索引、普通索引、全文索引
按字段个数单列索引、联合索引（复合索引）

3.1 聚簇索引 vs 非聚簇索引 ​

聚簇索引 ​

在 InnoDB 中，"索引即数据，数据即索引"。整张表的数据依附在聚簇索引的 B+ 树叶子节点上

  • 一张表有且只能有一个聚簇索引（通常是主键）
  • 数据文件本身就是索引文件

非聚簇索引（二级索引） ​

存的是当前索引的字段值 + 对应的主键 ID，绝对不存整行完整数据

为什么这么设计？

为了节省空间！如果每个二级索引的叶子节点都存一份完整的行数据，硬盘会爆掉，且数据一致性极难维护

3.2 什么是回表？ ​

假设表有 id（主键）、name（二级索引）、age 三个字段

执行：SELECT * FROM table WHERE name = '张三';

  1. MySQL 先去 name 的二级索引树里找，找到 '张三' 的叶子节点，拿到主键 ID = 5
  2. 但 SELECT * 需要所有字段（包括 age），二级索引里没有 age
  3. 于是 MySQL 拿着 ID = 5，再回到聚簇索引（主键）树里，查出整行完整数据

这个"跑回去再查一次"的动作，就叫回表

3.3 什么是覆盖索引？ ​

执行：SELECT id, name FROM table WHERE name = '张三';

  1. MySQL 同样去 name 的二级索引树里找，找到 '张三' 的叶子节点，拿到 ID = 5
  2. 这时 MySQL 发现，SELECT 只要 id 和 name，而当前这棵二级索引树的叶子节点里正好包含了 id 和 name
  3. MySQL 直接把数据返回，不用回表。这就是覆盖索引，极大提升查询性能！

4. 核心机制与高级特性【高阶理论】 ​

4.1 最左前缀匹配原则 (Leftmost Prefixing) ​

在联合索引 (a, b, c) 的 B+ 树中：

  • 数据首先按 a 全局排序
  • 在 a 值相同的情况下，再按 b 局部排序
  • b 相同再按 c 局部排序

为什么 b=1 AND c=2 失效？

脱离了 a，b 的值在整棵树里是无序散落的，MySQL 没法二分查找，只能放弃索引去全表扫描（或扫全索引）

关于 a=1 AND c=2：

  • MySQL 5.6 之前：只有 a 能走索引定位，c 用不上
  • MySQL 5.6 及之后：通过"索引下推"优化

4.2 索引下推 (Index Condition Pushdown, ICP) ⭐ ​

没有 ICP 时代（MySQL 5.6 之前） ​

InnoDB 引擎通过索引找到所有 a=1 的叶子节点后，因为二级索引里"不能用" c 的条件：

  1. 只能拿着这些节点里的主键 ID，疯狂"回表"去聚簇索引查出完整行数据
  2. 把数据扔给 MySQL 的 Server 层
  3. Server 层拿到数据后，再判断 c=2 是否成立

问题：如果 a=1 有 10 万条数据，就要回表 10 万次，非常慢！

有 ICP 时代（MySQL 5.6 及之后） ​

既然联合索引 (a, b, c) 的叶子节点里本来就存了 c 的值，MySQL 变聪明了：

  1. Server 层把 c=2 这个条件"下推"给 InnoDB 引擎
  2. InnoDB 在遍历 a=1 的二级索引节点时，顺手检查里面的 c 是不是等于 2
  3. 只有满足 c=2 的，才去"回表"查整行

核心目的：尽早在二级索引中过滤数据，大幅减少"回表"次数

4.3 前缀索引 (Prefix Index) ​

对于很长的字符串（如邮箱、URL），建立完整索引太占空间。可以只取前 n 个字符建索引

选择性（Selectivity）公式 ​

选择性 = 不重复的索引值数量 / 表记录总数

SQL 公式：

sql
SELECT COUNT(DISTINCT LEFT(column_name, n)) / COUNT(*) FROM table_name;

如何确定最佳前缀长度？ ​

  1. 不断调整 n 的大小（如 n=5, 6, 7）
  2. 当算出来的比例越接近 1，说明区分度越高
  3. 找到一个"长度最短，且区分度足够高（比如 > 0.9）"的 n 值

5. 索引失效场景【避坑指南】 ​

5.1 给数据"毁容"（破坏原本模样） ​

  • 使用函数
  • 类型转换
  • 隐式类型转换
  • 运算表达式

5.2 蒙住左眼找东西（破坏最左前缀） ​

  • LIKE 前缀模糊查询（如 LIKE '%张' 或 LIKE '%张%'）

5.3 寻找"大红海"（成本计算器觉得不划算） ​

  • NOT / !=（不等于、不为空）

5.4 一颗老鼠屎坏了一锅粥（逻辑短路） ​

  • OR 条件（只要有一个字段没索引，整个查询都可能失效）

6. 性能分析与调优实战【排查工具】 ​

使用 EXPLAIN ​

在 SQL 前加 EXPLAIN 分析执行计划

四个核心指标 ​

6.1 possible_keys 和 key（数据库的抉择） ​

指标含义
possible_keys候选人：可能用上的索引
key最终赢家：优化器实际跑的索引

关键判断：如果 possible_keys 有值，但 key 是 NULL，说明数据库算了一笔账，觉得索引还要回表，不如直接全表扫描

6.2 type（性能晴雨表） ​

type性能说明
const / system极快能够快速锁定唯一一行
eq_ref / ref极快用了普通索引，精准定位
range正常用了索引做范围查询
index勉强全索引扫描
ALL灾难全表扫描 ⚠️

6.3 Extra（附加信息） ​

Extra 值含义
Using index触发了覆盖索引 ✅
Using index condition触发了索引下推 (ICP) ✅
Using whereServer 层在内存中进行额外条件过滤
Using filesortORDER BY 无法利用索引顺序，需要重新排序 ⚠️
Using temporaryGROUP BY 需要临时表，消耗大 ⚠️

注意：Using filesort 或 Using temporary 说明你的排序/分组无法利用现有 B+ 树顺序，数据库被迫在内存或临时文件里重新排序，必须优化索引！

7. 设计规范与最佳实践【建库建表经验】 ​

7.1 应该在哪些列上建立索引？ ​

  1. 频繁作为 WHERE 查询条件的字段
  2. 经常需要 ORDER BY 和 GROUP BY 的字段
  3. 多表关联（JOIN）的 ON 字段

7.2 什么是索引的选择性（基数 Cardinality）？ ​

定义：不重复的索引值（基数）与表记录总数的比值

选择性 = COUNT(DISTINCT column_name) / COUNT(*)

取值范围：0 到 1 之间

选择性含义适合建索引
接近 1值几乎都不一样（如身份证号、手机号、UUID）✅ 适合
接近 0大量重复值（如状态码 0/1/2、性别男/女）❌ 不适合

7.3 哪些列不适合建立索引？ ​

  1. 致命雷区 1：选择性极低的字段
  2. 致命雷区 2：频繁更新的字段
  3. 致命雷区 3：数据量太少的表

8. 日志系统 ​

8.1 Undo Log【回滚/撤销日志】 ​

定义 ​

通俗理解：后悔药、历史快照，所属层级在 InnoDB 存储引擎特有

日志类型：逻辑日志，记录的是反向操作（如执行 INSERT 则记录 DELETE）

核心职责 ​

  1. 保证事务的原子性（Atomicity）：当事务回滚或异常中断时，通过执行反向操作将数据恢复到修改前状态

  2. 实现 MVCC（多版本并发控制）：解决读写冲突，实现"写不阻塞读"

底层机制（版本链） ​

InnoDB 为每行数据维护两个隐藏字段：

  • trx_id（事务ID）
  • roll_pointer（回滚指针）

每次修改数据时，旧数据存入 Undo Log，通过回滚指针将这些历史记录串联成版本链（Version Chain）。查询时配合 Read View（读视图）算法，找到对当前事务可见的历史版本

8.2 Redo Log【重做日志】 ​

定义 ​

通俗理解：防灾账本，断电不丢数据的底牌，所属层级在 InnoDB 存储引擎特有

日志类型：物理日志，记录的是"在某个数据页的某个偏移量修改了什么字节"

核心职责 ​

保证事务的持久性（Durability），实现 Crash-Safe（崩溃恢复） 能力

底层机制（WAL 技术） ​

  1. WAL (Write-Ahead Logging，日志先行)：

    • 修改数据时，先在内存（Buffer Pool）中修改
    • 立刻将物理变更追加写入 Redo Log 文件
    • 后台线程异步将内存脏页刷入磁盘真实数据文件

  2. 性能奥秘：

    • 直接写真实数据是极慢的随机 I/O
    • 写 Redo Log 是极快的顺序 I/O（追加写）

  3. 写入方式：

    • 大小固定，循环写（Circular Write）
    • 内存数据安全刷盘后，对应 Redo Log 空间可被覆盖重用

8.3 Binlog【归档日志】 ​

定义 ​

通俗理解：监控录像带，记录一切逻辑变更

所属层级：MySQL 服务层（Server Layer）共有，任何引擎都可用

日志类型：逻辑日志，记录的是 SQL 语句的原始逻辑或基于行的变更记录

核心职责 ​

  1. 数据备份与恢复：用于灾难后的增量数据恢复（PITR，将数据库恢复到过去的任意一秒，防删库跑路）

  2. 主从复制（Replication）：主库将 Binlog 同步给从库，从库进行回放，保证主从架构的数据一致

写入方式 ​

文件大小动态增长，追加写（Append Only），绝不覆盖历史数据

8.4 两阶段提交【2PC - Two-Phase Commit】 ​

定义 ​

通俗理解：将 Redo Log 和 Binlog 绑在一条船上，同生共死

核心目的：解决断电等异常情况下，Redo Log（引擎侧）与 Binlog（Server 侧）数据不一致的问题，最终保障主从集群的数据一致性

执行流程 ​

阶段一（Prepare）
    ↓
InnoDB 将修改操作写入 Redo Log，状态标记为 Prepare
    ↓
阶段二（写 Binlog）
    ↓
Server 层将修改逻辑完整写入 Binlog 文件
    ↓
阶段三（Commit）
    ↓
InnoDB 接收到 Binlog 写入成功信号，将 Redo Log 状态修改为 Commit
    ↓
事务正式完成

崩溃恢复的审判逻辑（极其重要） ​

当 MySQL 异常宕机重启时，根据 Redo Log 的状态判断：

Redo Log 状态判断逻辑处理方式
Commit事务已完美提交直接恢复数据
Prepare需查验 Binlog
→ Binlog 完整从库能收到这笔修改强行提交（Commit）
→ Binlog 不完整从库收不到这笔修改回滚（Rollback）

核心原则：以 Binlog 是否写入完整为最高裁决标准

9. MySQL 三层全局架构【宏观认知】 ​

MySQL 采用客户端/服务器（C/S）架构，内部实现了极其优雅的分层与插件式设计：

┌─────────────────────────────────────┐
│        客户端（Client）              │
└──────────────┬──────────────────────┘
               │ TCP/IP 连接
┌──────────────▼──────────────────────┐
│     连接层 (Connection Layer)        │  ← 网络通信、身份认证、连接管理
├─────────────────────────────────────┤
│      服务层 (Server Layer)           │  ← SQL 解析、优化、执行调度
│  - 连接管理  - 查询缓存(已移除)      │
│  - 解析器    - 优化器    - 执行器    │
├─────────────────────────────────────┤
│   存储引擎层 (Storage Engine Layer)  │  ← 数据落地存储、内存缓冲、事务锁
│  - InnoDB  - MyISAM  - Memory ...   │
└─────────────────────────────────────┘

9.1 连接层【通信与安全】 ​

当客户端与 MySQL 交互时，连接层完成以下核心动作：

1. 网络建连 ​

  • 基于 TCP/IP 协议进行三次握手建立连接
  • 断开时进行四次挥手
  • 高并发项目通常使用数据库连接池（Connection Pool）复用连接

2. 鉴权与上下文绑定 ​

  • 校验账号密码及 IP 白名单
  • 关键特性：权限在连接建立瞬间读取并绑定在当前连接上下文中
  • 后续修改权限不会影响已建立的连接（除非 FLUSH PRIVILEGES 且重新建连，或 KILL 线程）

3. 线程管理 ​

  • 为每个合法连接分配专属工作线程（Thread）
  • max_connections 控制最大并发数
  • 线程缓存（Thread Cache）复用销毁的线程
  • wait_timeout 机制剔除长时间休眠的空闲连接

9.2 服务层【四大核心组件】 ​

1. 查询缓存 (Query Cache) ​

⚠️ 在 MySQL 8.0 中已被官方彻底移除

原因：

  • 基于 SQL 文本的绝对哈希匹配，命中率极低
  • 任何 DML 操作都会引发该表所有缓存大面积失效
  • 维护成本远大于收益

现代架构通常将缓存层上浮至 Redis 等专业中间件

2. 解析器 (Parser) ​

（1）词法分析（Lexical Analysis）

  • 将 SQL 字符串切分为独立的 Token（关键字、标识符）

（2）语法分析（Syntax Analysis）

  • 校验语法规则（捕获语法错误）
  • 将 Token 组装成抽象语法树 (AST)

（3）预处理器（Preprocessor/语义分析）

  • 校验 AST 的语义真实性
  • 查验数据字典核对表名和列名是否存在

3. 优化器 (Optimizer) ⭐ ​

核心是 CBO (基于成本的优化器, Cost-Based Optimizer)

  • 分析底层统计信息
  • 计算各种索引组合及全表扫描的 I/O 与 CPU 成本
  • 生成成本最低的执行计划 (Execution Plan)

注意：统计信息不是实时的。如果短时间大量修改数据，可以主动刷新统计信息或指定索引

4. 执行器 (Executor) ​

根据优化器生成的执行计划开展工作：

  1. 细粒度的操作权限校验
  2. 调用存储引擎的统一接口（Handler API）
  3. 以循环方式向存储引擎索要数据
  4. 在 Server 层完成额外条件过滤（如 Using where）
  5. 将结果集返回给客户端

9.3 存储引擎层【底层基石】 ​

InnoDB vs MyISAM 核心对比 ​

维度InnoDBMyISAM
事务支持✅ 完整支持 ACID❌ 不支持
锁粒度行级锁 (Row Lock)表级锁
崩溃恢复✅ Crash-Safe❌ 容易损坏
物理存储聚簇索引非聚簇索引
外键✅ 支持❌ 不支持

自 MySQL 5.5 起，InnoDB 成为默认引擎

InnoDB 的 Buffer Pool 机制 ​

InnoDB 并非直接对磁盘进行高频读写，而是引入缓冲池 (Buffer Pool) 技术：

  1. 所有增删改查优先在内存中的缓冲池完成
  2. 修改后的数据页被称为"脏页 (Dirty Page）"
  3. 后台 I/O 线程通过异步刷盘机制慢慢写入磁盘
  4. 通过 Redo Log 保障数据一致性和持久性

优势：在保证极高并发性能的同时，确保数据安全

10. 事务机制 ​

10.1 事务的基础 ACID 特性 ​

特性全称含义
AAtomicity（原子性）事务内的操作要么全部成功，要么全部回滚失败
CConsistency（一致性）数据库状态从一个合法状态变为另一个合法状态（如转账前后总金额不变）
IIsolation（隔离性）多个并发事务同时执行时，互相不能看到对方的"半成品"或被互相干扰
DDurability（持久性）只要事务提交，数据绝对不会因为宕机断电而丢失

10.2 并发世界的三大灾难 ​

问题描述例子
脏读 (Dirty Read)读到了别人还没提交（甚至最后回滚了）的修改读到了根本不存在的数据
不可重复读 (Non-repeatable Read)同一事务内，两次读取同一行的"值"不一样别人在两次读取之间执行了 UPDATE 并提交
幻读 (Phantom Read)同一事务内，两次查询的"数据行数"不一样别人在两次读取之间执行了 INSERT/DELETE 并提交

10.3 四大隔离级别 ​

隔离级别脏读不可重复读幻读
读未提交 (Read Uncommitted)✅ 可能✅ 可能✅ 可能
读已提交 (Read Committed)❌ 避免✅ 可能✅ 可能
可重复读 (Repeatable Read)❌ 避免❌ 避免✅ 可能
串行化 (Serializable)❌ 避免❌ 避免❌ 避免

MySQL InnoDB 的默认事务隔离级别是：可重复读 (RR)

10.4 MVCC（多版本并发控制） ​

什么是 MVCC？ ​

MVCC (Multi-Version Concurrency Control) 是一种多版本并发控制机制，用于解决读写冲突，实现"写不阻塞读"。

核心原理 ​

MVCC 通过 Undo Log 的版本链 + Read View（读视图） 实现：

  1. 版本链：每行数据的隐藏字段 roll_pointer 将所有历史版本串联成链
  2. Read View：事务启动时生成的一个"快照视图"，记录当时活跃的事务 ID 列表
  3. 可见性判断：查询时根据 Read View 判断版本链中的哪个版本对当前事务可见

优势 ​

  • 读操作不加锁，不阻塞写操作
  • 写操作也不阻塞读操作
  • 极大提升了并发性能

10.5 锁机制 ​

锁的分类 ​

分类维度类型
按锁粒度全局锁、表级锁、行级锁
按锁类型共享锁（S锁）、排他锁（X锁）
按算法记录锁、间隙锁、临键锁（Next-Key）

InnoDB 的行锁 ​

锁类型说明
记录锁（Record Lock）锁定某一条具体记录
间隙锁（Gap Lock）锁定索引记录之间的间隙，防止幻读
临键锁（Next-Key Lock）记录锁 + 间隙锁的组合，RR 级别默认使用

锁的兼容性 ​

S锁（共享）X锁（排他）
S锁（共享）✅ 兼容❌ 不兼容
X锁（排他）❌ 不兼容❌ 不兼容

11. SQL 实战高频场景 ​

11.1 COUNT(*)、COUNT(1)、COUNT(column) 的区别 ​

写法含义注意点
COUNT(*)统计行数推荐，语义最清晰
COUNT(1)统计行数大多数场景与 COUNT(*) 等价
COUNT(column)统计该列非 NULL 的数量会忽略 NULL

结论：

  • 在 InnoDB 中，COUNT(*) 和 COUNT(1) 通常没有本质差别
  • 如果你要统计表总行数，优先写 COUNT(*)
  • 如果业务字段允许 NULL，COUNT(column) 很容易统计少了

11.2 EXISTS vs IN ​

IN ​

适合：

  • 子查询结果集较小
  • 语义偏向"某字段属于某个集合"
sql
SELECT *
FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE status = 1
);

EXISTS ​

适合：

  • 外层结果集较小，但子查询可利用索引快速判断是否存在
  • 语义偏向"是否存在满足条件的关联记录"
sql
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

经验原则：

  • 小表驱动大表、能用索引快速命中的场景，EXISTS 往往更稳
  • 不要死记硬背，一定看 EXPLAIN

11.3 JOIN 的基本原则 ​

常见 JOIN ​

  • INNER JOIN：只保留两边都匹配的数据
  • LEFT JOIN：保留左表全部数据，右表匹配不到补 NULL
  • RIGHT JOIN：语义对称，但工程中通常不推荐，统一改写成 LEFT JOIN

工程实践原则 ​

  1. 小结果集驱动大结果集
  2. 被驱动表的关联字段必须有索引
  3. 避免 SELECT *
  4. 多表 JOIN 前先确认业务是否真的需要一次查全

11.4 分页查询与深分页问题 ​

普通分页 ​

sql
SELECT id, name
FROM user
ORDER BY id
LIMIT 0, 20;

深分页问题 ​

sql
SELECT id, name
FROM user
ORDER BY id
LIMIT 100000, 20;

问题：

  • MySQL 需要先扫描并丢弃前 100000 行，再取后 20 行
  • 偏移量越大，性能越差

优化思路 1：基于主键游标翻页 ​

sql
SELECT id, name
FROM user
WHERE id > 100000
ORDER BY id
LIMIT 20;

优化思路 2：先定位主键，再回表 ​

sql
SELECT *
FROM user
WHERE id >= (
    SELECT id
    FROM user
    ORDER BY id
    LIMIT 100000, 1
)
ORDER BY id
LIMIT 20;

11.5 批量操作注意事项 ​

  • 批量 INSERT 要分批，避免单次事务过大
  • 批量 UPDATE / DELETE 要带条件、分批提交
  • 线上删除大表数据优先考虑按主键区间分段删除
  • 大事务会带来：锁持有时间变长、Undo 膨胀、主从延迟增加

12. 表设计与字段设计规范 ​

12.1 主键设计原则 ​

推荐：

  • 使用短、小、稳定、递增的主键
  • 常见做法：自增 ID、雪花 ID、业务无意义 Long 型主键

不推荐：

  • 使用很长的字符串做主键
  • 使用频繁变更的业务字段做主键

原因：

  • InnoDB 的聚簇索引叶子节点就是整行数据
  • 二级索引叶子节点存的是二级索引列 + 主键值
  • 主键越大，所有二级索引越大

12.2 常见字段类型选择 ​

场景推荐类型原因
金额DECIMAL避免浮点精度问题
时间点DATETIME / TIMESTAMP看范围与时区需求
状态值TINYINT节省空间
长文本TEXT不适合频繁排序过滤
主键/关联IDBIGINT兼容长期增长

12.3 varchar vs char ​

类型特点适用场景
CHAR定长长度固定、更新少的短字段
VARCHAR变长大多数字符串场景

经验：

  • 手机号、身份证号这类"看起来定长"的字段，在很多系统里仍常用 VARCHAR
  • 因为它们本质是字符串，不参与数值计算，且未来规则可能变化

12.4 datetime vs timestamp ​

类型特点
DATETIME范围大，和时区转换关系较弱
TIMESTAMP占用更小，受时区影响，更适合记录系统时间

建议：

  • 业务创建时间、更新时间常用 DATETIME
  • 如果非常明确依赖服务器时区和自动更新时间，可考虑 TIMESTAMP

12.5 建表规范建议 ​

  1. 所有表都要有主键
  2. 尽量设置 NOT NULL
  3. 重要字段要有默认值，避免业务侧空指针和三值逻辑混乱
  4. 字段名、表名要有统一规范，不使用保留字
  5. 业务系统中谨慎使用外键，很多团队更倾向应用层保证一致性

12.6 为什么很多互联网项目少用外键 ​

优点：

  • 数据库层强约束，数据一致性更直观

缺点：

  • 跨表耦合增强
  • DDL 和扩容迁移复杂
  • 高并发场景下排查问题不够灵活

结论：

  • 核心不是"外键好不好"
  • 而是是否符合你的系统规模、团队规范和治理能力

13. 联合索引设计方法论 ​

13.1 联合索引不是字段的简单拼接 ​

联合索引 (a, b, c) 设计时，顺序极其重要。它影响：

  • 哪些查询能命中索引
  • 是否能利用索引排序
  • 是否能形成覆盖索引

13.2 设计顺序的一般原则 ​

常见排序思路：

  1. 等值查询字段优先
  2. 区分度高的字段适当前置
  3. 排序字段尽量放到可以继续利用索引的位置
  4. 尽量让常用查询形成覆盖索引

例如业务查询：

sql
SELECT id, user_id, status, create_time
FROM orders
WHERE user_id = ?
  AND status = ?
ORDER BY create_time DESC;

可优先考虑：

sql
(user_id, status, create_time)

13.3 范围条件对索引使用的影响 ​

如果联合索引是 (a, b, c)：

  • a = ? AND b = ? AND c = ?：通常可充分利用
  • a = ? AND b > ? AND c = ?：c 往往无法继续用于有序检索
  • 一旦遇到范围条件，后续列的利用能力通常会下降

13.4 覆盖索引优先于回表 ​

很多高频查询并不需要整行字段。

例如：

sql
SELECT id, status
FROM orders
WHERE user_id = ?
AND create_time >= ?;

如果索引能覆盖查询所需字段，通常能显著减少回表成本。

13.5 不要重复建索引 ​

例如已经有：

sql
KEY idx_user_status_time (user_id, status, create_time)

那就要重新评估是否还需要：

sql
KEY idx_user (user_id)

因为联合索引可能已经覆盖前缀能力。索引不是越多越好，重复索引只会增加写入成本和维护成本。

13.6 一个实用设计流程 ​

  1. 列出系统 Top N 高频 SQL
  2. 按 WHERE、JOIN、ORDER BY、SELECT 字段拆解
  3. 找出可合并的索引需求
  4. 用 EXPLAIN 验证
  5. 关注慢查询日志和线上写入开销，持续调整

14. 锁死锁与事务排查 ​

14.1 什么情况下会发生死锁 ​

死锁本质：两个或多个事务互相等待对方持有的资源，形成环路。

典型场景：

  1. 事务 A 先锁记录 1，再锁记录 2
  2. 事务 B 先锁记录 2，再锁记录 1
  3. 双方都在等待，最终触发死锁检测

14.2 常见诱因 ​

  • 更新多行时访问顺序不一致
  • 没走索引，行锁扩大为大量记录扫描锁定
  • 大事务持锁时间过长
  • SELECT ... FOR UPDATE 使用不当

14.3 排查思路 ​

先看现象 ​

  • 接口超时
  • 数据库锁等待变长
  • 事务执行时间异常增加

再看数据库信息 ​

  • SHOW ENGINE INNODB STATUS;
  • SHOW PROCESSLIST;
  • 慢查询日志
  • 事务执行 SQL 与索引命中情况

最后定位根因 ​

重点看：

  1. 哪两条 SQL 互相等待
  2. 锁住了哪张表、哪个索引、哪些记录
  3. 是否因为缺失索引导致扫描范围过大

14.4 降低死锁概率的原则 ​

  1. 多条记录更新时保持固定顺序
  2. 尽量通过索引精确命中记录
  3. 缩短事务时间，避免事务里做远程调用
  4. 批量操作拆小批次
  5. 对热点资源做好重试机制

14.5 长事务的危害 ​

  • 长时间持锁，影响并发
  • Undo Log 无法及时清理
  • 主从复制延迟加剧
  • 回滚成本极高

经验：

  • 事务中只做必要的数据库操作
  • 不要把 RPC、发消息、复杂计算都塞进事务里

15. 主从复制与高可用基础 ​

15.1 为什么需要主从复制 ​

常见目标：

  • 读写分离
  • 数据备份
  • 故障切换
  • 扩展读取能力

15.2 主从复制的基本流程 ​

  1. 主库写入 Binlog
  2. 从库 I/O 线程拉取主库 Binlog
  3. 写入从库 Relay Log
  4. 从库 SQL 线程 / Worker 线程回放日志
  5. 从库完成数据同步

15.3 主从延迟的常见原因 ​

  • 主库写入压力太大
  • 从库执行能力不足
  • 大事务导致回放缓慢
  • 从库上存在慢 SQL、锁冲突

15.4 读写分离的注意事项 ​

核心问题：主从延迟导致读到旧数据

常见解决思路：

  • 写后读走主库
  • 对一致性要求高的接口强制读主
  • 业务接受最终一致时再读从

15.5 Binlog 的三种格式 ​

格式特点
STATEMENT记录 SQL 语句，体积小，但某些场景可能不够安全
ROW记录行变化，最稳，复制更准确
MIXED两者混合

工程上通常更偏向 ROW，因为一致性更稳。

15.6 GTID 简介 ​

GTID（Global Transaction Identifier）可以理解为给每个事务分配一个全局唯一 ID。

作用：

  • 简化主从切换
  • 让复制位点管理更清晰
  • 更方便故障恢复和拓扑调整

16. Java 开发中的 MySQL 常见问题 ​

16.1 Spring 事务常见误区 ​

1. 方法不是 public ​

  • 默认基于代理的事务增强可能失效

2. 同类方法内部自调用 ​

  • this.xxx() 不经过代理，事务可能不生效

3. 异常被吞掉 ​

  • 事务感知不到异常，导致没有按预期回滚

4. 回滚规则理解错误 ​

  • 默认对运行时异常回滚
  • 受检异常是否回滚要看配置

16.2 事务传播行为要理解到什么程度 ​

Java 开发至少要理解：

  • REQUIRED：默认加入当前事务
  • REQUIRES_NEW：挂起当前事务，开启新事务
  • SUPPORTS：有事务就加入，没有就非事务执行

最常见坑：

  • 外层大事务套内层 REQUIRES_NEW，结果和预期不一致
  • 事务边界设计不清，导致锁持有时间过长

16.3 MyBatis / JPA 常见 SQL 坑 ​

  • SELECT * 滥用
  • 动态 SQL 生成了没有索引支撑的条件
  • 批量更新没有分批
  • N+1 查询问题
  • 把本可一次 JOIN/批量查询的逻辑拆成循环查库

16.4 连接池相关问题 ​

常见连接池如 HikariCP、Druid 需要关注：

  • 最大连接数不能乱配
  • 连接获取超时要和业务超时协调
  • 长时间空闲连接要有回收机制
  • 必须配置连接有效性检测

16.5 如何写出更适合 MySQL 的业务代码 ​

  1. 先设计好索引，再写核心 SQL
  2. 事务尽量短小
  3. 不在循环里反复查数据库
  4. 避免把数据库当缓存使用
  5. 对热点更新、库存扣减、幂等处理要有专门方案

16.6 一个 Java 开发者的 MySQL 成长路径 ​

初级 ​

  • 会写增删改查
  • 理解索引、事务、隔离级别的基本概念
  • 能看懂简单 EXPLAIN

中级 ​

  • 能设计联合索引
  • 能优化慢 SQL
  • 能分析锁等待、回表、覆盖索引
  • 能处理分页、批量操作、读写分离等常见问题

高级 ​

  • 能从架构层面设计分库分表、复制、高可用方案
  • 能结合 Java 事务模型和数据库机制做系统设计
  • 能处理线上性能瓶颈、主从延迟、死锁、热点数据问题

17. EXPLAIN 深度解读 ​

17.1 为什么只看 type 不够 ​

很多人看 EXPLAIN 只盯着 type，这是不够的。真正要结合这些字段一起看：

  • id
  • select_type
  • table
  • type
  • possible_keys
  • key
  • key_len
  • rows
  • filtered
  • Extra

17.2 id 和执行顺序 ​

一般规律：

  • id 越大，越先执行
  • id 相同，按从上到下顺序执行
  • 如果是子查询或派生表，要结合 select_type 一起判断

17.3 select_type 常见值 ​

值含义
SIMPLE简单查询，不包含子查询或 UNION
PRIMARY最外层查询
SUBQUERY子查询
DERIVED派生表（通常来自 FROM 子查询）
UNIONUNION 后面的查询
UNION RESULTUNION 结果集

17.4 key_len 能看出什么 ​

key_len 表示 MySQL 实际使用的索引长度。

它可以帮助你判断：

  • 联合索引到底用了几列
  • 是否因为范围查询、类型不匹配等原因没用满索引
  • 是否可能存在隐式转换

经验：

  • 如果你预期用了 (a, b, c) 三列，但 key_len 明显偏短，就要怀疑后续列没用上

17.5 rows 和 filtered ​

字段含义
rows预估要扫描的行数
filtered经过条件过滤后预计保留的比例

判断思路：

  • rows 很大，通常意味着扫描范围大
  • filtered 很低，说明扫描了很多无效数据

17.6 Extra 里值得重点关注的值 ​

除了前面提到的 Using index、Using filesort、Using temporary，还常见：

  • Using join buffer：通常说明 JOIN 没很好利用索引
  • Impossible WHERE：条件恒不成立
  • Backward index scan：MySQL 8.0 中可能用于倒序扫描

17.7 一套实用的 EXPLAIN 分析顺序 ​

  1. 先看是否全表扫描：type=ALL
  2. 再看用了哪个索引：key
  3. 看联合索引用了几列：key_len
  4. 看扫描量大不大：rows
  5. 看有没有额外排序和临时表：Extra
  6. 最后结合 SQL 语义判断是否还有优化空间

18. 慢查询日志与性能排查流程 ​

18.1 慢查询日志是什么 ​

慢查询日志记录执行时间超过阈值的 SQL，是性能排查的第一入口。

常见价值：

  • 找出最耗时 SQL
  • 找出扫描行数过多的 SQL
  • 找出频率高但单次不一定很慢的热点 SQL

18.2 排查慢 SQL 的标准流程 ​

  1. 先确认是不是数据库慢
  2. 找到具体慢 SQL
  3. 查看执行计划 EXPLAIN
  4. 检查索引设计
  5. 检查数据量、统计信息、SQL 写法
  6. 回归验证

18.3 判断是不是数据库问题 ​

不要一上来就怪数据库。先排除：

  • 应用线程池阻塞
  • 连接池拿连接超时
  • 下游 RPC 太慢
  • 网络抖动

18.4 慢 SQL 常见根因 ​

  • 没有索引
  • 索引建了但没用上
  • 联合索引顺序不合理
  • SELECT * 导致回表成本大
  • 深分页
  • 排序/分组无法利用索引
  • 大事务、锁等待

18.5 一个排查模板 ​

看到一条慢 SQL 时，至少回答这几个问题：

  1. 它慢在扫描，还是慢在锁等待？
  2. 它是偶发慢，还是持续慢？
  3. 数据量变化前后是否有明显差异？
  4. 执行计划最近是否发生变化？
  5. 是否和上线、DDL、索引变更有关？

19. 字符集排序规则与存储细节 ​

19.1 为什么字符集问题很容易被忽略 ​

因为很多系统在开发初期看起来都"能用"，但到了跨系统对接、模糊查询、排序、表情符号、国际化场景时就容易出问题。

19.2 推荐字符集 ​

推荐统一使用 utf8mb4

原因：

  • 能完整支持 Unicode
  • 能支持 Emoji
  • 避免老 utf8 实际只支持最多 3 字节的问题

19.3 排序规则（Collation）是什么 ​

排序规则决定：

  • 字符串比较方式
  • 大小写是否敏感
  • 排序规则
  • 索引比较规则

例如常见差异：

  • 大小写敏感 vs 不敏感
  • 重音符号是否区分

19.4 工程上的建议 ​

  1. 库、表、字段字符集尽量统一
  2. 避免应用层和数据库层字符集不一致
  3. 新项目优先统一为 utf8mb4
  4. 涉及唯一索引时，要确认排序规则是否会影响唯一性判断

19.5 文本字段的注意点 ​

  • 长文本不适合频繁参与排序、分组、范围过滤
  • 大文本字段不应该轻易出现在联合索引中
  • TEXT/BLOB 类字段要谨慎放进热点表

20. 线上 DDL变更与大表治理 ​

20.1 为什么 DDL 很危险 ​

ALTER TABLE 看起来只是改个字段，但在生产环境里可能带来：

  • 表锁或元数据锁
  • 长时间阻塞业务 SQL
  • 主从延迟
  • 磁盘空间暴涨

20.2 元数据锁（MDL） ​

MySQL 对表结构操作会涉及 MDL。

典型问题：

  1. 某个长事务一直持有表的 MDL 读锁
  2. 你执行 ALTER TABLE
  3. ALTER 等待 MDL 写锁
  4. 后续新的查询也被堵住
  5. 最终看起来像"数据库突然卡死"

20.3 线上 DDL 的基本原则 ​

  1. 避开业务高峰期
  2. 先评估表大小、索引数量、磁盘空间
  3. 关注是否会阻塞主业务 SQL
  4. 优先使用支持 Online DDL 的方式
  5. 必要时使用成熟工具，如 pt-online-schema-change、gh-ost

20.4 大表治理思路 ​

大表本身不是原罪，问题在于：

  • 热点访问集中
  • 索引过多
  • 冷热数据混在一起
  • 历史数据无限堆积

常见治理手段：

  • 归档历史数据
  • 垂直拆分冷热字段
  • 水平拆分热点表
  • 减少冗余索引

20.5 删除大批量数据的正确姿势 ​

不推荐：

sql
DELETE FROM orders WHERE create_time < '2023-01-01';

更推荐分批：

sql
DELETE FROM orders
WHERE id >= ? AND id < ?
LIMIT 1000;

核心原因：

  • 缩短事务时间
  • 减少锁冲突
  • 降低主从延迟
  • 方便失败重试

21. 备份恢复与数据安全 ​

21.1 为什么备份不是“有就行” ​

很多团队有备份，但没有真正做过恢复演练。没有恢复验证的备份，不算真正可靠。

21.2 常见备份类型 ​

类型特点
全量备份完整备份整个数据集，恢复简单
增量备份只备份变化数据，节省空间
逻辑备份如 mysqldump，可读性强，恢复较慢
物理备份如 XtraBackup，恢复通常更快

21.3 恢复能力比备份文件更重要 ​

你要明确自己需要哪种能力：

  • 误删表能否恢复
  • 误删部分数据能否恢复
  • 能否恢复到某个时间点
  • 故障恢复需要多久

21.4 PITR（时间点恢复） ​

时间点恢复通常依赖：

  1. 全量备份
  2. 备份之后的 Binlog
  3. 按时间点回放 Binlog

21.5 数据安全最重要的几条原则 ​

  1. 生产库必须限制高危权限
  2. DELETE / UPDATE 必须带条件
  3. 重要操作先 SELECT 再执行
  4. 定期恢复演练
  5. 备份文件要有异地或多副本

22. 分库分表与分区表基础 ​

22.1 什么时候要分库分表 ​

不是数据量一大就必须分库分表，而是当单机数据库在这些方面遇到瓶颈时：

  • 存储容量
  • 单表性能
  • 写入吞吐
  • 热点争抢
  • 运维窗口过长

22.2 分库分表带来的代价 ​

好处：

  • 扩展容量
  • 分摊压力

代价：

  • 跨库 JOIN 复杂
  • 跨分片排序、分页、聚合困难
  • 全局唯一 ID 问题
  • 运维复杂度上升

22.3 常见拆分方式 ​

垂直拆分 ​

  • 按业务域拆库
  • 按字段冷热拆表

水平拆分 ​

  • 按用户 ID、订单 ID、时间等维度分片

22.4 分区表和分表不是一回事 ​

分区表：

  • 还是一张逻辑表
  • MySQL 内部按规则拆成多个分区

分库分表：

  • 通常需要应用层、中间件或基础设施配合
  • 属于系统架构层面的拆分

22.5 分片键选择原则 ​

好的分片键应该尽量满足：

  • 分布均匀
  • 访问路径稳定
  • 常用查询能带上分片键
  • 不容易形成热点

23. MySQL 常见线上故障场景 ​

23.1 CPU 飙高 ​

常见原因：

  • 大量全表扫描
  • 排序/分组过多
  • 热点 SQL 激增
  • 统计信息失真导致错误执行计划

排查方向：

  • 慢 SQL
  • EXPLAIN
  • 是否新增了大查询
  • 是否有索引失效

23.2 连接数打满 ​

常见原因：

  • 应用连接泄漏
  • 慢 SQL 太多，连接长时间不释放
  • 突发流量
  • 连接池配置不合理

处理思路：

  1. 先看应用连接池
  2. 再看 MySQL 当前活跃连接
  3. 排查阻塞 SQL 和慢 SQL
  4. 必要时限流降级

23.3 突然大量锁等待 ​

常见原因：

  • 大事务
  • 热点更新
  • DDL 阻塞
  • 批量更新/删除

23.4 主从延迟突然变大 ​

优先排查：

  • 是否有大事务
  • 是否刚做了 DDL
  • 从库是否有慢 SQL
  • 从库机器资源是否不足

23.5 执行计划突变 ​

常见诱因：

  • 数据分布变化
  • 统计信息变化
  • 索引新增/删除
  • MySQL 版本差异

应对思路：

  • 对比变更前后执行计划
  • 检查统计信息
  • 检查 SQL 参数分布
  • 必要时临时干预执行计划

23.6 一套通用故障处理方法 ​

  1. 先止血，保护主流程
  2. 先确认范围，是单 SQL、单表、单库还是整站问题
  3. 优先保留现场信息
  4. 不要在没有把握时直接做高风险操作
  5. 事后沉淀复盘：现象、原因、处理、预防

24. MySQL 8.0 重要特性 ​

24.1 为什么 Java 开发要关心 8.0 ​

因为 MySQL 8.0 不只是“版本升级”，它在这些方面都有明显变化：

  • 数据字典
  • 默认字符集
  • SQL 能力增强
  • 执行计划优化
  • 索引与统计信息能力

24.2 默认字符集变化 ​

MySQL 8.0 默认字符集更偏向 utf8mb4，这对国际化、Emoji、跨系统兼容更友好。

24.3 窗口函数 ​

窗口函数适合：

  • 排名
  • 分组内排序
  • 环比、同比
  • TopN 问题

例如：

sql
SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;

优点：

  • 很多以前依赖子查询、自连接的写法可以大幅简化

24.4 CTE（公用表表达式） ​

sql
WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE create_time >= '2026-01-01'
)
SELECT *
FROM recent_orders
WHERE status = 1;

适合：

  • 提高复杂 SQL 可读性
  • 分阶段组织查询逻辑

24.5 函数索引 ​

在一些场景下，查询条件里会对字段做函数处理。MySQL 8.0 对表达式索引支持更友好。

价值：

  • 避免因为函数处理导致索引失效

但原则不变：

  • 能直接改写 SQL 避免函数，通常还是优先改 SQL

24.6 降序索引与执行计划改进 ​

MySQL 8.0 在排序、倒序扫描、优化器能力方面更成熟。

意味着：

  • 某些 ORDER BY ... DESC 场景可以更好利用索引
  • 但最终仍要以 EXPLAIN 结果为准

24.7 隐藏索引（Invisible Index） ​

隐藏索引可以先让某个索引对优化器“不可见”，但不真正删除。

价值：

  • 评估删除索引的影响
  • 做索引治理时更安全

24.8 Java 项目升级到 8.0 要注意什么 ​

  1. 驱动版本要匹配
  2. 字符集、时区配置要核对
  3. 部分 SQL 执行计划可能变化
  4. 保留回归测试，尤其是核心查询和报表 SQL

25. 分布式事务与一致性设计 ​

25.1 为什么单机事务不够了 ​

在单体应用里，一个数据库本地事务通常够用。但到了微服务或多系统协作场景，就会出现：

  • 一个业务跨多个库
  • 一个业务既写数据库又发消息
  • 一个业务要调用多个远程服务

这时就不能只靠单机 MySQL 事务。

25.2 一致性问题的典型场景 ​

例如下单：

  1. 写订单表
  2. 扣库存
  3. 发优惠券消息
  4. 写支付流水

只要其中一步失败，就会出现数据不一致问题。

25.3 常见方案 ​

方案 1：强一致分布式事务 ​

典型代表：

  • 2PC
  • XA

优点：

  • 理论上更强一致

缺点：

  • 性能差
  • 实现复杂
  • 对系统可用性影响大

方案 2：最终一致性 ​

更符合互联网高并发系统。

常见做法：

  • 本地消息表
  • 事务消息
  • 补偿机制
  • 重试机制

25.4 本地消息表模式 ​

核心思路：

  1. 在本地事务里同时写业务数据和消息表
  2. 提交成功后，由后台任务投递消息
  3. 消费端做好幂等

优点：

  • 实现相对简单
  • 和 MySQL 本地事务结合自然

25.5 幂等是最终一致性的基础 ​

为什么必须幂等：

  • 消息可能重复投递
  • 接口可能超时重试
  • 消费者可能重复消费

常见幂等手段：

  • 唯一业务单号
  • 去重表
  • 状态机控制
  • 唯一索引约束

25.6 补偿思想 ​

最终一致性不是“不一致也没关系”，而是：

  • 允许短时间不一致
  • 必须设计恢复手段

补偿方式常见有：

  • 定时扫描补单
  • 死信队列重试
  • 人工介入修复

25.7 Java 开发应该掌握到什么程度 ​

至少要理解：

  1. 什么场景该用本地事务，什么场景不够
  2. 什么是最终一致性
  3. 为什么消息系统一定要配幂等
  4. 为什么不能迷信“分布式事务框架一键解决”

26. 典型排障案例 ​

26.1 案例一：慢 SQL ​

现象 ​

  • 某接口 RT 从 50ms 飙到 3s
  • 应用日志显示数据库查询耗时显著增加

排查 ​

  1. 查慢查询日志，发现某条订单查询异常慢
  2. EXPLAIN 发现 type=ALL
  3. SQL 中按 status 和 create_time 查询，但缺少合适联合索引

处理 ​

  • 新增 (status, create_time) 联合索引
  • 回归验证执行计划

复盘 ​

  • 原因不是数据库“突然变差”，而是数据量增长后原本凑合能跑的 SQL 撑不住了

26.2 案例二：死锁 ​

现象 ​

  • 订单取消和库存回滚接口偶发失败
  • 日志出现死锁异常

排查 ​

  1. 查看 SHOW ENGINE INNODB STATUS
  2. 发现两个事务更新资源顺序不一致
  3. 一个先改订单表再改库存表，另一个反过来

处理 ​

  • 统一资源访问顺序
  • 对热点操作增加有限重试

26.3 案例三：主从延迟 ​

现象 ​

  • 用户刚修改昵称，刷新后仍看到旧值
  • 读写分离架构下，从库延迟明显

排查 ​

  1. 检查复制延迟指标
  2. 发现主库刚执行大批量更新
  3. 从库回放压力增大

处理 ​

  • 写后读临时切主库
  • 批量操作改成分批
  • 优化从库资源和回放能力

26.4 案例四：连接数打满 ​

现象 ​

  • 服务大量报数据库连接获取超时
  • MySQL 活跃连接暴涨

排查 ​

  1. 看应用连接池监控
  2. 发现某批接口慢 SQL 激增
  3. 连接长时间不释放，连接池被耗尽

处理 ​

  • 先限流
  • 再处理慢 SQL
  • 调整连接池阈值和超时参数

26.5 案例五：线上 DDL 阻塞 ​

现象 ​

  • 执行 ALTER TABLE 后，大量查询卡住

排查 ​

  1. 检查发现前面有一个长事务未提交
  2. 该事务持有 MDL 读锁
  3. DDL 等待写锁，后续 SQL 全被堵住

处理 ​

  • 终止异常长事务
  • 重新选择业务低峰执行 DDL
  • 建立 DDL 前检查流程

27. 面试高频问题速查 ​

27.1 初级高频 ​

1. 为什么索引能加速查询 ​

  • 因为索引本质是有序数据结构，能减少扫描范围

2. 为什么 InnoDB 用 B+ 树 ​

  • 磁盘 I/O 友好
  • 高度低
  • 范围查询强

3. 聚簇索引和二级索引有什么区别 ​

  • 聚簇索引叶子节点存整行数据
  • 二级索引叶子节点存索引列和主键值

4. 什么是回表 ​

  • 先查二级索引，再根据主键回到聚簇索引查整行

5. 什么是覆盖索引 ​

  • 查询所需字段都在索引里，直接返回，不用回表

27.2 中级高频 ​

1. 联合索引为什么要遵循最左前缀 ​

  • 因为联合索引在 B+ 树中的排序是按列顺序构造的

2. 索引什么时候会失效 ​

  • 函数处理
  • 隐式转换
  • 违背最左前缀
  • 选择性太差
  • 优化器判断全表扫更划算

3. EXPLAIN 重点看哪些字段 ​

  • type
  • key
  • key_len
  • rows
  • Extra

4. MVCC 是什么 ​

  • 基于多版本数据和可见性规则实现非阻塞读

5. 为什么会发生死锁 ​

  • 多个事务以不同顺序持有并等待彼此资源

27.3 高级高频 ​

1. Redo Log 和 Binlog 的区别 ​

  • Redo Log：InnoDB 层，物理日志，保证持久性
  • Binlog：Server 层，逻辑日志，用于复制和恢复

2. 两阶段提交为什么有必要 ​

  • 保证 Redo Log 和 Binlog 在异常情况下的一致性

3. 如何设计一个高并发订单系统的 MySQL 层 ​

  • 合理分库分表
  • 热点隔离
  • 索引设计
  • 读写分离
  • 幂等与最终一致性

4. 为什么很多互联网项目不用外键 ​

  • 不是不能用，而是更倾向应用层治理一致性，以换取扩展性和灵活性

5. 主从延迟怎么处理 ​

  • 写后读主库
  • 控制大事务
  • 提升从库能力
  • 优化复制链路

27.4 一句话复习法 ​

  • 索引解决的是“怎么更快找到数据”
  • 事务解决的是“并发下如何保证正确性”
  • 日志解决的是“异常下如何保证不丢数据”
  • 复制解决的是“如何扩展读和做容灾”
  • 分库分表解决的是“单机数据库的容量与吞吐极限”

28. 学习路线与阅读顺序 ​

28.1 初级 Java 开发者阅读顺序 ​

建议顺序：

  1. 第 1 章 核心基础概念
  2. 第 2 章 底层数据结构
  3. 第 3 章 索引分类与物理存储
  4. 第 10 章 事务机制
  5. 第 11 章 SQL 实战高频场景
  6. 第 12 章 表设计与字段设计规范
  7. 第 16 章 Java 开发中的 MySQL 常见问题
  8. 第 27 章 面试高频问题速查

目标：

  • 能独立写常见 SQL
  • 理解索引、事务、回表、覆盖索引
  • 避免最常见的开发坑

28.2 中级 Java 开发者阅读顺序 ​

建议顺序：

  1. 第 4 章 核心机制与高级特性
  2. 第 5 章 索引失效场景
  3. 第 6 章 性能分析与调优实战
  4. 第 13 章 联合索引设计方法论
  5. 第 14 章 锁死锁与事务排查
  6. 第 17 章 EXPLAIN 深度解读
  7. 第 18 章 慢查询日志与性能排查流程
  8. 第 26 章 典型排障案例

目标：

  • 能独立优化慢 SQL
  • 能看懂执行计划
  • 能分析锁等待、回表、索引失效

28.3 高级 Java 开发者阅读顺序 ​

建议顺序：

  1. 第 8 章 日志系统
  2. 第 9 章 MySQL 三层全局架构
  3. 第 15 章 主从复制与高可用基础
  4. 第 20 章 线上 DDL变更与大表治理
  5. 第 21 章 备份恢复与数据安全
  6. 第 22 章 分库分表与分区表基础
  7. 第 23 章 MySQL 常见线上故障场景
  8. 第 24 章 MySQL 8.0 重要特性
  9. 第 25 章 分布式事务与一致性设计

目标：

  • 能参与数据库架构设计
  • 能处理线上故障与容量扩展
  • 能把 MySQL 机制和系统设计连起来

28.4 一条推荐的总路线 ​

先建立概念框架，再补 SQL 实战，然后学执行计划和索引设计，最后进入线上治理、架构扩展和一致性设计。

29. 章节检查清单 ​

29.1 索引检查清单 ​

做索引设计前，至少问自己：

  1. 这条 SQL 的高频过滤条件是什么
  2. 有没有排序、分组、分页需求
  3. 能否形成联合索引
  4. 能否形成覆盖索引
  5. 会不会和现有索引重复
  6. 写入成本是否能接受

29.2 SQL 优化检查清单 ​

优化一条慢 SQL 时，至少检查：

  1. 有没有 SELECT *
  2. 是否命中了正确索引
  3. 是否存在函数、隐式转换、OR 等问题
  4. 是否有深分页
  5. 是否触发 Using filesort 或 Using temporary
  6. 是慢在扫描还是慢在锁等待

29.3 事务检查清单 ​

设计事务时，至少确认：

  1. 事务边界是否足够小
  2. 事务里是否混入 RPC、发消息、复杂计算
  3. 是否可能形成长事务
  4. 是否有死锁风险
  5. 是否需要幂等和重试

29.4 建表检查清单 ​

新建表前，至少确认：

  1. 主键是否合理
  2. 字段类型是否合适
  3. 是否设置了 NOT NULL 和默认值
  4. 是否提前设计了核心索引
  5. 是否考虑未来数据量增长

29.5 线上变更检查清单 ​

做 DDL 或大批量数据操作前，至少确认：

  1. 是否在业务低峰
  2. 是否评估过表大小与影响范围
  3. 是否会造成 MDL 阻塞
  4. 是否会导致主从延迟
  5. 是否有回滚和兜底方案

30. 术语速查表 ​

术语一句话解释
聚簇索引叶子节点存整行数据的索引组织方式
二级索引叶子节点存索引列和主键值的索引
回表先查二级索引，再回主键索引查整行
覆盖索引查询字段都在索引里，不用回表
最左前缀联合索引从最左列开始才能更好利用
ICP索引下推，尽量在索引层过滤数据
MVCC多版本并发控制，核心目标是非阻塞读
Read View决定当前事务能看到哪些版本的可见性快照
Undo Log用于回滚和 MVCC 的历史版本记录
Redo Log用于崩溃恢复的物理日志
Binlog用于复制和恢复的逻辑日志
WAL先写日志再刷数据页的机制
Gap Lock锁定索引间隙，防止幻读
Next-Key Lock记录锁 + 间隙锁
AHIInnoDB 的自适应哈希索引机制
MDL元数据锁，涉及表结构操作时很关键
GTID全局事务 ID，方便复制和切换
PITR时间点恢复，恢复到某个具体时刻
CTE公用表表达式，提升复杂 SQL 可读性
幂等同一个请求执行多次，结果保持一致

30.1 最容易混淆的几组概念 ​

COUNT(*) vs COUNT(column) ​

  • COUNT(*) 统计总行数
  • COUNT(column) 只统计该列非 NULL 数量

DATETIME vs TIMESTAMP ​

  • DATETIME 更偏业务时间表达
  • TIMESTAMP 更偏系统时间与时区处理

分区表 vs 分库分表 ​

  • 分区表还是一张逻辑表
  • 分库分表是系统层面的拆分

31. 故障排查速查表 ​

31.1 慢 SQL ​

优先看：

  1. 慢查询日志
  2. EXPLAIN
  3. 索引是否命中
  4. 是否深分页
  5. 是否排序/分组引发额外开销

31.2 锁等待 ​

优先看：

  1. 是否有大事务
  2. 是否有热点更新
  3. 是否存在缺失索引
  4. SHOW ENGINE INNODB STATUS
  5. 当前阻塞链路

31.3 连接数打满 ​

优先看：

  1. 连接池配置
  2. 活跃 SQL 是否变慢
  3. 是否有连接泄漏
  4. 是否有阻塞 SQL

31.4 主从延迟 ​

优先看：

  1. 是否有大事务
  2. 是否有 DDL
  3. 从库资源是否足够
  4. 从库是否有慢 SQL

31.5 DDL 阻塞 ​

优先看：

  1. 是否有长事务
  2. 是否被 MDL 卡住
  3. 是否在高峰期执行
  4. 是否可改用在线变更工具

31.6 数据误删 ​

优先做：

  1. 先止损，避免继续写入扩大影响
  2. 确认影响范围
  3. 判断是否能通过备份 + Binlog 做 PITR
  4. 保留现场，谨慎操作

31.7 一个统一排查框架 ​

所有故障都可以先按这 5 步走：

  1. 先看现象
  2. 再定范围
  3. 保留现场
  4. 先止血再根因分析
  5. 复盘并沉淀为 checklist

📌 总结 ​

MySQL 学习的核心路径：

  1. 索引原理 → B+ 树结构、聚簇/非聚簇索引、回表与覆盖索引
  2. 执行分析 → EXPLAIN、最左前缀、索引下推、索引失效场景
  3. 事务机制 → ACID、隔离级别、MVCC、锁机制
  4. 日志系统 → Undo Log、Redo Log、Binlog、两阶段提交
  5. 架构认知 → 三层架构、连接层、服务层、存储引擎层
  6. 工程实战 → SQL 写法、分页、JOIN、批量操作、建表规范
  7. 性能优化 → 联合索引设计、覆盖索引、慢 SQL 分析、锁与死锁排查
  8. 架构进阶 → 主从复制、读写分离、高可用、GTID
  9. Java 结合 → Spring 事务、ORM 常见问题、连接池配置、业务代码规范
  10. 排查工具 → EXPLAIN 全字段、慢查询日志、性能定位方法
  11. 生产治理 → 字符集、线上 DDL、大表治理、备份恢复
  12. 架构扩展 → 分库分表、分区表、常见线上故障处理
  13. 版本进阶 → MySQL 8.0 新能力、执行计划变化、函数索引、窗口函数
  14. 一致性设计 → 分布式事务、消息最终一致性、幂等、补偿
  15. 案例与面试 → 典型故障案例、面试高频题、速查复习
  16. 知识管理 → 学习路线、检查清单、术语表、故障速查表

持续更新中...