后端面试题(我经常用的mysql面试题)

后端面试题(我经常用的mysql面试题)
我经常用的mysql面试题

从18年开始带团队之后,面试过的后端开发,没有100个,也至少80个了。

MySQL是必问的。天天打交道的东西,不仅要会用,还要真正理解。

问得多了,我干脆把题目打印出来了。有开发过来面试,就从里面挑几个考察。下面这18个问题,覆盖存储与索引、事务与日志、SQL调优、架构与运维四个方向。能答上来的候选人,MySQL这块我就觉得基本ok了。实际面下来,大部分Java开发答得都一般,能完整答上来的不多。

存储与索引

B+树的结构

这道题是后面聚簇索引、覆盖索引、回表这些概念的基础,答不上来后面的问题好多问题都无法回答的,这块一定要理解。

B+树是一种多叉平衡搜索树。MySQL的InnoDB引擎用它来组织索引数据。和普通二叉树不同,B+树的每个节点可以有多个子节点,树的高度很矮,通常3到4层就能存几千万条数据。树矮意味着从根节点到叶子节点的查找路径短,每经过一层就是一次磁盘IO,3层的B+树查一条数据最多3次IO。

B+树有两个关键特征:所有数据都存在叶子节点上,非叶子节点只存索引键值和指针;叶子节点之间通过双向链表连接。非叶子节点不存数据,这样每个节点能放下更多的索引键值,一个16KB的页能存上千个索引键值,所以树才能那么矮。叶子节点之间的双向链表让范围查询变得高效,找到起始位置后沿着链表往后扫就行,不需要回到上层节点。

面试的时候,我会追问一句:为什么MySQL选B+树而不是B树或者红黑树?B树的每个节点都存数据,导致单个节点能放的索引键值更少,树更高,IO次数更多。红黑树是二叉树,数据量大的时候树的高度太高,查询性能会明显下降。B+树在磁盘IO和范围查询这两件事上都做到了最优平衡,这是MySQL选择它的根本原因。

InnoDB表的数据存在哪

这个问题考的是对InnoDB存储结构的基本认知。

InnoDB用的是聚簇索引,整张表的数据就存在主键索引的B+树叶子节点上。不是单独存一份数据文件,索引和数据是在一起的。

如果建表的时候没有显式指定主键,InnoDB会找一个非空唯一索引来充当聚簇索引。如果连这个也没有,InnoDB会自动生成一个6字节的隐藏rowid作为聚簇索引。

这道题能看出候选人是不是理解InnoDB的存储本质。很多人只知道B+树,但说不清数据到底存在哪。

什么是覆盖索引

查询需要的所有字段,都包含在索引里,不需要再回表去查聚簇索引,这就是覆盖索引。

举个例子,表上有个联合索引 (name, age),你的SQL是 SELECT name, age FROM user WHERE name = 'zhangsan',name和age都在索引里,直接就能拿到结果,不需要再去主键索引里捞完整行数据。

判断方法也简单:用 EXPLAIN 看执行计划,Extra列显示 Using index,就说明走了覆盖索引。

回表是什么意思

回表和覆盖索引是一对概念,放在一起理解比较好。

通过二级索引查到主键值,再拿着主键值到聚簇索引里查完整行数据,这个过程就是回表。

二级索引的叶子节点存的不是完整行数据,而是主键值。如果你查的字段不全在二级索引里,就得拿着主键值再去聚簇索引里查一次。这一次额外的查询就是回表的开销。

覆盖索引的目的就是为了避免回表。 设计索引的时候,把查询需要的字段都加进去,就能避免这次回表操作,查询性能会有明显提升。

Index Merge是什么

这道题相对冷门,能答上来的候选人对索引的理解一般比较深。

Index Merge是指一条SQL同时使用多个单列索引,分别查出主键集合后取交集或并集。 在 EXPLAIN 的输出里,type列会显示 index_merge。

触发条件:WHERE条件里有多个字段,每个字段各自有独立的索引,优化器判断合并多个索引的结果比只走其中一个索引更高效,就会触发Index Merge。

生产环境里,Index Merge的实际效果不一定好。多数情况下,与其依赖Index Merge,不如直接建一个联合索引,效率更稳定。如果 EXPLAIN 里看到了 index_merge,反而值得想一下是不是索引设计有改进空间。

事务与日志

MVCC

MVCC这个概念面试出现频率很高,很多人能说出全称(多版本并发控制),但讲不清楚它的工作机制。

核心就两个东西:undo log版本链和ReadView。

InnoDB里每一行数据都有两个隐藏字段:最近修改这行数据的事务ID,以及一个指向undo log的回滚指针。每次有事务修改这行数据,旧版本就会被写到undo log里,通过回滚指针串成一条版本链。

