MySQL 面试题精讲

本文档面向 Java 后端开发者，涵盖 MySQL 高频面试题 42 道，按模块分类，每题提供三层答案。

难度标识： ⭐ 基础 | ⭐⭐ 中等 | ⭐⭐⭐ 高频重点 | ⭐⭐⭐⭐ 进阶深入

答案层次：

  • 🎯 面试直答版 —— 30 秒内说完，简洁有力
  • 📖 深度解析版 —— 展示完整理解，防追问
  • 💡 加分项 —— 体现深度思考或实战经验

特殊标记： ⚠️ 易错点 | 🔥 高频追问

模块一：基础概念（8 题）

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

🎯 面试直答版

MySQL 常见存储引擎有 InnoDB、MyISAM、Memory、Archive 等。InnoDB 是默认引擎，支持事务、行级锁和外键；MyISAM 不支持事务，只有表级锁，但读性能好、占用空间小。现在基本都用 InnoDB。

📖 深度解析版

对比维度InnoDBMyISAM
事务支持 ACID不支持
锁粒度行级锁（默认）表级锁
外键支持不支持
崩溃恢复通过 redo log 支持无，需修复工具
索引结构聚簇索引，数据和主键索引在一起非聚簇索引，索引和数据文件分离
全文索引5.6+ 支持支持
存储文件.frm + .ibd.frm + .MYD + .MYI
COUNT(*)需要遍历（MVCC 下无法直接存总数）直接存储行数，非常快

InnoDB 的核心优势在于：通过 redo log 保证持久性，通过 undo log + MVCC 实现事务隔离，通过行级锁提高并发性能。

💡 加分项

实际生产中几乎不会用 MyISAM。MySQL 8.0 已经把系统表也从 MyISAM 改为 InnoDB 了。选择 MyISAM 的唯一场景是只读或极少写的归档数据表，因为它压缩比更好。另外，InnoDB 的 COUNT(*) 慢是因为 MVCC 机制下不同事务看到的行数不一样，所以无法维护一个全局计数器。

2. VARCHAR 和 CHAR 的区别？VARCHAR(50) 和 VARCHAR(200) 存同样内容有区别吗？

🎯 面试直答版

CHAR 是定长，VARCHAR 是变长。CHAR(10) 固定占 10 个字符空间，VARCHAR(10) 按实际长度存储并额外用 1-2 字节记录长度。VARCHAR(50) 和 VARCHAR(200) 存同样的内容，磁盘占用一样，但 VARCHAR(200) 在内存中可能分配更多空间。

📖 深度解析版

  • CHAR(N)：固定 N 个字符，不足右补空格，查询时尾部空格被去掉。适合长度固定的数据（如 MD5 值、手机号）。
  • VARCHAR(N)：变长，额外使用 1 字节（N <= 255）或 2 字节（N > 255）记录实际长度。
  • VARCHAR(50) vs VARCHAR(200) 的隐藏区别：
    • 磁盘存储：相同内容占用空间一样。
    • 内存分配：MySQL 在排序（ORDER BY）、创建临时表时，会按照声明的最大长度分配内存。VARCHAR(200) 会分配更大的内存缓冲区。
    • 索引限制：InnoDB 单列索引最大 767 字节（utf8mb4 下约 191 个字符），定义过大会影响索引创建。

⚠️ 易错点：VARCHAR(N) 中 N 的单位是字符不是字节。在 utf8mb4 编码下，一个字符最多占 4 字节。

💡 加分项

所以定义 VARCHAR 时不要图省事写 VARCHAR(9999)，应该按业务需求合理定义长度。这不仅影响内存使用，还影响 SELECT * 的执行效率和临时表的大小。实际开发中，手机号用 CHAR(11) 或 VARCHAR(20)，昵称用 VARCHAR(64) 就够了。

3. DATETIME 和 TIMESTAMP 的区别？

🎯 面试直答版

DATETIME 占 8 字节，范围从 1000 年到 9999 年，不受时区影响，存什么取什么。TIMESTAMP 占 4 字节，范围从 1970 年到 2038 年，存储时转换为 UTC，查询时转换回当前时区。

📖 深度解析版

对比维度DATETIMETIMESTAMP
存储空间8 字节4 字节
范围1000-01-01 ~ 9999-12-311970-01-01 ~ 2038-01-19
时区不转换，存什么取什么存储转 UTC，查询转回当前时区
默认值无可设置 CURRENT_TIMESTAMP，自动更新
NULL允许 NULL早期版本不允许，5.6+ 允许

⚠️ 易错点：TIMESTAMP 有 2038 年问题。如果系统需要存储 2038 年之后的时间，必须用 DATETIME。

💡 加分项

在有国际化需求的系统中，TIMESTAMP 更合适，因为它会自动处理时区转换。但很多公司的做法是统一用 BIGINT 存毫秒级时间戳，在应用层处理时区，这样最灵活也最不容易出问题。MySQL 8.0.28+ 开始支持 TIMESTAMP 到 3000 年以上的范围（具体取决于版本），但为了兼容性很多团队仍然选择 DATETIME 或 BIGINT。

4. MySQL 的三大范式是什么？实际开发中要严格遵守吗？

🎯 面试直答版

第一范式：字段不可再分（原子性）。第二范式：非主键字段完全依赖主键，不能只依赖主键的一部分。第三范式：非主键字段不能依赖其他非主键字段（消除传递依赖）。实际开发中不会严格遵守，适当冗余可以避免多表 JOIN，提升查询性能。

📖 深度解析版

  • 1NF：每一列都是不可分割的基本数据项。比如”地址”不应该包含省市区，应该拆成省、市、区三个字段。
  • 2NF：在 1NF 基础上，非主键列必须完全依赖于整个主键，而不是主键的一部分。主要针对联合主键，比如（学号，课程号）-> 成绩是满足的，但（学号，课程号）-> 学生姓名不满足，因为学生姓名只依赖学号。
  • 3NF：在 2NF 基础上，非主键列之间不能有依赖关系。比如学生表中不应该同时存”班级ID”和”班级名称”，因为班级名称依赖班级ID。

反范式设计的常见场景：

  • 订单表冗余商品名称和价格（因为商品信息可能变更，订单需要记录下单时的快照）
  • 用户表冗余部门名称（避免频繁 JOIN）
  • 宽表设计用于报表查询

💡 加分项

实际项目中遵循的原则是”三范式为基础，适度冗余为优化”。OLTP 系统通常遵守到 3NF，适当冗余频繁查询的字段；OLAP 系统则大量使用反范式的宽表设计。关键是冗余后要考虑数据一致性问题，通常通过消息队列异步同步或定时任务对账来保证。

5. 一条 SQL 查询语句在 MySQL 中的执行流程？

🎯 面试直答版

客户端连接 -> 连接器验证权限 -> 查询缓存（8.0 已移除）-> 解析器做词法和语法分析 -> 优化器生成执行计划 -> 执行器调用存储引擎接口获取数据 -> 返回结果。

📖 深度解析版

MySQL 架构分为 Server 层 和 存储引擎层：

Server 层包含：

  1. 连接器：管理连接、验证身份、获取权限。长连接可能导致内存占用过大（OOM），可以定期断开长连接或执行 mysql_reset_connection 重置。
  2. 查询缓存（8.0 已删除）：以 SQL 文本为 key 缓存结果，命中率极低因为任何表更新都会清空该表所有缓存。
  3. 解析器：词法分析识别关键字和表名列名，语法分析检查 SQL 是否合法，生成语法树。
  4. 优化器：决定使用哪个索引，决定 JOIN 的顺序，生成执行计划。基于成本模型（Cost-Based Optimizer）选择最优方案。
  5. 执行器：校验权限，调用存储引擎的接口逐行获取数据或批量读取。

存储引擎层：

  • 负责数据的存储和读取，提供统一的 API 接口。
  • InnoDB 在这一层处理 Buffer Pool、redo log、undo log 等。

💡 加分项

可以补充一个写入语句的流程：执行器调用 InnoDB 接口写数据 -> 先写 undo log（用于回滚）-> 更新 Buffer Pool 中的数据页 -> 写 redo log（prepare 状态）-> 写 binlog -> redo log 改为 commit 状态。这就是两阶段提交，保证 redo log 和 binlog 的一致性。

6. MySQL 中 NULL 值需要注意什么？

🎯 面试直答版

NULL 参与比较运算结果为 NULL 而非 true/false；聚合函数会忽略 NULL（COUNT(字段) 不统计 NULL）；NULL 需要 IS NULL / IS NOT NULL 来判断；索引可以包含 NULL 值但会影响查询优化。建议字段尽量设为 NOT NULL 并给默认值。

📖 深度解析版

NULL 的坑点很多：

  1. 比较运算：NULL = NULL 结果是 NULL，不是 true。要用 IS NULL 或 <=> 安全等于运算符。
  2. 逻辑运算：NULL AND true = NULL，NULL OR true = true，NOT NULL = NULL。
  3. 聚合函数：SUM、AVG、COUNT(字段) 都忽略 NULL 行。COUNT(*) 不忽略。
  4. DISTINCT 和 GROUP BY：认为多个 NULL 是相同的值。
  5. 索引影响：InnoDB 允许在唯一索引列上存多个 NULL（因为 NULL != NULL）。但 WHERE col != 'abc' 不会返回 col 为 NULL 的行。
  6. 空间占用：NULL 在 InnoDB 的行格式中需要额外的 NULL 标志位（每 8 个可空列占 1 字节）。

