助你拷打面试官系列走到第九天数据库这关绕不过去了。我在后端面试和招聘这两头都坐过凳子MySQL的InnoDB引擎几乎每个技术面都会出现而且一旦聊开就是连环追问索引、事务、锁、MVCC表面是四个名词实际是一个咬合的整体。很多候选人能把概念背得滚瓜烂熟可一被问到为什么就卡壳反过来你能讲到为什么这样设计这一层基本就是面试官心里那档人。今天这篇就拿InnoDB开刀把索引、事务、锁这条线串起来每个问题都配一个反杀点答完之后还能把球抛回给对方——这才是拷打面试官的正确打开方式。1. 先立靶子MySQL这条面试线为什么越问越细1.1 一条真实的追问链条先还原最常见的面试场面面试官你了解MySQL的索引吗 你了解用的B树InnoDB里主键是聚簇索引…… 面试官为什么是B树不是B树 面试官那联合索引为什么要遵守最左前缀 面试官InnoDB默认隔离级别是RR它怎么解决幻读 面试官这时候执行一条update它到底加了几把锁这条链不是面试官提前背好的台词而是顺着答案自然往下长的。索引回答完了自然会问到树结构树结构聊完自然想看看你对索引怎么被用起来的理解聊到用起来就会碰到隔离级别碰到隔离级别就绕不开锁。真正难的从来不是单个知识点而是你知不知道每个知识点之间靠什么接上。1.2 面试官在考场上的分流逻辑这里给大家交个底。大多数有经验的面试官并不指望你每道题都答对他们做的是分层定位能说出概念名称和使用方式定位是用过能解释设计动机定位是真懂能推到边界条件和反例定位是可以深挖。这个定位决定了后面是加面还是收尾。很多面评里写基础扎实和原理研究深入差距往往不在音量而在能不能把为什么讲透。所以这一篇所有问题我都是按第三层准备的标准答案给到动机给到边界情形也给到。1.3 回答长什么样才算过关我常给候选人提一个三层回答法先给结论再给原因最后补一个自己踩过的坑或反例。就拿聚簇索引这道题来说低分回答是聚簇索引叶子节点存数据二级索引叶子节点存主键及格回答是补上所以二级索引查询通常要回表查询列被索引覆盖时不用高分回答再补一句这也解释了为什么主键要短、要顺序增长——二级索引每条都带着主键值主键越大每页能放的索引条目越少树越深IO越多。你注意最后这句不是背的它是把索引结构、磁盘IO、主键设计三条知识接起来之后自然推出来的。面试官听到这种连起来的表达基本就不会再往下考你了。2. 第一刀聚簇索引与二级索引叶子节点里到底存了什么2.1 先给标准答案再解释为什么这么设计这道题的教科书答案很简单InnoDB建表时按主键生成聚簇索引B树的叶子节点直接存整行数据其余索引都是二级索引叶子节点存的是索引列值 主键值二级索引查不到完整行需要拿主键去聚簇索引里再查一次这就是回表。但大部分回答到此为止。真正值钱的是下一个问题为什么InnoDB要这样设计直接把整行数据复制到每个索引的叶子节点不行吗不行。假设一张表有5个二级索引每份都存全量数据那就是6份行数据副本写入时的空间放大受不了更新一个字段要同步改6棵树的叶子节点维护成本也扛不住。所以InnoDB采用了以主键为锚点的做法数据只有一份存在聚簇索引里所有二级索引的叶子节点都只存一个指针——主键值。想要完整数据就顺着主键回表拿。这是一种典型的用一次跳转换一份数据的取舍设计跟对象存储把元数据和数据分开存的思路本质相同。2.2 回表的代价和覆盖索引回表听起来就是多查一次实际是两次B树查找每次都有树高度那么多次磁盘IO。在高并发或机械盘场景下这个代价会被放大得很明显。举个例子假设表结构长这样create table t_user ( id bigint primary key, name varchar(32), age int, city varchar(32), key idx_name(name) );select id, name from t_user where name 小明;这条查询只需要id和name而idx_name的叶子节点里恰好既有name又有id走索引查完直接返回不需要回表这叫覆盖索引。但如果你select *二级索引里没有age和city就必须拿id回聚簇索引取整行。所以我一直说select *顺手写多了真的会在高频查询上吃掉不少性能。2.3 反杀第一问B树的页分裂你遇到过吗等面试官以为你答完了你可以在合适的时机抛一个问题如果插入的主键不是递增的比如UUIDB树会发生什么这个问题看似在问索引结构实际在考验对方有没有真的运维过。顺序插入时新记录基本都追加在最右边的叶子页上页分裂很少随机插入时数据散落在各页某个页满了必须把一半数据搬到新页还会留下碎片页利用率下降树的高度和扫描范围都受影响。这也是为什么MySQL主键推荐自增或有序ID而不是UUID——UUID不仅长而且完全随机。二级索引每条都带着这个又长又乱的主键索引体积和分裂频率都会被放大。2.4 反杀第二问为什么偏偏是B树如果面试官接住了上面那招再往下聊就是为什么InnoDB选B树而不是B树B树的内部节点只存键和指针不存数据这意味着同样大小的一个16KB页B树能放下比B树多得多的键树就更矮。InnoDB里三五层就能撑起几千万上亿行数据查询固定就是三四次磁盘IO换成B树数据存在每个节点里页容量被数据吃掉树更高IO次数更多。还有个更关键的点B树的叶子节点用双向链表串起来了范围查询和排序可以顺着链表连续扫描B树的叶子之间没有这种链表范围查询需要频繁回溯到父节点。等值查询两者差别不大但MySQL里像select where id between 100 and 10000这种范围扫描太常见了B树的结构优势在实战里会被放大得很明显。3. 第二刀最左前缀不是背出来的是B树逼出来的3.1 联合索引的排序规则就是一本电话簿联合索引(a, b, c)可以理解成一本先按a排序、a相同再按b排序、a和b都相同再按c排序的电话簿。注意这个排序是逐列嵌套的不是把三列打个包一起排。知道这个规则最左前缀就很好理解了你在电话簿里只能按姓定位、再按名缩小范围如果你只知道名而不知道姓整本电话簿的顺序对你等于没有只能从头翻。联合索引也一个道理查询条件里用不到a那b和c的排序信息就失去了全局意义索引没法用来定位只能退化成扫描或者让优化器选别的路径。3.2 走不走索引的一张速查表假设有联合索引(a, b, c)各条件组合的情况大致如下查询条件是否能走索引说明where a 1完整走a定位后整棵子树可用where a 1 and b 2完整走a定位后b继续定位where a 1 and b 2 and c 3完整走最理想状态where b 2不走a缺失嵌套顺序不成立where a 1 and c 3部分走a用于定位c无法继续定位可能被ICP在索引内过滤where a 1 and b 2部分走a走范围后b无法继续定位这个表别死记拿电话簿去推一遍就能理解。真正容易翻车的是最后一行范围条件一旦让定位变成区间扫描区间内部的b排序已经不能保证全表有序优化器自然不会再拿b做等值定位。3.3 索引下推MySQL 5.6开始白送的一个优化先看没有索引下推ICP时的流程。还是上面那张表select * from t_user where name like 小% and age 20走idx_name(name, age)。注意age是二级索引里的第二列like的范围条件让age没法用来定位。没有ICP时存储引擎会把所有name以小开头的记录都回表取出来再由Server层过滤age20——明明索引里就有age却非得先回表再判断。ICP的意思就是把一部分条件判断下推到存储引擎扫描二级索引时发现索引条目里的age不等于20直接跳过不产生无谓的回表。判断能下推的前提是条件列确实存在于这个索引里所以查询列尽量留在索引里这个习惯在ICP加入之后变得更重要了——它不仅能靠覆盖索引省回表还能让引擎提前过滤掉更多行。3.4 反杀联合索引能帮ORDER BY和GROUP BY吗最左前缀不只管where还管排序和分组。where a 1 order by b天然用得上索引索引扫描本身就是按b有序的MySQL可以免掉filesortwhere a 1 order by c, b字段顺序反了走不了order by b desc, c asc方向混了也走不了。分组本质上也要排序group by b如果不满足最左前缀照样产生临时表和排序。你面试时能主动说出排序方向不一致也无法利用索引顺序面试官就知道你是在优化器层面思考问题而不是背了最左前缀四个字。4. 第三刀RR为什么能挡住幻读又为什么挡得不彻底4.1 事务隔离级别先摆清楚SQL标准定义了四种隔离级别事务的异常现象有三个脏读、不可重复读、幻读。关系用下表说明隔离级别脏读不可重复读幻读读未提交RU可能可能可能读已提交RC不会可能可能可重复读RR不会不会标准定义仍可能InnoDB实际已挡串行化Serializable不会不会不会InnoDB官方明确说它在RR下通过next-key lock解决了幻读问题所以这张表要按InnoDB的实际行为理解RR解决了一部分快照读下的幻读当前读也会被next-key lock挡住。这句话是这道题的题眼接下来拆开讲。4.2 快照读靠什么实现版本链加ReadViewInnoDB里每行数据可以存在多个版本旧版本放在undo log里行记录上挂着事务号和roll pointer顺着pointer能一路找到旧版本这就是版本链。查询要决定我该看哪个版本就得生成ReadView。ReadView里有四样东西m_ids生成快照时刻还在活跃的事务id列表、min_trx_id活跃事务最小id、max_trx_id下一个将分配的事务id、creator_trx_id生成这个视图的事务自己的id。可见性规则简化一下版本事务id比min还小说明它早提交了可见比max还大说明它是当前视图之后才开始的事务不可见在m_ids里说明还没提交不可见不在m_ids里且大于min说明已经提交可见。如果当前版本不可见就沿roll pointer找下一个旧版本反复按这个规则判断直到找到合适版本。4.3 RC和RR的差异就在ReadView生成时机这是最容易考到的一个点RC是每个快照读都重新生成一个ReadView所以同一事务里两次普通select如果期间别的事务提交了第二次就会看到新数据这就导致不可重复读。RR是在事务第一次执行快照读时生成一份ReadView之后一直复用后面每次select看到的都是同一个历史版本的快照自然就可重复读了。很多候选人对MVCC的了解停在undo log和ReadView两个名词上你能说出生成时机的差异就已经拉开差距了。4.4 快照读与当前读的区别以及幻读不彻底的争议MVCC管的是快照读也就是普通select。而select ... for update、select ... lock in share mode、update、delete、insert这些都属于当前读——读的是最新已提交版本并且会对扫描到的记录加锁。当前读如果只加记录锁并发插入时还会产生幻读所以InnoDB在RR下引入了间隙锁把扫描范围里的空档也锁住禁止别的事务往这个区间插新行这就从根上掐掉了幻读。但在两种读法交替时有个经典小坑事务A先用快照读查出一批行事务B插入并提交一条符合条件的新行然后事务A再用当前读重查会看到B插入的行两次结果集合不一致多了一行。这叫不叫幻读严格按SQL标准RR本来就允许幻读InnoDB说自己解决了幻读指的是当前读在next-key lock保护下不会遇到幻读。这两种说法在面试里碰到一起你当场把它讲清楚就是一次高光时刻。5. 第四刀一条update语句到底加了多少把锁5.1 锁的种类记录锁、间隙锁、临键锁、插入意向锁先把概念摆正记录锁Record Lock锁住索引上的一条记录间隙锁Gap Lock锁住两条记录之间的间隙禁止别人往这个空档插入临键锁Next-Key Lock记录锁加前面的间隙锁左开右闭区间是InnoDB在RR下的默认加锁单位插入意向锁Insert Intention Lock插入前需要先对目标间隙声明我想插如果间隙被别人的间隙锁占着就得等。这里有一句很多人会忽略的话InnoDB的锁是加在索引上的不是加在行这个抽象概念上的。所以在二级索引上加锁时还要去聚簇索引上加对应的主键记录锁两把锁要一起拿。5.2 具体SQL的加锁范围推演先建一张示例表create table t_order ( id bigint primary key, user_id bigint, status varchar(16), key idx_user_id(user_id) );假设RR隔离级别一条条推等值命中唯一索引或主键update t_order set statusdone where id100; 如果id100存在只加一个主键记录锁如果不存在加的是间隙锁用来防止并发插入id100。等值命中普通二级索引update ... where user_id10; 走idx_user_id先给所有user_id10的二级索引记录加临键锁再给对应的主键记录加记录锁。如果user_id10不存在加的是附近范围的间隙锁。无索引等值update ... where statusdone; status没有索引InnoDB只能全表扫描扫描到的每一行都要加临键锁。你没听错一条看起来只影响几行的update在无索引条件下会锁住整个扫描范围线上全表被锁就是这么来的。5.3 死锁现场与排查方式最常见的死锁有两个场景一是两个事务分别持有对方下一步要用的锁互相等待二是插入意向锁和间隙锁互等。举个例子事务1update id1然后update id2事务2先update id2再update id1。两个事务交错执行一个等另一个释放id2的锁另一个等一个释放id1的锁谁也走不动InnoDB会立刻检测到并回滚其中一边。自助排查的三板斧show engine innodb status;看里面的LATEST DETECTED DEADLOCK段能直接看到两个事务各持有什么锁、在等什么锁。另外两张表也常用information_schema.innodb_trx查当前事务和锁等待information_schema.innodb_lock_waits看谁在等谁。线上我一般先看innodb_lock_waits定位等待关系再看innodb_trx揪出慢事务最后用show engine innodb status确认是不是死锁。5.4 实战级反杀先确认delete走的是哪个索引面试如果问delete会不会锁很多行我会直接反问一句delete语句的where条件有没有索引没有索引的delete在RR下相当于全表扫描加锁期间任何往这张表的插入请求都被挡住业务很容易瞬间雪崩。这种工单我见过太多次了所以我删数据之前一定先explain确认delete走哪个索引、预估影响行数条件命中不了索引就先补索引或者分批删同时把大事务拆成小事务。这句话说出来比单纯背delete会加临键锁有价值得多。6. 追问不下去的时候靠什么体面翻盘6.1 不会答也要把思考过程演出来总有那么一两题没准备到这很正常但怎么回应很有讲究。最忌沉默或者硬编一个答案——面试官对技术细节是能听出真假的。比较稳的套路是开口先承认边界这块源码我确实没看过不过从它要解决的问题出发我可以推测……然后把相关的已知知识串起来推。哪怕推错了面试官看到的也是你的分析能力和诚实度这比一个编出来的假答案要好得多。6.2 把话题接到你的舒适区每个人都有自己的强项。被问到不熟的分区表你如果对索引熟可以把话题接到分区和索引的配合关系上被问到不熟的框架可以接我之前在相似场景下是怎么设计方案的。这叫话题桥接不是转移话题而是让面试官看到你处理未知问题的通用方法。注意别接得太硬承认未知再加一段具体相关经验这个度刚刚好。6.3 结尾的反问才是拷打的精髓回到这个系列的标题。拷打面试官不是让你把对方问倒而是在面试最后、或者讨论到某个技术点时提出一个有质量的问题既展示你的思考深度也让对方认可你的水平。我建议分三个段位入门反问问团队技术栈和当前痛点比如你们订单表并发写入量大概什么级别进阶反问针对刚讨论的技术细节确认共识比如刚才那道加锁的题我理解在非唯一二级索引下是临键锁加主键记录锁对吗实际上让面试官帮你复核既客气又有力高手反问抛开放性问题如果主键换成无顺序UUID你们一般怎么评估对索引的影响邀请对方分享实战谁聊得深谁就赢了。这一套下来即使前面有一两道题答得不完美你留给面试官的印象也是这人有技术讨论能力而不是这题他不会。面试这几年我最深的感触是候选人和面试官之间的这场对话本质是互相筛选。你用这篇的思路把索引、事务、锁串起来不是为了背下所有答案而是为了在对话里搭起自己的逻辑链条。MySQL的问题看似无穷无尽真正核心的支撑点就那么几个。当你把B树结构、最左前缀、MVCC和锁的边界都推到接近能设计它的层面面试官能问的东西就真的不多了。而且这套知识平时写代码优化慢查询也用得着——先把原理啃穿再谈面试技巧顺序不能反。
