【Java高频面试问题】数据库篇

为什么MySQL选择B+树作为索引
- 一、B+ 树的优势特性
- 二、与常见数据结构的对比
索引优化
- 一、索引类型及使用场景
- 二、索引优化核心策略
- - 1. 避免索引失效场景
  - 2. 性能优化实践
  - 3. 表结构与架构优化
- 三、高频面试问题参考答案
- 总结：面试核心要点
数据库事务
- 一、事务基础
- ‌二、事务隔离级别
- ‌三、Spring 事务管理
- - 1. ‌传播机制（高频考点）
  - 2. ‌事务失效场景
- 四、并发事务的控制方式
- - ‌1、锁机制（Locking）
  - 2、时间戳排序（Timestamp Ordering）
  - 3、乐观并发控制（OCC）
  - 4、多版本并发控制（MVCC）
  - 💎 ‌总结对比
- ‌五、高级考点
线上慢SQL查询优化

为什么MySQL选择B+树作为索引

MySQL 选择 B+ 树作为索引结构是综合磁盘 I/O 效率、范围查询能力、存储利用率等因素的结果，其核心优势如下（对比其他数据结构）：

一、B+ 树的优势特性

多路平衡树结构降低树高‌
- 每个节点可存储大量键值（通常数百个），千万级数据树高仅 ‌3-4 层‌（二叉树需 ‌20+ 层‌），减少磁盘 ‌I/O 次数‌（查询耗时从 O(n) 优化至 O(log n)）。
- 示例‌：查询 1000 万数据，B+ 树仅需 ‌3-4 次 I/O‌，二叉树需 ‌20+ 次 I/O‌。
‌叶子节点有序链表支持高效范围查询‌
- 叶子节点通过双向指针连接，范围查询（如 BETWEEN、ORDER BY）直接遍历链表，‌无需回溯父节点‌。
- 对比哈希索引‌：哈希仅支持等值查询，无法高效处理范围操作。
‌非叶子节点不存数据，提升存储利用率‌
- 非叶子节点仅存‌索引键+指针‌（不存实际数据），单节点可容纳更多键值：
- 1GB 索引‌中非叶子节点约占 ‌**10%‌，叶子节点占 ‌90%**‌，显著提升缓存命中率。
- 对比 B 树‌：B 树非叶子节点存数据，导致 ‌相同数据量下树高增加 1 层‌。

二、与常见数据结构的对比

‌索引类型‌	‌范围查询‌	‌树高/IO次数‌	‌写入性能‌	‌适用场景‌
‌B+ 树‌	✅ 高效	⭐ 极低 (3-4层)	⭐ 平衡	OLTP、高频范围查询
‌二叉树/红黑树‌	✅ 支持	❌ 高 (O(n) 退化风险)	⚠️ 频繁旋转维护成本	内存数据结构
‌哈希索引‌	❌ 不支持	⭐ O(1)	⭐ 高	等值查询场景
‌B 树‌	⚠️ 部分支持	⚠️ 较高 (比B+树多1层)	⭐ 平衡	文件系统

二叉树（红黑树）：节点仅存 1 个键值，树高过大导致 I/O 次数剧增，且插入删除需频繁旋转维护平衡。

B 树范：非叶子节点存数据，导致树高增加、范围查询效率低于 B+ 树。

哈希索引：不支持范围扫描。

索引优化

一、索引类型及使用场景

‌索引类型‌	‌特点‌	‌适用场景‌
‌主键索引‌	唯一、非空，聚簇索引结构	表的主键字段
‌唯一索引‌	列值唯一，允许 NULL	业务唯一字段（如手机号）
‌联合索引‌	多列组合，遵循最左前缀匹配原则	多条件查询（如 `WHERE a=1 AND b=2`）
‌覆盖索引‌	查询字段均在索引中，避免回表	高频查询的字段组合

⚠️ ‌联合索引陷阱‌：

违反最左前缀原则导致失效（如索引 (a,b,c)，条件 WHERE b=2 不生效）
范围查询右侧列失效（如 WHERE a>1 AND b=2，b 无法用索引）

二、索引优化核心策略

1. 避免索引失效场景

