05-面试题库

MySQL 题库

60 道精选 MySQL 题，从基础到进阶

📋 目录

• 基础篇 (15题)
• 索引篇 (15题)
• 事务和锁篇 (10题)
• 优化篇 (10题)
• 高可用篇 (10题)

---

基础篇

1. MySQL 有哪些存储引擎？InnoDB 和 MyISAM 的区别？

答案：

特性	InnoDB	MyISAM
事务	✅	❌
外键	✅	❌
锁粒度	行锁	表锁
MVCC	✅	❌
崩溃恢复	✅	❌
适用场景	事务、高并发	只读、统计

推荐：InnoDB（MySQL 5.5+ 默认）

---

2. MySQL 的数据类型有哪些？

答案： 数值类型：

• TINYINT、SMALLINT、INT、BIGINT
• FLOAT、DOUBLE、DECIMAL

字符串类型：

• CHAR、VARCHAR、TEXT、BLOB

日期时间：

• DATE、TIME、DATETIME、TIMESTAMP

其他：

• ENUM、SET、JSON（MySQL 5.7+）

---

3. CHAR 和 VARCHAR 的区别？

答案：

特性	CHAR	VARCHAR
长度	定长	变长
存储	空格填充	实际长度 + 1-2 字节
性能	快（固定长度）	慢（需计算长度）
空间	浪费	节省
适用	长度固定（手机号、身份证）	长度不定（用户名、标题）

---

4. DATETIME 和 TIMESTAMP 的区别？

答案：

特性	DATETIME	TIMESTAMP
大小	8 字节	4 字节
范围	1000-9999 年	1970-2038 年
时区	不受时区影响	受时区影响
自动更新	❌	✅

使用建议：

• 创建时间：DATETIME
• 更新时间：TIMESTAMP

---

5. AUTO_INCREMENT 的原理？

答案：

• 自动递增的唯一值
• 通常用于主键
• 从 1 开始（可修改起始值）

-- 设置起始值
ALTER TABLE users AUTO_INCREMENT = 1000;

-- 查看当前值
SELECT AUTO_INCREMENT FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users';

-- 重置（慎用）
ALTER TABLE users AUTO_INCREMENT = 1;

---

6. NULL 的注意事项？

答案： 问题：

• 需要额外空间存储
• 索引统计复杂
• 比较需要 IS NULL

建议：

• 尽量设置 NOT NULL
• 提供默认值

-- ❌ 允许 NULL
age INT

-- ✅ 不允许 NULL
age INT NOT NULL DEFAULT 0

---

7. 主键如何选择？

答案： 推荐：自增 INT/BIGINT

原因：

• 单调递增，插入性能好
• 占用空间小
• 二级索引占用空间小

不推荐：UUID

• 无序，插入性能差
• 占用空间大（16 字节）
• 二级索引占用空间大

---

8. COUNT(*) 和 COUNT(1) 和 COUNT(column) 的区别？

答案：

COUNT(*)      -- 统计所有行（包括 NULL）
COUNT(1)      -- 统计所有行（同 COUNT(*)）
COUNT(column) -- 统计该列非 NULL 的行数

-- 性能
MySQL 5.7+: COUNT(*) 和 COUNT(1) 性能相同，优化器会优化

-- 使用建议
推荐使用 COUNT(*)，语义更清晰

---

9. UNION 和 UNION ALL 的区别？

答案：

• UNION：去重（需要额外排序）
• UNION ALL：不去重（性能更好）

-- UNION（慢）
SELECT username FROM users WHERE age < 18
UNION
SELECT username FROM users WHERE age > 60;

-- UNION ALL（快）
SELECT username FROM users WHERE age < 18
UNION ALL
SELECT username FROM users WHERE age > 60;

建议：如果确定没有重复，使用 UNION ALL。

---

10-15. 基础快速问答

10. GROUP BY 和 DISTINCT 的区别？

• GROUP BY：分组，可配合聚合函数
• DISTINCT：去重

11. HAVING 和 WHERE 的区别？