⚠️ 易错点：NOT IN 子查询如果子查询结果包含 NULL，整个 NOT IN 结果为空集。例如 WHERE id NOT IN (1, 2, NULL) 不会返回任何行。

💡 加分项

阿里 Java 开发手册明确要求：数据库字段尽量设为 NOT NULL，用空字符串或 0 作为默认值。原因除了上面的逻辑陷阱外，NOT NULL 字段在索引统计和空间利用上也更优。

7. 什么是存储过程？为什么互联网公司很少用？

🎯 面试直答版

存储过程是预编译的 SQL 语句集合，存在数据库端执行。互联网公司很少用，主要因为：难以调试和版本控制、不利于分库分表和水平扩展、业务逻辑耦合在数据库端加重数据库负担、可移植性差。

📖 深度解析版

存储过程的优点：

  • 减少网络传输，多条 SQL 一次调用
  • 预编译执行，理论上更快
  • 可以封装复杂逻辑，减少应用端代码

互联网公司不用的原因：

  1. 运维成本高：存储过程修改需要 DBA 介入，无法像应用代码一样走 CI/CD 流程，无法代码 review。
  2. 调试困难：MySQL 没有好的存储过程调试工具，出了 bug 排查困难。
  3. 扩展性差：分库分表后，存储过程无法跨库执行，水平扩展非常麻烦。
  4. 数据库压力大：互联网架构的核心思想是把计算逻辑放在应用层（可以水平扩展），数据库只做存储和简单查询。
  5. 可移植性差：不同数据库的存储过程语法差异大，换库成本高。

💡 加分项

现在的主流思路是”让数据库做它擅长的事（存储和查询），复杂业务逻辑放在应用层”。数据库是有状态服务，扩展成本远高于无状态的应用服务器。不过在传统金融行业、ERP 系统中存储过程仍然大量使用，因为这些系统数据量相对可控且对事务一致性要求极高。

8. COUNT(*), COUNT(1), COUNT(字段) 的区别？

🎯 面试直答版

在 InnoDB 中，COUNT() 和 COUNT(1) 性能一样，都是统计总行数，MySQL 做了专门优化。COUNT(字段) 只统计该字段非 NULL 的行数，如果字段没有索引会全表扫描。推荐使用 COUNT()。

📖 深度解析版

  • COUNT(*)：统计所有行数，包括 NULL 行。MySQL 优化器会选择最小的索引树来遍历（不一定是主键索引），因为只需要计数不需要取值。
  • COUNT(1)：和 COUNT(*) 完全等价，MySQL 内部做了优化，不会真的对每行去判断常量 1。
  • COUNT(字段)：只统计该字段不为 NULL 的行数。如果字段有索引用索引树遍历，没有索引就全表扫描。

性能排序：COUNT(*) = COUNT(1) > COUNT(主键) > COUNT(普通字段)

⚠️ 易错点：很多人以为 COUNT(1) 比 COUNT(*) 快，这是错误的。MySQL 官方文档明确说明两者等价。

💡 加分项

如果表数据量极大（千万级），COUNT(*) 也很慢，常见优化方案：

  1. 使用 SHOW TABLE STATUS 获取近似值（不精确）。
  2. 用 Redis 缓存计数，增删时同步更新。
  3. 单独维护一张计数表，在事务中同步更新。
  4. 如果允许误差，用 EXPLAIN 中的 rows 估算值。

模块二：索引（10 题）

9. 什么是 B+ 树？为什么 MySQL 用 B+ 树而不用 B 树、红黑树、Hash？

🎯 面试直答版

B+ 树是一种多路平衡搜索树，非叶子节点只存索引不存数据，所有数据都在叶子节点，叶子节点之间用双向链表连接。MySQL 用 B+ 树因为：树矮（减少磁盘 IO）、叶子节点有序链表支持范围查询、非叶子节点不存数据所以一个节点能存更多索引项。

📖 深度解析版

B+ 树 vs B 树：

  • B 树非叶子节点也存数据，导致每个节点能存的 key 更少，树更高，IO 次数更多。
  • B+ 树非叶子节点只存 key 和指针。假设一个页 16KB，主键 BIGINT 8 字节，指针 6 字节，一个非叶子节点可存约 1170 个 key。两层就能索引 1170 x 1170 = 137 万条记录，三层就能索引约 16 亿条数据。所以一般 B+ 树 3-4 层就够了，也就是 3-4 次磁盘 IO。
  • B+ 树叶子节点用双向链表连接，范围查询非常高效。B 树做范围查询需要中序遍历。

B+ 树 vs 红黑树/AVL 树：

  • 红黑树是二叉树，数据量大时树高很大（千万数据约 23 层），IO 次数太多。
  • B+ 树是多叉树，高度通常 3-4 层。

B+ 树 vs Hash 索引：

  • Hash 索引等值查询 O(1) 很快，但不支持范围查询、排序、最左前缀匹配。
  • 存在 Hash 冲突问题。
  • InnoDB 有自适应哈希索引（AHI），会自动对热点数据建立 Hash 索引作为 B+ 树索引的补充。

💡 加分项

InnoDB 的每个节点大小为一个页（默认 16KB），这和操作系统的页大小对齐可以减少随机 IO。而且 InnoDB 的预读机制（read-ahead）会预加载相邻的页，进一步减少 IO。另外可以提到自适应哈希索引（AHI）：InnoDB 会监控索引的使用模式，对频繁访问的索引页自动建立 Hash 索引，这是 InnoDB 内部的优化，用户无法手动控制。

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

🎯 面试直答版

聚簇索引的叶子节点存的是整行数据，InnoDB 的主键索引就是聚簇索引，一个表只能有一个。非聚簇索引（二级索引/辅助索引）的叶子节点存的是主键值，查到主键后需要回表去聚簇索引查完整数据。

📖 深度解析版

聚簇索引的选择规则：

  1. 如果定义了主键，主键就是聚簇索引。
  2. 如果没有主键，选择第一个非空的唯一索引。
  3. 如果都没有，InnoDB 会隐式生成一个 6 字节的 row_id 作为聚簇索引。

聚簇索引的特点：

  • 数据和索引存储在一起，按主键顺序物理排列。
  • 主键查询非常快，不需要回表。
  • 插入数据时最好按主键递增顺序插入（如自增ID），否则会导致页分裂，影响性能。

非聚簇索引的特点：

  • 叶子节点存储的是 (索引列值, 主键值)。
  • 查询时如果索引列不能覆盖所有需要的字段，需要通过主键值回表。
  • 一个表可以有多个非聚簇索引。

⚠️ 易错点：MyISAM 的主键索引也是非聚簇的，索引文件和数据文件是分开的，叶子节点存的是数据的物理地址。所以 MyISAM 不存在”回表”的概念，所有索引都是”非聚簇”的。

💡 加分项

这也是为什么 InnoDB 推荐使用自增主键的原因：自增保证了数据按顺序插入，避免频繁的页分裂和数据移动。用 UUID 做主键会导致随机插入，大量页分裂，写入性能差且空间利用率低。但如果是分布式系统需要全局唯一 ID，可以考虑雪花算法（Snowflake），它生成的 ID 大致递增。

11. 什么是回表？怎么优化？

🎯 面试直答版

回表是指通过非聚簇索引查到主键值后，再到聚簇索引（主键索引）中查找完整行数据的过程。优化方式：使用覆盖索引（让查询的字段都在索引中）、减少 SELECT *、利用索引下推。

📖 深度解析版

回表的过程：

SELECT name, age FROM user WHERE phone = '13800138000';
-- 假设 phone 上有普通索引
  1. 在 phone 的 B+ 树上查找 ‘13800138000’，找到对应的主键值（如 id=5）。
  2. 拿着 id=5 到聚簇索引（主键索引）的 B+ 树上查找完整行数据。
  3. 从完整行数据中取出 name 和 age 返回。

第二步就是回表，每找到一条记录就要回表一次。如果查询结果集很大，回表次数就很多，性能会很差。

优化方案：

  1. 覆盖索引：创建联合索引 (phone, name, age)，索引中已经包含了需要查询的所有字段，不需要回表。
  2. 减少 SELECT 的字段：不要 SELECT *，只查需要的字段，更容易命中覆盖索引。
  3. 索引下推（ICP）：MySQL 5.6 引入，在索引遍历过程中先过滤不满足条件的记录，减少回表次数。

💡 加分项

判断是否发生了回表可以看 EXPLAIN 的 Extra 列：如果出现 Using index 说明用了覆盖索引没有回表；如果没有这个标记，很可能存在回表。另外 MySQL 优化器会评估回表的代价，如果预估回表次数太多（比如需要回表查大量数据），优化器可能放弃使用二级索引而选择全表扫描，因为顺序读全表可能比大量随机回表更快。

12. 什么是覆盖索引？

🎯 面试直答版

覆盖索引是指查询的字段全部包含在某个索引中，不需要回表查聚簇索引就能拿到所有数据。它不是一种索引类型，而是一种索引使用方式。EXPLAIN 中 Extra 列显示 Using index 就表示用到了覆盖索引。

📖 深度解析版

-- 假设有联合索引 idx_name_age(name, age)
-- 覆盖索引场景
SELECT name, age FROM user WHERE name = '张三';
-- Extra: Using index（覆盖索引，无需回表）

-- 非覆盖索引场景
SELECT name, age, phone FROM user WHERE name = '张三';
-- 需要回表，因为 phone 不在索引中