‌失效场景‌	‌示例‌	‌原因‌	‌解决方案‌
‌对索引列使用函数‌	`WHERE YEAR(create_time)=2023`	函数操作破坏索引值的有序性，优化器无法直接匹配索引树结构	改用范围查询：`WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'`
‌隐式类型转换‌	`WHERE phone=13800138000`（phone为VARCHAR）	类型不匹配触发隐式转换，导致索引字段计算（如字符串转数字）	统一类型：`WHERE phone='13800138000'`
‌左模糊查询‌	`WHERE name LIKE '%abc'`	通配符在前使B+树无法利用前缀匹配特性，退化为全表扫描	改用右模糊：`LIKE 'abc%'` 或全文索引
‌OR条件部分无索引‌	`WHERE a=1 OR b=2`（b无索引）	优化器判定全表扫描成本低于“索引+回表”组合操作	拆分为`UNION ALL`或为b建索引
‌违反最左前缀原则‌	`WHERE b=1 AND c=2`（联合索引为(a,b,c)）	跳过最左列导致索引树无法定位数据区间	查询条件必须包含最左列（如`WHERE a=1 AND b=1`）
‌范围查询后索引失效‌	`WHERE a>1 AND b=2`（联合索引(a,b)）	范围查询导致后续索引列无法使用有序性	调整列顺序或拆分为单列查询
‌IS NOT NULL条件‌	`WHERE a IS NOT NULL`	非覆盖索引时需回表验证数据是否存在，成本可能高于全表扫描

2. 性能优化实践

‌EXPLAIN 分析 SQL‌：

EXPLAIN SELECT * FROM users WHERE age > 25;

关注 type（扫描类型）、key（使用索引）、Extra（是否覆盖索引）

‌慢查询定位‌：
- 开启慢日志：slow_query_log=1, long_query_time=2
- 使用 SHOW PROFILE 分析执行耗时
‌索引覆盖优化‌：

-- 原查询需回表  
SELECT id, name, age FROM users WHERE city='Beijing';  
-- 创建覆盖索引（InnoDB 二级索引隐式包含主键）
CREATE INDEX idx_city_name_age ON users(city, name, age);

3. 表结构与架构优化

‌垂直分表‌：拆分大字段（如 TEXT）到单独表，减少主表 I/O
‌读写分离‌：主库写 + 从库读，分散压力
‌冷热数据分离‌：归档历史数据，减少主表体积

三、高频面试问题参考答案

‌B+Tree 为什么比 B-Tree 适合数据库索引？

B+Tree 非叶节点不存数据，单页存储更多键值，降低树高度；叶子节点链表支持高效范围查询，减少磁盘随机 I/O

‌如何优化深分页 LIMIT 1000000,10？

使用主键覆盖索引，避免全表扫描

SELECT * FROM table WHERE id > (SELECT id FROM table ORDER BY id LIMIT 1000000,1) LIMIT 10;

‌联合索引 (a,b,c)，WHERE b=1 AND c=2 是否生效？

‌否‌，违反最左前缀原则。需至少包含 a 字段（如 WHERE a=1 AND b=1）

总结：面试核心要点

‌方向‌	‌关键点‌
‌底层原理‌	B+Tree 结构优势、聚簇索引/二级索引区别、回表机制
‌优化策略‌	最左前缀原则、覆盖索引、索引失效场景规避、EXPLAIN 分析
‌架构设计‌	读写分离、分库分表、冷热数据分离
‌问题排查‌	慢查询日志定位、PROFILE 分析、索引使用监控

终极建议‌：结合业务场景设计索引（高频查询字段优先），避免过度索引；所有优化需通过 EXPLAIN 验证

数据库事务

数据库事务可以保证多个对数据库的操作构成一个逻辑上的整体。要么全部执行成功,要么全部不执行 。

一、事务基础

‌ACID 特性‌
- ‌原子性 (Atomicity)：事务操作要么全成功，要么全失败回滚（如转账操作）。
- ‌一致性 (Consistency) ‌：事务执行前后数据完整性不被破坏（如库存不为负）。
- ‌隔离性 (Isolation) ‌：并发事务相互隔离，互不干扰。
- ‌持久性 (Durability) ‌：事务提交后数据永久存储（如订单持久化）。

🌈 补充：只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

‌并发事务问题‌
- ‌脏读‌：读取到其他事务未提交的数据。
- ‌不可重复读‌：同一事务内多次读取同一数据结果不同 (因其他事务修改)。
- ‌幻读‌：同一查询条件返回结果集数量变化 (因其他事务增删数据)。

‌二、事务隔离级别

隔离级别	脏读	不可重复读	幻读	说明
‌READ UNCOMMITTED‌	❌	❌	❌	最低隔离，可能读到未提交数据。
‌READ COMMITTED‌	✅	❌	❌	仅读取已提交数据 (Oracle 默认)，解决脏读。
‌REPEATABLE READ‌	✅	✅	❌	保证多次读取结果一致 (MySQL 默认)，解决脏读、不可重复读。
‌SERIALIZABLE‌	✅	✅	✅	完全串行化，性能最低但解决所有问题。

📌 ‌MySQL 默认隔离级别为 REPEATABLE READ ，通过 MVCC 机制实现一致性读。

