【Java 面试 八股文】MySQL 篇

1. MySQL中，如何定位慢查询？

候选人：
嗯，我们当时在做压力测试时发现有些接口响应时间非常慢，超过了2秒。因为我们的系统部署了运维监控系统 Skywalking，在它的报表展示中可以看到哪个接口慢，并且能分析出接口中哪部分耗时较多，包括具体的SQL执行时间，这样就能定位到出现问题的SQL。

如果没有这种监控系统，MySQL本身也提供了 慢查询日志功能。可以在MySQL的系统配置文件中开启慢查询日志（调试阶段），并设置SQL执行时间超过多少就记录到日志文件，比如我们之前项目设置的是2秒，超过这个时间的SQL就会记录在日志文件中，我们就可以在那里找到执行慢的SQL。

2. 那这个SQL语句执行很慢，如何分析呢？

候选人：如果一条SQL执行很慢，我们通常会使用MySQL的 EXPLAIN 命令来分析这条SQL的执行情况。

• 通过 key 和 key_len 可以 检查是否命中了索引，如果已经添加了索引，也可以判断索引是否有效。
• 通过 type 字段可以查看SQL 是否有优化空间，比如是否存在全索引扫描或全表扫描。
• 通过extra 建议可以判断是否出现回表情况，如果出现，可以尝试添加索引或修改返回字段来优化。

列名解释：

• id：查询中每个操作的唯一标识符，显示操作的顺序。
• select_type：查询类型，如 SIMPLE（简单查询）、PRIMARY（主查询）等。
• table：表名，显示正在操作的表。
• type：连接类型，指示该操作扫描表的方式（例如 ALL、index、range 等）。
• possible_keys：查询中可能使用的索引。
• key：实际使用的索引。
• key_len：使用的索引的长度。
• ref：表示连接条件，通常是列名或常数。
• rows：扫描的行数。
• filtered：在执行过程中，过滤掉的行百分比。
• Extra：额外信息，例如是否使用了文件排序、临时表等。
• Actual Time：每个操作的实际执行时间（包括启动时间和结束时间），帮助分析性能瓶颈。

3. 了解过索引吗？（什么是索引）

候选人：嗯，索引在项目中非常常见，它是一种帮助MySQL 高效获取数据 的 数据结构，主要用来提高数据检索效率，降低数据库的I/O成本。同时，索引列可以对数据进行排序，降低数据排序的成本，也能减少CPU的消耗。

4. 索引的底层数据结构了解过吗？

候选人：MySQL的默认存储引擎 InnoDB 使用的是 B+树 作为索引的存储结构。选择B+树的原因包括：

• 节点可以有更多子节点，路径更短；
• 磁盘读写代价更低，非叶子节点只存储键值和指针，叶子节点存储数据；
• B+树适合范围查询和扫描，因为叶子节点形成了一个双向链表。

5. 为什么MySQL采用B+树作为索引

要设计一个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/0 的操作次数。

• 哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O(1)）。
  • 通过哈希算法（散列算法），我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。
  • 哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。
  • 一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。
  • Hash 索引不支持顺序和范围查询。
• 二分查找树 虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致二分查找树 退化成一个链表，此时查询复杂度就会从 O(logn)降低为 O(n)。
• 自平衡二叉树（AVL树），保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。
• 树的高度决定于磁盘 I/O 操作的次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

B树和B+树的主要区别在于：

1. B树的非叶子节点和叶子节点都存放数据，而B+树的所有 数据只出现在叶子节点，这使得B+树在查询时效率更稳定。
   • B 树内部节点既存键（索引）又存数据，导致单个节点能容纳的键数量较少（阶数小）。
   • B+ 树 ​内部节点不存数据 → 阶数更大 → 树更矮 → I/O 更少
   • B+ 树的阶数 m ≈ 页大小 / (key 大小 + 指针大小)
2. B+树在进行范围查询时效率更高，因为所有数据都在叶子节点，并且 叶子节点之间形成了双向链表。而 B 树要实现范围查询，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。
3. B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化
   • B+树 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）
   • B+树 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；

6. 什么是聚簇索引什么是非聚簇索引？

候选人：
聚簇索引 （聚集索引）是指 数据与索引放在一起，B+树的叶子节点保存了整行数据，通常只有一个聚簇索引，一般是由主键构成。
非聚簇索引（二级索引）则是数据与索引分开存储，B+树的叶子节点保存的是主键值，可以有多个非聚簇索引，通常我们自定义的索引都是非聚簇索引。

7. 知道什么是回表查询吗？

