MySQL常考八股面试题：日志：SQL 优化：锁：事务：MVCC

作者：袖梨 2026-07-05

这篇文章按图片里的分类整理 MySQL 面试高频问题。写法尽量通俗，重点放在面试常问、开发常用、容易踩坑的地方，不做过度源码展开。

一、日志

1. MySQL 三大日志：redo log、undo log、binlog 各自作用？

MySQL 面试里最常问的三类日志是 redo log、undo log、binlog。

redo log 是 InnoDB 的重做日志，主要保证事务的持久性。事务提交后，即使数据库突然宕机，也可以通过 redo log 把已经提交的数据恢复回来。

undo log 是回滚日志，主要保证事务原子性，也用于 MVCC。事务执行过程中会记录修改前的数据，如果事务回滚，就可以根据 undo log 恢复旧值。

binlog 是 MySQL Server 层的二进制日志，主要用于主从复制、数据恢复和审计。它记录的是数据库发生了哪些逻辑变更。

一句话记忆：

redo log：保证崩溃恢复，偏物理。
undo log：保证事务回滚和 MVCC，记录旧版本。
binlog：保证复制和归档，偏逻辑。

2. redo log 为什么能保证事务崩溃恢复？WAL 机制是什么？

redo log 能保证崩溃恢复，核心靠 WAL，也就是 Write Ahead Logging，先写日志，再写数据页。

InnoDB 修改数据时，不会每次都立刻把磁盘上的数据页改掉，而是先修改内存中的 Buffer Pool，同时记录 redo log。事务提交时，只要 redo log 持久化成功，就认为事务提交成功。

如果数据库宕机，内存里的脏页可能还没刷到磁盘，但 redo log 已经在磁盘上。重启后 InnoDB 会根据 redo log 重放修改，把数据恢复到提交后的状态。

通俗说：redo log 像快递签收记录，货物还没完全入库，但签收记录在，系统恢复后可以按记录补齐。

3. binlog 是什么？statement、row、mixed 格式区别？

binlog 是 MySQL Server 层的二进制日志，记录数据库变更，常用于主从复制和数据恢复。

它有三种常见格式：

statement：记录 SQL 语句。优点是日志量小，缺点是某些 SQL 在主从执行结果可能不一致，比如 now()、uuid()、不确定顺序的更新。

row：记录每一行数据的变化。优点是最准确，主从一致性最好；缺点是日志量可能很大。

mixed：混合模式。MySQL 会根据 SQL 是否安全，自动选择 statement 或 row。

生产环境更常用 row，因为复制更可靠，也方便做数据订正和恢复。

4. redo log 和 binlog 区别？

redo log 和 binlog 经常一起问，因为它们都记录修改，但定位完全不同。

redo log 是 InnoDB 引擎层日志，binlog 是 MySQL Server 层日志。

redo log 主要用于崩溃恢复，binlog 主要用于主从复制和数据恢复。

redo log 是循环写，空间固定，会覆盖旧日志；binlog 是追加写，一个文件写满后切换到下一个。

redo log 记录偏物理变化，比如某个页做了什么修改；binlog 记录偏逻辑变化，比如执行了什么 SQL 或哪行变成什么样。

一句话：redo log 管“宕机后自己怎么恢复”，binlog 管“别人怎么同步和回放”。

5. 事务提交时 redo log、binlog 两阶段提交原理？

两阶段提交是为了保证 redo log 和 binlog 一致。

大致流程：

InnoDB 写 redo log，状态为 prepare。
MySQL Server 写 binlog。
InnoDB 把 redo log 改成 commit 状态。

为什么要这样？因为事务提交涉及两个日志，如果只写成功一个就宕机，会出现主库和从库数据不一致。

恢复时会判断：

redo log 有 prepare，binlog 也完整：提交事务。
redo log 有 prepare，binlog 不完整：回滚事务。

这样可以保证主库崩溃恢复结果和 binlog 复制结果一致。

二、SQL 优化

1. 一条 SQL 执行完整流程？

一条 SQL 大致会经过这些步骤：

客户端发送 SQL 到 MySQL Server。
连接器管理连接和权限。
解析器做词法、语法分析。
预处理器检查表、字段是否存在。
优化器选择执行计划，比如用哪个索引、表连接顺序。
执行器调用存储引擎接口。
存储引擎读取或修改数据。
返回结果给客户端。

如果是更新语句，还会涉及 undo log、redo log、binlog 等日志。

2. explain 执行计划每个字段含义？

explain 用来查看 SQL 执行计划，面试常问这些字段：

id：查询执行顺序标识。id 越大通常越先执行；相同 id 从上往下执行。

