03 - MySQL数据库 · 高频面试考题背诵指南

Q1：B+树为什么比 B树、哈希表更适合做数据库索引？
Q2：聚簇索引和非聚簇索引的区别？什么是回表？
Q3：什么是覆盖索引？如何避免回表？（结合美团项目案例）
Q4：联合索引的最左前缀原则？什么情况会导致索引失效？
Q5：索引失效的常见场景（8种）
Q6：Explain 各字段含义？type 字段性能排序？
Q7：MySQL 四种事务隔离级别
Q8：InnoDB MVCC 原理详解
Q9：InnoDB 的锁类型详解
Q10：MySQL 死锁如何排查和预防？
Q11：乐观锁和悲观锁？版本号机制如何实现乐观锁？
Q12：MySQL 深分页（LIMIT 1000000, 10）为什么慢？如何优化？
Q13：百万级大表分库分表策略
Q14：MySQL 主从复制原理？Binlog 三种格式的区别？
Q15：一条 SQL 语句在 MySQL 中的完整执行过程

一、索引原理

Q1：B+树为什么比 B树、哈希表更适合做数据库索引？

核心考点：B+树的叶子节点存数据、非叶子节点只存索引、支持范围查询。

背诵话术：

对比哈希表：哈希表等值查询是 O(1)，但完全不支持范围查询（BETWEEN、>、<、ORDER BY），而数据库中范围查询非常常见，所以哈希索引只能作为特定场景的补充（如 Memory 引擎）。

对比 B 树：B 树的非叶子节点和叶子节点都存储了实际数据，导致单个节点能存储的索引键数量变少，树的高度更高，磁盘 IO 次数更多。更关键的是，B 树不支持高效的范围扫描，因为数据分散在各层节点中，需要中序遍历。

B+树的优势：

非叶子节点只存键值不存数据，单个页能存储更多键，树高度更低（通常 3~4 层就能存储千万级数据），磁盘 IO 次数更少。

所有数据都存储在叶子节点，且叶子节点通过双向链表相连，支持高效的范围扫描，直接在叶子节点链表上顺序遍历即可，无需回头查非叶子节点。

查询性能稳定：任何一次查询都需要从根节点走到叶子节点，路径长度固定，性能稳定。

Q2：聚簇索引和非聚簇索引的区别？什么是回表？

核心考点：聚簇索引叶子节点存行数据，非聚簇索引叶子节点存主键值，回表的代价。

背诵话术：

聚簇索引（主键索引）：InnoDB 的表数据本身就是按照主键顺序组织的 B+树，叶子节点存储的是完整的行数据。每张 InnoDB 表有且只有一个聚簇索引（优先使用主键，没有主键则选第一个非空唯一索引，都没有则 InnoDB 自动生成一个隐藏的 rowid 作为主键）。

非聚簇索引（二级索引/辅助索引）：叶子节点存储的是索引列的值 + 对应行的主键值，并不包含完整的行数据。

回表（Table Lookup）：当通过二级索引查询数据时，先在二级索引的 B+树中找到对应的主键值，再拿这个主键值去聚簇索引（主键索引）中再查一次，才能获取完整的行数据。这”两次 B+树查询”的过程就叫做回表，会增加磁盘 IO 开销。回表是大数据量下影响性能的重要因素。

Q3：什么是覆盖索引？如何避免回表？（结合美团项目案例）

核心考点：索引覆盖查询所需的所有列，Using index，消除回表。

背诵话术：

覆盖索引是指一个查询所需要的所有列（SELECT 的列、WHERE 的列、ORDER BY 的列）都包含在某一个索引中，查询引擎直接在该索引的 B+树上就能获取全部所需数据，不需要回表。Explain 的 Extra 字段显示 Using index 即说明用到了覆盖索引。

美团项目实战案例：在美团 SaaS 管家后台，商户对账列表接口有一个高频查询：按 shop_id（门店ID）+ status（状态）过滤，并按 create_time 排序，最后取若干展示字段。原始 SQL 用了多表 Left Join 并且字段全是 SELECT *。

优化过程：

把 3 个 Left Join 拆成独立的单表查询，在 Java 中通过 Stream + Map 做内存聚合。

对主查询表建立联合索引：idx_shop_status_time(shop_id, status, create_time)，并严格只查询需要展示的列（而不是 SELECT *）。

