AI Knowledge

外观

SQL 与 MySQL 底层机制、事务锁和性能优化专项面试题 ​

目录 ​

1. 使用说明 ​

单一事实源为正式主题。默认参考 MySQL 8.4/InnoDB；目标版本不明时先说明，避免把版本优化说成永恒保证。

2. 递进路线 ​

图：SQL 与 MySQL L1～L7 递进路线

替代文本： 从删除语义和 SQL 正确性进入索引、执行计划、MVCC/锁、生产故障、数据架构和项目复盘。

图表加载中…

读图结论： 高级回答要把一条 SQL 的语义、访问路径、锁范围和生产证据串起来。

2.1 主题专属排障图 ​

图：慢 SQL 与锁等待分流树

替代文本： 固定 SQL、参数和版本后，先区分执行慢与等待；执行慢检查扫描、回表、排序和估算，等待检查事务、锁图和扫描范围，修复后回放同数据与并发。

图表加载中…

读图结论： 慢 SQL 与锁等待可能互相影响，必须同时保留执行计划和事务证据。

3. L1～L7 压力面试 ​

第 1 题｜L1｜DELETE、TRUNCATE、DROP 有什么区别？ ​

核心考察点｜ DML/DDL 与恢复边界。

面试官提问

不要只回答“删数据、清表、删表”。

30 秒专业短答

DELETE 是可带条件的 DML，InnoDB 中可在事务内回滚并触发 DELETE Trigger；TRUNCATE TABLE 在 MySQL 中按 DDL 处理，快速清空、隐式提交、通常重置自增且不触发 DELETE Trigger；DROP TABLE 删除数据、索引和表定义。空间回收、外键和具体实现还要按引擎与版本核验。

深入展开

大量 DELETE 会产生 Undo/Redo、锁和复制成本，应按索引小批次并设计归档恢复。不能说 DELETE 永远不释放磁盘空间。

小白解释

删除部分档案、清空档案柜和拆掉整个档案室分别对应三种操作，回收场地方式还取决于仓库结构。

事实与证据边界

默认基于 MySQL 8.4/InnoDB；其他数据库语义不能照搬。

  • 合格线｜ 粒度、事务、Trigger、结构；
  • 加分项｜ 自增、空间、批量删除；
  • 工程证据｜ 事务实验、Schema、日志与恢复演练；
  • 高频误区｜ “TRUNCATE 等于 DELETE 后重建”；
  • 下一问｜ InnoDB 的主键和二级索引如何组织？

第 2 题｜L2｜联合索引最左原则到底是什么？ ​

核心考察点｜ 查询形状与索引键序。

面试官提问

(a,b,c) 索引中 WHERE 写成 b=? AND a=? 是否失效？跳过 a 呢？

30 秒专业短答

WHERE 文本书写顺序通常不决定索引使用，优化器会分析条件；关键是索引键按 (a,b,c) 排序。a=? AND b=? 能形成连续前缀，只有 b=? 通常不能同样高效定位；Skip Scan 只是在特定数据分布和成本下的候选，不能替代正确索引设计。

深入展开

范围条件后续列可能用于过滤但通常不能继续同等范围定位；覆盖、排序和选择性要结合计划。二级索引叶子带主键，非覆盖字段需要回表。

小白解释

电话簿先按姓再按名排序，交换查询条件写法不改变目录顺序；只知道名字很难直接定位。

事实与证据边界

最终看真实数据统计和 EXPLAIN，不承诺优化器固定选择。

  • 合格线｜ 区分 WHERE 顺序和索引顺序；
  • 加分项｜ 范围、覆盖、回表、Skip Scan；
  • 工程证据｜ 不同数据分布执行计划；
  • 高频误区｜ “高版本会自动重排索引字段”；
  • 下一问｜ MVCC 与锁如何共同工作？

第 3 题｜L3｜MVCC、Read View、Undo 和锁是什么关系？ ​

核心考察点｜ 一致性读与当前读边界。

面试官提问

可重复读是不是完全不加锁？

30 秒专业短答

普通一致性读主要通过 Undo 版本链和 Read View 找到可见版本，通常不对读取记录加锁；写操作和 FOR UPDATE/FOR SHARE 属于锁定读，会按实际索引扫描范围加记录或 Next-Key Lock。READ COMMITTED 常每次一致性读建立新快照，REPEATABLE READ 通常复用首次一致性读快照，具体锁行为还受唯一条件和索引影响。

深入展开

长事务阻碍旧版本清理；Redo 服务崩溃恢复，Undo 服务回滚/MVCC，Binlog 服务复制/恢复，不能混写。

小白解释

老读者看自己开始时的账页副本，修改库存的人则需要占用当前货架；历史账页和货架锁职责不同。

事实与证据边界

按隔离级别、SQL、索引和版本做并发实验。

  • 合格线｜ 一致性读与锁定读分开；
  • 加分项｜ Read View 时机、Undo/Redo/Binlog；
  • 工程证据｜ 两会话实验、事务/锁视图；
  • 高频误区｜ “MVCC 就完全不加锁”；
  • 下一问｜ 如何读执行计划并写深分页 SQL？

第 4 题｜L4｜如何用执行计划优化深分页？ ​

核心考察点｜ 计划、稳定排序和方案边界。

面试官提问

LIMIT 100000,20 怎么优化？先查第十万行 ID 是否已经解决？

30 秒专业短答

