第32章：MySQL面试题精选

MySQL教程 / 实战案例

第32章：MySQL面试题精选

面试必备知识点汇总

32.1 基础知识

32.1.1 MySQL架构

Q1：MySQL的架构分为哪几层？

A：MySQL架构分为4层：
1. 连接层：处理客户端连接、认证
2. 服务层：SQL解析、优化、缓存
3. 引擎层：存储引擎（InnoDB、MyISAM等）
4. 存储层：数据存储在磁盘

Q2：InnoDB和MyISAM的区别？

A：主要区别：
1. 事务：InnoDB支持，MyISAM不支持
2. 锁：InnoDB行锁，MyISAM表锁
3. 外键：InnoDB支持，MyISAM不支持
4. 崩溃恢复：InnoDB支持，MyISAM不支持
5. MVCC：InnoDB支持，MyISAM不支持
6. 性能：InnoDB适合写多，MyISAM适合读多

推荐：统一使用InnoDB

32.2 索引相关

32.2.1 索引基础

Q3：什么是索引？有什么作用？

A：索引是帮助MySQL高效获取数据的数据结构。
作用：
1. 加快查询速度
2. 减少磁盘I/O
3. 加速排序和分组

缺点：
1. 占用空间
2. 降低写入性能
3. 需要维护

Q4：MySQL索引使用什么数据结构？为什么？

A：B+树

原因：
1. B+树高度低（3-4层），减少磁盘I/O
2. 叶子节点存储所有数据，范围查询效率高
3. 叶子节点有指针，支持顺序访问
4. 非叶子节点只存储索引，可以存储更多索引

为什么不用B树？
- B树非叶子节点也存储数据，树高更高

为什么不用哈希？
- 哈希不支持范围查询

为什么不用红黑树？
- 红黑树高度高，磁盘I/O多

Q5：什么是聚簇索引和非聚簇索引？

A：
聚簇索引（Clustered Index）：
- 索引和数据存储在一起
- InnoDB的主键索引是聚簇索引
- 一个表只有一个聚簇索引

非聚簇索引（Secondary Index）：
- 索引和数据分开存储
- 叶子节点存储主键值
- 需要回表查询

回表：通过非聚簇索引查到主键，再通过主键查数据

Q6：什么是覆盖索引？

A：查询的列都在索引中，不需要回表。

示例：
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = '张三';  -- 覆盖索引，不需要回表

SELECT name, age, email FROM users WHERE name = '张三';  -- 需要回表

Q7：什么是最左前缀原则？

A：复合索引从最左边开始匹配。

示例：
CREATE INDEX idx_abc ON table(a, b, c);

✅ 可以使用索引：
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

❌ 不能使用索引：
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

32.2.2 索引失效

Q8：哪些情况会导致索引失效？

A：
1. 在索引列上使用函数
   WHERE YEAR(create_time) = 2024  ❌
   WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'  ✅

2. 隐式类型转换
   WHERE phone = 123456789  ❌（phone是VARCHAR）
   WHERE phone = '123456789'  ✅

3. LIKE以%开头
   WHERE name LIKE '%张%'  ❌
   WHERE name LIKE '张%'  ✅

4. OR条件（其中一个列没有索引）
   WHERE name = '张三' OR age = 18  ❌（age没有索引）

5. 不等于（!=、<>）
   WHERE status != 1  ❌

6. IS NULL / IS NOT NULL（视情况）
   WHERE name IS NULL  ❌（可能失效）

7. NOT IN、NOT EXISTS
   WHERE id NOT IN (1, 2, 3)  ❌

32.3 事务相关

32.3.1 事务基础

Q9：什么是事务？ACID是什么？

A：事务是一组SQL操作，要么全部成功，要么全部失败。

ACID：
1. Atomicity（原子性）：事务不可分割
2. Consistency（一致性）：事务前后数据一致
3. Isolation（隔离性）：事务之间互不影响
4. Durability（持久性）：事务提交后永久保存

Q10：MySQL的事务隔离级别有哪些？

A：4个隔离级别（从低到高）：

1. READ UNCOMMITTED（读未提交）
   - 可以读到未提交的数据
   - 问题：脏读、不可重复读、幻读

2. READ COMMITTED（读已提交）
   - 只能读到已提交的数据
   - 问题：不可重复读、幻读
   - Oracle默认

3. REPEATABLE READ（可重复读）
   - 同一事务中多次读取结果一致
   - 问题：幻读（InnoDB通过MVCC+间隙锁解决）
   - MySQL默认 ✅

4. SERIALIZABLE（串行化）
   - 最高隔离级别，完全串行执行
   - 问题：性能差

推荐：REPEATABLE READ

Q11：什么是脏读、不可重复读、幻读？

A：
脏读（Dirty Read）：
- 读到其他事务未提交的数据
- 示例：事务A修改但未提交，事务B读到了修改后的数据

不可重复读（Non-Repeatable Read）：
- 同一事务中多次读取结果不一致
- 示例：事务A读取两次，中间事务B修改并提交，两次结果不同

幻读（Phantom Read）：
- 同一事务中多次查询，记录数不一致
- 示例：事务A查询两次，中间事务B插入新记录，第二次多了记录

Q12：什么是MVCC？

A：MVCC（Multi-Version Concurrency Control）多版本并发控制

