网站监控系统,做电影网站心得体会,saas系统哪个公司做的最好,泊头市有做网站的吗MySQL3#xff1a;MySQL中一条更新SQL是如何执行的#xff1f; MySQL中一条更新SQL是如何执行的#xff1f;1.Buffer Pool缓冲池2.Redo logredo log作用Redo log文件位置redo log为什么是2个#xff1f; 3.Undo log4.更新过程5.InnoDB官网架构InnoDB架构-内存结构①Buffer … MySQL3MySQL中一条更新SQL是如何执行的 MySQL中一条更新SQL是如何执行的1.Buffer Pool缓冲池2.Redo logredo log作用Redo log文件位置redo log为什么是2个 3.Undo log4.更新过程5.InnoDB官网架构InnoDB架构-内存结构①Buffer Pool内存的缓冲池写满了怎么办预读机制线性预读和随机预读 Buffer Pool List(LRU)官网架构Buffer Pool List冷热分离 为什么有Buffer Pool二次查询相同SQL还是很慢Buffer Pool总结 ②Change Buffer写缓冲③Adaptive Hash Index④Redo Log BufferRedo Log Buffe刷盘机制 总结 InnoDB架构-磁盘结构InnoDB Doublewrite Buffer双写缓冲区 6.后台线程7.Binlog MySQL合集 MySQL中一条更新SQL是如何执行的
讲完了查询流程我们是不是再讲讲更新流程、插入流程和删除流程 在数据库里面我们说的update操作其实包括了更新、插入和删除。如果大家有看 过MyBatis的源码应该知道Executor里面也只有doQuery()和doUpdate()的方法 没有doDelete()和dolnsert()。
更新流程和查询流程有什么不同呢 基本流程也是一致的也就是说它也要经过解析器、优化器的处理最后交给执 行器。区别就在于拿到符合条件的数据之后的操作。
1.Buffer Pool缓冲池
首先对于InnoDB存储引擎来说数据都是放在磁盘上的存储引擎要操作数据, 必须先把磁盘里面的数据加载到内存里面才可以操作。
这里就有个问题是不是我们需要的数据多大我们就一次从磁盘加载多少数据到内存呢比如我要读6个字节磁盘就只返回6字节吗 并不是因为磁盘I/O的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方那就意味着会产生很多次的I/O操作。所以无论是操作系统也好还是存储引擎也好都有一个预读取的概念。也就是 说当磁盘上的一块数据被读取的时候很有可能它附近的位置也会马上被读取到这个就叫做局部性原理。所以它会每次多读取一点而不是用多少读多少。从磁盘读取数据到内存的最小的单位叫做页操作系统的页大小一般是4KB。
InnoDB也有这样的设置在InnoDB里面这个最小的单位默认是16KB大小。如果要修改这个值的大小需要清空数据重新初始化服务。我们要操作的数据就在这样的页里面数据所在的页叫数据页。
这里有一个问题操作数据的时候每次都要从磁盘读取到内存(再返回给Server)有没有什么办法可以提高效率? 还是缓存的思想。把读取过的数据页缓存起来。 InnoDB设计了一个内存的缓冲区读取数据的时候先判断是不是在这个内存区域里面如果是就直接读取然后操作不用再次从磁盘加载。如果不是读取后就写到这个内存的缓冲区。这个内存区域有个专属的名字叫缓冲池Buffer PooL。
修改数据的时候也是先写入到Buffer PooL而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候我们把它叫做脏页。
那脏页什么时候才同步到磁盘呢 InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘每隔一段时间就一次性地把多个修改写入磁盘这个动作就叫做刷脏。
总结Buffer Pool的作用是为了提高读写的效率。
2.Redo log
思考一个问题因为刷脏不是实时的如果Buffer Pool里面的脏页还没有刷入磁盘时也就是说写入磁盘是有一个延时的过程如果这个时候数据库宕机或者重启这些数据就会丢失。那怎么办呢 所以内存的数据必须要有一个持久化的措施。为了避免这个问题InnoDB把所有对页面的修改操作专门写入一个日志文件。如果有未同步到磁盘的数据数据库在启动的时候会从这个日志文件进行恢复操作(实现crash-safe)。我们说的事务的ACID里面D(持久性)就是用它来实现的。这个日志文件就是磁盘的redo log(重做日志)。
那么有个新的疑问现在要写Buffer Pool为了保证Buffer Pool的可用性还要做持久化写redo log那么同样需要写磁盘为什么不直接写到dbfile里面去为什么先写日志再写磁盘是否性能上有差异呢 写日志文件和和写到数据文件有什么区别 我们先说一下磁盘寻址的过程。 上图是磁盘的构造。 磁盘的盘片不停地旋转磁头会在磁盘表面画出一个圆形轨迹这个就叫磁道。从内到位半径不同有很多磁道。然后 又用半径线把磁道分割成了扇区两根射线之内的扇区组成扇面。如果要读写数据必须找到数据对应的扇区这个过程就叫寻址。
如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中那么找到相应的数据需要等到磁臂旋转到指定的页然后盘片寻找到对应的扇区才能找到我们所需要的一块数据依次进行此过程直到找完所有数据这个就是随机IO读取数据速度较慢。
假设我们已经找到了第一块数据并且其他所需的数据就在这一块数据后边那么就不需要重新寻址可以依次拿到我们所需的数据这个就叫顺序IO。
刷盘是随机I/O而记录日志是顺序I/O(连续写的)顺序I/O效率更高本质上是数据集中存储和分散存储的区别。
redo log作用
因此先把修改写入日志文件在保证了内存数据的安全性的情况下可以延迟刷盘时机进而提升系统吞吐量。
redo log是InnoDB存储弓|擎实现的并不是所有存储引擎都有。支持崩溃恢复 是InnoDB的一个特性。redo log不是记录数据页更新之后的状态而是记录的是在某个数据页上做了 什么修改”。属于物理日志。redo log的大小是固定的前面的内容会被覆盖一旦写满就会触发刷盘操作完成buffer pool到磁盘的同步以便腾出空间记录后面的修改因此这个值可以稍微设置大点。
在innodb中除了redo log之外还有一个跟修改有关的日志叫做undo log。redo log和undo log与事务密切相关统称为事务日志。
Redo log文件位置
redo log文件的位置是由datadir参数决定的。一般情况下redo log文件位于数据目录(datadir)下默认由两个文件ib_logfile0和ib_logfile1组成。每个48M。 下面命令可以查看到数据文件存储的位置。
show global variables like datadir%;show variables like innodb_log%;参数含义 innodb_log_buffer_size指定每个文件的大小默认48M innodb_log_file_size指定每个文件的大小默认48M innodb_log_files_in_group指定文件的数量默认为2 innodb_log_group_home_dir指定文件所在路径相对或绝对。如果不指定则为 datadir 路径 innodb_log_write_ahead_size提前写入大小
redo log为什么是2个 redo log的大小是固定的所谓大小固定是指它的结构是一个环状的当新的内容写满了会覆盖旧的内容这个就是大小固定的意思。
它可以通过参数innodb_log_files_in_group来设置它的文件个数可以通过innodb_log_buffer_size来指定每个redo log文件的大小默认是两个文件你设置成4就是4个文件。
所以在不断写的过程中也需要不断地清理redo log的内容如果一致不清理那么当当前写的位置赶上了上一个清理的检查点的位置那么就会触发buffer pool里面的内容刷盘。
3.Undo log
undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态分为insert undo log和update undo logo如果修改数据时出现异常可以用undo log来实现回滚操作(保持原子性)。
undo log记录的是反向的操作比如insert会记录deleteupdate会记录update原来的值所以叫做回滚日志redo log记录在哪个物理页面做了什么操作不同所以叫做逻辑格式的日志
show global variables like %undo%; 参数含义 innodb_max_undo_log_size如果innodb_undo_log_truncate设置为1超过这个大小的时候会触发触发 truncate回收收缩动作如果page大小是16KB, truncate后空间 缩小到10Mo默认1073741824字节二1G。 innodb_undo_directoryundo文件的路径 innodb_undo_log_truncate设置为1即开启在线回收收缩;undo log 0志文件 innodb_undo_logs回滚段的数量默认128这个参数已经过时。 innodb_undo_tablespaces设置undo独立表空间个数范围为0-95默认为00表示不开启独立undo表空间且undo日志存储在ibdata1文件中。这个参数已经过时。
redo log和undo log与事务密切相关统称为事务日志。
4.更新过程
有了这些日志之后我们来总结一下一个更新操作的流程比如将id1001的数据修改name原值是小李现在改为小王
update stu set name 小王 where idl001;以下是一个简化的过程
事务开始从内存buffer pool或磁盘data file取到包含这条数据的数据 页返回给Server的执行器Server的执行器修改数据页的这一行数据的值为 ‘小王’记录name‘小李’ 到undo log保证原子性记录name‘小王’ 到redo log保证持久性调用innodb存储引擎接口写入数据记录数据页到Buffer Pool(修改name ‘小王’)事务提交。
5.InnoDB官网架构
了解了内存的Buffer Pool和磁盘的两个日志我们也从总体上看下InnoDB的架构是什么样的。 下图是MySQL5.7的InnoDB的架构图 MySQL官方文档如何查看MySQL中文文档 Buffer Pool主要分为3个部分 Buffer Pool、Change Buffer、Adaptive Hash Index另外还有一个(redo)log buffer。
左边为In-Memory Structures(内存结构)右边为On-Disk Structures(磁盘结构)。
InnoDB架构-内存结构
①Buffer Pool
Buffer Pool缓存的是页面信息包括数据页、索引页。 Buffer Pool在Linux系统下默认大小是128M(134217728字节)在Windows系统下是8M(8388608字节)可以调整。 査看系统变量
SHOW VARIABLES like %innodb_buffer_pool%‘;查看服务器状态里面有很多跟Buffer Pool相关的信息:
SHOW STATUS LIKE %innodb_buffer_pool%‘;这些参数都可以在官网查到详细的含义用搜索功能。 https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
内存的缓冲池写满了怎么办
InnoDB用LRU算法来管理缓冲(链表实现不是传统的LRU分成了young和old)经过淘汰的数据就是热点数据。
传统LRU可以用Map链表实现。value存的是在链表中的地址。 InnoDB中确实使用了一个双向链表LRU list也叫Buffer Pool List它里面放的不是data page而是指向缓存页的指针。
如果写buffer pool的时候发现没有空闲页了就要从buffer pool中淘汰数据页了它要根据LRU链表的数据来操作。
预读机制
首先InnoDB的数据页并不是都在访问的时候才缓存到buffer pool的。 InnoDB有一个预读机制(read ahead)。也就是说设计者认为访问某个page的数据的时候相邻的一些page可能会很快被访问到所以先把这些page放到buffer pool中缓存起来。 https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-read_ahead.html
线性预读和随机预读
这种预读的机制又分为两种类型
Linear read-ahead——线性预读(异步的) 为了便于管理InnoDB中把64个相邻的page叫做一个extent(区)。如果顺序地访问了一个extent的56个page这个时候InnoDB就会把下一个extent(区)缓存到buffer pool。顺序访问多少个page才缓存下一个extent由innodb_read_ahead_threshold参数控制show variables like %innodb_read_ahead_threshold%;Random read-ahead——随机预读 如果buffer pool已经缓存了同一个extent(区)的数据页的个数超过13时就会把这个extent剩余的所有page全部缓存到buffer pool。随机预读的功能默认是不启用的由innodb_random_read_ahead参数控制:show variables like %innodb_random_read_ahead%;很明显线性预读或者异步预读都能够把可能即将用到的数据提前加载到buffer pool肯定能提升I/O的性能所以是一种非常有用的机制。
但是预读肯定也会带来一些副作用就是导致占用的内存空间更多剩余的空闲页更少。如果说buffer pool size不是很大而预读的数据很多很有可能那些真正的需要被缓存的热点数据被预读的数据挤出buffer pool淘汰掉了下次访问的时候又要先去磁盘。怎么让这些真正的热点数据不受到预读的数据的影响呢 从Buffer Pool List的架构来入手看看它是如何设计的。
Buffer Pool List(LRU)官网架构
Buffer Pool List官网架构图https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html
Buffer Pool List冷热分离
从Buffer Pool List官网架构可以得知InnoDB是把LRU list分成了两部分通过中间的分割线叫做Midpoint insertion(中点插入)也就是对buffer pool做一个冷热分离
new sublist靠近head的叫做new sublist用来放热数据(我们把它叫做热区)old sublist靠近tail的叫做old sublist用来放冷数据(我们把它叫做冷区)
所有新数据加入到buffer pool的时候一律先放到冷数据区的head不管是预读的还是普通的读操作。所以如果有一些预读的数据没有被用到会在old sublist(冷区)直接被淘汰。
放到LRU List以后如果再次被访问就把它移动到热区的head。如果热区的数据长时间没有被访问会被先移动到冷区的head部最后慢慢在tail被淘汰。
在默认情况下热区占了5/8的大小冷区占了3/8这个值由参数innodb_old_blocks_pct控制它代表的是old区的大小默认是37%也就是3/8。
show variables like %innodb_old_blocks_pct%;innodb_old_blocks_pct的值可以调整在5%到95%之间这个值越大new区越小这个LRU算法就接近传统LRU。如果这个值太小old区没有被访问的速度淘汰会更快。
预读的问题通过冷热分离解决了还有没有其他的问题呢 我们先把数据放到冷区用来避免占用热数据的存储空间。但是如果刚加载到冷区的数据立即被访问了一次按照原来的逻辑这个时候我们会马上把它移动至热区。假设这一次加载然后被立即访问的冷区数据量非常大比如我们查询了一张几 千万数据的大表没有使用索引做了一个全表扫描或者dump全表备份数据这种查询属于短时间内访问后面再也不会用到了。
如果短时间之内被访问了一次导致它们全部被移动到热区的head它会导致很多热点数据被移动到冷区甚至被淘汰造成了缓冲池的污染。这个问题我们又怎么解决呢 对于加载到冷区然后被访问的数据设置一个时间窗口只有超过这个时间之后被访问我们才认为它是有效的访问。
InnoDB 里面通过innodb_old_blocks_time这个参数来控制默认是1秒钟。
show variables like %innodb_old_blocks_time%;也就是说1秒钟之内被访问的不算数待在冷区不动。只有1秒钟以后被访问的才从冷区移动到热区的head。这样就可以从很大程度上避免全表扫描或者预读的数据污染真正的热数据。
似乎比较完美了还有没有可以优化的空间呢 InnoDB支持读写并发写不阻塞读(MVCC)。那么为了避免并发的问题对于LRU链表的操作是要加锁的。也就是说每一次链表的移动都会带来资源的竞争和等待。从这个角度来说如果要进一步提升InnoDB LRU的效率就要尽量地减少LRU链表的移动。
比如把热区一个非常靠近head的page移动到head有没有这个必要呢 所以InnoDB对于new区还有一个特殊的优化 如果一个缓存页处于热数据区域且在热数据区域的前1/4区域(注意是热数据区域的1/4不是整个链表的1/4)那么当访问这个缓存页的时候就不用把它移动到热数据区域的头部如果缓存页处于热区的后3/4区域那么当访问这个缓存页的时候会把它移动到热区的头部。
为什么有Buffer Pool二次查询相同SQL还是很慢
InnoDB中有Buffer Pool二次查询相同SQL会优先查询Buffer Pool中的数据但是如果你的查询结果超过了Buffer Pool的大小根据LRU淘汰算法那么一下就会将原来Buffer Pool加载进来的数据全部挤出去尤其是在做全表扫描的时候。
show VARIABLES LIKE %innodb_buffer_pool_size%;可以看到Windows环境下默认buffer pool大小为8388608 bytes也就是8Mold区3Myoung区5M在全表扫描的过程中所有的page都会加入old区的头部。
从page中找到对应行的时候所有的page都会移动到new区的头部因为容量有限前面的数据也全部被淘汰了。因此可以将buffer pool调大再进行测试。
通常来说我们建议一个比较合理的、健康的比例是给buffer pool设置你的机器内存的50%~60%左右 比如你有32GB的机器那么给buffer设置个20GB的内存剩下的留给OS和其他人来用这样比较合理一些。 假设你的机器是128GB的内存那么buffer pool可以设置个80GB左右大概就是这样的一个规则。
Buffer Pool总结
内存缓冲区对于提升读写性能有很大的作用。当需要更新一个数据页时如果数据页在Buffer Pool中存在那么就直接更新好了。否则的话就需要从磁盘加载到内存再对内存的数据页进行操作。也就是说如果没有命中缓冲池至少要产生一次磁盘IO有没有优化的方式呢
②Change Buffer写缓冲
Change Buffer是Buffer Pool的一部分可以大大提升非唯一性索引的增删改效率。 如果这个数据页不是唯一索引不存在数据重复的情况(或者说你的业务允许这些数据重复因此没有使用unique)也就不需要从磁盘加载索引页判断数据是不是重复(唯一性检查)。这种情况下可以先把修改记录在内存的缓冲池中从而提升更新语句(Insert、Delete、Update)的执行速度。
这一块区域就是Change BufferMySQL 5.5之前叫Insert Buffer(插入缓冲)现在也能支持 delete 和 update。最后把Change Buffer记录到数据页的操作叫做merge。
什么时候发生merge分以下几种情况
在访问这个数据页的时候通过后台线程、或者数据库shut downredo log写满时触发
可以通过参数innodb_change_buffer_max_size来查看Change Buffer占Buffer Pool的比例。
show variables like %innodb_change_buffer_max_size%;如果数据库大部分索引都是非唯一索引并且业务是写多读少不会在写数据后立 刻读取就可以使用Change Buffer(写缓冲)。Change Buffer占Buffer Pool的比例默认25%可以调大这个值来扩大Change Buffer的大小以支持写多读少的业务场景。
③Adaptive Hash Index
自适应的hash索引。 索引应该是放在磁盘的为什么要专门把一种哈希的索引放到内存 在InnoDB中不能显式地创建一个哈希索引所谓的InnoDB支持hsh索引指Adaptive Hash Index——自适应的hash索引它是在内存中进行维护的为什么要专门把一种哈希的索引放到内存它是InnoDB自动为buffer pool中的热点页创建的索引这个hash索引是InnoDB去维护的我们不能干涉。
④Redo Log Buffer
Redo log也不是每一次都直接写入磁盘在Buffer Pool里面有一块内存区域(Log Buffer)专门用来保存即)镀写入日志文件的数据默认16M其设计初衷也是一样为了较少磁盘IO。
SHOW VARIABLES LIKE %innodb_log_buffer_size%;需要注意redo log的内容主要是用于崩溃恢复。磁盘的数据文件数据来自buffer poolredo log写入磁盘不是写入数据文件。
Redo Log Buffe刷盘机制
在我们写入数据到磁盘的时候操作系统本身是有缓存的。flush就是把操作系统缓冲区写入到磁盘也叫刷盘。
log buffer写入磁盘的时机由参数innodb_flush_log_at_trx_commit的状态控制刷盘时机默认是1实时写。
SHOW VARIABLES LIKE %innodb_flush_log_at_trx_commit%;log buffer写入磁盘的时机一共由三种状态分别是012如下图
0 延退写。log buffer将每秒一次地写入log file中并且log file的flush操作同时进行。该模式下在事务提交的时候不会主动触发写入磁盘的操作。1 默认实时写实时刷。每次事务提交时MySQL都会把log buffer的数据写入log file并且刷到磁盘 中去。2 实时写延迟刷。每次事务提交时MySQL都会把log buffer的数据写入log file。但是flush操作并不会同时进行。该模式下MySQL会每秒执行一次flush操作。
刷盘越快越安全但是也会越消耗性能反之你更想提升性能你认为你的MySQL足够稳定崩溃或者重启的情况是很少的就可以尽量将log buffer的内容晚一点写入磁盘中去这样性能吞吐量上去了但是可靠性降低了。
总结
以上是MySQL的InnoDB架构-内存结构分为 Buffer pool、change buffer、Adaptive Hash Index、log buffer。
InnoDB架构-磁盘结构
下面我们来看一下磁盘结构磁盘结构里面主要是各种各样的表空间叫做Table space。
表空间可以看做是InnoDB存储引擎逻辑结构的最高层所有的数据都存放在表空 间中。InnoDB的表空间分为5大类
System TablespaceInnoDB存储引擎有一个共享表空间在默认情况下所有的表共享一个系统表空间这个文件会越来越大而且它的空间不会收缩。它是一个文件就是ibdata1这个文件文件的位置是由datadir参数决定的。文件里面会存很多的内容包括如下 InnoDB Data DictionaryInnoDB数据字典。所谓数据字典就是表结构定义的那些相关元数据的信息。Doublewrite Buffer双写缓冲区InnoDB的一大特性独有的设计。Change Buffer写缓冲区。它同时也作为内存的一个区域划分因此磁盘中也会有这样一个操作。Undo logs有了Undo Tablespace为什么这里还要Undo logs你既可以把undo log放在系统表空间存储也可以把undo log独立出来放在它自己的表空间中只是在那里存的区别如果没有指定File-Per-Table Tablespace也包含用户创建的表和索引数据 Undo Tablespace能够提供回滚的操作来保证原子性。undo log的数据默认在系统表空间ibdata1文件中因为共享表空间不会自动收 缩也可以单独创建一个undo表空间Redo Loglog buffer的数据会通过到此主要是用于崩溃恢复File-Per-Table Tablespace独占表空间 我们可以让每张表独占一个表空间。这个开关通过innodb_file_per_table设置默认开启。 SHOW VARIABLES LIKE %innodb_file_per_table%;开启后则每张表会开辟一个表空间这个文件就是数据目录下的ibd文件存放表的索引和数据。但是其他类的数据如回滚(undo)信息插入缓冲索引页、系统事务信息二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。 General Tablespace通用表空间多个表共享。也是一种共享的表空间跟ibdata1类似。 可以创建一个通用的表空间用来存储不同数据库的表数据路径(路径要用/而不是\\会语法错误)和文件可以自定义没有指定存储目录使用的是默认存储路径。语法 create tablespace mytblspace add datafile C:/ProgramData/MySQL/mytblspace/mytblspace.ibd file_block_size 16K engineinnodb;
create tablespace mytblspace1 add datafile mytblspace1.ibd file_block_size 16K engineinnodb;查看已存在的表空间和对应的文件 select TABLESPACE_NAME,FILE_NAME from information_schema.FILES; 查看表空间文件 在创建表的时候可以指定表空间用ALTER修改表空间可以转移表空间。 create table mytbl(id integer) tablespace mytblspace ;表导出为SQL脚本的时候可以看到会指定表空间 不同表空间的数据是可以移动的删除表空间需要先删除里面的所有表 drop table mytbl;
drop tablespace mytblspace ; Temporary Tablespace临时表空间存储系统临时的一些数据。比如用户创建的临时表还有磁盘去做数据排序的时候要占用磁盘的一块临时空间。它是一个文件就是ibtmp1这个文件文件的位置是由datadir参数决定的。
InnoDB Doublewrite Buffer双写缓冲区
Doublewrite Buffer它是页的一个备份它保证了内存同步磁盘的可靠性。解决防止页写到一半没写完被破坏了而没法恢复。
InnoDB的页和操作系统的页大小不一致InnoDB页大小一般为16KInnoDB存储引擎16K操作系统页 大小为4KInnoDB的页写入到磁盘时一个页需要分4次写。
操作系统从内存读取数据时以页为单位如果存储引擎正在写入页的数据到磁盘时发生了宕机可能出现页只写了一部分的情况比如只写了 4K就宕机了这种情况叫做部分写失效(partial page write)可能会导致数据丢失。
show variables like Innodb doublewrite1;
我们不是有redo log吗是否可以基于redo log将没有写完的16K的文件做一个恢复呢 这里有个问题当你把内存的16K的内容写到磁盘的16K的内容因为只写了一个4K已经导致磁盘页上的内容遭到破坏了这个磁盘上的页已经不完整了那么用它来做崩溃恢复是没有意义的。
所以在对于应用red log之前需要一个页的副本。如果出现了 写入失效就用页的副本来还原这个页然后再应用redo log。这个页的副本就是double write, InnoDB的双写技术通过它实现了数据页的可靠性。 跟redo log —样double write由两部分组成一部分是内存的double write —个部分是磁盘上的double write。因为double write是顺序写入的不会带来很大的开销。
6.后台线程
后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后 台线程分为master threadIO threadpurge threadpage cleaner thread。- - - master thread负责刷新缓存数据到磁盘并协调调度其它后台进程。
IO thread分为 insert buffer、log、read、write 进程。分别用来处理 insert buffer. 重做日志、读写请求的io回调。purge thread用来回收 undo 页。page cleaner thread用来刷新脏页。
7.Binlog
除了InnoDB架构中的日志文件MySQL的Server层也有一个日志文件叫做 binlog它可以被所有的存储引擎使用。
binlog以事件的形式记录了所有的DDL和DML语句(因为它记录的是操作而不是 数据值属于逻辑日志)可以用来做主从复制和数据恢复。
跟redo log不一样它的文件内容是可以追加的没有固定大小限制。 在开启了 binlog功能的情况下我们可以把binlog导出成SQL语句把所有的操 作重放一遍来实现数据的恢复。 binlog的另一个功能就是用来实现主从复制它的原理就是从服务器读取主服务器 的binlog然后执行一遍。
有了这两个日志之后我们来看一下一条更新语句是怎么执行的(redo不能一次写 入了):
有了这些日志之后我们来总结一下一个更新操作的流程这里redo log是两阶段提交比如将id1001的数据修改name原值是小李现在改为小王
update stu set name 小王 where id1001;先查询到这条数据如果有缓存也会用到缓存。把name改成盆鱼宴然后调用引擎的API接口写入这一行数据到内存 同时InnoDB记录redo logo这时redo log进入prepare状态然后告诉执行器执行完成了可以随时提交。执行器收到通知后记录binlog然后调用存储引擎接口提交事务InnoDB设置redo log为commit状态。更新完成。
图中重点步骤
先记录到内存再写日志文件。记录redo log分为两个阶段。存储引擎和Server记录不同的日志。先记录redo再记录binloq。
为什么需要两阶段提交 两阶段提交的作用就是提供一个可以协调的机制如果一次写完那么是没有办法做到你成功他失败的因此MySQL中设置了两阶段提交的方式来保证redo log和binlog的内容是一致的。
举例 如果我们执行的是把name改成小王如果写完redo log在还没有写binlog的时候MySQL重启了。因为redo log可以在重启的时候用于恢复数据所以写入磁盘的是小王但是 binlog里面没有记录这个逻辑日志所以这时候用binlog去恢复数据或者同步到从库 就会出现数据不一致的情况。
所以在写两个日志的情况下binlog就充当了一个事务的协调者。通知InnoDB来执行prepare或者commit或者rollback。 如果第⑥步Server层写入binlog失败就不会提交。
简单地来说这里有两个写日志的操作类似于分布式事务不用两阶段提交就 不能保证都成功或者都失败。
在崩溃恢复时判断事务是否需要提交
binlog无记录redo log无记录在redo log写之前crash(崩溃)恢复操作回滚事务binlog无记录, redo log状态prepare在binlog写完之前的crash(崩溃)恢复操作回滚事务binlog有记录redo log状态prepare在binlog写完提交事务之前的crash(崩溃)恢复操作提交事务binlog有记录redo log状态commit正常完成的事务不需要恢复
如果binlog不开启redo log也就不需要两阶段提交了因为不需要保证和binlog的内容一致它也不会影响到主从复制和基于binlog的数据恢复。
MySQL合集
MySQL1MySQL发展史MySQL流行分支及其对应存储引擎 MySQL2MySQL中一条查询SQL是如何执行的 MySQL3MySQL中一条更新SQL是如何执行的 MySQL4索引是什么索引类型索引存储模型发展1.二分查找2.二叉查找树3.平衡二叉树4.多路平衡查找树5. B树6.索引为什么不用红黑树7.InnoDB的hash索引指什么 MySQL5MySQL数据存储文件MylSAM中索引和数据是两个独立的文件它是如何通过索引找到数据呢聚集索引/聚簇索引InnoDB中二级索引为什么不存地址而是存键值row ID如何理解
