一条SQL更新句子是如何执行的？

什么是InnoDB页？缓存页又是什么？为什么这么规划？
什么是表空间？不同存储引擎的表在文件体系的底层表明上有什么差异？
BufferPool是什么？为什么需求？有哪些咱们需求把握的细节？
MySQL的三种日志文件redo日志、undo日志、binlog分别是什么？为什么需求这么多种类型的日志？
正文开端！
之前咱们讲过了一条SQL查询句子是怎么履行的，那么刺进（INSERT）、更新（UPDATE）和删去（DELETE）操作的流程又是什么姿态呢？
其实关于MySQL而言，只要两种通常意义的操作，一种是Query（查询），另一种是Update（更新），后者包括了咱们往常运用的INSERT、UPDATE和DELETE操作。
那么MySQL的更新流程和查询流程有什么差异呢？
其实基本的流程是一致的，也要通过处理衔接、解析优化、存储引擎几个进程。首要差异在更新操作触及到了MySQL更多的细节。
注：咱们接下来的一切描绘，针对的都是InnoDB存储引擎，假设触及到其他存储引擎，将会特别说明

1.一些需求知道的概念
关于MySQL任何存储引擎来说，数据都是存储在磁盘中的，存储引擎要操作数据，有必要先把磁盘中的数据加载到内存中才干够。
那么问题来了，一次性从磁盘中加载多少数据到内存中适宜呢？当获取记载时，InnoDB存储引擎需求一条条地把记载从磁盘中读取出来吗？
当然不可！咱们知道磁盘的读写速度和内存读写速度差了几个数量级，假设咱们需求读取的数据刚好运行在磁盘的不同方位，那就意味着会产生屡次I/O操作。
因而，无论是操作体系也好，MySQL存储引擎也罢，都有一个预读取的概念。概念的依据便是控制计算机界的局部性原理。
空间局部性：假设当时数据是正在被运用的，那么与该数据空间地址接近的其他数据在未来有更大的或许性被运用到，因而能够优先加载到寄存器或主存中进步功率
便是当磁盘上的一块数据被读取的时分，咱们干脆多读一点，而不是用多少读多少。
1.1InnoDB页
InnoDB存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的最小单位。InnoDB中页的巨细默以为16KB。也便是默许状况下，一次最少从磁盘中读取16KB的数据到内存中，一次最少把内存中16KB的内容刷新到磁盘上。
关于InnoDB存储引擎而言，一切的数据（存储用户数据的索引、各种元数据、体系数据）都是以页的方式进行存储的。
1.2表空间
为了更好地办理页，MySQL又规划了「表空间」的概念。表空间又有许多类型，详细类型咱们不需求知道，咱们只需求知道，一个表空间能够划分红许多个InnoDB页，InnoDB表数据都存储在某个表空间的页中。
为了便利咱们定位，MySQL贴心肠为表空间规划了一个仅有标识——表空间ID（spaceID）。同理，InnoDB页也有自己的仅有编号——页号（pagenumber）。
因而，咱们能够这么以为。给定表空间ID和页号以及页的偏移量，咱们就能够定位到InnoDB页的某条记载，也便是数据库表的某条记载。
1.2.1数据表在文件体系中的表明
为了更好地让大家了解这个笼统的概念，我创立了名为test的数据库，在其下分别创立了3张表t_user_innodb，t_user_myisam，t_user_memory，对应的存储引擎分别为InnoDB、MyISAM、MEMORY。
进入MySQL的数据目录，找到test目录，看一下test数据库下一切表对应的本地文件目录
drwxr-x—2mysqlmysql4096Jan2609:28.
drwxrwxrwt6mysqlmysql4096Jan2609:24..
-rw-r—–1mysqlmysql67Jan2609:24db.opt
-rw-r—–1mysqlmysql8556Jan2609:28t_user_innodb.frm
-rw-r—–1mysqlmysql98304Jan2609:28t_user_innodb.ibd
-rw-r—–1mysqlmysql8556Jan2609:27t_user_memory.frm
-rw-r—–1mysqlmysql0Jan2609:28t_user_myisam.MYD
-rw-r—–1mysqlmysql1024Jan2609:28t_user_myisam.MYI
-rw-r—–1mysqlmysql8556Jan2609:28t_user_myisam.frm
1.2.2InnoDB是怎么存储表数据的
「表空间」是InnoDB存储引擎独有的概念。
咱们看到t_user_innodb表在数据库对应的test目录下会生成以下两个文件
t_user_innodb.frm
t_user_innodb.ibd
其间，t_user_innodb.ibd便是t_user_innodb表对应的表空间在文件体系上的表明；t_user_innodb.frm用来描绘表的结构，如表有哪些列，列的类型是什么等。
1.2.3MyISAM是怎么存储表数据的
和InnoDB不同，MyISAM没有表空间的概念，表的数据和索引全都直接存放在对应的数据库子目录下，能够看到t_user_myisam对应了三个文件
t_user_myisam.MYD
t_user_myisam.MYI
t_user_myisam.frm
其间，t_user_myisam.MYD表明表的数据文件，也便是咱们实践看到的数据表的内容；t_user_myisam.MYI表明表的索引文件，为该表创立的索引都会存放在这个文件中；t_user_myisam.frm用来描绘表的结构。
1.2.4MEMORY是怎么存储表数据的
MEMORY存储引擎对应的数据表只要一个描绘表结构的文件t_user_memory.frm。
2.缓冲池BufferPool
为了更好的利用局部性原理带给咱们的优势，InnoDB在处理客户端请求时，假设需求访问某个页的数据，会把该数据所在的页的全部数据加载到内存中。哪怕是只需求访问一个页中的一条数据，也需求加载整个页。
从磁盘中加载数据到内存中的操作太昂贵了！有什么方法能够进步数据操作的功率呢？缓存！
为了缓存磁盘的页，InnoDB在MySQL服务器发动时会向操作体系申请一片接连的内存区域，这片内存区域便是BufferPool。
很容易了解，为了更好地缓存页数据，BufferPool对应的一片接连内存空间也被划分为若干个页，而且默许状况下，BufferPool页的巨细和InnoDB页巨细相同，都是16KB。为了区分两种不同的页，咱们将BufferPool中的页面称为缓冲页。
读取数据的时分，InnoDB先判别数据是否在BufferPool中，假设是，则直接读取数据进行操作，不用再次从磁盘加载；假设不是，则从磁盘加载到BufferPool中，然后读取数据进行操作。
修正数据的时分，也是将数据先写到BufferPool缓冲页中，而不是每次更新操作都直接写入磁盘。当缓冲页中的数据和磁盘文件不一致的时分，缓冲页被称为脏页。
那么脏页是什么时分被同步到磁盘呢？
InnoDB中有专门的后台线程每隔一段时刻会把脏页的多个修正刷新到磁盘上，这个动作叫做「刷脏」。
3.redo日志
3.1为什么需求redo日志
不定时刷脏又带来一个问题。假设脏页的数据还没有刷新到磁盘上，此时数据库突然宕机或重启，这些数据就会丢掉。
首要想到的最简略粗犷的处理计划便是在业务提交之前，把该业务修正的一切页面都刷新到磁盘。可是上文说过，页是内存和磁盘交互的最小单位，假设只修正了1个字节，却要刷新16KB的数据到磁盘上，不得不说太浪费了，此路不通！
所以，有必要要有一个耐久化的措施。
为了处理这个问题，InnoDB把对一切页的更新操作（再强调一遍，包括INSERT、UPDATE、DELETE）专门写入一个日志文件。
当有未同步到磁盘中的数据时，数据库在发动的时分，会依据这个日志文件进行数据康复。咱们常说的关系型数据库的ACID特性中的D（耐久性），便是通过这个日志来完结的。
这个日志文件便是大名鼎鼎的redo日志。
「re」在英文中的词根意义是“从头”，redo便是「从头做」的意思，顾名思义便是MySQL依据这个日志文件从头进行操作
这就呈现了一个有意思的问题，刷新磁盘和写redo日志都是进行磁盘操作，为什么不直接把数据刷新到磁盘中呢？
3.2磁道寻址
咱们需求略微了解一下磁道寻址的进程。磁盘的构造如下图所示。
每个硬盘都有若干个盘片，上图的硬盘有4个盘片。
每个盘片的盘面上有一圈圈的同心圆，叫做「磁道」。
从圆心向外画直线，能够将磁道划分为若干个弧段，每个磁道上一个弧段被称之为一个「扇区」（右上图白色部分）。数据是保存在扇区傍边的，扇区是硬盘读写的最小单元，假设要读写数据，有必要找到对应的扇区，这个进程叫做「寻址」。
3.2.1随机I/O
假设咱们需求的数据是随机涣散在磁盘上不同盘片的不同扇区中，那么找到相应的数据需求等到磁臂旋转到指定的盘片然后继续寻觅对应的扇区，才干找到咱们所需求的一块数据，继续进行此进程直到找完一切数据，这个便是随机I/O，读取数据速度非常慢。
3.2.2次序I/O
假设咱们现已找到了榜首块数据，并且其他所需的数据就在这一块数据之后，那么就不需求从头寻址，能够依次拿到咱们所需的数据，这个就叫次序I/O。
现在答复之前的问题。由于刷脏是随机I/O，而记载日志是次序I/O（接连写的），次序I/O功率更高，本质上是数据会集存储和涣散存储的差异。因而先把修正写入日志文件，在确保了内存数据的安全性的状况下，能够推迟刷盘时机，进而提升体系吞吐。
3.3redo日志的体系变量
redo日志坐落MySQL数据目录下，默许有ib_logfile0和ib_logfile1两个文件，如下图所示。
能够发现，两个redo日志文件的巨细都是50331648，默许48MB。为什么这个巨细是固定的呢？由于假设咱们要运用次序I/O，就有必要在申请磁盘空间的时分一次性决议申请的空间巨细，这样才干确保申请的磁盘空间在地址上的接连性。
这也就决议了redo日志的旧数据会被掩盖，一旦文件被写满，就会触发BufferPool脏页到磁盘的同步，以腾出额外空间记载后面的修正。
能够通过以下指令检查redo日志的体系特点。
mysql>showvariableslike’innodb_log%’;
+—————————–+———-+
|Variable_name|Value|
+—————————–+———-+
|innodb_log_buffer_size|16777216|
|innodb_log_checksums|ON|
|innodb_log_compressed_pages|ON|
|innodb_log_file_size|50331648|
|innodb_log_files_in_group|2|
|innodb_log_group_home_dir|./|
|innodb_log_write_ahead_size|8192|
+—————————–+———-+
参数称号意义innodb_log_file_size指定每个redo日志文件的巨细，默许48MBinnodb_log_files_in_group指定redo日志文件的数量，默许2innodb_log_group_home_dir指定redo文件的路径，假设不指定，则默以为datadir目录
介绍到这儿，读者朋友能够发现，咱们方才探究的是怎么让现已提交的业务坚持耐久化，可是假设某些业务偏偏在履行到一半的时分呈现问题怎么办？
业务的原子性要求业务中的一切操作要么都成功，要么都失利，不允许存在中间状况。就好比我在写这篇文章的时分，会时不时地敲一下ctrl+Z回来到上一步或者过去好几步之前的状况，MySQL也需求“留一手”，把业务回滚时需求的东西都记载下来。
比方，刺进数据的时分，至少应该把新增的这条记载的主键的值记载下来，这样回滚的时分只要把这个主键值对应的记载删去就能够了。
MySQL又一个鼎鼎大名的日志——undo日志，正式登场！
4.undo日志
undolog（吊销日志或回滚日志）记载了业务发生之前的数据状况，分为insertundolog和updateundolog。
假设修正数据时呈现异常，能够用undolog来完结回滚操作（坚持原子性）。能够了解为undo日志记载的是反向的操作，比方INSERT操作会记载DELETE，UPDATE会记载UPDATE之前的值，和redo日志记载在哪个物理页面做了什么操作不同，所以这是一种逻辑格局的日志。
undo日志和redo日志与业务密切相关，被统称为「业务日志」。
关于undo日志，咱们目前只需求了解这么多即可
5.SQL更新句子的履行总结——初版
有了业务日志之后，咱们来简略总结一下更新操作的流程，这是一个简化的进程。
name原值是chanmufeng。
updatet_user_innodbsetname=’chanmufeng1994’whereid=1;
业务开端，从内存（BufferPool）或磁盘取到包括这条数据的数据页，回来给Server的履行器；
Server的履行器修正数据页的这一行数据的值为chanmufeng1994；
记载name=chanmufeng到undolog；
记载name=chanmufeng1994到redolog；
调用存储引擎接口，记载数据页到BufferPool（修正name=penyuyan）；
业务提交。
6.binlog日志
之前咱们讲过，从MySQL整体架构来看，其实能够分红两部分
Server层，它首要做的是MySQL功用层面的事情，比方处理衔接、解析优化等；
存储引擎层，担任存储相关的详细事宜。
redo日志是InnoDB存储引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志），它能够被一切存储引擎运用。
6.1为什么有了redo日志还需求binlog？
我想你或许会问出这个问题，实践上，更准确的问法是为什么有了binlog还需求有redo日志？首要有以下几个原因。
由于最开端MySQL里并没有InnoDB存储引擎。MySQL自带的引擎是MyISAM，可是MyISAM没有崩溃康复的能力，InnoDB后来以插件的方式被引入，顺便带来了redo日志；
binlog日志是用来归档的，binlog以事件的方式记载了一切的DDL和DML句子（由于它记载的是操作而不是数据值，归于逻辑日志），可是不具备宕机康复的功用，由于或许没有来得及刷新脏页，形成脏页数据的丢掉，而这些操作也没有保存到binlog中然后形成数据丢掉；
binlog记载的是关于一个业务的详细操作内容，即该日志是逻辑日志。而redo日志记载的是关于每个页的更改的物理状况。功用压根不是一回事儿。
6.2binlog日志的作用
6.2.1主从复制
binlog是完结MySQL主从复制功用的核心组件。
master节点会将一切的写操作记载到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当时所在的slave节点。
6.2.2数据康复
假设你在阅览这篇文章的时分觉得我写得真实太好，拍案叫绝的时分一不小心把公司的数据库给删了，你该怎么做才干康复到你删库之前的那个时刻的状况？
这个时分就要用到binlog了，条件是binlog没有被删去，不然，神仙也救不了你了。
通常状况下，公司会定时对数据库进行全量备份，或许隔一个月，一周，乃至或许每天都备份一次。运气好的话你能够运用前一天的全量备份，康复到前一天的某时刻状况（或者一周、一月之前），然后从全量备份的时刻开端，从binlog中提取该时刻之后（条件是你的binlog里边存放了这段时刻的日志）的一切写操作（当然，你得过滤掉你的删库操作），然后进行操作回放就能够了。
是不是很简略？
问题又来了。再看一眼咱们的更新句子。
updatet_user_innodbsetname=’chanmufeng1994’whereid=1;
假设这条更新句子现已被写入到了redo日志，还没来得及写binlog的时分，MySQL宕机重启了，咱们看一下会发生什么。
由于redo日志能够在重启的时分用于康复数据，所以写入磁盘的是chanmufeng1994。可是binlog里边没有记载这个逻辑日志，所以这时分用binlog去康复数据或者同步到从库，就会呈现数据不一致的状况。
所以在写两个日志的状况下，就类似于「分布式业务」的状况，假设你不清楚分布式业务是个什么东西也不要紧，我在之后的文章会介绍到。能够明确的便是redo日志和binlog日志假设单纯依次进行提交是无法确保两种日志都写成功或者都写失利的。
咱们需求「两阶段提交」。
6.3两阶段提交
两阶段提交不是MySQL的专利，两阶段提交是一种跨体系维持数据逻辑一致性的常见计划，尤其在分布式业务上，所以请读者要点领会思维
咱们把redo日志的提交分红两步，两步中redo日志的状况分别是prepare和commit。进程如下
InnoDB存储引擎将更改更新到内存中后，同时将这个更新操作记载到redo日志里边，此时redo日志处于prepare状况；
履行器生成这个操作的binlog，并将binlog刷盘；
履行器调用InnoDB的提交业务接口，InnoDB把刚刚写入的redo日志改成commit状况。至此，一切操作完结。
加上两阶段提交之后咱们再来看一下SQL更新句子的履行流程。
7.SQL更新句子的履行总结——终版
客户端发送更新命令到MySQL服务器，通过处理衔接、解析优化等进程；
Server层向InnoDB存储引擎要id=1的这条记载；
存储引擎先从缓存中查找这条记载，有的话直接回来，没有则从磁盘加载到缓存中然后回来；
Server层履行器修正这条记载的name字段值；
存储引擎更新修正到内存中；
存储引擎记载redo日志，并将状况设置为prepare状况；
存储引擎告诉履行器，修正完毕，能够进行业务提交；
Server先写了个binlog；
Server提交业务；
存储引擎将redo日志中和当时业务相关的记载状况设置为commit状况。