覆盖索引的好处：

  • 避免回表，减少 IO 次数，性能提升显著。
  • 由于二级索引通常比聚簇索引小很多，扫描的数据量更少。

设计覆盖索引的原则：

  • 把 WHERE 条件中的列和 SELECT 中的列一起建联合索引。
  • 不要贪多，索引字段太多会增加写入负担和存储空间。
  • 高频查询优先考虑覆盖索引优化。

💡 加分项

实际工作中一个常见优化场景：分页查询的深分页优化。SELECT * FROM t ORDER BY id LIMIT 1000000, 10 非常慢，可以改为先用覆盖索引拿到 ID，再回表：SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10) AS tmp ON t.id = tmp.id。子查询走覆盖索引非常快。

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

🎯 面试直答版

索引下推（Index Condition Pushdown）是 MySQL 5.6 引入的优化，在使用联合索引时把部分 WHERE 条件的过滤下推到存储引擎层，在索引遍历时直接过滤不符合条件的记录，减少回表次数。

📖 深度解析版

-- 假设有联合索引 idx_name_age(name, age)
SELECT * FROM user WHERE name LIKE '张%' AND age = 20;

没有 ICP 时（MySQL 5.6 之前）：

  1. 存储引擎通过索引找到所有 name 以”张”开头的记录。
  2. 对每条记录都回表，取出完整行数据。
  3. Server 层再根据 age = 20 进行过滤。

有 ICP 时（MySQL 5.6+）：

  1. 存储引擎通过索引找到所有 name 以”张”开头的记录。
  2. 在索引层面直接判断 age 是否等于 20（因为 age 在联合索引中）。
  3. 只有 age = 20 的记录才回表。

这样就减少了大量不必要的回表操作。

EXPLAIN 标识：Extra 列显示 Using index condition 表示使用了索引下推。

⚠️ 易错点：ICP 只能用于二级索引，不能用于主键索引（聚簇索引不需要回表，也就无所谓下推）。

💡 加分项

ICP 默认开启，可以通过 SET optimizer_switch = 'index_condition_pushdown=off' 关闭。在实际排查性能问题时，如果发现大量回表但 EXPLAIN 没有显示 Using index condition，可以检查下 ICP 是否被关闭了，或者 WHERE 条件中的字段是否不在索引中。

14. 联合索引的最左前缀原则？

🎯 面试直答版

联合索引 (a, b, c) 遵循最左前缀原则，查询条件必须从最左列开始连续匹配才能使用索引。可以命中的组合：a、a+b、a+b+c。不能命中：b、c、b+c（跳过了 a）。但 MySQL 优化器会自动调整 WHERE 条件的顺序，所以 WHERE b=1 AND a=2 也能命中。

📖 深度解析版

联合索引 (a, b, c) 在 B+ 树中的排序规则是：先按 a 排序，a 相同的按 b 排序，b 相同的按 c 排序。

各种查询条件的索引使用情况：

查询条件是否走索引说明
WHERE a = 1是，用到 a
WHERE a = 1 AND b = 2是，用到 a, b
WHERE a = 1 AND b = 2 AND c = 3是，用到 a, b, c
WHERE b = 2否跳过了 a
WHERE b = 2 AND c = 3否跳过了 a
WHERE a = 1 AND c = 3用到 ac 无法使用，因为跳过了 b
WHERE a = 1 AND b > 2 AND c = 3用到 a, bb 用了范围查询后，c 无法使用
WHERE a = 1 ORDER BY b用到 a，且 b 排序不需要 filesort
WHERE a = 1 AND b = 2 ORDER BY c完全使用索引

⚠️ 易错点：范围查询（>、<、BETWEEN、LIKE ‘xx%‘）会导致后续列无法使用索引。但 MySQL 8.0 引入了索引跳跃扫描（Index Skip Scan），某些场景下可以跳过最左列。

💡 加分项

设计联合索引的经验：把等值查询的列放前面，范围查询的列放后面，排序的列也要考虑进去。例如 WHERE status = 1 AND create_time > '2024-01-01' ORDER BY id，可以建 (status, create_time) 索引。另外 MySQL 8.0 的索引跳跃扫描让 WHERE b = 2 在某些数据分布下也能用到 (a, b) 索引，但不能依赖这个优化。

15. 哪些情况会导致索引失效？

🎯 面试直答版

常见的索引失效场景：对索引列使用函数或运算、隐式类型转换、LIKE 以 % 开头、联合索引不满足最左前缀、OR 连接非索引列、使用 NOT IN / NOT EXISTS（部分场景）、优化器判断全表扫描更快时主动放弃索引。

📖 深度解析版

索引失效的十大场景：

  1. 对索引列使用函数：WHERE YEAR(create_time) = 2024 -> 改为 WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
  2. 对索引列做运算：WHERE id + 1 = 10 -> 改为 WHERE id = 9
  3. 隐式类型转换：字段是 VARCHAR 类型，查询条件用了数字 WHERE phone = 13800138000，MySQL 会对字段做类型转换函数，导致索引失效。
  4. 隐式字符编码转换：两表 JOIN 时字符集不一致（utf8 vs utf8mb4），会触发隐式转换。
  5. LIKE 左模糊：WHERE name LIKE '%张' 无法走索引；WHERE name LIKE '张%' 可以。
  6. 联合索引不满足最左前缀。
  7. OR 条件：WHERE a = 1 OR b = 2，如果 b 没有索引，整个查询都无法用索引。
  8. IS NOT NULL：某些情况下不走索引（取决于数据分布和优化器判断）。
  9. 范围查询数据量太大：优化器认为全表扫描更快时放弃索引。
  10. **使用 SELECT ***：可能导致优化器放弃覆盖索引。

⚠️ 易错点：WHERE varchar_col = 123 会索引失效，但 WHERE int_col = '123' 不会失效。因为 MySQL 的规则是把字符串转为数字，所以 varchar 列会被应用转换函数而失效，int 列是对值做转换，不影响索引。

💡 加分项

实际排查索引失效要养成看 EXPLAIN 的习惯。重点关注 type 列（至少要到 range 级别）和 key 列（实际使用的索引）。另外，MySQL 8.0 引入了不可见索引（Invisible Index），可以在不删除索引的情况下测试去掉索引后的影响，这在线上排查索引问题时非常有用：ALTER TABLE t ALTER INDEX idx_name INVISIBLE;

16. 什么是前缀索引？

🎯 面试直答版

前缀索引是对字符串字段的前 N 个字符建立索引，可以减小索引体积、提高索引效率。例如 ALTER TABLE t ADD INDEX idx_email(email(6)) 只对 email 的前 6 个字符建索引。缺点是不能用于 ORDER BY 和覆盖索引。

📖 深度解析版

适用场景：长字符串字段（如 email、URL）需要建索引时。

如何确定合适的前缀长度：

-- 计算完整列的区分度
SELECT COUNT(DISTINCT email) / COUNT(*) FROM user;
-- 结果：0.98

-- 计算不同前缀长度的区分度
SELECT
  COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel4,
  COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel5,
  COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS sel6,
  COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS sel7
FROM user;
-- 选择区分度接近完整列且长度尽量短的前缀

前缀索引的局限：

  • 无法用于 ORDER BY（因为索引不包含完整值，无法确定排序）。
  • 无法用作覆盖索引（同理，索引中没有完整值）。
  • 会增加回表比较的次数（前缀匹配到多条后需要回表确认完整值）。

💡 加分项

对于像身份证号这样前面几位区分度很低（因为同省同市前缀一样）的字段，有两个替代方案：一是存储倒序值再建前缀索引 ALTER TABLE t ADD INDEX idx_idcard(id_card_reverse(12))；二是新增一个哈希字段 ALTER TABLE t ADD COLUMN id_card_crc INT, ADD INDEX idx_crc(id_card_crc)，查询时 WHERE id_card_crc = CRC32('xxx') AND id_card = 'xxx'。

17. 索引不是越多越好，为什么？

🎯 面试直答版

索引虽然加快了查询，但会降低增删改的速度（需要同时维护索引的 B+ 树），占用额外的磁盘空间，而且过多的索引会让优化器在选择执行计划时更慢、甚至选错索引。一般单表索引不超过 5-6 个，单个联合索引不超过 5 个字段。

📖 深度解析版

索引过多的代价：

  1. 写入性能下降：每次 INSERT、UPDATE、DELETE 都需要维护所有索引的 B+ 树。如果一张表有 10 个索引，插入一条数据就要同时写 10 棵 B+ 树。
  2. 磁盘空间增大：索引文件有时比数据文件还大。
  3. 优化器负担加重：优化器需要分析所有可能的索引组合来选择最优执行计划，索引越多，这个过程越慢。
  4. 可能选错索引：索引过多时优化器可能选择了不是最优的索引，导致查询反而更慢。
  5. 内存占用：热点索引页需要缓存在 Buffer Pool 中，索引过多会挤占数据页的缓存空间。

什么时候该建索引：

  • WHERE 条件中频繁出现的列
  • JOIN 的关联列
  • ORDER BY / GROUP BY 用到的列
  • 选择性（区分度）高的列优先

什么时候不该建索引：

  • 数据量很小的表（几百条数据全表扫描更快）
  • 频繁更新的列
  • 选择性很低的列（如性别，只有男/女两个值）

💡 加分项

