MySQL 日志、性能与架构

MySQL 常见日志有哪些？分别位于哪一层？

回答重点

redo log 和 undo log 是 InnoDB 的实现，binlog 则独立于具体存储引擎。日志是否开启、保留多久、何时刷盘都受版本和实例配置影响。

undo log 有什么作用？

回答重点

undo log 保存撤销一项修改所需的信息，主要承担两个职责：

• 事务回滚：插入需要记录可用于删除新记录的信息，删除需要保留旧记录，更新需要保留被修改列的旧值。
• MVCC：聚簇索引记录通过回滚指针连接旧版本，一致性读可以沿版本链找到对当前 Read View 可见的版本。

undo log 不是简单保存一条“反向 SQL”，而是存储引擎内部的回滚记录。修改 undo 页本身也需要 redo log 保护，以便崩溃恢复。

事务提交后，undo 不一定立即删除。只要仍有 Read View 可能访问旧版本，purge 就不能清理它；因此长事务会让历史版本积压。

redo log 和 WAL 是什么？

回答重点

数据页通常先在 Buffer Pool 中修改并成为脏页，后台线程再把脏页刷入数据文件。如果提交必须等待随机位置的数据页全部落盘，写入延迟会很高。

WAL（Write-Ahead Logging）的核心约束是：对应数据页落盘前，描述其修改的 redo 必须先持久化到足以恢复的位置。在常用的强持久化配置下，事务提交时先保证 redo 落盘，而不必同步刷完所有脏页。

redo log 是面向数据页修改的物理或生理日志，写入通常比离散刷新数据页更顺序。实例崩溃后，InnoDB 从检查点附近扫描 redo，重放已经提交但尚未完整落入数据文件的修改。

redo 空间是有限的，会随着检查点推进被循环复用；具体文件布局在不同 MySQL 版本中有变化。它不是永久保留的业务变更历史，也不能替代备份和 binlog。

undo log、redo log 和 binlog 有什么区别？

回答重点

binlog 支持三种常见格式：

• STATEMENT：记录变更 SQL，体积可能较小，但非确定性语句可能带来复制风险。
• ROW：记录行变更事件，通常更可靠，但批量修改可能生成更多日志。
• MIXED：在语句和行事件之间选择。

ROW 记录的仍是 Server 层逻辑变更事件，不是 InnoDB 数据页的物理副本。默认格式随 MySQL 版本和配置而变化；主流 MySQL 8.0 默认通常是 ROW，但应查询当前实例而不是背固定结论：

SELECT @@GLOBAL.binlog_format, @@SESSION.binlog_format;

为什么有 binlog 还需要 redo log？

回答重点

两者解决的问题不同：

• redo log 与 InnoDB 的页、LSN、Buffer Pool 和事务状态紧密结合，可以判断并恢复尚未完整刷入数据文件的页修改。
• binlog 面向 Server 层的逻辑变更和跨实例重放，不记录 Buffer Pool 中哪些页是脏页，也不承担 InnoDB 本地崩溃恢复协议。

只依靠 binlog，在实例异常退出后无法高效、可靠地把本地数据文件恢复到存储引擎认可的一致状态。反过来，redo 会循环复用，也不适合承担长期增量备份和主从复制。因此两者不能互相替代。

哪些配置会影响日志持久化强度？

回答重点

最常见的是：

• innodb_flush_log_at_trx_commit=1：每次提交把 redo 写入并同步到持久化设备，通常提供最强的单机事务持久性。
• innodb_flush_log_at_trx_commit=2：提交时写到操作系统缓存，通常由后台周期性同步；操作系统或机器故障时可能丢失最近事务。
• innodb_flush_log_at_trx_commit=0：redo 的写入和同步主要由后台周期执行，进程崩溃也可能丢失最近事务。
• sync_binlog=1：每次事务提交或提交组同步 binlog，降低已返回成功但 binlog 尚未持久化的风险。

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';

“每秒同步”只是目标节奏，不是实时系统承诺。即使参数取最强值，磁盘控制器缓存、虚拟化平台和文件系统也必须正确兑现 fsync 语义。

redo log 和 binlog 为什么需要两阶段提交？

回答重点

开启 binlog 且事务由 InnoDB 执行时，同一个事务同时涉及 redo 和 binlog。如果两个日志独立提交，任意一份成功、另一份失败都会造成主库恢复结果与复制或备份结果不一致。

