★ FEATURED ARTICLE 某大厂OD技术面第三轮MySQL这一块占了将近一半时间。面试官没有让我背八股文而是直接贴了一条线上真实出现过的慢查询从执行计划开始问一路追到优化器为什么放弃索引、MVCC的ReadView到底怎么生成、从库延迟到底卡在哪一环。说实话平时写SQL、建索引我也算熟练但被这样一层层剥开问还是有点招架不住。这篇文章把我在这一轮里印象最深的几道题、当时的回答思路、以及面完查资料补上的细节一起复盘出来希望能帮同样在准备OD或中级后端岗位面试的朋友把MySQL知识从“会用”提升到“能讲清楚为什么”。先说结论OD技术面的MySQL题目一般不会让你手写特别复杂的存储过程更看重你能否快速定位慢查询、说清事务隔离与锁、解释主从复制中的延迟问题。这些知识点单独拎出来都不算难难的是面试官不断追问“为什么”的时候能不能一直往下讲而不自相矛盾。下面每一章都尽量还原面试现场也标注了我当时没答好的地方。1. 一条慢查询把索引知识问到底EXPLAIN只是起点1.1 题目原样几十万行的表这条SQL为什么慢面试官在共享屏幕上贴了一张表结构名字很普通就是一张用户订单表。CREATE TABLE user_order ( id bigint PRIMARY KEY AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(32) NOT NULL, status tinyint NOT NULL DEFAULT 0, created_at datetime NOT NULL, KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;慢查询是SELECT * FROM user_order WHERE status 1 ORDER BY created_at DESC LIMIT 20;他问我这张表有几十万行这条查询耗时在1.2秒左右你怎么优化我第一反应不是“加索引”而是先确认status这个条件到底能不能筛选出足够小的数据集合。因为从表结构看status只做了普通二级索引而这类状态字段的取值通常就那么几个区分度很低。如果一个状态下有80%的行优化器完全可能认为走索引回表比全表扫描更贵。我当时回答了两步先看数据分布再决定索引方案。数据分布可以用一条分组SQL确认SELECT status, COUNT(*) AS cnt FROM user_order GROUP BY status;如果status1占绝大多数那JOIN或查询本身是低选择性的单纯在status上建索引很难生效如果status1只占很小一部分在(status, created_at)上建复合索引既能让where快速过滤又能让order by走索引避免文件排序。1.2 现场分析EXPLAINtype、key、rows、Extra怎么串起来面试官让我把这条SQL的EXPLAIN输出画出来意思很明显想确认我是真用过EXPLAIN还是只背过几个字段名。这条查询做一个最简单的EXPLAIN正常会看到类似下面的结果---------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ---------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | user_order | NULL | ALL | idx_created_at| NULL | NULL | NULL | 456789 | 33.33 | Using filesort | ----------------------------------------------------------------------------------------------------------------type为ALL说明全表扫描possible_keys里虽然有idx_created_at但优化器最终没选择它因为where条件里没有可用的过滤列走created_at索引只能用来排序回表代价太高。rows显示40多万行估算filtered只有33%左右意味着大量行需要读取和过滤。我当时分析的重点放在Extra这一列。看到Using filesort说明排序没有走索引如果强制走idx_created_atEXPLAIN里会出现Backward index scan表示MySQL在反向扫描索引树取到created_at较大的行后还需要回表去拿整行再判断status是否为1。和全表扫相比哪个代价更大优化器心里有一本账。1.3 面试官追问加上(status, created_at)复合索引就够了吗我提出复合索引方案后面试官没有停紧接着问是不是加了复合索引就一定能走这里有个很关键的判断如果status1占比非常高即使有(status, created_at)优化器也可能走全表扫描。因为扫描所有status1的主键并进行回表可能比一次全表顺序扫描更慢。全表扫描可以利用预读按物理顺序读盘回表则是随机IOIO成本完全不是一个量级。正确的优化思路是尽量让查询变成覆盖索引。比如把SQL改成只查必要字段然后建一个包含排序字段和查询条件字段的索引ALTER TABLE user_order ADD INDEX idx_status_created_at_id (status, created_at, id); SELECT id, user_id, order_no, status, created_at FROM user_order WHERE status 1 ORDER BY created_at DESC LIMIT 20;如果查询的所有字段都在二级索引中就不需要回表取完整行。这样即使status1占比高扫描二级索引的代价也比“索引扫描回表”低很多优化器更愿意选择这个索引。注意覆盖索引并不能把status1的所有行都变成一条它只是减少了回表这个大头让limit 20在拿到20条后就能提前终止。面试官这时候又追问了一句既然只要20条为什么全表扫描也要扫完40万行因为全表扫描没有地方可以“提前停止”它不知道哪些行会有status1只能一路判断。而覆盖索引扫描可以在拿到20行后立刻停止这是覆盖索引在低区分度场景下的真正价值。1.4 一个加分项打开optimizer trace看优化器的成本账回答到“优化器觉得回表更贵”时面试官问我你怎么知道优化器是这么想的当时我有点卡住后来复盘想到的正确做法是打开optimizer trace让优化器把成本计算过程全部列出来。SET optimizer_traceenabledon; SELECT * FROM user_order WHERE status 1 ORDER BY created_at DESC LIMIT 20; SELECT * FROM information_schema.OPTIMIZER_TRACE;在输出的JSON里要重点看considered_execution_plans和cost相关字段可以看到每个候选执行计划的扫描行数、回表代价、排序代价。只有从这个层面分析才算真正理解了优化器。面试官没有要求我把JSON背下来但他显然希望我知道这个工具的存在而不是只会背“typeALL不好”。2. 事务隔离级别现场画ReadView再讲间隙锁2.1 MySQL为什么默认用可重复读而不是读已提交面试官从慢查询切到了事务问得很直接MySQL默认隔离级别是什么为什么是它这个我能答默认是REPEATABLE READ也就是可重复读。至于为什么背景是早期binlog格式默认是statement也就是记录SQL语句本身。在READ COMMITTED下主库一个事务中删除和插入多条记录时binlog里记录的语句顺序可能导致从库回放结果不一致所以MySQL用可重复读配合间隙锁来规避这个麻烦。后来binlog已经是ROW格式记录的是每行的变更其实主从一致性对隔离级别的要求没那么苛刻所以很多互联网公司会把隔离级别改成READ COMMITTED来减少锁竞争。但是注意MySQL的RR和标准SQL里的RR并不是完全等价的它通过MVCC和Next-Key Lock解决了大部分幻读但仍然有边界场景。这是我后面才补全的理解。2.2 MVCC现场推演两个事务并发读同一个行版本面试官让我在白板上画一下事务A和事务B并发执行事务A第一次查询看到的是旧值事务B更新并提交事务A第二次查询为什么还是旧值。这本质是考MVCC的快照读实现。假设表t有一行id1nameA当前事务版本号trx_id100。事务A在版本号101开始执行普通select事务B在版本号102执行了update把name改成B。在InnoDB里update不会直接覆盖旧行而是生成一个新版本旧版本通过undo log链被保留。事务A第一次select时会创建一个ReadView记录当前活跃事务列表也就是还没提交的事务集合。因为事务B的102在活跃列表里A会认为102修改的新版本不可见于是顺着undo log链找到旧版本读到A。事务B提交后事务A再次select在RR隔离级别下会复用第一次创建的ReadView而不是重新生成。活跃列表中仍然有102所以A依然看不到B的修改这就是所谓可重复读。这里有个容易困惑的点如果事务A的select是SELECT * FROM t WHERE id1 FOR UPDATE那属于当前读读的是最新已提交版本不走ReadView可见性判断而是走加锁读取。面试官看我把这两者混在一起专门纠正了一下。2.3 幻读到底靠谁解决MVCC和Next-Key Lock的边界面试官标准追问来了RR不是能解决幻读吗那为什么还要有间隙锁这个问题很多面经讲得含糊。我在现场梳理成两条线普通快照读比如不带for update的select靠MVCC保证同一事务多次查询结果一致相当于看不到别的事务新插入的行当前读比如带for update或在update语句里需要锁定真实的数据范围这时MVCC就没法阻止别人插入满足条件的新行必须靠锁。InnoDB使用的隔离方案是Next-Key Lock也就是记录锁加间隙锁的组合。假设事务A执行SELECT * FROM user_order WHERE user_id100 FOR UPDATE在RR下InnoDB会给扫描到的user_id100的索引记录加记录锁同时给这些记录之间的间隙加间隙锁。间隙锁锁的不是某一行而是不允许别的事务在这个范围内插入新行。这样的话事务B想插入一条user_id100的新订单时会被阻塞直到事务A提交或回滚。面试官还追问了一个特殊情况如果user_id100的记录在当前表里不存在事务A的for update会把整个间隙锁住吗答案是会的这也是RR下很容易产生死锁的原因。我在实际项目里见过因为一个不存在主键的for update把全表某段区间锁住导致后续大量插入阻塞。这类问题不是理论而是生产事故。2.4 如果把隔离级别改成RC会发生什么面试官问我既然RR有间隙锁、死锁概率高那改成RC有什么代价我回答RC下每次普通select都会生成新的ReadView所以同一事务里两次查询可能看到不同版本产生不可重复读。RC也没有间隙锁只有记录锁插入并发能力更强降低死锁概率。代价是如果一个事务先查了一条记录另一事务更新并提交再查可能读到新值某些业务逻辑不能被接受。但需要注意RC下依然有当前读和快照读的区别只是快照读每次重新生成快照。互联网高并发场景选RC往往是因为它更贴近“读已提交”的直觉配合ROW格式binlog也足够安全。我给出的结论是没有绝对的谁好谁坏关键看业务是否能容忍同一事务内读到不同的值。这个回答让面试官点头了。3. 三道“看起来简单”的索引失效题函数、隐式转换、LIKE3.1 字符串列与整型比较索引为什么失效面试官出了第一道SQL表user里user_no是varchar(32)有唯一索引查询条件是WHERE user_no 12345678问索引会不会生效。我一眼看出坑MySQL在比较会隐式把字符串转成浮点或整型字符串列作为索引列等值比较时会先对列做类型转换相当于对索引列套了一层函数结果就是索引失效。很多同学容易记反如果条件是WHERE id 123id是整型索引仍然有效因为MySQL是把右边的字符串转成整型而不是对id列做函数。真正失效的是索引列被隐式转换的那个方向。我在现场把这个区别说了一遍面试官明显对此很满意因为很多答案只说了“字符串列与数字比较会导致索引失效”但没有说清为什么以及方向性。3.2 JOIN时字符集不一致一个隐蔽的慢查询原因第二道题是一个真实业务里容易踩的坑。面试官说有两张表order_info和pay_infoorder_no字段都是varchar(32)但一张表是utf8mb4另一张是utf8mb3JOIN条件用order_no关联问为什么特别慢。慢的原因不是索引没建而是JOIN比较时MySQL会把utf8mb3自动转换为utf8mb4因为utf8mb4是utf8mb3的超集。这个转换如果发生在表的某列上索引就无法正常用于关联。验证方法很简单还是EXPLAIN。正常情况下被驱动表应该显示type为ref慢查询时却显示ALL同时能看到连接字符集相关的提示。优化方向是统一字符集把两张表的字段都改成utf8mb4或者都改成相同的排序规则避免隐式转换。这里有个实操提醒直接在大表上执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4可能非常耗时后续版本支持INPLACE但线上操作还是要评估磁盘空间和IO压力最好放在低峰期。3.3 函数调用和LIKE前缀索引优化的边界在哪面试官给了三条SQL问哪条可能走索引SELECT * FROM t WHERE DATE(created_at) 2024-01-01; SELECT * FROM t WHERE mobile LIKE 138%; SELECT * FROM t WHERE name LIKE %三%;第一条是典型的函数包裹索引列DATE(created_at)导致普通created_at索引失效正确写法是范围查询created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00这样可以直接走索引B树的范围扫描。第二条LIKE 138%是前缀匹配MySQL可以用索引的B树特性从13800000001开始连续扫描属于范围查询可以走索引。第三条LIKE中间包含%无法利用索引树定位基本只能全表扫描更多时候需要考虑全文索引或搜索引擎。我当时答完三条SQL后面试官追加了一句MySQL 8.0的函数索引能解决第一条吗我说能语法类似ALTER TABLE t ADD INDEX idx_created_date ((DATE(created_at)));但我不建议为了这个问题去建函数索引因为范围查询写法对优化器更友好也能兼容老版本函数索引只适合确实无法改写SQL的场景。3.4 为什么“有条件才建索引”比“无脑加索引”更重要面试官总结了一个点索引不是越多越好每一次插入和更新都要维护所有二级索引。低区分度列上的索引空间占用不小写入代价却不小还可能让优化器选择错误。我补充了一个度量指标索引选择性也就是COUNT(DISTINCT col) / COUNT(*)。如果选择性接近1适合当过滤条件如果非常低就要谨慎。在面试里能把这层讲出来会让面试官觉得你有真实调优经验而不是只会背“索引失效的原因”。我后来在自测时遇到慢SQL第一反应永远是三个问题条件列的选择性怎么样查询需要回表吗排序和分组能走索引吗这三个问题基本能覆盖大多数慢查询场景。4. 主从复制延迟从监控指标一路问到并行复制4.1 Seconds_Behind_Master看起来很大未必真是延迟面试官切换到主从场景给了一个监控截图从库的Seconds_Behind_Master数值非常高让我排查。我第一反应是提醒他注意这个指标的局限性。它是由从库SQL线程执行binlog事件的时间戳和当前系统时间对比出来的如果从库系统时间被调过或者主从机器时间不一致这个数值本身就有误导性。更准确的办法是对比主库当前binlog文件名和位置与从库SHOW SLAVE STATUS\G里的相关坐标。要看Master_Log_File和Read_Master_Log_Pos这两个表示IO线程已经读取到主库binlog的哪个位置再看Relay_Log_File和Exec_Master_Log_Pos这表示SQL线程已经回放到哪个位置。如果IO线程坐标落后很多可能是网络或主库本身在刷盘如果IO线程坐标已经追上但Exec位置落后很多那问题就在SQL线程回放。4.2 三步定位进程状态、复制状态、性能监控我当时说了一个排查顺序面试官听了比较认可。第一步看从库SHOW PROCESSLIST关注SQL线程是否处于Waiting for dependent transaction to commit或者System lock状态。这个状态经常意味着有大事务在反复执行SQL线程被卡住。第二步看二进制日志坐标判断是IO延迟还是SQL回放延迟。第三步如果MySQL版本支持查performance_schema里的复制相关表SELECT * FROM performance_schema.replication_applier_status_by_worker;这张表能看出每个并行回放worker的剩余工作量。如果worker积压了很多事务说明并行复制没完全发挥作用通常是有大事务在其中拖慢了整体进度。第三步能问出来说明不是只会读监控面板而是真正接触过复制链路。面试官接着问大事务为什么可怕我举了一个真实场景一条UPDATE一次修改了50万行主库执行可能只花几十秒但binlog里会记录每一行的变更事件从库要一个新的一个应用整个过程非常长。期间这个表上的查询都被拖慢延迟迅速累积。4.3 优化方向并行复制、分批写、DDL工具面试官问最终怎么解决我给出三个方向。第一开启并行复制也就是MTS。对MySQL 5.7以上版本设置slave_parallel_typeLOGICAL_CLOCK并把slave_parallel_workers调成4或8。并行复制对顺序没有要求的事务可以并发回放能明显提升从库吞吐。但要注意不能简单认为worker越多越好每个worker也是独立线程过多时会增加上下文切换和锁竞争。第二避免大事务。写代码时把大规模UPDATE、DELETE拆成批比如每次只更新1000行中间sleep一下。这不一定是为了从库主库的锁和undo膨胀也会更小。第三DDL要小心。大表的DDL在从库执行也会产生延迟甚至卡住复制。更好的方案是使用在线DDL工具比如gh-ost或类似方案通过binlog追数据完成变更尽量减少元数据锁的影响。4.4 一个容易答反的点半同步复制解决的是丢数据不是延迟面试官最后抛了个陷阱那我是不是把主从改成半同步复制延迟就没了这里要分清楚半同步复制是为了减少数据丢失窗口的。它要求主库在提交事务时等到至少一个从库收到binlog并写入relay log才向客户端返回成功。这减少的是主库宕机时binlog还没传到从库的风险并不能保证从库SQL线程已经把事务回放完。收到binlog和回放完是两码事延迟的瓶颈往往在回放侧。我之前确实见过有人把半同步和同步复制搞混。半同步协议里从库只需要ACK“我收到binlog”不需要ACK“我执行完了”。所以半同步解决的是“主库挂了会丢多少数据”不解决“从库读不到最新数据”。能把这一点讲明白面试官会认为你对复制原理有系统性理解。5. 复盘OD技术面MySQL环节的答题节奏与翻车清单5.1 我习惯用的五步回答框架面完这轮之后我给自己总结了一个通用答题框架遇到任何MySQL问题都可以套。第一步先复述场景确认表数据量、隔离级别、MySQL版本这些变量会影响答案。第二步用EXPLAIN或optimizer trace获取客观信息不凭感觉说“应该走索引”。第三步区分是数据量问题、索引问题、锁问题还是从库回放问题不要一上来就说加缓存。第四步给出最小改动方案比如先改SQL写法再考虑加索引或换隔离级别。第五步说出这个方案的局限性和验证方式比如加索引后需要看EXPLAIN和真实耗时而不是只改完就扔。这套框架不一定每次都能答对但能让思路看起来有条理。5.2 这一轮最容易翻车的细节我整理了一个翻车清单都是用血泪换来的。第一把MySQL的RR当成完全解决幻读忽略了当前读场景下依然存在幻读可能。第二看到慢SQL就加索引没有先看列的选择性和执行计划。第三忽略隐式类型转换导致索引白建。第四从库延迟只看Seconds_Behind_Master不会看binlog坐标和并行复制信息。第五把半同步复制当成延迟解决方案概念混淆。还有一个非常容易被问倒的细节为什么ORDER BY created_at DESC LIMIT 20在有个单列索引created_at时有时候反而不如全表扫描因为排序走索引可以减少filesort但回表次数可能很高。这里要回到“覆盖索引”和“选择性”两个关键词。我跟面试官拆解之后他补了一句能说出覆盖索引说明你踩过这类坑。5.3 面完后的自测与巩固这轮面试结束后我搭了一个本地的MySQL环境用几条典型的慢SQL重新做了实验。我用sysbench往测试表里写了20万行数据分别测了status低区分度、created_at范围查询、字符串隐式转换、JOIN字符集不一致这几种场景每一步都打开EXPLAIN和optimizer trace记录数据。这个过程比单纯刷面经有用得多因为很多结论只有亲手验证过才敢在面试时肯定地说出来。我还给自己留了一个小练习写一个事务并发脚本分别验证RR和RC下的不可重复读现象。具体做法是开两个会话事务A先select事务B更新提交事务A再select。在RR下结果不变在RC下结果会变。亲手跑一遍比我背十遍MVCC理论都深刻。如果你也在准备这类面试建议也做一遍这个实验成本很低收获很大。面到最后一轮其实最深的体会不是哪个知识点难而是能不能把一条线串起来。索引、事务、锁、主从复制这些点单独背都能背出来但要应对追问必须在动手实践里真正理解它们的因果关系。把这篇文章里的几道真题逐个跑一遍回头再看那些八股文你会觉得它们不再是孤立的问题。
阅读完成 · 觉得有帮助？
