MySQL面试常见基础问题深度剖析（万字干货）-CSDN博客

本文链接：https://blog.csdn.net/debugdynamo/article/details/147983707

文章目录

一、数据库基础三连问（高频考点）

1. 三大范式到底怎么记？

第一范式（1NF）的核心就一句话：每个字段都是原子的，不可再分！！！ 举个反例你就明白了——地址字段里同时存着"北京市海淀区+中关村大街1号"，这种设计就违反1NF。正确的做法是把省、市、区、详细地址拆分成独立字段。

第二范式（2NF）的关键词是"完全依赖"。举个实际案例：订单表里有[订单ID,商品ID,商品名称,数量]，这里商品名称只依赖于商品ID，而商品ID和订单ID共同构成主键，这就存在部分依赖。解决方法就是拆分成订单明细表和商品表。

第三范式（3NF）要消灭"传递依赖"。比如员工表里有[工号,部门ID,部门电话]，部门电话其实是通过部门ID传递过来的，正确的做法是把部门信息单独建表。

（超级重要）实际开发中不要盲目追求范式，很多大厂系统都会做反范式设计。比如电商系统的订单表，通常会冗余商品名称和价格，虽然违反3NF，但能提升查询效率！

2. 事务的ACID特性实战解读

原子性（Atomicity）：转账要么全成功要么全失败，这个特性由undo log保证
一致性（Consistency）：比如账户余额不能为负数，这个需要应用层和数据库共同维护
隔离性（Isolation）：重点看四种隔离级别（后面会详细展开）
持久性（Durability）：一旦提交就永久保存，由redo log机制实现

3. 存储引擎选型指南

InnoDB vs MyISAM 核心差异对比表

特性	InnoDB	MyISAM
事务支持	✅	❌
行级锁	✅	❌（表级锁）
外键	✅	❌
崩溃恢复	支持	较差
存储文件	.ibd + .frm	.MYD + .MYI + .frm
适用场景	高并发写/事务操作	读密集型操作

（血泪教训）千万不要在线上环境混用存储引擎！曾经有个项目在读写分离时，主库用InnoDB从库用MyISAM，结果同步时索引全乱了！

二、索引优化必杀技

1. B+树索引的底层秘密

为什么不用B树？看这个对比图就懂了：

B树节点存储数据 -> 树的高度更高
B+树数据全在叶子节点 -> 相同数据量树更矮胖

叶子节点形成双向链表，范围查询效率提升10倍不止！这也是为什么范围查询WHERE id > 100在B+树索引下效率极高的原因。

2. 最左前缀原则的实战应用

建立联合索引(name,age)后：

WHERE name='张三' ✅ 走索引
WHERE age=20 ❌ 不走索引
WHERE name LIKE '张%' ✅ 走索引
WHERE name='张三' AND age>18 ✅ 走索引

（避坑指南）常见的错误写法：WHERE LEFT(name,3)='张' 这种函数操作会导致索引失效！

3. 索引失效的七大罪状

在索引列上做计算：WHERE id+1=100
使用前导通配符：LIKE '%张三'
隐式类型转换：字符串列用WHERE num=123
OR连接非索引列：WHERE id=1 OR name='aaa'
使用!=或<>操作符
索引列使用函数
全表扫描比索引更快时（数据量很小）

三、锁机制与事务隔离

1. 当前读 vs 快照读

当前读：SELECT...FOR UPDATE / UPDATE语句，读取最新数据并加锁
快照读：普通SELECT语句，基于MVCC读取历史版本

2. 事务隔离级别对比表

隔离级别	脏读	不可重复读	幻读	实现方式
读未提交	✅	✅	✅	无任何控制
读已提交（RC）	❌	✅	✅	每次快照读都生成新ReadView
可重复读（RR）	❌	❌	❌（通过间隙锁解决）	事务首次快照读生成ReadView
串行化	❌	❌	❌	完全串行执行

（重要提示）MySQL默认是RR级别，但实际业务中很多公司会改用RC级别，因为能减少死锁概率！

3. 间隙锁引发的死锁案例

事务A执行：

UPDATE user SET score=100 WHERE age BETWEEN 20 AND 30;

事务B执行：

INSERT INTO user(age) VALUES(25);

这时就可能发生死锁，因为事务A锁定了20-30的间隙，而事务B试图插入这个区间的数据。

四、SQL优化实战技巧

1. EXPLAIN执行计划解读要点

type列：从优到劣排序 system > const > ref > range > index > ALL
Extra列常见值：
- Using index：覆盖索引
- Using temporary：使用了临时表
- Using filesort：需要额外排序

2. 分页查询优化方案对比

原始写法：

SELECT * FROM table LIMIT 1000000,10;

优化方案1（子查询）：

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

优化方案2（游标分页）：

SELECT * FROM table WHERE id > last_id ORDER BY id LIMIT 10;

3. 连接查询的玄机

INNER JOIN：MySQL会自动选择小表作为驱动表
STRAIGHT_JOIN：强制指定驱动表顺序
LEFT JOIN：右边的表会走索引吗？不一定！取决于where条件

（性能陷阱）遇到Using join buffer说明需要优化，可以尝试增加join_buffer_size参数，但更好的办法是优化索引。

五、高频灵魂拷问

Q：为什么建议用自增主键？
A：① 插入时直接追加，避免页分裂 ② 顺序写入对机械硬盘友好 ③ 范围查询效率高。但分布式场景下可能要用雪花ID。

Q：大表怎么修改字段？
A：① 先在从库执行 ② 使用pt-online-schema-change工具 ③ 避免同时修改多个字段 ④ 选择业务低峰期操作

Q：count(*)为什么慢？
A：MyISAM直接返回元数据计数，InnoDB要遍历索引。优化方案：① 用缓存记录总数 ② 单独维护计数表 ③ 使用EXPLAIN估算

下期预告：《MySQL深分页优化终极方案》+《死锁排查的十八般武艺》，关注不迷路！觉得有用请点个赞👍，有什么问题欢迎评论区交流～