select_type：查询类型，比如 SIMPLE、PRIMARY、SUBQUERY、DERIVED。

type：访问类型，表示查表效率，优化重点字段。

key：实际使用的索引。

rows：优化器预估要扫描的行数。

Extra：额外信息，比如 Using index、Using where、Using filesort、Using temporary。

看 explain 时，重点关注 type、key、rows、Extra。

3. type 执行效率级别：all、index、range、ref、eq_ref、const、system

type 表示 MySQL 怎么访问表，常见效率从差到好大概是：

ALL < index < range < ref < eq_ref < const < system

ALL：全表扫描，通常最差。
index：扫描整个索引树，比全表扫描稍好，但仍然扫很多。
range：范围扫描，比如 between、>、in。
ref：普通索引等值匹配，可能匹配多行。
eq_ref：唯一索引或主键关联查询，每次最多匹配一行。
const：主键或唯一索引等值查询，结果最多一行。
system：表只有一行，是 const 的特殊情况。

实际优化目标一般是避免 ALL，尽量达到 range、ref 或更好。

4. 怎么看慢查询日志？

慢查询日志用于记录执行时间超过阈值的 SQL。

常用参数：

slow_query_log：是否开启慢查询日志。
long_query_time：超过多少秒算慢 SQL。
slow_query_log_file：慢查询日志文件路径。

排查时重点看：

SQL 原文。
执行耗时。
扫描行数。
返回行数。
是否走索引。

常用分析工具有 mysqldumpslow 和 pt-query-digest。

5. 慢查询优化整体思路？

慢 SQL 优化可以按这个顺序来：

用慢查询日志定位问题 SQL。
用 explain 看执行计划。
判断是否走了合适索引。
检查是否有回表、Using filesort、Using temporary。
优化 SQL 写法，减少扫描行数。
必要时调整索引、拆表、缓存或改业务方案。

核心原则：少扫行、少回表、少排序、少临时表。

6. limit 分页深偏移量怎么优化？例如limit 1000000, 10

limit 1000000, 10 慢，是因为 MySQL 需要先扫描并丢弃前 1000000 行，再返回 10 行。

常见优化方式：

第一种，基于上一页最大 id 做游标分页：

select * from userwhere id > 1000000order by idlimit 10;

第二种，先用覆盖索引查出 id，再回表：

select u.*from user ujoin (    select id from user order by id limit 1000000, 10) t on u.id = t.id;

第三种，产品层面避免跳到特别深的页，比如搜索引擎通常只展示前几十页。

7. order by 排序原理，什么时候 Using filesort？怎么优化？

order by 如果能直接利用索引顺序，就不需要额外排序。

如果不能利用索引排序，MySQL 会使用 filesort。这里的 filesort 不一定真的落磁盘，它表示额外排序算法，数据大时可能用临时文件。

常见触发原因：

排序字段没有合适索引。
联合索引顺序不符合最左前缀。
排序方向混乱，索引无法完全利用。
where 条件和 order by 字段不匹配。

优化方式：

给 where + order by 建合适联合索引。
尽量使用覆盖索引。
控制返回数据量。
避免对排序字段使用函数或表达式。

8. group by 原理与优化思路？

group by 用于分组聚合。MySQL 执行时通常需要按分组字段聚集数据，可能用索引，也可能用临时表和排序。

优化思路：

给分组字段建立索引。
where 先过滤，减少参与分组的数据量。
只查询必要字段。
避免大结果集分组。
能在业务或离线任务预聚合的，不要每次实时算大表。

如果 explain 里出现 Using temporary、Using filesort，说明可能存在额外临时表和排序成本。

9. join 连接原理：内连接、左连接、右连接

join 本质是把多张表按条件关联起来。

内连接 inner join：只返回两边都匹配的数据。

左连接 left join：返回左表全部数据，右表匹配不到时右表字段为 null。

右连接 right join：返回右表全部数据，左表匹配不到时左表字段为 null。

开发中更常用 inner join 和 left join。右连接通常可以改写成左连接，保持阅读习惯统一。

10. 大表 join 怎么优化？

大表 join 优化重点是减少驱动表数据量，并让被驱动表能走索引。

常见做法：

小表驱动大表。
join 字段建立索引，类型保持一致。
先 where 过滤，再 join。
只查需要字段，避免 select *。
大分页、大排序、大分组尽量拆分。
复杂场景可以用冗余字段、宽表、缓存、离线计算。

一句话：让参与 join 的数据尽量少，让匹配过程尽量走索引。

三、基础概念

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