事务在做快照读(普通SELECT)的时候,会生成一个ReadView,里面记录了当前活跃的事务列表。拿着这个ReadView去版本链上找,找到第一个对当前事务可见的版本,就是这次读的结果。

MVCC让读操作不需要加锁,读和写互不阻塞。 这是InnoDB能在高并发场景下保持不错性能的关键。

MySQL默认的隔离级别,为什么选它

MySQL默认的隔离级别是可重复读(Repeatable Read)。

可重复读和读已提交的区别,用两个并发事务来说明最清楚:

-- 事务ABEGIN;SELECT balance FROM account WHERE id = 1;-- 读到 balance = 100-- 此时事务B执行了:-- UPDATE account SET balance = 200 WHERE id = 1;-- COMMIT;-- 事务A再读一次SELECT balance FROM account WHERE id = 1;-- 可重复读下:还是 100-- 读已提交下:变成了 200

可重复读保证了同一个事务内,多次读同一行数据结果一致,不管其他事务有没有提交新的修改。 很多人能说出默认级别是可重复读,但说不出为什么选它。原因是多方面的: 对大多数业务场景来说,可重复读够用了。 绝大多数业务不需要串行化那么强的一致性保证,可重复读能防止脏读和不可重复读,对于常规的CRUD操作已经足够。同时InnoDB的MVCC实现让可重复读下的读操作不需要加锁,读写互不阻塞,并发性能和读已提交基本没有差别。选可重复读是在数据一致性和并发性能之间取了一个比较好的平衡点。

为什么不建议用长事务

长事务的问题体现在三个地方:

锁占用时间过长。 事务持有的行锁、间隙锁在事务提交之前不会释放。长事务意味着这些锁长时间被占着,其他需要修改相同数据的事务只能排队等待,并发能力直接下降。

undo log无法回收。 MVCC依赖undo log维护历史版本,只要有一个长事务没提交,它启动时刻之后产生的所有undo log都不能被清理。时间一长,回滚段会越来越大,严重时会撑爆磁盘空间。

回滚代价大。 如果一个长事务最终需要回滚,MySQL要把这个事务期间所有的修改逆向执行一遍。事务越长,回滚的数据量越大,耗时可能比正向执行还长,期间数据库的性能会受到明显影响。

三种日志:binlog、redo log、undo log

这三种日志各自负责不同的事情,放在一起对比理解比较清楚。

binlog日志,属于MySQL Server层,所有存储引擎都会写。它记录的是逻辑层面的数据变更(比如这条SQL改了什么),主要用于主从复制和基于时间点的数据恢复。

redo log是重做日志,属于InnoDB引擎层。它记录的是物理层面的数据页变更(比如某个数据页的某个偏移位置改了什么值)。作用是保证crash-safe,也就是数据库异常崩溃后,重启时能通过redo log把已提交但还没刷到磁盘的数据恢复回来。

undo log是回滚日志,也属于InnoDB引擎层。它记录的是数据修改前的旧值。有两个用途:事务回滚的时候,用undo log把数据恢复到修改前的状态;MVCC读取历史版本的时候,也是从undo log的版本链上找。

三者的关系可以这样串联:一条更新语句执行时,InnoDB先写undo log(保存旧值),然后修改数据页并写redo log(保证崩溃恢复),最后Server层写binlog(用于复制和归档)。redo log和binlog之间通过两阶段提交来保证一致性。

日志

所属层

记录内容

核心作用

binlog

Server层

逻辑变更

主从复制、数据恢复

redo log

InnoDB引擎层

物理页变更

崩溃恢复

undo log

InnoDB引擎层

修改前的旧值

事务回滚、MVCC

SQL调优

有索引但不走索引怎么办

实际工作中这种情况不少见。表上明明建了索引,EXPLAIN 一看,没走。

最直接的处理方式是用 FORCE INDEX强制指定索引:

SELECT * FROM orders FORCE INDEX(idx_user_id) WHERE user_id = 100;

优化器不走索引,通常有几个原因:索引的统计信息不准确,导致优化器误判全表扫描更快;或者查询需要回表的行数太多,优化器认为直接扫描全表比回表划算。

遇到这种情况,除了force index/优化sql/优化索引,还可以试试 ANALYZE TABLE 重新收集统计信息,让优化器拿到更准确的数据做判断。force index是治标的手段,更新统计信息是治本的思路。

select … for update的含义和使用场景

SELECT ... FOR UPDATE 执行的是当前读,同时对查出来的行加排他锁。加锁之后,其他事务不能修改这些行,也不能对这些行加共享锁或排他锁,直到当前事务提交或回滚。

