Java面试数据库案例

本文目录导读：

1. 案例一：深分页查询慢（OFFSET过大）—— 索引失效与SQL改写
2. 案例二：库存超卖（并发下数据一致性）—— 事务与锁
3. 案例三：慢SQL大量堆积（索引失效）—— 索引优化
4. 案例四：主键冲突导致锁等待（死锁分析）
5. 高频追问与加分项（一定要看）

Java面试中，数据库部分不仅考察理论（索引、事务、锁），更侧重于实际场景的故障排查、性能优化和方案设计。

以下是面试官最喜欢问的数据库实战案例及对应的高分回答逻辑，涵盖MySQL（最主流）的核心高频考点。

深分页查询慢（OFFSET过大）—— 索引失效与SQL改写

面试官场景：业务后台需要查询第100000页的数据（每页10条），发现接口耗时8秒，前端页面一直加载中,你如何排查并解决？

回答思路（三步走）：

1. 定位问题（Explain分析）：

   • 首先用 EXPLAIN SELECT ... FROM orders ORDER BY id LIMIT 1000000, 10;
   • 分析结果：type 为 ALL（全表扫描），Extra 可能为 Using filesort（文件排序）。
   • 痛点：MySQL 的 LIMIT 是先查出前1000010条，然后丢弃前1000000条，只返回最后10条。前面100万条都是无用功。

2. 方案A：延迟关联（覆盖索引 + 子查询）（首选方案）

   • 思路：先快速在索引上定位到起始ID,再回表取完整行数据。
   • SQL改写：

     SELECT o.* 
     FROM orders o
     INNER JOIN (
         SELECT id 
         FROM orders 
         ORDER BY id 
         LIMIT 1000000, 10
     ) AS tmp ON o.id = tmp.id;

   • 优化点：内层子查询利用 PRIMARY KEY 索引，只扫描索引树（覆盖索引），速度快，外层通过主键关联回表,仅取10条数据。

3. 方案B：基于游标（记录上一页最大ID）（最高效方案）

   • 思路：不传页码，传上一页的最后一条记录的ID（或时间戳）。
   • 假设上一页最后一条记录ID是 1000000。
   • SQL改写：

     SELECT * FROM orders 
     WHERE id > 1000000 
     ORDER BY id ASC 
     LIMIT 10;

   • 优化点：直接利用主键索引范围扫描，秒级响应。但这需要前端配合改交互逻辑（改为“加载更多”模式）。

库存超卖（并发下数据一致性）—— 事务与锁

面试官场景：一个秒杀系统，仅有100件库存，却有10万人同时抢购，压测发现库存变成了负数（超卖）,如何修复？

回答思路（由浅入深）：

1. 基础方案：悲观锁（SELECT FOR UPDATE）（不推荐压测场景）

   • SQL：开启事务后，SELECT stock FROM product WHERE id = 1 FOR UPDATE;
   • 原理：给该商品行加排他锁，其他事务必须等待,更新完库存后提交事务释放锁。
   • 缺点：并发瓶颈极高，一旦锁等待，数据库连接池会瞬间被打满,造成雪崩。

2. 核心方案：乐观锁（版本号或条件更新）（推荐秒杀场景）

   • 原理：不加锁，在更新时校验库存是否足够（这是关键点）。
   • SQL改写（原子操作+条件判断）：

     UPDATE product 
     SET stock = stock - 1 
     WHERE id = 1 AND stock > 0;

   • 利用受影响行数：如果更新返回的受影响行数为 1，说明扣减成功；如果为 0，说明库存已不足（stock > 0为false）,回滚。
   • 痛点：上述SQL仍有一定性能问题（行锁竞争）。
   • 终极优化：Redis预扣减 + 异步MySQL同步
     • 用 Redis 的 DECR 或 Lua 脚本先扣减库存，抗住高并发，只有真正下单成功的，才异步落库到 MySQL，MySQL 使用上面的 WHERE stock > 0 做最终兜底校验。

3. 面试加分点：避免ABA问题

   • 如果使用版本号 version，更新时需带上 WHERE version = 旧值，如果在重试过程中，可能有业务要求不能使用版本号（比如操作字段是唯一索引），可以使用 CAS（Compare And Set）原理。

慢SQL大量堆积（索引失效）—— 索引优化

