MySQL核心机制详解：索引、事务隔离、锁与日志

写MySQL八股文这个东西这几年被很多人吐槽过说它“背了没用”“纸上谈兵”。但说句公道话如果把八股文当成纯粹的背诵题那确实没什么意义可如果你把它当成一份“问题清单”顺着这些问题去把底层的机制捋清楚它反而是效率极高的复习路径。我自己当年准备面试的时候就有这种体会——很多知识点平时写代码根本不会碰比如隔离级别的实现细节、redo log和binlog的配合方式都是靠着“背八股”的契机才真正搞明白的。这篇是第一篇我准备把MySQL里最常考、也最核心的几块地基先梳理一遍索引的底层结构、事务的隔离级别、锁机制和日志系统。这四个东西是理解MySQL的支柱后面的优化、主从复制、分库分表全都在它们之上长起来的。1. 索引B树为什么是MySQL的默认答案索引这一块面试基本是从“为什么用B树”开始的。这个问题看着简单但能把B树、B树、哈希索引、跳表这几样东西的差异说清楚的人其实不多。1.1 B树和B树的差异以及为什么InnoDB选后者先明确一点MySQL的InnoDB引擎用的是B树不是B树。这两个名字长得像结构上也有血缘关系但关键差别在三个地方。第一B树的非叶子节点不存数据只存索引键。这意味着每一页能放下的键数量会多很多。InnoDB默认的页大小是16KB假设主键是8字节的bigint再算上指针和页头页尾的开销一棵高度为3的B树大概能存2000多万行数据。而B树因为非叶子节点也要存数据同样高度下能承载的数据量会小一两个数量级。说白了B树让“矮胖”成为可能而树越矮查询时访问磁盘的次数就越少。第二B树的数据都集中在叶子节点而且叶子节点之间用链表串了起来。这对范围查询是决定性的优势。比如你要查“id在100到200之间”的所有记录B树只要找到id100那条顺着叶子链表往后扫就行了。B树则不然数据分散在所有节点上范围查询可能要反复在树的不同层级之间跳跃IO次数不可控。第三B树每次查询的路径长度是固定的——都是从根节点走到叶子节点。B树的查询在命中非叶子节点时就能提前结束听起来好像更快但实际上这带来一个问题查询耗时的波动比较大不稳定。对于数据库这种需要稳定延迟的服务来说路径固定反而更容易控制性能。当年有人问过我一个问题为什么不用跳表或者哈希跳表在Redis里用得多但那是内存数据库不需要考虑磁盘IO的局部性哈希索引只能做等值匹配一旦涉及范围查询就全废了。所以站在磁盘存储的角度B树的综合排序是合理的这也是它成为关系型数据库索引默认选择的原因。1.2 聚簇索引、二级索引、回表与覆盖索引InnoDB的索引设计有个核心特征数据本身就是按主键顺序组织的。这个按主键建的索引树叫聚簇索引它的叶子节点存的是整行的数据。而其他索引也就是二级索引叶子节点存的是主键的值。这里就引出了“回表”这个概念。你给name字段建了一个普通索引查询WHERE name 张三的时候MySQL先在二级索引的B树里找到“张三”对应的主键值再拿着这个主键值到聚簇索引里查一次才能拿到完整的数据行。这两步操作就叫回表。回表不是必须的。如果查询所需的列已经在二级索引里全部包含了MySQL就可以直接返回二级索引中的数据不碰聚簇索引。这种情况叫覆盖索引。我写过一条很典型的慢查询表里有几十万条记录查询条件用的字段上有索引但因为SELECT *把整行都查出来了每条记录都要回表结果慢得离谱。后来把SELECT *改成了只查索引里有的那两个字段直接从几秒降到了几十毫秒。这个优化思路就是覆盖索引。1.3 最左前缀原则以及它背后的优化器逻辑联合索引的最左前缀原则是面试里出现频率极高的问题。很多人能背出结论联合索引(a, b, c)能匹配(a)、(a, b)、(a, b, c)但用不上(b)或者(c)。但“为什么”才是关键。联合索引在B树里的排序规则是先按第一个字段排第一个字段相同的再按第二个字段排依此类推。所以当你跳过第一个字段直接拿第二个字段去查B树的排序规则就帮不上忙了——因为你无法利用树的顺序性去快速定位只能全表扫描。这就好比你有一本按“姓氏名字”排序的电话簿想找所有名字叫“伟”的人是没有办法用目录直接定位的因为目录只按姓氏组织。有一个实际工作中的易错点WHERE a 1 AND c 3这样的查询虽然用到了联合索引的a但c那一维是无法走索引下推的除非用了MySQL 5.6引入的索引下推优化这个后面细说。很多新手以为“只要查询条件里有索引的第一个字段整个查询就能用到索引”这是不对的。准确的说法是能用到索引中连续的前缀部分。还有一点需要特别提一句——把范围查询的字段放在联合索引的哪个位置对索引的可用性影响很大。WHERE a 1 AND b 2这种情况a走索引没问题但b就没办法参与索引匹配了因为a的范围条件打断了b的排序连续性。所以建联合索引的时候要把等值查询的字段放在前面范围查询的字段放在后面。这个经验面试官也爱问工作中能省很多事。2. 事务四大特性与隔离级别最容易背串的模块事务这块的八股文密度很高ACID四个字母谁都能说出来但“这四个特性到底是由哪个机制保证的”大部分人模棱两可。2.1 ACID每个字母分别靠什么支撑原子性Atomicity靠的是undo log。事务执行过程中所有对数据的修改都会记录反向操作到undo log里。如果事务中途失败或者你手动回滚MySQL就根据undo log把数据恢复成执行前的样子。注意这个恢复过程不是简单的“改回去”而是通过逻辑日志反向执行细节后面聊日志的时候再展开。一致性Consistency在MySQL里是一个“结果属性”不是某个单一机制能搞定的。它需要原子性、隔离性、持久性共同配合再加上数据库自身的约束比如主键唯一、外键、非空最后从应用层保证业务逻辑正确。所以才有人说一致性不是数据库“做出来的”而是数据库“保证其他特性之后自然达到的”。隔离性Isolation由两套机制协同锁和MVCC。锁负责保证并发写之间的互斥MVCC负责在读多写少的场景下降低锁竞争。这块是重点后面单独开一节细聊。持久性Durability靠的是redo log。事务提交时数据页可能还没来得及刷到磁盘但redo log一定已经写成功了。这样即使数据库瞬间宕机重启后也能根据redo log把已提交的事务恢复出来做到不丢数据。2.2 四种隔离级别以及它们各自拦住了什么SQL标准定义了四个隔离级别读未提交Read Uncommitted、读已提交Read Committed、可重复读Repeatable Read、串行化Serializable。它们分别解决或遗留三个并发问题脏读、不可重复读、幻读。脏读事务A读到了事务B还没提交的数据。如果B回滚了A读到的就是“不存在”的数据。不可重复读同一个事务里两次相同的查询返回了不同的值。原因是另一个事务在两次查询之间做了提交。幻读同一个事务里两次范围查询返回了不同数量的行。原因是另一个事务插入了新行。把这四个隔离级别和三个问题放在一起看会清晰很多隔离级别脏读不可重复读幻读读未提交可能可能可能读已提交不会可能可能可重复读不会不会可能InnoDB的RR已规避串行化不会不会不会MySQL的InnoDB引擎默认隔离级别是可重复读这本身就和SQL标准有点差异。标准里RR是允许幻读的但InnoDB通过间隙锁和MVCC把RR级别下的幻读问题也解决了。很多人在这里会把“标准定义”和“MySQL实现”搞混面试时建议先按标准讲然后补一句“在InnoDB的RR隔离级别下幻读基本不会发生”这样既严谨又能体现你区分了通用标准和具体实现。2.3 可重复读为什么在InnoDB里能防住幻读以及生产环境的真实选择MVCC是理解隔离级别的钥匙。InnoDB里的每行记录其实有两个隐藏字段事务ID和回滚指针。每次事务开始时会生成一个读视图ReadView里面记录了当前活跃的事务ID列表。在可重复读级别下这个ReadView是事务第一次查询时建立的之后整个事务的所有普通查询都复用同一个ReadView。所以其他事务后面提交了新数据当前事务也看不见这就保证了可重复读。那幻读是怎么被防住的分两种情况快照读普通的SELECT依靠ReadView天然看不到新插入的行当前读SELECT ... FOR UPDATE、UPDATE、DELETE则走锁机制在RR级别下会对扫描范围加上间隙锁把其他事务的插入操作直接阻塞掉。两条路径各有各的方案这也是为什么很多有经验的DBA对“RR是否防幻读”这件事会回答“要看读的方式”。生产环境的选择上很多团队会主动把隔离级别改成读已提交。原因之一是RC级别下间隙锁基本不生效死锁概率会降低原因之二是binlog在statement格式下RC配合row格式更稳妥。但这不意味着RC比RR好——如果你的业务需要长时间运行的一致性读比如账务核对RR的快照读特征是很有价值的。关键是理解两者的机制而不是盲目照搬别人的配置。3. 锁机制全局锁、表锁、行锁以及真正难以察觉的间隙锁锁是MySQL并发控制的执行层八股文里最常考的是“锁的粒度”和“死锁的处理”但实际工作中很多人会栽在“当前读到底加了哪些锁”这个问题上。3.1 从全局锁到行锁MySQL的锁分了三层防线全局锁是最狠的一条FLUSH TABLES WITH READ LOCK就把整个库变成只读状态。日常运维里全库逻辑备份需要一致性视图以前会用到这个但现在有了可重复读隔离级别加持单机备份可以用mysqldump --single-transaction在线DDL工具也能避免长时间锁全表。所以全局锁现在更多出现在“极端维护窗口”里而不是常规操作。表级锁分两种表锁和元数据锁MDL。表锁是显式锁LOCK TABLES ... READ/WRITE现在业务代码里基本没人用。MDL则不一样它是MySQL自动加的——任何一条SQL在执行前都会自动对涉及的表加MDL读锁DDL语句则需要MDL写锁。MDL写锁和读锁互斥所以一条ALTER TABLE可能把后续所有普通查询都堵住这就是经典的生产事故“DDL堵死读流量”的根源。我见过最典型的场景半夜有人在业务高峰期跑了一个大表的ALTER TABLE结果一连串的查询全部堆积在“Waiting for table metadata lock”看起来像数据库挂了其实是被一把看不见的“元数据锁”卡住了。行锁是InnoDB的看家本领又分为共享锁S锁和排他锁X锁。S锁之间可以共存X锁与任何锁都互斥。另外还有意向锁——它不锁具体的行而是标记“这张表上有没有行被加锁”目的是让表锁和行锁在检查时能快速冲突判断不用逐行遍历。3.2 行锁的加锁规则间隙锁和死锁的现场排查行锁在InnoDB里并不是只锁“命中的那一行”。在RR隔离级别下一个UPDATE语句扫描过的范围如果范围里有间隙MySQL就会加上间隙锁目的是防止幻读。间隙锁锁的是“两条记录之间的空隙”不是记录本身。比如表里有id为1和5两条记录你执行WHERE id BETWEEN 2 AND 4 FOR UPDATE这区间没有实际记录但依然会被锁住其他事务想插入id3就会阻塞。这里有一个非常有意思的坑间隙锁可能让两个事务互相等待造成死锁。我举个具体场景事务A执行SELECT * FROM t WHERE id 5 FOR UPDATE如果id5的记录不存在它会锁住5附近的间隙事务B也想插入id5它也需要先获取插入意向锁需要和A的间隙锁互斥。假设两个事务分别锁了不同的间隙然后互相想往对方的间隙里插入数据死锁就出现了。遇到死锁时InnoDB会自动检测并回滚其中一个代价较小的事务但那个被回滚的客户端会收到“Deadlock found when trying to get lock”的错误。处理思路有三个让加锁顺序全局一致缩小事务范围以及把隔离级别从RR调成RC来减少间隙锁的出现。3.3 一条UPDATE到底锁了多少行用数据说话有一次我们排查线上慢事务发现一条简单的UPDATE ... WHERE status 0把大量插入操作全堵住了。原因就是status字段没有索引这条UPDATE需要全表扫描InnoDB在RR级别下会对扫描过程中碰到的主键记录加上锁扫描过的间隙也会被间隙锁覆盖。也就是说一条看起来只改了几行的UPDATE实际可能把整个表的写入都掐住了。结论很简单也很反直觉UPDATE的锁范围取决于扫描范围而不是命中范围。只要扫描路径上碰到一行这一行以及它旁边的间隙都会被锁。所以给WHERE条件涉及的字段建合适的索引不只是为了查询快更是为了把锁的范围缩到最小。这也是为什么DBA总是强调“生产库的UPDATE/DELETE必须先看执行计划”。4. 日志系统redo log、undo log、binlog以及两阶段提交的巧劲日志是MySQL里最不像“八股”的八股。你理解了日志就理解了MySQL为什么在宕机之后还能不丢数据也理解了主从复制为什么能工作。4.1 redo log的WAL机制先写日志再改数据redo log的存在逻辑是直接改磁盘数据页太慢了。如果每次事务提交都强制把对应的数据页刷到磁盘性能会低到没法用。于是MySQL采用了WALWrite-Ahead Logging策略事务提交时先把修改记录写到redo log里并保证redo log落盘而数据页的修改可以留在内存缓冲池中之后再慢慢刷回磁盘。这个思路的本质是“把随机IO变成顺序IO”。数据页的修改是分散在各个位置的属于随机写很慢而redo log是追加写入的属于顺序写很快。万一数据库突然宕机内存里的数据页还没来得及刷盘redo log里已经记录了一切。重启后MySQL会扫描redo log把那些还没刷盘的操作重新执行一遍这就是崩溃恢复Crash Recovery。redo log不是无限的它采用循环写的方式空间用完后就从头覆盖。这里有一个“checkpoint”的概念系统会在合适时机把内存中的脏页主动刷盘并把redo log的检查点位置向前推进。如果redo log满了且脏页还没刷完MySQL会强制执行刷脏这时候更新操作会被阻塞。这个行为平时不会触发但如果你遇到过“更新突然卡住”可以去查innodb_io_capacity和刷脏策略的配置。4.2 undo log与一致性读为什么你删了数据快照还能读到旧值undo log在事务部分已经提过它的核心职责是保存“数据在被修改之前的样子”因此可以做两件事事务回滚和MVCC快照读。回滚很好理解就不展开了。重点说MVCC一行记录被修改多次后会形成一个版本链每个版本的“以前的样子”都存在undo log里。事务执行快照读时会根据事务ID判断哪个版本是“自己可见的”然后沿着版本链找到那个版本。这就是为什么其他事务删掉了一行你的事务还能通过快照读到它——你读的不是当前的物理数据而是undo log里保留的旧版本。有一个实际运维里的常见坑一个超长事务迟迟不结束会导致它最早创建的ReadView一直存活这时历史版本无法被purge线程清理undo log会不断膨胀表空间可能被撑大。所以线上要留意长事务不只是因为它会积累锁还因为它会让undo log无法回收。4.3 binlog和redo log的区别以及两阶段提交为什么是必要的binlog和redo log容易混这里用一张表讲清楚对比项redo logbinlog所属引擎InnoDB存储引擎层MySQL Server层记录内容物理修改“第几页第几行改成什么”逻辑修改“执行了什么SQL/行了什么变更”写入时机事务执行过程中持续写事务提交时一次性写用途崩溃恢复、保证持久性主从复制、数据恢复两个日志如果不做协调崩溃恢复就可能出问题事务先写了binlog但redo log没提交或反过来主库恢复后的数据和从库不一致。为了解决这个问题MySQL引入两阶段提交事务提交时先写redo log并处于prepare状态然后写binlog最后把redo log改为commit状态。如果崩溃恰好发生在两步之间恢复时MySQL会检查binlog里有没有完整的事务记录——有则提交没有则回滚。这样就保证了主从数据的一致。两阶段提交在八股文里常被问到“为什么不能只用一个日志”答案简练一点说就是redo log只管InnoDB的数据持久化不负责主从间的逻辑传输binlog管逻辑复制但没法做崩溃恢复时对数据页的物理重放。两者各管一段配合起来才完整。5. 执行计划与索引失效把八股文变成线上排查的实弹八股文的最终价值是你在EXPLAIN输出面前能一眼看出问题。所以这最后一章我按实际排查的顺序把执行计划里最值得看的几列和索引失效的常见场景串一遍。5.1 EXPLAIN里的key、type和Extra怎么看才高效一张表的数据量到几十万之后一条业务查询哪怕只慢0.5秒用户都能感觉到。拿到慢查询日志后第一件事就是EXPLAIN。在我的经验里优先看三列type、key、Extra。type列描述的是访问类型按性能从好到差排列常见顺序是system const eq_ref ref range index ALL。其中ALL是全表扫描一定要避免index是扫描了整棵索引树虽然比ALL好一点但仍不是理想状态range是范围扫描可以接受ref和const是典型的“有索引且正确使用”的样子。线上遇到ALL的查询基本可以直接往索引方向上查。key列展示的是实际用到的索引这里有一个常见误区有时候SQL的WHERE条件里写了索引字段但key却是空的说明索引没被用上。这时候就要看Extra列。Extra列里有几个信息量很大的值Using index表示覆盖索引Using where表示在存储引擎层拿到数据后还要再过滤要注意是不是发生了“索引下推失效”Using index condition是索引下推ICP触发的标志Using filesort是一件需要警惕的事它代表MySQL不得不额外做一次排序操作常见于ORDER BY字段没有按要求走索引的场景。5.2 索引失效的六种场景以及背后的共同原因下面这六种场景几乎涵盖了线上90%的索引失效问题对索引列做了函数运算或表达式计算比如WHERE DATE(create_time) 2024-01-01。原因是索引里存的是原始值MySQL无法用B树的顺序来加速一个被函数转换后的值。隐式类型转换比如索引字段是varchar但查询条件传了数字。MySQL会把字段隐式转成数字再比较实际上相当于对索引列加了函数操作。LIKE前置百分号比如LIKE %关键词。B树的有序性依赖前缀相同从中间开始匹配自然用不上索引。OR条件中有一个字段没索引。优化器为了保证结果的完备性可能直接放弃使用索引改为全表扫描。!或者NOT IN很多时候优化器会认为全表扫描比索引查找代价更小。联合索引不满足最左前缀。这六种场景背后有一个统一的判断逻辑优化器在决定“走索引”还是“全表扫描”时有一个预估代价的模型。很多所谓“索引失效”的案例准确说不是“不能走”而是“走了也不划算”。理解了这一点你在面对“这SQL为什么没走索引”时就不会只局限于背诵列表而是会去算一算回表成本、扫描行数这些变量。5.3 一次压测调优的完整排查链路最后分享一个我实际做过的调优案例把前面的内容串起来。某项目的列表页接口压测到一定并发后响应时间从平均20ms飙升到1.2秒。第一反应是查慢查询日志抓到一条按用户ID和时间范围查询订单的SQL形态是SELECT order_id, amount, status FROM orders WHERE user_id 123 AND created_at BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY created_at DESC LIMIT 20EXPLAIN的结果是typeALLrows预估扫了全表差不多70万行。orders表有联合索引(user_id, created_at)为什么没走继续往下看发现WHERE条件里对created_at做了BETWEEN这确实能走索引但问题在于user_id字段的隐式转换。表里user_id是varchar类型而传入的参数在应用层被拼成了整数于是MySQL对user_id做了类型转换索引失效。修复方案很简单应用层把参数改成严格传字符串同时给联合索引加上排序字段需要的覆盖能力。改完再压测同样的并发下响应时间回到20ms以内EXPLAIN显示typerefExtra里也出现了Using index。这次调优让我印象很深的地方在于问题不是出在索引缺失而是出在“有索引但被隐式转换废掉了”。这类问题的排查能力不是靠背八股能完全覆盖的但八股文里的索引失效场景恰恰给了你快速定位的指南针。MySQL的基础知识远不止这一篇能写完的。索引和事务是地基日志和锁是支撑后面继续聊执行计划优化、主从复制、InnoDB的内存结构这些话题的时候你会发现今天这些内容都是第一块多米诺骨牌。我自己复习下来的体会是不要只背结论每背一个结论都问一句“底层靠什么实现的”然后回来画一遍图或者写个小实验验证一下。能在脑子里把流程跑通一遍比背十遍都管用。