• WHERE：过滤行，分组前执行
• HAVING：过滤分组，分组后执行

12. 内连接和外连接的区别？

• INNER JOIN：只返回匹配的行
• LEFT JOIN：返回左表所有行
• RIGHT JOIN：返回右表所有行

13. DELETE 和 TRUNCATE 的区别？

• DELETE：逐行删除，可回滚，不重置 AUTO_INCREMENT
• TRUNCATE：删除整表，不可回滚，重置 AUTO_INCREMENT

14. DROP、DELETE、TRUNCATE 的区别？

• DROP：删除表结构和数据
• TRUNCATE：删除所有数据，保留结构
• DELETE：删除数据，保留结构

15. LIMIT 的性能问题？

-- 深分页慢
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- 优化：子查询
SELECT * FROM users 
WHERE id >= (SELECT id FROM users LIMIT 1000000, 1) 
LIMIT 10;

---

索引篇

16. 什么是索引？为什么需要索引？

答案： 索引是帮助 MySQL 高效查询数据的数据结构。

作用：

• 加快查询速度
• 唯一索引保证数据唯一性
• 加速 JOIN、ORDER BY、GROUP BY

代价：

• 占用存储空间
• 降低写入性能

---

17. 索引有哪些类型？

答案： 按数据结构：

• B+Tree 索引（最常用）
• Hash 索引（Memory 引擎）
• Full-Text 索引（全文检索）
• R-Tree 索引（空间数据）

按功能：

• 主键索引（PRIMARY KEY）
• 唯一索引（UNIQUE）
• 普通索引（INDEX）
• 全文索引（FULLTEXT）

按列数：

• 单列索引
• 联合索引（复合索引）

---

18. 为什么 InnoDB 使用 B+Tree 而不是 Hash 或 B Tree？

答案： 对比 Hash：

• Hash 不支持范围查询
• Hash 不支持排序
• B+Tree 支持范围查询和排序

对比 B Tree：

• B+Tree 所有数据在叶子节点，非叶子节点只存键，可存储更多键，树高度更低
• B+Tree 叶子节点有链表，范围查询快
• B Tree 数据分散在所有节点，范围查询需要多次随机 IO

---

19. 什么是聚簇索引和非聚簇索引？

答案： 聚簇索引：

• 叶子节点存储完整行数据
• InnoDB 的主键索引
• 一个表只有一个聚簇索引

非聚簇索引：

• 叶子节点存储主键值
• InnoDB 的二级索引
• 查询需要回表

MyISAM：

• 所有索引都是非聚簇索引
• 叶子节点存储行指针

---

20. 什么是覆盖索引？

答案： 查询的字段都在索引中，无需回表。

-- 索引：idx_username_email(username, email)

-- ✅ 覆盖索引
SELECT username, email FROM users WHERE username = 'alice';

-- ❌ 需要回表
SELECT * FROM users WHERE username = 'alice';

优势：

• 减少回表，性能更好
• 减少 IO 操作

---

21. 什么是最左前缀原则？

答案： 联合索引遵循最左前缀匹配。

-- 索引：idx_a_b_c(a, b, c)

✅ WHERE a = 1
✅ WHERE a = 1 AND b = 2
✅ WHERE a = 1 AND b = 2 AND c = 3
✅ WHERE a = 1 AND c = 3  -- 只用 a

❌ WHERE b = 2
❌ WHERE c = 3
❌ WHERE b = 2 AND c = 3

---

22. 索引什么时候会失效？

答案： 见上面 MySQL进阶.md Q3，核心：

1. 使用函数或表达式
2. 隐式类型转换
3. LIKE 左模糊
4. OR 条件（索引不完整）
5. NOT、!=、<>
6. 联合索引不遵循最左前缀

---

23. 如何优化索引？

答案：

1. 选择选择性高的列
2. 遵循最左前缀原则
3. 使用覆盖索引
4. 避免冗余索引
5. 定期检查无用索引

-- 查找无用索引
SELECT * FROM sys.schema_unused_indexes;

