对应 2 周计划:D6 | 优先级: 必考 | 大厂二面重头戏,面试官最爱深挖为什么〇、设计哲学:MySQL 为什么这么设计?(先读这节)一句话主线:MySQL 的每个设计,都是为了在「磁盘慢」这个物理现实下,把查询做得又快又不乱——树要矮(少 IO)、读写不互锁(MVCC)、用空间换时间(索引)。图 0 · MySQL 三大设计动机三大设计动机 → 对应机制① 为什么 B 树磁盘 IO 一次 ~10ms内存 ns 级→ 树要矮:非叶只存索引→ 叶子链表:范围查询少一次 IO 快百倍② 为什么 MVCC读写互斥会阻塞加锁读 并发差→ 读不加锁看快照→ 写只改最新版本读写并发不打架③ 为什么建索引全表扫描 O(n)千万行灾难→ 索引 O(log n)→ 用空间换时间B树就是导航图各机制的设计思路(为什么):为什么用 B 树不用 Hash:Hash 等值 O(1) 但无法范围查询、不能排序、不支持最左前缀——数据库 80% 查询是范围/排序,所以淘汰 Hash 做主索引。为什么 B 树非叶子不存数据:非叶只存索引 → 一个 16KB 页能塞更多索引项 → 树更矮(3 层存百万行)→ 一次查询最多 3 次磁盘 IO。为什么 MVCC 而不是直接加锁读:若读也加 S 锁,写要 X 锁,读写互斥 → 并发暴跌。MVCC 让读走历史快照(undo log 版本链),写走最新,二者互不阻塞。为什么默认 RR 而非 RC:RR 下 ReadView 整个事务复用,天然防不可重复读和幻读,业务更安全;代价是间隙锁可能锁等待,但默认值得。为什么建议自增主键:聚簇索引按主键顺序插入 顺序写,避免页分裂;UUID 乱序插入频繁页分裂,性能差。一、索引Q1: 索引为什么用 B 树而不是 B 树/红黑树/Hash?答案要点:B 树 vs B 树:B 树数据都在叶子节点,非叶子只存索引(更矮更宽,一次 IO 可加载更多索引,减少磁盘 IO);叶子节点用链表串联(范围查询友好)B 树 vs 红黑树:红黑树高度约 log2(n),B 树高度 3-4 层(百万级数据);数据库数据量巨大,树越矮磁盘 IO 越少B 树 vs Hash:Hash 等值查询 O(1) 更快,但不支持范围查询、无法排序、无法最左前缀图 1 · InnoDB 聚簇索引 B 树(叶子存整行,链表串联)根:索引页索引页索引页PK1 整行(name,age...)PK5 整行(name,age...)PK9 整行(name,age...)PK15 整行(name,age...)叶子横向链表 → 范围查询(WHERE id5)顺着链表扫,不用回树深追问:为什么磁盘 IO 是瓶颈?→ 一次磁盘 IO 约 10ms,内存 ns 级;一个节点对应一个页(16KB),减少树高 减少磁盘访问一个 3 层的 B 树能存多少数据?→ 假设主键 8 字节 指针 6 字节,每个节点约 16KB/14B ≈ 1170 个索引;第二层 1170×1170 ≈ 137 万;第三层叶子存数据,百万级完全覆盖Q2: 聚簇索引 vs 二级索引 vs 覆盖索引?答案要点:索引说明聚簇索引InnoDB 主键即聚簇索引,叶子存整行数据;一张表只有一个二级索引非主键索引,叶子存主键值(回表查询)覆盖索引查询列全部在索引中,无需回表,Extra 显示 Using index联合索引多个字段,遵循最左前缀原则深追问:为什么建议用自增主键?→ 聚簇索引按主键顺序插入,B 树顺序写,避免页分裂;业务主键(如 UUID)乱序插入导致页分裂、性能下降回表是什么?→ 二级索引查到主键,再用主键查聚簇索引拿整行联合索引最左前缀:查询必须包含最左列,如 (a,b,c) 索引支持 a、a,b、a,b,c;不包含 a 则失效Q3: 索引失效场景(必背)?答案要点:违反最左前缀原则对索引列做运算/函数(where a12 或 where date(a)...)隐式类型转换(字符串列用数字比较,需加引号)索引列使用 LIKE 通配符开头(%abc)OR 连接的非索引列索引列参与比较时全表扫描更优(数据量少,优化器选择)NOT IN / 不一定失效(取决于优化器)联合索引中间有范围查询(如 a1 and b2,则 b 失效——范围右侧失效)深追问:如何确认是否走索引?→ EXPLAIN:type 列(const eq_ref ref range index ALL)、key 列、rows 列、Extra 列(Using index/Using filesort/Using temporary)二、事务Q4: 事务四大特性 ACID?图 2 · ACID 的保证机制原子性undo log 回滚一致性应用层 三性隔离性锁 MVCC持久性redo log(WAL)WAL(日志先行):先写 redo 顺序日志,提交即持久,崩溃后重放恢复特性含义保证机制原子性 Atomicity要么全成功要么全回滚undo log一致性 Consistency数据状态合法应用层 其他三性隔离性 Isolation事务间互不干扰锁 MVCC持久性 Durability提交后永久保存redo log深追问:undo log 和 redo log 区别?→ undo 记录修改前数据用于回滚;redo 记录修改后数据用于崩溃恢复;redo 是物理日志(页级别),undo 是逻辑日志(记录反向操作)为什么需要 redo log?→ 随机写磁盘慢,先写 redo(顺序写,日志先行 WAL),崩溃后重放恢复,保证持久性Q5: 事务隔离级别与问题?4 种隔离级别:级别脏读不可重复读幻读Read Uncommitted可能可能可能Read Committed(默认?Oracle 默认)解决可能可能Repeatable Read(MySQL 默认)解决解决基本解决(间隙锁)Serializable解决解决解决3 种并发问题:脏读:读到其他事务未提交的数据不可重复读:同一事务内两次读同一行,值不同(其他事务提交修改)幻读:同一事务内两次查询,行数不同(其他事务插入)深追问(必问):MySQL 默认 RR 为什么还能解决幻读?→MVCC 快照读(普通 SELECT 走快照,天然防幻读)间隙锁/临键锁(当前读 UPDATE/DELETE 加锁防止插入)快照读 vs 当前读?→ 快照读:普通 select,读 undo log 版本链,不加锁;当前读:select for update/lock in share mode/update/delete,读最新 加锁可重复读是怎么用 MVCC 实现的?→ 事务第一次读时生成 ReadView(活跃事务列表),之后读的都是基于这个 ReadView 判断可见性,不受其他事务提交影响Q6: MVCC 原理(必考)?答案要点:每条记录有两个隐藏列:trx_id(最后修改事务 ID)、roll_pointer(指向 undo log 版本链)版本链:每次修改生成新版本,通过 roll_pointer 串成链ReadView(读视图)包含:活跃事务 ID 列表、最小 ID、最大 ID可见性判断:当前事务读版本链,若版本 trx_id 在 ReadView 活跃列表内 → 不可见,沿链找更早版本;否则可见图 3 · MVCC:版本链 ReadView 可见性判断同一行数据的版本链(新→旧)v3 当前trx30 age25v2trx20 age24v1trx10 age23⟸ReadView(事务 T 的快照)[min_trx10, max_trx30, 活跃:20]可见性:trx_id 在活跃列表内→不可见,沿链找更早判断结果v3(trx30)不在活跃→可见?否(max)v2(trx20)活跃→不可见;v1(trx10)可见 ✓RR:整个事务复用同一 ReadView → 每次读都看到 v1,可重复读RC:每次 select 新建 ReadView → 能看到别人已提交的新值(不可重复读)深追问:RC 和 RR 的 ReadView 区别?→ RC:每次 select 都生成新 ReadView(所以可读到已提交的新值,不可重复读);RR:第一次 select 生成后复用(整个事务用同一视图,可重复读)为什么 RR 下普通 select 无幻读?→ 整个事务 ReadView 固定,新增的行不在视图内,看不到三、锁Q7: MySQL 锁分类?答案要点:粒度:表锁(MyISAM/DDL)、行锁(InnoDB)、页锁模式:共享锁 S(读锁,可共存)、排他锁 X(写锁,互斥)行锁算法(InnoDB):记录锁 Record Lock:锁单行间隙锁 Gap Lock:锁区间,防插入(防幻读),RR 级别生效临键锁 Next-Key Lock:记录锁 间隙锁,默认加锁方式深追问(必问):死锁怎么产生?→ 两个事务互相持有对方要的锁,循环等待死锁排查?→show engine innodb status查看 LATEST DETECTED DEADLOCK;开启 innodb_print_all_deadlocks 记日志死锁解决?→ 按相同顺序访问表/行、缩短事务、减小锁范围、innodb_lock_wait_timeout乐观锁 vs 悲观锁?→ 乐观:版本号/CAS(update ... where version?);悲观:select for update;读多写少用乐观四、SQL 优化Q8: 慢 SQL 优化流程?答案要点:开启慢查询日志:long_query_time1s,show variables like slow_query_logEXPLAIN 分析执行计划:关注 type、key、rows、Extra优化手段:建立合适索引(覆盖索引避免回表)避免select *(减少回表/网络传输)分页优化:limit 大偏移用子查询先查 id(select * from t where id (select id from t order by id limit 1000000,1) limit 20)避免函数/运算导致索引失效大表避免全表扫描:查询走索引、减少联表、小表驱动大表数据量大(千万):分库分表、归档、读写分离深追问:EXPLAIN 的 type 顺序?→ const eq_ref ref range index ALLUsing filesort 怎么优化?→ 排序字段建索引(索引有序,免排序)深分页为什么慢?→ 大偏移需要扫描并丢弃大量行;用游标/子查询/延迟关联五、主从复制与读写分离Q9: 主从复制原理?答案要点(三步):主库 binlog(二进制日志)记录变更从库 IO 线程拉取 binlog 写入 relay log(中继日志)从库 SQL 线程重放 relay log 完成同步深追问:同步延迟怎么解决?→ 半同步复制(主库等至少一个从库 ACK)、并行复制(多线程)、读写分离时强制走主库(关键数据)、缓存兜底复制模式:异步(默认,快但可能丢)、半同步(折中)、全同步(慢,基本不用)binlog 三种格式?→ statement(语句)、row(行,推荐,准确)、mixed(混合)六、分库分表Q10: 什么时候分库分表?怎么分?答案要点:触发条件:单表数据量千万级/亿级、单库连接数瓶颈、写并发过高垂直分库:按业务域拆(用户库/订单库)——微服务化天然如此垂直分表:按字段拆(大字段/热字段分离)水平分表:按主键/业务键取模或范围拆分(最常用)分片键选择:高频查询条件、分布均匀(避免热点)、业务生命周期(如按用户 ID)深追问(必问):分库分表后的问题?跨库 join:冗余字段 / 应用层组装 / 大宽表分布式事务:见分布式篇(TCC/Seata/最终一致性)全局唯一 ID:雪花算法/号段模式(见分布式篇)分布式分页:各分片查 N 条再合并(可能不准)扩容:一致性哈希减少迁移;提前规划分片数(2 的幂)中间件:ShardingSphere、MyCat;迁移工具:ShardingSphere 或自研双写分表后查询不走分片键?→ 全分片广播查询(慢),所以分片键设计很关键;或用 ES 等搜索引擎做检索七、漫画 · 回表历险记(理解为什么覆盖索引)回表历险记:一次查询的两次跑腿① 出发SELECT age FROM userWHERE nameTom查② 走二级索引name 索引树Tom→PK100✓③ 傻眼age 不在索引里!?得再跑一趟④ 回表聚簇索引树PK100 整行↺拿 age 返回⑤ 覆盖索引捷径联合索引(name,age)✓一步到位!⑥ 总结二级索引只存主键→ 必须回表覆盖索引免回表 IO 减半,更快八、考前速记(10 条)B 树:数据在叶子 叶子链表,树矮减少磁盘 IO,支持范围查询聚簇索引存整行,二级索引存主键(回表);覆盖索引免回表索引失效 8 场景:最左前缀/函数运算/类型转换/LIKE 前缀/OR/范围右侧ACID:原子性 undo、持久性 redo(WAL)、隔离性锁MVCCMySQL 默认 RR;MVCC 快照读防幻读,当前读用间隙锁MVCC:trx_id roll_pointer 版本链 ReadView;RR 复用 ReadView行锁三兄弟:记录锁/间隙锁/临键锁慢 SQL:慢日志 → EXPLAIN → 索引/避免select*/深分页优化主从复制:binlog → relay log → 重放;延迟用半同步并行复制分库分表:垂直按域、水平按分片键;解决跨库 join/分布式事务/全局 ID九、易错点提醒MySQL 默认隔离级别是RR,Oracle 默认是 RC——别记混「幻读」是行数变化,「不可重复读」是行内容变化间隙锁只在 RR 生效;RC 下幻读存在二级索引在 InnoDB 中一定回表(除非覆盖索引),MyISAM 的索引都存指针binlog(主从/恢复)是 server 层,redo log(崩溃恢复)是 InnoDB 层,别搞混十、深挖 · 面试官连环追问 源码级原理源码级 · InnoDB:聚簇索引的一个数据页(16KB)物理结构:页头PAGE_HEADER(含PAGE_N_DIR_SLOTS槽数、PAGE_HEAP_TOP空闲位、PAGE_N_RECS记录数)→PAGE_DIRECTORY(页尾的槽位数组,每个槽指向一条记录,用于二分查找)→ 用户记录(F1 头含next_record指针串成单向链表)。所以页内查 槽位数组二分定位 记录链表顺序扫,不是全扫。MVCC 版本链一行数据的多个版本如何被 ReadView 选中最新行trx_id20旧版本1trx_id15旧版本2trx_id10每条记录靠 DB_ROLL_PTR 指向上一版本 → 形成 undo 版本链ReadView 四字段m_ids(活跃事务)、min(最小id)、max(下次分配id)、creator(本事务id)可见判定trx_id min → 已提交可见在 m_ids → 活跃不可见creator → 自己可见≥max → 未来不可见B 树非叶子节点存(key, child_ptr),页默认 16KB,内部以PAGE_DIRECTORY槽位做二分查找;叶子通过FIL_PAGE_NEXT指针串成双向链表。MVCC 的 undo log 分 insert_undo / update_undo,版本链通过DB_ROLL_PTR串起来;ReadView 在事务第一条 SELECT 生成(RR)或每条 SELECT(RC)。redo log 是物理日志(页号偏移改动),先写 WAL 再改页,innodb_flush_log_at_trx_commit1保证提交必落盘。Buffer Pool 冷热分代:LRU 不是单链表,而是按innodb_old_blocks_pct(默认 37%)切成 young/old 两段。新读入的页先放 old 段头部,停留超过innodb_old_blocks_time(默认 1s)才晋升 young——这专门防全表扫描把热点数据冲掉的缓冲池污染。binlog 三种格式:STATEMENT(记 SQL,省空间但函数/UUID 主从不一致)、ROW(记每行前后镜像,安全但量大,默认)、MIXED(由 MySQL 判断选哪种)。主从复制靠 binlog, crash-safe 靠 redobinlog 的 2PC。组提交(group commit):redo 的fsync把同一时刻多个事务的刷盘合并成一次,binlog 也有自己的组提交,大幅降低磁盘 fsync 次数,是高并发写入吞吐的关键。连环追问:为什么 RR 还有幻读?→ 普通 SELECT 快照读无幻读;但当前读(UPDATE/SELECT FOR UPDATE)靠 Next-Key Lock(记录锁间隙锁)防插入。Next-Key Lock 加锁范围示例:表有索引值 10/20/30,执行SELECT * FROM t WHERE id 10 AND id 20 FOR UPDATE,锁的不是单点而是间隙 (10,20) 加上 20 的记录和间隙 (20,30),即(10, 30)这个开区间都被锁住,杜绝别的事务插入 15/25 造成幻读。等值查询且命中唯一索引则退化为纯记录锁(不锁间隙)。自增主键回滚后不回收(MySQL 自增不回退),因此可能出现空洞。COUNT(*)在 InnoDB 需扫索引,MyISAM 有计数缓存;大表 COUNT 慢,可用估算information_schema或专门计数表。为什么SELECT COUNT(*) FROM 大表慢且不准:InnoDB 没有行数缓存,要扫最小的那棵索引(B 树叶子数);RR 下还受 MVCC 影响,不同事务看到的行数都可能是当时的快照值,所以即使扫完也只是该事务视角。常见陷阱:隐式转换使索引失效(字符串列用数字比较);WHERE a12对 a 做运算导致索引失效。十一、大厂真题话术(照着背)真题 1(阿里/字节):索引为什么用 B 树,不用 Hash / B 树?第一句:B 树叶子链表有序且全连起来,范围查询和排序极快;Hash 只能等值且不支持范围;B 树叶子不连、非叶也存数据,扫库更慢。展开:InnoDB 数据就在主键索引的叶子(聚簇),二级索引叶子存主键,回表就是拿主键再去聚簇找。带节奏:所以索引设计要尽量覆盖查询(覆盖索引),避免回表。真题 2(腾讯):事务隔离级别和 MVCC 怎么实现的?第一句:RR 靠 MVCC 快照读,读不加锁,靠 undo log 版本链 ReadView 看该看哪个版本。展开:每行有隐藏事务 ID 和回滚指针,ReadView 决定可见性;RC 每次读新建 ReadView(所以会不可重复读),RR 复用首次的。带节奏:但 RR 的可重复读对当前读(加锁读)仍可能幻读,靠 Next-Key Lock 间隙锁兜。真题 3(美团):慢 SQL 你怎么调?索引为什么失效?第一句:先EXPLAIN看 type/key/rows,定位是全表扫还是没走索引。展开:失效常见原因:对列做函数/运算(WHERE a12)、隐式转换(字符串列比数字)、前导模糊%x、最左前缀没满足、OR 混入非索引列。带节奏:我们建索引遵循最左前缀,并把高区分度列放前面,慢查询平台每天巡检。真题 4(字节):redo / undo / binlog 各干啥?为什么写 redo 不先写 binlog?第一句:redo 保崩溃恢复(物理日志,WAL 先写),undo 保回滚和 MVCC(逻辑日志),binlog 是 Server 层归档/主从。展开:两阶段提交保证 redo 和 binlog 一致:prepare 写 redo → 写 binlog → commit 写 redo;中途崩靠这个顺序恢复。带节奏:所以 redo 是 InnoDB 的命,binlog 是 MySQL 的账,两者靠 2PC 对齐。十一、项目实战复盘(讲得出才加分)复盘 1:订单列表慢查询拖垮接口(2s → 80ms)背景(S):运营后台订单列表页,数据量 800 万,随着时间推移接口 P95 从 200ms 涨到 2s,DB CPU 常驻 90%。问题/任务(T):EXPLAIN发现核心查询WHERE user_id? AND status? ORDER BY created_at DESC LIMIT 100000,20走全表扫描(typeALL),rows800 万。行动(A):建联合索引(user_id, status, created_at),满足最左前缀且 created_at 在索引内 → 覆盖排序,免 filesort;深度分页改造:LIMIT 100000,20改成「游标分页」WHERE created_at ? AND user_id? ORDER BY created_at DESC LIMIT 20,用上一页最小时间当游标,避免扫 10 万行再丢;冷热分离:3 个月前订单迁历史表,主表只留近期。结果(R):type 从 ALL → range,P95 降到 80ms,DB CPU 回到 35%。追问点:为什么不直接LIMIT 100000,20加索引?→ 索引能加速定位 user_id,但 offset 100000 仍要跳过 10 万行取后续;游标分页把跳过变成范围定位,复杂度从 O(offset) 降到 O(limit)。复盘 2:大表加字段不敢动(gh-ost 在线变更)背景:用户表 5000 万行,要加一列last_login_ip。直接ALTER TABLE在 5.7 会锁表数小时,业务不可接受。行动:用gh-ost(基于 binlog 的在线变更工具)做无锁变更——它伪装成从库消费 binlog,把改动同步到影子表,最后原子切表;全程不阻塞读写。结果:变更在业务高峰外 40 分钟完成,零停机。追问点:gh-ost 和 pt-online-schema-change 区别?→ gh-ost 不依赖触发器(靠 binlog 回放),对主库压力更小、可控暂停;pt-osc 用触发器,简单但主库有写入放大。复盘 3:读写分离后读到「旧数据」(主从延迟脏读)背景:订单支付成功后跳转详情页,偶发显示未支付(用户投诉)。根因:应用读写分离,支付写主库,详情读从库;主从延迟 200ms~1s,刚写的记录从库还没同步。行动:支付完成后的「关键读」(详情/状态确认)强制走主库(HintManager.setMasterRouteOnly()或注解Master);非关键读(历史列表)仍走从库分摊;监控主从延迟Seconds_Behind_Master,超阈值告警。结果:关键路径脏读归零,从库仍承担 70% 读流量。追问点:能不能从库强一致?→ 半同步复制能缩小延迟但不能消除;金融级强一致干脆走主库或加分布式锁串行化,用性能换准确。十五、面试官追问模拟录音体场景一为什么明明建了索引还是慢面试官你说这 SQL 慢建了索引怎么还全表扫你看 EXPLAINtype 是 ALL。原因是我查询条件是 WHERE DATE(create_time)...对列用了函数索引失效了。得改成范围查询 create_time BETWEEN ...。面试官还有别的情况会让索引失效吗你隐式类型转换字符串列传数字、左模糊 %xx、OR 连接非索引列、联合索引不满足最左前缀、优化器觉得回表贵反而选全表——最后这种要看 optimizer_trace。面试官那回表是什么为什么贵你二级索引叶子存主键查到主键再回主键索引拿整行叫回表。覆盖索引查询列都在索引里能免回表所以高频查询我们尽量建覆盖索引。复盘索引失效能列 5 种以上并讲清回表/覆盖索引就是 DBA 级别的回答。场景二MVCC 能解决幻读吗面试官MVCC 解决了幻读吗你快照读普通 SELECT靠 ReadView 看到一致性快照不会看到别的事务新插入的行所以快照读下没有幻读。但当前读SELECT ... FOR UPDATE / UPDATE会读最新已提交版本别的事务插入后当前读会看到产生幻读。面试官那 MySQL 怎么挡住当前读的幻读你靠 Next-Key Lock——间隙锁 记录锁锁住索引记录和它前面的间隙插入被挡住。所以 RR 隔离级别下当前读也不会幻读。面试官那为什么还有人用 RC你RC 下 Next-Key 退化成只锁记录并发高、锁冲突少很多互联网公司用 RC 业务层防幻读来换吞吐。复盘能区分快照读无幻读 / 当前读靠 Next-Key并解释 RC 取舍非常加分。十二、易错点提醒(高频踩坑清单)⚠️隐式转换使索引失效:字符串列用数字比较(或反之),MySQL 会做类型转换,索引用不上,全表扫。⚠️对列做函数/运算(如WHERE DATE(create_at)...)导致索引失效;改写为范围条件。⚠️COUNT(*)在 InnoDB 要扫索引,大表慢;用估算值或专门计数表。⚠️自增主键回滚不回收,可能出现空洞(非连续),别当业务连续号用。⚠️%前导模糊走不了索引,后缀模糊(模糊在后面)可以;全文检索用全文索引或 ES。