实际工作中可以通过 sys.schema_unused_indexes 查找从未使用过的索引进行清理。也可以通过慢查询日志和 pt-index-usage 工具分析索引使用情况。清理无用索引是成本很低但收益很高的优化手段。

18. 如何判断一个索引设计是否合理？

🎯 面试直答版

从查询模式出发：是否覆盖了高频查询的 WHERE、JOIN、ORDER BY 条件；区分度是否够高；是否有冗余索引或未使用的索引；写入性能是否受影响。核心指标是 EXPLAIN 的 type 至少到 ref 或 range 级别。

📖 深度解析版

评估索引合理性的几个维度：

  1. 是否满足查询需求：用 EXPLAIN 检查高频 SQL 是否走到了合适的索引，type 列是否在 const/ref/range 级别。
  2. 区分度（选择性）：SELECT COUNT(DISTINCT col) / COUNT(*) FROM t，越接近 1 越好。低于 0.1 的字段通常不值得单独建索引。
  3. 是否存在冗余索引：如已有 (a, b) 索引，又建了 (a) 索引，后者是冗余的。可以用 pt-duplicate-key-checker 工具检测。
  4. 索引使用率：通过 sys.schema_unused_indexes 或 performance_schema 分析哪些索引从未被使用。
  5. 写入影响：对比加索引前后的写入 QPS 和响应时间。
  6. 空间占用：SELECT index_name, stat_value * @@innodb_page_size FROM mysql.innodb_index_stats WHERE stat_name = 'size' 查看索引大小。

索引设计的实践原则：

  • 高频查询优先覆盖
  • 联合索引遵循等值在前、范围在后
  • 尽量利用覆盖索引
  • 长字符串考虑前缀索引
  • 定期清理无用索引

💡 加分项

在真实项目中，索引设计不是一次性的工作，需要随着业务发展和数据增长不断调整。我的经验是：上线前用 EXPLAIN 审查所有核心 SQL；上线后通过慢查询日志持续监控；每月做一次索引使用率审计。另外，MySQL 8.0 的降序索引（Descending Index）在 ORDER BY a ASC, b DESC 这类混合排序场景下非常有用。

模块三：事务与锁（8 题）

19. 事务的 ACID 特性分别靠什么实现？

🎯 面试直答版

A（原子性）靠 undo log 实现回滚；C（一致性）是目的，由其他三个特性共同保证；I（隔离性）靠 MVCC + 锁实现；D（持久性）靠 redo log 实现，即使崩溃也能恢复已提交的数据。

📖 深度解析版

特性实现机制说明
原子性 (Atomicity)undo log事务中的操作要么全部成功，要么全部回滚。每条修改操作都先写 undo log，回滚时通过 undo log 逆向恢复。
一致性 (Consistency)AID 共同保证事务执行前后数据都处于合法状态，是最终目标而非手段。应用层约束（如业务校验）也是一致性的一部分。
隔离性 (Isolation)MVCC + 锁读操作通过 MVCC（ReadView + undo log 版本链）实现无锁读；写操作通过行锁、间隙锁等保证并发安全。
持久性 (Durability)redo log事务提交时先写 redo log（WAL 机制），即使数据页还没刷盘，崩溃后也能通过 redo log 恢复。

⚠️ 易错点：很多人把一致性理解为”数据一致”，其实一致性的含义更广，包括约束条件、触发器、级联操作等都要保持数据库的合法状态。一致性是事务的目标，AID 是实现手段。

💡 加分项

持久性的保证程度取决于 innodb_flush_log_at_trx_commit 参数：设为 1 表示每次提交都刷盘（最安全），设为 0 表示每秒刷盘（丢失最多 1 秒数据），设为 2 表示提交时写到 OS 缓存但不刷盘。生产环境一般设为 1，性能要求极高且允许少量丢失时设为 2。

20. MySQL 的四种隔离级别？默认是哪个？

🎯 面试直答版

四种隔离级别从低到高：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、串行化（Serializable）。MySQL 默认是可重复读（RR）。RR 级别下通过 MVCC 解决了脏读和不可重复读，通过临键锁在一定程度上解决了幻读。

📖 深度解析版

隔离级别脏读不可重复读幻读
Read Uncommitted可能可能可能
Read Committed (RC)不可能可能可能
Repeatable Read (RR)不可能不可能InnoDB 大部分场景解决
Serializable不可能不可能不可能

名词解释：

  • 脏读：读到其他事务未提交的数据。
  • 不可重复读：同一事务中两次读取同一行，结果不同（被其他事务修改了）。
  • 幻读：同一事务中两次范围查询，结果集行数不同（被其他事务插入或删除了）。

RC vs RR 的核心区别：

  • RC 每次 SELECT 都会创建新的 ReadView，所以能读到其他事务已提交的最新数据。
  • RR 在事务第一次 SELECT 时创建 ReadView，后续读取复用这个 ReadView，所以看不到其他事务后续提交的变更。

💡 加分项

很多互联网公司（如阿里）生产环境使用 RC 而不是默认的 RR。原因是：RC 加锁范围更小（没有间隙锁），并发性能更好；RC 下半一致性读（semi-consistent read）可以减少锁等待。业务层面通过乐观锁或分布式锁来处理并发问题。

21. MVCC 的实现原理？

🎯 面试直答版

MVCC（多版本并发控制）通过 undo log 版本链 + ReadView 实现，让读操作不加锁就能读到一致的快照。每行数据有隐藏的 trx_id（最近修改的事务ID）和 roll_pointer（指向 undo log 中旧版本的指针）。ReadView 包含活跃事务列表，根据可见性规则判断当前事务能看到哪个版本的数据。

📖 深度解析版

三个隐藏列：

  • DB_TRX_ID：最近修改该行的事务 ID。
  • DB_ROLL_PTR：回滚指针，指向 undo log 中该行的上一个版本。
  • DB_ROW_ID：如果没有主键，InnoDB 自动生成的隐藏主键。

undo log 版本链： 每次更新一行数据，旧版本数据会写入 undo log，并通过 roll_pointer 串成一个链表。这样一行数据可能同时存在多个历史版本。

ReadView 的四个关键字段：

  • m_ids：创建 ReadView 时当前所有活跃（未提交）事务的 ID 列表。
  • min_trx_id：m_ids 中的最小值。
  • max_trx_id：创建 ReadView 时系统应该分配给下一个事务的 ID。
  • creator_trx_id：创建该 ReadView 的事务 ID。

可见性判断规则（对于行数据的 trx_id）：

  1. trx_id == creator_trx_id：自己修改的，可见。
  2. trx_id < min_trx_id：在 ReadView 创建前就已提交，可见。
  3. trx_id >= max_trx_id：在 ReadView 创建后才开始的事务，不可见。
  4. min_trx_id <= trx_id < max_trx_id：
    • 如果 trx_id 在 m_ids 中 -> 说明创建 ReadView 时该事务还未提交，不可见。
    • 如果 trx_id 不在 m_ids 中 -> 说明创建 ReadView 时该事务已提交，可见。

如果当前版本不可见，就沿着 roll_pointer 找上一个版本继续判断，直到找到可见的版本或到达链表末尾。

💡 加分项

MVCC 只在 RC 和 RR 两个隔离级别下工作。它们的区别在于 ReadView 的创建时机：RC 是每次 SELECT 都创建新的 ReadView，RR 是事务第一次 SELECT 时创建并复用整个事务生命周期。这就是为什么 RR 能实现”可重复读”，而 RC 每次都能读到最新已提交数据。

22. 什么是幻读？MySQL 怎么解决的？

🎯 面试直答版

幻读是指在同一事务中，两次相同的范围查询返回的行数不同（其他事务插入了新行）。MySQL 在 RR 级别下通过 MVCC 解决快照读的幻读，通过临键锁（Next-Key Lock）解决当前读的幻读。但 MVCC 无法完全解决所有幻读场景。

📖 深度解析版

幻读的例子：

-- 事务A
SELECT * FROM user WHERE age > 20; -- 返回 3 条
-- 事务B：INSERT INTO user(name,age) VALUES('新用户', 25); COMMIT;
SELECT * FROM user WHERE age > 20; -- RR下快照读仍返回3条（MVCC解决）
-- 但如果事务A执行 UPDATE user SET name='test' WHERE age > 20;
-- 会更新到4条记录（包括事务B新插入的），再查询就能看到4条了

MySQL 的解决方案：

  1. 快照读（普通 SELECT）：通过 MVCC 的 ReadView 机制，RR 级别下同一事务中的快照读总是读取同一个版本的数据，自然看不到其他事务新插入的行。

  2. 当前读（SELECT … FOR UPDATE / INSERT / UPDATE / DELETE）：通过临键锁（Next-Key Lock = 行锁 + 间隙锁）锁住记录及其间隙，防止其他事务在间隙中插入新记录。

⚠️ 易错点：RR 级别下 MVCC 并不能完全解决幻读。如果事务先做快照读再做当前读，仍然可能出现”幻读”现象。严格来说，只有当前读 + 临键锁才能完全防止幻读。

💡 加分项

一个经典的幻读场景：事务A先 SELECT * FROM t WHERE id = 5（快照读，没加锁，结果为空），然后 INSERT INTO t VALUES(5, ...)，结果报主键冲突。因为事务B在这期间插入了 id=5 的记录并提交了。要避免这种情况，事务A应该用 SELECT ... FOR UPDATE（当前读）加锁，阻止事务B的插入。

