【mysql】MySQL 数据库 30 道高频面试题（含答案）

MySQL 数据库 30 道高频面试题（含答案）

这套题覆盖了从基础到架构的核心考点，建议重点背诵加粗部分。

一、基础篇

1. MySQL 常用的存储引擎有哪些？InnoDB 和 MyISAM 的区别是什么？

• InnoDB（默认）：支持事务、行级锁、外键；采用聚簇索引；支持崩溃恢复（Crash-Safe）；适合写密集型应用。
• MyISAM（旧版默认）：不支持事务、只支持表锁、不支持外键；非聚簇索引；支持全文索引；适合读多写少场景。
• 核心区别总结：InnoDB 支持事务和行锁，MyISAM 不支持；InnoDB 崩溃后可恢复，MyISAM 容易丢数据；InnoDB 并发性能好，MyISAM 并发差。

2. CHAR 和 VARCHAR 的区别是什么？

• CHAR：定长字符串，长度固定（0-255），存储时会用空格填充，检索时去掉空格。存取速度快，但浪费空间。适合存 MD5 值、手机号。
• VARCHAR：变长字符串，长度可变（0-65535），只占用实际长度+1/2字节（记录长度），节省空间，但存取速度稍慢。适合存用户名、地址。

3. DROP、DELETE 和 TRUNCATE 的区别是什么？

• DELETE：DML 语句，逐行删除，支持 WHERE，可回滚，删除后不重置自增列计数器，产生 Undo Log，速度慢。
• TRUNCATE：DDL 语句，清空整表，不支持 WHERE，不可回滚，重置自增列，不产生 Undo Log，速度快。
• DROP：DDL 语句，删除表结构和数据，释放表所占空间，不可回滚，速度最快。

4. 什么是视图（View）？

• 定义：虚拟表，其内容由查询定义（一条 SELECT 语句）。
• 优点：简化复杂 SQL、保护数据（隐藏敏感字段）、逻辑独立性。
• 缺点：性能较差（简单视图还好，复杂视图可能很慢）、修改限制（某些复杂视图不能更新）。

5. UNION 和 UNION ALL 的区别？

• UNION：对两个结果集进行并集操作，去除重复行。
• UNION ALL：不进行去重。
• 建议：如果确定结果没有重复数据，或者不需要去重，务必使用 UNION ALL，因为去重非常消耗 CPU。

6. 内连接、左连接、右连接的区别？

• INNER JOIN：返回两张表中匹配的记录。
• LEFT JOIN：返回左表的全部记录，即使右表没有匹配；右表无匹配则补 NULL。
• RIGHT JOIN：返回右表的全部记录，即使左表没有匹配；左表无匹配则补 NULL。
• 口诀：左连左为准，右连右为准。

7. GROUP BY 和 ORDER BY 的区别？

• GROUP BY：用于分组，通常与聚合函数（SUM, COUNT, AVG）一起使用，改变原数据集的形态（行数变少）。
• ORDER BY：用于排序，不改变数据集的行数，只改变行的顺序。

8. MySQL 中 NULL 值的处理？

• NULL 代表未知数据。
• 使用IS NULL或IS NOT NULL判断，不能用= NULL。
• 任何值与 NULL 运算结果都为 NULL。
• 聚合函数（COUNT(*)除外）会忽略 NULL 值。
• 尽量给关键字段设置默认值，减少NULL 值。

二、索引篇（重中之重）

9. 索引的底层数据结构是什么？为什么不用 Hash、二叉树？

• B+ Tree。
• 不用 Hash：虽然等值查询 O(1)，但不支持范围查询、不支持排序、模糊查询，存在哈希冲突。
• 不用二叉树/红黑树：树的高度太高，磁盘 I/O 次数多（树高=logN），且无法很好地利用磁盘预读特性。
• B+ Tree 优势：多路平衡查找树，矮胖型，减少磁盘 I/O；叶子节点形成有序双向链表，极大支持范围查询；非叶子节点只存 key，存储密度高。

10. 聚簇索引和非聚簇索引的区别？

• 聚簇索引（InnoDB）：索引和数据存放在一起。主键索引的叶子节点存储整行数据。一张表只有一个聚簇索引（通常是主键）。
• 非聚簇索引（二级索引）：叶子节点存储的是主键值，而不是数据地址。
• 回表：使用二级索引查询时，先找到主键，再拿主键去聚簇索引查数据的过程。
• 覆盖索引：查询的列恰好是索引列，不需要回表。

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

