第 10 章 MySQL：存储与数据库

MySQL 是多数业务系统的核心事务存储。对于 3 到 10 年工程师来说，面试官通常不会只问“索引是什么”“事务四大特性是什么”，而是会把问题放进真实业务里：订单列表为什么慢、库存为什么超卖、支付回调为什么重复、主从延迟为什么导致用户看不到刚下的订单、单表过大后为什么不能只靠加机器解决。

因此，本章的目标不是堆 MySQL 命令，而是建立一套面试和工程都能复用的决策框架：

• 从业务建模出发，判断哪些数据应该进入 MySQL，哪些不该进入 MySQL。
• 从表结构、索引和执行计划出发，解释查询性能问题。
• 从事务、锁、MVCC 和状态机出发，解释一致性问题。
• 从 redo、undo、binlog、复制和高可用出发，解释可靠性问题。
• 从容量、归档、分库分表和读模型出发，解释系统如何演进。

如果用一句话概括：MySQL 面试的核心不是“背概念”，而是能把业务约束、数据模型、索引设计、事务边界和扩展路径串成一条完整的工程链路。

MySQL 在电商系统中的定位

电商系统里，MySQL 通常承载的是“必须可信、可追溯、可恢复”的核心业务事实，而不是所有读请求的最终形态。

一个成熟系统通常不会让 MySQL 同时承担所有职责。常见分工是：

• MySQL 保存核心事实，例如订单、支付、库存流水。
• Redis 承担热点缓存、限流、活动预热和临时状态。
• Elasticsearch 承担商品搜索、订单后台检索等多条件查询。
• Kafka 或其他 MQ 承担异步通知、数据同步、削峰和最终一致性。
• HBase、ClickHouse、Hive 等承担历史归档和分析查询。

面试时如果被问“订单系统怎么设计存储”，好的回答一般不是一句“订单表分库分表”，而是先拆读写场景：

1. 买家下单、支付、取消、退款属于强一致写链路，核心状态落 MySQL。
2. 买家订单列表属于高频读，可以走 MySQL 索引、缓存或读模型。
3. 商家后台按多条件检索订单，适合同步到 Elasticsearch。
4. 超过一定时间的历史订单可以归档，在线库只保留热数据。
5. 财务对账与审计要保留不可篡改流水，不应只依赖订单当前状态。

这个拆法体现的是系统设计能力：先识别数据的权威来源，再为不同访问模式构建合适的读模型。

MySQL 架构与 InnoDB 基础

MySQL 可以粗略分成四层：

线上问题定位时，先判断瓶颈在什么层，比直接改 SQL 更有效：

• 连接打满，可能是应用连接池、慢查询堆积或数据库连接配置问题。
• CPU 高，可能是大量排序、函数计算、低选择性索引或并发过高。
• IO 高，可能是 Buffer Pool 命中率低、刷脏页、临时表落盘或大查询扫表。
• 锁等待高，可能是事务过长、索引未命中、加锁顺序混乱或热点行竞争。

InnoDB 是大多数业务库默认选择，因为它同时提供事务、行锁、崩溃恢复和 MVCC。

InnoDB 的几个核心组件要能和线上现象对应起来：

面试中不要孤立背这些名词。更好的回答方式是：InnoDB 把数据按页组织在 B+ 树里，读写优先经过 Buffer Pool；事务修改先写 redo，旧版本写 undo；提交时通过 WAL 和刷盘策略保证持久性；崩溃后用 redo 重放、undo 回滚未完成事务。

表设计：把治理成本前置

表设计不是把字段放进去就结束。对电商系统来说，表结构决定了后续索引、事务、归档、分库分表和数据同步的成本。

主键与业务 ID

InnoDB 是聚簇索引组织表，主键会直接影响数据页组织和所有二级索引大小。主键设计通常遵循：

• 尽量短：二级索引叶子节点会保存主键值，主键越长，二级索引越大。
• 尽量稳定：主键不应跟随业务状态变化。
• 尽量单调：随机主键会增加页分裂和写放大。
• 避免把可变业务含义塞进主键：业务规则变更会非常痛苦。

电商里经常同时存在两类 ID：

订单表可以用自增或趋势递增的 BIGINT 作为内部主键，同时用 order_no 做唯一业务单号。分库分表后，order_no 往往还要编码时间、机房、分片或随机位，便于路由和排障。

字段类型与约束

字段类型决定存储成本和索引效率。常见建议如下：

• 金额用整数存分，避免浮点误差。
• 状态用 TINYINT 或 SMALLINT，并在代码中维护枚举含义。
• 时间字段统一使用明确语义，例如 created_at、updated_at、paid_at、deleted_at。
• 字符集默认 utf8mb4，避免后续表级字符集迁移。
• 除少数确实需要三值逻辑的字段外，尽量 NOT NULL 并给默认值。
• 大文本、JSON、图片地址列表等低频大字段，优先考虑垂直拆分。

示例订单表结构可以这样思考，而不是照抄字段：