23. MySQL 有哪些锁？行锁、间隙锁、临键锁分别是什么？

🎯 面试直答版

按粒度分：表锁、行锁。InnoDB 的行锁包括三种：记录锁（锁住单行）、间隙锁（锁住两个记录之间的间隙，防止插入）、临键锁（记录锁 + 间隙锁，左开右闭区间）。此外还有意向锁（表级别，用于行锁和表锁的协调）、插入意向锁、自增锁等。

📖 深度解析版

InnoDB 锁的分类：

  1. 表级锁：

    • 表锁：LOCK TABLES t READ/WRITE
    • 意向锁（IS / IX）：事务要加行锁前，先加对应的意向锁。用于快速判断表中是否有行锁，避免遍历。
    • AUTO-INC 锁：用于自增列。

  2. 行级锁（都是加在索引上的）：

    • 记录锁（Record Lock）：锁住索引中的一条记录。WHERE id = 5 的等值查询会加记录锁。
    • 间隙锁（Gap Lock）：锁住两条记录之间的间隙，是一个开区间 (a, b)。防止其他事务在这个间隙插入数据。只在 RR 级别下存在。
    • 临键锁（Next-Key Lock）：记录锁 + 间隙锁，左开右闭区间 (a, b]。InnoDB 在 RR 级别下默认使用临键锁。

加锁规则简述（RR 级别）：

  • 等值查询命中唯一索引：退化为记录锁。
  • 等值查询未命中：退化为间隙锁。
  • 范围查询：临键锁。
  • 非唯一索引等值查询命中：临键锁 + 间隙锁。

⚠️ 易错点：InnoDB 的行锁是加在索引上的。如果 SQL 没有走索引，行锁会退化为表锁（因为要锁全表的所有记录和间隙）。

💡 加分项

可以通过 SELECT * FROM performance_schema.data_locks 查看当前的加锁情况（MySQL 8.0+），SHOW ENGINE INNODB STATUS 的 TRANSACTIONS 部分也能看到锁信息。在排查死锁或锁等待问题时这两个命令必不可少。

24. 死锁是怎么产生的？如何排查和避免？

🎯 面试直答版

死锁是两个或多个事务互相等待对方持有的锁，形成循环等待。MySQL 有死锁检测机制，会自动回滚代价较小的事务。排查用 SHOW ENGINE INNODB STATUS 查看最近的死锁日志。避免死锁：保持加锁顺序一致、缩小事务范围、使用合理的索引避免锁升级。

📖 深度解析版

死锁产生的经典场景：

-- 事务A
UPDATE t SET name = 'a' WHERE id = 1;  -- 获取 id=1 的行锁
UPDATE t SET name = 'a' WHERE id = 2;  -- 等待 id=2 的行锁

-- 事务B
UPDATE t SET name = 'b' WHERE id = 2;  -- 获取 id=2 的行锁
UPDATE t SET name = 'b' WHERE id = 1;  -- 等待 id=1 的行锁
-- 死锁！

InnoDB 的死锁处理：

  • 等待超时：innodb_lock_wait_timeout 默认 50 秒，超时回滚。
  • 死锁检测：innodb_deadlock_detect 默认开启，主动检测循环依赖，回滚 undo log 量最小的事务。

排查方法：

  1. SHOW ENGINE INNODB STATUS\G -> 查看 LATEST DETECTED DEADLOCK 部分。
  2. 开启 innodb_print_all_deadlocks，所有死锁信息写入 error log。
  3. 分析死锁日志中的两个事务分别持有和等待的锁。

避免死锁的方法：

  1. 按固定顺序访问表和行（如按主键升序）。
  2. 尽量缩小事务范围，减少持锁时间。
  3. 合理建索引，避免行锁升级为表锁。
  4. 使用低隔离级别（RC 没有间隙锁，死锁概率更低）。
  5. 大事务拆分成小事务。

💡 加分项

在高并发场景下，死锁检测本身也有性能开销。当大量事务同时竞争同一行锁时，死锁检测需要遍历锁等待图，时间复杂度是 O(n^2)。如果确认业务逻辑不会产生死锁，可以关闭死锁检测并设置较短的锁等待超时时间，以此提升高并发写入性能。

25. @Transactional 注解失效的场景有哪些？

🎯 面试直答版

常见失效场景：同类方法内部调用（未经过 AOP 代理）、方法不是 public 的、异常被 try-catch 吞掉了、抛出的是 checked exception（默认只回滚 RuntimeException）、数据库引擎不支持事务（如 MyISAM）、使用了多数据源未正确指定事务管理器。

📖 深度解析版

@Transactional 失效的七大场景：

  1. 自调用（最常见）：同一个类中方法 A 调用方法 B，B 上的 @Transactional 不生效，因为调用没有经过 Spring AOP 代理对象。

    public void methodA() {
    this.methodB(); // 直接调用，不走代理，事务不生效
}
@Transactional
public void methodB() { ... }

    解决：注入自身、使用 AopContext.currentProxy()、或拆到不同类中。

  2. 方法非 public：Spring AOP 默认只代理 public 方法。private/protected 方法上的 @Transactional 无效。

  3. 异常被捕获：事务是基于异常回滚的，catch 了异常不抛出，Spring 认为执行成功不会回滚。

  4. 异常类型不对：@Transactional 默认只回滚 RuntimeException 和 Error。如果抛出 checked exception（如 IOException），不会回滚。解决：@Transactional(rollbackFor = Exception.class)。

  5. 数据库引擎不支持事务：如 MyISAM。

  6. 多数据源未指定事务管理器：使用 @Transactional("secondaryTransactionManager") 指定。

  7. 传播机制设置不当：如 PROPAGATION_NOT_SUPPORTED 会以非事务方式执行。

💡 加分项

在排查 @Transactional 失效问题时，可以开启 Spring 事务日志：logging.level.org.springframework.transaction=DEBUG，这样可以看到事务的创建、提交、回滚过程。另外，SpringBoot 2.0+ 默认使用 CGLIB 代理而不是 JDK 动态代理，final 类和 final 方法也无法被代理。

26. 当前读和快照读的区别？

🎯 面试直答版

快照读是普通 SELECT，读取的是 MVCC 机制下的快照版本，不加锁。当前读是 SELECT … FOR UPDATE / LOCK IN SHARE MODE、INSERT、UPDATE、DELETE，读取的是数据的最新版本，并且会加锁。

📖 深度解析版

快照读（Consistent Read）：

  • 普通的 SELECT 语句。
  • 通过 MVCC 读取 ReadView 对应的历史版本。
  • 不加任何锁，不会阻塞其他事务。
  • RR 级别下，整个事务期间看到的快照一致。
  • RC 级别下，每次 SELECT 看到的是最新快照。

当前读（Current Read）：

  • SELECT ... FOR UPDATE（加排他锁/X 锁）
  • SELECT ... LOCK IN SHARE MODE（加共享锁/S 锁，MySQL 8.0 推荐 FOR SHARE）
  • INSERT、UPDATE、DELETE（都会加排他锁）
  • 读取的是数据的最新版本。
  • 会加行锁（记录锁/间隙锁/临键锁），阻塞其他事务的写操作。

⚠️ 易错点：UPDATE 语句的 WHERE 条件匹配也是当前读。所以先快照读再 UPDATE 时，UPDATE 实际看到的数据可能和之前 SELECT 看到的不一样。这是 RR 级别下”幻读”问题的一个变体。

💡 加分项

在业务中需要”先查后改”的场景（如扣减库存），一定要用当前读：SELECT stock FROM product WHERE id = 1 FOR UPDATE，否则可能出现超卖。或者更推荐的做法是直接用 UPDATE product SET stock = stock - 1 WHERE id = 1 AND stock > 0，利用行锁保证原子性，通过 affected_rows 判断是否成功。

模块四：日志与恢复（5 题）

27. redo log 和 binlog 的区别？

🎯 面试直答版

redo log 是 InnoDB 引擎层的物理日志，记录”某个数据页做了什么修改”，用于崩溃恢复。binlog 是 Server 层的逻辑日志，记录”原始 SQL 语句或行变更”，用于主从复制和数据恢复。redo log 是循环写固定大小，binlog 是追加写不会覆盖。

📖 深度解析版

对比维度redo logbinlog
层级InnoDB 引擎层MySQL Server 层
内容物理日志（数据页的修改）逻辑日志（SQL 或行变更）
写入方式循环写，固定大小，写满会覆盖追加写，一个文件写满切换到下一个
用途崩溃恢复（crash recovery）主从复制、数据恢复（point-in-time recovery）
写入时机事务执行过程中持续写入事务提交时写入
是否所有引擎都有只有 InnoDB所有引擎都有

redo log 的 WAL 机制（Write-Ahead Logging）： 先写日志再写磁盘。修改数据时先写 redo log，数据页的修改在内存中进行（脏页），后续由后台线程异步刷盘。即使崩溃，也能通过 redo log 恢复脏页的修改。

为什么需要两个日志：

  • redo log 提供崩溃恢复能力，保证已提交事务的数据不丢失。
  • binlog 提供归档能力，支持主从复制和任意时间点恢复。
  • 历史原因：binlog 在 MySQL 早期就有，redo log 是 InnoDB 引入的。两者结合需要两阶段提交来保证一致性。

💡 加分项