调整后，Explain 显示 type = ref（按索引查找），Extra = Using index（命中覆盖索引，无需回表）。最终接口响应时间从 3.2s 降至 0.4s，CPU 负载大幅下降。

Q4：联合索引的最左前缀原则？什么情况会导致索引失效？

核心考点：联合索引的存储顺序，跳过中间列或范围查询之后的列会导致索引中断。

背诵话术：

联合索引 (a, b, c) 在 B+树中先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。

最左前缀原则：查询时，必须从联合索引的最左列开始匹配，且不能跳过中间的列。以 (a, 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 a = 1 AND c = 3：部分命中（只使用 a，c 无法利用索引）

❌ WHERE b = 2 AND c = 3：完全不命中（跳过了最左列 a）

⚠️ WHERE a = 1 AND b > 5 AND c = 3：b 之后的 c 无法使用索引（范围查询之后的列索引中断）

Q5：索引失效的常见场景（8种）

核心考点：需要背熟的 8 种会导致全表扫描的 SQL 写法。

背诵话术：

以下场景会导致索引失效，退化为全表扫描（type = ALL）：

对索引列使用函数操作：WHERE YEAR(create_time) = 2024，应改为 WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'。

隐式类型转换：字段类型为 VARCHAR，但查询时传的是整数 WHERE phone = 13800138000（应加引号），MySQL 会对列做隐式转换，导致索引失效。

LIKE '%xxx'（前导通配符）：LIKE '%abc' 无法利用 B+树索引，LIKE 'abc%' 可以利用。

OR 条件中有一列没有索引：WHERE a = 1 OR b = 2，如果 b 没有索引，整个查询走全表扫描。

!=、<>、NOT IN、NOT EXISTS：这类反向查询通常无法使用索引（MySQL 优化器判断全表扫描成本更低时会放弃索引）。

IS NULL / IS NOT NULL：IS NULL 可能走索引，IS NOT NULL 通常不走，取决于优化器。

范围查询之后的联合索引列：WHERE a = 1 AND b > 5 AND c = 3，b 使用范围查询后，c 的索引失效。

索引选择性太低：如性别字段只有”男/女”两个值，索引选择性极低，MySQL 优化器会判断全表扫描更快而放弃索引。

Q6：Explain 各字段含义？type 字段性能排序？

核心考点：type、key、rows、Extra 四个最重要的字段的含义。

背诵话术：

Explain 最重要的字段：

type（访问类型，性能由好到差）：
system > const > eq_ref > ref > range > index > ALL

const：主键或唯一索引等值查询，最多匹配 1 行。

ref：非唯一索引等值查询，匹配多行。

range：范围查询，如 BETWEEN、>、<、IN。

index：全索引扫描（比全表扫描省磁盘IO，但仍是全扫描）。

ALL：全表扫描，需要立即优化！

key：实际使用的索引名称，NULL 说明没有用索引。

rows：估算需要扫描的行数，越小越好。

Extra（最重要的诊断字段）：

Using index：✅ 命中覆盖索引，无需回表，最佳。

Using where：在 Server 层对数据进行过滤（索引可能只是范围扫描）。

Using filesort：⚠️ 需要额外的排序操作（在内存或磁盘中排序），说明 ORDER BY 没有利用索引，需优化。

Using temporary：⚠️ 使用了临时表，通常在 GROUP BY 或 DISTINCT 时出现，开销较大。

Using index condition：索引条件下推（ICP），在存储引擎层就过滤数据，减少回表次数。

二、事务与 MVCC

Q7：MySQL 四种事务隔离级别

核心考点：从低到高，各级别解决的问题，RR 是 MySQL 默认级别。

背诵话术：

隔离级别脏读不可重复读幻读

读未提交（Read Uncommitted） ❌ 存在 ❌ 存在 ❌ 存在

读已提交（Read Committed，RC） ✅ 解决 ❌ 存在 ❌ 存在

可重复读（Repeatable Read，RR） ✅ 解决 ✅ 解决 ⚠️ 基本解决

串行化（Serializable） ✅ 解决 ✅ 解决 ✅ 解决

脏读：读到其他事务未提交的数据。

不可重复读：同一事务内，两次读同一行数据结果不同（其他事务 UPDATE 后提交）。

幻读：同一事务内，两次范围查询结果的行数不同（其他事务 INSERT 后提交）。

MySQL InnoDB 默认隔离级别是 RR。InnoDB 通过 MVCC 解决了不可重复读，通过 Next-Key Lock（临键锁）在大多数场景下防止了幻读，因此 RR 级别下幻读被”基本解决”。

隔离级别	脏读	不可重复读	幻读
读未提交（Read Uncommitted）	❌ 存在	❌ 存在	❌ 存在
读已提交（Read Committed，RC）	✅ 解决	❌ 存在	❌ 存在
可重复读（Repeatable Read，RR）	✅ 解决	✅ 解决	⚠️ 基本解决
串行化（Serializable）	✅ 解决	✅ 解决	✅ 解决

Q8：InnoDB MVCC 原理详解

核心考点：隐藏字段（trx_id + roll_pointer）、Undo Log 版本链、ReadView 可见性算法、RC 和 RR 的区别。

背诵话术：

MVCC 是 InnoDB 实现”读不加锁、读写不冲突”的核心机制，它依赖三个组件：

1. 隐藏字段：每行数据有两个隐藏字段：

trx_id：最后一次修改该行的事务 ID。

roll_pointer：回滚指针，指向 Undo Log 中该行的上一个版本。

2. Undo Log 版本链：每次对行数据进行修改，旧版本会被保存到 Undo Log 中，多次修改会形成一条版本链，链头是最新版本，链尾是最旧版本，通过 roll_pointer 串联。

3. ReadView（读视图）：事务发起快照读时生成，记录了：

creator_trx_id：当前事务 ID。

m_ids：生成 ReadView 时，所有活跃（已开启但未提交）的事务 ID 列表。

min_trx_id：m_ids 中的最小值。

max_trx_id：下一个将要分配的事务 ID（即当前最大 trx_id + 1）。

可见性判断规则：沿着版本链从最新版本往旧版本遍历，对每个版本的 trx_id 进行判断：

trx_id == creator_trx_id：自己修改的，可见。

trx_id < min_trx_id：在 ReadView 生成前就已提交，可见。

trx_id >= max_trx_id：在 ReadView 生成后才开启的事务，不可见。

min_trx_id <= trx_id < max_trx_id：如果 trx_id 在 m_ids 中，说明该事务未提交，不可见；不在 m_ids 中，说明已提交，可见。

RC 和 RR 的区别：

RC（读已提交）：每次执行 SELECT 都重新生成一个新的 ReadView，所以能读到其他事务已提交的最新修改 → 存在不可重复读。

RR（可重复读）：在事务内只在第一次 SELECT 时生成 ReadView，后续所有查询复用同一个 ReadView → 同一事务内读取结果一致，解决了不可重复读。

三、锁机制

Q9：InnoDB 的锁类型详解

核心考点：各种锁的定义和加锁条件，间隙锁防幻读原理。

背诵话术：

按锁的粒度分：

表级锁：开销小，加锁快，并发度低。DDL 操作会加元数据锁（MDL）。MyISAM 只有表锁，InnoDB 在特定条件下也会退化为表锁。

行级锁：开销大，加锁慢，并发度高。InnoDB 的行锁是加在索引上的，如果 WHERE 条件没有命中索引，会退化为表锁！

InnoDB 行锁的三种类型：

记录锁（Record Lock）：锁住某一条具体的记录（索引项）。SELECT ... FOR UPDATE 精确命中某行时使用。

间隙锁（Gap Lock）：锁住两个索引记录之间的空隙，防止其他事务在这个区间内 INSERT 新记录，解决幻读问题。间隙锁之间不冲突，多个事务可以同时持有同一间隙的间隙锁，但间隙锁与 INSERT 意图锁冲突，可能导致死锁。

临键锁（Next-Key Lock）：= 记录锁 + 间隙锁，锁住一条记录以及该记录之前的间隙，是 InnoDB 在 RR 级别下的默认行锁算法。

意向锁：表级锁，由 InnoDB 自动管理。事务加行锁前先加意向锁，让其他事务判断表上是否有行锁时无需逐行检查，提高加表锁的效率。

Q10：MySQL 死锁如何排查和预防？

核心考点：SHOW ENGINE INNODB STATUS 查看死锁日志，预防策略。

背诵话术：

排查死锁：执行 SHOW ENGINE INNODB STATUS\G，在输出的 LATEST DETECTED DEADLOCK 段落中，可以看到发生死锁的事务信息、它们持有和等待的锁，以及 MySQL 选择回滚的事务（通常是回滚代价较小的）。也可以通过 information_schema.INNODB_LOCK_WAITS 和 INNODB_TRX 表查询当前的锁等待情况。

预防死锁的策略：

统一加锁顺序：多个事务如果需要锁定多个资源，确保始终以相同的顺序加锁，从根本上打破循环等待。

缩短事务持锁时间：尽量减少事务的执行时间，减少持锁窗口，降低锁冲突概率。

为 WHERE 条件加索引：确保更新/删除操作命中索引，使用行锁而不是退化为表锁，减小锁的粒度。

合理使用 SELECT … FOR UPDATE：尽量在事务最开始时就获取所有需要的锁（一次性加锁），而不是在事务中途分散加锁。

使用乐观锁替代悲观锁：对于读多写少的场景，乐观锁（版本号）可以避免大量加锁。

Q11：乐观锁和悲观锁？版本号机制如何实现乐观锁？

核心考点：两种锁的思想差异，乐观锁的 CAS 实现，适用场景。

背诵话术：

悲观锁：悲观地认为并发冲突必然发生，每次操作数据前都先加锁。适用于写操作多、并发冲突频繁的场景，如 SELECT ... FOR UPDATE。代价是高并发下锁等待时间长，吞吐量低。

乐观锁：乐观地认为并发冲突不常发生，操作数据时不加锁，而是在提交时检测是否有冲突，有则重试。适用于读多写少、并发冲突较少的场景。

版本号机制实现乐观锁：在表中增加一个 version 字段。读取数据时把 version 一并读出；更新时在 SQL 中加上 WHERE id = ? AND version = ?，同时将 version + 1：
1
2
UPDATE orders SET status = 'PAID', version = version + 1
WHERE id = 123 AND version = 5;
如果 UPDATE 影响的行数为 0，说明在我读取和更新之间，有其他事务已经修改了该行（version 变了），此时触发重试或抛出业务异常。在医博系统的号源扣减场景中，我们就使用了 WHERE stock > 0 AND version = ? 的乐观锁方式，防止超卖。

四、SQL 优化与大表处理

Q12：MySQL 深分页（LIMIT 1000000, 10）为什么慢？如何优化？

核心考点：LIMIT offset 需要扫描前 N 行再丢弃，延迟关联（子查询先取主键）优化方案。

背诵话术：

LIMIT 1000000, 10 慢的原因：MySQL 执行时会先从索引或全表中顺序扫描并跳过前 100 万行，再取第 100 万零 1 到 100 万零 10 行返回。虽然最终只返回 10 条，但前 100 万行的扫描（包括可能的回表）是实实在在发生的，IO 开销极大。

优化方案一：延迟关联（子查询先取主键）：
1
2
3
4
5
6
7
8
-- 优化前（慢）
SELECT * FROM orders ORDER BY create_time LIMIT 1000000, 10;

-- 优化后（快）：先通过覆盖索引拿主键，再用主键关联取完整数据
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY create_time LIMIT 1000000, 10
) t ON o.id = t.id;
子查询只查 id（覆盖索引，无需回表），速度极快；外层再用 10 个主键精确查询行数据。

