一条update语句都经历了些什么

Hi 大家好啊，还是我呀，上次我给你们介绍了我们mysql架构组织，现在我给大家讲一下如果一个update请求，会被mysql怎么处理。

redo log 和 binlog基本介绍

首先介绍这么一个场景，假如你是一个小吃店的主人，每天接到订单就准备开始做小吃了了，如果某段时间你的生意特别火爆出现了大量订单，而你需要记录今天收入支出情况，有以下几个方案。

1. 每卖出一份小吃，分别记录这个订单的各个菜品的成本和收入到我们的记账大本本中,计算盈亏。
2. 将这个订单信息先用小本本记下来，等下班的时候再汇总到我们的大本本中，慢慢计算。

这个记录订单信息的小本本+我们记录收入的小单子就是我们mysql的WAL(Write-Ahead Logging)，先写日志再写磁盘，就是先写这个订单的小本本再统计到我们记账的大本本中。

如果今天卖出的小吃不多，我们的小本本写完之后，下班时候直接将信息记录大本本中，然后记录我们今天赚了多少钱，但是如果今天卖出去的特别多呢，小本本写完了，那么就需要重新擦掉小本本内容，将擦掉的小本本信息写到大本本中，这样小本本可以让我们继续写。

小本本就是我们mysql的redo log，redo log是固定大小的，可以配置为一组4个文件，每个文件大小是1GB,共记录4GB。

write pos和checkpoint一个是记录当前位置，一个是当前要擦除的位置，redo log 其实可以把他想成一个环形结构，write pos在写，checkpoint在追，如果checkpoint超过write pos，那么就让你嘿嘿嘿，可是这个嘿嘿嘿的代价就是mysql停止接受其他修改请求，加快checkpoint的推进脚步

redo log 和 binlog区别

我们之前说过mysql的结构分负责的分为server层和存储相关的引擎层，我们上面说的redo log就是引擎层的，而server层会称为binlog。那么这两个log的区别是什么呢？

1. redo log是物理日志，记着某个数据页做了什么修改，binlog是逻辑日志记录，例如给id=2这个记录中c字段的属性值+1

2. redo log是循环写，空间固定写完重新写，binlog是追加写，文件写到一定大小会切换下一个

3. redo log是innodb独有的，binlog是在server端的，所有引擎都会有

update语句在数据库中是怎么执行的

update t set c = 2 where id = 1;注意id是主键，是唯一索引

• 首先执行器先调用执行引擎找到id为1的记录，如果数据页在内存中直接返回，如果不在的话执行引擎先从磁盘中读取数据返回给执行器
• 执行器拿到存储引擎的值后进行修改得到一行数据，再调用存储引擎写入这行数据
• 引擎将这个数据更新到内存中，并进行redo log的prepare，并告知执行器这个事务可以了，准备提交
• 执行器生成binlog写入磁盘
• 执行器调用执行引擎提交事务接口，引擎将刚刚写入的redo log变为commit状态

我们通常会有一个疑问，写在内存中的数据一旦遇到mysql挂掉了，宕机了就没有恢复了，而mysql针对这种情况也做了处理。

binlog的写入

binlog的写入比较简单，先写到binlog的操作是以事务为基本单位的， 首先写write到binlog cache中，事务提交后fsync到binlog中。write和fsync的时机是由sync_binlog参数控制的

• sync_binlog = 0 只write到page cache不刷到磁盘
• sync_binlog = 1 每次事务提交都会刷到磁盘
• sync_binlog=N 每个事务都先提交到page cache中，等到攒到N个事务时，一起提交到刷盘

redo log的写入

首先说明一下redo log的结构

由于page cache和redo log buffer是基于内存的写起来很快，而disk是基于磁盘的写起来很慢，innodb帮我们提供了这么一个配置参数innodb_flush_log_at_trx_commit

• 设置为0时，每次事务提交都只会写在redo log buffer中
• 设置为1时，每次事务提交redo log都写到磁盘
• 设置为2时，每次事务提交只是把redo log写到page cache中

redo log可能存在事务没有提交但是我们的redo log被持久到磁盘

• redo log buffer占用空间达到innodb_log_buffer_size一半的时候，后台先从主动写盘，这个写盘只是write到cache中
• 并行事务提交时，顺便把他也给带持久化到磁盘了

组提交机制

LSN(log sequence number)单调递增的，用来对应redo log的写入点，每次写入长度为length的redo log，LSN的值就会加上length，如果有多个事务同时到达时，第一个到达就会选为组里的leader，当leader准备写盘时，所有LSN小于等于当前LSN的redo log都会被持久化到磁盘。

在并发更新场景下，一个事务写完redo log buffer之后，fsync越晚提交，组员越多，节约IOPS效果更好，可以通过binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count实现

binlog_group_commit_sync_delay表示多少微妙后调用fsync