-- 查找重复索引
SELECT * FROM sys.schema_redundant_indexes;

---

24. 联合索引的顺序如何确定？

答案： 原则：

1. 选择性高的列在前
2. 常用查询条件在前
3. 范围查询列在后

-- 查询：WHERE city = ? AND age > 18

-- ✅ 推荐
CREATE INDEX idx_city_age ON users(city, age);

-- 原因：city 选择性高，age 是范围查询

---

25. 什么是索引下推（ICP）？

答案： MySQL 5.6+ 支持，在索引遍历时就过滤数据，减少回表。

-- 索引：idx_city_age(city, age)
SELECT * FROM users WHERE city = 'Beijing' AND age > 18;

-- 没有 ICP：
-- 1. 使用 city 索引查找所有 Beijing 的记录
-- 2. 回表获取完整行
-- 3. 过滤 age > 18

-- 有 ICP：
-- 1. 使用 city 索引查找
-- 2. 在索引中过滤 age > 18（减少回表）
-- 3. 回表获取完整行

---

26-30. 索引快速问答

26. 主键索引和唯一索引的区别？

• 主键索引：不允许 NULL，一个表只有一个
• 唯一索引：允许 NULL，可以有多个

27. 前缀索引的优缺点？

• 优点：节省空间
• 缺点：可能增加扫描行数，不支持覆盖索引

28. 索引的基数（Cardinality）是什么？

• 索引中不重复值的数量
• 基数越高，选择性越好

29. 如何查看索引使用情况？

SHOW INDEX FROM users;
SELECT * FROM sys.schema_unused_indexes;

30. 什么时候不需要建索引？

• 数据量小（< 1000 行）
• 频繁更新的字段
• 选择性低的字段（如性别）
• WHERE 条件很少使用的字段

---

事务和锁篇

31. 什么是事务？ACID 特性？

答案： 事务是一组 SQL 语句的集合，要么全部成功，要么全部失败。

ACID：

• A（Atomicity）原子性：全部成功或全部失败
• C（Consistency）一致性：事务前后数据一致
• I（Isolation）隔离性：并发事务互不干扰
• D（Durability）持久性：事务提交后永久保存

---

32. MySQL 的隔离级别有哪些？

答案：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✅	✅	✅
READ COMMITTED	❌	✅	✅
REPEATABLE READ（默认）	❌	❌	❌（InnoDB）
SERIALIZABLE	❌	❌	❌

InnoDB 如何解决幻读：

• MVCC（多版本并发控制）
• Next-Key Lock（行锁 + 间隙锁）

---

33. 什么是脏读、不可重复读、幻读？

答案： 脏读：读取未提交的数据

-- 事务 A
UPDATE users SET age = 26 WHERE id = 1;  -- 未提交

-- 事务 B
SELECT age FROM users WHERE id = 1;  -- 读到 26（脏读）

-- 事务 A
ROLLBACK;  -- 回滚，age 仍是 25

不可重复读：两次读取结果不同

-- 事务 A
SELECT age FROM users WHERE id = 1;  -- 25

-- 事务 B
UPDATE users SET age = 26 WHERE id = 1;
COMMIT;

-- 事务 A
SELECT age FROM users WHERE id = 1;  -- 26（不可重复读）

幻读：两次查询结果集数量不同

-- 事务 A
SELECT COUNT(*) FROM users WHERE age > 18;  -- 10

-- 事务 B
INSERT INTO users (age) VALUES (20);
COMMIT;

-- 事务 A
SELECT COUNT(*) FROM users WHERE age > 18;  -- 11（幻读）

---

34. MVCC 的实现原理？

答案： 核心概念：

• 隐藏字段：DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID
• undo log：记录数据的历史版本
• Read View：判断版本可见性

可见性规则：

1. trx_id < min_trx_id：可见（已提交）
2. trx_id >= max_trx_id：不可见（未开始）
3. min_trx_id <= trx_id < max_trx_id：
   • 在活跃列表中：不可见
   • 不在活跃列表中：可见

---

35. MySQL 的锁有哪些？