MySQL 常见存储引擎有 InnoDB、MyISAM、Memory、Archive 等。现在生产最常用的是 InnoDB。

InnoDB 支持事务、行级锁、外键、崩溃恢复，适合高并发和事务场景。

MyISAM 不支持事务，不支持行锁，主要是表级锁，崩溃恢复能力弱，但结构简单，早期读多写少场景用得较多。

现在默认优先选择 InnoDB。

2. InnoDB 相比 MyISAM 优势在哪？

InnoDB 的优势主要有：

支持事务，满足 ACID。
支持行级锁，并发写能力更好。
支持崩溃恢复，可靠性更高。
支持 MVCC，提高读写并发。
支持外键。

面试里可以直接说：InnoDB 更适合现代业务系统，尤其是高并发、强一致、需要事务的场景。

3. 什么是事务？事务四大特性 ACID 分别是什么？

事务是一组操作的集合，要么全部成功，要么全部失败。

ACID 分别是：

A Atomicity 原子性：事务内操作要么全成功，要么全失败。主要靠 undo log。

C Consistency 一致性：事务执行前后，数据从一个一致状态变成另一个一致状态。

I Isolation 隔离性：多个事务并发执行时，彼此影响受隔离级别控制。

D Durability 持久性：事务提交后数据不会丢。主要靠 redo log。

4. 数据库三范式是什么？日常开发一定要严格遵守吗？

第一范式：字段不可再分，保证原子性。

第二范式：非主键字段必须完全依赖主键，避免部分依赖。

第三范式：非主键字段不能依赖其他非主键字段，避免传递依赖。

日常开发不一定死守三范式。范式能减少冗余、提高一致性，但有时为了查询性能，会适当反范式，比如冗余用户名、订单快照、统计字段。

原则是：核心数据保证一致，读多性能瓶颈场景可以有控制地冗余。

5. 执行一条 SQL 语句，期间都发生了哪些事情？

以更新语句为例：

客户端发送 SQL。
Server 层解析、优化、生成执行计划。
执行器调用 InnoDB。
InnoDB 找到数据页，加载到 Buffer Pool。
记录 undo log，便于回滚。
修改内存页，产生脏页。
写 redo log prepare。
Server 层写 binlog。
redo log commit。
后台线程择机把脏页刷盘。

查询语句则主要涉及解析、优化、执行、走索引、回表、返回结果。

四、锁

1. InnoDB 有哪些锁？行锁、表锁、意向锁

InnoDB 常见锁包括：

表锁：锁整张表。
行锁：锁某些记录，粒度小，并发度高。
意向锁：表级锁，用来表示事务接下来想锁某些行。
记录锁：锁具体索引记录。
间隙锁：锁两个索引记录之间的间隙。
临键锁：记录锁 + 间隙锁。

InnoDB 的行锁是加在索引上的，如果查询条件没有走索引，可能导致锁范围变大。

2. 行锁什么时候变表锁？

严格说，InnoDB 行锁不会真的“升级”为表锁；但如果 SQL 没有走索引，InnoDB 可能扫描很多行并对大量记录加锁，看起来像锁表。

常见原因：

where 条件没有索引。
索引失效。
字段类型不一致导致隐式转换。
范围条件过大。

所以更新和删除时一定要确认条件走索引，尤其是大表。

3. 记录锁、间隙锁、临键锁分别是什么？

记录锁 Record Lock：锁住某一条索引记录。

间隙锁 Gap Lock：锁住索引记录之间的间隙，不锁具体记录，主要防止幻读。

临键锁 Next-Key Lock：记录锁 + 间隙锁，既锁记录，也锁记录前面的间隙。

例如索引里有 10 和 20，间隙锁可能锁住 (10, 20) 这个范围，防止其他事务插入 15。

4. 临键锁怎么解决幻读？

幻读是同一个事务内，两次范围查询结果条数不一致，比如第一次查没有某条记录，第二次查突然出现了。

在可重复读隔离级别下，InnoDB 对范围查询加临键锁，锁住已有记录和记录之间的间隙。这样其他事务就不能在这个范围里插入新记录。

所以临键锁通过“锁记录 + 锁间隙”防止范围内新增数据，从而解决当前读下的幻读问题。

5. 什么是死锁？产生条件、怎么排查和避免死锁？

死锁是多个事务互相等待对方持有的锁，导致都无法继续。

产生条件：

互斥。
持有并等待。
不可剥夺。
循环等待。

排查方式：

使用 show engine innodb status 查看最近一次死锁信息。
查看事务持有什么锁、等待什么锁。
结合慢 SQL、业务日志定位 SQL 顺序。

避免方式：

