跟着AI学习Java，第三天

Java小趴菜
4 阅读9分钟

第 3 天学习任务

MySQL 核心（索引、事务、隔离级别、锁、MVCC、执行计划）

一、索引详解

1. 索引本质

索引是帮助 MySQL 高效获取数据的排好序的 B + 树数据结构，核心是「空间换时间」—— 牺牲写入性能（增删改需维护索引），提升查询性能。

2. InnoDB B + 树索引底层实现（源码级）

B + 树物理结构（以 InnoDB 默认页大小 16KB 为例）

层级存储内容大小作用
根节点索引键 + 子节点指针16KB定位到下一层节点
非叶子节点索引键 + 子节点指针16KB缩小查询范围
叶子节点聚簇索引：整行数据；二级索引：主键值16KB / 节点存储最终数据 / 回表依据

B + 树 vs B 树 核心区别（必考）

维度B 树B + 树
节点存储内容非叶子 / 叶子节点都存「键 + 数据」非叶子节点只存「键 + 指针」，叶子节点存「键 + 数据」
叶子节点关系独立节点，无关联所有叶子节点通过双向链表串联
数据存储位置分散在所有节点全部集中在叶子节点
节点大小大（包含数据）小（非叶子仅存键）
树高度更高（节点存储少）更低（16KB 页能存更多键）
IO 次数更多（高度高 + 节点数据多）更少（高度低 + 单次 IO 获取更多键）
范围查询需遍历多个节点，效率低叶子链表直接遍历，效率极高
全表扫描需遍历所有节点仅遍历叶子链表，速度更快
适用场景随机查询为主（如 MongoDB）范围查询 / 排序为主（如 MySQL）

为什么 InnoDB 用 B+ 树，不用 B 树？

  1. IO 效率更高：B + 树非叶子节点只存键，16KB 页能存更多键，树高度更低（通常 3-4 层），查询只需 3-4 次 IO；B 树节点存数据，高度更高，IO 次数更多。
  2. 范围查询更快：B + 树叶子节点是双向链表，范围查询（between、order by）只需遍历链表；B 树需逐节点查找，效率低。
  3. 缓存命中率更高：B + 树非叶子节点只存键，相同内存能缓存更多索引键，减少磁盘 IO；B 树节点包含数据，缓存利用率低。
  4. 数据一致性更好：B + 树数据只在叶子节点，修改时仅需维护叶子节点；B 树数据分散在所有节点，修改成本高。

为什么不用 Hash 索引？

  • 只支持 = 查询，不支持 >、<、between、order by、范围查询。

3. 索引分类

  1. 主键索引（PRIMARY KEY）

    • 唯一、非空、一张表只能一个。
    • 叶子节点存整行数据（聚簇索引）。

  2. 唯一索引（UNIQUE）

    • 列值必须唯一，允许 NULL。

  3. 普通索引（INDEX）

    • 最常用，仅加速查询。

  4. 联合索引

    • 多个字段组合成一个索引。
    • 遵循最左前缀原则。

  5. 覆盖索引

    • 查询的字段全部包含在索引中，不需要回表。

  6. 全文索引（FULLTEXT）

    • 用于长文本搜索。

🔥 覆盖索引 超详细

1. 什么是覆盖索引

  • 查询所需的所有字段，都在索引树里，不需要再回表查聚簇索引。
  • 一句话：只查索引，不查数据行。

2. 覆盖索引的好处

  1. 避免回表，减少一次 B + 树 查找
  2. 减少随机 IO，性能提升巨大
  3. 索引更小更容易缓存，命中率更高

3. 覆盖索引判断标准

只要 select 字段、where 字段、order by 字段、group by 字段 全部包含在一个索引里，就是覆盖索引。

示例：索引：idx_name_age (name, age)

sql

select name, age from user where name = '张三';

✅ 覆盖索引，不需要回表。

sql

select name, age, gender from user where name = '张三';

❌ gender 不在索引里，必须回表。

4. 联合索引 + 覆盖索引 最优实践

建立联合索引时，把查询字段也放进索引，实现覆盖索引。

例：经常执行：

sql

select name, age from user where name = ? order by age;

直接建：

sql

index (name, age)

完全覆盖，性能最优。

5. 如何从 explain 看覆盖索引

  • Extra 列出现 Using index → 就是覆盖索引！
  • 这是 MySQL 最优查询状态之一。

4. 聚簇索引 vs 非聚簇索引（核心区别）

维度聚簇索引非聚簇索引（二级索引）
存储内容整行数据主键值
数量一张表仅 1 个可多个
叶子节点按主键有序排列按索引键有序排列
查询性能一次查询直达需回表（覆盖索引除外）
插入性能主键有序插入快，随机插入慢无主键依赖，相对稳定

5. 最左前缀原则（底层逻辑）

  • 联合索引 (a,b,c) 的 B + 树，按「a→b→c」的顺序排序；
  • 查询条件必须从最左列 a 开始，才能匹配索引的排序逻辑：✅ 命中：where a=1 / where a=1 and b=2 / where a=1 and b=2 and c=3❌ 未命中：where b=2 / where a=1 and c=3（c 失效）