MySQL 使用内部 XA 协调两份日志，核心流程是：

1. Prepare：InnoDB 写入带事务 XID 的 redo，并把事务标记为 prepare。
2. 写 binlog：Server 把事务的 binlog cache 写入 binlog，并按配置同步。
3. Commit：Server 通知 InnoDB 提交，redo 中的事务状态变为 commit。

实际实现还会使用组提交降低多次刷盘成本，因此不能把流程理解成每个事务都完全独占三次磁盘 I/O。两阶段提交关注的是两个日志的原子一致性，不是 SQL 标准中由业务直接控制的分布式 XA。

两阶段提交过程中宕机会怎样恢复？

回答重点

恢复时重点处理处于 prepare 状态的 redo：

• redo 已 prepare，但 binlog 中没有对应 XID：说明 binlog 未完整提交，InnoDB 回滚事务。
• redo 已 prepare，binlog 中存在完整的对应 XID：说明 binlog 已成功写入，InnoDB提交事务，即使 redo 的 commit 标记尚未持久化也能恢复一致。

如果在 redo prepare 之前崩溃，事务按未提交处理；如果 commit 已完成，则按正常已提交事务恢复。

这个协议保证本地 InnoDB 恢复出的提交结果与 binlog 中可复制的事务保持一致。具体“写入”是否已经达到持久化设备，仍取决于 innodb_flush_log_at_trx_commit、sync_binlog 和存储系统。

一条 UPDATE 语句的完整执行流程是什么？

回答重点

以主键更新为例，可以概括为：

1. Server 层完成解析、预处理和优化，执行器按执行计划调用 InnoDB。
2. InnoDB 通过索引定位记录；数据页不在 Buffer Pool 时，先从数据文件读入。
3. 更新使用当前读，并对涉及的索引记录或范围加锁。
4. InnoDB 生成 undo 记录，修改 Buffer Pool 中的数据页和相关索引页，并为这些页修改生成 redo；内存页被标记为脏页。
5. Server 为语句生成 binlog event，事务提交前先保存在 binlog cache。
6. 提交时通过 redo prepare、binlog 写入、redo commit 完成两阶段提交。
7. 提交成功后向客户端返回；脏页可以由后台线程稍后刷入数据文件。

UPDATE users
SET name = 'xiaolin'
WHERE id = 1;

自动提交模式下，单条语句隐式完成事务提交；显式事务中则要等 COMMIT。触发器、外键、二级索引数量和实际执行计划会让内部步骤更复杂，但日志与提交主线不变。

doublewrite buffer 解决什么问题？

回答重点

InnoDB 默认页通常是 16 KiB，而操作系统和存储设备的原子写单位可能更小。写数据页过程中宕机，可能只写入页的一部分，形成 torn page（页断裂）。此时页本身已损坏，仅凭“在该页哪个位置做了修改”的 redo 不一定能重建可靠基线。

doublewrite 的基本流程是：

1. 把一批待刷脏页先写入 doublewrite 区域并确保持久化。
2. 再把这些页写到各自的数据文件位置。
3. 崩溃恢复发现数据文件中的页损坏时，先从 doublewrite 找到完整副本恢复该页，再应用 redo。

doublewrite 保护的是数据页写入完整性，不是事务原子性，也不能替代 redo。它会增加写入量，但批量、相对连续地写 doublewrite 区域可以降低开销。

早期版本常把 doublewrite 区域放在系统表空间，较新版本可以使用专用 doublewrite 文件；数量、位置和开关也会随版本与配置变化。

EXPLAIN 应该重点看哪些字段？

回答重点

EXPLAIN 展示优化器选择的执行计划：

EXPLAIN
SELECT id, name
FROM users
WHERE email = 'user@example.com';

重点字段包括：

• type：访问方式。常见访问方式大致从 ALL、index、range、ref、eq_ref 到 const 逐步收窄，但不能只靠这个顺序判断总成本。
• possible_keys：优化器认为可能使用的索引。
• key：实际选择的索引；为 NULL 通常表示没有选择索引访问。
• key_len：计划使用的索引键长度，可辅助判断联合索引实际利用到哪些部分。
• ref：索引查找与哪些列或常量比较。
• rows：预计扫描行数，是估算值。
• filtered：预计经过表条件过滤后保留的比例。
• Extra：补充信息，例如 Using index、Using index condition、Using temporary 和 Using filesort。

