MySQL 速查卡

🎯 覆盖 29 题 | ⭐ 高频 14 题 | 预计扫描 10 分钟 📌 先看⭐一句话答案 → 展开要点 → 自测清单检验

一、索引

知识地图：B+树(非叶只存key,叶子存数据+双向链表) → 聚簇(主键=数据) / 二级(叶子存主键→回表)

⭐ InnoDB 索引 = B+ 树

一句话： InnoDB 用 B+ 树作为索引结构，非叶子节点只存 key（路由），叶子节点存数据且用双向链表相连，三层可存约 2200 万条记录。

B+ 树 vs B 树： B 树非叶子也存数据 → 每页存的 key 少 → 树更高 → IO 多；B+ 叶子链表支持高效范围查询

关键数字： 页 16KB，bigint 8B + 指针 6B → 每页 ~1170 个 key → 三层 1170 × 1170 × 16 ≈ 2200 万行（只需 3 次 IO）

⭐ 聚簇索引 / 二级索引 / 回表

一句话： 聚簇索引叶子存完整行数据（主键索引=聚簇），二级索引叶子存主键值 → 查完整数据需回表（用主键去聚簇索引二次查找）。

二级索引查询 SELECT * WHERE name='Alice':
  name 索引找到 → 得到主键 id=100 → 用 id=100 去聚簇索引查完整行（回表）
  
覆盖索引避免回表：SELECT name,age WHERE name='Alice' + 联合索引(name,age) → 索引中已有全部查询列

⭐ 联合索引 + 最左前缀

一句话： 联合索引 (a,b,c) 按 a→b→c 排序，查询必须从最左列开始匹配；范围查询后面的列失效。

索引 (a,b,c):
  WHERE a=1 AND b=2 AND c=3  ✅ 全用
  WHERE a=1 AND c=3           ✅ 只用到 a（跳过了 b）
  WHERE b=2                   ❌ 跳过了 a
  WHERE a=1 AND b>2 AND c=3  ✅ a,b 用到，c 失效（b 是范围）

⚠️ 易错：MySQL 优化器会自动调整 WHERE 子句字段顺序，WHERE b=2 AND a=1 等效于 WHERE a=1 AND b=2

⭐ 索引失效 7 大场景

① 索引列用函数: WHERE YEAR(time)=2024 → 改为范围查询
② 隐式类型转换: WHERE varchar_col=100 → 加引号 '100'
③ LIKE '%xx': 后缀/中缀通配符 → 前缀 'xx%' 才走索引
④ 联合索引跳过最左列
⑤ OR 有一侧无索引 → 全表扫描
⑥ 索引列参与计算: WHERE age+1=19 → 改为 age=18
⑦ 数据量太小，优化器选全表扫描更快

二、事务与 MVCC

知识地图：ACID(原一隔持) → 隔离级别(RU/RC/RR/S) → MVCC(隐藏列+undo log版本链+ReadView)

⭐ ACID

一句话： A 原子性(undo log 回滚) + C 一致性(AID 保证) + I 隔离性(锁+MVCC) + D 持久性(redo log + WAL)。

⭐ 事务隔离级别

级别	脏读	不可重复读	幻读
READ UNCOMMITTED	有	有	有
READ COMMITTED	✅	有	有
REPEATABLE READ(默认)	✅	✅	⚠️基本解决
SERIALIZABLE	✅	✅	✅

⚠️ 易错：InnoDB 的 RR 通过 MVCC + Next-Key Lock 解决了大多数幻读，比标准 SQL 定义更强

⭐ MVCC 原理

一句话： MVCC 通过隐藏列(DB_TRX_ID/DB_ROLL_PTR) + undo log 版本链 + ReadView 实现无锁快照读；RC 每次读生成新 ReadView，RR 整个事务只生成一次。

ReadView 四字段： creator_trx_id / trx_ids(活跃事务集) / min_trx_id / max_trx_id

可见性判断：

版本的 trx_id:
  == creator → 自己改的，可见
  < min → 已提交，可见
  >= max → 后来的事务，不可见
  在[min,max)之间 → 在 trx_ids 中=未提交不可见；不在=已提交可见
  不可见 → 沿版本链找上一版本

RC vs RR 的区别就在于 ReadView 生成时机：

RC：每次 SELECT 生成新 ReadView → 能看到其他事务已提交的新数据
RR：第一次 SELECT 生成后复用 → 看到的始终是同一快照

三、锁与日志

⭐ MySQL 锁体系

一句话： InnoDB 行锁有三种——Record Lock(锁单行) + Gap Lock(锁间隙防插入) + Next-Key Lock(前开后闭,RR默认,防幻读)。

数据 id = 1, 5, 10, 15 时 Next-Key Lock 区间：
  (-∞,1] (1,5] (5,10] (10,15] (15,+∞)

当前读 vs 快照读： 普通 SELECT = 快照读(MVCC,无锁)；SELECT FOR UPDATE / INSERT / UPDATE / DELETE = 当前读(加锁)

⭐ redo log / undo log / binlog

