LongJie 的知识库

Appearance

L13: MySQL 大厂面试题 - 主从复制与分库分表（Q81-Q100） ​

专题六：主从复制与高可用（10 题） ​

Q81：主从复制的原理是什么？ ​

答案 ​

  1. 主库执行 SQL，变更写入 binlog
  2. 从库 IO 线程连接主库，拉取 binlog
  3. 写入从库 relay log（中继日志）
  4. 从库 SQL 线程读取 relay log，重放操作
  5. 从库数据与主库同步

Q82：binlog 三种格式有什么区别？ ​

答案 ​

Statement ​

  • 记录原始 SQL
  • 体积小
  • 可能主从不一致（NOW()、UUID() 等不确定函数）

Row ​

  • 记录行数据变更
  • 更可靠，不受不确定函数影响
  • 体积较大

Mixed ​

  • 自动选择
  • 通常用 Statement，遇到不确定函数切换 Row

生产推荐：Row

Q83：主从延迟有哪些原因？怎么解决？ ​

答案 ​

原因 ​

  1. 主库写入 TPS 过高
  2. 从库硬件配置差
  3. 大事务（binlog 大，重放慢）
  4. 从库单线程重放
  5. 网络延迟
  6. 从库有大查询

解决 ​

  1. 避免大事务
  2. 开启并行复制（MySQL 5.7+）
  3. 升级从库硬件
  4. 分库分表降低主库压力
  5. 优化从库慢查询

Q84：读写分离有什么一致性问题？怎么解决？ ​

答案 ​

问题 ​

刚写主库，立刻读从库，因为主从延迟可能读不到最新数据。

解决方案 ​

  1. 关键读走主库（最常用）
  2. 引入缓存：写后更新 Redis，读先读 Redis
  3. 标记路由：Session 里标记"刚写过"，短时间内强制读主库
  4. 等待从库追上：写后等待从库复制完成（性能差）

Q85：什么是半同步复制？ ​

答案 ​

默认主从复制是异步的， 主库写完 binlog 就返回成功，不等从库确认。

半同步复制要求：

主库写入 binlog 后，至少等一个从库确认收到，才返回成功。

好处：

  • 主库宕机时，至少有一个从库有完整数据
  • 减少数据丢失风险

坏处：

  • 每次提交需要等从库 ACK，性能下降
  • 从库超时时会退化为异步复制

Q86：什么是 GTID？有什么优点？ ​

答案 ​

GTID = Global Transaction ID，全局事务 ID。

每个事务在主库上有一个全局唯一 ID。

优点 ​

  1. 主从切换更简单 从库根据 GTID 自动定位同步位置， 不需要手动指定 binlog 文件名和位置。

  2. 避免重复执行 从库不会重复执行已经应用过的事务。

  3. 更容易实现高可用 切换主库时，其他从库能自动找到同步点。

Q87：主库宕机后如何切换？ ​

答案 ​

手动切换 ​

  1. 选数据最新的从库（Seconds_Behind_Master 最小）
  2. 让该从库追上主库 binlog
  3. 执行 STOP SLAVE
  4. 执行 RESET MASTER
  5. 其他从库指向新主库
  6. 应用切换连接

自动切换 ​

用工具自动完成：

  • MHA（常用）
  • Orchestrator
  • MySQL InnoDB Cluster

Q88：什么是 MGR（MySQL Group Replication）？ ​

答案 ​

MySQL 官方高可用方案。

  • 多个节点组成一个复制组
  • 基于 Paxos 协议，保证数据强一致
  • 自动故障检测和切换
  • 支持单主模式和多主模式

单主模式 ​

只有一个主节点提供写，其他节点只读。 主节点故障，自动选出新主。

多主模式 ​

所有节点都可以写，但需要处理冲突。

Q89：什么是 InnoDB Cluster？ ​

答案 ​

MySQL 官方完整高可用解决方案，由三部分组成：

  1. MySQL Group Replication：强一致性复制
  2. MySQL Shell：管理工具
  3. MySQL Router：连接路由，自动把读写分发到合适节点

好处：

  • 自动故障切换
  • 强一致性
  • 官方支持

Q90：如何防止主库宕机时数据丢失？ ​

答案 ​

方案 1：半同步复制 ​

主库等从库确认收到 binlog 才返回， 主库宕机时从库有完整数据。

方案 2：MGR（强一致性） ​

基于 Paxos 协议，写入需要多数节点确认， 不会丢数据。

方案 3：提高 binlog 刷盘频率 ​

sql
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

确保每次提交都刷盘，宕机不丢数据，但性能下降。

方案 4：binlog 实时备份 ​

实时把 binlog 同步到备份存储， 即使主库数据丢失也能恢复。

专题七：分库分表（10 题） ​

Q91：什么时候需要分库分表？ ​

答案 ​

通常在：

  • 单表数据量超过 5000 万行，查询明显变慢
  • 单库写入 TPS 已到瓶颈
  • 磁盘容量不足

注意：分库分表复杂度高，应该先用：

  • 索引优化
  • 读写分离
  • Redis 缓存
  • 垂直拆分

这些手段无效后，再考虑水平分库分表。

Q92：垂直拆分和水平拆分有什么区别？ ​

答案 ​

