MySQL 索引与锁深度话题
B+ 树 · 联合索引最左前缀 · EXPLAIN · 事务/MVCC · 行锁间隙锁死锁 · 慢查询优化
🎬 怎么引出
「MySQL 我踩过几个坑。一是大分页慢 — LIMIT 1000000,10 慢得要命,因为要扫前 100 万行,我改成游标分页(WHERE id > last_id);二是死锁 — 两个事务相反顺序更新两行,用 SHOW ENGINE INNODB STATUS 看死锁日志定位,改成统一加锁顺序解决。索引失效也常见 — 查询用了函数 / 隐式类型转换,索引就不走了。」MySQL 全栈必须懂,有真实排查故事很加分。
1
为什么是 B+ 树
B 树
- 所有节点都存数据
- 叶子不连
- 范围查询要回溯
B+ 树(MySQL 用)
- 只有叶子存数据
- 叶子用双向链表连
- 范围查询快(顺着链表走)
- 非叶子只存索引,扇出大,树矮
🧠 为什么不用 hash / 二叉树
Hash: 等值查询快 O(1),但不支持范围查询(ORDER BY/BETWEEN 全废),所以 MySQL 主用 B+。二叉树/红黑树: 数据量大时树高(磁盘 IO 多)。B+ 树矮胖,3-4 层就能存千万级数据,每次查询 3-4 次 IO。
关键数字: InnoDB 默认页 16KB,一般 B+ 树 3-4 层。3 层能存约两千万行(非叶子节点存索引指针,一个页能存上千指针)。所以"千万级表 B+ 树 3-4 次 IO 就能定位"。
2
联合索引 & 最左前缀
⚠️ 最左前缀法则
联合索引 (a, b, c),能用的查询:WHERE a / WHERE a AND b / WHERE a AND b AND c。不能跳过 a 直接用 b、c(最左前缀)。WHERE a AND c 只能用 a(c 用不上,因为中间 b 没参与)。
设计原则: 联合索引把区分度高、最常查的放左边。索引不是越多越好(占空间 + 影响写性能)。
📋 索引失效场景(高频考点)
- 索引列做运算/函数:
WHERE YEAR(create_time)— 改成范围WHERE create_time >= '2024-01-01' - 隐式类型转换: 字段是 varchar,查询
WHERE phone = 13800138000(数字),索引失效 - LIKE 左模糊:
LIKE '%abc'失效,LIKE 'abc%'走索引 - OR 连接非索引列:
WHERE a=1 OR b=2,b 没索引则全失效 - != / NOT IN: 通常走全表(优化器判断)
- 最左前缀不满足: 联合索引跳过最左列
3
EXPLAIN 看执行计划
📋 EXPLAIN 关键字段
| 字段 | 看什么 | 好/坏 |
|---|---|---|
| type | 访问类型 | system > const > eq_ref > ref > range > index > ALL(最差) |
| key | 实际用的索引 | NULL = 没用索引(全表扫) |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index(覆盖索引好)、Using filesort(差)、Using temporary(差) |
| key_len | 索引使用长度 | 判断联合索引用了几列 |
重点: ① type=ALL 全表扫描,必须优化;② Extra 出现 Using filesort / Using temporary 要警惕(慢查询前兆);③ Using index 是好事(覆盖索引,不用回表)。
4
事务隔离级别 & MVCC
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交(RU) | ❌ | ❌ | ❌ |
| 读已提交(RC) | ✅ | ❌ | ❌ |
| 可重复读(RR,MySQL 默认) | ✅ | ✅ | ✅(间隙锁) |
| 串行化(Serializable) | ✅ | ✅ | ✅ |
MVCC(多版本并发控制): 每行数据有隐藏的版本字段,事务读到的是快照版本(不是最新)。读不加锁(快照读),写才加锁(当前读)。好处: 读写不冲突,并发高。RR 级别第一次快照读时建立 read view,之后都用这个(可重复读)。
5
锁:行锁 / 间隙锁 / 死锁
| 锁 | 锁什么 | 场景 |
|---|---|---|
| 记录锁(Record Lock) | 锁一行 | 等值查询命中索引 |
| 间隙锁(Gap Lock) | 锁一个范围(不含记录) | RR 防幻读 |
| 临键锁(Next-Key Lock) | 记录 + 前面的间隙 | RR 默认范围查询 |
| 意向锁 | 表级,标记有行锁 | 加速判断能否加表锁 |
⚠️ 死锁:两个事务相反顺序
事务 A:先锁行 1 再锁行 2;事务 B:先锁行 2 再锁行 1。互相等对方释放 → 死锁。
排查:
SHOW ENGINE INNODB STATUS 看 LATEST DETECTED DEADLOCK 段。预防: ① 多个表/行加锁统一顺序;② 大事务拆小,缩短持锁时间;③ 业务允许用 RC 降低间隙锁。MySQL 有死锁检测,会自动回滚一个(默认超时 50s)。
共享锁 vs 排他锁: S 锁(读锁,共享,
LOCK IN SHARE MODE),X 锁(写锁,排他,FOR UPDATE)。兼容性:S+S 兼容,X 与任何冲突。
6
慢查询优化实战
📋 优化手段
- 加索引: WHERE/ORDER BY/GROUP BY 的字段加,EXPLAIN 确认走索引
- 覆盖索引: 查的字段都在索引里,不用回表(Extra: Using index)
- 大分页优化: LIMIT 1000000,10 →
WHERE id > last_id LIMIT 10(游标)或延迟关联 - 避免 SELECT *: 只查需要的列
- 避免索引失效: 不在索引列做运算/函数/左模糊/类型转换
- JOIN 优化: 小表驱动大表,JOIN 字段加索引,类型要一致
- 子查询改 JOIN: 子查询常产生临时表,JOIN 更快
大分页为什么慢:
LIMIT 1000000, 10 不是"跳过 100 万",而是扫描前 100 万行再丢掉。优化:① 游标分页(记上次最大 id,WHERE id > ?);② 延迟关联(先子查询查出 id,再 JOIN);③ 产品层面限制跳页(只允许上一页下一页)。
7
高频追问
1MySQL 为什么用 B+ 树? 必问
① 叶子链表,范围查询快(顺着走);② 非叶子只存索引,扇出大,树矮(3-4 层存千万级),IO 次数少;③ 比 hash 支持范围/排序;④ 比二叉树矮(二叉树数据多时高,IO 多)。
💬 追问「为什么不用红黑树?」→ 红黑树二叉,千万数据树高 20+,IO 多;B+ 多路,矮。
2联合索引最左前缀?索引什么时候失效? 高频
最左前缀: 联合索引 (a,b,c) 必须从 a 开始用,不能跳。失效场景:索引列运算/函数、隐式类型转换、LIKE 左模糊、OR 连非索引列、!=/NOT IN、不满足最左前缀。排查:EXPLAIN 看 key 是不是 NULL,type 是不是 ALL。
3MySQL 默认隔离级别?MVCC 原理? 高频
默认 RR(可重复读)(其他数据库多是 RC)。MVCC:每行有版本字段,读操作读快照(不加锁),写加锁(当前读)。RR 在第一次快照读建立 read view,之后都用这个 → 可重复读。好处:读写不冲突。幻读 RR 用间隙锁防。
4死锁怎么排查和预防? 高频
排查:
SHOW ENGINE INNODB STATUS 看 LATEST DETECTED DEADLOCK 段,看两个事务持有什么等什么。预防: ① 多表/多行加锁统一顺序;② 大事务拆小缩短持锁;③ 必要时降 RC;④ InnoDB 有死锁检测自动回滚一个。典型:两个事务相反顺序更新两行。5大分页 LIMIT 慢怎么优化? 高频
LIMIT 1000000,10 要扫前 100 万行。优化: ① 游标分页(WHERE id > 上次最大 id LIMIT 10);② 延迟关联(先子查询出 id 再 JOIN);③ 产品限制跳页(只上下页)。本质:避免扫描然后丢弃大量行。6覆盖索引和回表? 中等
回表: 二级索引找到主键,再回主键索引查完整行(两次 B+ 查)。覆盖索引: 查的字段都在索引里,不用回表(一次 B+ 查),EXPLAIN 显示 Using index。优化:把常查的字段建联合索引,实现覆盖索引避免回表。
7聚簇索引和非聚簇索引? 基础
聚簇索引: 叶子节点存完整行数据(InnoDB 主键索引),一张表只有一个。非聚簇索引(二级索引): 叶子存主键值,要回表查完整行。MyISAM 都是非聚簇(叶子存行地址)。InnoDB 主键是聚簇,其他索引是非聚簇。
8
30 秒陈述
🎤 30 秒版
「MySQL 我踩过几个坑。大分页 LIMIT 100 万慢(要扫前 100 万行),改游标分页 WHERE id > last_id。死锁用 SHOW ENGINE INNODB STATUS 看日志,改成统一加锁顺序解决。索引失效踩过 — 字段是 varchar 查询传数字隐式转换索引不走。原理上 MySQL 用 B+ 树(叶子链表范围查询快、树矮 IO 少),默认 RR 隔离 + MVCC 快照读读写不冲突。优化先 EXPLAIN 看 type/key,加覆盖索引避免回表。」