redo log 的大小可以通过 innodb_log_file_size 和 innodb_log_files_in_group 配置。设得太小会导致频繁触发 checkpoint 刷脏页影响性能，设得太大会导致崩溃恢复时间变长。生产环境一般设置为 1-4 GB。MySQL 8.0.30 之后引入了动态调整 redo log 大小的功能。

28. 为什么需要两阶段提交？

🎯 面试直答版

两阶段提交是为了保证 redo log 和 binlog 的一致性。如果不用两阶段提交，在 redo log 写入和 binlog 写入之间崩溃，就会导致主库和从库数据不一致。两阶段提交把 redo log 的写入分为 prepare 和 commit 两个阶段，binlog 的写入在中间。

📖 深度解析版

两阶段提交的流程：

  1. InnoDB 写入 redo log，状态设为 prepare。
  2. 执行器写入 binlog。
  3. 执行器调用 InnoDB 的提交接口，redo log 状态改为 commit。

如果不用两阶段提交会怎样？

场景一：先写 redo log 再写 binlog

  • redo log 写完、binlog 未写就崩溃 -> 主库通过 redo log 恢复了这条数据 -> 但 binlog 没有这条记录 -> 从库通过 binlog 同步时缺少这条数据 -> 主从不一致。

场景二：先写 binlog 再写 redo log

  • binlog 写完、redo log 未写就崩溃 -> 主库重启后没有 redo log，数据丢失 -> 但 binlog 有记录 -> 从库通过 binlog 多了一条数据 -> 主从不一致。

崩溃恢复时的判断逻辑：

  • redo log 是 commit 状态 -> 直接提交。
  • redo log 是 prepare 状态，且 binlog 完整 -> 提交。
  • redo log 是 prepare 状态，且 binlog 不完整 -> 回滚。

💡 加分项

MySQL 5.7 引入了 binlog 组提交（Group Commit）优化：多个事务的 binlog 可以合并成一次 fsync，减少了磁盘刷写次数，显著提升了高并发写入场景的吞吐量。组提交分为 flush、sync、commit 三个阶段，每个阶段都可以积攒多个事务一起处理。

29. undo log 的作用？

🎯 面试直答版

undo log 有两个作用：一是实现事务回滚，保证原子性，事务执行失败时通过 undo log 恢复到修改前的状态；二是实现 MVCC，通过 undo log 版本链让其他事务读到历史版本的数据，实现快照读。

📖 深度解析版

undo log 的类型：

  • insert undo log：插入操作产生的 undo log，事务提交后可以直接丢弃（因为新插入的数据其他事务看不到）。
  • update undo log：更新和删除操作产生的 undo log，需要保留直到没有事务需要通过它访问历史版本为止。由 purge 线程异步清理。

undo log 的版本链： 每次修改一行数据，旧版本存入 undo log，行数据的 roll_pointer 指向这个旧版本。多次修改就形成一条版本链。MVCC 读取时，根据 ReadView 的可见性规则沿版本链找到合适的版本。

undo log 的存储： 存储在共享表空间（ibdata1）或独立的 undo 表空间（MySQL 5.6+ 支持，8.0 默认）中。

⚠️ 易错点：长事务会导致 undo log 大量积累无法回收（因为版本链不能被 purge），造成表空间膨胀。这是长事务的主要危害之一。

💡 加分项

MySQL 5.7+ 支持 undo 表空间截断（truncate），通过 innodb_undo_log_truncate = ON 开启。当 undo 表空间超过 innodb_max_undo_log_size（默认 1GB）时，会自动截断回收空间。在线上遇到 undo 表空间暴涨时，首先要排查是否有长事务，用 SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ASC 查看。

30. MySQL 崩溃恢复的过程？

🎯 面试直答版

MySQL 崩溃恢复主要依赖 redo log。重启时先通过 redo log 把所有已提交但未刷盘的数据恢复（重做），再通过 undo log 回滚所有未提交的事务。同时结合两阶段提交的状态判断事务是该提交还是该回滚。

📖 深度解析版

崩溃恢复的流程：

  1. redo log 回放：扫描 redo log，找到 checkpoint 之后的所有记录，重放这些物理修改操作，把数据页恢复到崩溃前的状态。这一步叫做 redo（重做）。

  2. 判断事务状态：

    • redo log 为 commit 状态 -> 事务已提交，保留。
    • redo log 为 prepare 状态 -> 检查 binlog：
      • binlog 完整（有对应的 XID） -> 提交事务。
      • binlog 不完整 -> 回滚事务。
    • 没有 redo log 记录 -> 事务未提交也未写 redo log，无需处理。

  3. undo log 回滚：对于需要回滚的事务，通过 undo log 逆向恢复数据到修改前的状态。这一步叫做 undo（回滚）。

  4. 清理工作：purge 线程清理不再需要的 undo log 记录。

💡 加分项

崩溃恢复的时间取决于 redo log 需要回放的量。innodb_log_file_size 越大，可能需要回放的 redo log 越多，恢复时间越长。但文件太小又会频繁 checkpoint，影响运行时性能。这是一个 trade-off。另外 MySQL 8.0.30 增加了并行恢复的能力（Parallel Redo Log Recovery），大幅缩短了恢复时间。

31. binlog 的三种格式？

🎯 面试直答版

Statement 格式记录原始 SQL 语句，节省空间但可能导致主从不一致（如使用 NOW()、UUID() 等不确定函数）。Row 格式记录每行数据的变更（修改前和修改后的值），数据一致性最好但日志量大。Mixed 格式是前两者的混合，默认用 Statement，不安全时自动切换为 Row。生产环境推荐 Row 格式。

📖 深度解析版

格式记录内容优点缺点
StatementSQL 语句日志量小非确定性函数导致主从不一致
Row行数据变更数据一致性最好日志量大，批量操作尤其明显
Mixed混合兼顾两者不够可控，部分场景仍有问题

Row 格式的详细模式（binlog_row_image）：

  • FULL（默认）：记录所有列的修改前后值。
  • MINIMAL：只记录修改的列和能标识行的列（如主键），日志更小。
  • NOBLOB：不记录未修改的 BLOB/TEXT 列。

Statement 不安全的场景：

  • 使用 NOW()、UUID()、RAND() 等非确定性函数
  • 使用 LIMIT 但没有 ORDER BY
  • 使用用户自定义函数（UDF）
  • 使用触发器或存储过程中的某些操作

💡 加分项

Row 格式虽然日志量大，但有一个重要好处：可以用来做数据恢复。比如误执行了 DELETE FROM user WHERE id < 100，可以用 mysqlbinlog 解析 Row 格式的 binlog，反向生成 INSERT 语句来恢复数据。工具有 binlog2sql，它能自动生成回滚 SQL，在误操作恢复中非常实用。

模块五：性能优化与架构（11 题）

32. 如何定位和优化慢 SQL？

🎯 面试直答版

先开启慢查询日志定位慢 SQL，再用 EXPLAIN 分析执行计划，看是否走了索引、扫描行数是否合理。优化手段包括：加合适的索引、优化 SQL 写法（避免 SELECT *、子查询改 JOIN）、利用覆盖索引减少回表、优化分页查询、必要时分库分表。

📖 深度解析版

定位慢 SQL 的方法：

  1. 开启慢查询日志：

    SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未使用索引的查询

  2. 分析慢查询日志：使用 mysqldumpslow 或 pt-query-digest 工具聚合分析。

  3. EXPLAIN 分析执行计划：重点关注 type、key、rows、Extra 字段。

  4. SHOW PROFILE / Performance Schema：更细粒度地分析 SQL 各阶段耗时。

优化方法（按收益排序）：

  1. 索引优化：命中率最高的优化手段。确保 WHERE、JOIN、ORDER BY 字段有合适的索引。
  2. SQL 改写：
    • SELECT * 改为具体字段
    • 子查询改为 JOIN
    • OR 改为 UNION ALL
    • IN 列表过大时改为 JOIN 临时表
  3. 架构优化：读写分离、缓存、分库分表。
  4. 参数调优：Buffer Pool 大小、连接数、排序缓冲区等。

💡 加分项

实际排查慢 SQL 的工作流：先用 pt-query-digest 找出 Top 10 慢查询 -> EXPLAIN 逐个分析 -> 优先处理执行频率高 x 单次耗时长的 SQL（总耗时 = 频率 x 单次时间）。一个每天执行 10 万次、每次 100ms 的 SQL，优化到 10ms 的收益远大于优化一个每天执行 10 次、每次 10 秒的 SQL。

33. EXPLAIN 执行计划怎么看？重点关注哪些字段？

🎯 面试直答版

EXPLAIN 最重要的字段：type（访问类型，从好到差：const > eq_ref > ref > range > index > ALL）、key（实际使用的索引）、rows（预估扫描行数）、Extra（额外信息，关注 Using index、Using filesort、Using temporary）。

📖 深度解析版

EXPLAIN 各字段详解：

字段含义关注点
id查询序号id 相同从上往下执行，id 不同大的先执行
select_type查询类型SIMPLE/PRIMARY/SUBQUERY/DERIVED 等
table查询的表
type访问类型（最重要）至少要到 range 级别
possible_keys可能使用的索引
key实际使用的索引NULL 表示没走索引
key_len使用的索引长度判断联合索引用了几个字段
ref索引的哪些列或常量被使用
rows预估扫描行数越小越好
filtered按条件过滤后的行比例越大越好（100 表示没有额外过滤）
Extra额外信息重点关注

