SYSTEM
MySQL 数据库
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 不走索引
索引失效的情况
- like 左模糊
%xxx - 索引列上用了函数或运算
- 隐式类型转换
- or 条件中有没索引的列
!=或<>不一定走is null/is not null不一定走- 数据量太少,MySQL 觉得全表扫更快
2. 事务(⭐⭐⭐⭐)
MySQL 默认存储引擎:InnoDB(支持事务、行锁、外键)
MVCC(多版本并发控制)
- 实现:每行数据有隐藏字段(事务ID、回滚指针)+ undo log 版本链 + ReadView
- 作用:读写不阻塞,提高并发性能
- RC 隔离级别:每次 select 都生成新的 ReadView
- RR 隔离级别:第一次 select 生成 ReadView,之后复用
Next-Key Lock(临键锁)
- InnoDB 在 RR 级别下,解决幻读的方式
- 行锁 + 间隙锁,锁住一个左开右闭的区间
3. 优化(⭐⭐⭐)
SQL 优化手段
- 合理建索引
- 避免 select *,只查需要的字段
- 用 explain 分析执行计划
- 小表驱动大表
- 分页优化(用子查询/延迟关联,避免大偏移量)
- 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 | 慢 | ✅ | 删指定行 |
三大范式
- 第一范式:列不可再分
- 第二范式:在 1NF 基础上,非主键列完全依赖主键(消除部分依赖)
- 第三范式:在 2NF 基础上,非主键列不传递依赖主键