• 联合索引(a, b, c)相当于建立了(a),(a, b),(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 a>1 and b=2（范围查询后的字段失效）、where a=1 and c=3（跳过了 b，c 失效）。

12. 哪些情况下索引会失效？

• 使用OR连接条件（除非 OR 前后都有索引）。
• 复合索引未遵循最左前缀原则。
• 在索引列上进行运算或函数操作（如DATE(create_time)）。
• 使用LIKE '%abc'（前导模糊）。
• 类型隐式转换（如字符串字段不加引号where varchar_col = 123）。
• 使用!=、<>、NOT IN（有时失效）。
• 全表扫描比走索引更快时（数据量少或区分度低）。

13. Explain 执行计划中 type 字段的含义？

• system/const：表中只有一行记录（系统表），或主键/唯一索引等值查询（最优）。
• eq_ref：联表查询中，主键或唯一索引关联（非常好）。
• ref：非唯一索引等值查询（良好）。
• range：范围查询（BETWEEN, IN, >）（可接受）。
• index：全索引扫描（比 ALL 好一点，因为只扫索引树）。
• ALL：全表扫描（必须优化）。
• 目标：至少优化到range，最好到ref。

14. 索引是不是越多越好？

• 不是。
• 缺点：占用磁盘空间；降低 INSERT、UPDATE、DELETE 的速度（需要维护 B+ 树）；优化器在选择索引时也会消耗更多时间。
• 不适合建索引的情况：表数据太少；频繁更新的字段；区分度低的字段（如性别、状态位）。

15. 什么是前缀索引？

• 对字段的前 N 个字符建立索引，而不是整个字段。
• 目的：减少索引占用的空间，提高索引效率。
• 适用场景：TEXT/BLOB 类型或大字段的 VARCHAR。
• 注意：无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法做覆盖扫描。

三、事务与锁篇

16. ACID 特性是什么？

• Atomicity（原子性）：事务是不可分割的最小单元，要么全做，要么全不做（Undo Log 实现）。
• Consistency（一致性）：事务执行前后，数据库都必须处于一致的状态（最终目标，由原子性、隔离性、持久性共同保证）。
• Isolation（隔离性）：多个事务并发执行时，互不干扰（MVCC/Lock 实现）。
• Durability（持久性）：事务一旦提交，其结果就是永久性的（Redo Log 实现）。

17. MySQL 的事务隔离级别有哪些？

• READ UNCOMMITTED（读未提交）：会出现脏读、不可重复读、幻读。
• READ COMMITTED（读已提交 RC）：解决脏读，出现不可重复读、幻读（Oracle 默认）。
• REPEATABLE READ（可重复读 RR）：解决脏读、不可重复读，InnoDB 通过 Next-Key Lock 解决幻读（MySQL 默认）。
• SERIALIZABLE（串行化）：最高隔离级别，完全串行执行，性能最低。

18. 脏读、不可重复读、幻读的区别？

• 脏读：读到其他事务未提交的数据（Rollback 了）。
• 不可重复读：同一事务内，两次读取同一条记录，数据内容变了（被 UPDATE/DELETE）。
• 幻读：同一事务内，两次读取一个范围内的记录，结果集行数变了（多了或少了几行，被 INSERT）。
• 侧重：不可重复读侧重Update/Delete，幻读侧重Insert。

19. MVCC 的原理是什么？

• 多版本并发控制。
• 核心：通过保存数据在某个时间点的快照来实现。
• 实现：每行记录后面隐藏两个字段（DB_TRX_ID事务ID，DB_ROLL_PTR回滚指针）。
• Undo Log：用于保存数据的历史版本，形成版本链。
• Read View：在事务开始时生成一个 Read View，根据规则判断版本链中哪个版本对当前事务可见。RC 级别下每次 SELECT 都生成新的 Read View，RR 级别下只在第一次 SELECT 时生成 Read View。

20. InnoDB 是如何解决幻读的？

• 在RR隔离级别下，InnoDB 使用Next-Key Lock（临键锁）。
• Next-Key Lock =Record Lock（行锁）+Gap Lock（间隙锁）。
• 它锁定的是一个索引区间（左开右闭），防止其他事务在这个区间内插入新数据，从而彻底解决了幻读问题。

21. MyISAM 和 InnoDB 的锁机制区别？

• MyISAM：只支持表级锁。读锁（共享锁 S）和写锁（排他锁 X）。并发度低，锁冲突概率高。
• InnoDB：支持行级锁和表级锁（意向锁）。默认是行锁，基于索引实现。并发度高，锁冲突概率低。

22. 什么是死锁？如何解决？

• 死锁：两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象。
• 排查：SHOW ENGINE INNODB STATUS;查看最近一次死锁信息。
• 解决/预防：
  • 设置超时时间：innodb_lock_wait_timeout。
  • 开启死锁检测：innodb_deadlock_detect=ON（默认开启）。
  • 业务逻辑：尽量以相同的顺序访问表和行；大事务拆小；为表添加合理的索引（减少锁的范围）。

四、日志与底层原理篇

23. Redo Log、Undo Log 和 Binlog 的区别？

24. 两阶段提交（XA）是什么？

• 为了保证 Redo Log 和 Binlog 的一致性（因为两者都是事务提交的关键依据）。
• 过程：
  1. InnoDB 写入 Redo Log，标记为prepare状态。
  2. Server 层写入 Binlog。
  3. 提交事务，InnoDB 将 Redo Log 标记为commit状态。
• 如果崩溃恢复时发现 Redo Log 处于 prepare 状态，就去检查 Binlog 是否完整，完整则提交，否则回滚。

25. MySQL 的 WAL 机制是什么？

• Write-Ahead Logging（预写日志）。
• 核心思想：在数据写入磁盘之前，先将修改操作记录到日志（Redo Log）中。
• 好处：将随机写磁盘（修改数据页）变成了顺序写磁盘（写日志），极大提升了数据库的写入性能。脏页可以后台慢慢刷盘。

五、优化与架构篇

26. 如何排查慢 SQL？

1. 开启慢查询日志：slow_query_log=ON，设置long_query_time。
2. 使用mysqldumpslow或pt-query-digest分析慢日志，找出 Top SQL。
3. 使用EXPLAIN分析 SQL 执行计划（重点看 type, key, rows, Extra）。
4. 使用SHOW PROFILE分析 SQL 在 CPU、IO 上的消耗。
5. 检查 Schema 设计、索引设计、业务逻辑。

-- 找出执行次数最多的慢查询（高频慢 SQL） mysqldumpslow-sc-t10/var/log/mysql/slow.log -- 找出单次执行最耗时的慢查询（最慢 SQL） mysqldumpslow-st-t10/var/log/mysql/slow.log -- 找出锁等待时间最长的 SQL mysqldumpslow-sl-t10/var/log/mysql/slow.log

27. 大表分页优化（Limit 100000, 10）？

• 问题：MySQL 需要扫描前 100010 条记录，然后丢弃前 100000 条，效率极低。
• 优化方案：
  1. 覆盖索引 + 子查询：SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 100000, 1) LIMIT 10;
  2. 利用自增主键：SELECT * FROM table WHERE id > 100000 LIMIT 10;（前提是 id 连续且无断层）。
  3. 延迟关联：先查主键，再回表。

