深入解读MySQL InnoDB存储引擎Update语句执行过程 - Cuzzz

参考b站up 戌米的论文笔记 https://www.bilibili.com/video/BV1Tv4y1o7tA/
书籍《mysql是怎样运行的》
极客时间《mysql实战45讲》

系列文章目录和关于我

一丶Mysql整体架构#

MySQL 可以分为 Server 层和存储引擎层两部分

1.Server 层#

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

• 连接器负责跟客户端建立连接、获取权限、维持和管理连接

• 对于查询语句，mysql server层会将查询语句和对应的结果，使用key - value的缓存结构进行缓存，但是一旦发生更新，那么查询缓存就需要失效。因此查询缓存在高版本的mysql中已经被移除

• 分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。

  然后做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

• 优化器是在表里面有多个索引的时候，决定使用哪个索引。或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

• MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

2.存储引擎层#

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。下面我们对比两个常用的存储引擎

以下的分析针对 update t set a='1' where 主键 = 1这条语句

二丶开启事务#

在mysql中，无论用户是否手动开启一个事务，sql都是在一个事务中进行的。我们可以使用start transaction开启一个事务，commit提交事务，rollback回滚事务。

默认情况下，mysql存在自动提交(autocommit=1)，这时候即使我们没有显式开启事务，直接执行update语句，那么mysql会隐式的开启一个事务，并在这条update执行结束后自动提交

如果set autocommit = 0 或者显式开启了一个事务，那么update执行结束后不会自动提交，而是需要手动发起commit 或者rollback

无论式显式事务，还是隐式事务，mysql都会在事务内部第一次执行增删改操作的时候，给事务分配一个事务号

三丶Sql解析，查询计划生成#

mysql服务器层会从连接中读取sql语句，然后进行词法解析，语法解析，查询优化（为什么update语句需要查询优化？不查出来怎么知道修改哪些行数据昵）最终生成一个AST树，这便是物理执行计划，执行器会根据执行计划，调用存储引擎的接口，

四丶查询需要修改的数据#

Mysql InnoDB Buffer Pool

