数据库面试核心要点与SQL优化实战
1. 数据库面试核心要点解析
作为Java技术栈的重要组成部分，数据库知识在面试中的考察比重通常占到30%以上。我经历过上百场技术面试后发现，数据库问题往往集中在几个经典领域，掌握这些核心要点能显著提升面试通过率。
2. 基础理论篇
2.1 事务特性与隔离级别
ACID特性是数据库事务的基石。在实际项目中，我们最常遇到的是隔离级别问题。以电商系统为例：
- 读未提交（Read Uncommitted）会导致"脏读"问题
- 读已提交（Read Committed）能避免脏读但可能出现"不可重复读"
- 可重复读（Repeatable Read）是MySQL默认级别，能解决不可重复读但可能有"幻读"
- 串行化（Serializable）完全避免问题但性能最差
提示：面试官常会追问MVCC实现原理，建议准备InnoDB的版本链机制解释
2.2 索引优化实践
B+树索引的查询复杂度是O(log n)，但要注意：
- 最左前缀原则：联合索引(a,b,c)只能用于a、ab或abc查询
- 索引失效场景：
- 使用函数操作：WHERE YEAR(create_time)=2023
- 隐式类型转换：varchar字段用数字查询
- 使用!=或<>操作符
- 使用前导通配符：LIKE '%xxx'
实测案例：某用户表2000万数据，无索引的status查询耗时3.2秒，添加索引后降至28毫秒。
3. SQL优化实战
3.1 执行计划解读
EXPLAIN关键字段解读：
| 字段 | 重点关注值 | 优化建议 |
|---|---|---|
| type | const > ref > range > index > ALL | 避免出现ALL |
| rows | 预估扫描行数 | 超过1000行需要考虑优化 |
| Extra | Using filesort, Using temporary | 需要立即优化 |
3.2 分页查询优化
常规分页的问题：
SELECT * FROM orders LIMIT 100000, 10会导致先读取100010行再丢弃前10万行。
优化方案：
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10前提是有自增主键索引，实测性能提升200倍。
4. 高并发场景应对
4.1 锁机制详解
- 乐观锁：适合读多写少场景，通过version字段实现
- 悲观锁：SELECT...FOR UPDATE，注意可能引发死锁
- 间隙锁：RR隔离级别特有，防止幻读但影响并发
注意：分布式锁要用Redis的SETNX或Zookeeper实现，数据库锁不适用分布式场景
4.2 分库分表策略
当单表超过500万行需要考虑拆分：
- 水平拆分：按ID范围或哈希取模
- 垂直拆分：将大字段拆分到扩展表
- 中间件选型：
- ShardingSphere：功能全面
- MyCat：配置简单
- 自研路由：灵活性高
5. 高频面试题精讲
5.1 为什么用自增主键？
- 插入性能：避免B+树频繁分裂
- 存储空间：比UUID节省50%以上
- 缓存友好：局部性原理提升命中率
例外场景：需要隐藏业务量的场景可以使用雪花ID。
5.2 千万级数据如何快速导入？
实测方案对比：
| 方法 | 1000万数据耗时 | 特点 |
|---|---|---|
| 单条INSERT | 85分钟 | 绝对不要用 |
| 批量INSERT(1000条/批) | 4分20秒 | 需要调整max_allowed_packet |
| LOAD DATA INFILE | 1分15秒 | 需要文件权限 |
| 存储过程 | 6分30秒 | 灵活性高但速度一般 |
6. 避坑指南
- 不要使用SELECT *，特别是Blob/Text字段
- 避免在循环中执行SQL，用批量操作替代
- 大表ALTER TABLE会导致锁表，用pt-online-schema-change
- 连接池配置要合理：最大连接数= (核心数 * 2) + 有效磁盘数
- 模糊查询用ES替代，LIKE '%xxx%'必定全表扫描
7. 进阶知识储备
- WAL机制与redo/undo日志
- Change Buffer优化原理
- 索引下推优化(ICP)
- Buffer Pool多实例配置
- 在线DDL实现原理
我在实际面试中最常被追问的是"从执行SQL到返回结果的全过程"，建议准备完整的执行链路说明，包括连接器、分析器、优化器、执行器等组件协作流程。