binlog_group_commit_sync_no_delay_count累计多少次后调用fsync

change buffer基本介绍

好了我们介绍了redo log和binlog，你还记得我在开篇说的那个问题么，我们更新语句数据优先更新到内存再更新到磁盘中，那么他是怎么操作的呢，铛铛铛，接下来就是我们change buffer和buffer poll的介绍了。

介绍change buffer之前，我先问一个问题，唯一索引和主键索引有什么区别，相信很多人都会答出来，唯一索引是说加了索引的字段在数据库中只有一个记录，超过一个就报错，普通索引就是加了索引的字段可以有多个相同的记录。但是唯一索引和主键索引对修改和查询语句造成的影响，很少有人知道。

• 查询区别，唯一索引查到记录直接返回，普通索引查到记录后还会继续找，直到找到不满足的内容返回，虽然他查询次数比唯一索引多，但是innodb每次都是16k的读数据到内存中，对于普通索引来说他只是多了一次指针和一次计算的时间，就算他是数据页中最后一条，需要访问下一页的数据，而我们实际一个数据页可以存放上千个数据，这种情况时间时间差距很少
• 更新区别，如果更新数据不在内存中，对于唯一索引来说需要先将数据页读入内存，判断有没有冲突，插入这个值，对于普通索引，只是将更新记录写到change buffer中就结束了

change buffer应用

如果数据页内存包含这个数据直接写在内存中，如果数据页不在内存中，会将这页数据缓存在change buffer中，下次查询需要访问这个数据页的时候，merge到磁盘中，除了查询会merge到磁盘，mysql也会默认启动线程定期merge到磁盘。

change buffer解决了修改数据时的读的时间，而一旦出现大量修改又大量查询的情况，就会额外需要维护change buffer时间，所以写多读少的数据库来说，change buffer真是帮了大忙了。

上面我们有说过redo log减少了随机写的时间，而我们change buffer减少了随机读的时间，那么他们有什么区别的呢？change buffer中所有操作完成后都是要写到redo log中

buffer pool

取数据流程，mysql边读边发

• 获取第一行，写到net_buffer中，这块内存大小由参数net_buffer_length定义的，默认是16k
• 重新获取行，直到net_buffer写满，调用网络接口发出去
• 如果发送成功，清空net_buffer.继续取下一行，写入net_buffer中
• 如果发送函数返回EAGIN或WASWOULDBLOCK，表示本地socket send byffer写满进入等待

LRU算法

mysql为了让我们检索快一点，引入了buffer pool的概念，会预先将数据先放在内存中，我们再次查询就会很快了，但是有一个问题，如果一个信息表，百年不做更新，但是偶然一天导出报表，就会将当前buffer pool里面的数据全部淘汰掉，存入扫描过程中访问的数据页内容，就是我们这个百年不会操作的表里面的内容，造成我们的内存命中率降低，磁盘压力变大，sql响应慢

mysql为了避免出现这种情况，引入了老年代，新生代的概念，老年代3/8、新生代5/8

• 访问数据页p3，由于p3在young区，因此将其移动到链表头部
• 访问不存在链表的数据页，这时候需要淘汰P8数据页，但是新插入数据页PX，放在old区
• 处于old区的数据页，每次访问都要进行判断？
  这个数据页在LRU链表超过1s，移动到头部
  这个数据页在LRU链表短于1s，位置保持不变。innodb_old_blocks_time控制

一条sql语句突然慢了怎么办

我们肯定在开发或者线上遇到一个场景就是我有个sql语句平常日子执行很快，但是突然偶尔执行变慢了，但是又很难复现出来，那是数据库可能在刷脏页。

脏页、干净页

当内存数据页和磁盘数据页不一致时候，我们称这个内存页为脏页，内存数据写入磁盘后和磁盘数据内容一致了，就是干净页。

什么时候发生这个事情呢

• 第一种场景就是我们的小吃摊中，记录账单信息的小本本写满了，我们需要将checkpoint向前推进，写到磁盘来供write pos的写入空间。
• 一种是内存buffer pool满了，系统内存不足，需要新的内存页，就要淘汰一些数据页，空出内存给别的数据页，如果淘汰的是"脏页"，需要将脏页写到磁盘
• mysql空闲，刷一下"脏页"
• mysql正常关闭时，把脏页从内存flush到内存中

innodb刷脏页的控制策略

innodb_io_capacity他会告诉innodb的磁盘能力，建议设置成磁盘的iops。- innodb的刷盘速度要考虑两个因素一个是脏页比例，一个是redo log写盘速度。

• 脏页比例innod_max_dirty_pages_pct脏页比例上限，默认值是75%
• inndob每次写入日志都有一个序号，当前序号跟checkpoint之间差值为n
  根据两个权衡标准，选取较大值，按照innodb_io_capacity能力可以得到刷盘速度。