每日一讲技术题（8.1-2）

主从复制延迟的原因有哪些？如何监控和优化？

主从复制延迟的本质是：主库产生 binlog 的速度，超过了从库接收和回放 binlog 的速度。

原因主要有：

1. 主库写入压力大：高并发写入、大事务、批量更新/删除、DDL。
2. 从库性能不足：CPU、内存、磁盘 I/O 跟不上，或者读请求太多。
3. SQL 执行慢：缺索引、大表更新、慢 SQL、主从索引或表结构不一致。
4. 网络问题：跨机房、跨地域、带宽不足、网络抖动。
5. 复制机制限制：单线程复制、大事务无法并行、并行复制配置不合理。
6. 锁等待或复制异常：长事务、长查询、备份任务、复制线程报错。

监控主要看：

SHOW REPLICA STATUS\G;
-- 或
SHOW SLAVE STATUS\G;

重点关注：

Seconds_Behind_Master
Slave_IO_Running
Slave_SQL_Running
Read_Master_Log_Pos
Exec_Master_Log_Pos
Relay_Log_Space
Last_IO_Error
Last_SQL_Error

更准确的方式是用心跳表监控真实延迟，同时结合 CPU、磁盘 I/O、网络、慢 SQL、锁等待、relay log 堆积一起看。

优化方式：

1. 拆分大事务，批量操作分小批执行。
2. 优化 SQL 和索引，避免无索引更新、删除。
3. 降低从库读压力，避免长查询、报表、备份影响复制。
4. 开启并调优并行复制。
5. 提升从库硬件，重点关注磁盘 I/O。
6. 优化网络链路，减少跨地域复制延迟。
7. 谨慎调整刷盘参数，在性能和可靠性之间权衡。
8. 如果长期延迟严重，需要做分库分表、冷热拆分、消息队列削峰等架构优化。

总结：

排查主从延迟时，先判断复制线程是否正常，再区分是 I/O 线程延迟还是 SQL 线程回放延迟；优化上优先处理大事务、慢 SQL、从库资源瓶颈和并行复制，如果写入量长期超过复制能力，就需要从架构层面拆分。

谈谈你对读写分离的理解，以及它在业务中如何应用。

读写分离就是：写请求走主库，读请求走从库。

它的主要作用是分担主库读压力，提高系统查询吞吐量，适合读多写少的业务，比如商品详情、文章浏览、订单列表、用户信息查询等。

业务中一般：

1. insert / update / delete 走主库。
2. 普通 select 走从库。
3. 写后立即读、支付、余额、库存、权限等强一致场景走主库。
4. 报表、统计、后台大查询走专门从库。

它能解决的是读压力大的问题，但不能提升主库写能力，也不能天然解决主从延迟。

核心注意点是主从延迟和一致性问题。比如刚写完数据马上查，如果读从库，可能查不到最新结果。常见处理方式是：

1. 写后短时间读主库。
2. 强一致业务固定读主库。
3. 监控从库延迟，延迟过高就摘除。
4. 对热点数据结合缓存，但要设计好缓存一致性。
5. 能接受最终一致的业务，比如点赞数、浏览量，可以读从库。

总结：

读写分离的本质是通过主从架构把读流量分摊到从库，提高读吞吐。实际使用时，普通查询走从库，写操作和强一致查询走主库，同时要监控主从延迟，避免因为读到旧数据影响业务。

如何设计一个高可用的MySQL方案？

MySQL 高可用的核心目标是消除单点故障，让主库出现问题时系统能够快速恢复写能力，并尽量减少数据丢失。

常见做法是一主多从架构，主库负责写入，从库负责读取。主从之间可以使用半同步复制来降低主库宕机时的数据丢失风险。当主库故障时，通过 MHA、Orchestrator、ProxySQL、Keepalived 或 InnoDB Cluster 等组件自动检测故障，并从从库中选择数据最新、延迟最小的节点提升为新主库，应用再通过 VIP、数据库代理或配置中心切换到新的主库。

设计时最关键的是故障切换、防脑裂和数据一致性。切换过程中要避免出现两个主库同时写入，旧主库恢复后也不能直接重新加入集群，必须先隔离、校验数据，再重新同步。同时，读写分离可以提升读能力，但强一致场景，比如支付、余额、库存、权限等，仍然应该读主库，避免主从延迟带来的问题。