CREATE TABLE order_main (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  buyer_id BIGINT UNSIGNED NOT NULL,
  seller_id BIGINT UNSIGNED NOT NULL,
  status TINYINT NOT NULL,
  total_amount BIGINT NOT NULL,
  pay_amount BIGINT NOT NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_buyer_status_created (buyer_id, status, created_at, id),
  KEY idx_seller_created (seller_id, created_at, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里的重点不是字段是否完整，而是几个设计意图：

• order_no 用唯一索引保障业务幂等。
• 买家订单列表按 buyer_id + status + created_at 建索引。
• 商家后台如果只是按商家和时间查，可以用 seller_id + created_at。
• 如果商家后台要按手机号、商品名、收货人、状态组合查询，就不应该强行全靠 MySQL 单表索引，通常要同步搜索读模型。

外键、约束与应用层一致性

很多互联网业务不会大量使用数据库外键，不是因为外键没价值，而是因为高并发、分库分表、异步解耦和跨服务调用会让外键约束变得很难扩展。

更常见的做法是：

• 数据库用主键、唯一键、非空约束兜住底线。
• 应用层用状态机、幂等键和事务边界保证业务一致性。
• 异步链路用消息、补偿任务和对账任务修复最终一致性问题。

比如订单和支付单不一定用外键绑定，但支付单必须有唯一支付流水号，订单状态更新必须带前置状态条件，支付成功消息必须可重放且幂等。

索引设计：从访问模式倒推

索引本质上是用写入成本和存储成本换查询效率。真正的索引设计不是“字段越多越好”，而是从高频访问模式倒推。

为什么 InnoDB 使用 B+ 树

InnoDB 主流索引结构是 B+ 树。和红黑树、B 树相比，B+ 树更适合磁盘和页缓存模型：

面试里常见追问是“一棵三层 B+ 树能存多少数据”。不要死背数字，应该说明估算逻辑：InnoDB 页默认常见大小是 16KB，非叶子节点存索引键和子节点指针，叶子节点存整行或二级索引条目。每层扇出越大，树高越低。实际容量受主键大小、行宽、页填充率影响，所以“几层树支撑百万到千万级数据”是估算，不是固定结论。

聚簇索引、二级索引与回表

InnoDB 的聚簇索引把主键和整行数据放在同一棵 B+ 树上。二级索引叶子节点保存的是二级索引列加主键值。

这带来几个重要结论：

• 通过主键查询通常最快，因为一次命中聚簇索引即可。
• 通过二级索引查询整行，通常要先命中二级索引，再用主键回表。
• 覆盖索引能避免回表，因为查询所需列都在二级索引里。
• 主键越大，所有二级索引都会变大。

例如：

SELECT order_no, status, pay_amount
FROM order_main
WHERE buyer_id = 1001 AND status = 20
ORDER BY created_at DESC
LIMIT 20;

如果索引是：

KEY idx_buyer_status_created (buyer_id, status, created_at, id)

查询可以较好地按买家、状态、时间定位。但如果 SELECT *，仍可能回表读取其他列。面试时提到覆盖索引，最好同时补一句：覆盖索引不是为了炫技，而是为了减少随机回表；但索引列过多会增加写入成本和存储成本，要围绕高频查询设计。

联合索引设计原则

联合索引遵循最左前缀原则，但工程上不能只背这句话。更实用的判断顺序是：

1. 等值条件优先放在前面，例如 buyer_id、status。
2. 范围条件之后的列通常不能继续用于精确定位，只能部分用于过滤或排序。
3. 排序字段要和过滤条件组合考虑，避免额外 filesort。
4. 覆盖索引只覆盖高频轻量查询，不要把大字段塞进索引。
5. 区分度高不等于一定放最前，字段顺序要服务具体查询模式。

以订单列表为例，用户最常见查询是：

SELECT id, order_no, status, pay_amount, created_at
FROM order_main
WHERE buyer_id = ?
  AND status = ?
ORDER BY created_at DESC, id DESC
LIMIT 20;

可以考虑：

KEY idx_buyer_status_created_id (buyer_id, status, created_at, id)

如果业务更常见的是“查询某个用户全部订单，不一定按状态筛选”，还需要评估是否补充：

KEY idx_buyer_created_id (buyer_id, created_at, id)

但不能机械地为每种组合建索引。索引过多会带来：

• 写入、更新和删除变慢。
• Buffer Pool 被更多索引页占用。
• DDL 成本增加。
• 优化器选择成本上升。

一个好习惯是把索引和 SQL 场景写在一起评审，而不是只评审表结构。

索引失效与执行计划

常见导致索引效果变差的原因包括：

• 对索引列做函数运算，例如 DATE(created_at) = '2026-04-01'。
• 隐式类型转换，例如字符串列用数字条件比较。
• 前导模糊匹配，例如 LIKE '%phone'。
• 低选择性字段单独建索引，例如只有几个状态值的 status。
• OR 条件跨多个字段，导致优化器难以使用单个索引。
• 范围条件过宽，优化器认为扫表更便宜。
• 统计信息不准，执行计划选错。

排查慢 SQL 时，EXPLAIN 至少关注：

EXPLAIN 不是终点。更严谨的排查还要结合慢日志、实际执行耗时、扫描行数、返回行数、索引统计信息和业务流量峰值。

查询优化：从慢 SQL 到访问模式重构

SQL 优化的第一原则是：先确认瓶颈，再决定手段。不要看到慢 SQL 就立刻加索引。

慢 SQL 排查路径

一条线上 SQL 很慢，可以按下面顺序排查：

1. 看慢日志，确认 SQL 模板、耗时、扫描行数和返回行数。
2. 看 EXPLAIN，确认索引、Join 顺序、排序和临时表。
3. 看业务参数分布，确认是不是少数大客户、爆款商品或异常时间范围。
4. 看锁等待，确认慢是执行慢还是等待慢。
5. 看资源指标，确认 CPU、IO、Buffer Pool、连接数是否异常。
6. 再决定是改 SQL、补索引、拆查询、加缓存、建读模型还是做归档。

这个顺序很适合面试回答，因为它体现你不是“头痛医头”，而是在定位瓶颈。

深分页

深分页是电商后台和订单列表常见问题。下面这个 SQL 看似只取 20 条：

SELECT id, order_no, status, created_at
FROM order_main
WHERE buyer_id = ?
ORDER BY created_at DESC
LIMIT 100000, 20;

数据库通常仍要扫描、排序或跳过大量记录。常见优化有三类。

第一类是延迟关联，先用索引取主键，再回表：

SELECT o.id, o.order_no, o.status, o.created_at
FROM order_main o
JOIN (
  SELECT id
  FROM order_main
  WHERE buyer_id = ?
  ORDER BY created_at DESC
  LIMIT 100000, 20
) t ON o.id = t.id;

它能减少回表数据量，但不能从根上消除大偏移扫描。

第二类是游标分页，也叫 keyset pagination：

SELECT id, order_no, status, created_at
FROM order_main
WHERE buyer_id = ?
  AND (created_at < ? OR (created_at = ? AND id < ?))
ORDER BY created_at DESC, id DESC
LIMIT 20;

它适合 App 和 C 端列表，因为用户通常只是连续向下翻页，不需要精确跳到第 5000 页。

第三类是读模型重构。比如商家后台要求按多个条件筛选历史订单，并支持任意页跳转，这类需求不一定适合直接压在订单主表上，可以同步到 Elasticsearch 或专门的后台查询表。

Join 与反范式

面试里经常有人把“禁止 Join”当成经验。更准确的说法是：核心高并发链路要谨慎 Join，大查询和跨业务域 Join 要尽量避免。

订单详情页如果每次都 Join 用户、商品、优惠、支付、履约多张表，稳定性会很差。常见做法是：

• 写入时适度冗余订单快照，例如商品标题、购买价、店铺名。
• 核心链路按主键或唯一键查询，避免复杂 Join。
• 后台分析和报表走离线或近实时数仓。
• 搜索和复杂筛选走专门读模型。

反范式不是不讲一致性，而是把“交易事实”和“展示快照”分开。订单里的商品标题是下单时快照，即使商品后来改名，历史订单也不应该跟着变。

事务、MVCC 与隔离级别

事务的价值是把多条操作封装成一个具备原子性、一致性、隔离性和持久性的整体。对工程师来说，更重要的是知道什么时候需要事务、事务边界怎么划、长事务有什么风险。

隔离级别

MySQL InnoDB 默认隔离级别通常是可重复读。

要区分快照读和当前读：

• 普通 SELECT 通常是快照读，读的是 ReadView 可见版本。
• SELECT ... FOR UPDATE、UPDATE、DELETE 是当前读，要读取最新版本并加锁。

很多幻读问题的争议都来自没有区分这两类读。InnoDB 在可重复读下，快照读通过 MVCC 保持一致视图；当前读则通过 Next-Key Lock 等机制控制并发插入。

MVCC

MVCC 的核心由三部分组成：

• 隐藏列：记录事务 ID 和回滚指针。
• undo log：保存旧版本，形成版本链。
• ReadView：记录当前活跃事务范围，判断哪个版本可见。

RC 和 RR 的关键差异在 ReadView 创建时机：

• RC：每次快照读都创建新的 ReadView。
• RR：事务内第一次快照读创建 ReadView，后续复用。

因此 RR 下同一事务里两次普通查询结果可以保持一致，而 RC 下第二次查询可能看到其他事务已经提交的数据。

面试回答 MVCC 时，可以这样组织：

1. InnoDB 不直接覆盖旧数据，而是通过 undo log 保存旧版本。
2. 每行有事务 ID 和回滚指针，可以沿版本链找到历史版本。
3. 事务读取时根据 ReadView 判断哪些版本可见。
4. 这样读写可以并发，普通读不必阻塞写，写也不必阻塞普通读。
5. 但当前读和写冲突仍要靠锁解决。

锁、库存扣减与订单状态机

锁是 MySQL 面试最容易从概念走向工程的部分。不要只背行锁、间隙锁、Next-Key Lock，要能说清楚它们在库存、订单和支付里的作用。

行锁、Gap Lock 与 Next-Key Lock

InnoDB 行锁是加在索引记录上的。这个细节非常重要：

• 条件命中唯一索引，锁范围通常很小。
• 条件命中普通索引，可能锁住多条索引记录。
• 条件没有命中索引，可能扫描并锁住大量记录。

几类锁可以这样理解：

如果面试官问“为什么 SELECT ... FOR UPDATE 变成锁表”，好的回答是：它不是字面意义一定锁全表，而是如果条件没有走索引，InnoDB 需要扫描大量记录并加锁，效果上接近大范围锁，导致并发更新被阻塞。

库存扣减：不要先查再扣

库存扣减是电商最经典的一致性场景。错误写法通常是：

SELECT available_stock
FROM sku_stock
WHERE sku_id = ?;

UPDATE sku_stock
SET available_stock = available_stock - ?
WHERE sku_id = ?;

如果没有事务和锁保护，并发下很容易超卖。更常见的安全写法是条件更新：

UPDATE sku_stock
SET available_stock = available_stock - ?,
    locked_stock = locked_stock + ?,
    updated_at = NOW()
WHERE sku_id = ?
  AND available_stock >= ?;

然后检查影响行数：

• affected_rows = 1：扣减成功。
• affected_rows = 0：库存不足或记录不存在。

这类写法利用 MySQL 单行更新的原子性，避免“先查再改”的竞态。对于多 SKU 订单，还要注意：

• 按 sku_id 排序后统一扣减，降低死锁概率。
• 每个扣减动作写库存流水，便于回滚、审计和补偿。
• 下单失败、支付超时、订单取消时释放锁定库存。
• 秒杀等极高并发场景可以前置 Redis 预扣，但最终仍要落 MySQL 事实和流水。

乐观锁与订单状态机

订单状态流转更适合用状态机和乐观更新控制。例如支付成功回调只允许把 UNPAID 改成 PAID：

UPDATE order_main
SET status = 20,
    paid_at = NOW(),
    updated_at = NOW()
WHERE order_no = ?
  AND status = 10;

这条 SQL 的关键是 status = 10。它保证重复回调、乱序回调或人工操作不会随便覆盖状态。更新后必须检查影响行数：

• affected_rows = 1：状态流转成功。
• affected_rows = 0：订单不存在，或已经处理过，或状态不允许流转。

如果表里使用 version 字段，写法类似：

UPDATE order_main
SET status = ?,
    version = version + 1,
    updated_at = NOW()
WHERE order_no = ?
  AND version = ?;

乐观锁的重点不是有 version 字段，而是更新后必须判断影响行数，否则代码会把“更新失败”误认为“更新成功”。

悲观锁适合什么场景

悲观锁适合冲突概率高、必须串行处理的关键资源。例如账户余额、同一张优惠券核销、同一笔售后单处理。典型写法是：

SELECT id, balance
FROM account
WHERE user_id = ?
FOR UPDATE;

使用悲观锁要注意：

• 查询条件必须命中索引。
• 事务内不要调用慢外部接口。
• 锁住资源后尽快更新并提交。
• 多资源加锁要统一顺序，减少死锁。

一个常见事故是：在事务里先锁订单，再调用支付渠道或库存服务，外部调用慢导致数据库锁长期持有，最终拖垮整个订单库。正确做法通常是把外部调用放到事务外，事务内只做必要的状态校验和落库。

死锁治理

死锁不是 MySQL 异常，而是高并发系统的常见现象。重点不是追求永不死锁，而是降低概率并做好重试。

常见治理手段：

• 多资源操作统一顺序，例如多个 SKU 按 sku_id 升序扣减。
• 缩短事务时间，事务中不做 RPC、不做大批量循环。
• 确保更新条件命中索引，缩小锁范围。
• 拆分大事务，避免一次锁太多行。
• 对可重试事务做有限次数重试，并保证幂等。

面试里如果被问“线上死锁怎么排查”，可以回答：

1. 查看死锁日志或 InnoDB status，找到两边 SQL 和锁等待关系。
2. 确认 SQL 是否命中索引，是否扫描了过大范围。
3. 确认事务代码路径，是否存在相反顺序加锁。
4. 缩小事务范围，统一加锁顺序。
5. 对业务允许的场景增加重试和幂等。

日志、崩溃恢复与复制

MySQL 的可靠性离不开 undo log、redo log 和 binlog。它们回答的是不同问题。

WAL 与崩溃恢复

InnoDB 使用 WAL 思想：事务修改数据时，不必立刻把脏页刷到磁盘，而是先保证 redo log 持久化。崩溃后根据 redo log 重放已提交修改，再配合 undo 回滚未提交事务。

innodb_flush_log_at_trx_commit 常见取值：

电商交易链路通常更偏向安全，而不是为了少量性能牺牲提交持久性。非核心日志类数据可以根据业务损失窗口做不同选择。

redo log 与 binlog 的一致性

redo log 用于 InnoDB 崩溃恢复，binlog 用于复制和数据订阅。两者必须保持一致，否则可能出现主库恢复后数据存在，但从库和下游没有对应 binlog，或反过来。

MySQL 通过两阶段提交协调 redo 和 binlog，大致过程是：

1. InnoDB 写 redo prepare。
2. MySQL Server 写 binlog。
3. InnoDB 写 redo commit。

面试中如果被问“为什么需要两阶段提交”，可以回答：因为 redo 和 binlog 属于不同日志体系，如果不协调，崩溃时可能出现主库事务状态和复制日志不一致，影响主从复制和数据恢复。

binlog 与数据订阅

电商系统经常通过 binlog 同步数据到其他系统：

• 订单数据同步到 Elasticsearch，支撑商家后台检索。
• 商品变更同步到缓存和搜索索引。
• 支付状态同步到账务、履约和通知系统。
• 用户行为或订单事实同步到数仓。

这类 CDC 链路要注意：

• 下游消费必须幂等。
• 乱序和重复要能处理。
• 大事务会阻塞同步延迟。
• 表结构变更要和订阅方兼容。
• 删除操作要考虑软删和审计要求。

主从复制、读写分离与高可用

主从复制是 MySQL 高可用和读扩展的基础形态。主库写入 binlog，从库拉取并重放。

主从延迟为什么发生

主从延迟常见原因包括：

• 主库写入流量过大，从库重放追不上。
• 大事务或大批量 DDL 阻塞复制。
• 从库机器规格弱于主库。
• 从库同时承担大量慢查询。
• 网络抖动或复制线程异常。

读写分离后，延迟会变成业务一致性问题。比如用户刚支付成功，订单详情页如果读从库，可能仍然显示“待支付”。

常见兜底策略：

• 写后短时间内读主库，保证 read-your-writes。
• 对订单详情、支付结果、库存确认等强一致读走主库。
• 监控复制延迟，超过阈值时熔断从库读。
• 根据 GTID 或位点等待从库追上，但要设置超时。
• 对后台列表、历史查询、非关键统计允许读从库。

面试回答时要把“哪些读必须回主”说清楚：

故障切换

主库故障后，需要从从库中选新主，并让应用切换写入。这里的难点不是“能不能切”，而是：

• 新主是否拥有最完整数据。
• 老主恢复后如何避免双主写入。
• 应用连接如何发现新主。
• 复制拓扑如何重建。
• 切换过程中业务是否允许短暂不可用。

常见方案包括 MHA、Orchestrator、云数据库高可用方案和自研管控平台。面试不一定要深入某个工具，但要说明高可用不是只有数据库内部问题，还包括应用路由、连接池刷新、数据一致性和故障演练。

多主与单主

多主写入听起来能提高可用性，但会引入冲突解决、全局唯一 ID、跨地域延迟和一致性问题。大多数交易系统更常见的是：

• 单主写入，主从高可用。
• 按业务域拆库，各自单主。
• 分库分表后，每个分片单主。
• 异地多活场景按业务规则拆写入归属，而不是任意多点同时写同一份数据。

一句实用判断：交易事实尽量避免多点同时写同一行或同一业务对象，除非你已经有非常明确的冲突解决模型。

分库分表、归档与容量演进

分库分表是 MySQL 面试大题，但它不是第一选择。好的回答要先说明什么时候不分，什么时候必须分，分了之后会引入什么新成本。

单库单表先做到合理

在讨论分库分表前，先做这些事：

1. 表结构和索引是否合理。
2. 慢 SQL 是否已经治理。
3. 是否有缓存或读模型承接高频读。
4. 历史数据是否可以归档。
5. 大字段是否可以垂直拆分。
6. 是否可以按业务域垂直拆库。

“单表多少行要分表”没有固定答案。500 万、2000 万只是经验区间，真正要看：

• 行宽和索引大小。
• 热数据比例和 Buffer Pool 命中率。
• 高频查询是否稳定命中索引。
• 写入 QPS 和更新热点。
• DDL、备份、恢复和归档耗时。
• 业务是否已经遇到容量或性能瓶颈。

面试里不要只说“超过 2000 万就分表”。更好的回答是：我会先看访问模式和增长趋势，如果索引高度、Buffer Pool、慢查询、备份恢复和 DDL 成本都开始不可控，再考虑分表，并且会优先通过归档和读模型降低在线库压力。

垂直拆分

垂直拆分有两类。

垂直分表：把低频大字段拆出去。例如订单主表保留状态、金额、用户、时间等核心字段，把扩展信息、备注、发票、地址快照放到扩展表。

垂直分库：按业务域拆库。例如用户库、商品库、订单库、库存库、支付库、营销库。它的价值是：

• 降低单库复杂度。
• 隔离故障域。
• 让不同业务域独立扩容。
• 减少跨团队变更冲突。

垂直拆分的代价是跨库 Join 消失，应用层要通过服务接口、冗余快照、异步同步和读模型解决查询需求。

水平分片

水平分片要先选分片键。订单系统常见候选有：

实际系统里经常组合使用：

• 订单主写入按 buyer_id 或 order_no 分片。
• 单号里编码分片信息，便于直接路由。
• 商家后台查询走搜索读模型。
• 财务和运营分析走数仓。
• 历史订单按时间归档。

分片后要解决的问题包括：

• 路由：请求如何定位到库表。
• 唯一 ID：如何生成全局唯一业务单号。
• 跨分片查询：如何查多个分片并合并排序。
• 分布式事务：如何避免跨分片强事务。
• 扩容再平衡：如何从 128 张表扩到 256 张表。
• 运维治理：备份、恢复、DDL、监控如何批量执行。

跨分片查询与读模型

很多分库分表失败，不是因为写入分散不了，而是因为查询需求没想清楚。

例如订单分片按 buyer_id，买家列表很好查；但商家后台要按 seller_id + status + created_at + phone 查订单，就会变成跨分片扫描。常见解法不是硬查所有分片，而是：

• 建商家订单读模型。
• 同步到 Elasticsearch。
• 后台查询限制时间范围和条件。
• 把复杂报表放到数仓。

这也是电商系统常见 CQRS 思路：写模型围绕交易一致性设计，读模型围绕查询效率设计。

历史归档

订单、支付、库存流水都天然随时间增长。在线库不应该无限承载所有历史数据。

归档设计要回答：

• 在线库保留多久热数据，例如 3 个月、6 个月、1 年。
• 归档库是否支持用户查询历史订单。
• 归档过程如何避免影响主库。
• 归档后订单详情、售后、发票、客服如何查询。
• 归档数据是否需要参与审计和对账。

常见做法是：

• 在线库保留近期订单。
• 历史库或冷存储保留全量订单。
• 用户历史订单查询通过归档服务或异步查询。
• 财务和审计走独立账务或数仓链路。

归档不是简单 DELETE。大批量删除会产生大量 undo、redo、binlog，还可能造成复制延迟。工程上通常要小批量、限速、可恢复，并配合监控。

DDL、连接池与线上治理

很多 MySQL 事故不是来自 SQL 写错，而是来自变更和治理不到位。

DDL 风险

加字段、改字段、加索引、删索引都可能带来：

• 元数据锁阻塞。
• 表重建。
• 大量 IO。
• 主从延迟。
• binlog 暴涨。
• 应用兼容问题。

MySQL 8 对部分 DDL 支持更好的在线能力，例如某些场景可以 INSTANT 加列，但不能假设所有 DDL 都无成本。不同版本、字段位置、索引类型和表结构都会影响 DDL 算法。面试里说“先确认 MySQL 版本和 DDL 算法”，最好能落到可执行动作。

第一步是确认数据库版本、发行版和表结构特征：

SELECT VERSION();
SHOW VARIABLES LIKE 'version%';
SHOW CREATE TABLE order_main\G
SHOW TABLE STATUS LIKE 'order_main'\G

要重点看：

• 是 MySQL 5.7、MySQL 8.0、MySQL 8.4，还是云厂商兼容版本；不同小版本的 Online DDL 能力差异很大。
• 表是否为 InnoDB，是否有 ROW_FORMAT=COMPRESSED、全文索引、函数索引、分区、外键、生成列等特殊结构。
• 本次 DDL 是加列、删列、改类型、改默认值、加索引、删索引，还是调整列顺序；不同操作对应的算法完全不同。
• 是否会修改行格式或重建表，例如 VARCHAR(255) 改到 VARCHAR(256)、字段类型变更、列顺序调整，这类操作经常比“加一个字段”危险得多。

第二步是明确 DDL 算法。常见算法可以这样理解：

判断方法不是靠猜，而是主动约束算法和锁级别。比如你希望这是元数据级变更，就显式指定：

ALTER TABLE order_main
  ADD COLUMN ext_info JSON NULL,
  ALGORITHM=INSTANT,
  LOCK=NONE;

如果当前版本或表结构不支持该算法，MySQL 会直接报错，而不是悄悄退化成更重的方式。对于加索引这类常见操作，也应该尽量显式指定期望：

ALTER TABLE order_main
  ADD INDEX idx_buyer_created (buyer_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

上线前还要在影子库或预发库用同等量级数据验证：真实耗时、是否重建表、临时空间增长、binlog 增长、主从延迟、业务 SQL 是否被元数据锁阻塞。不要把开发库的几万行测试结果，直接外推到生产几亿行大表。

大表 DDL 的稳妥流程：

1. 查清 MySQL 版本、表结构特征和本次 DDL 类型。
2. 查官方文档或云厂商文档，确认目标版本对该操作支持 INSTANT、INPLACE 还是只能 COPY。
3. 在 SQL 里显式指定 ALGORITHM 和 LOCK，让不符合预期的 DDL 直接失败。
4. 影子库或预发库用接近生产的数据量验证耗时、空间放大和主从延迟。
5. 选择低峰执行，必要时使用 gh-ost 或 pt-online-schema-change。
6. 监控主库负载、元数据锁、复制延迟、临时空间、binlog 增长和错误日志。
7. 应用发布采用 expand-contract 策略：先加字段兼容，再切流量，最后清理旧字段。
8. 准备回滚方案，并明确回滚是不是另一个更重的 DDL。

连接池治理

连接数不是越大越好。连接池过大可能把数据库从“排队”打成“雪崩”。

估算连接池要考虑：

• 应用实例数。
• 每个实例最大连接数。
• 数据库 max_connections。
• 平均 SQL 耗时和峰值 QPS。
• 是否有慢查询占住连接。

例如 50 个应用实例，每个实例连接池 100，总连接上限就是 5000。即使数据库允许这么多连接，真正同时执行 SQL 时，CPU、IO 和锁也未必扛得住。

常见治理手段：

• 按服务重要性设置不同连接池大小。
• 对慢接口限流，避免占满连接。
• 设置合理超时，避免连接长时间挂起。
• 监控活跃连接、等待连接和连接获取耗时。
• 对后台任务和在线链路使用不同账号或资源隔离。

监控指标

MySQL 监控至少覆盖这些维度：

面试里说监控时，不要只列指标。最好能说明指标和动作的关系：慢查询上升要看执行计划和流量参数；主从延迟上升要看大事务和从库慢查询；连接池等待上升要看数据库延迟和应用并发；磁盘水位上升要看归档和 binlog 清理。

电商典型场景设计

这一节把前面的概念放到几个真实面试题里。

场景一：下单扣库存如何防止超卖

目标：不能卖出超过真实库存，同时要支撑较高并发。

基础方案：

1. 订单请求先做幂等校验，例如 request_id 或购物车提交 token。
2. 库存表用条件更新扣减可售库存。
3. 扣减成功后写库存流水。
4. 创建订单并记录订单明细。
5. 支付超时或取消订单时释放锁定库存。

核心 SQL：

UPDATE sku_stock
SET available_stock = available_stock - ?,
    locked_stock = locked_stock + ?,
    updated_at = NOW()
WHERE sku_id = ?
  AND available_stock >= ?;

高并发秒杀场景可以进一步：

• Redis 预扣减少 MySQL 压力。
• MQ 削峰，异步创建订单。
• 用户限购和风控前置。
• MySQL 保留最终库存事实和流水。
• 对账任务校验 Redis、订单和库存流水一致性。

优秀回答要点：不要只说“加锁”，要说清楚条件更新、影响行数、库存流水、幂等、失败补偿和热点削峰。

场景二：支付回调重复怎么办

支付渠道可能重复通知，网络也可能重试。系统必须保证重复回调不会重复发货、重复加积分或重复记账。

常见设计：

• 支付流水号建立唯一索引。
• 回调原始报文落库，便于审计。
• 更新订单状态时带前置状态条件。
• 后续履约、积分、通知通过消息异步触发，并且消费端幂等。

示例：

INSERT INTO payment_callback_log (
  channel_trade_no,
  order_no,
  payload,
  created_at
) VALUES (?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE updated_at = NOW();

UPDATE order_main
SET status = 20,
    paid_at = NOW(),
    updated_at = NOW()
WHERE order_no = ?
  AND status = 10;

如果更新影响行数为 0，不一定是错误，可能是重复回调或状态已经流转。业务要查询当前状态后做幂等返回。

场景三：订单列表为什么越来越慢

常见原因：

• 单表数据持续膨胀，索引和热数据无法很好留在内存。
• 查询没有命中合适联合索引。
• 使用深分页。
• SELECT * 导致大量回表。
• 历史订单和近期订单混在同一张在线表。
• 商家后台复杂筛选压在交易库上。

治理路径：

1. 先看慢日志和执行计划。
2. 针对买家列表建立合适联合索引。
3. App 端改为游标分页。
4. 垂直拆分低频字段，减少行宽。
5. 历史订单归档。
6. 商家后台和客服检索走搜索读模型。
7. 数据继续增长后再考虑水平分片。

这个回答比“加索引”更完整，因为它覆盖了访问模式、数据生命周期和读模型分离。

场景四：主从延迟导致用户看到旧订单状态

现象：用户支付成功后跳转订单页，页面仍显示待支付。

原因：支付成功写主库，但订单详情读从库；从库复制有延迟。

解决：

• 支付成功后的订单详情读主库。
• 用户写操作后一段时间粘主。
• 延迟超过阈值时从库摘流。
• 对关键状态变更使用消息通知前端刷新时，也要保证查询源一致。
• 从根因上治理大事务、慢查询和从库资源不足。

面试追问通常是“那所有读都读主不就好了？”回答应该是：可以但会牺牲读扩展能力。更合理的是按一致性要求分级，强一致读回主，弱一致列表读从库。

常见线上问题排查手册

慢查询

排查路径：

1. 慢日志定位 SQL 模板。
2. EXPLAIN 看索引、扫描行数、排序和临时表。
3. 对比实际参数，确认是否有大商家、大用户或异常时间范围。
4. 看是否锁等待，而不是执行慢。
5. 优化 SQL、索引或读模型。

常见动作：

• 避免 SELECT *。
• 增加合适联合索引。
• 改写深分页。
• 拆分大查询。
• 把复杂检索迁到搜索系统。

锁等待

排查路径：

1. 查看当前执行 SQL 和阻塞链路。
2. 找出先持锁事务。
3. 确认事务是否长时间未提交。
4. 检查更新条件是否命中索引。
5. 检查代码里是否事务内调用外部服务。

常见动作：

• 缩短事务。
• 补充索引。
• 统一加锁顺序。
• 拆分批量任务。
• 对可重试事务增加幂等重试。

主从延迟

排查路径：

1. 看延迟从什么时候开始。
2. 查主库是否有大事务、大 DDL 或批量更新。
3. 查从库是否有慢查询占用资源。
4. 看网络和复制线程状态。
5. 判断是否需要临时摘除从库读流量。

常见动作：

• 大任务小批量提交。
• DDL 低峰执行。
• 从库避免跑重查询。
• 延迟过高时强一致读回主。
• 对归档、报表类任务限速。

连接耗尽

排查路径：

1. 看数据库连接数是否打满。
2. 看应用连接池等待是否上升。
3. 查是否有慢 SQL 占住连接。
4. 查是否有连接泄漏或事务未关闭。
5. 查发布或流量突增是否导致实例数变化。

常见动作：

• 限制非核心接口。
• 降低单实例连接池上限。
• 优化慢 SQL。
• 增加超时和熔断。
• 后台任务与在线流量隔离。

面试答题框架

MySQL 问题可以按四层回答：

1. 业务语义：这是订单、库存、支付还是后台查询？一致性要求是什么？
2. 数据模型：表怎么设计，主键、唯一键、状态、流水怎么设计？
3. 执行机制：索引、事务、锁、日志、复制如何支撑它？
4. 工程治理：慢 SQL、归档、分库分表、监控、补偿怎么做？

例如被问“如何设计订单表”，不要直接列字段。可以这样答：

1. 订单是交易事实，主表记录订单核心状态、金额、买卖双方、时间，明细表记录商品快照。
2. 主键用内部递增 ID，外部用全局唯一 order_no，并建唯一索引保证幂等。
3. 买家列表按 buyer_id + status + created_at 建联合索引，单订单按 order_no 查。
4. 状态流转用前置状态条件或版本号保证幂等。
5. 支付、履约、积分等通过 MQ 异步解耦，但消费端必须幂等。
6. 后台复杂检索走搜索读模型，历史订单做归档，数据量继续增长再分库分表。

这类回答体现的是工程经验，而不是背字段。

本章小结

MySQL 的工程主线可以总结为五句话：

• MySQL 保存核心业务事实，不负责承接所有查询形态。
• 表设计要提前考虑主键、唯一键、状态机、索引、归档和分片。
• 索引设计必须从访问模式倒推，而不是为每个字段机械建索引。
• 事务和锁要服务业务一致性，库存、订单、支付都要靠幂等和状态条件兜底。
• 复制、归档、读模型和分库分表是容量演进手段，但每一步都会引入新的治理成本。

对于 3 到 10 年工程师，面试官真正想听的是：你是否知道一个概念在真实业务里会带来什么收益、什么风险、如何排查、如何取舍。

本章面试题与优秀回答要点

9. 为什么 InnoDB 选择 B+ 树，而不是红黑树或 B 树？

优秀回答要点：

• 数据库存储按页读取，B+ 树扇出大、树高低，能减少磁盘 IO。
• B+ 树叶子节点有序链表，适合范围查询和排序。
• 红黑树适合内存结构，但树高大、节点分散，不适合磁盘页模型。
• B 树非叶子节点也存数据，范围扫描和页利用率通常不如 B+ 树适合 InnoDB 场景。

追问：三层 B+ 树大致能存多少数据？

答题方向：说明估算方法，而不是死背数字。页大小、主键大小、行宽、页填充率都会影响容量。

9. 聚簇索引、二级索引、覆盖索引和回表是什么关系？

优秀回答要点：

• 聚簇索引叶子节点保存整行数据。
• 二级索引叶子节点保存索引列和主键值。
• 二级索引查整行通常要通过主键回表。
• 覆盖索引是查询所需列都在索引里，可以避免回表。
• 主键越大，二级索引越大。

追问：为什么不把所有查询字段都放进覆盖索引？

答题方向：索引会增加写入成本、存储成本和 Buffer Pool 压力，只覆盖高频轻量查询。

9. 如何为订单列表设计索引？

优秀回答要点：

• 先明确查询模式：买家列表、商家列表、后台检索不是同一个问题。
• 买家按状态和时间翻页，可以设计 buyer_id + status + created_at + id。
• App 端优先游标分页，避免深分页。
• 商家后台复杂多条件查询不一定适合压在订单主表，可能需要 Elasticsearch。
• 索引设计要结合写入成本，不能为所有条件组合建索引。

追问：如果查询不带 status，原索引还能用吗？

答题方向：要看联合索引最左前缀，buyer_id 仍可用，但排序和过滤效果可能不同，必要时补充适配高频查询的索引。

9. MVCC 是如何实现可重复读的？

优秀回答要点：

• InnoDB 通过 undo log 保存旧版本。
• 行记录有事务 ID 和回滚指针，形成版本链。
• ReadView 判断哪些事务版本对当前事务可见。
• RR 下第一次快照读创建 ReadView，事务内复用，所以多次普通查询一致。
• 当前读仍然要靠锁处理并发写入。

追问：RC 和 RR 的 ReadView 有什么区别？

答题方向：RC 每次快照读创建新 ReadView，RR 事务内第一次快照读创建并复用。

9. SELECT ... FOR UPDATE 一定是行锁吗？

优秀回答要点：

• InnoDB 锁加在索引记录上。
• 条件命中唯一索引时，锁范围较小。
• 条件未命中索引时，可能扫描并锁住大量记录，效果接近锁表。
• 事务内持锁时间越长，阻塞越严重。

追问：如何降低锁范围？

答题方向：命中合适索引，缩短事务，避免事务内 RPC，拆分批量操作。

9. 库存扣减如何防止超卖？

优秀回答要点：

• 不要先查库存再扣减。
• 使用条件更新：available_stock >= quantity。
• 更新后检查 affected_rows。
• 写库存流水，支持审计、回滚和对账。
• 多 SKU 按固定顺序扣减，降低死锁。
• 秒杀场景可以 Redis 预扣和 MQ 削峰，但 MySQL 仍保存最终事实。

追问：Redis 扣成功但 MySQL 扣失败怎么办？

答题方向：需要补偿释放 Redis 预扣、返回失败或重试，最终以 MySQL 库存事实和流水对账。

9. 支付回调重复如何保证幂等？

优秀回答要点：

• 支付渠道流水号唯一索引。
• 回调日志落库。
• 订单状态更新带前置状态条件。
• 更新影响行数为 0 时，查询当前状态做幂等返回。
• 后续履约、积分、通知消费端也要幂等。

追问：为什么只靠分布式锁不够？

答题方向：锁只能降低并发，不保存业务事实；重复请求、重放消息、进程崩溃后仍要靠唯一键和状态机保证幂等。

9. redo log、undo log、binlog 分别解决什么问题？

优秀回答要点：

• undo log 保存旧版本，用于回滚和 MVCC。
• redo log 记录物理页修改，用于崩溃恢复。
• binlog 记录逻辑变更，用于复制、审计和数据订阅。
• redo 和 binlog 通过两阶段提交保持一致。

追问：为什么需要两阶段提交？

答题方向：避免崩溃时 redo 和 binlog 不一致，导致主库恢复状态和从库复制状态不一致。

9. 主从延迟会带来什么业务问题？

优秀回答要点：

• 写主读从可能读到旧数据。
• 支付成功页、订单详情、库存决策等强一致场景不能随便读从。
• 可以写后粘主、强一致读回主、监控延迟并摘流。
• 大事务、DDL、从库慢查询都可能造成延迟。

追问：哪些读可以读从库？

答题方向：历史列表、后台报表、弱一致统计可以读从或数仓；刚写后的关键状态读主。

9. 单表多少行需要分库分表？

优秀回答要点：

• 没有绝对阈值。
• 要看行宽、索引大小、热数据比例、QPS、慢查询、Buffer Pool、备份恢复和 DDL 成本。
• 先治理索引、SQL、缓存、垂直拆分和历史归档。
• 分库分表会引入路由、跨分片查询、扩容、分布式事务和运维成本。

追问：订单表按什么分片键？

答题方向：买家查询适合 buyer_id，单号查询适合 order_no，商家后台适合读模型；通常要结合业务主路径和辅助索引设计。

9. 分库分表后如何处理跨分片查询？

优秀回答要点：

• 尽量避免在线跨分片扫全量。
• 核心查询通过分片键路由。
• 商家后台、多条件检索走 Elasticsearch 或查询读模型。
• 报表分析走数仓。
• 必须跨分片时限制时间范围、并发查询、合并排序，并做好降级。

追问：扩容从 128 张表到 256 张表怎么做？

答题方向：提前设计可扩展路由；迁移要双写、校验、灰度切流、回滚；也可用逻辑分片避免频繁物理迁移。

9. 一条 SQL 很慢，你如何系统排查？

优秀回答要点：

• 先看慢日志和 SQL 模板。
• 用 EXPLAIN 看索引、扫描行数、排序和临时表。
• 看参数分布，确认是否热点用户或大时间范围。
• 判断是执行慢还是锁等待。
• 看数据库资源：CPU、IO、Buffer Pool、连接。
• 再决定补索引、改 SQL、拆查询、缓存、读模型或归档。

追问：Using filesort 一定有问题吗？

答题方向：不一定。小结果集 filesort 可接受；大结果集频繁 filesort 才需要通过索引、限制范围或读模型优化。

9. 大表 DDL 如何降低风险？

优秀回答要点：

• 先用 SELECT VERSION()、SHOW VARIABLES LIKE 'version%'、SHOW CREATE TABLE 确认数据库版本、发行版、表引擎、行格式、索引、分区、外键和生成列等信息。
• 再判断 DDL 类型：加列、删列、改类型、加索引、改默认值、调整列顺序，对应的 INSTANT、INPLACE、COPY 能力不同。
• 显式指定 ALGORITHM 和 LOCK，例如期望元数据变更就写 ALGORITHM=INSTANT, LOCK=NONE；如果不支持，让它在预发或低峰前失败，而不是线上静默退化。
• 影子环境用接近生产的数据量验证耗时、临时空间、binlog 增长、主从延迟和元数据锁影响。
• 大表高风险变更选择低峰执行，必要时用 gh-ost 或 pt-online-schema-change。
• 执行时监控元数据锁、主从延迟、IO、CPU、磁盘水位、错误日志和业务慢 SQL。
• 应用采用 expand-contract 兼容发布：先扩展字段和代码兼容，再切流量，最后收缩旧字段。
• 准备回滚方案，并确认回滚是否也需要重 DDL。

追问：为什么删字段比加字段更危险？

答题方向：删字段同时有兼容性风险和 DDL 风险。兼容性上，可能还有旧版本应用、定时任务、报表、数据同步、客服后台或风控规则在读写该字段；一旦删除，问题会立刻变成运行时错误或数据缺失。DDL 上，不同 MySQL 版本对 DROP COLUMN 的算法支持不同，可能是元数据级，也可能触发表重建、binlog 暴涨和主从延迟。稳妥做法是先让代码停止写，再停止读，保留一段观察期，通过 SQL 审计、代码搜索、binlog/CDC 订阅方确认无依赖，最后低峰删除。

9. 如何设计 MySQL 监控和告警？

优秀回答要点：

• 监控 QPS、延迟、慢查询、连接、锁等待、Buffer Pool、复制延迟、磁盘。
• 告警要能映射动作，例如延迟高摘从库、磁盘高触发归档或扩容。
• 区分主库、从库、在线链路、后台任务。
• 监控要覆盖应用连接池，不只看数据库本身。

追问：连接数打满时先扩连接可以吗？

答题方向：不一定。先确认是不是慢 SQL、锁等待或连接泄漏。盲目扩连接可能把数据库打得更慢。