会写 SQL 是入门
会建索引是基础
能在高并发场景下扛住流量，才是能力

这篇文章不仅讲原理，还会结合真实业务场景分析：
👉 为什么慢？
👉 为什么锁？
👉 为什么死锁？
👉 为什么突然雪崩？

适合：

• 1–5 年后端开发
• 有线上经验，想补底层认知
• 准备面试冲中高级

---

一、从一个“慢 SQL”场景开始

🎯 场景

用户表 300 万数据，接口突然变慢。

SELECT * FROM user WHERE phone = '138xxxx';

平均耗时 2 秒。

---

🔍 排查第一步：EXPLAIN

EXPLAIN SELECT * FROM user WHERE phone = '138xxxx';

发现：

type: ALL
rows: 3000000

👉 全表扫描。

---

✅ 解决方案

给 phone 建索引：

CREATE INDEX idx_phone ON user(phone);

再次执行：

type: ref
rows: 1

耗时 < 10ms。

---

💡 底层原理

MySQL 默认存储引擎是 InnoDB。

InnoDB 使用 B+ 树作为索引结构：

• 索引查找 = 树查找
• 全表扫描 = 顺序扫描

👉 是否走索引，决定了性能上限。

---

二、索引为什么会失效？（真实线上案例）

🎯 场景 2：明明有索引，却不生效？

SELECT * FROM user WHERE phone LIKE '%138';

索引失效。

---

🔍 原因

B+ 树按顺序存储：

1380000
1381111
1382222

但：

LIKE '%xxx'

不知道前缀是什么 → 无法定位起点 → 全表扫描。

---

✅ 正确方式

LIKE '138%'

👉 可以走索引。

---

三、联合索引真实场景分析

🎯 场景 3：订单系统

订单表 1000 万数据。

查询语句：

SELECT * FROM orders 
WHERE user_id = 1001 
AND status = 1 
AND create_time > '2025-01-01';

---

建立联合索引：

index(user_id, status, create_time)

---

💡 为什么这样建？

因为最左前缀原则：

索引匹配顺序：

user_id
user_id + status
user_id + status + create_time

---

❌ 错误示例

index(status, user_id)

如果业务更多是按 user_id 查 → 索引效果极差。

👉 索引顺序必须基于查询场景设计。

---

四、MVCC 场景解析（电商秒杀）

🎯 场景 4：秒杀库存扣减

库存表：

UPDATE product 
SET stock = stock - 1 
WHERE id = 1001;

并发 1000 人。

---

问题

为什么不会读到脏数据？

---

🔍 原理

InnoDB 通过：

• undo log
• Read View
• 事务 ID

实现 MVCC。

---

读已提交（RC）

每次读取都会生成新的 Read View。

可重复读（RR，默认）

第一次读取生成 Read View。

👉 后续读取基于同一快照。

---

💡 为什么默认 RR？

在 RR 下：

• MVCC + 间隙锁
• 可以避免幻读

这也是 MySQL 默认隔离级别是 RR 的原因。

---

五、锁机制真实场景

---

🎯 场景 5：更新变慢，接口卡死

SQL：

UPDATE user SET balance = balance - 100 WHERE id = 1;

发现接口偶发卡 5 秒。

---

🔍 排查

SHOW ENGINE INNODB STATUS;

发现锁等待。

---

原因分析

事务 A：

UPDATE user SET balance = balance - 100 WHERE id = 1;
-- 未提交

事务 B：

UPDATE user SET balance = balance - 50 WHERE id = 1;

👉 行锁冲突。

---

💡 面试加分点

InnoDB 行锁是基于索引的：

如果没有索引：

UPDATE user SET balance = balance - 100 WHERE name = '张三';

👉 可能锁全表。

---

六、深分页问题（高并发真实场景）

---

🎯 场景 6：订单分页慢到爆

SELECT * FROM orders LIMIT 1000000, 20;

耗时 3 秒。

---

🔍 原因

MySQL 会：

1. 先扫描 1000000 行
2. 丢弃
3. 返回 20 行

👉 代价极高。

---

✅ 优化方案

SELECT * FROM orders
WHERE id > 上一页最大ID
LIMIT 20;

👉 主键游标分页。

---

七、为什么不建议 UUID 做主键？

🎯 场景 7：表越来越慢

使用 UUID 做主键。

---

问题

UUID 无序：

• 插入随机
• 页分裂严重
• 磁盘碎片

---

💡 推荐方案

• 自增主键
• 雪花算法

保证：

• 有序插入
• 减少页分裂

---

八、死锁真实案例

---

🎯 场景 8：转账系统

事务 A：

UPDATE account SET money = money - 100 WHERE id = 1;
UPDATE account SET money = money + 100 WHERE id = 2;

事务 B：

UPDATE account SET money = money - 50 WHERE id = 2;
UPDATE account SET money = money + 50 WHERE id = 1;

---

结果

死锁。

---

原因

A 锁 1 等 2
B 锁 2 等 1

形成循环等待。

---

解决方案

统一加锁顺序：

先锁 id 小的
再锁 id 大的

---

九、线上调优完整思路

当数据库变慢时：

第一步：看慢日志

开启：

SET GLOBAL slow_query_log = 1;

---

第二步：EXPLAIN 分析

关注：

• type
• rows
• key
• extra

---

第三步：检查索引设计

• 是否最左匹配
• 是否覆盖索引
• 是否函数操作

---

第四步：检查锁

SHOW PROCESSLIST;

---

第五步：看事务

是否存在：

• 大事务
• 未提交事务

---

十、MySQL 核心能力模型

如果你想进阶：

必须掌握：

1️⃣ 索引结构
2️⃣ 回表机制
3️⃣ MVCC
4️⃣ 锁模型
5️⃣ 日志系统（redo / undo / binlog）
6️⃣ 调优思路

---

结尾总结

MySQL 性能问题本质只有 3 种：

• 没走索引
• 锁冲突
• IO 过多

掌握底层原理 + 场景分析，你会比 80% 开发更强。