type 从好到差：

  • system/const：主键或唯一索引等值查询，最多返回一行。
  • eq_ref：JOIN 时使用主键或唯一索引。
  • ref：使用非唯一索引的等值查询。
  • range：索引范围扫描（BETWEEN、>、< 等）。
  • index：全索引扫描（遍历整棵索引树）。
  • ALL：全表扫描，最差，必须优化。

Extra 中的关键信息：

  • Using index：覆盖索引，好。
  • Using index condition：索引下推，好。
  • Using where：Server 层过滤，需看情况。
  • Using filesort：额外排序，较差，考虑优化索引。
  • Using temporary：使用临时表，差，需要优化。

💡 加分项

MySQL 8.0 引入了 EXPLAIN ANALYZE，它不仅显示执行计划，还会实际执行 SQL 并展示每一步的真实耗时和行数。这比传统 EXPLAIN 的估算值更准确。另外 EXPLAIN FORMAT=TREE 提供了更直观的执行计划展示形式。

34. 深分页问题怎么解决？

🎯 面试直答版

LIMIT 1000000, 10 会扫描前 100 万零 10 行再丢弃前 100 万行，非常慢。解决方案：一是用子查询先通过覆盖索引拿到主键再回表；二是用游标分页（记住上次查询的最大 ID，WHERE id > last_max_id LIMIT 10）；三是业务上限制不允许跳转到特别靠后的页。

📖 深度解析版

方案一：子查询优化（延迟关联）

-- 优化前（慢）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

-- 优化后（快）
SELECT * FROM orders
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) AS tmp
ON orders.id = tmp.id;

子查询中 SELECT id 走覆盖索引非常快，只需要遍历索引树而不需要回表。拿到 10 个 ID 后再回表查完整数据。

方案二：游标分页（推荐）

-- 第一页
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 10;
-- 假设最后一条 id = 10
-- 第二页
SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10;

利用 WHERE 条件直接定位到起始位置，不需要扫描前面的数据。缺点是不能跳页，只能上一页/下一页。

方案三：业务限制

  • 限制最大页码（如最多翻到 100 页）。
  • 用搜索代替分页（引导用户通过条件筛选缩小范围）。
  • 瀑布流/无限滚动代替分页。

💡 加分项

在实际项目中，大多数分页场景都适合用游标分页。比如 App 的列表加载更多、后台管理系统的数据导出等。只有需要”跳转到第 N 页”的场景才需要传统分页，这种需求可以通过 Elasticsearch 来实现。

35. 大表怎么加索引不影响线上业务？

🎯 面试直答版

MySQL 5.6+ 支持 Online DDL，大部分 ALTER TABLE 操作可以在线执行，不阻塞 DML。但大表加索引仍然会占用大量资源。推荐使用 pt-online-schema-change 或 gh-ost 工具，它们通过创建新表、触发器同步数据、最后原子重命名的方式完成变更，对线上影响最小。

📖 深度解析版

方案一：Online DDL（MySQL 原生）

ALTER TABLE t ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;
  • ALGORITHM=INPLACE：原地修改，不复制全表数据（但会重建索引）。
  • LOCK=NONE：不阻塞读写。
  • 缺点：大表仍然耗时很长且占用大量磁盘 IO，可能影响线上查询性能。

方案二：pt-online-schema-change（Percona）

  1. 创建和原表结构相同的新表。
  2. 在新表上执行 ALTER。
  3. 在原表上创建触发器，同步增量变更到新表。
  4. 分批 copy 原表数据到新表。
  5. 原子 rename 交换表名。

方案三：gh-ost（GitHub 出品，推荐）

  • 不使用触发器，改为监听 binlog 获取增量变更，对线上性能影响更小。
  • 支持暂停、限速、动态调整迁移速度。
  • 支持在从库上测试后再应用到主库。

💡 加分项

实际操作时还需要注意：选择业务低峰期执行；提前评估磁盘空间（pt-osc 需要大约 1.5 倍的原表空间）；设置合理的 chunk-size 和 sleep 时间控制迁移速度；操作前后对比数据一致性。另外 MySQL 8.0 的 Instant DDL 支持某些操作（如加列）瞬间完成，但加索引目前还不支持 Instant。

36. 分库分表怎么做？什么时候该分？

🎯 面试直答版

单表数据量超过千万级或数据库连接数/IO 成为瓶颈时考虑分库分表。水平分表：把同一张表的数据按规则分散到多张表（如按 ID 取模）。垂直分表：把字段多的表拆成多张表。水平分库：把数据分散到多个数据库实例。常用中间件有 ShardingSphere、MyCat。

📖 深度解析版

什么时候该分（不要过早分库分表）：

  • 单表数据量超过 2000 万行或数据文件超过 20GB，查询明显变慢。
  • 数据库 CPU 或 IO 持续打满。
  • 单库连接数不够用。
  • 先尝试其他优化手段（索引优化、读写分离、缓存），都不能解决再分库分表。

分库分表的策略：

方式说明场景
垂直分表大表拆成多个小表（按字段拆）字段特别多或有大字段（TEXT/BLOB）
垂直分库按业务模块拆分到不同库微服务架构下按业务域拆分
水平分表同一张表按规则拆成多张表单表数据量过大
水平分库数据分散到多个数据库实例单库性能瓶颈

分片键（Sharding Key）的选择：

  • 选择区分度高、查询频繁的字段。
  • 常见分片策略：Hash 取模（均匀分布）、Range 范围（按时间段或 ID 段）、一致性 Hash。

💡 加分项

分库分表是架构层面的大改造，一旦做了很难回头。建议先评估是否能通过以下方式缓解：归档冷数据到历史表、升级硬件（SSD、大内存）、读写分离、引入缓存层、使用 TiDB 等分布式数据库。如果确定要分库分表，优先垂直拆分（按业务域拆），水平拆分作为最后手段。

37. 分库分表后带来哪些问题？怎么解决？

🎯 面试直答版

主要问题：跨库 JOIN、分布式事务、全局唯一 ID、跨库分页排序、数据迁移和扩容。解决方案分别是：冗余字段或应用层组装、Seata/TCC 事务、雪花算法、归并排序或限制查询条件、一致性 Hash + 数据迁移工具。

📖 深度解析版

  1. 跨库 JOIN：

    • 冗余必要字段避免 JOIN。
    • 应用层分别查询再组装（内存 JOIN）。
    • 使用宽表（数据同步到 ES 或 ClickHouse 做查询）。

  2. 分布式事务：

    • 柔性事务：TCC、SAGA、本地消息表。
    • 中间件：Seata AT 模式（推荐，侵入性小）。
    • 最终一致性：消息队列 + 补偿机制。

  3. 全局唯一 ID：

    • 雪花算法（Snowflake）：64 位 Long，大致递增。
    • 号段模式（Leaf、美团 Leaf）：从数据库批量获取 ID 段。
    • UUID：不推荐做主键（无序，影响 B+ 树性能）。

  4. 跨库分页排序：

    • 各分片分别查询，应用层归并排序。
    • 深分页问题更严重，必须用游标分页。
    • 查询条件尽量带上分片键，定位到具体分片。

  5. 扩容问题：

    • 一致性 Hash 减少数据迁移量。
    • 初期预留足够分片数（如先分 16 库 x 64 表 = 1024 张表）。
    • 使用影子表双写验证。

💡 加分项

现在越来越多的公司选择 TiDB、OceanBase 等分布式数据库来替代手动分库分表，它们在底层实现了数据分片和分布式事务，对应用层透明。如果是新项目且预计数据量会很大，建议直接评估分布式数据库方案。

38. 主从复制原理？主从延迟怎么解决？

🎯 面试直答版

主库把变更写入 binlog，从库的 IO 线程拉取 binlog 写入 relay log，SQL 线程读取 relay log 重放执行。主从延迟的原因通常是从库单线程重放跟不上主库写入速度。解决方案：开启多线程复制（MTS）、读写分离中间件强制读主、业务层面做延迟容忍设计。

📖 深度解析版

主从复制的详细流程：

  1. 主库执行事务，把变更写入 binlog。
  2. 从库 IO 线程 连接主库，请求 binlog。
  3. 主库 Binlog Dump 线程 读取 binlog 发送给从库。
  4. 从库 IO 线程把收到的 binlog 写入 relay log（中继日志）。
  5. 从库 SQL 线程 读取 relay log，重放执行。

三种复制模式：

  • 异步复制（默认）：主库不等从库确认，性能最好但可能丢数据。
  • 半同步复制：主库等待至少一个从库确认收到 binlog 再返回成功，兼顾性能和数据安全。
  • 全同步复制（组复制 MGR）：所有节点确认，性能最差但数据最安全。

主从延迟的原因和解决：

原因解决方案
从库单线程重放MySQL 5.7+ 多线程复制（基于 WRITESET 并行）
主库大事务拆分大事务，避免一次更新/删除大量数据
从库硬件差从库配置不低于主库
从库查询压力大增加从库数量或引入缓存层
DDL 操作使用 pt-osc/gh-ost 减少 DDL 时间

💡 加分项

处理主从延迟的业务方案：写后立即读的场景强制读主库（通过中间件如 ShardingSphere 设置 hint 或延迟检测）；利用 GTID（全局事务标识）配合 wait_for_executed_gtid_set() 确保从库已同步到指定事务；业务设计上对非关键数据容忍短暂延迟（如用户发帖后 1 秒内刷新页面看不到自己的帖子是可以接受的）。

39. MySQL 和 Redis 数据一致性怎么保证？

🎯 面试直答版

