MYSQL · 进阶 / 高可用与分库分表
MySQL速查
锁机制与临键锁、undo/redo/binlog 日志体系与两阶段提交、主从复制与高可用、分库分表与分布式 ID、SQL 优化实战、备份恢复与延时从库——MySQL 进阶最常追问的 50 个核心考点一张表收齐，随查随用。
50条速查
8大主题
∞持续更新
📖 速查表
点击展开各小节
🔒 锁机制
| 名称 | 说明 | 要点 / 示例 |
|---|---|---|
| 全局锁 | Flush Tables With Read Lock（FTWRL）让全库只读，用于逻辑备份 | InnoDB 场景改用 --single-transaction 借 MVCC 一致性快照备份，不阻塞业务 |
| 表锁 | lock tables ... read/write 显式加表锁；InnoDB 行锁够用时基本不用 | MyISAM 全靠表锁：读读不冲突、读写与写写均冲突，写操作会饿等待 |
| 元数据锁 MDL | 访问表自动加：增删改查加 MDL 读锁，变更表结构加 MDL 写锁；读写锁互斥 | MDL 写锁等待会阻塞后续所有请求 → 长事务期间别执行 DDL 线上事故高发 |
| 意向锁 | 表级 IS/IX，标记「表内有行已加行锁」，使表锁申请无需逐行扫描判断冲突 | 意向锁之间互相兼容，只与表级读写锁互斥；行锁加锁时自动附带 |
| 记录锁 Record Lock | 锁单条索引记录；查询条件无索引可用时，行锁退化为「扫到哪锁到哪」，近似全表加锁 | 行锁锁的是索引项而不是数据行本身 易混淆，所以更新要走索引 |
| 间隙锁 Gap Lock | 锁索引记录之间的开区间间隙，阻止区间内插入，用于解决幻读 | 只在 RR（可重复读）隔离级别生效；间隙锁之间不冲突，但会互相阻塞插入 |
| 临键锁 Next-Key Lock | 记录锁 + 间隙锁（前开后闭区间），RR 隔离级别下行锁的基本加锁单位 | 等值查询命中唯一索引时退化为记录锁 面试高频 加锁规则分析题核心 |
| 共享锁与排他锁 | S 锁：select ... for share，兼容 S 阻塞 X；X 锁：for update，读写全阻塞 | 普通 select 是快照读不加锁（走 MVCC）；当前读才加锁 |
| 死锁示例与排查 | 典型：两个事务以相反顺序更新两行互相等待；InnoDB 自动检测并回滚代价小的事务 | 排查：show engine innodb status 看 LATEST DETECTED DEADLOCK；预防：统一加锁顺序 + 小事务 + 合理索引 |
📜 日志体系
| 名称 | 说明 | 要点 / 示例 |
|---|---|---|
| undo log | 回滚日志：保存修改前的值，用于事务回滚与 MVCC 一致性读（ReadView + 版本链） | insert undo 事务提交即删；update undo 保留供旧版本读，由 purge 线程清理 |
| redo log | 重做日志：InnoDB 层物理日志，记录「某页某处做了什么修改」；WAL 先写日志后写数据页 | 保证崩溃不丢已提交数据；循环写（ib_logfile），落盘策略由 innodb_flush_log_at_trx_commit 控制 必背 |
| binlog | Server 层逻辑日志，记录逻辑变更（SQL 或行变更），追加写不覆盖 | 用途：主从复制 + 恢复到指定时间点（point-in-time recovery） |
| redo vs binlog 对比 | 层次：InnoDB 物理 / Server 逻辑；写入：循环写 / 追加写；用途：崩溃恢复 / 复制与归档 | 两者由内部 XA（两阶段提交）协调一致 易混淆 |
| 两阶段提交 | redo prepare → 写 binlog → redo commit；保证两份日志逻辑一致 | 不两阶段提交的后果：崩溃恢复后 redo 与 binlog 不一致 → 主从数据漂移 必背 |
| binlog 三种格式 | statement：记 SQL，量小但 NOW()/UUID() 等不确定函数会主从不一致；row：记行变更，量大但最安全；mixed：普通语句 statement、不确定函数切 row | 5.7.7+ 默认 row；涉及主从一致与审计优先 row 面试高频 |
| 崩溃恢复 | 一句话：重启后扫描 redo log，对 prepare 状态的事务核对 binlog——binlog 完整则提交、不完整则回滚，再以 binlog 对齐从库 | 这就是两阶段提交在恢复期的裁决逻辑，保证主库与从库最终一致 |
🔁 主从与高可用
| 名称 | 说明 | 要点 / 示例 |
|---|---|---|
| 复制流程（三线程） | 主库 dump 线程发送 binlog → 从库 IO 线程接收并写入 relay log（中继日志）→ 从库 SQL 线程重放 relay log | 5.7+ SQL 线程可分裂为协调线程 + 多个 worker 并行回放 必背 |
| 异步复制 | 主库提交即返回，不等从库确认 | 性能最好；主库宕机可能丢失尚未传到从库的最新事务 默认模式 |
| 半同步复制 | 至少一个从库确认收到 binlog（写入 relay log）后主库才向客户端返回成功 | after_sync / after_commit 两种等待点位；等待超时自动降级为异步，需监控恢复 |
| GTID | 全局事务标识 server_uuid:seq，复制位点自动定位 | 主从切换不再手工找 binlog file/pos，gtid_mode=ON 是现代架构标配 推荐 |
| 主从延迟原因 | 从库单线程重放（老版本）、大事务重放慢、从库机器规格低或承载大量读、主库批量写（批量导入/大更新） | 延迟本质：主库并行写、从库串行（或半并行）读回放 |
| 主从延迟对策 | 开并行复制（MTS：基于库 / 基于 WRITESET）、拆分大事务、从库瘦身（关不必要 binlog）、关键读强制走主、监控 Seconds_Behind_Master | 业务侧兜底：写后短时间内的读路由到主库 实战技能 |
| MHA / MGR | MHA：外部工具，故障时基于 binlog 补差并选新主；MGR：组复制，基于 Paxos 多数派协议强一致、自带选主 | MGR 是 MySQL 8.0 官方方案；生产也常用 Orchestrator / 云高可用套件编排切换 |
🗂️ 分库分表
拆分是最后的手段：先榨干索引、缓存与归档的潜力，再垂直拆、确实顶不住了才水平拆——拆分带来的复杂度会伴随系统整个生命周期。
| 名称 | 说明 | 要点 / 示例 |
|---|---|---|
| 垂直拆分 | 垂直分库：按业务域把不同表拆到不同库（订单库/商品库/用户库）；垂直分表：把大字段、低频字段拆到扩展表 | 主表瘦身、冷热分离；伴随业务边界清晰化，与微服务拆分思路同源 |
| 水平拆分 | 同一张表按分片键把行分散到多个库表，突破单库数据量与 QPS 瓶颈 | 拆完的代价长期存在（跨片查询/事务/扩容），不到万不得已不拆 先优化再拆分 |
| 分片键选择 | 选查询中最高频的等值条件（如 user_id / buyer_id），让绝大多数查询命中单片 | 基因法：订单号嵌入分片基因，同一订单既可按买家也可按订单号定位 面试高频 |
| 跨片查询 | 无分片键的查询要广播到全部分片聚合，性能差 | 解法：异构索引表、ES 宽表、CQRS 冗余读模型、冗余常用查询维度字段 |
| 分布式 ID | 分库后自增主键会冲突，需要全局唯一 ID | 方案：号段模式（Leaf-segment）、雪花算法（时钟回拨要处理）、UUID（无序、不适合聚簇主键） 面试常考 |
| 扩容方案 | 一句话：双写迁移——新旧库双写、存量灰度迁移、校验一致性、读切流、最后撤旧写 | 翻倍扩容可预留 2^n 槽位或用一致性哈希减少迁移量 |
| 分片中间件 | ShardingSphere（JDBC / Proxy 两种形态）、MyCat 等 | 应用层（JDBC）分片性能好、更可控；Proxy 形态对业务语言透明 |
🚀 SQL 优化实战
| 名称 | 说明 | 要点 / 示例 |
|---|---|---|
| 慢查询定位流程 | 开 slow_query_log + 设置 long_query_time → explain 分析执行计划 → 优化（索引/SQL 改写）→ 复测对比 | 线上批量分析用 pt-query-digest 聚合 top SQL 必背 |
| explain 关键列 | type（至少 range，避免 ALL 全表扫）、key / key_len（实际使用的索引）、rows（预估扫描行数）、Extra（附加信息） | Extra 出现 Using filesort / Using temporary 要警惕；Using index 表示覆盖索引 |
| 深分页优化 | limit 1000000,20 会扫描并丢弃前 100 万行 | 游标法：where id > 上次最大值 limit 20（连续翻页）；延迟关联：子查询用覆盖索引取主键再回表 面试高频 |
| count(*) 优化 | InnoDB 必须遍历（MVCC 决定没有全局计数），count(1)≈count(*)，count(字段) 忽略 NULL 更慢 | 思路：近似值 information_schema.tables、缓存计数（Redis/汇总表）、判断存在用 limit 1 / exists |
| join 优化 | 小表驱动大表（驱动表结果集小）；Index Nested-Loop 要求被驱动表 join 字段有索引 | 无索引退化为 BNL（Block Nested-Loop）；8.0.18+ hash join 兜底；on 字段类型/字符集一致防隐式转换失效 |
| 覆盖索引与索引下推 | 覆盖索引：查询列全在索引内，免回表；ICP 索引下推：where 条件下推到存储引擎层先过滤再回表 | 高频查询列表按需建联合索引，用 Extra 验证 Using index |
| 索引失效常见场景 | 联合索引跳过最左列、对索引列做函数或运算、隐式类型转换（字符串列传数字）、like '%xx' 前缀模糊、or 连接非索引列 | 另一类「失效」是优化器判断回表代价高于全表扫而主动放弃 易混淆 |
💾 备份与恢复
| 名称 | 说明 | 要点 / 示例 |
|---|---|---|
| mysqldump 常用参数 | --single-transaction（InnoDB 一致性快照不锁表）、--master-data=2（注释记录位点便于搭从库）、--set-gtid-purged | 配合 --databases / --tables / --where 控制备份范围 |
| xtrabackup 物理备份 | 一句话：直接拷贝数据文件并应用备份期间的 redo，速度快于逻辑备份，适合 TB 级实例热备 | 流程：--backup 备份 → --prepare 整理 → --copy-back 恢复 大库首选 |
| 备份策略 | 每周全量 + 每日增量（binlog），保留期分级：7 天快速恢复 / 30 天追溯 / 年级异地归档 | 全量用 xtrabackup、增量靠 binlog，两者配合才能恢复到任意时间点 |
| 延时从库误删恢复 | 配置一台延迟从库（DELAY 一小时至一天）；误删后立即 stop slave 冻结重放 | 让延时从库停在误操作前的那条 binlog，跳过该事务后重放即得到「误删前」数据 误删兜底 |
| 备份验证习惯 | 定期在测试环境恢复演练：验证恢复成功率与耗时，比对表行数、抽样数据、checksum | 没验证过的备份不算备份；恢复 RTO/RPO 指标要写进运维预案 |
🧪 索引有效性实验
「加了索引为什么还慢」别靠猜，用 EXPLAIN 做一次前后对比实验：盯住 type、key、rows 三个字段的变化，索引是否生效一目了然。
-- 实验：orders 表约 300 万行，查询「某用户某状态最近的订单」很慢
-- 表结构：orders(id, user_id, status, amount, created_at)
-- ① 建索引前：看这三个字段——
-- type = ALL 全表扫描，最差等级
-- rows = 3000000 预估要扫 300 万行
-- key = NULL possible_keys 有值但 key 为空 = 没用上索引
EXPLAIN SELECT * FROM orders
WHERE user_id = 42 AND status = 1
ORDER BY created_at DESC;
-- ② 建联合索引：等值列在前（user_id, status），排序列在后（created_at）
-- 顺序依据：最左前缀原则 + 让 ORDER BY 免去 filesort
CREATE INDEX idx_user_status_created
ON orders (user_id, status, created_at);
-- ③ 建索引后再跑同一句 EXPLAIN，对比变化——
-- type = ref 普通索引等值匹配，可接受等级
-- rows = 42 预估扫描几十行，从百万级掉到两位数
-- key = idx_user_status_created 索引真的被用上了
EXPLAIN SELECT * FROM orders
WHERE user_id = 42 AND status = 1
ORDER BY created_at DESC;
-- ④ 收尾：大量写入后统计信息可能失真，ANALYZE TABLE orders 刷新；
-- 若优化器仍不选新索引，可用 FORCE INDEX 验证是统计问题还是写法问题
📑 表设计军规
建表时的 7 条铁律，条条都是踩坑换来的：字段类型选错、注释缺失，后期修复的代价远超建表时多花的十分钟。
| 军规 | 原因 | 说明 / 反例 |
|---|---|---|
| 金额用 DECIMAL | FLOAT / DOUBLE 是二进制浮点，必然精度丢失 | 对账差一分钱查半天；用 DECIMAL(12,2)，或 BIGINT 以「分」为单位存储 血泪教训 |
| 状态用 TINYINT + 字典表 | 省空间、可扩展，含义集中在字典表维护 | 0 待支付 / 1 已支付 / 2 已取消…；ENUM 加值要 DDL，魔法数字必须配字典表或枚举类 |
| 时间用 DATETIME(3) | 毫秒精度排障有用，范围到 9999 年 | TIMESTAMP 只到 2038 年且受时区影响；跨时区业务更要统一 DATETIME + UTC 约定 |
| 字符集统一 utf8mb4 | MySQL 的 utf8 是 3 字节残缺实现，存不了 emoji 与生僻字 | 建库建表建连接三处统一；排序规则用 8.0 默认 utf8mb4_0900_ai_ci，混用会索引失效 |
| 必备 created_at / updated_at | 排障与对账的生命线，缺了只能翻 binlog | DEFAULT CURRENT_TIMESTAMP(3) + ON UPDATE 自动维护，不依赖业务代码 |
| 软删除用 deleted 标记 | 数据可恢复、审计有据，物理删除无法挽回 | 注意与唯一索引冲突：把标记列并入唯一键如 (email, deleted)，或用 deleted_at 时间戳 易踩坑 |
| 每表每字段写 COMMENT | 半年后没人记得字段含义，注释即文档 | 表注释写业务域与写入方，字段注释带单位与取值范围；配合 information_schema 可自动生成数据字典 |