MySQL 原理大纲

这份你要建立的不是“记几个术语”，而是一整条 MySQL 原理链：

存储结构 -> 索引 -> 查询执行 -> 事务 -> 并发控制 -> 日志 -> 为什么适合业务主库

一、总纲

MySQL 原理层最核心的 6 条主线：

  1. 存储引擎和 InnoDB
  2. B+ 树索引
  3. 查询与执行计划
  4. 事务与 ACID
  5. MVCC 与锁
  6. redo / undo / binlog

二、为什么大家面试默认讲 InnoDB

因为 InnoDB 是 MySQL 中最常用的事务型存储引擎，支持：

  • 事务
  • 行锁
  • MVCC
  • 崩溃恢复

所以大多数订单、支付、订阅、交易场景默认都是建立在 InnoDB 语义上的。

三、B+ 树原理链

1. 为什么数据库索引常用 B+ 树

因为数据库主要面对磁盘 IO，而不是纯内存结构。

B+ 树适合的原因：

  • 多叉树，高度低
  • 非叶子节点只存索引，单页容纳更多 key
  • 叶子节点有序，范围查询好
  • 比哈希更适合排序和范围

2. 为什么不是二叉树

因为树太高，磁盘 IO 太多。

3. 为什么不是哈希

哈希适合等值查找，不适合范围查询和排序。

四、聚簇索引和二级索引

1. 聚簇索引

InnoDB 的主键索引是聚簇索引，叶子节点直接存整行数据。

2. 二级索引

二级索引叶子节点存的是索引列值 + 主键值。

3. 为什么会回表

因为通过二级索引找到主键后，还要回到聚簇索引拿整行数据。

4. 覆盖索引为什么快

因为不需要回表。

五、最左前缀原则

联合索引从最左列开始匹配，只有命中最左连续前缀时，才能更高效地利用索引。

这块不是为了死记规则，而是要理解：

联合索引本质上有自己的有序组织方式，查询条件如果不符合这个有序前缀，利用效率就会差。

六、执行计划与慢 SQL

1. explain 在看什么

核心不是背字段名，而是看：

  • 是否走索引
  • 走了哪个索引
  • 扫描行数多不多
  • 有没有额外排序、临时表

你可以把它理解成一句话：

优化器打算用什么方式拿数据，预计要扫多少，过程中要不要额外排序、临时表和回表。

2. explain 里最值得看的字段

key 和 possible_keys

  • possible_keys：理论上可能用哪些索引
  • key：最终实际用了哪个索引

这两个字段常用来判断：

  • 优化器有没有命中你设计的索引
  • 是完全没索引，还是“有索引但没选中”

type

这是访问类型，常见从好到差可以粗略理解为：

  • const
  • eq_ref
  • ref
  • range
  • index
  • ALL

工程上最重要的不是死记顺序，而是知道：

  • ref / range 往往是可接受的常见状态
  • index 说明在扫整棵索引，不一定真正高效
  • ALL 说明全表扫描，通常要重点排查

rows

表示优化器预估要扫描的行数。 这往往比很多人想象的更重要，因为“走了索引”不代表扫描成本就低。

filtered

表示扫描出来的数据，经过 where 过滤后预计还会留下多少比例。 如果 rows 很大、filtered 很低，常常说明先扫了很多无效数据再过滤。

Extra

这是 explain 里最容易暴露问题的字段，尤其要关注：

  • Using filesort
  • Using temporary
  • Using index
  • Using where
  • Using index condition

其中：

  • Using filesort：排序没完全被索引承接
  • Using temporary：通常是 group by / distinct / 聚合中间结果要临时表
  • Using index：通常表示覆盖索引，不需要回表

3. explain 真实分析时的顺序

我一般会按这个顺序看：

  1. key 是否命中预期索引
  2. type 是 ref / range 还是已经掉到 ALL
  3. rows 有没有大到离谱
  4. Extra 有没有 filesort、temporary
  5. 最后再回到 SQL 本身看 where / order by / group by 的写法

也就是说，explain 不是“看见走索引就结束”，而是判断这条 SQL 的整体取数路径是否合理。

4. 常见异常信号怎么解读

key = NULL

常见含义：

  • 没有可用索引
  • 有索引但优化器判断不值得用
  • where 条件写法让索引失效