日志	层级	作用	写入时机
redo log	InnoDB 引擎	崩溃恢复(持久性)	事务执行中
undo log	InnoDB 引擎	回滚(原子性) + MVCC 版本链	事务执行中
binlog	Server 层	主从复制 + 数据恢复	事务提交时

两阶段提交： redo log prepare → binlog fsync → redo log commit；崩溃恢复：prepare+有binlog→补commit；prepare+无binlog→回滚

四、性能优化

⭐ EXPLAIN 关键字段

type 性能排名： system > const > eq_ref > ref > range > index > ALL

Extra 关注： Using index(覆盖索引,好) / Using filesort(需优化) / Using temporary(需优化) / Using index condition(ICP)

⭐ SQL 优化思路

① 加合适索引(联合索引覆盖高频查询)
② 避免 SELECT *，按需查字段(利用覆盖索引)
③ 深分页优化：游标分页(WHERE id > last_id) 或延迟关联(先查主键再JOIN)
④ 避免索引失效(函数/隐式转换/LIKE%开头)
⑤ 小表驱动大表，被驱动表 JOIN 字段有索引
⑥ 批量操作代替逐行

五、分库分表

⭐ 分表策略

一句话： 单表超 500万-2000万行考虑分表；哈希分表（均匀但扩容难）vs 范围分表（扩容易但可能热点）vs 预分表（1024张表，扩容只迁物理机）。

Sharding Key 选择： 高频查询字段 + 高基数 + 不可变 + 相关数据同分片（减少跨分片 JOIN）

分布式事务： TCC(Try-Confirm-Cancel) / Seata AT(无侵入) / 本地消息表+MQ(最终一致)

六、MySQL 8.0

⭐ 窗口函数 vs GROUP BY

一句话： GROUP BY 多行变一行（丢失明细），窗口函数行数不变（聚合值附加在每行上）；窗口函数通常比等效自连接更高效。

常用：ROW_NUMBER() / RANK() / DENSE_RANK() / LAG() / LEAD() / SUM() OVER()

补充速览

关键词	核心答案
B+树增删	插入:节点满→分裂上推; 删除:节点不足→借兄弟/合并
覆盖索引	查询列都在索引中，不需要回表；EXPLAIN Extra=Using index
何时建索引	WHERE/JOIN/ORDER BY 高频列 + 高选择性；不建：数据少/更新频繁/低选择性
哈希索引	O(1)等值查询但不支持范围排序；InnoDB 的 AHI 是自动的
SQL 执行全过程	连接器→(查询缓存8.0已移除)→解析器→优化器→执行器→存储引擎→返回
千亿数据存储	热(MySQL分库分表)→温(TiDB)→冷(Hive/ClickHouse) + 读写分离 + 多级缓存
CTE	WITH RECURSIVE 递归查询，适合树形结构(组织架构)
JSON 支持	config->>'$.host' 提取; generated column + 索引

🧠 助记汇总

口诀	含义
原一隔持	ACID: 原子性/一致性/隔离性/持久性
脏不幻	三大并发问题：脏读/不可重复读/幻读
记隙临	三种行锁：Record Lock/Gap Lock/Next-Key Lock
redo持久,undo原子,bin复制	三种日志各自作用
const>eq_ref>ref>range>index>ALL	EXPLAIN type 性能排名
函转模跳或算少	索引失效七场景首字

✅ 自测清单

#	问题	你能说出...
1	B+树	核心特点 + 为什么选 B+ 不选 B + 2200 万计算
2	聚簇/二级/回表	各自定义 + 如何避免回表(覆盖索引)
3	联合索引	最左前缀 + 范围查询后列失效
4	索引失效	至少说出 5 种场景
5	ACID	各自实现机制
6	MVCC	三大组件 + ReadView 可见性判断 + RC vs RR 区别
7	隔离级别	四级 + 各解决什么问题
8	三种日志	redo/undo/binlog 各自作用 + 两阶段提交
9	MySQL 锁	三种行锁 + 当前读 vs 快照读
10	EXPLAIN	type 排名 + Extra 关键值
11	SQL 优化	六大思路
12	分库分表	策略 + Sharding Key 选择 + 分布式事务
13	窗口函数	vs GROUP BY 核心区别

💡 首次全部过一遍 → 第2天只过答不上来的 → 第4天再复习 → 面试前一天最后扫一遍

MySQL 速查卡 ​

一、索引 ​

⭐ InnoDB 索引 = B+ 树 ​

⭐ 聚簇索引 / 二级索引 / 回表 ​

⭐ 联合索引 + 最左前缀 ​

⭐ 索引失效 7 大场景 ​

二、事务与 MVCC ​

⭐ ACID ​

⭐ 事务隔离级别 ​

⭐ MVCC 原理 ​

三、锁与日志 ​

⭐ MySQL 锁体系 ​

⭐ redo log / undo log / binlog ​

四、性能优化 ​

⭐ EXPLAIN 关键字段 ​

⭐ SQL 优化思路 ​

五、分库分表 ​

⭐ 分表策略 ​

六、MySQL 8.0 ​

⭐ 窗口函数 vs GROUP BY ​

补充速览 ​

🧠 助记汇总 ​

✅ 自测清单 ​