优化方案二：游标分页（推荐）：记录上一页最后一条记录的主键或游标值，下一页查询时直接用 WHERE id > last_id LIMIT 10，彻底避免大 offset 扫描，是最优解。适合需要翻页的场景。

Q13：百万级大表分库分表策略

核心考点：垂直分表（按列拆）、水平分表（按行拆）、分片键选择、跨库查询问题。

背诵话术：

垂直分表（按列拆）：将一张宽表中不常用的大字段（如 content、image_url 等 BLOB/TEXT 字段）拆分到另一张扩展表，主表只保留常用的热字段。优点是减小主表行宽，提高缓存效率，适合字段很多但实际使用时只需查询部分字段的场景。

水平分表（按行拆）：将一张大表按照某个分片键（如 user_id % 16 或按时间范围）将数据分散到多个相同结构的表或库中。核心挑战是分片键的选择：

分片键必须是高频查询条件，否则每次查询都要扫全部分片（扇出查询）。

分片键一旦确定，几乎不能修改，需要谨慎设计。

常用方案：取模分片（数据分布均匀，但扩容困难）、范围分片（便于归档，但容易导致热点）、一致性哈希（扩容时数据迁移量小）。

分库分表引入的新问题：跨库 JOIN 无法直接执行（需要业务层聚合）、分布式事务复杂化、全局唯一 ID 需要雪花算法等方案、分页和排序需要查询所有分片再汇总。