垂直拆分 ​

  • 按业务把不同表拆到不同库（垂直分库）
  • 把一张宽表拆成多张窄表（垂直分表）
  • 解决业务耦合问题
  • 不解决单表数据量大的问题

水平拆分 ​

  • 把同一张表的数据按规则分散到多个库/表
  • 解决单表数据量大、写入瓶颈问题
  • 实现更复杂

Q93：分片键怎么选？ ​

答案 ​

原则 ​

  1. 数据分布均匀（避免热点）
  2. 覆盖最多查询场景（带分片键的查询直接路由）
  3. 字段值稳定（不会变化）

常见选择 ​

  • 订单表：按 user_id 分片
  • 用户表：按 user_id 分片
  • 消息表：按 conversation_id 分片

坑 ​

  • 不要按时间分片（热点严重）
  • 不要选会变化的字段

Q94：分库分表后分页查询怎么处理？ ​

答案 ​

带分片键（推荐） ​

sql
SELECT * FROM order_info
WHERE user_id = 1001  -- 分片键
ORDER BY create_time DESC
LIMIT 20 OFFSET 40;

直接路由到对应分片，没有跨分片问题。

不带分片键 ​

  • 所有分片各自查，汇总后排序分页
  • 代价随页数增大
  • 推荐用 ES 做分页

Q95：全局主键怎么生成？ ​

答案 ​

推荐方案 ​

雪花算法（Snowflake）

  • 64 位整数，趋势递增，全局唯一
  • 本地生成，性能高
  • 不依赖外部存储

号段模式

  • 数据库维护发号器，每次批量取一段 ID
  • 简单可靠
  • 依赖发号器高可用

不推荐 ​

  • UUID：无序，索引性能差
  • 数据库自增：分库分表后不能保证全局唯一

Q96：分库分表后如何做跨库 JOIN？ ​

答案 ​

方案 1：业务层关联 ​

  • 先查一张表，拿到关联 ID
  • 再查另一张表
  • 代码里做关联

方案 2：冗余字段 ​

在一张表里冗余另一张表的字段， 避免 JOIN。

方案 3：宽表 ​

把需要关联的数据提前整合到一张宽表， 异步同步，查询直接查宽表。

方案 4：数仓 ​

复杂的跨库查询放到数据仓库， 不在 MySQL 里做。

Q97：分库分表后如何做分布式事务？ ​

答案 ​

方案 1：最终一致性（最常用） ​

  • 主事务提交后发消息
  • 其他服务消费消息，执行子事务
  • 失败重试，幂等处理

优点：性能好，扩展性强。

方案 2：TCC ​

  • Try：预留资源
  • Confirm：确认提交
  • Cancel：撤销预留

优点：灵活，一致性强。 缺点：业务侵入，实现复杂。

方案 3：2PC（两阶段提交） ​

性能差，一般不用。

Q98：分库分表后唯一约束失效怎么处理？ ​

答案 ​

分库分表后，数据库唯一索引只在单分片内有效， 无法保证全局唯一。

解决方案 ​

方案 1：全局发号器 所有 ID 由统一发号器生成， 天然全局唯一。

方案 2：Redis 唯一性校验 写入前先在 Redis 里检查是否存在：

text
SET unique:phone:13800000000 1 NX

NX 表示不存在才设置，利用 Redis 原子性保证唯一。

方案 3：唯一索引表 单独维护一张唯一索引表， 写入前先插入这张表（利用唯一索引约束）， 成功再写业务表。

Q99：分库分表后如何扩容？ ​

答案 ​

扩容是分库分表最难的问题。

挑战 ​

Hash 取模方案： 4 个分片 -> 8 个分片， 几乎所有数据都要迁移。

解决方案 ​

方案 1：翻倍扩容 每次翻倍（4 -> 8 -> 16）， 每个分片只有一半数据需要迁移。

方案 2：提前规划逻辑分片 比如规划 1024 个逻辑分片， 初期映射到 4 个物理库， 扩容时迁移部分逻辑分片到新物理库， 不改路由规则。

方案 3：一致性哈希 扩容时只有少量数据需要迁移。

Q100：分库分表有哪些常见坑？ ​

答案 ​

1. 分片键选错 ​

按时间分片导致热点， 或选了会变化的字段。

2. 全局 ID 重复 ​

没用全局发号器， 自增 ID 在多个分片重复。

3. 深翻页没有处理 ​

全分片扫描，性能极差。

4. 跨库 JOIN 没有方案 ​

查询变得极其复杂。

5. 分布式事务用 2PC ​

性能很差，大多数场景不合适。

6. 扩容没有提前规划 ​

上线后扩容，数据迁移代价极大。

7. 唯一约束失效 ​

忘记唯一索引在分库分表后只在单分片有效。

8. 过早引入分库分表 ​

明明索引优化 + 读写分离就能解决， 却引入分库分表，增加无谓复杂度。

总结 ​

MySQL 大厂面试题 100 问全部完成。

专题分布回顾 ​

专题题目核心能力
基础与表设计Q1-Q10设计能力
索引Q11-Q35查询性能
事务与日志Q36-Q50数据安全
锁与并发Q51-Q65并发控制
性能调优Q66-Q80线上优化
主从复制Q81-Q90高可用
分库分表Q91-Q100大规模架构

基于 VitePress 构建

MIT 许可证