Using index 通常表示覆盖索引。Using filesort 表示不能直接利用索引顺序完成排序，但排序可能在内存中，不等于一定写磁盘；Using temporary 也要结合数据量和查询目标判断，不能机械地视为必须消除。

支持的版本中可使用 EXPLAIN ANALYZE 对比估算行数和实际执行时间。它会真正执行语句，生产环境尤其不能随意对写语句使用：

EXPLAIN ANALYZE
SELECT id, name
FROM users
WHERE email = 'user@example.com';

EXPLAIN ANALYZE 的输出格式和能力随版本演进，例如 MySQL 8.0.18 才开始提供该能力。

如何定位并优化慢查询？

回答重点

先收集证据，再修改 SQL 或索引：

1. 从慢查询日志、Performance Schema、APM 或数据库监控中定位高总耗时、高频、高尾延迟的 SQL。
2. 确认慢的是执行、锁等待、网络返回，还是连接池排队。
3. 使用 EXPLAIN 查看访问路径，支持时用 EXPLAIN ANALYZE 对比估算与实际值。
4. 检查扫描行数、返回行数、索引选择性、回表次数、排序、临时表和 join 顺序。
5. 修改后在接近真实的数据量和参数分布下验证，关注吞吐、尾延迟和写入成本。

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';

常见优化手段：

• 为过滤、连接、排序和分组设计合适的联合索引，同时控制索引数量。
• 只读取需要的列，减少回表、网络传输和 Buffer Pool 压力。
• 改写导致索引失效的函数、隐式类型转换和左模糊匹配。
• 用基于稳定唯一排序键的游标翻页替代深 OFFSET。

SELECT id, created_at, title
FROM articles
WHERE id > 20000
ORDER BY id
LIMIT 20;

• 拆分过大的事务和批处理，减少锁持有时间。
• 更新统计信息，排查数据倾斜和参数分布差异。
• 在 SQL 和表结构优化后，再评估读副本、缓存、归档或分片。

“小表驱动大表”“禁止 SELECT *”“看到临时表就优化”都不是脱离执行计划和负载的绝对规则。

优化器选错索引时怎么办？

回答重点

先确认是否真的是错误选择，而不是数据分布、缓存状态或返回列导致另一条路径成本更低：

1. 检查 EXPLAIN ANALYZE 的估算偏差。
2. 使用 ANALYZE TABLE 更新统计信息。
3. 检查联合索引顺序、选择性和数据倾斜；支持的版本可评估直方图。
4. 调整 SQL 或索引，让优化器获得更稳定的低成本路径。

ANALYZE TABLE products;

EXPLAIN
SELECT product_name, buy_price
FROM products FORCE INDEX (idx_buy_price)
WHERE buy_price BETWEEN 10 AND 80
ORDER BY buy_price;

USE INDEX、FORCE INDEX、IGNORE INDEX 或优化器 Hint 可以临时干预计划，但它们会把代码与当前索引和数据分布绑定。数据增长或版本升级后，强制方案可能反而更慢，因此应作为有监控、有回归验证的手段，而不是首选答案。

MySQL 主从复制的基本流程是什么？

回答重点

MySQL 传统复制以 binlog 为基础，可以使用文件位置或 GTID 标识复制进度：

1. 主库事务按提交协议写入 binlog。
2. 副本的接收线程连接主库 dump 线程，拉取 binlog event 并写入本地 relay log。
3. 副本的协调线程和应用线程读取 relay log，在副本存储引擎中重放事务。

传统异步复制中，主库提交不等待副本接收或回放，所以主库成功不代表副本已经有这条数据。复制应用可以并行执行，但并行度受事务依赖、热点行、DDL 和版本配置限制。

术语和命令在较新 MySQL 版本中逐步从 master/slave 改为 source/replica，阅读旧文档时会看到两套名称。

异步复制、半同步复制和读写分离有什么一致性差异？

回答重点

• 异步复制：主库不等待副本确认，提交延迟低，但主库故障时可能有尚未传到副本的事务。
• 半同步复制：主库等待至少一个副本确认收到并按协议持久化日志后再返回。它降低日志丢失窗口，但确认通常不代表事务已经在副本回放完成，因此不能自动提供读后写一致性。
• 更强一致性的集群方案：例如 Group Replication，会引入成员一致性协议和额外约束，不能与传统半同步复制混为一谈。

