每日问答mysql篇1.InnoDB 为什么选用 B 树做索引而不用 B 树、哈希索引InnoDB 选用 B 树①非叶子节点只存索引键一页存放更多索引树矮磁盘 IO 少②叶子节点双向链表非常适合范围查询、排序③B 树叶子没有链表范围查询性能差hash 索引等值查询快但不支持范围、排序还有哈希冲突。2.什么是聚簇索引、二级索引什么叫回表什么是覆盖索引覆盖索引如何避免回表聚簇索引就是主键索引叶子节点存放整行数据二级索引叶子存索引列 主键。通过二级索引拿到主键再去聚簇索引拿完整行就是回表。覆盖索引查询字段全部在二级索引中无需回表。3.请讲联合索引最左前缀原则并且说出几种索引失效的场景联合索引 (a,b,c)索引排序顺序 a→b→c。查询必须匹配最左边的起始列才能用上索引。例where a? 能用where a? and b? 能用where b? 不能直接走联合索引跳过最左 a索引失效。失效场景汇总①索引列做函数、运算②like 以 % 开头③隐式类型转换④not in、!⑤or 一侧无索引⑥违背最左前缀。4.MVCC 有哪几个核心组成快照读、当前读分别对应哪些 SQL完整 MVCC 三大核心组成undo log保存数据历史版本构建版本链行隐藏字段trx_id最后修改事务 ID、roll_pointer指向 undo 上一个历史版本指针Read‑View 读视图控制我能看到哪些版本的数据RC/RR 最大区别就在 Read‑View 生成时机快照读普通select * from table读取 undo 版本链历史快照不加行锁。当前读select ... for update/select ... lock in share mode/update/delete读取最新数据会上锁。短句总结MVCC 三要素undo log、行隐藏字段 trx_id 与 roll_pointer、Read‑View。快照读普通 select当前读是加锁查询以及 update、delete 语句。间隙锁属于锁体系不属于 MVCC。5.RC 读已提交 和 RR 可重复读MVCC 的 Read‑View 生成时机有什么区别RR 级别是怎么解决幻读的1.Read‑View 生成时机RC vs RRRC读已提交每执行一次普通 select就生成一个新 Read‑View每次查询都拿最新视图所以能看到别的事务已经提交的数据会出现不可重复读同一个事务内两次 select 结果不一样。**RR可重复读InnoDB 默认事务开启之后**第一次执行快照读 select 的时候生成一次 Read‑View整个事务复用这一个视图 **。整个事务全程用同一个 Read‑View所以同一个事务多次 select 读到的数据不变实现可重复读。记忆口诀RC 每次查询新建视图RR 事务第一次 select 生成全程复用。2.RR 怎么解决幻读高频大坑幻读同一个事务两次当前读中间别的事务插入新行第二次读到多出的行。分两层快照读普通 select靠 MVCC 的 Read‑View读取历史版本看不到别人新插入的数据快照读没有幻读。当前读update/delete/for updateMVCC 管不住会产生幻读。InnoDB 使用Next‑Key 临键锁 (记录锁 间隙锁)锁住扫描到记录以及记录之间的间隙禁止其他事务在间隙插入数据解决当前读幻读。⚠️RR 不是单靠 MVCC 解决幻读快照读靠 MVCC当前读靠临键锁两者配合才解决幻读。极简口述答案RC 隔离级别每一次 select 都会生成新的 Read‑ViewRR 隔离级别事务第一次快照读时生成 Read‑View整个事务复用同一个视图。RR 解决幻读分为两部分快照读依靠 MVCC 的 Read‑View 读取历史快照避免幻读而 update、delete、for update 这类当前读依靠 Next‑Key 临键锁锁住间隙阻止其他事务插入新数据解决当前读的幻读。6.ACID 四个特性分别依靠 InnoDB 哪些机制来保证redo log、undo log 各自作用是什么什么是 WALACID 四个特性分开对应底层机制A 原子性事务要么全成要么全失败 →undo log失败回滚C 一致性是最终业务结果不是某一个组件直接实现原子 隔离 持久共同保障I 隔离性多个事务之间互不干扰 →MVCC 锁体系行锁、临键锁等D 持久性事务提交后数据永久生效宕机不丢 →redo logredo log记录数据页修改之后的物理日志宕机恢复用。事务提交redo log 刷盘保证崩溃之后可以恢复已提交事务。undo log保存数据修改前的历史版本用于事务回滚 MVCC 生成版本链不是简单事务操作日志。WALWrite‑Ahead‑Log 预写日志。修改数据页之前先把 redo log 写入磁盘再更新内存的数据页晚一点再刷脏页到磁盘。不是先写缓存再落盘。✅精简口述版A 原子性由 undo log 实现事务失败可以回滚D 持久性依靠 redo logI 隔离性靠锁和 MVCCC 一致性是业务最终结果由 AID 共同保证。redo log 记录数据页物理修改用于崩溃恢复undo log 保存修改前数据用于回滚和 MVCC 版本链。WAL 预写日志机制修改内存数据页之前先把 redo log 持久化磁盘脏页可以后续慢慢刷盘。7.Record 记录锁、Gap 间隙锁、Next‑Key 临键锁分别是什么什么场景会产生间隙锁前提只有 RR 可重复读隔离级别才会出现间隙锁、临键锁RC 没有间隙锁只有记录锁Record 记录锁行锁锁住已存在的某一行真实记录。只锁这条数据不让别的事务修改删除。Gap 间隙锁锁住两条记录中间的空隙不锁记录本身。目的禁止其他事务往这个间隙插入新数据防止幻读。间隙可以是索引最前面、两条记录中间、索引最后面之后。Next‑Key Lock 临键锁 Record 锁 Gap 间隙锁InnoDB RR 下默认的行锁算法。既锁住当前这条记录同时锁住这条记录前面的间隙防止插入。什么时候产生间隙锁RR 隔离级别使用当前读update / delete / select … for update查询条件走索引条件命中不到真实存在的数据就会产生间隙锁举例表 id 有 1、5、10执行update set name? where id77 不存在会锁住 5‑10 这个间隙其他事务不能插入 id6,7,8,9。重点坑RC 隔离级别没有间隙锁所以 RC 无法解决当前读幻读。精简短句Record 记录锁锁住真实存在的行记录。Gap 间隙锁锁住索引之间空隙阻止插入Next‑Key 临键锁 记录锁 间隙锁RR 下默认行锁。间隙锁产生条件RR 隔离级别当前读条件走索引查询的数据不存在锁住间隙防止插入解决幻读。8.死锁产生四个必要条件线上如何排查 MySQL 死锁死锁 4 个必要条件必须全部满足才会死锁互斥条件资源同一时刻只能一个事务占用行锁就是互斥不可剥夺已经拿到锁的事务别的事务不能强行抢走锁只能自己释放请求并保持事务已经持有部分锁同时还要去申请别的锁资源不释放手上已有的循环等待两个 / 多个事务互相持有对方想要的锁形成环路等待。记忆互斥、不可剥夺、请求保持、循环等待四个缺一不可破坏任意一个就消除死锁。MySQL 死锁排查执行命令show engine innodb status;输出结果里找到LATEST DETECTED DEADLOCK板块里面记录最近一次死锁两个事务分别执行什么 SQL、各自持有什么锁、等待什么锁。看错误日志也会打印死锁信息。定位业务 SQL调整加锁顺序尽量统一获取锁的顺序避免循环等待减少大事务事务尽快提交RC 隔离级别消除间隙锁降低死锁概率。口述精简版死锁四条件互斥、不可剥夺、请求并保持、循环等待。排查用show engine innodb status查看 LATEST DETECTED DEADLOCK拿到死锁两条事务 SQL解决手段统一加锁顺序、缩小事务、可改为 RC 减少间隙锁。9.explain 执行计划中 type 字段从最优到最差排序ALL 代表什么完整 type 优先级从最优 → 最差面试必须背顺序system const eq_ref ref range index ALL简单解释关键几项system表只有一行数据极少出现const主键 / 唯一索引等值查询最多匹配一行eq_ref关联查询主键、唯一索引匹配ref普通索引等值匹配range范围查询 between inindex索引全扫描扫全部索引叶子不回表比全表扫描 ALL 快但数据量大依然慢ALL全表扫描扫描聚簇索引全部数据行性能最差业务调优目标尽量达到range/ref杜绝 index、ALL。最后留个问题那么用到了索引就一定快吗