统一加锁顺序。
事务尽量短。
where 条件走索引。
避免大范围更新。
必要时使用重试机制。

五、事务（超级高频）

1. MySQL 四大事务隔离级别分别是什么？

MySQL 标准隔离级别有四种：

读未提交 Read Uncommitted。
读已提交 Read Committed。
可重复读 Repeatable Read。
串行化 Serializable。

隔离级别越高，并发能力通常越低，一致性约束越强。

2. 脏读、不可重复读、幻读分别是什么？

脏读：读到了其他事务还没提交的数据。如果对方回滚，你读到的就是脏数据。

不可重复读：同一个事务内，两次读取同一行数据，结果不一样。通常是其他事务提交了修改。

幻读：同一个事务内，两次范围查询，结果集条数不一样。通常是其他事务插入或删除了符合条件的数据。

简单记：脏读读到未提交，不可重复读是同一行变了，幻读是结果集行数变了。

3. 四大隔离级别分别能解决哪些问题？

读未提交：什么都解决不了，可能脏读、不可重复读、幻读。

读已提交：解决脏读，但可能不可重复读和幻读。

可重复读：解决脏读、不可重复读。InnoDB 通过 MVCC 和临键锁在很多场景下也解决幻读。

串行化：基本都能解决，但并发性能最低。

生产中 MySQL InnoDB 默认是可重复读。

4. InnoDB 默认隔离级别是什么？

InnoDB 默认隔离级别是 Repeatable Read，也就是可重复读。

它通过 MVCC 保证普通快照读的可重复读，通过 next-key lock 处理当前读下的幻读问题。

5. 什么是幻读？怎么解决幻读？

幻读指一个事务内两次范围查询，第二次出现了第一次没有的记录，像“幻影”一样。

解决方式：

串行化隔离级别，直接强约束并发。
InnoDB 可重复读下，普通快照读通过 Read View 避免幻读。
当前读通过 next-key lock 锁住范围，阻止其他事务插入。

注意：MVCC 主要解决快照读的一致性，临键锁主要解决当前读的幻读。

6. 事务的实现原理：MVCC 是什么？

事务能力不是一个单独机制完成的，而是多个机制配合：

原子性靠 undo log。
持久性靠 redo log。
隔离性靠锁和 MVCC。
一致性由业务约束、数据库约束和上述机制共同保证。

MVCC 是多版本并发控制。它让读操作可以读到某个时间点的数据版本，而不是总被写操作阻塞。

通俗说：数据被修改后，旧版本不会立刻消失，读事务可以根据规则找到自己应该看到的版本。

7. 可重复读隔离级别，完全解决幻读了吗？

要分情况说。

对于普通 select 快照读，可重复读通过 MVCC 的 Read View 能保证同一事务内查询结果一致，基本避免幻读。

对于 select ... for update、update、delete 这类当前读，InnoDB 通过 next-key lock 锁范围，防止其他事务插入，从而避免幻读。

但如果混用快照读和当前读，可能看到不一样的结果，这是面试里的加分点。

六、MVCC

1. MVCC 底层原理是什么？

MVCC 全称 Multi-Version Concurrency Control，多版本并发控制。

InnoDB 每行记录都有隐藏字段，配合 undo log 保存历史版本，再通过 Read View 判断当前事务能看到哪个版本。

它的核心目标是提高读写并发：读不阻塞写，写不阻塞普通读。

2. 什么是快照读、当前读？举例说明

快照读读取的是某个时间点的数据版本，不加锁。

普通 select 通常是快照读：

select * from user where id = 1;

当前读读取的是最新数据，并且通常要加锁。

常见当前读：

select * from user where id = 1 for update;update user set name = 'Tom' where id = 1;delete from user where id = 1;

快照读看历史版本，当前读看最新版本。

3. 隐式字段 DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID 作用？

InnoDB 行记录里有几个隐藏字段：

DB_TRX_ID：最近一次修改这行记录的事务 id。

DB_ROLL_PTR：回滚指针，指向 undo log 中的旧版本。

DB_ROW_ID：如果表没有主键和唯一非空索引，InnoDB 会生成隐藏行 id。

MVCC 主要依赖 DB_TRX_ID 和 DB_ROLL_PTR 找到可见版本。

4. undo log 日志作用、版本链是什么？

undo log 记录数据修改前的旧值，用于事务回滚和 MVCC。

当一行数据被多次修改时，每次修改都会产生 undo log，记录之间通过回滚指针串起来，就形成版本链。

查询时，如果当前版本对事务不可见，InnoDB 会沿着版本链往前找，直到找到一个可见版本，或者找不到。

