MySQL基础面试题深度解析（打工人必备宝典）

本文链接：https://blog.youkuaiyun.com/pythonpioneer/article/details/147990407

文章目录

一、事务的ACID特性（必考题预警！）

事务是面试官最爱挖坑的地方，咱们先来看这个经典问题：“请解释ACID特性并举例说明”。这里有个大坑——很多同学背得出概念但说不清实际应用！

（真实案例警告）去年我们组就遇到个转账bug：用户A给B转账时，系统突然宕机，结果A的钱扣了B却没收到。这就是典型的事务原子性没处理好！！！

正确的ACID解释应该是：

Atomicity原子性：事务操作要么全成功，要么全失败（比如转账的两个账户必须同时变更）
Consistency一致性：事务执行前后数据库状态都合法（例如账户余额不能为负数）
Isolation隔离性：多个并发事务互不干扰（重要！后面会详细讲隔离级别）
Durability持久性：事务提交后数据永久保存（就算磁盘坏了也得通过备份恢复）

（高频追问）面试官可能会让你手写事务代码：

START TRANSACTION;
UPDATE account SET balance=balance-100 WHERE user='A';
UPDATE account SET balance=balance+100 WHERE user='B';
COMMIT;

注意！！这里一定要加异常处理，真实项目里要配合try-catch使用！

二、索引优化迷思（90%的人理解有偏差）

“为什么加了索引反而变慢了？”——这是今年我面试遇到的真实问题。先说结论：索引不是银弹，用错了就是灾难！

索引底层揭秘

B+树索引：默认类型，适合范围查询（比如WHERE age>20）
哈希索引：精准匹配快，但不支持排序（MEMORY引擎特有）
全文索引：大文本搜索用，但别拿它当ES用！

（血泪教训）去年我们有个表加了8个索引，结果插入速度直接暴跌80%！索引维护是要成本的，记住两个原则：

频繁更新的字段谨慎加索引
区分度低的字段（比如性别）不要加索引

最左前缀原则（面试重灾区）

假设有联合索引(a,b,c)，以下查询能用索引吗？

SELECT * FROM table WHERE b=1 AND c=2; ❌
SELECT * FROM table WHERE a=1 AND c=2; ✅（只用a列）
SELECT * FROM table WHERE a>1 AND b=2; ❌（范围查询后失效）

（救命技巧）用EXPLAIN看执行计划，关注type列：

const：通过主键查
ref：普通索引
ALL：全表扫描（赶紧优化！）

三、锁机制深水区（小心死锁！）

上周生产环境刚发生死锁，来看看当时的情况：

-- 事务1
UPDATE user SET score=score+10 WHERE id=1;
UPDATE user SET score=score-5 WHERE id=2;

-- 事务2
UPDATE user SET score=score-5 WHERE id=2;
UPDATE user SET score=score+10 WHERE id=1;

（灵魂拷问）这两个事务为什么会死锁？因为加锁顺序不同导致循环等待！

锁类型大全

行级锁：InnoDB专属，细粒度但开销大
表级锁：MyISAM默认，粗粒度但简单
间隙锁：解决幻读问题（Next-Key Locking）

（重要对比）乐观锁 vs 悲观锁：

乐观锁：适合读多写少，通过版本号控制（CAS机制）
悲观锁：适合写多读少，直接SELECT ... FOR UPDATE

四、SQL优化实战技巧（让查询快10倍）

最近优化了一个3秒的查询到0.3秒，分享我的checklist：

避免SELECT *（特别是TEXT/BLOB字段）
LIMIT分页优化：不要用OFFSET 10000，改用WHERE id>last_id
类型转换陷阱：WHERE phone=13800138000（phone是varchar时会全表扫描！）
连接查询：小表驱动大表（STRAIGHT_JOIN强制连接顺序）

（冷知识）MySQL的查询缓存从8.0开始被移除了！别再想着靠这个优化了！

五、存储引擎选型指南（别再无脑选InnoDB了）

虽然InnoDB是默认引擎，但有些场景MyISAM更合适：

读密集且不需要事务（比如数据仓库）
全文索引需求（不过现在ES更流行）
空间数据（GIS场景）

但注意！！MyISAM的表级锁在并发写时会成为性能瓶颈。最近我们迁移了一个200GB的MyISAM表到InnoDB，写并发从50TPS提升到1200TPS！

六、主从复制原理（高可用基石）

一次故障复盘：主库宕机后，为什么从库数据少了5分钟？

（关键点）复制延迟的三大元凶：

网络带宽不足
从库单线程应用（5.6版有了并行复制）
大事务（比如一次性更新100万行）

七、分库分表生死局（千万级数据怎么存？）

当单表超过2000万行时，就要考虑拆分了。我们的订单表拆分方案：

垂直拆分：把JSON字段拆到扩展表
水平拆分：按用户ID取模分1024个表

（血泪教训）分布式事务要用最终一致性，别强求ACID！常见方案：

本地消息表
Seata框架
事务消息（RocketMQ）

八、备份恢复实战（DBA救命技能）

千万别只用mysqldump！我们的备份策略：

全量备份：每周日0点，用XtraBackup热备
增量备份：每小时binlog
逻辑备份：每天mysqldump核心表

（救命命令）误删数据恢复步骤：

mysqlbinlog --start-position=1234 binlog.000001 | mysql -u root -p

九、执行计划详解（EXPLAIN终极指南）

看懂EXPLAIN输出是优化必备技能：

EXPLAIN SELECT * FROM users WHERE age>18;

重点关注：

key_len：索引使用长度
rows：预估扫描行数
Extra：Using filesort/Using temporary 出现就要警惕！

十、MVCC原理（高手过招必备）

多版本并发控制是InnoDB的核武器！原理拆解：

每个事务有唯一ID
数据行通过undo log保留多个版本
Read View判断可见性

（面试陷阱）为什么RR隔离级别能解决不可重复读？因为每次读取都使用第一次的Read View！

结语（最后叮嘱）

MySQL就像瑞士军刀，功能多但要用对场景。最后送大家三个锦囊：

复杂查询拆分成多个简单查询（网络开销远小于错误成本）
定期执行OPTIMIZE TABLE（特别是频繁更新的表）
监控慢查询日志（long_query_time不要超过1秒）

记得收藏本文，面试前突击看一遍，通过率提升90%！遇到难题欢迎评论区讨论，咱们一起攻克MySQL的深水区！