大 Offset 仍需扫描并丢弃前面记录。若先从覆盖窄索引取主键再回表，只是减少宽行回表成本，未消除 Offset；连续翻页优先使用稳定唯一排序的 Keyset，例如 (created_at,id) 游标。是否需要任意跳页、数据变更下允许重复/遗漏，是选型前提。

深入展开

读计划要看实际索引、扫描/返回行、回表、排序、临时表和 loop；EXPLAIN ANALYZE 会执行查询，只在安全条件下使用。

小白解释

从十万张档案中数到目标页仍要数；使用窄目录数更省搬运，记住上次档案号才能从当前位置继续。

事实与证据边界

id > last_id 只有在 ID 与排序一致时成立。

  • 合格线｜ 区分 Offset、延迟关联、Keyset；
  • 加分项｜ 稳定排序和数据漂移；
  • 工程证据｜ 扫描行、P99、不同页深；
  • 高频误区｜ “先查 ID 就是 O(1)”；
  • 下一问｜ 锁等待和死锁如何排查？

第 5 题｜L5｜死锁与大范围锁如何闭环处理？ ​

核心考察点｜ 索引扫描与事务恢复。

面试官提问

更新一行为什么可能锁很多行？死锁后重试哪部分？

30 秒专业短答

InnoDB 通常按执行时扫描的索引记录加锁，缺少合适索引可能扫描并锁住很大范围；不同事务加锁顺序不一致会形成循环等待。先保存 deadlock graph、事务 SQL 和计划，止损限并发；长期加合适索引、缩短事务、统一锁序。死锁受害事务已回滚，应对整个可幂等事务有界重试，不是只重放最后一条 SQL。

深入展开

重试有退避和总预算，外部副作用移出事务或通过 Outbox；锁超时与死锁错误分开处理。

小白解释

两人分别拿着不同仓库钥匙等待对方，必须统一取钥匙顺序；管理员撤销其中一个人的整次办理。

事实与证据边界

没有锁图和计划时不凭 SQL 文本断言锁范围。

  • 合格线｜ 扫描锁、锁序、整个事务重试；
  • 加分项｜ 幂等、Outbox、退避；
  • 工程证据｜ deadlock graph、计划、并发回放；
  • 高频误区｜ “死锁只会发生在多行更新”；
  • 下一问｜ 数据访问架构怎样预防这些问题？

第 6 题｜L6｜如何设计高并发订单数据库访问？ ​

核心考察点｜ 约束、事务、索引和读写边界。

面试官提问

订单状态、列表、导出和异步消息如何组织？

30 秒专业短答

数据库以主键、唯一约束和状态条件守住订单不变量；写事务短小并按稳定顺序锁定，业务与 Outbox 同事务提交。在线列表使用匹配租户/状态/排序的索引和 Keyset，任意跳页另做受控查询；大导出走快照或异步批次。读副本只承担读延迟可接受的查询，不能处理刚写后强一致判断。

深入展开

连接池按实例×Worker 预算，慢 SQL、锁等待、事务时长、复制延迟统一观测；DDL 采用兼容演进。

小白解释

柜台实时改账、后台批量报表和寄送通知使用不同通道，但都引用同一订单号和账本事实。

事实与证据边界

具体索引列由查询集合和数据分布决定，不给万能字段顺序。

  • 合格线｜ 约束、事务、索引、分页、Outbox；
  • 加分项｜ 读副本和连接预算；
  • 工程证据｜ Schema、计划、容量与故障测试；
  • 高频误区｜ “读写分离解决所有数据库压力”；
  • 下一问｜ 如何可信讲一次 SQL 优化项目？

第 7 题｜L7｜SQL 优化项目怎样回答？ ​

核心考察点｜ 证据与副作用。

面试官提问

请讲一次慢 SQL 或并发问题优化。

30 秒专业短答

我先给业务影响、SQL、数据量级和版本，再用慢日志、执行计划或锁图证明扫描、回表、排序或等待根因；说明为何改索引/查询/事务而不是强制索引或扩容。验证在相同数据与并发下比较 P99、扫描行、锁等待和写入成本，并说明新增索引的空间与写放大边界。

深入展开

没有实测数据只讲设计；若涉及生产删除，还要说明备份、批次、复制、回滚与授权。

小白解释

展示原来搬了多少档案、改目录后搬了多少，同时说明新增目录让入库变慢多少。

事实与证据边界

估算计划不等于实际结果，测试数据分布必须接近目标环境。

  • 合格线｜ 现象、证据、决策、验证、代价；
  • 加分项｜ 反方案和回滚；
  • 工程证据｜ SQL、计划、锁图、压测；
  • 高频误区｜ “加索引后快了很多”无控制变量；
  • 下一问｜ 查询分布变化后，你如何发现并撤销失效索引？

4. 自测与评分 ​

五维各 0～5 分，总分 20 且准确性至少 4。必须完成两会话 MVCC/锁实验、一次死锁回放、三种分页计划和四道现场 SQL。

5. 事实边界 ​

默认 MySQL 8.4/InnoDB；其他版本和数据库需重新核验。题中故障为演练，不代表用户生产事故；没有真实计划不得宣布索引收益。

6. 总结 ​

一句话记忆： SQL 回答要从语义和不变量开始，经索引、版本、锁和计划，最终落到可重复验证的生产证据。

  • 删除操作不能只背三个名词；
  • WHERE 书写顺序不等于联合索引键顺序；
  • MVCC 一致性读与锁定读分开回答；
  • 深分页必须说明稳定排序和跳页边界；
  • 死锁和慢 SQL 都要用计划、锁图和同负载回归。