type = ALL

常见含义：

  • 全表扫描
  • 通常要排查索引缺失、联合索引不匹配、函数操作、类型转换

Using filesort

常见含义：

  • 排序不能直接走索引
  • order by 和 where / 索引设计没对齐

Using temporary

常见含义：

  • group by、distinct、复杂聚合要借助临时表
  • 数据量一大就容易慢

5. 慢 SQL 优化思路

  • 看索引是否命中
  • 看 where 条件设计
  • 看排序和分组
  • 看分页方式
  • 看是不是分析型查询压在主库

6. explain 之后怎么落优化

如果 explain 暴露问题，常见优化方向通常是：

  • 补索引或重做联合索引
  • 让过滤字段和排序字段尽量走同一联合索引
  • 避免 select *
  • 减少回表
  • 把大 offset 分页改成游标分页
  • 把分析型查询迁出 MySQL

7. 你在面试里怎么讲得像做过

我看 explain 时不会只盯 type，而是先看 key 有没有命中预期索引，再看 rows 和 Extra。如果 rows 很大，哪怕 type 不是 ALL，我也会继续怀疑是不是用了一个不够好的索引。很多 SQL 真正慢，不是完全没索引，而是索引设计和排序过滤路径不匹配。

七、事务原理链

1. ACID 是什么

  • Atomicity：原子性
  • Consistency：一致性
  • Isolation：隔离性
  • Durability：持久性

2. 原子性靠什么

undo log

修改失败时可以回滚。

3. 持久性靠什么

redo log

即使宕机，也能恢复已提交事务。

4. 隔离性靠什么

  • 锁
  • MVCC

5. 一致性怎么理解

一致性不是单个组件独立保证的，而是：

  • 事务机制
  • 约束
  • 业务规则

一起保证的结果。

八、undo log / redo log / binlog

1. undo log

记录旧值，服务于：

  • 回滚
  • MVCC 历史版本读取

2. redo log

记录新值变化，用于：

  • 崩溃恢复
  • 持久性

3. binlog

是 server 层逻辑日志，主要用于：

  • 主从复制
  • 数据恢复

4. 为什么要两阶段提交

因为 redo log 和 binlog 不在同一层。 如果不协调提交顺序，可能出现：

  • 存储层成功，复制层失败
  • 或复制层成功，存储层失败

九、MVCC 原理链

1. MVCC 在解决什么问题

解决并发读写时，读操作如果全靠加锁，会严重影响吞吐。

2. 核心思路

  • 每行记录有版本信息
  • 事务有 read view
  • 查询时判断版本是否可见
  • 不可见就顺着 undo log 找旧版本

3. 价值

让很多普通读不用加锁，也能读到一致快照。

十、锁原理链

常见锁

  • 行锁
  • 间隙锁
  • Next-Key Lock

为什么有间隙锁和 Next-Key Lock

为了处理幻读和范围更新场景。

工程上的理解

锁不是越多越安全。 真正重要的是：

  • 锁粒度
  • 锁范围
  • 事务长度
  • 是否会造成阻塞和死锁

十一、为什么 MySQL 适合你的核心业务

海外业务平台

  • 订阅
  • 支付
  • 退款
  • 权益状态

核心交易链路

  • 订单
  • 交易
  • 规则配置

因为这些都属于：

  • 强状态
  • 强事务
  • 可追溯要求高

所以 MySQL 天然适合作为主库。

十二、MySQL 的边界

它不适合长期承担：

  • 大规模分析型报表
  • 高吞吐原始事件接入
  • 全文检索

所以你项目里才会出现：

  • ClickHouse 做报表分析
  • MongoDB 做原始事件
  • ES 做搜索

这恰恰说明你不是“只会用 MySQL 干所有事”。

十三、你至少要讲顺的一条原理链

数据写入 -> InnoDB 页和索引结构 -> 事务修改 -> undo/redo 记录 -> 提交 -> binlog -> 并发读通过 MVCC 或锁控制

只要这条链能讲顺，MySQL 原理层就有底了。

十四、最适合你的结尾表达

我对 MySQL 的理解不是停留在表设计和 CRUD，而是理解它为什么适合承接强状态业务、事务如何工作、索引为什么这样设计，以及它在系统里应该放在哪一层、又不应该承担哪些职责。