不瞒你说我在带新人、帮人做面试辅导的时候反复强调过一个观点MySQL学到五六章才算是真正入了门。前面的建库建表、增删改查说白了是体力活背一背谁都行但到了事务、锁、索引、存储过程这一带拼的才是对数据库底层机制的理解。这两天我把黑马程序员的第五章和第六章重新过了一遍顺手把复习笔记整理成文。这篇文章不打算从头复述教程而是按照面试考什么、实战用在哪、底层原理怎么串这条线把这两章的核心知识拆开揉碎讲清楚。正在准备面试、或者学完这两章想系统巩固一下的朋友可以照着这个框架自查一遍。1. 第五章第六章到底讲了什么先摸清知识地图再复习很多人复习有个误区打开视频从头再看一遍看到哪里算哪里看完发现脑子里还是一团浆糊。我习惯的做法是先不看内容而是把章节标题拎出来自己问自己这一章如果让我出三道面试题我会出什么然后再对照教程查漏补缺。下面是我基于黑马课程第五章第六章梳理出来的整体版图。1.1 课程定位事务和索引为什么被放在一起讲黑马程序员这套MySQL课程的前四章基本是在教你怎么操作数据到了第五章多表查询与事务、第六章索引与优化突然从操作跳到了机制。这两个章节放在一起不是没道理的事务保证了数据在并发场景下的可靠性索引保证了数据在大量场景下的查询效率这两个东西共同支撑起InnoDB存储引擎既要稳又要快的核心能力。往深了说事务和索引也不是孤立的知识点。事务隔离级别的实现依赖锁和MVCC而锁的粒度设计又和索引结构密不可分——比如InnoDB的行锁到底是锁在索引上还是锁在数据行上这个问题不弄清索引结构是答不利索的。所以我建议复习的时候别一章一章割裂着看要尝试画一条线把它们串起来。1.2 把知识点按背、懂、会三层分类同样的知识复习深度要求不一样。我给学员做整理的时候通常把这两章的内容拆成三档掌握层级代表知识点复习要求必须背事务四大特性ACID、隔离级别名称、索引分类张嘴就来不能卡壳必须懂原理MVCC快照读、B树结构、最左前缀法则、锁的兼容矩阵能讲清楚为什么能举出反例必须会实操事务控制语句、索引创建/查看/删除、存储过程编写、EXPLAIN分析手写SQL能说清执行计划每列含义为什么要这么分因为面试问法和考核点完全不同。背的题短平快原理题要展开讲两三分钟实操题直接给你一个场景让你写语句。如果复习时一视同仁效率一定低。我自己复习的时候会先拿一张纸默写第一档和第二档的框架默写不出来的地方标红重点看。1.3 这两章和后面所有章节的关系还有一点必须提醒第五章和第六章是后续所有进阶内容的地基。你以后学主从复制、分库分表、性能调优、读写分离底层全部绕不开事务日志和索引机制。比如主从复制要理解binlog和事务提交的关系分库分表要理解分布式事务和全局唯一索引性能调优更是直接吃第六章的老本。所以这两章复习得好不好直接决定你后面能不能起飞别抱着考完就扔的心态学。2. 第五章核心之一事务机制——从ACID到隔离级别一条线打通事务这块是第五章的绝对重点也是面试问得最密集的区域。很多人ACID背得滚瓜烂熟但一被问到MySQL默认隔离级别是什么为什么要有这个级别就卡住这就是典型的只背结论不追原因。2.1 为什么需要事务拿转账场景说透ACID学事务最好的切入场景就是转账。A给B转账100块这事由两条SQL组成一条从A账户扣钱一条往B账户加钱。如果第一条执行成功、第二条执行失败而中间没有事务包着钱就凭空消失了。事务就是用来保证这类操作要么全成功、要么全不成功的机制。ACID四个字拆开看原子性对应上面的全成功或全不成功一致性指转账前后总金额不变隔离性指两个事务同时操作时互不干扰持久性指事务一旦提交数据就不能再丢。黑马课程里对这四个特性各有展开但我复习时觉得最值得深挖的是原子性和隔离性——因为这俩分别对应了undo log和锁机制是面试能拉开差距的地方。要特别纠正一个容易误解的说法原子性是靠undo log实现的不是靠数据库很智能地判断该不该执行。事务里每一条SQL执行前InnoDB都会先往undo log里写一条反向操作的记录你执行UPDATEundo log就记一条对应的DELETE或反向UPDATE。一旦事务中途失败引擎可以根据undo log把数据回滚到执行前的样子。这就是原子性的底层。2.2 四种隔离级别脏读、不可重复读、幻读逐个击破隔离级别是为了解决并发事务互相干扰的问题。四个级别从低到高分别是读未提交、读已提交、可重复读、串行化。先明确三个异常场景的定义脏读事务A读到了事务B未提交的数据之后B回滚了A读到的就是脏数据。不可重复读事务A内两次查询读到的事务B已提交的不同数据——重点在同一个事务内两次查询结果不一致。幻读事务A按条件查询得到一批记录此时事务B插入了一条新记录并提交事务A再次按相同条件查询多出来一条像幻觉一样的记录。隔离级别对这三个异常的解决情况我直接做成表隔离级别脏读不可重复读幻读读未提交可能发生可能发生可能发生读已提交不会可能发生可能发生可重复读不会不会可能发生InnoDB下不会串行化不会不会不会这里有个特级重要的知识点也是黑马课程里强调过的MySQL默认的隔离级别是可重复读但在InnoDB引擎下可重复读级别通过间隙锁和MVCC把幻读也一并解决了。所以在MySQL里可重复读级别下你实际是遇不到幻读的。这不代表你能把课本上的表格背错但面试官如果问MySQL默认级别下会不会幻读你要能给出准确回答。2.3 MVCC快照读背后的功臣我第一次学MVCC的时候觉得这名词特别唬人拆开就明白了Multi-Version Concurrency Control多版本并发控制。它的核心思想是在可重复读级别下事务读到的数据不是实时的最新值而是这个事务开始那一刻的快照。MVCC靠三个隐藏字段实现DB_TRX_ID最近修改该行的事务ID、DB_ROLL_PTR指向undo log里上一个版本的指针、DB_ROW_ID隐藏主键。配合视图read view来判断当前事务能看到哪个版本的数据。我复习时手动画过一条链一条记录被三个事务依次修改undo log会形成版本链每读一次就沿着版本链找符合可见性规则的版本。这块内容是第五章最难啃的骨头但也是区分背过和真懂的分水岭。建议至少花一个晚上配合画图把版本链和可见性判断规则捋清楚。网上有很多MVCC的动图前面看十遍不如自己动手画一遍。3. 第五章核心之二锁机制——以前觉得难串起来后豁然开朗锁在第五章里是承上启下的角色隔离级别靠锁实现索引又决定了锁的粒度。黑马课程里锁的分类讲得很细但很多同学学完只记住一句话锁有表锁和行锁真遇到问题不会分析。我自己复习时是按有哪些锁、锁谁的资源、锁和隔离级别什么关系、什么场景会出问题四个问题串的。3.1 锁的分类从粒度、模式、算法三个维度拆按粒度分锁分成表锁和行锁。表锁开销小、并发度低MyISAM引擎只有表锁行锁开销大、并发度高InnoDB支持行锁是它比MyISAM更适合高并发场景的关键原因之一。按模式分有共享锁S锁读锁和排他锁X锁写锁。共享锁和共享锁可以共存共享锁和排他锁互斥排他锁和排他锁互斥。这个兼容矩阵必须背熟后面分析死锁靠的就是它。按算法分InnoDB的行锁又细分为记录锁、间隙锁、临键锁。记录锁锁的是索引记录本身间隙锁锁的是索引记录之间的间隙目的是防止幻读临键锁是记录锁和间隙锁的组合锁住的是一个范围范围里的记录。我第一次学的时候总觉得这三兄弟太多余后来才明白没有间隙锁和临键锁可重复读隔离级别根本防不住幻读。3.2 行锁到底锁的是什么索引项还是数据行这个坑很多人不知道但面试特别喜欢问InnoDB的行锁锁的到底是行还是索引答案是索引。InnoDB的聚簇索引叶子节点直接存着整行数据所以锁定了索引项就等于锁定了数据行。而如果SQL的WHERE条件没走索引InnoDB就只能用表锁级别的扫描行锁形同虚设。给你一个具体场景一条UPDATE user SET age18 WHERE name小明如果name字段没建索引InnoDB会全表扫描找到所有匹配记录逐条加行锁。表面看是行锁但因为扫过大量无索引记录加锁范围被放大锁冲突概率剧增。这也是为什么我一直强调更新、删除语句的WHERE条件尽量走索引这不只是查询优化问题更是锁开销问题。3.3 从锁的角度重新看待死锁和锁等待有了锁就会有等待和死锁。锁等待好理解事务A拿着某行锁不释放事务B要改同一行就得等等超过innodb_lock_wait_timeout默认50秒就直接报错。死锁更麻烦一点事务A拿着资源1等资源2事务B拿着资源2等资源1谁也等不到谁只能靠InnoDB的死锁检测机制杀一个回滚。我见过最典型的死锁场景是两个事务以不同顺序更新两张表。事务A先更新表1再更新表2事务B先更新表2再更新表1高并发下撞车概率极高。解决办法就是让所有事务都按照相同的顺序访问资源。这个经验在面试里可以直接当加分项讲出来。4. 第六章核心之一索引——B树、聚簇与回表全链路拆解进入第六章索引这东西你平时天天用但真要讲清楚为什么索引能加速没几个人能说到点子上。索引这块我复习时最大的感受是只要把B树的结构和聚簇索引的关系搞明白后面所有问题都能推导出来。4.1 为什么MySQL选了B树而不是哈希表、红黑树面试必背题MySQL索引为什么用B树回答这题的关键在于对比。哈希表查询单个数据确实猛O(1)的复杂度但哈希索引不支持范围查询、不支持排序而且哈希冲突时性能不稳定。红黑树是平衡二叉树查找复杂度O(logN)但树太高了数据量一千万红黑树高度大概二十多层每次查找都要访问二十多个节点磁盘IO次数扛不住。B树的优势正好打在数据库的痛点上它的非叶子节点只存索引键和指针一个节点能容纳成百上千个键值树高度极低三到四层就能支撑千万级数据叶子节点之间用指针相连形成有序链表范围查询和排序直接顺着链表走就行。数据库的IO瓶颈主要在磁盘B树把树高压到极致等于把磁盘IO次数压到极致。4.2 聚簇索引、二级索引、回表、覆盖索引这个链条是第六章的主心骨。InnoDB的聚簇索引就是主键索引叶子节点存储的是完整的数据行二级索引我们平时建的非主键索引叶子节点存储的是索引列主键值。注意这个差异二级索引查完拿到的是主键还得再通过主键回聚簇索引查一遍完整数据这个过程就是回表。回表带来一次额外的磁盘IO所以就有了覆盖索引的优化思路如果你要查的字段本身就包含在二级索引里那就直接拿结果不需要回表。比如SELECT id, name FROM user WHERE name张三只要name和id都在二级索引的叶子节点上这就是覆盖索引查询。这也是为什么很多场景下推荐用联合索引选择性查询列而不是SELECT *。4.3 最左前缀法则和索引失效考试和实战的双重暴击黑马课程对最左前缀法则的讲解很清楚联合索引(a,b,c)实际上建立了a、a,b、a,b,c三个索引查询条件必须从最左列开始连续匹配才能用上索引。比如条件只有b1或者c1 and b1索引就废了。但光知道最左前缀还不够很多索引失效的场景要是没吃过亏根本记不住。我把常见的失效场景列一下在索引列上做函数操作或计算比如WHERE SUBSTR(name,1,2)张隐式类型转换比如索引列是varchar传入的数字类型没加引号LIKE模糊查询以通配符开头比如WHERE name LIKE %明OR连接的非索引列条件使用IS NULL、IS NOT NULL、!、等不等于条件部分场景下复习的时候我建议把这些失效场景每个都自己跑一遍EXPLAIN看key列到底有没有用上索引。看十遍文档不如亲手看一眼执行计划。5. 第六章核心之二EXPLAIN执行计划——把索引好不好用变成看得见的证据Black horse课程第六章后半段会教你用EXPLAIN分析SQL。这个工具学会之后你就不再是凭感觉优化了而是能指着执行计划告诉别人你的SQL慢在这条索引没走对。5.1 核心字段速查type、key、rows、ExtraEXPLAIN输出一堆字段新手看不过来。我复习时只重点盯四个type、key、rows、Extra。type字段是访问类型的等级从好到差大致是system const eq_ref ref range index ALL。看到ALL就要警惕这是全表扫描看到index也高兴不起来这代表扫描了整个索引树。至少达到ref或者range才算健康。key字段表示实际用到的索引名如果为NULL说明没用上索引。rows是估算的扫描行数数值越小越好但不能全信只是优化器的估算。Extra字段里看到Using filesort就麻烦了虽然字面意思不是文件排序但这代表MySQL需要额外的排序操作性能一般不会好看到Using index则是大好消息这是覆盖索引的标志。5.2 一次完整的调优实战从ALL到ref光背字段没用要会实战。我拿课程里那个经典例子改一下演示整个排查链路一条SELECT * FROM order WHERE user_id123 AND status1先跑EXPLAIN看到typeALL、rows五十多万行——这就是全表扫描了慢SQL没跑。第一步检查WHERE条件里的列发现user_id和status都没索引。给user_id建了单列索引后type变成ref。但rows还是偏高因为user_id123的订单可能有一万条再做status过滤还是扫一万行。第二步建联合索引(user_id, status)type还是ref但rows降到了几百行。顺手再看Extra从原来的乱七八糟变成Using index condition说明下推优化生效了。三次EXPLAIN跑下来整个优化过程完全可视化。这个先看type、再建索引、再看rows验证的方法论比任何口诀都管用。你现在学的每一条索引失效知识最终都要落到EXPLAIN的验证上。5.3 索引下推与ICP一个容易被忽视的优化点索引下推Index Condition PushdownICP是MySQL 5.6以后引入的优化。简单说以前二级索引查询时服务层把符合索引条件的所有记录都回表再由服务层去过滤其他条件有了ICP存储引擎在读取索引记录时就能先判断其余条件是否能过滤掉一部分记录减少回表次数。黑马课程对这个优化专门做了说明但很多同学没注意。用上面的例子说联合索引(user_id,status)MySQL在引擎层拿索引记录时发现status1不满足直接跳过不用回表拿整行再判断。执行计划里出现Using index condition就代表ICP生效了。这算是个白捡的性能优化知道它的原理和标志信息面试时很加分。6. 存储过程与触发器第五章第六章的实用部分别只停留在会写黑马课程在第六章后半段安排了存储过程和触发器的内容。说实话现在一线互联网公司用存储过程的比例在下降业务逻辑更多放到了应用层但这不代表你可以跳过这部分——面试会问、老系统里也还有大量存量代码你得能看得懂、改得动。6.1 存储过程的核心语法和变量体系存储过程说白了就是把一段SQL逻辑封装起来像写函数一样带参数、有返回值不是函数那种return是通过OUT参数传出去。典型结构是CREATE PROCEDURE 名称(参数列表) BEGIN ... END中间可以写IF判断、CASE分支、WHILE/REPEAT循环。变量分两类局部变量用DECLARE声明比如DECLARE cnt INT DEFAULT 0用户变量用SET name xxx。面试常考的一个点是DECLARE必须在BEGIN块的开头声明不能边写边声明——这个语法习惯和写代码差别很大容易忘。游标是存储过程里最容易翻车的部分。游标的作用是逐行遍历查询结果集但它的使用套路比较死板声明游标、打开游标、FETCH取值、关闭游标缺一不可。黑马课程里有关闭游标和退出循环的写法我建议没写过的同学一定要亲手敲一遍否则面试手写游标时容易当场卡壳。6.2 触发器的时机与隐藏坑触发器Trigger是在表上监听INSERT、UPDATE、DELETE操作自动执行一段逻辑。分为BEFORE和AFTER两类分别对应操作前的校验拦截和操作后的联动处理。比如订单表插入一条记录后自动更新订单统计表这就可以用AFTER INSERT触发器搞定。触发器的坑必须知道首先触发器会让隐式操作变得不可见排查问题时经常被忽略——你明明只执行了一条INSERT但影响了三张表这在生产环境是很危险的事其次触发器过多会拖慢DML性能最后触发器里的SQL没法像存储过程那样方便调试错了很难定位。我的建议是能不用就尽量不用除非是数据归档、审计日志这类被动的旁路场景。6.3 视图虚拟表到底解决了什么问题视图也属于第六章的内容。视图本身不存数据只是把一条SQL查询保存成一张虚拟表好处是复用复杂查询、隐藏敏感字段、提供一定程度的安全隔离。比如CREATE VIEW v_user AS SELECT id,name FROM user对外只暴露这两个字段手机号等隐私字段就被遮住了。不过视图执行时还是会去执行底层的查询语句不会因为建了视图就自动变快。有些同学误以为视图能加速查询这是常见误区。视图适合做逻辑层面的封装简化性能优化还得靠索引和SQL改写。7. 期末自查清单用面试题的方式检验这两章学透没有复习最终要闭环不能只看不测。我给自己整理了一份自查问题清单都是从这两章高频考点提炼出来的每个问题要求自己不看任何资料、用口语讲清楚才算过。先是一档的基础题事务的ACID分别是什么MySQL默认隔离级别是哪个共享锁和排他锁兼容关系是什么索引底层为什么用B树这四题要求30秒内答完答不上的话说明基础不牢回头重新过。二档原理题MVCC在可重复读级别下怎么解决幻读间隙锁和临键锁的区别是什么聚簇索引和二级索引的叶子节点有什么不同回表和覆盖索引的关系这些题要能画图、举例子讲三分钟。三档实战题给你一条慢SQL说说你的优化思路联合索引(a,b,c)写几个可以用索引的查询条件和几个无法用索引的条件存储过程里怎么声明游标并循环遍历这三道题必须在纸上写出SQL才能算过。顺带把近期热点里被反复问的技术点也补一句像MySQL 5.7.44安装配置MySQL锁表分析Docker部署MySQL失败这类运维问题底层考的还是这两章的知识——比如锁表就是行锁范围放大、连接失败有时和事务未提交有关。你把原理吃透了字面换了马甲也认得出来。我自己复习时的经验是第一天把框架画出来第二天逐个攻破原理第三天专门用EXPLAIN跑各种SQL做验证第四天用上面的清单闭卷自查。这样四天下来比我当初第一遍学两周的效果都好。学习方法这东西有时候就是把时间花在检验上比把时间花在重复看视频上划算得多。这两章啃透了后面的路会顺很多。