28. 主从复制的原理？

1. 主库（Master）：将数据变更写入Binary Log（Binlog）。
2. 从库（Slave）：I/O Thread 连接到主库，读取 Binlog，写入本地的Relay Log（中继日志）。
3. 从库：SQL Thread 读取 Relay Log，重放执行，更新数据。
4. 核心：异步复制（默认）。

29. 主从延迟的原因及解决方案？

• 原因：主库并发高，从库单线程重放（SQL Thread）；从库硬件配置不如主库；网络延迟；大事务（如大批量 DELETE/UPDATE）。
• 解决：
  • 使用并行复制（MySQL 5.7+ 支持基于组提交的并行复制）。
  • 升级从库硬件。
  • 拆分大事务。
  • 强制读主库（业务妥协）。

30. 什么情况下需要分库分表？分表策略？

• 什么时候分：单表数据量超过千万级；数据库成为性能瓶颈；索引效率下降；单机磁盘不足。
• 垂直拆分：
  • 垂直分库：按业务拆分（用户库、订单库）。
  • 垂直分表：大表拆小表（基础信息表 + 扩展信息表），减少单行大小，提升 Buffer Pool 利用率。
• 水平拆分：
  • 数据量大，按某种规则（Hash、Range、日期）将数据分布到多个库/表中。
• 带来的问题：分布式事务、跨库 Join、全局主键 ID 生成、分页/排序困难。