6. 索引失效底层原因

  1. 对索引列使用 函数、运算、隐式转换
  2. != / <> / is null / is not null
  3. like '% xxx'` 左边 %
  4. 违背最左前缀
  5. 使用 or 但有一侧无索引
  6. MySQL 优化器认为全表更快（数据量小）

7. 建索引最佳实践

  • where、order by、group by、join 字段建索引
  • 区分度高的字段建索引
  • 大表必须建索引
  • 尽量用联合索引，少用单字段索引
  • 尽量设计成覆盖索引，避免回表
  • 不要建太多索引（写入变慢）

二、事务 ACID（底层实现）

1. 原子性（Atomicity）

  • 实现：undo log（回滚日志）
  • 原理：事务执行时，记录每一步修改的「反向操作」到 undo log；事务失败时，执行 undo log 的反向操作，恢复数据到事务前状态。

2. 一致性（Consistency）

  • 业务规则 + 数据库约束（主键、外键、唯一索引） + ACID 其他特性共同保证。

3. 隔离性（Isolation）

  • 实现：锁 + MVCC；
  • 不同隔离级别通过「锁的粒度」「MVCC 的可见性规则」控制并发干扰。

4. 持久性（Durability）

  • 实现：redo log（重做日志） + 双写缓冲区

  • 原理：

    1. 事务提交时，先将修改写入 redo log（内存 + 磁盘）；
    2. MySQL 后台线程异步将 redo log 的修改刷到磁盘数据文件；
    3. 即使宕机，重启后通过 redo log 恢复未刷盘的修改，保证数据不丢失。

三、事务隔离级别 & 并发问题

1. 三大并发问题对比（表格已恢复）

问题场景根本原因
脏读事务 A 读到事务 B 未提交的修改隔离级别太低（RU）
不可重复读事务 A 两次读同一行，事务 B 修改并提交RC 下 ReadView 每条 SQL 都更新
幻读事务 A 范围查询，事务 B 插入新行，A 再查多一行无间隙锁，新行可插入

2. 四大隔离级别（底层实现差异・表格已恢复）

隔离级别解决问题实现逻辑
读未提交（RU）无直接读最新数据，无锁、无 MVCC
读已提交（RC）脏读每条 SQL 执行时生成新的 ReadView，只看已提交事务的数据
可重复读（RR）脏读、不可重复读、幻读（基本）事务启动时生成一次 ReadView，全程不变；加间隙锁 / 临键锁防止新行插入
串行化（Serializable）所有问题所有操作加表锁，事务串行执行

四、MVCC（多版本并发控制）- 完整实现（压轴）

1. MVCC 核心定义

通过为数据生成多个历史版本，让读操作访问历史版本，实现读写不冲突，是 InnoDB 高并发核心。

2. 核心三大组件

（1）隐藏列（每行数据自带）

  • db_trx_id：最后修改该行的事务 ID
  • db_roll_ptr：指向 undo log 的指针，形成版本链
  • db_row_id：无主键时自动生成

（2）undo log（版本链）

  • 每次修改生成一条 undo log，通过roll_ptr串联成版本链（最新→最旧）。

（3）ReadView（可见性视图）

  • 生成时机：

    • RC：每条 SQL 执行时生成
    • RR：事务启动时生成一次（全程不变）

  • 包含 4 个值：① m_ids：当前活跃事务 ID 列表② min_trx_id：最小活跃 ID③ max_trx_id：下一个要分配的 ID④ creator_trx_id：当前事务 ID

3. 可见性判断规则（核心）

遍历版本链：

  1. trx_id < min_trx_id → 可见
  2. trx_id > max_trx_id → 不可见
  3. 在活跃列表里 → 不可见
  4. 不在 → 可见

4. MVCC 与 幻读

  • 快照读（普通 select） ：MVCC 解决幻读
  • 当前读（for update/insert/update/delete） ：靠临键锁解决幻读

五、InnoDB 锁机制

1. 表锁 vs 行锁

  • 表锁：锁全表，开销小，并发低
  • 行锁：锁行，开销大，并发高，必须命中索引

2. 行锁三种算法

  1. 记录锁（Record Lock） ：锁单行
  2. 间隙锁（Gap Lock） ：锁区间，防止插入
  3. 临键锁（Next-Key Lock） ：默认，记录锁 + 间隙锁，解决幻读

3. 行锁变表锁的场景

  • 无索引 / 索引失效
  • 范围条件过大
  • 事务过长

六、explain 执行计划

字段核心值解读
typeall全表扫描，必须优化（加索引）
typerange范围扫描（in/between/>/<），性能一般
typeref非唯一索引扫描，性能较好
typeeq_ref唯一索引扫描，性能优
typeconst/system主键 / 唯一索引匹配，仅 1 行结果，性能最优
keyNULL未使用索引
ExtraUsing filesort无法用索引排序，需在内存 / 磁盘排序，优化：加排序字段的索引
ExtraUsing temporary创建临时表存储中间结果，优化：避免 group by/order by 的非索引字段
ExtraUsing index覆盖索引，无需回表，性能最优

七、高频面试题（标准答案）

  1. B 树和 B + 树区别？为什么 MySQL 用 B + 树？
  2. 什么是覆盖索引？怎么判断？
  3. MVCC 怎么实现？RC 和 RR 区别？
  4. 事务 ACID 分别靠什么保证？
  5. InnoDB 怎么解决幻读？
  6. 什么是回表？如何避免？答：使用覆盖索引，让查询字段全部包含在索引里，避免回表。
avatar
文章
阅读
粉丝