答案： 按粒度：

• 表锁：LOCK TABLES
• 行锁：InnoDB 支持
• 页锁：BDB 引擎

按类型：

• 共享锁（S Lock）：读锁
• 排他锁（X Lock）：写锁

InnoDB 行锁：

• 记录锁（Record Lock）
• 间隙锁（Gap Lock）
• Next-Key Lock（记录锁 + 间隙锁）

---

36. 什么是死锁？如何避免？

答案： 死锁：两个或多个事务互相等待对方持有的锁。

避免方法：

1. 固定加锁顺序
2. 缩小事务范围
3. 使用索引（减少锁定行数）
4. 降低隔离级别

查看死锁：

SHOW ENGINE INNODB STATUS;

---

37. 乐观锁和悲观锁？

答案： 悲观锁：每次都加锁

SELECT * FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;

乐观锁：不加锁，使用版本号

-- 查询时获取版本号
SELECT stock, version FROM products WHERE id = 1;

-- 更新时检查版本号
UPDATE products 
SET stock = stock - 1, version = version + 1 
WHERE id = 1 AND version = 10;

-- affected_rows == 0 表示版本冲突，重试

---

38. 间隙锁是什么？

答案： 锁定记录之间的间隙，防止幻读。

-- 假设有记录：1, 5, 10

-- 事务 A
SELECT * FROM users WHERE id > 5 FOR UPDATE;
-- 锁定间隙：(5, 10)、(10, +∞)

-- 事务 B
INSERT INTO users (id) VALUES (6);  -- 被阻塞
INSERT INTO users (id) VALUES (3);  -- 不阻塞

---

39. 表锁和行锁的区别？

答案：

特性	表锁	行锁
粒度	整个表	单行
并发	低	高
开销	小	大
死锁	不会	可能
引擎	MyISAM	InnoDB

---

40. 如何查看锁等待和死锁？

答案：

-- 查看锁等待
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看事务
SELECT * FROM information_schema.innodb_trx;

-- 查看死锁
SHOW ENGINE INNODB STATUS;

---

优化篇

41. EXPLAIN 执行计划如何看？

答案： 重点字段：

• type：访问类型（const > eq_ref > ref > range > index > ALL）
• key：实际使用的索引
• rows：扫描行数（越小越好）
• Extra：额外信息

优化目标：

• type 至少 ref
• 避免 Extra 中的 Using temporary、Using filesort

---

42. 如何优化慢查询？

答案： 步骤：

1. 开启慢查询日志
2. 使用 EXPLAIN 分析
3. 添加索引或优化 SQL
4. 验证效果

优化方法：

• 添加索引
• 避免函数、类型转换
• 使用覆盖索引
• 优化 JOIN
• 分页优化

---

43. 大表如何优化？

答案： 定义：单表超过 500 万行或 2GB

优化方案：

1. 索引优化：添加合适的索引
2. 分区表：按时间或范围分区
3. 水平分表：拆分成多个表
4. 归档历史数据：定期归档
5. 读写分离：减轻主库压力

---

44. 如何优化 COUNT 查询？

答案：

-- ❌ 慢
SELECT COUNT(*) FROM users;

-- ✅ 优化方案
1. 使用 Redis 缓存计数
2. 使用汇总表
3. 使用近似值（EXPLAIN 的 rows）
4. 添加 WHERE 条件，使用索引

---

45. 如何优化分页查询？

答案： 见上面，核心：

• 使用子查询
• 记录上次查询的最大 ID

---

46. 如何优化 JOIN 查询？

答案：

1. 小表驱动大表
2. JOIN 字段都要有索引
3. 避免 JOIN 太多表（建议 < 3 个）
4. 使用 STRAIGHT_JOIN 指定驱动表

---

47. 如何优化 GROUP BY？

答案：

1. 为 GROUP BY 字段建索引
2. 使用 ORDER BY NULL 禁用排序
3. 避免使用临时表

---

48. INSERT 如何优化？

答案：