另外，高可用不等于备份。主从复制可以应对机器故障，但不能防止误删、误更新等逻辑故障，所以还需要全量备份、binlog 备份和定期恢复演练。面试中可以总结为：MySQL 高可用通常采用一主多从加半同步复制和自动故障切换，核心是快速切主、防止脑裂、减少数据丢失，并配合监控、读写分离和备份恢复来保证整体可靠性。

你如何备份和恢复MySQL数据库？

MySQL 备份恢复的核心思路是：全量备份 + binlog 增量备份，这样既能恢复整库，也能恢复到某个指定时间点。

小库可以用 mysqldump 做逻辑备份，简单、兼容性好，适合小数据量、单表恢复或迁移。大库更适合用 XtraBackup 做物理热备，备份和恢复速度更快，对线上影响也更小。

恢复时一般先恢复最近一次全量备份，再通过 mysqlbinlog 回放 binlog。如果是误删数据，可以把数据恢复到误操作前的时间点。生产环境通常不会直接覆盖原库，而是先恢复到临时实例，校验没问题后再导回线上。

备份文件不能只放在数据库机器本地，要上传到独立存储或对象存储，并做好压缩、加密、权限控制和保留周期。同时要监控备份是否成功，并定期做恢复演练，因为备份的最终价值取决于能不能真正恢复。

总结：MySQL 备份通常采用全量备份加 binlog 增量备份，小库用 mysqldump，大库用 XtraBackup。恢复时先还原全量备份，再回放 binlog 到指定时间点。生产中要先在临时实例验证，再回填或切换，同时做好异地保存、备份监控和恢复演练。

数据库服务器磁盘IO压力过大，可能的原因和排查思路是什么？

数据库服务器磁盘输入输出压力大，常见原因包括慢查询或索引问题，比如全表扫描、排序、分组、临时表写入磁盘，导致大量磁盘读写。也可能是写入压力过高，比如高并发插入、更新、删除，大事务频繁提交，造成重做日志（redo log）、二进制日志（binlog）、回滚日志（undo log）和数据页刷盘压力增加。还有可能是内存不足或数据库缓冲池（buffer pool）太小，缓存命中率低，导致频繁读取磁盘。另外，备份、表结构变更（DDL）、建索引、报表、归档、批量清理等后台任务，也会明显增加磁盘输入输出（I/O）压力。最后，还要考虑主从复制、磁盘空间不足、云盘每秒输入输出能力（IOPS）达到上限，或者其他系统进程占用磁盘。

排查时可以先从系统层确认是否真的是磁盘瓶颈，比如看磁盘输入输出统计（iostat）、进程级磁盘占用（iotop）、系统资源统计（vmstat），重点关注磁盘利用率（%util）、磁盘等待时间（await）、每秒读写次数（IOPS）、吞吐量和队列长度。如果磁盘利用率长期接近 100%，磁盘等待时间很高，说明磁盘压力确实很大。

然后看 MySQL 层面，检查慢查询日志、执行计划、当前数据库连接和执行中的 SQL、InnoDB 存储引擎状态、数据库性能统计表，判断是否有慢 SQL、全表扫描、锁等待、大事务、临时表落盘、数据库缓冲池命中率低等问题。

优化时要先区分读压力还是写压力。读压力高，重点优化 SQL 和索引，减少全表扫描，提高数据库缓冲池命中率。写压力高，重点拆分大事务、降低批量写入压力、优化刷盘策略和日志配置。备份、报表、表结构变更这类任务尽量放到低峰期或从库执行。如果最终是硬件瓶颈，就需要升级为固态硬盘或高速固态硬盘（SSD/NVMe），提升云盘每秒读写能力，或者通过读写分离、分库分表来分散压力。

如何在线修改大表结构？

在线修改大表结构的核心是：尽量不阻塞线上读写，并保证数据一致性。大表不要直接盲目执行普通 ALTER TABLE，否则可能长时间锁表、打满磁盘输入输出（I/O）、造成主从延迟，影响业务。