典型使用场景是先查后改,中间不允许数据被其他事务修改。比如扣库存:

BEGIN;-- 先锁住这行SELECT stock FROM product WHERE id = 111 FOR UPDATE;-- 业务层判断库存够不够-- 然后更新UPDATE product SET stock = stock - 1 WHERE id = 111;COMMIT;

如果不加 FOR UPDATE,两个并发事务可能同时读到库存为1,都认为够扣,结果库存变成了负数。

需要注意的是,FOR UPDATE 要在事务里使用才有效,自动提交模式下加锁马上就释放了,等于没加。另外,WHERE条件里要走索引,否则会锁全表。

如何分析SQL性能

EXPLAIN 是分析SQL性能的核心工具。 面试时我一般会追问几个关键字段的含义。

重点看这几个字段:

type 表示访问类型,从好到差的排序是:system > const > eq_ref > ref > range > index > ALL。看到 ALL 就是全表扫描,大表上出现这个基本就是慢SQL的源头。能到 ref 或 range 级别,一般就没太大问题。

key 表示实际使用的索引。如果这里是NULL,说明没有用到任何索引。

rows 是优化器预估需要扫描的行数。这个数字越大,查询越慢。

Extra 里面信息量很大。Using index 表示覆盖索引,好事。Using filesort 表示需要额外排序,Using temporary 表示用了临时表,这两个出现的时候就要关注了,通常意味着有优化空间。

高并发下能用join吗

不建议。 这是一个很实际的问题,很多人在低并发环境下用join用得好好的,上了高并发就出问题。

join操作在数据库内部需要做表关联计算,占用CPU和内存。并发低的时候感知不到,并发一上来,每个连接都在做join,数据库的CPU和内存很快就被打满了。一旦出现慢SQL,连接数耗尽,整个服务就卡住了。

高并发场景下的做法是拆成单表查询,在应用层做数据组装。 先根据条件从A表查出一批ID,再拿这批ID去B表查对应的数据,在代码里拼起来。看起来多了一次查询,但每次查询都是简单的单表操作,执行速度快,数据库压力小,整体吞吐量反而更高。

阿里的Java开发规范里也明确要求,超过三张表的join在业务代码里禁止使用,背后就是这个原因。

IN里元素太多怎么处理

实际项目里经常遇到这种情况:根据一批ID去查数据,IN列表里塞了几千甚至上万个值。

IN里的元素太多会导致两个问题:SQL文本过长,解析耗时增加;优化器可能放弃走索引,直接全表扫描。 MySQL对SQL长度有限制(max_allowed_packet),元素太多还可能直接报错。

处理方式有两种:

分批查询。 把大的IN列表拆成多个小批次,每批控制在500到1000个。在代码层做循环,每次查一批,最后合并结果。这是最常用的做法,简单直接。

临时表 + JOIN。 把这批ID先写到一张临时表里,然后用JOIN去关联查询。适合ID数量特别大(几万以上)的场景,比多次分批查询的总耗时更低。MyBatis里可以用批量插入临时表,再做一次JOIN查询来实现。

两种方式选哪个,看数据量。几千个ID以内,分批查询就够了;上万的,临时表JOIN更合适。

如果使用的是mysql 8,也可以使用更加先进的虚拟表方案。

深分页的处理

LIMIT 100000, 10 这种写法在数据量大的时候性能会很差。MySQL的处理方式是先扫描前100010行,然后丢掉前100000行,只返回最后10行。偏移量越大,扫描的数据越多,查询越慢。

正确的做法是用ID分页,也叫游标分页。 每次查询带上上一页最后一条记录的ID作为起始条件:

-- 传统分页,偏移量大时很慢SELECT * FROM orders ORDER BY id LIMIT 100000, 10;-- ID分页,不管第几页都很快SELECT * FROM orders WHERE id > 上一页最后一条的id ORDER BY id LIMIT 10;

ID分页每次都是从索引的某个位置开始往后扫10条,不管翻到第几页,执行时间都是一样的。

这种方式有个前提:分页字段必须有索引,而且数据要能按这个字段有序排列。 主键自增ID天然满足这个条件。如果业务上需要按其他字段排序(比如按时间),那这个字段也需要加索引,并且保证排序方向和索引方向一致。

如果业务场景确实需要跳页(直接跳到第500页),ID分页就不太适用了。这种情况比较少见,可以考虑用Elasticsearch来做分页查询。

架构与运维

什么情况下分库

并发写的压力太大了,一个数据库实例的写入能力到瓶颈了,这时候才考虑分库。

读的问题有很多解决方案:加从库做读写分离、加Redis缓存、上Elasticsearch做搜索分流。这些手段能把读的压力分散出去。