没有完美方案，只能做到最终一致性。推荐的方案是”先更新数据库，再删除缓存”（Cache Aside 模式）+ 消息队列重试 + 设置缓存过期时间兜底。延迟双删也是常见方案但实现复杂。

📖 深度解析版

常见方案对比：

方案做法问题
先更新缓存，再更新数据库不推荐两个线程并发写时可能导致数据库和缓存数据不一致
先更新数据库，再更新缓存不推荐并发写时有不一致风险，且缓存更新可能是无效计算
先删缓存，再更新数据库有风险并发读写时：线程 A 删缓存 -> 线程 B 读到旧数据写入缓存 -> 线程 A 更新数据库，缓存就脏了
先更新数据库，再删缓存（推荐）Cache Aside极小的时间窗口可能不一致，但概率很低且有缓存过期兜底

先更新数据库再删缓存的不一致场景： 缓存恰好过期 -> 线程 A 读数据库旧值 -> 线程 B 更新数据库新值 -> 线程 B 删缓存 -> 线程 A 把旧值写入缓存。这种情况要求”读数据库比写数据库还慢”，概率极低。

加强一致性的措施：

  1. 缓存设置过期时间：最终一致性的兜底方案。
  2. 消息队列重试删除：删缓存失败时发消息重试。
  3. 订阅 binlog：用 Canal 监听 binlog，异步删除/更新缓存，可靠性最高。
  4. 延迟双删：先删缓存 -> 更新数据库 -> 延迟（如 500ms）再删一次缓存。

💡 加分项

在我的实际项目中，使用的方案是 “更新数据库 + Canal 监听 binlog 异步删缓存 + 缓存过期时间兜底”。这个方案的好处是：应用代码只关注数据库操作，缓存一致性由 Canal 统一处理，解耦且可靠。对于一致性要求极高的场景（如余额、库存），可以直接读数据库不走缓存。

40. 如果让你设计一个千万级数据量的查询方案，你怎么做？

🎯 面试直答版

分层处理：热数据走缓存（Redis），复杂查询走搜索引擎（Elasticsearch），统计分析走 OLAP（ClickHouse），核心交易走 MySQL。MySQL 层面做好索引优化、读写分离，必要时分库分表或使用分布式数据库。

📖 深度解析版

整体架构设计：

用户请求 -> 应用层
  |
  ├─ 热点数据/简单查询 -> Redis 缓存
  |
  ├─ 复杂搜索/全文检索 -> Elasticsearch
  |
  ├─ 实时统计/报表 -> ClickHouse / TiFlash
  |
  └─ 核心交易数据 -> MySQL（主从 + 分库分表）

MySQL 层面的优化清单：

  1. 硬件：SSD 硬盘、大内存（Buffer Pool 占物理内存的 60-70%）。
  2. 索引：覆盖核心查询场景，定期审查优化。
  3. SQL：杜绝慢 SQL，使用覆盖索引和游标分页。
  4. 架构：读写分离（一主多从），写少读多的场景效果好。
  5. 数据治理：冷热数据分离，历史数据归档到 HBase 或对象存储。
  6. 分库分表：按业务维度垂直拆分，按数据量水平拆分。

具体场景的方案选择：

  • 精确查询（如订单号查订单）：MySQL + 缓存。
  • 列表页搜索+筛选+排序：Elasticsearch。
  • 后台数据统计报表：ClickHouse 或 TiDB。
  • 用户行为日志：先 Kafka 缓冲，再写入 ClickHouse/HBase。

💡 加分项

设计方案时要考虑数据一致性和复杂度的 trade-off。引入越多组件，运维成本越高。如果数据量在千万到亿级别且增速不快，一个优化良好的 MySQL（合理索引 + 读写分离 + 数据归档）配合 Redis 缓存就足够了，不需要过度设计。另外关注查询模式比关注数据量更重要：千万级数据如果都是主键查询，根本不需要任何特殊架构。

41. MySQL 单表数据量多大需要考虑优化？

🎯 面试直答版

经验值是单表 2000 万行或数据文件 20GB 以上时需要考虑优化。但这不是固定阈值，取决于表结构、索引设计、查询复杂度和硬件配置。有些表几百万行就很慢（字段多、索引不合理），有些表几亿行也很快（结构简单、索引合理）。

📖 深度解析版

为什么常说 2000 万？

这个数字来源于 B+ 树的层数推算：

  • InnoDB 页大小 16KB，主键 BIGINT 8 字节，指针 6 字节。
  • 非叶子节点：16384 / 14 ≈ 1170 个 key。
  • 叶子节点：假设每行 1KB，一个叶子页约 16 行。
  • 两层非叶子 + 一层叶子：1170 x 1170 x 16 ≈ 2190 万行。
  • 三层 B+ 树意味着主键查询只需 3 次 IO，性能非常好。

超过这个量级后树可能变成 4 层，每次查询多一次磁盘 IO。但如果行数据更小，一个叶子页能放更多行，上限就更高。

真正需要关注的指标：

  • 查询响应时间是否满足业务要求。
  • EXPLAIN 的 rows 扫描行数是否合理。
  • 磁盘 IO 是否成为瓶颈。
  • Buffer Pool 命中率（通常应 > 99%）。

💡 加分项

与其纠结”多大需要优化”，不如建立监控体系。通过慢查询日志、性能指标（QPS、RT、CPU、IO）持续监控，当性能出现拐点时再介入优化。提前做好索引优化和数据归档策略，很多千万级甚至亿级单表也能跑得很好。

42. 线上 MySQL CPU 飙高怎么排查？

🎯 面试直答版

排查步骤：SHOW PROCESSLIST 看当前执行的 SQL -> 找到耗时长或状态异常的线程 -> EXPLAIN 分析是否走了索引 -> 查看是否有锁等待或死锁 -> 检查是否有大量慢查询或全表扫描。紧急情况下可以 KILL 掉问题线程。

📖 深度解析版

系统化排查流程：

第一步：确认 CPU 占用来源

# 查看 MySQL 进程的 CPU 使用
top -Hp <mysql_pid>
# 确认是 MySQL 造成的 CPU 高

第二步：查看当前活跃连接

SHOW PROCESSLIST;
-- 或者更详细的
SELECT * FROM information_schema.processlist
WHERE command != 'Sleep' ORDER BY time DESC;

关注 State 列和 Time 列，找到执行时间长的 SQL。

第三步：分析问题 SQL

EXPLAIN <问题SQL>;
-- 查看是否全表扫描、是否使用了合适的索引

第四步：检查锁等待

-- MySQL 8.0
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM information_schema.innodb_trx;
-- 查看是否有长事务或锁等待

第五步：检查慢查询日志

mysqldumpslow -s t -t 10 slow.log

常见原因和解决方案：

原因表现解决
全表扫描type=ALL加索引
锁等待大量 Lock wait优化事务、减少锁范围
大量排序Using filesort优化索引支持排序
大量连接Too many connections增加连接数或使用连接池
大事务长时间运行的事务拆分事务
不合理 SQL大量子查询/临时表SQL 优化改写

💡 加分项

紧急处理措施：先 KILL 掉最耗资源的 SQL 线程恢复服务，再排查根因。可以提前配置 max_execution_time 限制 SQL 最大执行时间（MySQL 5.7+）。长期方案是建立 SQL 审核机制（如 Archery/Yearning 平台），上线前审核 SQL 质量。另外，Performance Schema 和 sys 库提供了非常丰富的性能分析视图，如 sys.statements_with_full_table_scans 可以快速定位全表扫描的 SQL。

附录：面试高频追问速查

追问简要回答
Buffer Pool 是什么？InnoDB 的内存缓存区，缓存数据页和索引页，减少磁盘 IO。建议设置为物理内存的 60-70%。使用 LRU 变体算法（young/old 区域）管理。
Change Buffer 是什么？对非唯一二级索引的修改先缓存在 Change Buffer 中，等数据页被读取时再合并。减少了随机 IO，提升写入性能。
WAL 是什么？Write-Ahead Logging，先写日志再写磁盘。把随机写转化为顺序写（redo log 是顺序写），显著提升写入性能。
什么是脏页刷盘？Buffer Pool 中被修改但未写入磁盘的页就是脏页。InnoDB 后台线程定期刷脏页到磁盘。触发条件：redo log 写满、Buffer Pool 空间不足、空闲时、MySQL 正常关闭。
InnoDB 的行格式有哪些？COMPACT（默认）、REDUNDANT、DYNAMIC（8.0 默认）、COMPRESSED。DYNAMIC 对溢出列只存 20 字节指针，COMPRESSED 支持压缩。
什么是页分裂和页合并？插入数据时如果目标数据页已满，就会分裂为两个页。删除数据后如果页利用率太低（< 50%），InnoDB 会尝试合并相邻的两个页。自增主键能减少页分裂。
乐观锁和悲观锁的区别？悲观锁假设冲突多，先加锁再操作（SELECT FOR UPDATE）。乐观锁假设冲突少，不加锁，更新时通过版本号或 CAS 检测冲突。互联网场景多用乐观锁。
MVCC 能解决幻读吗？快照读场景下可以，因为 ReadView 固定。但当前读场景下不行，需要靠临键锁。而且混合使用快照读和当前读时仍可能出现幻读。

最后的建议：面试时不要死记硬背，理解原理后用自己的话组织答案。遇到不确定的问题，诚实说”这个我了解的不够深，但我的理解是…”，比胡编乱造好得多。