MySQL高频面试题深度拆解：从索引优化到死锁排查全攻略-CSDN博客

本文链接：https://blog.csdn.net/pythonxiaowang8/article/details/147360248

前言：为什么MySQL总是面试重灾区？

（敲黑板）但凡做过互联网项目的同学都知道，数据库就是系统的"任督二脉"！特别是MySQL这个扛把子选手，从初创公司到BAT大厂，几乎每个技术面试都会碰到它的灵魂拷问。今天咱们就扒开MySQL的底裤，看看那些高频面试题背后真正的考点都在哪！！！

一、索引优化：你以为加了索引就完事了？

1.1 B+树索引的底层玄机

记住这句话：MySQL的索引就像图书馆的目录卡片（但比那个高级多了）！B+树的三层结构（根节点->中间节点->叶子节点）是面试官最爱挖的坑。画个重点：

叶子节点双向链表结构（范围查询快如闪电）
非叶子节点只存索引键（节省空间小能手）
树高度通常不超过4层（千万级数据也能hold住）

1.2 最左前缀原则实战案例

有个血泪教训必须说：曾经有个项目加了复合索引(a,b,c)，结果查询where b=1 and c=2时索引直接失效！为什么？（答案在下面）

![B+树索引结构示意图]（此处应有图但按规则不展示）

正确打开方式：

-- 正确姿势
SELECT * FROM table WHERE a=1 AND b=2;

-- 作死姿势（索引失效警告！）
SELECT * FROM table WHERE b=2 ORDER BY a;

1.3 覆盖索引的隐藏buff

当看到Extra列出现Using index时（恭喜中奖！），说明查询所需字段全在索引里。这时候连数据文件都不用读，性能直接起飞！举个栗子：

-- 需要回表
SELECT * FROM user WHERE age>20;

-- 覆盖索引直接返回
SELECT id,age FROM user WHERE age>20;

二、事务隔离级别：你以为的幻读可能是个假象

2.1 四大隔离级别对照表（背不下来就等着被挂吧！）

级别	脏读	不可重复读	幻读	实现方式
读未提交	✅	✅	✅	无锁
读已提交(RC)	❌	✅	✅	快照读
可重复读(RR)	❌	❌	✅	MVCC+间隙锁
串行化	❌	❌	❌	全表锁

2.2 MVCC多版本并发控制揭秘

重点来了！InnoDB的隐藏字段DB_TRX_ID和DB_ROLL_PTR构成了版本链。不同隔离级别下，ReadView的生成策略也不同：

RC级别：每次select都生成新ReadView
RR级别：第一次select生成ReadView

（灵魂拷问）为什么RR级别下还会出现幻读？因为当前读（如SELECT ... FOR UPDATE）会触发间隙锁！

三、锁机制：死锁排查的骚操作

3.1 行锁/表锁/间隙锁的相爱相杀

昨晚线上刚发生的事故：两个事务互相等待对方的锁，导致接口超时。怎么快速定位？

-- 死锁检测神器
SHOW ENGINE INNODB STATUS;

输出里找LATEST DETECTED DEADLOCK部分，你会看到：

*** (1) TRANSACTION: 执行了UPDATE table SET ... WHERE id=1
*** (1) HOLDS THE LOCK(S): 行锁id=1
*** (2) TRANSACTION: 执行了UPDATE table SET ... WHERE id=2
*** (2) HOLDS THE LOCK(S): 行锁id=2
*** 互相等待对方的锁...

3.2 避免死锁的三大绝招

事务尽量小（别在事务里调第三方接口！）
访问资源的顺序要一致（比如都按id升序操作）
合理设置超时时间innodb_lock_wait_timeout

四、性能优化：慢查询日志的正确食用方式

4.1 让慢查询无所遁形

配置文件中打开慢查询日志：

slow_query_log = 1
long_query_time = 2  # 超过2秒的查询
slow_query_log_file = /var/log/mysql/slow.log

然后用mysqldumpslow工具分析：

# 查看前10条慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

4.2 EXPLAIN执行计划全解析

看到type=ALL（全表扫描）就要警惕了！执行计划关键列解读：

type：从优到差 system > const > ref > range > index > ALL
key_len：索引使用长度（越大越好）
rows：预估扫描行数（越小越好）

五、分库分表：什么时候该祭出这个大杀器？

5.1 拆库拆表的三大信号

单表数据量突破500w（不是绝对！）
QPS超过2000且无法通过缓存优化
业务有明显的热点数据特征

5.2 分片策略选择困难症

范围分片：适合有时间特征的数据（但容易热点）
哈希分片：数据分布均匀（但无法范围查询）
基因分片：在主键中携带分片信息（折中方案）

（血泪经验）曾经用一致性哈希分片，结果扩容时数据迁移量高达40%！后来改用基因法分片，扩容只需迁移10%数据。

六、终极灵魂拷问：主从同步延迟怎么办？

线上遇到从库延迟好几秒，用户看到"脏数据"怎么办？试试这些方案：

半同步复制（保证至少一个从库收到日志）
并行复制（设置slave_parallel_workers>1）
强制走主库查询（简单粗暴但有效）
中间件缓存主库位点（如ShardingSphere的Hint强制路由）

结语：MySQL学习的正确姿势

记住这个学习路线（划重点）：
1️⃣ 先搞懂索引原理和事务机制 →
2️⃣ 实践锁机制和死锁排查 →
3️⃣ 掌握性能优化方法论 →
4️⃣ 最后研究架构扩展方案

（超级重要）一定要自己动手复现各种场景！比如：

故意制造死锁观察现象
用100万数据测试不同索引效果
模拟主从延迟的业务影响

最后送大家一句话：MySQL学得好，offer随便挑！遇到难题别怂，直接EXPLAIN+慢查询分析干就完了！