五、MySQL 架构

Q14：MySQL 主从复制原理？Binlog 三种格式的区别？

核心考点：Binlog + Relay Log 的三步走，statement/row/mixed 三种格式的取舍。

背诵话术：

主从复制的三步流程：

主库（Master）将所有 DDL/DML 操作写入 Binlog 文件，并通过 dump 线程将 Binlog 推送给从库。

从库（Slave）的 IO 线程接收 Binlog，写入本地的 Relay Log（中继日志）。

从库的 SQL 线程读取 Relay Log，在本地重放执行，使从库数据与主库保持一致。

Binlog 三种格式：

STATEMENT：记录原始 SQL 语句。日志体积小，但某些包含函数（如 NOW()、UUID()）的 SQL 在从库重放时可能得到不同结果（数据不一致风险）。

ROW（推荐）：记录每行数据的实际变更（变更前和变更后的值）。精确可靠，不存在数据不一致问题，但日志体积较大（大量更新时会膨胀）。Canal 解析 Binlog 时也要求使用 ROW 格式。

MIXED：自动混合使用。普通 SQL 用 STATEMENT，遇到可能导致不一致的 SQL 时自动切换为 ROW 模式。

Q15：一条 SQL 语句在 MySQL 中的完整执行过程

核心考点：连接器→查询缓存→解析器→优化器→执行器→存储引擎。