面试官场景：监控发现数据库 CPU 飙高，出现大量慢查询日志，日志内容为：SELECT * FROM user WHERE age = 20 AND name LIKE '%张%';，但该表已经在 age 字段加了索引,为何还慢？

回答思路（索引失效的深入分析）：

1. 分析执行计划（重点）：

   • 使用 EXPLAIN 查看，发现 possible_keys 虽然用了 age，但 type 是 ALL（全表扫描）？或者 key 为 NULL。
   • 核心错误点：
     • name LIKE '%张%'：左模糊匹配（百分号在最前），导致非聚簇索引失效（无法使用B+树从左到右的二分查找）。
     • 回表代价高：MySQL优化器会判断，即使走了 age 索引，我也要回表查询 name 字段。age=20 的记录数占据全表记录数的 30% 以上，优化器会认为“全表扫描+内存过滤”比“走索引+大量回表”更快，因此自动放弃索引,导致全表扫描。

2. 解决方案（多元策略）：

   • 覆盖索引：将 name 字段加入索引中，形成联合索引 (age, name)。
     • 优化后：SELECT age, name FROM user WHERE age = 20 AND name LIKE '%张%';
     • 效果：因为查询字段和条件都在索引里，不需要回表,索引必走。
   • 改为右模糊：如果业务允许，将 LIKE '%张%' 改为 LIKE '张%'，这样可以利用B+树索引的有序性。
   • 使用全文索引（MySQL 5.7+）：对于真正的全文搜索需求，使用 FULLTEXT 索引或接入 Elasticsearch。
   • 强制走索引 FORCE INDEX(idx_age)，但这只是权宜之计,治标不治本。

主键冲突导致锁等待（死锁分析）

面试官场景：业务日志报错 Deadlock found when trying to get lock; try restarting transaction,如何排查死锁？

回答思路（死锁的定位步骤）：

1. 定位步骤：

   • 首先查询 SHOW ENGINE INNODB STATUS \G; 查看 LATEST DETECTED DEADLOCK 部分。
   • 死锁原因：通常是由于两个事务加锁顺序不一致导致互相持有对方需要的锁。

2. 常见死锁场景复现：

   • 事务A：UPDATE t SET v=1 WHERE id=1; -> 锁住 id=1
   • 事务B：UPDATE t SET v=2 WHERE id=2; -> 锁住 id=2
   • 事务A：UPDATE t SET v=1 WHERE id=2; -> 等待B释放id=2
   • 事务B：UPDATE t SET v=2 WHERE id=1; -> 等待A释放id=1 -> 死锁产生。

3. 解决策略（防 + 治）：

   • 业务层面：尽量保证对多个表的更新顺序一致（例如按主键ID升序进行更新）。
   • 降低隔离级别：如果业务允许，将 RR（可重复读）降级为 RC（读已提交）级别，因为 RR 级别下有 间隙锁（Gap Lock）,间隙锁更容易造成死锁。
   • 缩短事务时间：避免在事务中做耗时操作（如远程调用RPC、复杂计算）,快速提交事务释放锁。
   • 唯一索引兜底：如果死锁是因为先查后插（两个并发执行不存在记录时插入），可以给表添加唯一索引，让数据库自己处理冲突,避免程序逻辑控制锁。

高频追问与加分项（一定要看）

• 追问1：B+树和B树的区别？为什么用B+树做索引？

  • 答：非叶子节点不存储数据，只存索引键，一棵树能容纳的键更多（扇出更多），树高度低，IO次数少；数据都存在的叶子节点，范围查询速度快；叶子节点之间用双向指针连接,非常适合排序和范围查询。

• 追问2：你说用了乐观锁，那如果并发更新导致冲突很多，怎么办？

  • 答：引入重试机制（如最多重试3次），或者引入消息队列（MQ）串行化,保证单库存扣减请求是串行的。

• 追问3：如何发现慢SQL？

  • 答：开启 MySQL 的 慢查询日志（slow_query_log），设定阈值 long_query_time=2s，使用 mysqldumpslow 工具分析；或者使用开源工具 pt-query-digest；亦或是监控平台（如Prometheus + Grafana）采集 performance_schema 数据。

面试官核心考察点：你是否真的在实战中遇到过问题，是否理解原理（索引、锁、MVCC），是否具备系统性解决问题的能力（排查 -> 分析 -> 优化 -> 验证），回答时尽量按照这个逻辑链条去组织语言,而不是干巴巴地背概念。