一般先看 MySQL 原生在线表结构变更（Online DDL）是否支持，比如加索引可以尝试：

ALTER TABLE user ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

其中原地修改（INPLACE）和不加锁（LOCK=NONE）可以减少对业务的影响。但不是所有操作都支持完全在线，比如修改字段类型、调整主键、重建表等，可能仍然会锁表或复制整张表，所以要提前验证。

如果原生在线 DDL 不合适，可以使用在线改表工具，比如 pt-online-schema-change 或 gh-ost。它们通常会创建一张新结构的影子表，把原表数据分批复制过去，同时同步增量变更，最后短时间切换新旧表，从而降低锁表时间。

如果是特别复杂的结构调整，比如字段拆分、表拆分、字段语义变化，可以用业务层灰度方案：先加新字段或新表，业务双写，新旧数据逐步迁移，读流量灰度切换，最后下线旧结构。

执行前要评估表大小、数据量、索引、磁盘空间、主从延迟和备份情况，最好先在测试环境验证。执行时选择低峰期，并监控锁等待、慢 SQL、磁盘输入输出、CPU、binlog 增长和主从延迟。如果发现影响业务，要能暂停或回滚。

总结：大表结构变更要优先考虑原生 Online DDL，不支持或风险较高时使用 pt-online-schema-change、gh-ost 这类在线改表工具，通过影子表、分批迁移和短暂切换降低影响。复杂变更可以走业务双写和灰度迁移，整个过程要提前评估、备份、低峰执行并持续监控。

误删除数据后，如何通过binlog进行恢复？

误删除数据后，通过 binlog 恢复的核心思路是：利用全量备份 + binlog，把数据恢复到误操作前的状态，再把正确数据补回生产库。

一般不要直接在原库上恢复，而是先准备临时实例，恢复最近一次全量备份，然后用 mysqlbinlog 回放全量备份之后的 binlog，并停止在误删除发生前：

mysqlbinlog --stop-datetime="2026-07-31 10:29:59" mysql-bin.000123 | mysql -u root -p

恢复到误删前状态后，再从临时实例导出被删除的数据，回填到生产库。这样不会影响误删之后产生的正常业务数据。

如果 binlog 是行模式（ROW），也可以解析 binlog 中的 DELETE 事件，拿到被删除前的行数据，再生成反向 INSERT 语句恢复。可以用：

mysqlbinlog --base64-output=DECODE-ROWS -vv mysql-bin.000123

或者使用 binlog2sql 这类工具反向生成恢复 SQL。

需要注意的是，如果 binlog 是语句模式（STATEMENT），可能只能看到删除 SQL，不一定能拿到每行原始数据，恢复难度更大。

总结：误删数据后，优先用全量备份恢复到临时实例，再回放 binlog 到误操作前的时间点，导出误删数据回填生产库；如果 binlog 是 ROW 格式，也可以解析 DELETE 事件生成反向 INSERT。整个过程要先验证，避免直接在生产库上回滚或覆盖正常数据。

服务器意外断电重启后，MySQL是如何保证数据一致性的？

服务器意外断电后，MySQL 主要依靠 redo log、undo log 和预写日志机制（WAL） 保证数据一致性。

事务提交时，InnoDB 不一定马上把数据页写入磁盘，而是先写 redo log（重做日志）。只要 redo log 已经落盘，即使断电时数据页还没写完，重启后也可以通过 redo log 把已提交事务重新恢复出来，这叫前滚。

对于断电前没有提交的事务，MySQL 会通过 undo log（回滚日志） 撤销这些修改，保证不会出现只执行一半的事务，这叫回滚。

所以恢复过程可以理解为：

已提交事务：通过 redo log 前滚恢复
未提交事务：通过 undo log 回滚撤销

另外，InnoDB 还有 双写缓冲（doublewrite buffer），用来防止数据页写到一半时断电造成页损坏。

刷盘参数也会影响安全性：

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

这种配置安全性最高，能最大程度保证事务提交后不丢数据，但性能开销也更大。

总结：MySQL 断电重启后，InnoDB 会通过 redo log 恢复已提交但未落盘的数据，通过 undo log 回滚未提交事务，并借助双写缓冲防止数据页损坏，从而保证事务的原子性、一致性和持久性。