聊Java面试数据库八股是无论如何都绕不开的一块硬骨头。前两篇我们把 SQL 基础、索引结构、B 树这些地基过了一遍这篇是数据库篇的收尾重点解决剩下的高频深水区事务隔离级别、MVCC 底层原理、锁机制与死锁实战、索引失效场景、SQL 优化以及建表和数据维护里的各种坑。说实话这块内容是面试官最爱追问的也是候选人最容易答到一半就卡壳的地方。这篇内容适合三类人准备 Java 后端面试的候选人想系统补数据库并发控制知识点的开发以及被线上死锁、慢 SQL、唯一索引冲突折磨过的运维和 CRUD 工程师。文章会以面试追问的形式展开配合真实案例和排查思路尽量做到既能背、又能用。1. 事务隔离级别与 MVCC并发控制的基础盘1.1 四种隔离级别别只背名字要能讲清背后的并发问题面试官问MySQL 默认隔离级别是什么大部分人都能答出 RRRepeatable Read可重复读但再往下追问为什么默认 RR、Oracle 为什么默认 RC就沉默了。这里我给你一个能打满分的回答链路。先说四个隔离级别本身读未提交Read Uncommitted事务可以读到其他事务未提交的数据会出现脏读。基本没人会用除非你对一致性完全没要求。读已提交Read Committed只能读到已提交的数据解决了脏读但同一个事务里两次 SELECT 可能读到不同的结果也就是不可重复读。可重复读Repeatable Read同一个事务里多次读取同一行数据结果保持一致。解决了不可重复读但理论上还存在幻读——同一个查询条件两次执行返回的行数不一样。串行化Serializable事务完全串行执行读写互相阻塞性能最差但所有并发问题都解决了。把脏读、不可重复读、幻读放进一张表里面试时直接说这层对应关系就行隔离级别脏读不可重复读幻读读未提交可能可能可能读已提交不会可能可能可重复读不会不会可能InnoDB 实际基本解决串行化不会不会不会关键点来了MySQL 的 RR 隔离级别下InnoDB 通过 MVCC 加 Next-Key Lock记录锁间隙锁实际上把幻读也解决了所以 MySQL 的 RR 和标准 SQL 定义的 RR 不完全是一回事。这也是很多面试官喜欢挖的一个细节。那为什么 MySQL 默认用 RR而 Oracle 默认用 RC讲白了是两个数据库对复制机制的取舍不同。MySQL 早期主从复制基于 statement逻辑 SQL格式的 binlog如果隔离级别是 RC一个事务里两次查询结果可能不同从库回放时无法保证数据一致。而 RR 下同一事务内的读是快照读结果稳定从库能正确回放。Oracle 没有这个历史包袱直接选择了并发性能更好的 RC。现在 MySQL 用 row 格式的 binlog 也能在 RC 下保持一致性了但默认值一直没有改。1.2 MVCC 到底是怎么工作的undo log 版本链与 ReadViewMVCCMulti-Version Concurrency Control多版本并发控制是 InnoDB 实现高并发读的核心机制核心思想就是读不加锁读写不冲突。这句话背下来容易但面试官一定接着问那 MVCC 具体怎么做到的呢。InnoDB 里每行记录其实有隐藏列关键的两个DB_TRX_ID最近一次修改该行记录的事务 ID。DB_ROLL_PTR回滚指针指向 undo log 中该行记录上一个版本的位置。每次 UPDATE 操作InnoDB 不会直接覆盖旧值而是把旧值写入 undo log然后通过回滚指针串成一个版本链。比如一行记录被三个事务依次改过版本链就是记录当前值 (trx_id103) - 版本2 (trx_id102) - 版本1 (trx_id101) - 原始值查询的时候需要通过 ReadView读视图来判断版本链里哪个版本对当前事务可见。ReadView 里主要维护四个东西m_ids生成 ReadView 时当前活跃未提交的事务 ID 列表。min_trx_idm_ids 中的最小值。max_trx_id下一个待分配的事务 ID也就是当前系统最大事务 ID 1。creator_trx_id生成这个 ReadView 的事务自己的 ID。判断规则是如果某版本记录的 trx_id 小于 min_trx_id说明该事务在 ReadView 生成之前就提交了可见。如果 trx_id 大于等于 max_trx_id说明是 ReadView 生成之后才开启的事务不可见。如果 trx_id 在 min_trx_id 和 max_trx_id 之间并且存在于 m_ids 活跃列表里说明事务还没提交不可见不在活跃列表里说明已提交可见。如果 trx_id 等于 creator_trx_id是自己修改的可见。这一套规则不难理解真正难的是 RC 和 RR 下 ReadView 生成时机的差异。这也是面试中最容易踩坑的点RC 隔离级别下每次执行 SELECT 都会生成一个新的 ReadView所以前后两次查询可能因为其他事务提交而看到不同版本的数据这就是不可重复读的根源。RR 隔离级别下只有第一次执行 SELECT 时生成 ReadView之后整个事务内都复用这个 ReadView所以后续查询永远基于同一个快照数据自然就是可重复的。这个快照复用的机制能解释很多现象。比如面试官常问的RR 下用普通 SELECT 查一条数据另一个事务插入了一条满足条件的新记录再提交再查一次还是查不到这就是快照读在起作用不会出现幻读。但如果事务里先做了 UPDATE 或者 SELECT ... FOR UPDATE就会走当前读。1.3 当前读与快照读一个容易被忽略的差异快照读就是不加锁的普通 SELECT读的是版本链中可见的历史版本不会阻塞其他事务。而当前读读取的是记录的最新版本并且会对读取的记录加锁。什么时候会触发当前读SELECT ... FOR UPDATE加排他锁SELECT ... LOCK IN SHARE MODE加共享锁UPDATE、DELETE、INSERT 语句执行时内部也会走当前读。这里有个很容易被忽视的问题在 RR 隔离级别下普通 SELECT 是快照读理论上不会出现幻读但如果你先用 FOR UPDATE 查询再执行普通 SELECT结果可能不一致因为 FOR UPDATE 拿到的是最新数据。所以真正要防幻读RR 级别下得靠当前读加 Next-Key Lock把查询范围锁住阻止其他事务插入新记录。理解了当前读和快照读的区别才能继续往下聊锁。面试官经常就是这么一环扣一环地追问你一旦在某层断了就只能尴尬收场。2. 锁机制与死锁实战面试深水区2.1 InnoDB 锁的类型与兼容关系锁这部分我建议你把知识分为两层粒度层和行锁实现层。粒度层分为表锁和行锁中间还有意向锁。表锁会锁住整张表行锁只锁住命中的索引记录。InnoDB 支持行锁但它有个前提行锁是加在索引上的不是直接加在物理行上的。这一点非常关键后面讲死锁案例时会再次提到。意向锁是表级别的锁分为意向共享锁IS和意向排他锁IX。它的作用就是告诉其他事务这张表里已经有行被加锁了这样其他事务想加表锁时不用一行行扫描判断有没有行锁冲突直接看意向锁就行提升加表锁的效率。意向锁之间互相兼容但意向锁和对应的表级锁是冲突的。行锁层面InnoDB 有三种实现记录锁Record Lock锁住单条索引记录。间隙锁Gap Lock锁住一个区间但不锁具体记录作用是阻止其他事务在区间内插入新记录解决幻读。Next-Key Lock记录锁和间隙锁的组合既锁住记录又锁住记录前面的间隙范围是一个左开右闭区间。知道了 Next-Key Lock就能解释 RR 级别下 InnoDB 是怎么防幻读的事务执行当前读的时候会对扫描范围内所有命中的索引记录加 Next-Key Lock其他事务想在间隙里插入数据会被间隙锁挡住。而 RC 隔离级别下因为没有间隙锁幻读问题就依然存在。这里给一个锁兼容矩阵方便记忆锁类型S共享锁X排他锁ISIXS兼容冲突兼容冲突X冲突冲突冲突冲突IS兼容冲突兼容兼容IX冲突冲突兼容兼容面试时把这个矩阵口述出来基本就能证明你是真懂锁而不是背概念。需要强调一点只有在 RR 级别下InnoDB 才默认使用间隙锁和 Next-Key LockRC 级别下只有记录锁。2.2 一个真实死锁案例的完整复盘死锁是数据库面试里出镜率极高的话题但很多人只会背死锁的四个必要条件一让分析具体案例就露馅。我先给个面试标准答案框架死锁发生的四个条件是互斥、持有并等待、不可剥夺、循环等待。InnoDB 会通过等待图主动检测死锁检测到后回滚代价较小的一方事务并且抛出Deadlock found when trying to get lock的错误。然后是我遇到的一个真实线上案例典型的两个事务交叉更新导致的死锁。场景是订单表 order主键 id。业务上有两个任务并发处理订单任务 A 先更新 id1 的订单再更新 id2 的订单任务 B 正好相反先更新 id2再更新 id1。两个任务并发执行时时间线如下事务T1: UPDATE order SET status1 WHERE id1; -- 持有 id1 的记录锁 事务T2: UPDATE order SET status1 WHERE id2; -- 持有 id2 的记录锁 事务T1: UPDATE order SET status2 WHERE id2; -- 等待 T2 释放 id2 的锁 事务T2: UPDATE order SET status2 WHERE id1; -- 等待 T1 释放 id1 的锁循环等待形成死锁日志怎么查执行 SHOW ENGINE INNODB STATUS\G在输出的 LATEST DETECTED DEADLOCK 段落里能看到完整信息。重点关注这几块涉及事务的 ID、每个事务当前持有锁和等待锁的索引记录、哪个事务被回滚。日志里会显示类似这样的信息*** (1) TRANSACTION: TRANSACTION 2589, ACTIVE 0 sec *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... index PRIMARY of table test.order ... *** (2) TRANSACTION: TRANSACTION 2590, ACTIVE 0 sec *** (2) HOLDS THE LOCK(S): RECORD LOCKS ... index PRIMARY of table test.order ... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... index PRIMARY of table test.order ... *** WE ROLL BACK TRANSACTION (2)排查的思路是先根据日志确认是哪两条 SQL、以什么顺序加锁然后回到业务代码里看并发场景最后从三个方向解决所有事务统一按相同顺序访问资源比如都先更新 id 小的记录再更新 id 大的。缩小事务范围减少持锁时间比如把无关查询和远程调用移出事务。控制并发度比如引入分布式锁从源头避免两个任务同时操作同一批订单。这套方法论在面试里可以直接用先复现再看日志最后给方案。比单纯背诵死锁条件强得多。2.3 无索引更新导致的行锁升级成表锁这是另一个高频踩坑场景也是面试官常拿来考察行锁基于索引这个知识点的变体。假设 user 表里 email 字段没有索引执行UPDATE user SET name 张三 WHERE email zhangsanexample.com;InnoDB 需要扫描全表才能找到满足条件的记录而扫描过程中访问到的每一行都会加锁。实际效果就是整个表的记录都被上锁了其他事务的更新和插入全部被阻塞数据库连接池被积压耗尽线上直接报警。这类事故的预防手段很简单所有 UPDATE 和 DELETE 语句的 WHERE 条件里必须确保用到了索引。排查的时候用 EXPLAIN 看一眼 type 是否为 ALL是否为 range/ref/const 级别的。生产环境我甚至建议把 UPDATE 和 DELETE 语句纳入代码评审没有索引条件直接打回。3. 索引失效与 SQL 优化八股文的落地版3.1 索引失效的常见场景不只是背口诀索引失效是 Java 面试八股文里经常出现的高频考点。但很多候选人只会背函数、模糊、OR这几个关键词一旦面试官换个场景追问原因就答不上来。我给你整理一份能当速查表的清单每一行都说明原理和解决方案。失效场景示例 SQL原因解决方案对索引列使用函数WHERE DATE(create_time) 2024-01-01函数处理后索引树无法直接查找改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02隐式类型转换WHERE phone 13812341234phone 是 varchar字符串和数字比较时发生类型转换导致索引列上被应用了 CAST 函数参数传字符串WHERE phone 13812341234前置模糊匹配WHERE name LIKE %张B 树无法从中间开始匹配改用后缀匹配或引入全文索引 / ESOR 连接非索引条件WHERE name 张 OR age 20优化器无法确定走 name 索引后能过滤掉所有数据需要全表扫描拆成两个查询用 UNION 合并联合索引不满足最左前缀索引 (a,b,c)WHERE b 1跳过 a 直接查 b无法利用索引的有序性调整索引顺序或补上 a 条件索引列参与运算WHERE id 1 100运算导致索引列值发生变化无法定位把运算移到等号右侧使用 NOT IN、WHERE status NOT IN (0, 1)优化器认为范围扫描代价高用 EXISTS 或 NOT EXISTS 改写字符集不一致表 A 用 utf8mb4表 B 用 utf8关联查询隐式字符集转换导致索引列上发生函数操作统一字符集注意一点不是所有加了函数的 SQL 都一定失效。比如索引列是 create_time写成 WHERE create_time 2024-01-01 AND create_time 2024-02-01 就是范围查询完全能走索引。所以判断标准不是有没有函数而是索引列本身还能不能保持有序性。3.2 EXPLAIN 执行计划精读type 和 Extra 最关键很多开发基础还不错但一被问这条 SQL 为什么慢就只会说我加了索引啊。加没加对走了没走得用 EXPLAIN 验证。这里我给一个高频字段速查。type 字段是查询访问类型从好到差大概是system const eq_ref ref range index ALLconst根据主键或者唯一二级索引等值查询最多返回一条记录。eq_ref被驱动表通过主键或唯一索引等值匹配多表 JOIN 时最优。ref通过普通二级索引等值查询。range索引范围扫描比如 BETWEEN、IN、、。index全索引扫描遍历整个索引树比全表扫好一点。ALL全表扫描必须优化。Extra 字段最容易暴露问题看到这些关键词要拉响警报Using filesort需要额外的排序操作。如果 ORDER BY 的字段没有索引MySQL 会把数据查出来再排序数据量大时特别吃内存和磁盘。Using temporary用到了临时表。常见于 GROUP BY、DISTINCT 或者 ORDER BY 和 GROUP BY 字段不一致时。Using index覆盖索引查询列全部包含在索引里不需要回表是最理想的情况。Using index condition走了索引下推ICP存储引擎层先用索引过滤再回表也是比较好的信号。Using where存储引擎层返回后Server 层再做条件过滤不算严重问题但要结合 type 一起看。举个实际案例假设有订单表 order包含 id、order_no、user_id、amount、status、create_time 字段查询最近一年的订单按金额倒序取 10 条SELECT * FROM order WHERE create_time 2024-01-01 ORDER BY amount DESC LIMIT 10;EXPLAIN 的结果大概率会看到 typerangecreate_time 走了索引但 Extra 里出现 Using filesort因为 amount 没有索引排序只能临时做。解决方案有三种给 (create_time, amount) 建联合索引或者把 ORDER BY amount 改成按主键排序再在内存里排序或者业务上接受重新设计排序维度。3.3 深翻页优化LIMIT 1000000 为什么会慢分页查询是日常开发里最简单也最容易写崩的 SQL 之一。只要数据超过几十万行下面的写法就会明显变慢SELECT * FROM user ORDER BY id LIMIT 1000000, 10;原因不难理解MySQL 会先扫描出前 1000010 行然后丢弃前 1000000 行最后只返回 10 行。前面的 100 万行虽然不返回但每一行都要回表去读取完整行记录IO 代价全花在被丢弃的数据上了。偏移量越大扫描和回表的数据越多性能直线下降。常用的优化方案是延迟关联也叫子查询分页。先通过覆盖索引只查主键再用主键关联回原表取完整数据SELECT t.* FROM user t INNER JOIN ( SELECT id FROM user ORDER BY id LIMIT 1000000, 10 ) AS tmp ON t.id tmp.id;子查询里 SELECT 的只有 id可以直接走二级索引或覆盖索引不需要回表扫描大量数据瓶颈就被绕开了。如果业务允许更推荐基于游标的分页也就是把 LIMIT 偏移量换成条件过滤SELECT * FROM user WHERE id 1000000 ORDER BY id LIMIT 10;这种写法每次只扫描从主键断点开始的 10 行性能稳定不会随着页数增加而恶化。但它的限制是只能按顺序翻页不能跳页因此适用于下拉加载更多这类场景不适合需要点击第 N 页的传统分页。项目里选哪种要看产品交互不是所有情况都适合游标分页。4. 建表设计与数据维护避坑从一道实操题说起4.1 唯一约束冲突已有重复数据时如何加唯一索引很多人在生产环境遇到过这个报错。用户表 email 字段历史数据就有一堆重复某天业务要求给 email 加唯一索引直接执行ALTER TABLE user ADD UNIQUE INDEX uk_email (email);结果报错Duplicate entry xxx for key user.uk_email。MySQL 要求创建唯一索引前字段里不能有重复数据。处理分两步。先查重复数据SELECT email, COUNT(*) AS cnt FROM user GROUP BY email HAVING COUNT(*) 1;然后根据业务规则决定怎么处理重复记录。保留策略通常有三种保留主键最小的一条删除其他重复记录。按业务维度合并比如把重复账号的订单、积分合并到保留账号上。确认为无效的数据直接物理删除或软删除。比如保留最小 id 的删除方式DELETE FROM user WHERE id NOT IN ( SELECT MIN(id) FROM user GROUP BY email );注意 MySQL 的 UPDATE 和 DELETE 语句不能直接对正在操作的表做子查询需要用临时表绕一层。这是很多人现场写 SQL 时容易卡住的点。数据清理干净后再加索引。这里提醒一句生产环境大表加索引不要直接 ALTER虽然 MySQL 8.0 支持 Online DDL但在大表上执行还是会占用额外空间、产生日志压力且可能阻塞 DDL。稳妥的做法是用 pt-online-schema-change 或者 gh-ost 这类在线变更工具在业务低峰期操作并且先备份。这个经验不太会出现在八股文里但线上踩一次坑就长记性了。4.2 增删改查的规范与防坑热搜词里出现了数据库增删改查看着基础但很多线上事故恰恰是基础操作没守住底线。INSERT 方向批量插入用一条 INSERT 语句拼接多组 VALUES而不是在循环里一条条执行。前者只发起一次网络往返后者会产生大量日志和事务开销。如果插入的数据特别多还要注意分批比如每批 500 到 1000 条避免单事务过大导致锁持有时间过长和 undo 日志膨胀。UPDATE 和 DELETE 方向必须带 WHERE这是铁律。很多开发在测试环境写习惯了一条 UPDATE 忘带条件直接清了整张表只能从备份恢复。线上大批量更新也要分批用 LIMIT 循环执行每批小事务提交避免长时间持锁影响业务。SELECT 方向只查需要的列别一上来就 SELECT *。尤其在覆盖索引的场景下SELECT * 会让覆盖索引直接失效因为索引里没有的所有列都得回表去取。分页查询加上合理的排序字段和索引避免无谓的全表扫描。还有 SQL 注入的问题。Java 里直接拼接字符串构造 SQL 是绝对禁止的必须用 PreparedStatement 的占位符传参。这不是面试题里的花架子而是真实的安全漏洞。MyBatis 中也要注意能用 #{} 就不要用 ${}${} 的本质是字符串拼接一旦参数被注入恶意 SQL后果不堪设想。4.3 数据库同步与迁移工具选型热搜词里数据库同步软件数据库同步工具出现频率很高。这块在真实项目里几乎避不开从 MySQL 同步数据到 Elasticsearch、数仓或者做多环境数据迁移。Java 后端面试问到项目经验时这里往往是个加分点。最常见的一套组合是 Canal 加 DataX。Canal 把自己伪装成 MySQL 的从库订阅 binlog解析出增量变更事件再转发给下游消费者。DataX 是做全量离线同步的它能从 MySQL 或其他数据源读取全量数据写入目标库。生产环境里通常先用 DataX 同步存量数据再用 Canal 同步增量两条链路配合完成全量增量的完整同步。商业工具方面CloudCanal 和 NineData 这类可视化工具也做得比较成熟支持 Oracle、MySQL、PostgreSQL、达梦等数据源。团队人力不足时直接上这类工具效率很高省去自研和运维的成本。需要特别注意的是国产数据库的适配问题。热搜词里的达梦、人大金仓、华为 GaussDB在语法和协议层面和 MySQL、Oracle 并不完全一致。如果你的项目用了这些数据库同步工具和 ORM 的兼容性一定要提前验证。比如达梦对 Oracle 语法兼容较好人大金仓对 PostgreSQL 兼容性更接近选工具时要先确认它们是不是在官方支持列表里否则到了上线前才发现同步链路不通排期就炸了。5. 最后分享一点我个人的准备经验数据库这块是 Java 面试里最需要讲出原理感的部分只背结论是撑不住追问的。我建议准备时抓住一条主线事务隔离级别是怎么实现的从隔离级别讲到 MVCC从 MVCC 讲到当前读与快照读从当前读讲到锁从锁讲到死锁再从锁回到索引优化。整条链路能自洽地讲下来数据库面试基本不用担心。实操层面多去线上环境看看 SHOW ENGINE INNODB STATUS 里的死锁日志多跑几次 EXPLAIN 观察 type 和 Extra 的变化比刷 100 道面试题都管用。数据库没有银弹所有优化都靠理解原理 实测验证这两件事。
聊Java面试数据库八股是无论如何都绕不开的一块硬骨头。前两篇我们把 SQL 基础、索引结构、B 树这些地基过了一遍这篇是数据库篇的收尾重点解决剩下的高频深水区事务隔离级别、MVCC 底层原理、锁机制与死锁实战、索引失效场景、SQL 优化以及建表和数据维护里的各种坑。说实话这块内容是面试官最爱追问的也是候选人最容易答到一半就卡壳的地方。这篇内容适合三类人准备 Java 后端面试的候选人想系统补数据库并发控制知识点的开发以及被线上死锁、慢 SQL、唯一索引冲突折磨过的运维和 CRUD 工程师。文章会以面试追问的形式展开配合真实案例和排查思路尽量做到既能背、又能用。1. 事务隔离级别与 MVCC并发控制的基础盘1.1 四种隔离级别别只背名字要能讲清背后的并发问题面试官问MySQL 默认隔离级别是什么大部分人都能答出 RRRepeatable Read可重复读但再往下追问为什么默认 RR、Oracle 为什么默认 RC就沉默了。这里我给你一个能打满分的回答链路。先说四个隔离级别本身读未提交Read Uncommitted事务可以读到其他事务未提交的数据会出现脏读。基本没人会用除非你对一致性完全没要求。读已提交Read Committed只能读到已提交的数据解决了脏读但同一个事务里两次 SELECT 可能读到不同的结果也就是不可重复读。可重复读Repeatable Read同一个事务里多次读取同一行数据结果保持一致。解决了不可重复读但理论上还存在幻读——同一个查询条件两次执行返回的行数不一样。串行化Serializable事务完全串行执行读写互相阻塞性能最差但所有并发问题都解决了。把脏读、不可重复读、幻读放进一张表里面试时直接说这层对应关系就行隔离级别脏读不可重复读幻读读未提交可能可能可能读已提交不会可能可能可重复读不会不会可能InnoDB 实际基本解决串行化不会不会不会关键点来了MySQL 的 RR 隔离级别下InnoDB 通过 MVCC 加 Next-Key Lock记录锁间隙锁实际上把幻读也解决了所以 MySQL 的 RR 和标准 SQL 定义的 RR 不完全是一回事。这也是很多面试官喜欢挖的一个细节。那为什么 MySQL 默认用 RR而 Oracle 默认用 RC讲白了是两个数据库对复制机制的取舍不同。MySQL 早期主从复制基于 statement逻辑 SQL格式的 binlog如果隔离级别是 RC一个事务里两次查询结果可能不同从库回放时无法保证数据一致。而 RR 下同一事务内的读是快照读结果稳定从库能正确回放。Oracle 没有这个历史包袱直接选择了并发性能更好的 RC。现在 MySQL 用 row 格式的 binlog 也能在 RC 下保持一致性了但默认值一直没有改。1.2 MVCC 到底是怎么工作的undo log 版本链与 ReadViewMVCCMulti-Version Concurrency Control多版本并发控制是 InnoDB 实现高并发读的核心机制核心思想就是读不加锁读写不冲突。这句话背下来容易但面试官一定接着问那 MVCC 具体怎么做到的呢。InnoDB 里每行记录其实有隐藏列关键的两个DB_TRX_ID最近一次修改该行记录的事务 ID。DB_ROLL_PTR回滚指针指向 undo log 中该行记录上一个版本的位置。每次 UPDATE 操作InnoDB 不会直接覆盖旧值而是把旧值写入 undo log然后通过回滚指针串成一个版本链。比如一行记录被三个事务依次改过版本链就是记录当前值 (trx_id103) - 版本2 (trx_id102) - 版本1 (trx_id101) - 原始值查询的时候需要通过 ReadView读视图来判断版本链里哪个版本对当前事务可见。ReadView 里主要维护四个东西m_ids生成 ReadView 时当前活跃未提交的事务 ID 列表。min_trx_idm_ids 中的最小值。max_trx_id下一个待分配的事务 ID也就是当前系统最大事务 ID 1。creator_trx_id生成这个 ReadView 的事务自己的 ID。判断规则是如果某版本记录的 trx_id 小于 min_trx_id说明该事务在 ReadView 生成之前就提交了可见。如果 trx_id 大于等于 max_trx_id说明是 ReadView 生成之后才开启的事务不可见。如果 trx_id 在 min_trx_id 和 max_trx_id 之间并且存在于 m_ids 活跃列表里说明事务还没提交不可见不在活跃列表里说明已提交可见。如果 trx_id 等于 creator_trx_id是自己修改的可见。这一套规则不难理解真正难的是 RC 和 RR 下 ReadView 生成时机的差异。这也是面试中最容易踩坑的点RC 隔离级别下每次执行 SELECT 都会生成一个新的 ReadView所以前后两次查询可能因为其他事务提交而看到不同版本的数据这就是不可重复读的根源。RR 隔离级别下只有第一次执行 SELECT 时生成 ReadView之后整个事务内都复用这个 ReadView所以后续查询永远基于同一个快照数据自然就是可重复的。这个快照复用的机制能解释很多现象。比如面试官常问的RR 下用普通 SELECT 查一条数据另一个事务插入了一条满足条件的新记录再提交再查一次还是查不到这就是快照读在起作用不会出现幻读。但如果事务里先做了 UPDATE 或者 SELECT ... FOR UPDATE就会走当前读。1.3 当前读与快照读一个容易被忽略的差异快照读就是不加锁的普通 SELECT读的是版本链中可见的历史版本不会阻塞其他事务。而当前读读取的是记录的最新版本并且会对读取的记录加锁。什么时候会触发当前读SELECT ... FOR UPDATE加排他锁SELECT ... LOCK IN SHARE MODE加共享锁UPDATE、DELETE、INSERT 语句执行时内部也会走当前读。这里有个很容易被忽视的问题在 RR 隔离级别下普通 SELECT 是快照读理论上不会出现幻读但如果你先用 FOR UPDATE 查询再执行普通 SELECT结果可能不一致因为 FOR UPDATE 拿到的是最新数据。所以真正要防幻读RR 级别下得靠当前读加 Next-Key Lock把查询范围锁住阻止其他事务插入新记录。理解了当前读和快照读的区别才能继续往下聊锁。面试官经常就是这么一环扣一环地追问你一旦在某层断了就只能尴尬收场。2. 锁机制与死锁实战面试深水区2.1 InnoDB 锁的类型与兼容关系锁这部分我建议你把知识分为两层粒度层和行锁实现层。粒度层分为表锁和行锁中间还有意向锁。表锁会锁住整张表行锁只锁住命中的索引记录。InnoDB 支持行锁但它有个前提行锁是加在索引上的不是直接加在物理行上的。这一点非常关键后面讲死锁案例时会再次提到。意向锁是表级别的锁分为意向共享锁IS和意向排他锁IX。它的作用就是告诉其他事务这张表里已经有行被加锁了这样其他事务想加表锁时不用一行行扫描判断有没有行锁冲突直接看意向锁就行提升加表锁的效率。意向锁之间互相兼容但意向锁和对应的表级锁是冲突的。行锁层面InnoDB 有三种实现记录锁Record Lock锁住单条索引记录。间隙锁Gap Lock锁住一个区间但不锁具体记录作用是阻止其他事务在区间内插入新记录解决幻读。Next-Key Lock记录锁和间隙锁的组合既锁住记录又锁住记录前面的间隙范围是一个左开右闭区间。知道了 Next-Key Lock就能解释 RR 级别下 InnoDB 是怎么防幻读的事务执行当前读的时候会对扫描范围内所有命中的索引记录加 Next-Key Lock其他事务想在间隙里插入数据会被间隙锁挡住。而 RC 隔离级别下因为没有间隙锁幻读问题就依然存在。这里给一个锁兼容矩阵方便记忆锁类型S共享锁X排他锁ISIXS兼容冲突兼容冲突X冲突冲突冲突冲突IS兼容冲突兼容兼容IX冲突冲突兼容兼容面试时把这个矩阵口述出来基本就能证明你是真懂锁而不是背概念。需要强调一点只有在 RR 级别下InnoDB 才默认使用间隙锁和 Next-Key LockRC 级别下只有记录锁。2.2 一个真实死锁案例的完整复盘死锁是数据库面试里出镜率极高的话题但很多人只会背死锁的四个必要条件一让分析具体案例就露馅。我先给个面试标准答案框架死锁发生的四个条件是互斥、持有并等待、不可剥夺、循环等待。InnoDB 会通过等待图主动检测死锁检测到后回滚代价较小的一方事务并且抛出Deadlock found when trying to get lock的错误。然后是我遇到的一个真实线上案例典型的两个事务交叉更新导致的死锁。场景是订单表 order主键 id。业务上有两个任务并发处理订单任务 A 先更新 id1 的订单再更新 id2 的订单任务 B 正好相反先更新 id2再更新 id1。两个任务并发执行时时间线如下事务T1: UPDATE order SET status1 WHERE id1; -- 持有 id1 的记录锁 事务T2: UPDATE order SET status1 WHERE id2; -- 持有 id2 的记录锁 事务T1: UPDATE order SET status2 WHERE id2; -- 等待 T2 释放 id2 的锁 事务T2: UPDATE order SET status2 WHERE id1; -- 等待 T1 释放 id1 的锁循环等待形成死锁日志怎么查执行 SHOW ENGINE INNODB STATUS\G在输出的 LATEST DETECTED DEADLOCK 段落里能看到完整信息。重点关注这几块涉及事务的 ID、每个事务当前持有锁和等待锁的索引记录、哪个事务被回滚。日志里会显示类似这样的信息*** (1) TRANSACTION: TRANSACTION 2589, ACTIVE 0 sec *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... index PRIMARY of table test.order ... *** (2) TRANSACTION: TRANSACTION 2590, ACTIVE 0 sec *** (2) HOLDS THE LOCK(S): RECORD LOCKS ... index PRIMARY of table test.order ... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS ... index PRIMARY of table test.order ... *** WE ROLL BACK TRANSACTION (2)排查的思路是先根据日志确认是哪两条 SQL、以什么顺序加锁然后回到业务代码里看并发场景最后从三个方向解决所有事务统一按相同顺序访问资源比如都先更新 id 小的记录再更新 id 大的。缩小事务范围减少持锁时间比如把无关查询和远程调用移出事务。控制并发度比如引入分布式锁从源头避免两个任务同时操作同一批订单。这套方法论在面试里可以直接用先复现再看日志最后给方案。比单纯背诵死锁条件强得多。2.3 无索引更新导致的行锁升级成表锁这是另一个高频踩坑场景也是面试官常拿来考察行锁基于索引这个知识点的变体。假设 user 表里 email 字段没有索引执行UPDATE user SET name 张三 WHERE email zhangsanexample.com;InnoDB 需要扫描全表才能找到满足条件的记录而扫描过程中访问到的每一行都会加锁。实际效果就是整个表的记录都被上锁了其他事务的更新和插入全部被阻塞数据库连接池被积压耗尽线上直接报警。这类事故的预防手段很简单所有 UPDATE 和 DELETE 语句的 WHERE 条件里必须确保用到了索引。排查的时候用 EXPLAIN 看一眼 type 是否为 ALL是否为 range/ref/const 级别的。生产环境我甚至建议把 UPDATE 和 DELETE 语句纳入代码评审没有索引条件直接打回。3. 索引失效与 SQL 优化八股文的落地版3.1 索引失效的常见场景不只是背口诀索引失效是 Java 面试八股文里经常出现的高频考点。但很多候选人只会背函数、模糊、OR这几个关键词一旦面试官换个场景追问原因就答不上来。我给你整理一份能当速查表的清单每一行都说明原理和解决方案。失效场景示例 SQL原因解决方案对索引列使用函数WHERE DATE(create_time) 2024-01-01函数处理后索引树无法直接查找改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02隐式类型转换WHERE phone 13812341234phone 是 varchar字符串和数字比较时发生类型转换导致索引列上被应用了 CAST 函数参数传字符串WHERE phone 13812341234前置模糊匹配WHERE name LIKE %张B 树无法从中间开始匹配改用后缀匹配或引入全文索引 / ESOR 连接非索引条件WHERE name 张 OR age 20优化器无法确定走 name 索引后能过滤掉所有数据需要全表扫描拆成两个查询用 UNION 合并联合索引不满足最左前缀索引 (a,b,c)WHERE b 1跳过 a 直接查 b无法利用索引的有序性调整索引顺序或补上 a 条件索引列参与运算WHERE id 1 100运算导致索引列值发生变化无法定位把运算移到等号右侧使用 NOT IN、WHERE status NOT IN (0, 1)优化器认为范围扫描代价高用 EXISTS 或 NOT EXISTS 改写字符集不一致表 A 用 utf8mb4表 B 用 utf8关联查询隐式字符集转换导致索引列上发生函数操作统一字符集注意一点不是所有加了函数的 SQL 都一定失效。比如索引列是 create_time写成 WHERE create_time 2024-01-01 AND create_time 2024-02-01 就是范围查询完全能走索引。所以判断标准不是有没有函数而是索引列本身还能不能保持有序性。3.2 EXPLAIN 执行计划精读type 和 Extra 最关键很多开发基础还不错但一被问这条 SQL 为什么慢就只会说我加了索引啊。加没加对走了没走得用 EXPLAIN 验证。这里我给一个高频字段速查。type 字段是查询访问类型从好到差大概是system const eq_ref ref range index ALLconst根据主键或者唯一二级索引等值查询最多返回一条记录。eq_ref被驱动表通过主键或唯一索引等值匹配多表 JOIN 时最优。ref通过普通二级索引等值查询。range索引范围扫描比如 BETWEEN、IN、、。index全索引扫描遍历整个索引树比全表扫好一点。ALL全表扫描必须优化。Extra 字段最容易暴露问题看到这些关键词要拉响警报Using filesort需要额外的排序操作。如果 ORDER BY 的字段没有索引MySQL 会把数据查出来再排序数据量大时特别吃内存和磁盘。Using temporary用到了临时表。常见于 GROUP BY、DISTINCT 或者 ORDER BY 和 GROUP BY 字段不一致时。Using index覆盖索引查询列全部包含在索引里不需要回表是最理想的情况。Using index condition走了索引下推ICP存储引擎层先用索引过滤再回表也是比较好的信号。Using where存储引擎层返回后Server 层再做条件过滤不算严重问题但要结合 type 一起看。举个实际案例假设有订单表 order包含 id、order_no、user_id、amount、status、create_time 字段查询最近一年的订单按金额倒序取 10 条SELECT * FROM order WHERE create_time 2024-01-01 ORDER BY amount DESC LIMIT 10;EXPLAIN 的结果大概率会看到 typerangecreate_time 走了索引但 Extra 里出现 Using filesort因为 amount 没有索引排序只能临时做。解决方案有三种给 (create_time, amount) 建联合索引或者把 ORDER BY amount 改成按主键排序再在内存里排序或者业务上接受重新设计排序维度。3.3 深翻页优化LIMIT 1000000 为什么会慢分页查询是日常开发里最简单也最容易写崩的 SQL 之一。只要数据超过几十万行下面的写法就会明显变慢SELECT * FROM user ORDER BY id LIMIT 1000000, 10;原因不难理解MySQL 会先扫描出前 1000010 行然后丢弃前 1000000 行最后只返回 10 行。前面的 100 万行虽然不返回但每一行都要回表去读取完整行记录IO 代价全花在被丢弃的数据上了。偏移量越大扫描和回表的数据越多性能直线下降。常用的优化方案是延迟关联也叫子查询分页。先通过覆盖索引只查主键再用主键关联回原表取完整数据SELECT t.* FROM user t INNER JOIN ( SELECT id FROM user ORDER BY id LIMIT 1000000, 10 ) AS tmp ON t.id tmp.id;子查询里 SELECT 的只有 id可以直接走二级索引或覆盖索引不需要回表扫描大量数据瓶颈就被绕开了。如果业务允许更推荐基于游标的分页也就是把 LIMIT 偏移量换成条件过滤SELECT * FROM user WHERE id 1000000 ORDER BY id LIMIT 10;这种写法每次只扫描从主键断点开始的 10 行性能稳定不会随着页数增加而恶化。但它的限制是只能按顺序翻页不能跳页因此适用于下拉加载更多这类场景不适合需要点击第 N 页的传统分页。项目里选哪种要看产品交互不是所有情况都适合游标分页。4. 建表设计与数据维护避坑从一道实操题说起4.1 唯一约束冲突已有重复数据时如何加唯一索引很多人在生产环境遇到过这个报错。用户表 email 字段历史数据就有一堆重复某天业务要求给 email 加唯一索引直接执行ALTER TABLE user ADD UNIQUE INDEX uk_email (email);结果报错Duplicate entry xxx for key user.uk_email。MySQL 要求创建唯一索引前字段里不能有重复数据。处理分两步。先查重复数据SELECT email, COUNT(*) AS cnt FROM user GROUP BY email HAVING COUNT(*) 1;然后根据业务规则决定怎么处理重复记录。保留策略通常有三种保留主键最小的一条删除其他重复记录。按业务维度合并比如把重复账号的订单、积分合并到保留账号上。确认为无效的数据直接物理删除或软删除。比如保留最小 id 的删除方式DELETE FROM user WHERE id NOT IN ( SELECT MIN(id) FROM user GROUP BY email );注意 MySQL 的 UPDATE 和 DELETE 语句不能直接对正在操作的表做子查询需要用临时表绕一层。这是很多人现场写 SQL 时容易卡住的点。数据清理干净后再加索引。这里提醒一句生产环境大表加索引不要直接 ALTER虽然 MySQL 8.0 支持 Online DDL但在大表上执行还是会占用额外空间、产生日志压力且可能阻塞 DDL。稳妥的做法是用 pt-online-schema-change 或者 gh-ost 这类在线变更工具在业务低峰期操作并且先备份。这个经验不太会出现在八股文里但线上踩一次坑就长记性了。4.2 增删改查的规范与防坑热搜词里出现了数据库增删改查看着基础但很多线上事故恰恰是基础操作没守住底线。INSERT 方向批量插入用一条 INSERT 语句拼接多组 VALUES而不是在循环里一条条执行。前者只发起一次网络往返后者会产生大量日志和事务开销。如果插入的数据特别多还要注意分批比如每批 500 到 1000 条避免单事务过大导致锁持有时间过长和 undo 日志膨胀。UPDATE 和 DELETE 方向必须带 WHERE这是铁律。很多开发在测试环境写习惯了一条 UPDATE 忘带条件直接清了整张表只能从备份恢复。线上大批量更新也要分批用 LIMIT 循环执行每批小事务提交避免长时间持锁影响业务。SELECT 方向只查需要的列别一上来就 SELECT *。尤其在覆盖索引的场景下SELECT * 会让覆盖索引直接失效因为索引里没有的所有列都得回表去取。分页查询加上合理的排序字段和索引避免无谓的全表扫描。还有 SQL 注入的问题。Java 里直接拼接字符串构造 SQL 是绝对禁止的必须用 PreparedStatement 的占位符传参。这不是面试题里的花架子而是真实的安全漏洞。MyBatis 中也要注意能用 #{} 就不要用 ${}${} 的本质是字符串拼接一旦参数被注入恶意 SQL后果不堪设想。4.3 数据库同步与迁移工具选型热搜词里数据库同步软件数据库同步工具出现频率很高。这块在真实项目里几乎避不开从 MySQL 同步数据到 Elasticsearch、数仓或者做多环境数据迁移。Java 后端面试问到项目经验时这里往往是个加分点。最常见的一套组合是 Canal 加 DataX。Canal 把自己伪装成 MySQL 的从库订阅 binlog解析出增量变更事件再转发给下游消费者。DataX 是做全量离线同步的它能从 MySQL 或其他数据源读取全量数据写入目标库。生产环境里通常先用 DataX 同步存量数据再用 Canal 同步增量两条链路配合完成全量增量的完整同步。商业工具方面CloudCanal 和 NineData 这类可视化工具也做得比较成熟支持 Oracle、MySQL、PostgreSQL、达梦等数据源。团队人力不足时直接上这类工具效率很高省去自研和运维的成本。需要特别注意的是国产数据库的适配问题。热搜词里的达梦、人大金仓、华为 GaussDB在语法和协议层面和 MySQL、Oracle 并不完全一致。如果你的项目用了这些数据库同步工具和 ORM 的兼容性一定要提前验证。比如达梦对 Oracle 语法兼容较好人大金仓对 PostgreSQL 兼容性更接近选工具时要先确认它们是不是在官方支持列表里否则到了上线前才发现同步链路不通排期就炸了。5. 最后分享一点我个人的准备经验数据库这块是 Java 面试里最需要讲出原理感的部分只背结论是撑不住追问的。我建议准备时抓住一条主线事务隔离级别是怎么实现的从隔离级别讲到 MVCC从 MVCC 讲到当前读与快照读从当前读讲到锁从锁讲到死锁再从锁回到索引优化。整条链路能自洽地讲下来数据库面试基本不用担心。实操层面多去线上环境看看 SHOW ENGINE INNODB STATUS 里的死锁日志多跑几次 EXPLAIN 观察 type 和 Extra 的变化比刷 100 道面试题都管用。数据库没有银弹所有优化都靠理解原理 实测验证这两件事。