候选人：回表查询是指通过二级索引找到对应的主键值，然后再通过主键值查询聚簇索引中对应的整行数据的过程。

8. 知道什么叫覆盖索引吗？

候选人：覆盖索引是指在SELECT查询中，返回的列全部能在索引中找到，避免了回表查询，提高了性能。 使用覆盖索引可以减少对主键索引的查询次数，提高查询效率。

9. MySQL超大分页怎么处理？

候选人：超大分页通常发生在数据量大的情况下，使用LIMIT分页查询且需要排序时效率较低。可以通过 覆盖索引 和 子查询 来解决。首先查询数据的ID字段进行分页，然后根据ID列表用子查询来过滤只查询这些ID的数据，因为查询ID时使用的是覆盖索引，所以效率可以提升。

10. 索引创建原则有哪些？

候选人：创建索引的原则包括：

• 表中的数据量超过10万以上时考虑创建索引。
• 选择查询频繁的字段作为索引，如查询条件、排序字段或分组字段。
• 尽量使用复合索引，覆盖SQL的返回值。
• 如果字段区分度不高，可以将其放在组合索引的后面。
• 对于内容较长的字段，考虑使用前缀索引。
• 控制索引数量，因为索引虽然可以提高查询速度，但也会影响插入、更新的速度。

11. 什么情况下索引会失效？

候选人：索引可能在以下情况下失效：

• 没有遵循最左匹配原则。 指的是查询从索引的最左前列开始，并且不跳过索引中的列。
• 使用了模糊查询且%号在前面。
• 在索引字段上进行了运算或类型转换。
• 使用了复合索引但在中间使用了范围查询，导致右边的条件索引失效。
• 字符串不加单引号，导致类型转换。

通常情况下，想要判断 SQL 是否有索引失效的情况，可以使用 explain 执行计划来分析。

12. SQL的优化经验有哪些？

候选人：SQL优化可以从以下几个方面考虑：

• 表的设计优化，建表时选择合适的字段类型。
• 索引优化，遵循创建索引的原则。
• SQL语句优化，比如避免使用SELECT *，尽量使用UNION ALL代替UNION，以及在表关联时使用INNER JOIN。
• 采用主从复制和读写分离提高性能。
• 在数据量大时考虑分库分表。

13. 创建表的时候，你们是如何优化的呢？

候选人：创建表时，我们主要参考《嵩山版》开发手册，选择字段类型时结合字段内容，比如数值类型选择TINYINT、INT、BIGINT等，字符串类型选择CHAR（定长效率高）、VARCHAR（可变长度效率稍低）或TEXT。

14. 在使用索引的时候，是如何优化呢？

候选人：在使用索引时，我们 遵循索引创建原则，确保索引字段是查询频繁的，使用复合索引覆盖SQL返回值，避免在索引字段上进行运算或类型转换，以及控制索引数量。

15. 你平时对SQL语句做了哪些优化呢？

候选人：我对SQL语句的优化包括

• 指明字段名称而不是使用SELECT *
• 避免造成索引失效的写法
• 聚合查询时使用UNION ALL代替UNION
• 表关联时优先使用INNER JOIN，以及在必须使用LEFT JOIN或RIGHT JOIN时，确保小表作为驱动表。

16. 事务的特性是什么？可以详细说一下吗？

候选人：事务的特性是 ACID，即 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。例如，A向B转账500元，这个操作要么都成功，要么都失败，体现了原子性。转账过程中数据要保持一致，A扣除了500元，B必须增加500元。隔离性体现在A向B转账时，不受其他事务干扰。持久性体现在事务提交后，数据要被持久化存储。

17. 并发事务带来哪些问题？

候选人：并发事务可能导致脏读、不可重复读和幻读。

• 脏读是指一个事务读到了另一个事务未提交的“脏数据”。
• 不可重复读是指在一个事务内多次读取同一数据，由于其他事务的修改导致数据不一致。
• 幻读是指一个事务读取到了其他事务插入的“幻行”。

18. 怎么解决这些问题呢？MySQL的默认隔离级别是？

候选人：解决这些问题的方法是使用 事务隔离。MySQL支持四种隔离级别：

1. 未提交读（READ UNCOMMITTED）：解决不了所有问题。
2. 读已提交（READ COMMITTED）：能解决脏读，但不能解决不可重复读和幻读。
3. 可重复读（REPEATABLE READ）：能解决脏读和不可重复读，但不能解决幻读，这也是MySQL的默认隔离级别。
4. 串行化（SERIALIZABLE）：可以解决所有问题，但性能较低。

19. undo log和redo log的区别是什么？