读写分离把写请求发往主库、读请求分散到副本，可以扩展读能力，但会暴露复制延迟。需要读后写一致性的请求可以：

• 在一段时间内继续读取主库。
• 携带提交位点或 GTID，等待目标副本执行到该位置后再读取。
• 对强一致路径不使用异步副本。

具体选择取决于业务能容忍的陈旧时间、延迟和可用性要求。

主从延迟如何定位和处理？

回答重点

常见原因包括：

• 大事务或单条语句修改大量行，副本必须长时间回放。
• 主库并发写入高，而副本应用并行度不足或事务依赖强。
• 副本 CPU、磁盘 I/O、内存或网络能力不足。
• 缺少索引导致副本回放更新缓慢。
• DDL、元数据锁、行锁或副本上的分析查询阻塞应用线程。
• 主库和副本配置、版本或表结构不一致。

较新版本可从以下入口检查状态：

SHOW REPLICA STATUS;

旧版本常用 SHOW SLAVE STATUS。Seconds_Behind_Source 只是一个近似指标，还应结合接收线程、应用线程状态、relay log 积压、GTID 差距和实际业务时间戳判断。

处理方式包括拆小批量事务、补齐必要索引、启用并合理配置并行复制、隔离副本上的重负载查询、提升硬件与网络能力，并修复持续制造积压的上游写入模式。

对于无法容忍延迟的读取，应临时路由主库或等待副本追上指定提交位点。半同步复制只能改善“日志是否到达”，不能直接消除“日志尚未回放”的延迟。

分库和分表有什么区别？

回答重点

分库、分表需要同时区分垂直和水平两个维度：

• 垂直分库：按业务域拆库，例如订单库、支付库、会员库。它隔离连接数、资源和故障域，但引入跨库事务与跨库查询。
• 垂直分表：把宽表按字段职责拆成一对一或一对多的表，例如把低频大字段从核心用户表拆出。它减少热点行宽和无效 I/O。
• 水平分表：把同结构表的不同数据行拆到同一实例的多张表中，缓解单表数据量、索引和维护压力，但仍共享同一实例资源。
• 水平分库：把同结构数据按分片键拆到不同数据库实例，能够扩展存储和读写吞吐，同时带来分布式路由与一致性问题。

因此，“分表就是垂直拆字段”并不准确，分表既可以垂直拆，也可以水平拆。

MySQL 原生分区表仍属于一个逻辑表和一个实例，主要改善分区裁剪与数据管理，不等同于把数据分散到多台机器的水平分库。

水平分片有哪些常见路由策略？

回答重点

• 哈希或取模：数据分布通常较均匀，点查容易路由；扩容改变分片数时迁移量可能很大。
• 范围分片：按 ID、时间或业务范围划分，扩容和归档直观；连续写入可能形成热点。
• 目录映射：单独维护“分片键到节点”的映射，迁移灵活；目录服务本身需要高可用和缓存。
• 一致性哈希或虚拟分片：减少节点变化时的数据迁移，并通过虚拟节点改善均衡，但实现和运维更复杂。

分片键应优先覆盖高频查询和数据归属边界，并兼顾均匀性。若查询不带分片键，系统可能不得不广播到所有分片，再汇总结果。

实际工程常先固定较多逻辑分片，再把逻辑分片映射到较少物理节点，扩容时迁移映射单元，避免直接依赖“用户 ID 对机器数取模”。

什么时候应该分库分表？主要难点是什么？

回答重点

只有当单实例的容量、写入吞吐、连接数、维护窗口或故障域已经成为明确瓶颈，并且索引、SQL、归档、缓存、读副本和硬件扩容不足以解决时，才应考虑分库分表。不存在适用于所有业务的“单表达到一千万行就必须拆分”阈值。

主要代价包括：

• 跨分片事务和一致性处理。
• 跨库 join、聚合、排序和分页。
• 全局唯一 ID 与唯一约束。
• 分片键选择错误造成数据倾斜或热点。
• 扩容、缩容、数据迁移和双写校验。
• DDL、备份、恢复、监控和故障处理成倍复杂。
• 应用路由、中间件和连接池治理。

架构设计时需要提前定义全局 ID、路由规则、跨分片查询边界、迁移方案和一致性目标。分片是解决已确认瓶颈的手段，不应作为普通慢查询的第一反应。