Mysql索引(究极无敌细节版

mysql innodb存储引擎对磁盘的读取是以页为单位的，为了避免每次都从磁盘读取数据，innodb存在buffer pool使用LRU链表维护最近访问到的页，为了更快的从buffer pool中查找到目标页，innodb 还使用表空间号和页号作为key，页作为value，形成Hash表。如果我们目标页已经在buffer pool中那么直接返回目标页，如果不在那么需要进行磁盘io加载目标页到内存，然后缓存到buffer pool中

1.buffer pool是如何维护页在内存中的#

buffer poo中存在三个关键的链表结构

• Free List 空闲链表，链表中将空闲缓冲页的控制块（控制块中记录了缓冲页的位置）进行串联，用于管理未被使用的缓冲池空间
• LRU List，最近最少使用链表，利用LRU算法在buffer pool满后淘汰冷门数据页（innodb 为了应对预读，全表扫描，对应LRU链表进行了改进，分成young区，和old区，解决预读：innodb规定当磁盘某个页面在初次加载到buffer pool中某个缓冲页时，该缓冲页对应的控制块会放在old区域的头部，这样预读到的且后续如果不进行后续访问的页面会逐渐从old区移除，而不影响young区使用频率高的缓冲页。，解决全表扫描：nnodb规定对于某个处于old区的缓冲页第一次访问时，就在其控制块中记录下访问时间，如果后续访问的时间和第一次访问的时间，在某个时间访问间隔内（innodb_old_blocks_time可以进行设置）那么页面不会从old区移动到young区，反之移动到young区中。这个时间间隔默认时1000ms，基本上多次访问同一个页面中的多个记录的时间不会超过1s，解决热门数据经常需要移动到LRU链头部的问题： innodb规定只有被访问的缓冲页位于young区的前1/4范围外，才会进行移动，所以前1/4的高热度的数据，不会频繁移动）
• Flush List，脏链，维护在buffer pool中进行了修改，后续需要刷新到磁盘的缓冲页信息，innodb修改后的页不会立即刷盘，而是使用Flush list记录，后台存在线程定时进行刷脏

2.怎么从16k的页中找到目标数据#

结合B+树索引结构，执行引擎根据页号找到根节点，然后根据根节点中的索引数据进行比较，找到子节点，重复此过程直至找到叶子节点所在的页。

到了叶子节点所在的页后，根据叶子节点页中的Page Dictionary中的槽找到目标记录所在的组，然后遍历这一组中的记录，找到目标记录。如果是范围查询，还需要根据B+树叶子节点间的双向指针继续查找，直到找到不符合要求的记录位置。（为了避免我们在遍历B+树的时候，其他线程修改了B+树的结构，此过程还需要对B+树进行加闩锁）（详细可看 Mysql索引(究极无敌细节版中的InnoDB索引方案一节）

五丶检验锁和加锁#

Mysql 锁

1.Mysql中的锁#

• 元数据锁MDL，mysql服务器层的MDL主要是避免操作数据的同时存在另外线程修改表结构，实现二者的互斥

• innodb表锁

  • 表级S锁，X锁

    使用Lock Tables t Read，innodb存储引擎会对表t加共享锁

    使用Lock tables t write，innodb存储引擎会对表t加独占锁

  • 表级意向锁：

    innodb存储引擎中，当对表中某些记录加S锁之前，会在表上加上一个IS锁，同样加X锁之前会加表级IX锁，这里的I表示意向锁，S or X表示共享还是互斥，表级意向锁存在的目的是后续对表加S锁，X锁的时候，可以快速判断表中是否存在加锁的记录，避免遍历每一个记录查看是否被加锁。

• innodb 行锁

  • Record Lock

  官方名称Lock_REC_NOT_GAP

  记录锁有S锁和X锁，S型记录锁之间可以共享，X型记录锁和S型记录锁，X型记录锁互斥

  • GAP Lock

  innodb的可重复读级别，使用词锁解决幻读问题，前面我们说过，其难点在于，加锁的时候幻影记录还未出现。官方使用Lock_GAP实现如下操作

  此处的gap锁可以反之其他事务在number为8记录前面的间隙插入新的记录，在区间（3，8）内无法进行插入操作，当另外一个事务要插入number为4的记录时，首先需要定位到该条记录的下一条记录，也就是number为8的记录，此时number为8的记录具备gap锁，所以将阻塞插入操作，直到gap锁被释放，其他事务才能进行插入。
  gap锁出现的目的，就是为了防止插入幻影记录，如果对记录上gap锁，并不会限制其他事务对记录加记录锁。

  innodb有两个虚拟的记录Infimum(虚拟最小)，Supermun(虚拟最大)当我们想在(xx,正无穷)范围锁住幻影记录时就可以对Supermun加gap锁。

  • Next-Key Lock

  Next-Key Lock = 记录锁 + gap锁，既锁住记录，也锁住记录之前的间隙

  • Insert Intention Lock#

  插入意向锁，表示事务想在某个间隙插入新的记录，但是当前处于等待状态。

  比如事务A持有（4，8）范围内的gap锁，事务B和C，想插入（4，8）范围内的记录，就会在内存中生成事务B，C对应的插入意向锁，当前事务A释放gap锁的时候，将唤醒事务B和C，事务B和C可以同时获取插入意向锁，然后进行插入。插入意向锁并不会阻止对记录继续上锁。

  为事务生成内存中的锁结构并不是一个0成本的事情，为了节省这个成本，提出隐式锁的概念。

  当一个事务插入语一条记录A，其他事务

  1. select xxx Lock in share mode读取记录A（获取记录A的S锁），或者使用select xxx for update（获取记录A的X锁）
  2. 立即修改记录A（获取x锁）

  对于聚簇索引来说，有一个隐藏列trx_id此列存储着最后更改记录的事务id，在当前事务A插入记录后，便是存储着当前事务A的id，其他事务B企图获取x锁，s锁的时候，就需要下先看一下，trx_id隐藏列对应的事务是否存活，如果不是那么正常获取，反之需要为当前事务A创建一个x锁内存结构，并标记is_waiting为false，然后事务B将为自己创建一个锁结构，is_waiting 为true然后事务B进入等待状态

  对于二级索引来说，其不具备隐藏列trx_id但是在二级索引页面的page header中的page_maxt_trx_id属性，记录了改动页面最大的事务id，如果其属性值小于当前最小的活跃事务id，那么说明对页面的改动事务已经提交，否则需要定位到二级索引记录，然后回表对聚簇索引进行上述聚簇索引的操作。

  一个事务对新插入的记录不需要显示的加锁，由于事务id的存在相当于加了一个隐式锁，别的事务需要加S锁或者X锁的时候，先帮之前的事务生成锁结构，然后为自己生成锁结构，再进入阻塞状态。隐式锁起到了延迟加锁的作用，也许别的事务不会获取于隐式锁冲突的锁，这时候可以减少内存中生成锁结构。

2.一条Update语句涉及的锁#

2.1加共享元数据锁#

为了避免当前事务操作的时候，存在另外的用户对当前表进行DDL操作，mysql首先会为当前操作的表加共享元数据锁。这个过程可能存在阻塞的可能，如果当前事务企图加共享元数据锁的时候，存在另外一个事务正在对表进行DDL操作，这时候另外一个事务上了互斥元数据锁，这时候会出现当前事务阻塞的情况

2.2 加表级意向互斥锁#

此阶段也可能存在阻塞，但是由于innodb支持行锁，基本上很少有人给表上锁。如果执行当前事务之前存在另外一个事务给表上了表记共享锁，表记互斥锁，那么当前操作也会被阻塞。

加表记意向锁的好处在于，若没有意向锁，那么其他事务对表加锁的时候，需要遍历表中所有记录确保当前行中的记录没有被上锁

2.3 行锁#

innodb中的行锁，其实是在内存中，为当前行生成一个锁结构，记录事务id，索引信息，锁信息，锁类型等.如果当前事务加锁的时候，记录并没有加锁，那么会生成一个锁结构存储于内存中。如果锁已经被占用那么会挂起当前事务，直到锁被释放后唤醒当前事务。

六丶修改数据和生成日志#

在成功上锁之后，就可以放心的更新数据了，innodb将写三部分内容

1.写缓冲页#

• 如果修改前后这行数据的大小完全没有发生改变，每一个字段所占用的大小和之前一样，那么进行就地更新
• 但凡存在任何一个字段的大小发生了改变，那么删除旧记录，将旧纪录放入页的垃圾链表中，插入新的记录

不进行需要修改sql中指定的字段，还需要更新trx_id=当前事务的id,roll_pointer = 指向undo log

buffer pool中脏页的刷盘依赖于后台定时任务线程定时进行刷新，如果修改到此为止将存在数据丢失的问题，为此innodb存储引擎还需要写入以下两种日志

2.写undo log#

Mysql InnoDB多版本并发控制MVCC

undo log是为了记录行数据修改前的结果，用于回滚和mvcc。undo log 可以分为两种——记录insert undo log，和 update/delete undo log，生成的undo log会写入到undo log buffer。

• insert undo log 如何帮助回滚刚insert的一行数据

  insert undo log实际上记录了插入行数据的主键，回滚是只需要根据主键进行删除即可

  

• update/delete undo log怎么回滚update/delete的一行数据

  update/delete操作的回滚需要记录操作前数据的完整信息

  

  update/delete undo log中的trx_id,roll_pointer是为了支持mvcc，并且还需要记录修改删除前后的列信息，便于回滚恢复记录

2.1.mvcc#

如图多个版本的数据，在undo log中进行了记录，并且使用roll_pointer,进行串联，形成版本链。快照读查询语句执行前，或者使用start transaction with consistent snapshot（立即生成read view）会生成一个read view（一致性视图，如下）

read view包含如下几个字段

• m_ids:在生成read view时，当前系统中活跃的读写事务id列表
• min_trx_id:生成read view时，当前系统中活跃的读写事务中最小事务id，也就是m_ids中的最小值
• max_trx_id:生成read view时，系统应该分配给下一个事务的事务id值
• creator_trx_id:生成该read view的事务的事务id

2.2如何利用一致性视图判断数据是否可见#

1. 如果被访问版本的trx_id和creator_trx_id相同，意味着当前事务在访问自己修改的记录，自然可见
2. 如果访问版本的trx_id属性值小于read view中的min_trx_id 表明此版本是生成read view之前已经提交的事务，那么自然可见
3. 如果访问版本的trx_id,大于等于read view中的max_trx_id说明，当前版本数据是生成read view后开启事务产生的，那么自然不可见
4. 如果访问版本的trx_id 介于min_trx_id和max_trx_id之间，需要判断trx_id是否位于m_ids列表中，如果在说明创建read view时生成该版本的事务还是活跃的，那么该版本，不可被访问，如果不在说明创建read view 时生成该版本的事务已经提交，可以被访问到

2.3Read Committed和 Repeatable Read的不同#

• Read Committed——每次读取数据前都生成一个Read View

  这样可以保证生成Read view 中的m_ids是实时活跃事务id集合，也许第一次读取的时候事务A没提交，其id位于m_ids中，但是第二次读取的时候事务A提交了，事务A将不位于m_ids中，这样在第二次读取的时候，通过m_ids判断事务A是否提交的时候，可以得到事务A已经提交了，然后让事务A版本产生的数据可见（见2.2.4中的内容）。

• Repeatable Read——如果使用begin开启事务那么在第一次查询的时候生成Read view，如果使用start transaction with consistent snapshot那么执行的时候就会生成read view

  这样可以保证当前事务从头到尾都是read view中记录的内容是一致的，第一次读取的时候事务A没有提交，那么不可见，但是第二次读取的时候事务A提交了，但是read view的m_ids 和max_trx_id可以判断事务A不可见，比如事务A事务id小于max_trx_id意味着生成read view是事务A启动但是没提交，即使第二次读事务A提交了，但是m_ids中还是包含事务A，那么不可见。如果事务A事务id大于max_trx_id，那么自然第二次还是大于max_trx_id，也是不可见的，从而实现了可重复读。

3.写redo log#

Mysql InnoDB Redo log

redo log 记录事务修改了哪个表空间（space id属性），哪个页（page number 属性），修改后的值（data属性）

即使是非常简单的一条变更sql，往往涉及到多出的改动，比如需改sql数据的字符数发生了变更，需要先删除，后插入。并且需要对上一条行记录的next_record 属性进行修改，页中行数据的修改，往往同样需要修改page header，page dictionary等内容，并且可能伴随着B+树节点分裂和合并。为了解决存在多种不同修改的问题，innodb存在多种类型的redo log。

3.1 mini-transaction#

innodb 把一次变更分为多个mini-transaction（MTR）一个MTR包含一组redo log，这一组redo log以一个特殊类型的redo log作为类型，恢复的时候，这一组redo log具备原子性，只有检测到特殊类型的redo log才任何一组redo log是完整的才会进行恢复（B+树叶子节点的分裂，不能说分裂一半）

3.2 log buffer#

生成redo log，会写入到log buffer，log buffer是一块连续的内存空间，由一个个大小为512B的log block组成，默认16mb大小。生成的redo log会找最小的一个redo log block 顺序写入

• buf_next_to_write 标记redo log已经落盘的位置

• buf_free 是标记buffer pool 剩下的空闲空间

3.3 redo log 刷盘的时机：#

1. log buffer 空间低于50%
2. 后台线程周期性刷盘
3. mysql服务正常关闭
4. 做checkpoint

3.4 redo log 进行崩溃恢复#

从checkpoint_lsn位置开始读取redo log，来恢复脏页和undo log，然后通过undo log把所有未提交的事务的脏页进行回滚

七丶本地提交#

提交阶段 innodb存储引擎需要落盘redo log，mysql服务器层需要落盘binlog

1.binlog#

二进制逻辑日志，在逻辑备份和主备复制中发挥重要作用，具备三种格式

• statement

  每一条会修改数据的 SQL 都会记录在 binlog 中。

  Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。

  但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就获取到另外一个结果了。

  所以使用 Statement 格式会出现一些数据一致性问题。

• Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。

  Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

  不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题

• mixed

  Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。

  Row 格式的日志内容会非常清楚的记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。

  不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题

2.怎么保证binlog 和redo log状态一致#

mysql采用了内部XA事务的机制保证binlog，和redo log的状态顺序一致，通过两阶段提交的方式实现，两阶段提交存在一个协调者和多个参与者，在mysql中binlog是协调者，redo log是参与者

2.1mysql的两阶段提交#

1. prepare阶段
   • innodb刷redo log到磁盘，redo log刷盘完成后，修改事务状态为TRX_PREPARED
   • prepare如果失败，那么事务会回滚，而prepare成功那么进入commit阶段
2. commit阶段
   • mysql服务器层写入binlog，写入完成后，修改事务状态为TRX_NOT_STARTED,表示事务已经成功提交

2.2宕机的处理#

• 事务转换若为TRX_ACTIVE那么回滚事务
• 事务状态为TRX_NOT_STARTED 那么说明redo log 和binlog都成功落盘，这时候任务事务已经提交
• 恢复的时候如果发现事务状态为TRX_PREPARED,根据binlog的状态判断是提交还是回滚。
  • 若binlog 写入失败，那么回滚
  • 若binlog写入成功那么提交并修改事务为TRX_NOT_STARTED

3.redo log 和binlog 物理落盘策略#

3.1 innodb_flush_log_at_trx_commit空置redo log的落盘#

• 0表示每秒进行一次刷新
• 1表示每次事务提交时落盘
• 2表示每次事务提交都只写redolog缓冲写道操作系统缓存中，由操作系统决定何时刷盘

3.2 sync_binlog控制binlog的落盘#

• 0 表示当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
• n表示当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
• 1表示每次事务提交都刷盘

八丶主备复制#

主库写入binlog之后，备库的io线程会读取主库的binlog，并转存为本地的中继日志relay log，备库上的sql线程读取relay log并在本地执行

1.主备复制的策略#

• 异步复制：主库写完binlog后即可返回提交成功，无需等待备库响应
• 半同步复制：主库接受指定数量的备机转储relay log成功的ACK后可返回提交成功（还支持超时时间，超时没有返回那么主库返回成功）
• 同步复制：主库等备库回放relay log执行完，事务之后才能返回提交成功

不同的策略，其性能和一致性要求不同，也影响到主库能否返回

九丶返回提交成功#

至此mysql会给客户端返回成功

十丶脏页刷盘#

innodb后台有专门的线程负责将buffer pool中的脏页刷新到磁盘

• 从LRU链表中的冷数据刷新一部分页面到磁盘

  后台线程定时从LRU链表尾部扫描一些页面，扫描的页面数量可以通过innodb_lru_scan_depth指定，如果在LRU中发现脏页，那么刷新到磁盘

• 从flush链表刷新一部分页面到磁盘

  后台线程也会定时从flush链表中刷新一部分页面到磁盘，刷新速率取决于系统是否繁忙

如果后台线程刷新的很慢，且有新的页面需要进行缓存，这时候会从LRU链表尾部看看是否有可以直接释放的非脏页，如果不存在那么需要刷盘然后缓存新的页。