本文旨在记录近期面试中遇到的 MySQL 核心考点帮助深入理解 MySQL 内部构造为日后工作中排查疑难问题打下基础。文中内容多由 AI 辅助生成如有疏漏或错误恳请指正。欢迎一起学习共同进步。一. InnoDB和MyISAM的区别考点事务核心区别事务支持InnoDB 支持事务MyISAM 不支持。锁粒度InnoDB 支持行级锁和表级锁MyISAM 仅支持表级锁。因此在高并发场景下InnoDB 的性能表现更优。外键约束InnoDB 支持外键MyISAM 不支持。事务区别详述:a. InnoDB 完整实现 ACID支持四种事务隔离级别读未提交、读已提交、可重复读MySQL 默认、串行化。b. 依靠 undo log回滚日志与 redo log重做日志实现事务redo log用于崩溃恢复保证事务的持久性。undo log用于事务回滚并支持 MVCC多版本并发控制。c. MyISAM 没有事务机制DML 语句执行中途失败无法回滚可能导致数据部分写入。没有 commit/rollback 概念每条 SQL 都被视为独立操作。实战影响对于订单、支付、账户扣减等强一致性业务必须使用 InnoDB。对于简单的日志表或静态数据且无需事务的场景可考虑 MyISAM目前极少使用。锁粒度区别详述:InnoDB 支持行锁和表锁而 MyISAM 仅支持表锁。MyISAM 表级锁执行 UPDATE、DELETE、INSERT 等写操作时会立即锁定整张表。问题同一时刻只能有一个线程执行写操作其他读写操作均被阻塞。适用场景读多写极少。在高并发写入场景下极易发生阻塞产生大量等待。InnoDB 行级锁Record Lock前提WHERE 条件必须使用有效索引否则行锁会退化为表锁高频面试点。附加锁机制Gap Lock间隙锁与Next-Key Lock临键锁用于在默认的“可重复读”隔离级别下解决幻读问题。InnoDB 也可手动加表锁如LOCK TABLES ... WRITE;但开发中一般不推荐使用。并发差异总结高并发读写、频繁更新的场景InnoDB 优势显著。MyISAM 的写操作会阻塞所有读写在并发写入场景下性能会急剧下降。外键约束区别详述(没太搞懂):InnoDB 支持MyISAM 不支持外键作用保证参照完整性例如订单表 order 关联用户表 user外键可以阻止插入不存在用户 ID 的订单外键底层要求关联字段必须同类型、建立索引生产环境普遍不推荐使用外键重要实战考点原因外键约束校验增加数据库压力分布式分库分表场景下外键完全失效出现死锁概率提升微服务架构下数据完整性一般交给应用代码控制不在数据库层面约束。结论InnoDB 虽然支持外键但企业项目大多禁用。补充区别MVCC多版本并发控制InnoDB支持 MVCC不加锁实现读操作快照读读写不冲突MyISAM没有 MVCC查询是当前数据写阻塞读。崩溃恢复InnoDB依靠 redo log宕机重启自动恢复数据安全性高MyISAM无崩溃安全机制断电、宕机极易损坏表文件需要执行repair table修复。索引结构与存储InnoDB聚簇索引主键和数据存在同一个文件二级索引存储主键值MyISAM非聚簇索引数据文件和索引文件完全分离。缓存InnoDB缓冲池 (Buffer Pool) 缓存索引 数据页MyISAM缓存只存索引数据靠操作系统文件缓存。二. 为什么都用BTree作为索引结构(考点BTree的索引结构)InnoDB与MyISAM默认采用BTree结构为什么不用二叉树、AVL、红黑树、B-Tree、哈希表对比二叉树 / 红黑树只有两路分支树很高磁盘 IO 多BTree 多路平衡树矮减少 IO。对比 B-TreeB-Tree 节点同时存 key 数据BTree 非叶子只存 key一页能放下更多索引树更低叶子用有序链表相连范围查询、排序更快所有查询都走到叶子性能稳定。对比哈希表哈希只支持等值查询不支持范围、排序、前缀匹配无法满足大部分 SQL 场景。总结磁盘 IO 是瓶颈BTree 最大限度降低 IO适配数据库等值 范围查询需求。话术总结BTree的所有叶子节点通过双向链表项链更适合范围查询BTree的所有数据存放在叶子节点中非叶子节点仅作路由作用因此每次查询都会走到叶子节点因此查询路径长度固定效率稳定BTree的非叶子节点只存储索引键值不存储数据这样每个节点能容纳更多键值树的高度更低查询所需的IO次数也就更少三. 什么是聚簇索引InnoDB有聚簇索引吗(考点BTree的查询机制sql优化回表)话术总结聚簇索引与数据行的存储顺序一直数据本身直接存放在索引的叶子节点上一张表只能有一个聚簇索引InnoDB一定存在聚簇索引创建规则①默认主键索引。②如无主键默认为第一个非空唯一索引。③以上都没有则在表生成时创建一个名为row_id的隐藏聚簇索引字节扩展除聚簇索引外如联合索引等也叫二级索引的存储顺序与物理行的存储顺序不一致它的也自己节点存储的是对应的主键值。回表当通过非聚簇索引进行查询时如果select 的字段包含除主键外的其他字段则此时需要根据叶子节点里的主键值去聚簇索引上对应的主键值所在的叶子节点获取对应的行数据此时这个动作就叫回表。(也不是所有的二级索引会引发回表只要保证当前select的字段在当前索引中存在即可避免回表比如联合索引字段ab此时select a,b便不会引发回表操作)四. MVCC 具体是什么有什么特点(考点隔离级别具体应用看第五题)话术总结MVCC 会为一条数据维护多个历史版本通过 undo log 构建版本链事务查询时利用 ReadView 选择满足可见性规则的数据版本。快照读读取历史版本无需加锁实现读写不阻塞以此完成并发控制这就是多版本并发控制。现象层面总结① 依靠 ReadView 可见性规则看不到其他事务未提交版本避免脏读② 写事务持有排他锁阻塞其他写请求快照读不走锁读取 undo 历史版本实现读写不阻塞个人总结主要应用在’读已提交’与’可重复读’的隔离级别在这两种隔离级别中当开启事务后进行select操作会通过undo log构建的版本链找到一条符合可见规则的历史版本返回查询结果。对于这两种不同的隔离级别有着不用的处理。举个例子对于不同级别下MVCC的一个处理机制与结果两个事务并发执行#事务Abegin;#开启事务selectid,name,statuswhereid1;selectid,name,statuswhereid1;selectid,name,statuswhereid1;#*事务Bbegin;#开启事务updatetsetstatus0whereid1;commit;#提交事务已以上两个事务A事务首先执行但并未提交B事务随后执行已提交读已提交在事务A执行时此时查询的结果为当前行数据status字段0在事务B提交后再次查询当前行数据status字段1出现不可重复读可重复五在事务A执行时此时在事务A提交之前查询的结果一直为当前行数据status字段1前后查询结果一致规避不可重复读MVCC 简要执行流程执行普通 select 快照读1.生成 ReadViewRC 每次查询新建RR 事务首次快照读创建全程复用2.顺着 undo log 的回滚指针遍历版本链3.使用 ReadView 规则逐一判断每条版本是否对当前事务可见4.返回第一条满足可见性的数据版本。五. MySQL默认隔离级别是什么能解决哪些问题(考点隔离级别)数据操作的三个定义脏读读到其他事务未提交的数据对方回滚读到的数据无效。不可重复读同一事务内两次查询同一行中间被别的事务修改提交两次结果不一样。幻读同一事务范围查询别的事务新增 / 删除数据并提交前后查询行数不一致。区分不可重复读侧重数据更新幻读侧重新增、删除。1. READ UNCOMMITTED 读未提交(最低隔离级别)允许读取未提交数据存在问题脏读、不可重复读、幻读线上几乎没人使用如事务 B 还没 commit事务 A 就能看到它的修改极易读到脏数据。2. READ COMMITTEDRC读已提交Oracle 默认级别很多互联网项目主动切换至此,对于一致性要求不是特别严格的可以用只能读到其他事务已经提交的数据✅ 解决脏读❌ 存在不可重复读、幻读此处MVCC特点每次普通 select快照读都会新建 Read View。别的事务提交更新后当前事务再次查询能立刻看到最新值。优点间隙锁失效锁范围更小死锁概率降低缺点同一事务多次查询同一行结果可能变化不可重复读。3. REPEATABLE READRR可重复读InnoDB 默认隔离级别✅ 解决脏读、不可重复读⚠️幻读快照读普通 selectMVCC 快照看不到新插入数据感受不到幻读当前读update/delete/for update仍会出现幻读依靠临键锁 Next-Key Lock解决此处MVCC特点事务中第一次快照读时创建 Read View整个事务复用。同一事务多次查询始终看到同一套快照数据不受外部事务提交影响。4. SERIALIZABLE 串行化最高隔离级别✅ 脏读、不可重复读、幻读全部解决工作方式普通 select 自动转为 select … lock in share mode全部变成当前读读写互相阻塞。缺点并发能力极差大量锁等待、死锁业务极少使用(常规项目正式生产环境基本不用)隔离级别脏读不可重复读幻读读未提交发生发生发生读已提交 RC杜绝发生发生可重复读 RR (默认)杜绝杜绝快照读规避当前读依靠临键锁解决串行化杜绝杜绝杜绝扩展RC 和 RR 最核心区别回答ReadView 生成时机RC 每次快照读新建RR 事务首次快照读创建全程复用。六. 拿到一条慢SQL该做什么(考点SQL优化流程)话术总结在格式没问题的前提下优先聚焦where条件进行逐步排查①. where后的查询条件是否走索引字段。②是否索引失效如条件加入函数运算、隐式转换、like %xxx’前置通配符、in/not in等③索引正常命中是否由于查询字段过多引发的回表操作导致性能过低。完整排查流程① 定位慢SQL开启慢查询日志SET GLOBAL slow_query_log ON;设置阈值SET GLOBAL long_query_time 1;单位秒默认10秒查看慢日志SHOW VARIABLES LIKE %slow_query_log%;实时查看线程SHOW FULL PROCESSLIST;借助监控工具阿里云ARMS、Prometheus Grafana② EXPLAIN分析执行计划语法EXPLAIN SELECT ... FROM ... WHERE ...;重点关注以下字段字段含义优化目标type访问类型至少达到range最好是const/eq_ref/refkey实际使用的索引不为 NULLrows扫描行数越小越好Extra额外信息避免Using filesort、Using temporarytype性能排序system const eq_ref ref range index ALL出现ALL表示全表扫描必须优化③ 常见优化策略扩展索引优化为 WHERE、JOIN、ORDER BY 字段建索引联合索引遵循最左前缀法则- 使用覆盖索引减少回表Extra 中出现Using index避免索引失效详见上方总结SQL 改写禁止SELECT *只查询需要的字段小表驱动大表合理选用 IN / EXISTS批量操作代替循环单条插入避免在索引列上使用函数、表达式、隐式类型转换合理使用分页LIMIT 游标 / 主键分段表结构优化字段类型选择合理尽量小、精确- NOT NULL 设默认值减少 NULL 判断大字段TEXT / BLOB垂直拆分单表数据量过大 500万考虑水平拆分架构层面引入缓存Redis减轻 DB 压力读写分离主从架构分库分表Sharding-JDBC、MyCat冷热数据分离 本文旨在记录近期面试中遇到的 MySQL 核心考点帮助深入理解 MySQL 内部构造为日后工作中排查疑难问题打下基础。文中内容多由 AI 辅助生成如有疏漏或错误恳请指正。欢迎一起学习共同进步。一. InnoDB和MyISAM的区别考点事务核心区别事务支持InnoDB 支持事务MyISAM 不支持。锁粒度InnoDB 支持行级锁和表级锁MyISAM 仅支持表级锁。因此在高并发场景下InnoDB 的性能表现更优。外键约束InnoDB 支持外键MyISAM 不支持。事务区别详述:a. InnoDB 完整实现 ACID支持四种事务隔离级别读未提交、读已提交、可重复读MySQL 默认、串行化。b. 依靠 undo log回滚日志与 redo log重做日志实现事务redo log用于崩溃恢复保证事务的持久性。undo log用于事务回滚并支持 MVCC多版本并发控制。c. MyISAM 没有事务机制DML 语句执行中途失败无法回滚可能导致数据部分写入。没有 commit/rollback 概念每条 SQL 都被视为独立操作。实战影响对于订单、支付、账户扣减等强一致性业务必须使用 InnoDB。对于简单的日志表或静态数据且无需事务的场景可考虑 MyISAM目前极少使用。锁粒度区别详述:InnoDB 支持行锁和表锁而 MyISAM 仅支持表锁。MyISAM 表级锁执行 UPDATE、DELETE、INSERT 等写操作时会立即锁定整张表。问题同一时刻只能有一个线程执行写操作其他读写操作均被阻塞。适用场景读多写极少。在高并发写入场景下极易发生阻塞产生大量等待。InnoDB 行级锁Record Lock前提WHERE 条件必须使用有效索引否则行锁会退化为表锁高频面试点。附加锁机制Gap Lock间隙锁与Next-Key Lock临键锁用于在默认的“可重复读”隔离级别下解决幻读问题。InnoDB 也可手动加表锁如LOCK TABLES ... WRITE;但开发中一般不推荐使用。并发差异总结高并发读写、频繁更新的场景InnoDB 优势显著。MyISAM 的写操作会阻塞所有读写在并发写入场景下性能会急剧下降。外键约束区别详述(没太搞懂):InnoDB 支持MyISAM 不支持外键作用保证参照完整性例如订单表 order 关联用户表 user外键可以阻止插入不存在用户 ID 的订单外键底层要求关联字段必须同类型、建立索引生产环境普遍不推荐使用外键重要实战考点原因外键约束校验增加数据库压力分布式分库分表场景下外键完全失效出现死锁概率提升微服务架构下数据完整性一般交给应用代码控制不在数据库层面约束。结论InnoDB 虽然支持外键但企业项目大多禁用。补充区别MVCC多版本并发控制InnoDB支持 MVCC不加锁实现读操作快照读读写不冲突MyISAM没有 MVCC查询是当前数据写阻塞读。崩溃恢复InnoDB依靠 redo log宕机重启自动恢复数据安全性高MyISAM无崩溃安全机制断电、宕机极易损坏表文件需要执行repair table修复。索引结构与存储InnoDB聚簇索引主键和数据存在同一个文件二级索引存储主键值MyISAM非聚簇索引数据文件和索引文件完全分离。缓存InnoDB缓冲池 (Buffer Pool) 缓存索引 数据页MyISAM缓存只存索引数据靠操作系统文件缓存。二. 为什么都用BTree作为索引结构(考点BTree的索引结构)InnoDB与MyISAM默认采用BTree结构为什么不用二叉树、AVL、红黑树、B-Tree、哈希表对比二叉树 / 红黑树只有两路分支树很高磁盘 IO 多BTree 多路平衡树矮减少 IO。对比 B-TreeB-Tree 节点同时存 key 数据BTree 非叶子只存 key一页能放下更多索引树更低叶子用有序链表相连范围查询、排序更快所有查询都走到叶子性能稳定。对比哈希表哈希只支持等值查询不支持范围、排序、前缀匹配无法满足大部分 SQL 场景。总结磁盘 IO 是瓶颈BTree 最大限度降低 IO适配数据库等值 范围查询需求。话术总结BTree的所有叶子节点通过双向链表项链更适合范围查询BTree的所有数据存放在叶子节点中非叶子节点仅作路由作用因此每次查询都会走到叶子节点因此查询路径长度固定效率稳定BTree的非叶子节点只存储索引键值不存储数据这样每个节点能容纳更多键值树的高度更低查询所需的IO次数也就更少三. 什么是聚簇索引InnoDB有聚簇索引吗(考点BTree的查询机制sql优化回表)话术总结聚簇索引与数据行的存储顺序一直数据本身直接存放在索引的叶子节点上一张表只能有一个聚簇索引InnoDB一定存在聚簇索引创建规则①默认主键索引。②如无主键默认为第一个非空唯一索引。③以上都没有则在表生成时创建一个名为row_id的隐藏聚簇索引字节扩展除聚簇索引外如联合索引等也叫二级索引的存储顺序与物理行的存储顺序不一致它的也自己节点存储的是对应的主键值。回表当通过非聚簇索引进行查询时如果select 的字段包含除主键外的其他字段则此时需要根据叶子节点里的主键值去聚簇索引上对应的主键值所在的叶子节点获取对应的行数据此时这个动作就叫回表。(也不是所有的二级索引会引发回表只要保证当前select的字段在当前索引中存在即可避免回表比如联合索引字段ab此时select a,b便不会引发回表操作)四. MVCC 具体是什么有什么特点(考点隔离级别具体应用看第五题)话术总结MVCC 会为一条数据维护多个历史版本通过 undo log 构建版本链事务查询时利用 ReadView 选择满足可见性规则的数据版本。快照读读取历史版本无需加锁实现读写不阻塞以此完成并发控制这就是多版本并发控制。现象层面总结① 依靠 ReadView 可见性规则看不到其他事务未提交版本避免脏读② 写事务持有排他锁阻塞其他写请求快照读不走锁读取 undo 历史版本实现读写不阻塞个人总结主要应用在’读已提交’与’可重复读’的隔离级别在这两种隔离级别中当开启事务后进行select操作会通过undo log构建的版本链找到一条符合可见规则的历史版本返回查询结果。对于这两种不同的隔离级别有着不用的处理。举个例子对于不同级别下MVCC的一个处理机制与结果两个事务并发执行#事务Abegin;#开启事务selectid,name,statuswhereid1;selectid,name,statuswhereid1;selectid,name,statuswhereid1;#*事务Bbegin;#开启事务updatetsetstatus0whereid1;commit;#提交事务已以上两个事务A事务首先执行但并未提交B事务随后执行已提交读已提交在事务A执行时此时查询的结果为当前行数据status字段0在事务B提交后再次查询当前行数据status字段1出现不可重复读可重复五在事务A执行时此时在事务A提交之前查询的结果一直为当前行数据status字段1前后查询结果一致规避不可重复读MVCC 简要执行流程执行普通 select 快照读1.生成 ReadViewRC 每次查询新建RR 事务首次快照读创建全程复用2.顺着 undo log 的回滚指针遍历版本链3.使用 ReadView 规则逐一判断每条版本是否对当前事务可见4.返回第一条满足可见性的数据版本。五. MySQL默认隔离级别是什么能解决哪些问题(考点隔离级别)数据操作的三个定义脏读读到其他事务未提交的数据对方回滚读到的数据无效。不可重复读同一事务内两次查询同一行中间被别的事务修改提交两次结果不一样。幻读同一事务范围查询别的事务新增 / 删除数据并提交前后查询行数不一致。区分不可重复读侧重数据更新幻读侧重新增、删除。1. READ UNCOMMITTED 读未提交(最低隔离级别)允许读取未提交数据存在问题脏读、不可重复读、幻读线上几乎没人使用如事务 B 还没 commit事务 A 就能看到它的修改极易读到脏数据。2. READ COMMITTEDRC读已提交Oracle 默认级别很多互联网项目主动切换至此,对于一致性要求不是特别严格的可以用只能读到其他事务已经提交的数据✅ 解决脏读❌ 存在不可重复读、幻读此处MVCC特点每次普通 select快照读都会新建 Read View。别的事务提交更新后当前事务再次查询能立刻看到最新值。优点间隙锁失效锁范围更小死锁概率降低缺点同一事务多次查询同一行结果可能变化不可重复读。3. REPEATABLE READRR可重复读InnoDB 默认隔离级别✅ 解决脏读、不可重复读⚠️幻读快照读普通 selectMVCC 快照看不到新插入数据感受不到幻读当前读update/delete/for update仍会出现幻读依靠临键锁 Next-Key Lock解决此处MVCC特点事务中第一次快照读时创建 Read View整个事务复用。同一事务多次查询始终看到同一套快照数据不受外部事务提交影响。4. SERIALIZABLE 串行化最高隔离级别✅ 脏读、不可重复读、幻读全部解决工作方式普通 select 自动转为 select … lock in share mode全部变成当前读读写互相阻塞。缺点并发能力极差大量锁等待、死锁业务极少使用(常规项目正式生产环境基本不用)隔离级别脏读不可重复读幻读读未提交发生发生发生读已提交 RC杜绝发生发生可重复读 RR (默认)杜绝杜绝快照读规避当前读依靠临键锁解决串行化杜绝杜绝杜绝扩展RC 和 RR 最核心区别回答ReadView 生成时机RC 每次快照读新建RR 事务首次快照读创建全程复用。六. 拿到一条慢SQL该做什么(考点SQL优化流程)话术总结在格式没问题的前提下优先聚焦where条件进行逐步排查①. where后的查询条件是否走索引字段。②是否索引失效如条件加入函数运算、隐式转换、like %xxx’前置通配符、in/not in等③索引正常命中是否由于查询字段过多引发的回表操作导致性能过低。完整排查流程① 定位慢SQL开启慢查询日志SET GLOBAL slow_query_log ON;设置阈值SET GLOBAL long_query_time 1;单位秒默认10秒查看慢日志SHOW VARIABLES LIKE %slow_query_log%;实时查看线程SHOW FULL PROCESSLIST;借助监控工具阿里云ARMS、Prometheus Grafana② EXPLAIN分析执行计划语法EXPLAIN SELECT ... FROM ... WHERE ...;重点关注以下字段字段含义优化目标type访问类型至少达到range最好是const/eq_ref/refkey实际使用的索引不为 NULLrows扫描行数越小越好Extra额外信息避免Using filesort、Using temporarytype性能排序system const eq_ref ref range index ALL出现ALL表示全表扫描必须优化③ 常见优化策略扩展索引优化为 WHERE、JOIN、ORDER BY 字段建索引联合索引遵循最左前缀法则- 使用覆盖索引减少回表Extra 中出现Using index避免索引失效详见上方总结SQL 改写禁止SELECT *只查询需要的字段小表驱动大表合理选用 IN / EXISTS批量操作代替循环单条插入避免在索引列上使用函数、表达式、隐式类型转换合理使用分页LIMIT 游标 / 主键分段表结构优化字段类型选择合理尽量小、精确- NOT NULL 设默认值减少 NULL 判断大字段TEXT / BLOB垂直拆分单表数据量过大 500万考虑水平拆分架构层面引入缓存Redis减轻 DB 压力读写分离主从架构分库分表Sharding-JDBC、MyCat冷热数据分离 