原理：
1. 每行记录有两个隐藏列：trx_id（事务ID）、roll_pointer（回滚指针）
2. 每次修改生成新版本，旧版本通过undo log保存
3. 读取时根据事务ID判断可见性

优点：
- 读不加锁，提高并发性能
- 解决不可重复读问题

InnoDB通过MVCC+间隙锁解决幻读

32.4 锁相关

32.4.1 锁类型

Q13：MySQL有哪些锁？

A：
按粒度分：
1. 表锁：锁整个表（MyISAM）
2. 行锁：锁单行（InnoDB）
3. 间隙锁：锁范围（InnoDB）

按类型分：
1. 共享锁（S锁）：读锁，多个事务可以同时持有
2. 排他锁（X锁）：写锁，只有一个事务可以持有

按算法分：
1. Record Lock：记录锁
2. Gap Lock：间隙锁
3. Next-Key Lock：记录锁+间隙锁

Q14：什么是死锁？如何避免？

A：
死锁：两个或多个事务互相等待对方释放锁。

示例：
事务A：锁住行1，等待行2
事务B：锁住行2，等待行1

避免方法：
1. 按相同顺序访问资源
2. 减少锁持有时间
3. 使用乐观锁
4. 降低隔离级别
5. 设置锁等待超时：innodb_lock_wait_timeout

32.5 性能优化

32.5.1 SQL优化

Q15：如何优化慢查询？

A：
1. 分析执行计划（EXPLAIN）
2. 添加索引
3. 避免SELECT *
4. 避免索引失效
5. 优化JOIN
6. 分页优化
7. 使用覆盖索引
8. 避免子查询，改用JOIN

Q16：如何优化深分页？

A：
问题SQL：
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

优化方案：
1. 子查询优化
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1)
LIMIT 10;

2. 延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

3. 记录上次最大ID
SELECT * FROM orders WHERE id > @last_id ORDER BY id LIMIT 10;

32.5.2 参数优化

Q17：重要的MySQL参数有哪些？

A：
1. innodb_buffer_pool_size：缓冲池大小（50-70%内存）
2. innodb_log_file_size：redo log大小
3. innodb_flush_log_at_trx_commit：刷盘策略（1:最安全，2:性能好）
4. sync_binlog：binlog刷盘策略
5. max_connections：最大连接数
6. query_cache_size：查询缓存（MySQL 8.0已移除）

32.6 高可用

32.6.1 主从复制

Q18：MySQL主从复制原理？

A：
1. 主库写入binlog
2. 从库I/O线程读取binlog，写入relay log
3. 从库SQL线程执行relay log

三个线程：
- 主库：binlog dump线程
- 从库：I/O线程、SQL线程

Q19：主从延迟如何解决？

A：
原因：
1. 从库性能差
2. 主库写入量大
3. 大事务
4. 网络延迟

解决方案：
1. 升级从库硬件
2. 并行复制（MySQL 5.7+）
3. 拆分大事务
4. 读写分离，读从库时容忍延迟
5. 重要查询读主库

32.7 实战问题

Q20：如何设计秒杀系统？

A：
1. 数据库层面：
   - 使用乐观锁（version字段）
   - 减库存SQL：UPDATE ... WHERE stock > 0 AND version = @old_version

2. 缓存层面：
   - Redis预减库存
   - 库存放入Redis

3. 消息队列：
   - 削峰填谷
   - 异步处理订单

4. 限流：
   - 接口限流
   - 用户限流（一人一单）

32.8 本章总结

本章学习内容：

✅ 基础知识（架构、存储引擎）⭐⭐⭐⭐⭐
✅ 索引相关（B+树、索引失效）⭐⭐⭐⭐⭐
✅ 事务相关（ACID、隔离级别、MVCC）⭐⭐⭐⭐⭐
✅ 锁相关（锁类型、死锁）⭐⭐⭐⭐⭐
✅ 性能优化（慢查询、参数）⭐⭐⭐⭐⭐
✅ 高可用（主从复制）⭐⭐⭐⭐⭐
✅ 实战问题⭐⭐⭐⭐⭐

高频面试题：

InnoDB和MyISAM的区别
索引为什么用B+树
索引失效的情况
事务隔离级别
MVCC原理
死锁如何避免
如何优化慢查询
主从复制原理
如何设计秒杀系统
如何优化深分页

面试技巧：

先说原理，再说实践
结合实际项目经验
说出优缺点和适用场景
准备几个实战案例

下一章预告： 疑难问题解决方案

练习题

InnoDB和MyISAM有什么区别？
索引为什么用B+树？
什么是聚簇索引？
哪些情况会导致索引失效？
什么是MVCC？
MySQL的事务隔离级别有哪些？
什么是死锁？如何避免？
如何优化慢查询？
主从复制原理是什么？
如何设计秒杀系统？

继续学习： 第33章：疑难问题解决方案

第32章：MySQL面试题精选 - MySQL教程 - jackssybin 的个人博客

jackssybin 的个人博客

第32章：MySQL面试题精选

第32章：MySQL面试题精选

第32章：MySQL面试题精选

32.1 基础知识

32.1.1 MySQL架构

32.2 索引相关

32.2.1 索引基础

32.2.2 索引失效

32.3 事务相关

32.3.1 事务基础

32.4 锁相关

32.4.1 锁类型

32.5 性能优化

32.5.1 SQL优化

32.5.2 参数优化

32.6 高可用

32.6.1 主从复制

32.7 实战问题

32.8 本章总结

练习题