← 返回学习路线

SYSTEM

MySQL 数据库

面试复习三、MySQL 数据库growth
src/content/notes/growth/MySQL-数据库.md

三、MySQL 数据库

1. 索引(⭐⭐⭐⭐⭐ 必问)

索引底层数据结构

  • B+ 树(MySQL InnoDB 默认)

为什么用 B+ 树不用 B 树 / 红黑树?

  • vs B 树:B+ 树所有数据都在叶子节点,查询更稳定;叶子节点有链表,范围查询快
  • vs 红黑树:红黑树是二叉,树高高,磁盘 IO 次数多;B+ 树多路,树高矮

聚簇索引 vs 非聚簇索引

对比项 聚簇索引 非聚簇索引(二级索引)
叶子节点 存整行数据 存主键值
数量 一张表只有一个 可以有多个
回表 不需要 需要(先查二级索引拿到主键,再回表查聚簇索引)

覆盖索引

  • 查询的字段刚好在二级索引里就能拿到,不需要回表
  • 比如 select name from user where name = 'xxx',name 有索引

最左前缀原则

  • 联合索引 (a, b, c),只有 a、a+b、a+b+c 能走索引
  • b、c、b+c 不走索引

索引失效的情况

  1. like 左模糊 %xxx
  2. 索引列上用了函数或运算
  3. 隐式类型转换
  4. or 条件中有没索引的列
  5. != 或 <> 不一定走
  6. is null / is not null 不一定走
  7. 数据量太少,MySQL 觉得全表扫更快

2. 事务(⭐⭐⭐⭐)

MySQL 默认存储引擎:InnoDB(支持事务、行锁、外键)

MVCC(多版本并发控制)

  • 实现:每行数据有隐藏字段(事务ID、回滚指针)+ undo log 版本链 + ReadView
  • 作用:读写不阻塞,提高并发性能
  • RC 隔离级别:每次 select 都生成新的 ReadView
  • RR 隔离级别:第一次 select 生成 ReadView,之后复用

Next-Key Lock(临键锁)

  • InnoDB 在 RR 级别下,解决幻读的方式
  • 行锁 + 间隙锁,锁住一个左开右闭的区间

3. 优化(⭐⭐⭐)

SQL 优化手段

  1. 合理建索引
  2. 避免 select *,只查需要的字段
  3. 用 explain 分析执行计划
  4. 小表驱动大表
  5. 分页优化(用子查询/延迟关联,避免大偏移量)
  6. join 不超过 3 张表

explain 关键字段

  • type:system > const > eq_ref > ref > range > index > ALL(越往左越好)
  • key:实际用的索引
  • rows:预估扫描行数
  • Extra:
    • Using index:覆盖索引
    • Using where:需要回表过滤
    • Using filesort:需要外部排序(不好)
    • Using temporary:用到临时表(不好)

4. 基础题

char vs varchar

  • char:定长,快,浪费空间
  • varchar:变长,省空间,稍慢

drop / truncate / delete 区别

操作 类型 速度 可回滚 删除范围
drop DDL 最快 ❌ 删表(结构+数据)
truncate DDL 快 ❌ 删所有数据,保留表结构
delete DML 慢 ✅ 删指定行

三大范式

  1. 第一范式:列不可再分
  2. 第二范式:在 1NF 基础上,非主键列完全依赖主键(消除部分依赖)
  3. 第三范式:在 2NF 基础上,非主键列不传递依赖主键