候选人：
redo log记录的是数据页的物理变化，用于服务宕机后的恢复，保证事务的持久性。
undo log记录的是逻辑日志，用于事务回滚时恢复原始数据，保证事务的原子性和一致性。

20. 事务中的隔离性是如何保证的呢？（你解释一下MVCC）

候选人：事务的隔离性通过 锁 和 多版本并发控制（MVCC） 来保证。MVCC通过维护数据的多个版本来避免读写冲突。底层实现包括 隐藏字段、undo log 和 read view。

• 隐藏字段包括 trx_id 和 roll_pointer。
  • trx_id （事务id），记录每一次操作的事务id，是自增的。
  • roll_pointer（回滚指针），指向上一个版本的事务版本记录地址。
• undo log记录了不同版本的数据，通过roll_pointer形成版本链。
  • 回滚日志，存储老版本数据。
  • 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过 roll_pointer 形成一个链表。
• read view 定义了不同隔离级别下的快照读，决定了事务访问哪个版本的数据。
  • 根据 readView 的匹配规则和当前的一些事务 id 判断该访问哪个版本的数据。
  • 不同的隔离级别快照读是不一样的，最终的访问的结果不一样。
    • RC（读已提交）：每一次执行快照读时生成 readView
    • RR（可重复读）：仅在事务中第一次执行快照读时生成ReadView，后续复用。

21. MySQL主从同步原理是什么？

候选人：MySQL主从复制的核心是 二进制日志（Binlog）。
Binlong 记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。步骤如下：

1. 主库在事务提交时记录数据变更到 Binlog。
2. 从库读取主库的Binlog并写入中继日志（Relay Log）。
3. 从库重做中继日志中的事件，反映到自己的数据中。

22. 你们项目用过MySQL的分库分表吗？

候选人：我们采用微服务架构，每个微服务对应一个数据库，是根据业务进行拆分的，这个其实就是垂直拆分。

23. 那你之前使用过水平分库吗？

候选人：使用过。当时业务发展迅速，某个表数据量超过1000万，单库优化后性能仍然很慢，因此采用了水平分库。我们首先部署了3台服务器和3个数据库，使用mycat进行数据分片。旧数据也按照ID取模规则迁移到了各个数据库中，这样各个数据库可以分摊存储和读取压力，解决了性能问题。

24. Mysql 锁

1. 锁的分类

按锁的粒度

• ​表级锁：
  • 锁定整个表。
  • 实现简单，开销小，但并发性能低。
  • 例如：LOCK TABLES 语句。
• ​行级锁：
  • 锁定表中的特定行。
  • 并发性能高，但开销较大。
  • 例如：InnoDB 引擎的行锁。
• ​页级锁：
  • 锁定数据页（一组行）。
  • 介于表级锁和行级锁之间。
  • 例如：BDB 引擎的页锁。

按锁的类型

• ​共享锁（S 锁）​：
  • 允许多个事务同时读取同一资源。
  • 语法：SELECT ... LOCK IN SHARE MODE。
• ​排他锁（X 锁）​：
  • 只允许一个事务读取或修改资源，其他事务无法访问。
  • 语法：SELECT ... FOR UPDATE。
• ​意向锁：
  • 意向锁是表级锁，共有两种：
    • 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
    • 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。
  • 意向锁是由数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享/排他锁之前，InnoDB 会先获取该数据行所在在数据表的对应意向锁。意向锁之间是互相兼容的。

2. 常用锁机制

表级锁

• ​显式锁：
  • 使用 LOCK TABLES 语句手动锁定表。
  • 例如：LOCK TABLES table_name READ/WRITE。
• ​隐式锁：
  • 在执行某些操作时自动加锁，例如 ALTER TABLE。

行级锁（InnoDB）

• ​记录锁（Record Lock）​：
  • 锁定单行记录。
• ​间隙锁（Gap Lock）​：
  • 锁定索引记录之间的间隙，防止其他事务插入。
• ​临键锁（Next-Key Lock）​：
  • 记录锁 + 间隙锁，锁定一个范围。

死锁

• ​定义：
  • 多个事务相互等待对方释放锁，导致无法继续执行。
• ​解决方案：
  • MySQL 会自动检测死锁并回滚其中一个事务。
  • 避免死锁：按固定顺序访问资源，减少事务持有锁的时间。

3. 锁的使用场景

共享锁（S 锁）

• 适用于只读操作，允许多个事务同时读取数据。
• 例如：SELECT * FROM table_name LOCK IN SHARE MODE。

排他锁（X 锁）

• 适用于写操作，确保数据一致性和完整性。
• 例如：SELECT * FROM table_name FOR UPDATE。