背诵话术：

以 SELECT * FROM users WHERE id = 1 为例：

连接器：建立 TCP 连接，进行身份认证（用户名/密码），获取该用户的权限并缓存。

查询缓存（MySQL 8.0 已移除）：如果命中查询缓存，直接返回缓存结果。由于缓存失效频繁，实际效果差，MySQL 8.0 直接移除了这个功能。

解析器（Parser）：对 SQL 进行词法分析（识别关键字、表名、列名等 Token）和语法分析（验证 SQL 语法是否正确），生成语法树（AST）。

优化器（Optimizer）：根据统计信息（索引选择性、数据分布等），选择最优的执行计划（如选择哪个索引、JOIN 的驱动表顺序等）。这一步决定了 Explain 输出的内容。

执行器（Executor）：调用存储引擎的接口，按照执行计划逐步获取数据。执行前会再次检查操作权限。

存储引擎（InnoDB）：实际执行数据的读写操作。从 Buffer Pool 中查找数据（命中则直接返回），未命中则从磁盘读取数据页到 Buffer Pool 再返回。

MySQL数据库面试考题

03 - MySQL数据库 · 高频面试考题背诵指南

目录

一、索引原理

Q1：B+树为什么比 B树、哈希表更适合做数据库索引？

Q2：聚簇索引和非聚簇索引的区别？什么是回表？

Q3：什么是覆盖索引？如何避免回表？（结合美团项目案例）

Q4：联合索引的最左前缀原则？什么情况会导致索引失效？

Q5：索引失效的常见场景（8种）

Q6：Explain 各字段含义？type 字段性能排序？

二、事务与 MVCC

Q7：MySQL 四种事务隔离级别

Q8：InnoDB MVCC 原理详解

三、锁机制

Q9：InnoDB 的锁类型详解

Q10：MySQL 死锁如何排查和预防？

Q11：乐观锁和悲观锁？版本号机制如何实现乐观锁？

四、SQL 优化与大表处理

Q12：MySQL 深分页（LIMIT 1000000, 10）为什么慢？如何优化？

Q13：百万级大表分库分表策略

五、MySQL 架构

Q14：MySQL 主从复制原理？Binlog 三种格式的区别？

Q15：一条 SQL 语句在 MySQL 中的完整执行过程