从 MySQL 默认存储引擎 InnoDB 的索引结构讲起这是几乎每一场 Java 后端面试都绕不开的硬骨头。很多候选人能背出“B 树”“聚簇索引”“回表”这些名词但一旦面试官追问“为什么 MySQL 选 B 树而不是 B 树”“覆盖索引到底怎么减少了一次回表”“最左前缀原则的底层依据是什么”就明显露怯。这篇数据库篇二就集中把索引、事务、锁、SQL 优化这几个高频板块讲透每一块都会补充我实际面试别人和被别人面试时最常被追问的细节。1. B 树索引为什么它是 InnoDB 的绝对核心1.1 从数据页到 B 树一次查询是如何发生的先建立一个基本认知InnoDB 存储引擎操作数据的最小单位不是行而是数据页默认大小是 16KB。你执行一条SELECT * FROM user WHERE id 100MySQL 服务器层负责解析 SQL、生成执行计划真正去磁盘上把数据捞回来的是 InnoDB 存储引擎。InnoDB 会先把id 100这条记录所在的整个数据页加载到内存的 Buffer Pool 中然后在页内部通过二分查找定位到具体的行记录。当表里的数据页越来越多一个页一个页顺序扫描显然不现实于是就有了索引索引本身也是一棵 B 树它的叶子节点存的是主键值或者行数据。这里有一个面试官特别爱问的点为什么千万级数据量的表B 树的层高通常只有 3 到 4 层我习惯这么算给大家听非叶子节点也就是目录项页里每条索引项大概占 8 字节主键 6 字节 页号 4 字节取整估算一个 16KB 的页大约能存放 16 * 1024 / 8 2048 条索引项叶子节点存放完整行记录假设一行记录平均 1KB一个叶子页能放约 16 行三层 B 树能存放的记录数大约是2048第 1 层 * 2048第 2 层 * 16第 3 层叶子 ≈ 6700 万行。这就是为什么千万级表走主键查询依然能维持在毫秒级的原因走 B 树索引最多只需要 3 到 4 次磁盘 I/O而全表扫描在数据量大时可能需要上万次 I/O。面试时如果能当场把 8 字节、16KB、层高换算讲给面试官听比单纯背“B 树矮胖”要有说服力得多。1.2 聚簇索引与二级索引回表到底是怎么发生的InnoDB 的表数据本身就按照主键索引的 B 树组织这棵树的叶子节点直接存了整行数据所以它叫聚簇索引。你建的其他索引叫二级索引也叫辅助索引它的叶子节点存的是索引列的值 主键值不是完整的行数据。于是就有了“回表”这个概念SELECT * FROM user WHERE name 张三如果 name 上有索引会先去 name 的二级索引 B 树里找到主键 id拿着这个 id 再到主键聚簇索引的 B 树里查一次拿到完整行记录这两次查询合起来就叫回表。面试官常在这里挖坑“那我把SELECT *改成SELECT id, name还用回表吗”答案是不用。因为二级索引的叶子节点已经包含了 name 和 id 这两个字段查询所需的列在二级索引里全都能找到不需要再回聚簇索引这就是覆盖索引。实际开发中覆盖索引是优化 SQL 最立竿见影的手段之一尤其是针对高频查询的字段组合。我做项目时有一个习惯核心业务表的查询 SQL都会刻意检查一下 select 的列是否都能被某个二级索引覆盖。比如订单表经常按user_id查order_status和create_time那就建一个(user_id, order_status, create_time)的联合索引既能满足覆盖索引又能顺便给排序和分组提供帮助一举两得。1.3 联合索引与最左前缀原则索引下推的底层逻辑联合索引的匹配规则是面试高频中的高频。很多人背了“最左前缀原则”但讲不清楚为什么。我换一种方式解释联合索引(a, b, c)在 B 树里的排序规则是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。也就是说这个索引本质上是一个“先按 a 分组再在每个分组内按 b 排序再在更小分组内按 c 排序”的复合结构。所以你的查询条件必须包含最左列 a才能利用这个索引的排序规则定位数据。如果直接WHERE b 1 AND c 2跳过 a在 B 树里根本不知道从哪棵子树开始查索引就失去了指导意义。但这里有一个更细的考点MySQL 8.0 之后支持了索引跳跃扫描Index Skip Scan。即使查询条件里没有 a 列优化器在某些情况下也可能会自动扫描 a 的不同值来复用联合索引。不过这个特性有比较严格的触发条件比如 a 的区分度不能太高日常开发不能把宝押在它身上最稳妥的做法还是让查询条件老老实实贴合最左前缀。再往下挖一层还有索引下推Index Condition PushdownICP。MySQL 5.6 引入的特性它允许在存储引擎层直接用索引列进行过滤减少回表次数。举个例子联合索引(name, age)执行SELECT * FROM user WHERE name LIKE 张% AND age 20。没有 ICP 时存储引擎先用索引定位到所有name LIKE 张%的主键然后逐一回表把完整行捞出来再在 Server 层过滤age 20有 ICP 时存储引擎在索引遍历过程中直接判断age 20不满足条件的直接跳过少回表好多次。面试时主动把这个特性说出来再加上一句“这就是为什么联合索引里字段顺序的摆放不仅要考虑查询匹配还要考虑过滤下推”基本上就能让面试官觉得你是真做过优化的而不是单纯背概念。2. 索引失效场景与慢查询排查那些最容易翻车的细节2.1 八个最常见的索引失效场景逐个拆解索引失效是实际开发中最常见的性能杀手我给大家整理成一张对照表每一行都来自我线上环境真实踩过的坑场景示例失效原因正确姿势对索引列使用函数WHERE YEAR(create_time) 2024索引存的是原始值函数破坏了原始值的排序改成create_time 2024-01-01 AND create_time 2025-01-01隐式类型转换WHERE phone 13800138000phone 是 varcharMySQL 会把字符串列转成数字比较相当于对列用了 CAST 函数应用层传字符串类型参数前置模糊查询WHERE name LIKE %张字符串匹配必须从头开始才能走索引树使用name LIKE 张%或引入搜索引擎联合索引未遵循最左前缀WHERE b 1联合索引 a,b,c索引排序规则以最左列为第一关键字补上 a 列条件或调整索引字段顺序OR 连接非索引列WHERE id 1 OR status 2status 无索引优化器无法确定哪种路径代价低可能全表扫描拆成 UNION或给 status 加索引对索引列做计算WHERE price 10 100表达式结果无法匹配索引树中的值改写成WHERE price 90使用不等于或 NOT INWHERE status ! 1不等值查询很难利用有序树的定位能力分情况拆SQL或走全表缓存字符集不一致表A使用 utf8mb4关联表B使用 latin1关联比较时 MySQL 要做隐式字符集转换统一所有表的字符集2.2 隐式类型转换的坑比想象中更隐蔽上面表格里“隐式类型转换”这一行我特别想展开讲讲。很多人以为只有字符串列传数字才会有问题实际上反过来也一样如果索引列是 int 类型你传字符串 123MySQL 同样会尝试把字符串转成数字来比较。关键在于转换的方向——MySQL 通常是把字符串转换为数字所以索引列是 varchar传数字WHERE phone 13800138000相当于CAST(phone AS SIGNED) 13800138000索引列上套了函数失效索引列是 int传字符串WHERE id 100相当于id CAST(100 AS SIGNED)是参数被转换索引列没套函数索引还能用。这也解释了为什么字段定义的类型和传入的参数类型严格一致是索引生效的前提之一。我在代码评审时看到 JPA 或 MyBatis 的查询条件里如果实体字段是 String、数据库列却是 bigint都会直接提出来让改掉哪怕业务上暂时没问题。2.3 慢查询日志与 Explain 的配合用法排查慢 SQL 的标准链路我一般是这么走的先开启慢查询日志设置阈值比如超过 1 秒的 SQL 都记录下来SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow-query.log;拿到慢 SQL 之后用EXPLAIN看执行计划。这里我建议重点关注四列type从好到差依次是systemconsteq_refrefrangeindexALL。看到ALL全表扫描就要警惕了key实际用到的索引。如果key是 NULL说明这条 SQL 没走任何索引rows预估扫描的行数这个数字越小越好Extra出现Using filesort或Using temporary通常是性能隐患Using index是好消息代表覆盖索引生效。顺便说一个比较容易忽略的点有时候明明 SQL 已经建了索引EXPLAIN的key却显示 NULL大概率是优化器认为走索引还不如全表扫描。比如区分度太低的列如性别只有男/女两类优化器会估算需要扫描超过全表 30% 的数据此时它宁可全表也不用索引。这不是索引建错了而是区分度不够需要结合业务重新设计索引组合而不是硬加索引了事。3. 事务的隔离级别与 MVCC面试必背但很多人讲不透3.1 事务四大特性ACID到底由谁来保证事务这块Java 面试几乎是必考但很多候选人张口就是“原子性、一致性、隔离性、持久性”这四句话然后就没下文了。我会建议大家把“每个特性由什么机制保证”也一并记住原子性Atomicity由 undo log 保证。事务执行过程中所有未提交的修改都会先记录 undo 日志如果事务回滚InnoDB 通过 undo log 把数据恢复到修改前的状态持久性Durability由 redo log Buffer Pool 配合保证。事务提交时先把 redo log 刷到磁盘即使数据页还没来得及落盘宕机后也能通过 redo log 重放恢复隔离性Isolation由锁机制 MVCC 保证。写操作之间通过锁隔离读写之间通过 MVCC 实现快照读一致性Consistency这是最终结果由前面三个特性共同保证同时外键约束、唯一约束等也参与。面试官经常会顺势追问一个问题“MySQL 在事务提交时是直接把数据页刷到磁盘吗”答案是不是。InnoDB 采用 WALWrite-Ahead Logging机制事务提交时主要保证 redo log 落盘数据页只是先缓存在 Buffer Pool 里由后台线程择机刷新。这就是为什么 MySQL 崩溃恢复能够不丢数据靠的就是 redo log 的重放。3.2 四种隔离级别与它们各自的软肋SQL 标准定义了四种隔离级别MySQLInnoDB的默认隔离级别是可重复读REPEATABLE READ这一点和 Oracle 默认的读已提交READ COMMITTED不同经常成为对比类题目的切入点。隔离级别脏读不可重复读幻读READ UNCOMMITTED可能可能可能READ COMMITTED不可能可能可能REPEATABLE READ不可能不可能可能InnoDB 已解决SERIALIZABLE不可能不可能不可能重点说下幻读。在 REPEATABLE READ 下普通SELECT是快照读MVCC 生成的 ReadView 已经保证了一个事务内多次读取结果一致。但如果是SELECT ... FOR UPDATE这种当前读或者UPDATE、DELETE、INSERT操作就会走最新数据这时可能会插入新的满足条件的行产生“幻读”。InnoDB 用**间隙锁Gap Lock 临键锁Next-Key Lock**来解决这个问题在 RR 隔离级别下当前读会对扫描范围内的区间加锁不仅锁定匹配的记录还锁定记录之间的间隙阻止其他事务在这个间隙里插入新数据从而限制幻读的产生。顺带提一个容易搞混的考点“MVCC 能解决幻读吗”正确答案是MVCC 解决的是快照读场景下的幻读而当前读场景下的幻读是靠 Next-Key Lock 来解决的。两者配合才让 InnoDB 在 RR 级别下几乎不出现幻读。这个“几乎”也是面试官爱抠的点因为如果事务 A 先快照读、再当前读还是有可能读到新插入的数据的严格来说不能叫 100% 解决了幻读。3.3 MVCC 的工作原理三个隐藏字段 版本链 ReadViewMVCC 全称是 Multi-Version Concurrency Control中文叫多版本并发控制。它的核心思想是同一行数据在数据库里可能存在多个版本每个版本都对应一个事务读操作根据可见性规则选择该读哪个版本。展开来讲InnoDB 的聚簇索引行记录里隐藏着三个关键字段DB_TRX_ID最后修改这个行版本的事务 idDB_ROLL_PTR回滚指针指向 undo log 中的上一个版本所有版本串起来就是一条版本链DB_ROW_ID隐藏主键当表没有显式主键时 InnoDB 用它生成聚簇索引。当开启一个事务执行普通SELECT时InnoDB 会生成一个 ReadView里面核心记录了m_ids当前活跃未提交的事务 id 列表min_trx_id活跃事务中最小的事务 idmax_trx_id下一个将要分配的事务 idcreator_trx_id创建这个 ReadView 的事务自己的 id。然后沿着版本链从最新版本往前找按规则判断每个版本的DB_TRX_ID是否可见。规则可以简化成一句话如果该版本的生成事务在 ReadView 的活跃事务列表里或者事务 id 比 min_trx_id 还小但已提交就对当前事务可见否则就继续往前找更老的版本。这里最经典的面试追问是“RC 和 RR 隔离级别下ReadView 的生成时机有什么区别”答案是RC 级别是每次 SELECT 都生成一个新的 ReadView所以同一事务两次 SELECT 之间其他事务提交了第二次 SELECT 就能看到新数据于是产生了不可重复读RR 级别是事务第一次 SELECT 时生成 ReadView之后整个事务都复用这一个所以无论后续其他事务怎么提交看到的快照都是一致的这就是 RR 能解决不可重复读的根本原因。理解了这一层你对“数据库篇”里事务相关的面试题基本就不会再怕了因为你不是在背结论而是真的知道它内部是怎么转的。4. InnoDB 的锁机制从行锁到死锁排查4.1 共享锁、排他锁、意向锁的关系锁在数据库里主要用来解决并发写冲突。InnoDB 支持两种行级锁共享锁S Lock读锁多个事务可以同时持有共享锁排他锁X Lock写锁同一行只能有一个事务持有且与其他锁都互斥。另外 InnoDB 还有意向锁Intention Lock它是个表级锁分意向共享锁IS和意向排他锁IX。意向锁本身不直接锁数据它的作用是当一个事务想对表加表级锁时可以快速判断表里是否已经有不兼容的行锁避免逐行检查。面试里常问的一个点是SELECT ... FOR UPDATE加的是排他锁SELECT ... LOCK IN SHARE MODE加的是共享锁普通SELECT不加锁走 MVCC 快照读。这个分类要记清楚尤其是有同事写代码时习惯给查询语句加FOR UPDATE如果事务范围过长很容易造成锁等待和死锁。4.2 记录锁、间隙锁、临键锁以及它们的作用范围在 RR 隔离级别下InnoDB 的锁不只是锁住一条记录而是引入了更加精细的锁类型记录锁Record Lock锁住索引记录本身间隙锁Gap Lock锁住记录之间的间隙防止其他事务在间隙中插入数据但它不锁记录本身临键锁Next-Key Lock记录锁 间隙锁的组合锁住一个左开右闭的区间是 RR 级别下默认的加锁方式。我这里给一个最容易考到的场景题事务 A 执行SELECT * FROM user WHERE age 20 FOR UPDATE假设 age 上有普通索引且表中 age 有 18、20、20、22 这几个值。此时 InnoDB 会怎么加锁答案是会对 age 20 的两条记录加记录锁同时会对(18, 20]、(20, 22]这两个区间加临键锁还会对(22, ∞)这个区间加临键锁。也就是说它锁住的范围比你直观想象的更大这就是为什么高并发下容易发生锁等待的原因。这个例子实际上在考一个点RR 级别为了防幻读加锁范围会扩大化。如果业务对幻读不敏感可以考虑把隔离级别改成 RC这样 InnoDB 会退化成只加记录锁并发度能显著提升。很多互联网大厂的核心交易链路用的是 RC 而不是 RR不完全是为了兼容 Oracle 语法更多是为了减少锁冲突。4.3 死锁的产生与排查授人以渔的完整链路死锁在实际生产环境中并不罕见尤其是多个事务以不同顺序更新相同记录时。经典场景会话 AUPDATE account SET balance balance - 100 WHERE id 1;先锁 id1 的行再执行UPDATE account SET balance balance 100 WHERE id 2;会话 B正好反着来先更新 id2再更新 id1。两个事务互相等待对方持有的锁就会形成死循环等待InnoDB 检测到死锁后会回滚其中一个事务通常选择 undo 量较小的事务释放锁。排查死锁的步骤我可以分享一套线上实战流程查看最近一次死锁日志SHOW ENGINE INNODB STATUS\G;重点看LATEST DETECTED DEADLOCK部分里面记录了死锁发生时的具体 SQL、持锁事务 id、等待的锁资源类型确认涉及的表和 SQL通过日志里的事务 id 去information_schema.innodb_trx表查看事务状态、执行的 SQL、等待时间进一步缩小范围分析死锁原因通常是两条 SQL 更新顺序不一致或者涉及范围锁导致锁冲突。修复方案一般是在业务层统一所有事务对多个资源的加锁顺序让它们都按 id 从小到大的顺序更新如果必须保证高并发考虑用乐观锁版本号或 CAS替代悲观锁减少锁等待窗口。这里有个面试加分项能说出死锁和锁等待的区别。锁等待是“我等你的锁释放但不久后你释放了我继续跑”死锁是“你等我释放锁我也等你释放锁谁也等不到”数据库每过一段时间会检测并杀掉其中一个事务。5. SQL 优化实战从执行计划到深分页5.1 一条慢 SQL 的完整优化实录很多人在简历上写“熟悉 SQL 优化”但在面试官眼里背几条优化原则并不算熟悉。至少得能拿一条具体的慢 SQL完整说出从发现问题到优化的过程。我拿一个真实场景来演示一下。线上有一张订单流水表order_flow数据量约 3000 万其中有条高频统计 SQLSELECT order_id, user_id, amount, status FROM order_flow WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY user_id LIMIT 20;这条 SQL 慢到平均 4 秒以上。EXPLAIN显示 type 为 ALL走了全表扫描Extra 里还有 Using filesort。优化思路分三步建立联合索引(create_time, user_id, order_id, amount)这样 WHERE 条件能走 create_time 的范围索引而且 select 的字段全部落在索引里覆盖索引 避免回表解决 filesort。其实排序字段 user_id 已经在索引里但由于 create_time 的范围查询导致索引扫描时 user_id 不是全局有序的排序还是要做。如果查询条件固定为某个月可以把 limit 条件改成基于分页参数, 减少排序数据量。更彻底一点如果业务允许改成按(user_id, create_time)的联合索引走user_id ? ORDER BY create_time的方式就能避免 filesort最终线上采用的方案是保留联合索引(create_time, user_id, order_id, amount)把查询改成先按天分页避免一次扫描一个月的数据再把EXPLAIN的 type 从 ALL 优化到 rangeExtra 从 Using filesort 变成 Using index。这个小例子我希望表达的是SQL 优化不是背几条原则就完事而是要能读懂执行计划每一步的代价然后针对性地调整索引和 SQL 写法。5.2 深分页为什么慢以及三套替代方案LIMIT 1000000, 20这种深分页是开发中绕不开的痛点。很多人一开始会觉得奇怪MySQL 明明只返回 20 条记录为什么越往后翻越慢原因在于LIMIT offset, size的执行过程是先扫描并丢弃前 offset 行再取 size 行返回。也就是说一旦 offset 很大引擎还是要把前面的一百万行都扫一遍回表也在所难免代价自然高。三套常用解决方案我按适用场景分一下方案一基于排序字段优化延迟关联 覆盖索引。SELECT a.* FROM order_flow a INNER JOIN ( SELECT id FROM order_flow ORDER BY create_time LIMIT 1000000, 20 ) t ON a.id t.id;子查询里只查主键 id走的是覆盖索引扫描速度极快然后再回到原表查完整的行。这种方案改造简单适合大多数业务。方案二记录上一页最后一条记录的游标。SELECT * FROM order_flow WHERE id 上一页最后一条记录的id ORDER BY id LIMIT 20;这是我最推荐的深分页实践方式尤其适合移动端 Feed 流。它不依赖 offset也不会有跳页需求因为用户一般是顺序往下滑。缺点是如果业务必须支持跳页就不适用了。方案三如果分页字段是自增主键但中间有删除导致空洞可以直接用时间或序列号字段代替 id 做游标。思路同方案二好处是即使有数据删除也不会影响游标连续性。无论哪种方案核心思想都是减少无谓回表、减少扫描行数。5.3 优化器选错索引怎么手工干预有一类问题很隐蔽明明 A 索引更好优化器却选择了 B 索引。常见原因有两个统计信息过期优化器估算的扫描行数与实际偏离严重涉及范围查询时优化器高估了范围索引的代价。解决方案按优先级排先执行ANALYZE TABLE更新表的统计信息很多“优化器犯傻”的问题刷新统计信息后就自动解决了如果还不行考虑修改 SQL 写法比如用FORCE INDEX强制指定索引SELECT * FROM order_flow FORCE INDEX (idx_create_time) WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31;也可以使用IGNORE INDEX让优化器排除某个索引从而选择更合适的索引路径。不过我要多说一句FORCE INDEX是最后的干预手段不是常规手段。索引选择问题应该优先从 SQL 写法、索引设计上解决硬编码指定索引会让后续索引调整变得困难而且一旦数据分布变化强制的索引可能反而不是最优的。5.4 批量操作与深分页结合时注意锁范围如果你在优化一个定时任务比如每天批量清洗流水表SQL 大概是UPDATE order_flow SET process_flag 1 WHERE process_flag 0 LIMIT 1000;这类批量更新如果不分页一次性更新几十万行行锁会积累到非常夸张的程度很容易拖垮主库。我的经验是分批更新 每批之间 sleep 一会比如 50ms或者直接用主键范围分批保证单批锁范围可控。这样既能提高吞吐量也能显著降低锁冲突和死锁概率。6. 两阶段提交与崩溃恢复redo log 和 binlog 配合的底层逻辑6.1 redo log 和 binlog 的区别一张表讲清很多同学在“日志”这块容易混淆 redo log 和 binlog面试被问到“这两个日志有什么区别”时说不全。先用表格把最核心的几个维度理清对比维度redo logbinlog所在层级InnoDB 存储引擎层MySQL Server 层记录内容物理日志记录“哪个数据页的哪个偏移量改成了什么”逻辑日志记录 SQL 语句或行变更前后镜像记录方式循环写文件大小固定追加写文件滚动增加作用崩溃恢复保证持久性主从复制、数据恢复刷盘时机事务提交时刷盘组提交优化事务提交时刷盘sync_binlog 配置一句话总结redo log 用来保证 MySQL 自己崩溃后能恢复数据binlog 用来给主从复制和误操作恢复提供基础。两者缺一不可。6.2 prepare、commit 两个阶段到底在防什么两阶段提交Two-Phase Commit是 InnoDB 事务提交时的核心机制面试官非常喜欢让候选人画一下这个流程。文字版整理如下prepare 阶段事务执行过程中产生 redo log事务提交时先写 redo log并标记为 prepare 状态写 binlog 阶段事务将变更写入 binlogbinlog 落盘commit 阶段把 redo log 标记为 commit 状态事务正式提交。为什么要这么麻烦核心原因是 redo log 和 binlog 是两份独立的日志如果只写一份崩溃恢复时可能不一致。举个例子如果先写 binlog 再写 redo logbinlog 写完后 MySQL 崩溃此时主库持久化只差 redo log但从库已经通过 binlog 拿到了这个事务重启主库后这个事务丢了主从数据就会不一致。引入两阶段提交后崩溃恢复的规则是redo log 处于 prepare 状态且 binlog 完整事务可以提交恢复时补 commitredo log 处于 prepare 状态但 binlog 不完整事务回滚redo log 处于 commit 状态事务直接生效。这套机制保证了只要 binlog 里写入了事务主库就一定能通过 redo log 恢复出同一个事务只要 binlog 没写入完整事务就原子性地回滚。主从复制的一致性就是靠这个细节兜底的。6.3 刷盘参数怎么选兼顾性能与安全生产环境中DBA 通常会关注两个核心刷盘参数innodb_flush_log_at_trx_commit控制 redo log 的刷盘策略值为 0事务提交时只把日志留在内存每秒刷一次盘性能最高但 MySQL 宕机会丢最近 1 秒内的事务值为 1事务提交时立即把 redo log 刷入磁盘最安全但性能最低值为 2事务提交时写入操作系统缓存每秒刷盘MySQL 宕机不丢数据操作系统宕机可能丢最近 1 秒数据。sync_binlog控制 binlog 刷盘策略值为 1每次事务提交都刷盘最安全但性能低值为 0由操作系统决定刷盘时机性能好但可能丢日志值为 N每 N 次事务提交刷一次盘。在生产环境追求数据安全的核心链路官方推荐innodb_flush_log_at_trx_commit 1和sync_binlog 1但这会明显拉低吞吐量。很多高并发业务会折中设置成2和1或者2和100。这里我给个个人建议核心交易数据别省这个性能开销非核心但需要事务的数据按场景去权衡。7. 主从复制与读写分离延迟问题的来龙去脉7.1 一主一从的复制链路三个线程讲明白主从复制是 MySQL 高可用和读写分离的基石。很多面试者知道有“主从复制”这回事但说不清具体链路。我习惯这么讲主从复制依赖 binlog 和三个线程主库的 dump 线程主库收到从库的复制请求后dump 线程负责读取 binlog 并发送给从库从库的 I/O 线程接收主库发来的 binlog写入从库本地的中继日志relay log从库的 SQL 线程读取 relay log 并在从库上重放应用这些日志内容。整个链路可以概括为主库写 binlog - 从库 I/O 线程拉取 - 写入 relay log - 从库 SQL 线程重放。一句话记忆“主库记日志从库拉日志SQL 线程还日志。”这里有一个容易被面试官追问的点从库的 SQL 线程和 I/O 线程是单线程的吗早期 MySQL 是单线程主库并发写入高时从库很容易延迟。从 MySQL 5.7 开始支持基于库级别的并行复制MTSMulti-Threaded Slave8.0 进一步支持基于事务提交顺序的 Writeset 并行复制。所谓并行复制就是把 commit 阶段不冲突的事务分配给多个 SQL 线程并行执行大幅降低从库延迟。7.2 主从延迟的三大核心原因真实业务里主从延迟Seconds_Behind_Master是 DBA 和开发共同的头疼问题。常见原因我可以总结成三类大事务比如一次 UPDATE 影响几十万行binlog 体积巨大从库要慢慢重放DDL 操作在生产环境直接对几百 GB 的大表执行ALTER TABLE即使主库执行很快从库 SQL 线程回放也需要很长时间单线程瓶颈或并发复制配置不当从库配置较低或者并行复制参数没调好也会导致重放速度跟不上主库的写入速度。排查链路一般是SHOW SLAVE STATUS\G;重点看Seconds_Behind_Master主从延迟秒数、Relay_Log_Spacerelay log 积压量、Slave_IO_Running和Slave_SQL_Running状态。7.3 读写分离后数据延迟怎么兜底读写分离架构下最怕出现“写完主库立刻读从库结果读到旧数据”。我在实际项目里给过几种兜底策略对实时性要求高的读请求强制走主库通过注解或路由规则标记刚写完主库后的短时间内同一用户的请求路由到主库读取时间窗口通常设置几百毫秒从库延迟监控超过阈值后自动把所有读流量切换回主库保证业务可用性优先。面试时能把这些策略讲出来说明你在真实架构上思考过而不是只背了“读写分离”四个字。8. 分库分表什么时候做怎么做8.1 分库分表的触发条件与前置方案很多面试者一上来就说“数据量大了就分库分表”但什么时候算“大”并没有统一标准。以我个人经验来看通常从这几个维度评估单表数据量超过千万级到亿级且查询性能明显退化数据库连接数成为瓶颈比如一个库连接池已被占满写入吞吐达到单库上限磁盘 I/O、网络带宽紧张单库容量达到存储瓶颈。但在真的走到分库分表这一步之前有几件事值得先做优化 SQL 和索引这一步没做好的话分库分表只是拿着放大镜看清自己的烂代码引入缓存把热点读流量挡在数据库前面做分区表或者归档历史数据降低单表活跃数据量冷热分离把不再频繁访问的数据迁移到单独的归档库。只有这些手段都用尽了数据增长依然压不住才轮到分库分表。面试里如果能先讲清楚“分库分表是最后手段”这个观点会让面试官觉得你更有全局观。8.2 垂直拆分与水平拆分先拆方向再拆策略分库分表有两层含义垂直拆分按业务域拆库把订单、用户、商品拆到不同的库或者按字段访频拆分到不同表把大字段拆到扩展表目的是减少单库的数据量和访问压力。水平拆分把同一张表按照某个分片键拆到多个库和表中。这个环节最核心的是选分片键和分片策略。分片键选不好后面的路由、扩容、数据迁移都会很痛苦。以订单表为例如果业务查询基本都带 user_id那就用 user_id 做分片键如果后台管理要按商家查订单则要考虑订单号里嵌入用户维度或者额外建立一张映射表。常用分片策略有三种哈希取模user_id % 库数数据分布均匀但扩容要搬迁数据一致性哈希扩容时只需迁移部分数据适合节点频繁变化的场景范围分片按时间或 id 范围划分适合流水类数据但容易产生热点分片。其实没有哪一种策略绝对好关键是看业务查询模式。比如订单增量导入场景按时间范围分片就很好如果是一个多租户系统各个租户的查询量相对独立按租户 id 哈希就比按时间更稳。8.3 分库分表之后的几个经典难题分库分表不是结束而是新问题的开始。面试官最爱问的“分库分表后怎么办”系列通常就离不开这几件事第一跨库查询。例如在多个订单库中查某个用户的所有订单只能每个分片单独查然后业务层合并。通常做法是先定位到用户对应的分片减少跨片扫描。第二全局主键。分表的自增 id 会出现冲突通常需要统一生成 ID。常见方案有雪花算法Snowflake、Redis 原子自增、数据库号段模式。雪花算法在分布式环境里用得最多64 位 long符号位 1 位 时间戳 41 位 机器 id 10 位 序列号 12 位。面试时如果能把雪花算法每一段占多少位说清楚立刻加分。第三分布式事务。分库后一个业务操作可能涉及多个库传统本地事务失效。常见方案有基于消息队列的最终一致性、TCC 补偿事务、Seata AT 模式等。面试遇到这类题重点是讲清楚取舍强一致性要求高就 TCC能接受最终一致就 MQ 本地消息表。第四跨分片排序分页。ORDER BY create_time LIMIT 0, 20在分库后每个分片都要先查出各自的 top 20然后汇总后再排序取前 20。如果页数很深汇总的数据量会非常大所以深分页在分库场景下更要避免。9. 数据库面试的串联学习法与我的经验总结9.1 用一条 SQL 的执行过程串起所有知识点我在带新人时发现一个特别好的学习方法用一条 SQL 的执行过程把上面所有知识点串起来。SELECT * FROM user WHERE name 张三 AND age 20这条语句从客户端发出去到结果返回中间发生了什么MySQL Server 层先做连接管理、权限校验、查询缓存判断8.0 后移除、解析器做词法和语法分析生成语法树优化器选择执行计划优先判断 name 上的索引是否可用计算扫描行数决定是否回表引擎层执行如果是普通 SELECT走 MVCC 快照读生成 ReadView沿着版本链找可见版本如果是FOR UPDATE走当前读加 Next-Key Lock返回结果前如果有排序分组、limit在 Server 层完成如果是 UPDATE 语句则要写 undo log、redo log最终事务提交走两阶段提交binlog 同步给从库。你会发现索引、事务、MVCC、锁、日志、主从复制全都在这一个链条里面。面试时如果你能用一条 SQL 的执行链路来回答问题会显得特别完整。9.2 关于八股文的正确打开方式其实我不反对背八股文我反对的是只背不理解。数据库领域尤其如此因为很多“八股”本身就是对真实机制的抽象总结死记硬背很容易在追问面前露馅。我的建议是每背一个概念至少追问自己三个“为什么”。比如背“联合索引遵循最左前缀”就问自己“为什么最左列能走索引跳过最左列为什么不行”然后去翻一下 B 树的排序规则这个问题自然就通了。再比如背“InnoDB 默认 RR 隔离级别”就问自己“RR 是怎么解决不可重复读的”“RR 为什么没有被幻读完全绕开”然后去研究 ReadView 和 Next-Key Lock。这样一轮下来八股文就不再是零散的知识点而是能灵活调用的作战地图。9.3 给准备面试的人三条实操建议第一别只刷题要动手验证。自己本地装个 MySQL建一张百万行测试表把课上讲到的索引失效场景一个个跑一遍亲眼看到EXPLAIN结果的变化印象会比刷十道题都深。第二准备一份自己的“数据库实战案例”。无论面试官问什么最后都能往自己真实处理过的问题上靠。比如你优化过一条慢 SQL可以从慢查询日志、执行计划、索引设计、最终效果这条链路讲比背十个优化原则更有说服力。第三注意表达结构。面试回答问题时按“结论 - 原理 - 案例”的顺序组织语言。先给出明确答案再讲底层机制最后用实际案例佐证。数据库方向的面试题普遍偏深这样的结构能帮助你在有限时间内把信息密度最大化也不会被面试官的连环追问带乱节奏。数据库这块的知识就像盖楼的地基面试题只是地基上刷的漆。把索引、事务、锁、日志、分库分表这五根柱子立稳了不管面试官怎么问你都能接得住。