间隙锁（Gap Lock）

• 防止幻读，锁定索引记录的间隙。
• 例如：SELECT * FROM table_name WHERE id BETWEEN 10 AND 20 FOR UPDATE。

25. 行锁升级为表锁

InnoDB 的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。

26. 数据库三大范式

第一范式（1NF）​

• ​确保每列的原子性：每一列都是不可再分的基本数据项，不能包含集合、数组或重复的属性。
• 每行数据唯一：每一行数据必须唯一，通常通过主键（Primary Key）实现。

第二范式（2NF）

• ​满足第一范式。
• ​消除部分依赖：非主键列必须完全依赖于主键，而不是依赖于主键的一部分（适用于复合主键的情况）。

第三范式（3NF）​

• ​满足第二范式。
• ​消除传递依赖：非主键列之间不能存在依赖关系，所有非主键列必须直接依赖于主键。

范式	核心要求	作用
1NF	每列原子性，每行唯一	确保数据的原子性
2NF	消除部分依赖	减少数据冗余，避免更新异常
3NF	消除传递依赖	进一步提高数据一致性

27. MySQL 字段类型

MySQL 字段类型可以简单分为三大类：

• 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
• 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
• 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

CHAR 和 VARCHAR 的区别

• CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。
• CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。
• VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

DECIMAL 和 FLOAT/DOUBLE 的区别是什么？

• DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

为什么 MySQL 不建议使用 NULL 作为列默认值？
NULL 和 ‘’ (空字符串) 是两个完全不同的值，它们分别表示不同的含义，并在数据库中有着不同的行为。NULL 代表缺失或未知的数据，而 ‘’ 表示一个已知存在的空字符串。

• 存储空间问题
  • NULL 占用额外空间：在 InnoDB 中，NULL 值会占用额外的存储空间（1 bit 的标记位 + 可能的空间对齐开销），而 NOT NULL 的列如果使用默认值（如空字符串 ‘’ 或数字 0），可能更节省空间。
  • 对于可变长字段（如 VARCHAR），NULL 需要额外的字节来标记是否为 NULL，而空字符串 ‘’ 只需存储长度信息（0字节）。
  • 如果表中有大量 NULL 列，会浪费存储空间，影响内存和磁盘利用率。
• 查询性能影响
  • ​NULL 导致索引失效：如果列允许 NULL 且未显式指定值，默认 NULL 可能不会被索引高效处理（尤其是复合索引）。例如，索引 (a, b) 中如果 b 为 NULL，则 WHERE a=1 AND b IS NULL 可能无法充分利用索引。
  • ​比较运算复杂化：NULL 的语义是“未知值”，与任何值的比较（如 =, <, >）结果都是 NULL（即 UNKNOWN），必须用 IS NULL 或 IS NOT NULL 判断，增加查询复杂度。例如：WHERE column = NULL 不会返回任何结果，必须写成 WHERE column IS NULL。
• 聚合函数与计算问题
  • 聚合函数忽略 NULL：COUNT(column) 会忽略 NULL 值，可能导致统计结果不符合预期。
    例如，COUNT(*) 和 COUNT(column) 的结果可能不同。

为什么不建议在实际生产项目中使用外键

​场景	​使用外键	​不使用外键
​数据一致性	​ 数据库自动保证	​依赖程序代码维护
​写入性能	​较差（每次操作检查约束）	​更高（无约束检查）
​删除主表数据	​必须级联删除或先删从表	​可直接删除（可能产生脏数据）
​分库分表	​不支持	​支持
​适用场景	​单体应用、强一致性系统（如银行）	​高并发、分布式系统（如电商、社交）

28. Mysql 存储引擎

一个 SQL 语句在 MySQL 中的执行流程

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）。
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行。
• 执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎：主要负责数据的存储和读取，采用的是 插件式架构，支持多种存储引擎，我们可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

MyISAM 和 InnoDB 有什么区别？

• 是否支持行级锁
  • MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
• 是否支持事务
  • MyISAM 不提供事务支持。
  • InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别
• 是否支持数据库异常崩溃后的安全恢复
  • MyISAM 不支持，而 InnoDB 支持。
  • 使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。
• 索引结构
  • InnoDB是聚簇索引，MyISAM是非聚簇索引。
  • 聚簇索引|的文件存放在主键索引|的叶子节点上，因此InnoDB必须要有主键，通过主键索引效率很高。
  • 而MyISAM是非聚簇索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
• count 的效率
  • InnoDB不保存表的具体行数，执行 select count() from table 时需要全表扫描。
  • MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快。