这一篇的主题:日志双写机制、深分页瓶颈以及死锁怎么查。上次和大家聊了 B 树和 MVCC今天这篇我们直接上硬菜。在社招或者大厂面试中面试官往往不满足于只问你“什么是索引”他们更喜欢切入高并发、大数量、分布式的真实场景。今天复盘的 3 个高频硬核面试题不讲虚的全是底层逻辑和线上实战。一、 日志双写Redo Log 和 Binlog 的“两阶段提交”到底在干嘛面试官最喜欢抛出这个连环炮“既然有了 Binlog为什么 InnoDB 还要搞个 Redo Log”“执行一条 UPDATE 语句底层的日志是怎么写入的”面试官的潜台词我想看看你对高可用、崩溃恢复Crash-Safe和分布式一致性的理解到底在哪一层。1. 为什么必须用两个日志别死记硬背看下分工Binlog归档日志属于MySQL Server 层。不管你用什么存储引擎InnoDB、MyISAM只要发生数据变更它就会记录。它是追加写的用来做主从复制和数据恢复。Redo Log重做日志属于InnoDB 引擎层。它是循环写的空间固定会覆盖专门用来实现Crash-Safe。哪怕数据库忽然断电重启后也能靠它把没刷到磁盘的数据“救回来”。2. 什么是两阶段提交2PC如果执行一句话UPDATE users SET age 20 WHERE id 1;MySQL 是这样分两步写日志的[准备阶段] InnoDB 修改内存数据 - 记录 Redo Log (状态设为 prepare) ↓ [提交阶段] Server 层生成 Binlog 写入磁盘 - InnoDB 修改 Redo Log 状态为 commit为什么这么费劲如果不搞两阶段直接先写 A 再写 B 会怎么样情况一先写 Redo再写 Binlog刚写完 Redo Log系统断电了。重启后InnoDB 靠 Redo Log 把数据恢复成了 age 20。但 Binlog 没来得及写。后面主从同步或者用 Binlog 恢复备库时备库里还是老数据。主备不一致情况二先写 Binlog再写 Redo刚写完 Binlog 机器挂了。重启后InnoDB 发现没有 Redo Log觉得这次事务失败了数据还是原样。但 Binlog 已经发出去了从库执行了这条 Binlog 变成了新数据。又是主备不一致老鸟总结两阶段提交说白了就是分布式事务的简化版。通过把 Redo Log 拆成 prepare 和 commit 两个状态卡住中间的 Binlog确保两边要么一起成功要么一起失败。二、 痛入骨髓的 LIMIT 1000000, 10深分页怎么优化面试官“你们系统数据量大了之后做分页查询越来越慢你怎么解决”避坑别一上来就说分库分表。面试官想听的是你在单表上千万时怎么通过 SQL 优化把性能拉回来。1. 为什么深分页比如 LIMIT 1000000, 10慢到爆炸很多人以为 MySQL 遇到了 LIMIT 1000000, 10是直接跳过前 100 万条只查最后的 10 条。完全错了MySQL 的真实做法是老老实实扫描前1000010条数据如果是 SELECT *它还要吐血地做1000010 次回表最后把前 100 万条丢掉只给你留最后 10 条。CPU 和磁盘 I/O 早就被回表给冲垮了。2. 老鸟拿捏深分页的两种标准姿势方案 A延迟关联覆盖索引法既然回表成本高那我就先不回表。利用覆盖索引先在二级索引树上把这 10 个主键 id 找出来再去回表拿完整数据。 -- 优化前 SELECT * FROM orders ORDER BY create_time LIMIT 1000000, 10; -- 优化后延迟关联 SELECT * FROM orders o JOIN ( SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 10 ) t ON o.id t.id; 原理内层的子查询只拿 id刚好触发了覆盖索引不需要回表速度极快。拿到 10 个 id 后外层只需要做 10 次精确的回表性能直接提升几个数量级。方案 B标签记录法游标分页如果你的业务允许比如手机 App 往下滑动刷新不需要直接跳到第 100 页可以记录上一次查询的最后一条数据的 ID。 SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 10; 原理直接走主键索引定位到具体位置一秒都不耽误。三、 线上死锁发生 Deadlock 了你作为主导怎么排查面试官“线上突然报警提示 Deadlock found when trying to get lock...你怎么排查是哪两段代码冲突了”面试官的潜台词我要听的不是死锁的定义互斥、请求保持啥的我要看你的线上应急能力和日志分析能力。老鸟的标准排查 SOP1. 第一步抓现场立刻登录生产数据库或者看监控日志执行核心命令 SHOW ENGINE INNODB STATUS; 在这个命令输出的长篇大论里找到LATEST DETECTED DEADLOCK这一栏。这里详细记录了最近一次发生死锁的全部罪证。2. 第二步看懂死锁日志拆解罪证日志里通常包含两段核心信息WEITING FOR THIS LOCK TO BE GRANTED事务 A 持有了锁 X正在等待锁 Y。HOLDS THE LOCK(S) 加上 WAITING FOR THIS LOCK TO BE GRANTED事务 B 持有了锁 Y正在等待锁 X。你会清晰地看到两个事务分别执行的是哪句 SQL以及它们分别在抢哪张表的哪个索引是主键索引还是二级索引是记录锁还是间隙锁。3. 第三步对齐业务代码并复盘拿到这两句 SQL 后去代码里搜通常是因为两个并发业务更新物资或者数据的顺序不一致导致的。业务 A 的代码逻辑是先更新商品 1再更新商品 2。业务 B 的代码逻辑是先更新商品 2再更新商品 1。 当两个业务同时跑各执行完第一步时死锁就成了必然。怎么防范让团队开发规范死死卡住所有并发业务更新多表/多行数据的顺序必须保持绝对一致。尽量缩短事务的长度把不需要事务的逻辑比如调外部接口移出事务块。总结面试时聊到这些深水区问题多用“线上排查”和“主备一致”的视角去切入别把自己局限在写代码的工位上要把自己放在架构复盘的角度。面试官听了绝对眼前一亮。觉得有用的话别忘了点赞、收藏下期想看什么技术内幕在评论区留言
MySQL高频面试题-02
这一篇的主题:日志双写机制、深分页瓶颈以及死锁怎么查。上次和大家聊了 B 树和 MVCC今天这篇我们直接上硬菜。在社招或者大厂面试中面试官往往不满足于只问你“什么是索引”他们更喜欢切入高并发、大数量、分布式的真实场景。今天复盘的 3 个高频硬核面试题不讲虚的全是底层逻辑和线上实战。一、 日志双写Redo Log 和 Binlog 的“两阶段提交”到底在干嘛面试官最喜欢抛出这个连环炮“既然有了 Binlog为什么 InnoDB 还要搞个 Redo Log”“执行一条 UPDATE 语句底层的日志是怎么写入的”面试官的潜台词我想看看你对高可用、崩溃恢复Crash-Safe和分布式一致性的理解到底在哪一层。1. 为什么必须用两个日志别死记硬背看下分工Binlog归档日志属于MySQL Server 层。不管你用什么存储引擎InnoDB、MyISAM只要发生数据变更它就会记录。它是追加写的用来做主从复制和数据恢复。Redo Log重做日志属于InnoDB 引擎层。它是循环写的空间固定会覆盖专门用来实现Crash-Safe。哪怕数据库忽然断电重启后也能靠它把没刷到磁盘的数据“救回来”。2. 什么是两阶段提交2PC如果执行一句话UPDATE users SET age 20 WHERE id 1;MySQL 是这样分两步写日志的[准备阶段] InnoDB 修改内存数据 - 记录 Redo Log (状态设为 prepare) ↓ [提交阶段] Server 层生成 Binlog 写入磁盘 - InnoDB 修改 Redo Log 状态为 commit为什么这么费劲如果不搞两阶段直接先写 A 再写 B 会怎么样情况一先写 Redo再写 Binlog刚写完 Redo Log系统断电了。重启后InnoDB 靠 Redo Log 把数据恢复成了 age 20。但 Binlog 没来得及写。后面主从同步或者用 Binlog 恢复备库时备库里还是老数据。主备不一致情况二先写 Binlog再写 Redo刚写完 Binlog 机器挂了。重启后InnoDB 发现没有 Redo Log觉得这次事务失败了数据还是原样。但 Binlog 已经发出去了从库执行了这条 Binlog 变成了新数据。又是主备不一致老鸟总结两阶段提交说白了就是分布式事务的简化版。通过把 Redo Log 拆成 prepare 和 commit 两个状态卡住中间的 Binlog确保两边要么一起成功要么一起失败。二、 痛入骨髓的 LIMIT 1000000, 10深分页怎么优化面试官“你们系统数据量大了之后做分页查询越来越慢你怎么解决”避坑别一上来就说分库分表。面试官想听的是你在单表上千万时怎么通过 SQL 优化把性能拉回来。1. 为什么深分页比如 LIMIT 1000000, 10慢到爆炸很多人以为 MySQL 遇到了 LIMIT 1000000, 10是直接跳过前 100 万条只查最后的 10 条。完全错了MySQL 的真实做法是老老实实扫描前1000010条数据如果是 SELECT *它还要吐血地做1000010 次回表最后把前 100 万条丢掉只给你留最后 10 条。CPU 和磁盘 I/O 早就被回表给冲垮了。2. 老鸟拿捏深分页的两种标准姿势方案 A延迟关联覆盖索引法既然回表成本高那我就先不回表。利用覆盖索引先在二级索引树上把这 10 个主键 id 找出来再去回表拿完整数据。 -- 优化前 SELECT * FROM orders ORDER BY create_time LIMIT 1000000, 10; -- 优化后延迟关联 SELECT * FROM orders o JOIN ( SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 10 ) t ON o.id t.id; 原理内层的子查询只拿 id刚好触发了覆盖索引不需要回表速度极快。拿到 10 个 id 后外层只需要做 10 次精确的回表性能直接提升几个数量级。方案 B标签记录法游标分页如果你的业务允许比如手机 App 往下滑动刷新不需要直接跳到第 100 页可以记录上一次查询的最后一条数据的 ID。 SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 10; 原理直接走主键索引定位到具体位置一秒都不耽误。三、 线上死锁发生 Deadlock 了你作为主导怎么排查面试官“线上突然报警提示 Deadlock found when trying to get lock...你怎么排查是哪两段代码冲突了”面试官的潜台词我要听的不是死锁的定义互斥、请求保持啥的我要看你的线上应急能力和日志分析能力。老鸟的标准排查 SOP1. 第一步抓现场立刻登录生产数据库或者看监控日志执行核心命令 SHOW ENGINE INNODB STATUS; 在这个命令输出的长篇大论里找到LATEST DETECTED DEADLOCK这一栏。这里详细记录了最近一次发生死锁的全部罪证。2. 第二步看懂死锁日志拆解罪证日志里通常包含两段核心信息WEITING FOR THIS LOCK TO BE GRANTED事务 A 持有了锁 X正在等待锁 Y。HOLDS THE LOCK(S) 加上 WAITING FOR THIS LOCK TO BE GRANTED事务 B 持有了锁 Y正在等待锁 X。你会清晰地看到两个事务分别执行的是哪句 SQL以及它们分别在抢哪张表的哪个索引是主键索引还是二级索引是记录锁还是间隙锁。3. 第三步对齐业务代码并复盘拿到这两句 SQL 后去代码里搜通常是因为两个并发业务更新物资或者数据的顺序不一致导致的。业务 A 的代码逻辑是先更新商品 1再更新商品 2。业务 B 的代码逻辑是先更新商品 2再更新商品 1。 当两个业务同时跑各执行完第一步时死锁就成了必然。怎么防范让团队开发规范死死卡住所有并发业务更新多表/多行数据的顺序必须保持绝对一致。尽量缩短事务的长度把不需要事务的逻辑比如调外部接口移出事务块。总结面试时聊到这些深水区问题多用“线上排查”和“主备一致”的视角去切入别把自己局限在写代码的工位上要把自己放在架构复盘的角度。面试官听了绝对眼前一亮。觉得有用的话别忘了点赞、收藏下期想看什么技术内幕在评论区留言