5. Read View 视图四个字段作用、可见性规则？

Read View 是 MVCC 判断版本是否可见的核心。

常见字段：

m_ids：创建 Read View 时，系统中活跃事务 id 列表。
min_trx_id：活跃事务中最小 id。
max_trx_id：下一个将要分配的事务 id。
creator_trx_id：创建这个 Read View 的事务 id。

可见性规则简化理解：

如果版本的事务 id 小于 min_trx_id，说明早就提交了，可见。
如果版本的事务 id 大于等于 max_trx_id，说明是之后才出现的，不可见。
如果事务 id 在 m_ids 里，说明当时还没提交，不可见。
如果事务 id 不在 m_ids 里，说明已经提交，可见。

6. MVCC 怎么实现可重复读、读已提交？

读已提交 RC：每次执行普通 select 都会生成新的 Read View。所以同一个事务里，第二次查询能看到其他事务已经提交的数据。

可重复读 RR：事务中第一次普通 select 时生成 Read View，后续普通 select 复用这个 Read View。所以同一个事务里多次读取结果一致。

区别就在于 Read View 的创建时机。

七、索引

1. InnoDB 索引结构为什么选 B+ 树，不选二叉树、红黑树、B 树？

数据库索引存储在磁盘上，核心目标是减少磁盘 IO。

二叉树和红黑树高度相对较高，数据量大时查找层数多，磁盘 IO 多。

B 树每个节点既存 key 也存数据，单页能放的 key 数量相对少，树可能更高。

B+ 树非叶子节点只存 key 和指针，叶子节点存完整数据或主键，单页能放更多 key，树更矮，IO 更少。叶子节点还用链表连接，范围查询更方便。

所以 InnoDB 选择 B+ 树是为了降低 IO、提高范围查询效率。

2. B+ 树和 B 树的区别？

B 树的每个节点都可能存数据。

B+ 树只有叶子节点存数据，非叶子节点只做索引导航。

B+ 树叶子节点之间有链表，范围查询更快。

B+ 树单个非叶子节点能存更多 key，树更矮，磁盘 IO 更少。

3. 聚簇索引和非聚簇索引（二级索引）区别？

聚簇索引的叶子节点存整行数据。在 InnoDB 中，主键索引就是聚簇索引。

非聚簇索引，也叫二级索引，叶子节点存的是索引字段和主键值。

通过二级索引查到主键后，如果还需要其他字段，就要再根据主键回到聚簇索引查整行，这就是回表。

4. 主键索引、唯一索引、普通索引、联合索引区别？

主键索引：唯一且不能为空，一张表只能有一个主键。

唯一索引：值不能重复，但通常允许 null，具体行为和数据库规则有关。

普通索引：没有唯一性限制，只提升查询效率。

联合索引：多个字段组合成一个索引，比如 (a, b, c)，使用时要遵守最左前缀原则。

5. 什么是回表查询？怎么避免回表？

回表是指通过二级索引查到主键后，再根据主键去聚簇索引查整行数据。

例如有索引 (name)：

select age from user where name = 'Tom';

如果 age 不在索引里，就需要回表。

避免回表的方式是使用覆盖索引，把查询需要的字段都放进索引：

create index idx_name_age on user(name, age);

6. 覆盖索引是什么？使用场景？

覆盖索引是指查询需要的字段都能从索引里拿到，不需要回表。

例如联合索引 (name, age)：

select name, age from user where name = 'Tom';

这种情况下只查索引就够了。

覆盖索引适合高频查询、列表页、分页查询等场景，可以减少回表 IO。

7. 最左前缀原则原理，为什么要遵守？

联合索引按字段顺序排序，比如 (a, b, c)，索引先按 a 排，再按 b 排，最后按 c 排。

所以查询必须从最左边字段开始连续使用，才能充分利用索引。

可以走索引的例子：

where a = 1where a = 1 and b = 2where a = 1 and b = 2 and c = 3

不符合的例子：

where b = 2where c = 3

因为缺少最左字段 a，索引整体顺序用不上。

8. 索引下推原理和作用？

索引下推 Index Condition Pushdown，简称 ICP，是 MySQL 的一种优化。

没有索引下推时，存储引擎根据索引找到记录后，可能先回表，再由 Server 层判断其他条件。

有索引下推时，能在存储引擎层先用索引里的字段过滤一部分数据，减少回表次数。

典型场景是联合索引中部分字段可以用于过滤，但不能完全用于定位。

9. 什么是索引失效？哪些情况会导致索引失效？

索引失效是指 SQL 虽然有索引，但优化器没有使用，或者只能使用一部分。