1. 批量插入（1000-5000 条/批）
2. 使用事务
3. 禁用索引（大量导入时）
4. 使用 LOAD DATA（从文件导入）

---

49. UPDATE 如何优化？

答案：

1. 使用索引
2. 批量更新
3. 避免更新不必要的字段
4. 分批更新（大量数据）

---

50. DELETE 如何优化？

答案：

1. 使用索引
2. 分批删除（LIMIT 10000）
3. 定期归档
4. 考虑逻辑删除（软删除）

---

高可用篇

51. MySQL 主从复制原理？

答案： 流程：

1. Master 执行 SQL，写入 binlog
2. Slave IO 线程读取 Master 的 binlog
3. Slave IO 线程写入 relay log
4. Slave SQL 线程执行 relay log

复制方式：

• 异步复制（默认）
• 半同步复制
• 组复制（MGR）

---

52. 主从延迟如何解决？

答案： 原因：

• 主库写入快，从库执行慢
• 大事务
• 从库硬件差

解决方案：

1. 并行复制（MySQL 5.7+）
2. 半同步复制
3. 读写分离时从缓存读
4. 优化从库硬件
5. 避免大事务

---

53. 什么是半同步复制？

答案： Master 等待至少一个 Slave 确认接收 binlog 后才返回成功。

优势：数据更安全 缺点：性能略降低

---

54. binlog 的三种格式？

答案：

• STATEMENT：记录 SQL 语句（节省空间，可能不一致）
• ROW：记录数据变化（空间大，完全一致，推荐）
• MIXED：混合模式

---

55. redo log 和 binlog 的区别？

答案：

特性	redo log	binlog
层级	InnoDB 引擎	Server 层
内容	物理日志（页的修改）	逻辑日志（SQL 或行变化）
作用	崩溃恢复	主从复制、数据恢复
大小	固定（循环写）	无限（追加写）

---

56. 如何保证主从一致性？

答案：

1. 半同步复制：等待从库确认
2. 并行复制：减少延迟
3. 监控延迟：Seconds_Behind_Master
4. 强制主库读：写后立即读

---

57. 什么是读写分离？

答案：

• 主库：处理写请求
• 从库：处理读请求

优势：

• 分担主库压力
• 提高并发能力

问题：

• 主从延迟
• 数据不一致

---

58. 如何进行数据库备份？

答案： 逻辑备份：

mysqldump -u root -p mydb > backup.sql

物理备份：

# Percona XtraBackup
xtrabackup --backup --target-dir=/backup/

备份策略：

• 全量备份：每周一次
• 增量备份：每天一次
• binlog 备份：实时

---

59. 分库分表的策略？

答案： 垂直拆分：

• 按业务拆分数据库
• 按字段拆分表（冷热分离）

水平拆分：

• 按范围分表（时间、ID）
• 按哈希分表（user_id % 10）
• 按地区分表

---

60. 如何设计高可用方案？

答案：

1. 主从复制：数据备份
2. 半同步复制：数据安全
3. MHA/MMM：自动故障转移
4. 读写分离：提高并发
5. 监控告警：及时发现问题
6. 定期备份：数据保护

---

总结

MySQL 核心：

1. ✅ 基础知识：数据类型、存储引擎、SQL 语句
2. ✅ 索引原理：B+Tree、聚簇索引、覆盖索引、最左前缀
3. ✅ 事务和锁：ACID、隔离级别、MVCC、死锁
4. ✅ 性能优化：EXPLAIN、索引优化、SQL 优化、配置优化
5. ✅ 高可用：主从复制、半同步、读写分离、备份

建议：

• 理论 + 实践
• 举例说明
• 量化指标
• 总结经验

高频问题 TOP 10：

1. InnoDB 和 MyISAM 的区别？
2. 索引原理？为什么用 B+Tree？
3. 什么情况下索引失效？
4. 事务的隔离级别？
5. MVCC 原理？
6. 如何优化慢查询？
7. 主从复制原理？
8. 如何解决主从延迟？
9. 大表如何优化？
10. 分库分表的策略？

祝你顺利！ 🎉