‌三、Spring 事务管理

1. ‌传播机制（高频考点）

PROPAGATION_REQUIRED‌（默认）：当前有事务则加入，无则新建。

@Transactional(propagation = Propagation.REQUIRED)
public void methodA() {methodB(); // 加入同一事务
}

PROPAGATION_REQUIRES_NEW‌：挂起当前事务，新建独立事务。

@Transactional(propagation = Propagation.REQUIRES_NEW)
public void methodB() { /* 新事务执行 */ }

PROPAGATION_NESTED‌：嵌套事务，外层失败时回滚内层操作。

2. ‌事务失效场景

非 public 方法使用 @Transactional。
自调用（同类方法内部调用）导致代理失效。
异常类型错误（默认仅回滚 RuntimeException）或异常被捕获未抛出。
未配置事务管理器或数据源问题。

四、并发事务的控制方式

‌1、锁机制（Locking）

通过对数据对象加锁限制并发访问，分为两类：

共享锁（S锁/读锁）：允许事务读取数据，阻止其他事务加排他锁（但允许多个事务同时加读锁）。
排他锁（X锁/写锁）：允许事务修改数据，阻止其他事务加任何锁。
‌封锁协议‌：
- 一级封锁：写前加X锁，事务结束释放（防丢失修改）。
- 二级封锁：读前加S锁（防脏读），写前加X锁，读后即释S锁。
- 三级封锁：读前加S锁、写前加X锁，所有锁事务结束释放（防不可重复读）。

2、时间戳排序（Timestamp Ordering）

为每个事务分配唯一时间戳，按时间顺序调度操作：

‌规则‌：若事务T1时间戳早于T2，则T1操作优先执行，冲突时回滚时间戳大的事务。
‌优点‌：避免死锁，但需全局时钟维护时序。

3、乐观并发控制（OCC）

假设事务冲突概率低，分三阶段执行：

‌读阶段‌：记录读写集，不立即加锁。
验证阶段‌：提交前检测读写冲突（如向后/向前校验）。
写阶段‌：无冲突则提交，否则回滚重试。

适用场景‌：低冲突、读多写少环境。

4、多版本并发控制（MVCC）

维护数据多个历史版本，实现读写分离：

‌读操作‌：访问事务开始时的快照版本（避免阻塞写操作）。
‌写操作‌：创建新版本，不影响正在读取的旧版本。
‌典型应用‌：
- MySQL的REPEATABLE READ隔离级别通过MVCC解决不可重复读。
- Oracle采用MVCC优化行级锁，减少阻塞。

💎 ‌总结对比

‌方法‌	‌核心思想‌	‌优势‌	‌劣势‌
‌锁机制‌	强制串行访问	强一致性，实现简单	死锁风险，并发度低
‌时间戳排序‌	按时间顺序调度	无死锁	时钟同步难，事务重启率高
‌乐观控制‌	提交时冲突检测	高并发，减少锁开销	高冲突时频繁回滚
‌MVCC‌	多版本快照读	读写无阻塞，高并发	版本存储开销大

📌 ‌实践选择‌：单机高并发首选MVCC（如MySQL）；分布式系统常组合使用MVCC与时间戳（如CockroachDB）；低冲突场景可采用OCC优化性能。

‌五、高级考点

‌分布式事务‌

CAP 理论‌：一致性 (Consistency)、可用性 (Availability)、分区容错性 (Partition Tolerance) 不可兼得。
解决方案‌：
- ‌2PC (两阶段提交)：强一致，但存在同步阻塞问题。
- ‌TCC (补偿事务)：通过 Try/Confirm/Cancel 柔性事务保证最终一致。
- ‌Seata 等框架‌：基于 AT 模式自动回滚。

‌事务日志与锁机制‌
- ‌Redo Log‌：保证持久性，记录物理修改。
- ‌Undo Log‌：保证原子性，记录事务回滚所需信息。
- ‌锁分类‌：乐观锁 (CAS)、悲观锁 (行锁/表锁)。

线上慢SQL查询优化

‌定位慢SQL‌
- ‌日志监控‌：开启MySQL慢查询日志（slow_query_log=ON, long_query_time=1）
- ‌连接池工具‌：Druid监控面板（记录执行耗时、最慢SQL排行）或云平台慢SQL分析功能
‌分析执行计划：关键字段分析‌
```
EXPLAIN SELECT * FROM orders WHERE user_id=100;
```
- type：效率排序 const > ref > range > index > ALL（避免全表扫描）
- rows：扫描行数（越少越好）
- Extra：Using filesort（需排序优化）、Using temporary（需避免临时表）
‌制定优化方案‌
- 索引优化 → SQL重写 → 分页/表结构优化 → 业务/架构优化