写的问题不一样。MySQL的写操作只能走主库,从库帮不上忙,缓存也帮不上忙。单个MySQL实例的写入TPS是有上限的,到了这个瓶颈,唯一的出路就是分库,把写流量分散到多个数据库实例上。

分库的判断标准不是数据量大不大,而是写并发扛不扛得住。数据量大但写并发不高的场景,分表就够了,不需要分库。

SQL无法优化了怎么办

有时候业务场景确实复杂,查询条件多,表关联多,SQL层面能做的优化都做了,还是慢。

这种情况下的思路是建宽表。把多张表里需要查询的字段,提前聚合到一张宽表里。原来需要join三四张表的查询,现在变成了单表查询,性能直接就上去了。

宽表的维护方式一般有两种:通过binlog监听数据变更,实时同步到宽表;或者定时任务批量刷新。根据业务对实时性的要求来选择。

本质上是用空间换时间,用数据冗余换查询性能。 这不是什么高级技巧,但在实际项目中用得非常多。很多候选人遇到SQL优化的问题,思路只停留在加索引、改SQL这个层面,没有跳出SQL本身去想方案,这就是经验的差距。

线上表加字段怎么处理

这道题能考察候选人有没有实际的线上操作经验。

MySQL8引入了Instant DDL,在表的末尾加字段,只修改数据字典里的元信息,不需要重建表,毫秒级完成,不锁表,不拷贝数据。这个改进对线上操作来说非常实用。

-- MySQL 8,在表末尾加字段ALTER TABLE orders ADD COLUMN remark VARCHAR(255), ALGORITHM=INSTANT;

原理是MySQL8改变了行格式的存储方式,新加的字段在已有行里实际上是不存在的,读取的时候如果发现这一行没有这个字段,就返回默认值。只有后续有数据写入或更新的时候,新字段才会被真正写到行里。所以加字段只需要改元信息,不需要遍历每一行数据。

Instant DDL有限制:MySQL 8.0.12到8.0.28版本,只能在表的最后面加字段,不能在中间插入。8.0.29之后放开了这个限制,支持在任意位置加列。

MySQL8之前的版本,加字段就是一个重量级操作了。MySQL需要创建一个临时表,把旧表的数据全部拷贝过去,再替换。大表这个过程可能要几十分钟甚至更久,期间会锁表。只能选在业务低峰期执行。

速查表

题目

核心答案

B+树的结构

多叉平衡树,数据全在叶子节点,叶子双向链表连接,3层可存千万级数据

InnoDB表数据存在哪

聚簇索引的B+树叶子节点上

覆盖索引

查询字段全在索引里,不用回表

回表

二级索引查到主键,再去聚簇索引查完整行

Index Merge

多个单列索引分别查询后取交集或并集

MVCC

undo log版本链 + ReadView,读写不加锁

默认隔离级别

可重复读,因为早期Statement格式binlog在读已提交下主从会不一致

不建议长事务

锁占用久、undo log膨胀、回滚代价大

三种日志

binlog主从复制、redo log崩溃恢复、undo log回滚和MVCC

有索引不走索引

force index强制指定,或analyze table更新统计信息

for update

当前读+排他锁,先查后改防并发

分析SQL性能

explain,重点看type、key、rows、Extra

高并发下用join

后端面试题(我经常用的mysql面试题)

不建议,拆成单表查询在应用层组装

IN元素太多

分批查询(500-1000一批),或临时表+JOIN

深分页处理

用ID分页(游标分页),不用LIMIT偏移量

什么时候分库

并发写到瓶颈,读的问题用从库和缓存解决

SQL无法优化

建宽表,提前聚合数据,单表查询

线上表加字段

MySQL8用Instant DDL毫秒级完成,老版本低峰期操作

小结

面了这么多人,我发现MySQL相关的问题,能不能答好,和背了多少八股文关系不大。真正答得好的人,是在项目里实际用MySQL解决过问题的。他们回答的时候会自然地带出上下文,比如当时为什么用for update、宽表是怎么维护的、加字段踩过什么坑。背出来的答案和经历过的答案,完全不一样。

面试准备MySQL最有效的方式,不是去刷题库,而是把自己项目里用到MySQL的地方想清楚,每个技术决策背后的原因是什么。 面试官追问的时候,你能接得住,能讲出细节,这就够了。

希望这篇内容可以帮到你。

💻 配套实训环境与动手练习

本文涉及的相关技术指令、开发环境与工具链已内置在边学边练在线实验室中,无需繁琐安装配置,随时在浏览器中实践体验:

文章版权声明:除非注明,否则均为边学边练网络文章,版权归原作者所有

最新文章

热门文章

本栏目文章