数据库的查询操作具有天然幂等性，不会对数据库有任何的修改。但是mysql如何实现对数据库的更新操作呢？
1、内存与磁盘的逻辑结构图
要了解一个sql是如何更新的，需要了解一下Innodb的内存和磁盘的结构之间的关系。
官网Innodb的内存和磁盘结构图参考资料：
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
2、内存缓存模块
思考：每次更新数据访问磁盘效率低下，有没有什么优化方式呢？
2.1、Buffer pool
首先数据库更新操作都是基于内存页，更新的时候不会直接更新磁盘，如果内存有存在就直接更新内存，如果内存没有存在就从磁盘读取到内存，在更新内存，并且写redo log，目的是为了更新效率更快，等空闲时间在将其redo log所做的改变更新到磁盘中，innodb_flush_log_at_trx_commit设置为1时，也可以防止服务出现异常重启，数据不会丢失;
Innodb操作数据有一个最小的数据单位，称为页（索引页和数据页），因为数据在磁盘更新的速度太慢，所以将数据放入内存页缓存Buffer pool，
默认大小128M，下
一次读取相同的页，判断是否在缓冲池里，如果在，直接读取，不用再访问磁盘；
-
脏页： 修改数据的时候先修改缓存中的数据，数据发生变更就变成了 脏页 ；
-
刷脏： 每隔一段时间将数据刷回磁盘，称为 刷脏；
内存中满了怎么办？
-
采用lru的算法来淘汰旧的数据，分成了young和old区来实现，分代思想，类似jvm中的分代思想；
思考：如果数据在缓存中，则直接进行更新，但是如果不在缓存中，至少需要进行一次磁盘io，有没有什么方法可以进行优化呢？
2.2、 Change Buffer
Change buffer
也称
insert buffer
：写缓冲，默认是buffer pool的25%，
为了提高
非唯一性索引而避免唯一性检查
的数据的修改而提供的缓冲区，提高效率；
如果更新的数据不是唯一索引数据，也就是不需要从磁盘加载数据，那么先将更新的数据记录在change buffer中，之后再merge到页缓存中，以提高写的效率。
将change buffer的数据merge到数据页的情况叫做merge，什么时候发生merge？
-
在访问这个数据页的时候;
-
或者通过后台线程;
-
或者数据库 shut down;
-
redo log 写满时触发。
如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立 刻读取，就可以使用 Change Buffer(写缓冲)。写多读少的业务，调大这个值:
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
思考：如果更新的数据在buffer pool中还未同步到磁盘中，这时候mysql重启了，数据是不是丢失了呢？
2.3、Log Buffer
Log Buffer
默认是16M，还有对应的
Redo Log默认大小48M，也称重做日志。
为了避免上述问题，innodb提供了
crash-safe功能-崩溃恢复能力，使用了
WAL技术（write-ahead-log），提供了redo log。
用它来实现事务的持久性。它的关键点就是先写日志再写磁盘，二阶段提交:
使用redo log和binlog来判断事务的完整性；
2.4、Adaptive Hash Index自适应hash索引
主要保存内存中的热点页上的数据，用于内存的快速索引。
思考：log buffer什么时候写入log file，即rodo log呢？
和事务相关：
innodb_flush_log_at_trx_commit = 1 来控制其写入的时机
innodb_flush_log_at_trx_commit控制
刷盘方式逻辑示意图：
3、日志三剑客
再来了解一下三个重要的日志，
日志三剑客：
-
redo log-(上面提到的Log Buffer)；
-
binlog；
-
undo log；
|
Redo log-WAL技术-
（持久化，
纪录页做了什么改动
，字段0改为1）
|
Bin log-
归档日志
（怎么修改的，sql语句本身）
|
|
特点：
tips：物理日志只有具体引擎自己能用，别人没有共享我的物理格式；
逻辑日志可以给别的数据库用，公用的逻辑；
优点：
缺点：
非双一配置：
innodb_flush_logs_at_trx_commit=2
sync_binlog=1000。
为控制
redo log的写入策略，采用
innodb_flush_log_at_trx_commit
参数来控制；
|
特点：
作用：
两种格式：
binlog
的写入流程:
binlog cache ->write binlog file - > fsync 磁盘
非双一配置：
innodb_flush_logs_at_trx_commit=2
sync_binlog=1000。
write和fsync的时机控制：提供了
sync_binlog
参数
|
|
undo log-撤销日志
|
其他：
|
|
数据恢复：当mysql数据库出现问题后，在进行数据恢复的时候，会根据redo log来决定undo log，这样来进行数据恢复；
MVCC: 并发版本控制MVCC的时候，会有一个
一致性视图，里面会记录相应的回滚日志。
比如一个事务在执行到一半的时候实例崩溃了，在恢复的时候是不是先恢复redo，再根据redo log和binlog两阶段提交状态，决定执行undo回滚宕机前没有提交的事务。
|
持久化控制
：“双一”和“非双一”设置
二阶段提交：
持久化：“非双一”
会涉及到数据的丢失
|
3.1、数据库binlog日志格式
-
行格式row：按行数据来记录日志
-
语句格式statement：执行的sql语句记录；
-
Mixed格式：如果主库和从库的索引不一样，会造成执行的sql不一样，这时候就需要用mixed格式。
思考：为什么会出现
mixd
格式的
binlog
？
因为有些statement格式的binlog可能会导致主备不一致，所以要用row格式。
如果通过索引及范围查找limit 1，如果binlog是
statement格式，在语句执行的时候会出现不一致的情况。
delete from Order where num>4 and t_modified<='2018-11-10' limit 1;例如主库上以num建立索引，从库上以时间t_modified建立索引，这样执行的结果就有可能不一样，mysql认为这样是不安全的。
row
格式优点：
数据安全，因为记录的详细过程；此外，
设置row
格式的另一个好处是：恢复数据
。
缺点：占空间。比如用一个delete语句删掉10万行数据，用statement格式就是一个sql被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要把10万条记录写入到binlog中。这样做，就会浪费很大的内存空间，同时写binlog也要耗费io资源，影响到执行速度。
如果设置
为
row格式的另一个好处是：恢复数据。
所以，mysql就取了个折中方案，也就是有了
mixed
格式。如果mysql判断会出现sql语句可能会引起主备的不一致性，就用row格式，否则就用statement格式。
4、sql的更新流程
update USER set name=“king” where id = 9527;更新sql的详细执行步骤：
-
(1). 客户端通过tcp/ip和数据库的 连接器建立连接，连接器获取用户账号信息并验证权限是否匹配；
-
⚠️此步可能出现的常见错误：“Access deied for user”
-
(2). 如果开启了 缓存查询，先查看缓存是否存在数据，对表的权限进行校验，通过则直接返回给客户端；如果没有开启缓存，则走向第三步；
-
(3). 通过 分析器的词法分析，得到是一个update操作，表名是USER_TABLE，字段age where；
-
⚠️此步可能出现的常见错误：“Unknown column ‘XXX’ in ‘where clause”
-
(4). 通过 分析器的语义分析，看看是否有语法问题
-
⚠️此步可能出现的错误：“You hava an error in your SQL syntax. ”
-
(5). 通过 优化器选择索引，id为主键，使用主键索引查询；
-
(6). 将生成的最优执行方案交给 执行器，执行器调用底层的存储引擎的读接口通过搜索书取到id=6这行的数据，如果id=6的这行数据本来就在内存中，那么将会直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回；
-
(7). 执行器拿到 存储引擎返回的age数据，进行运算+1，得到新的一行数据，然后执行器调用引擎的写接口写入这行新数据；
-
(8). 引擎将这行数据更新到内存中，同时将这个更新操作 记录到Redo log 里面，此时redo log处于 prepare状态，然后告诉执行器完成了，随时可以提交事务；
-
(9). server层的 执行器生成这个操作的binlog，并把binlog写入磁盘；
-
(10). 执行器调用引擎的事务接口，引擎把刚刚写入的Redo log改为提交 commit状态;
-
更新完成。
具体更新流程如下所示：
5、问题思考答疑
问题一：
响应一次update sql需要写几次磁盘？
答：三次。redo log 2次(prepare + commit)，binlog一次。
问题二：
为什么需要两份日志呢？
答：Mysql里并没有InnoDB引擎，MySql自带的引擎是MyISAM，但是MyISAM 没有crash-safe能力，binlog只能用鱼归档，所以InnoDB使用了另外一套日志系统，也就是Redo log来实现creash-safe的能力。
一句话区别：crash-safe是崩溃恢复，就是原地满血复活；binlog是制造一个副本；
问题三：
如何让数据库恢复到一个月内的任意一秒的状态呢？
答：首先我们的备份系统需要保存近一个月的所有的binlog；另外，要求系统会定期做整库备份，根据系统的重要性，可以一天或者是一周备份。定期的整库备份时间越短，“最快恢复的时间”就越短，主要根据具体的业务容忍度来做。
恢复步骤：
-
1、找到需要恢复时间点之前的最近一次的整库备份，将其恢复到临时数据库；
-
2、从整库备份时间点开始，将备份的binlog依次回放，重放到需要的时间点那个时刻；
-
3、至于误删之后的，不能只靠binlog，需要和业务方一起来完成数据的恢复，因为由于误删，可以插入了一些错误的操作；
问题四：
为什么需要两阶段提交？
答：
主要为了保证binlog和原库数据一致性，分析步骤如下
-
1、redo log 处于prepare状态；
-
2、server写binglog；
-
3、redolog commit；
第2步 崩溃：不满足binlog和redo log一致性，重启恢复：没有commit，回滚；备份恢复：没有binlog ；结果：一致；
第3步 崩溃： 满足binlog和redo log一致性，重启恢复：自动commit，提交；备份恢复：有binlog； 结果：一致
事务是否提交的条件是：看结果是否符合我们要达到的“用binlog恢复的库和原库逻辑相同”这个要求；
可利用反证法证明：
如果不使用两阶段提交，无论是先写Redo log 后写 binlog，还是先写Binlog 后写 Redo log，都会出现主从数据库数据的不一致性。
问题五：
两个参数的意义？
数据库的“ 双一”配置
答：
innodb_flush_log_at_trx_commit：表示每次事务的redo log 都直接持久化到磁盘，值建议设置为1，可以保证MySql异常重启后的数据不会丢失；
sync_binlog: 表示每次事务的binlog都持久化到磁盘，这个参数最好也设置为1，可以保证mysql异常重启后binlog不丢失；
保证事务成功，参数设置为1后，日志必须落盘，这样在crash后不会出现数据的丢失；
问题六：
有了Redo log，binlog能不能去掉？
答：不能去，至少目前不能去。原因：
-
1、redo log只有innodb有，别的引擎没有；
-
2、redo log是循环写的，不持久保存，binlog的归档功能redo log不具备。所以在主从备份的时候还是需要server层所有引擎都可以用的binlog。
-
3、binglog没有crash-safe功能；
-
4、binlog是可以手动关闭的，所以只依靠binlog是不靠谱的；
ps：个人观点：当redo log可以追加写 并被所有的存储引擎可用的时候就可以丢弃binlog，并且redo log的恢复效率和同步效率会显著提高，因为它记录的是物理的变化。
问题七：
同样是写磁盘，为啥要先写日志后写磁盘呢？
主要优化利器点：
-
* 顺序写
-
* 组提交；
首先数据库的数据更新都是基于内存页的更新，更新的时候不会直接更新磁盘，如果内存有数据就直接更新内存，如果没有就从磁盘读取数据到内存，在内存更新，并写入redo log。目的就是为了减少访问延迟，提高更新效率，等空闲的时候再将redo log所做的改变更新到磁盘中。Rodo log是顺序写，而update是直接更新磁盘，寻找到数据再进行更新；即使有索引也是随机写，所以速度会很慢；磁盘访问顺序写的时间优势，不用找“磁盘位置”。
访问磁盘的时间：每次访问磁盘的一个块时，磁臂就需移动到正确的磁道上（这段时间为寻址时间），然后盘片就需旋转到正确的扇区上（这叫旋转时延），这套动作需要时间，所以说顺序写比随机写性能高，要知道db的最大瓶颈在io；
我们先分析下redo log再哪些场景会刷到磁盘。
-
场景1：redo log写满了，此时MySQL会停止所有更新操作，把脏页刷到磁盘
-
场景2：系统内存不足，需要将脏页淘汰，此时会把脏页刷到磁盘
-
场景3：系统空闲时，MySQL定期将脏页刷到磁盘
问题八：
数据库Redo log只有commit的时候才会真正的提交吗？
答：正常情况是只有在commit时才提交到数据库落盘，但是当崩溃恢复的过程中，当存在“binlog完整 + redo log prpare ”的条件，数据也会自动被提交到数据库；redo log 和binlog 之间通过事务ID进行对应。
问题九：
数据写在redo log上而没有写入数据库，那读到的数据不是不一致吗？
答：写到了内存，读取的时候是在内存读取。并且读和写操作会引起内存的淘汰。
问题10
：mysql启动，对于innodb的启动是如何实现的，undo log的作用？
答：mysql重启，需要读完redo log的日志，从checkpoint开始到writepos结束。如果mysql的一个实例崩溃了，一个事务写入了redo log但是未写入binlog，也就是未提交commit，那么该mysql在重启的时候，会先恢复redo log，之后构造undo log回滚宕机前没有提交的事务。
6、binlog文件
了解binlog文件内容，可以更好的理解mysql的执行原理，查看命令：
show binlog events mysql-bin.000001;部分binlog日志的内容如下：
*************************** 20. row ***************************
Log_name: mysql-bin.000001 ----------------------------------------------> 查询的binlog日志文件名
Pos: 11197 ----------------------------------------------------------------> pos起始点:
Event_type: Query -------------------------------------------------------------> 事件类型：Query
Server_id: 1 --------------------------------------------------------------------> 标识是由哪台服务器执行的
End_log_pos: 11308 ------------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)
Info: use `zyyshop`; INSERT INTO `team2` VALUES (0,345,'asdf8er5') ---> 执行的sql语句
*************************** 21. row ***************************
Log_name: mysql-bin.000001
Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)
Event_type: Query
Server_id: 1
End_log_pos: 11417
Info: use `zyyshop`; /*!40000 ALTER TABLE `team2` ENABLE KEYS */
*************************** 22. row ***************************
Log_name: mysql-bin.000001
Pos: 11417
Event_type: Query
Server_id: 1
End_log_pos: 11510
Info: use `zyyshop`; DROP TABLE IF EXISTS `type`
7、小结
一个sql的输入执行并不是我们想象的那么简单，背后付出了很多的艰辛，经典的东西值得深究和回味。
水滴石穿，积少成多。学习笔记，内容简单，用于复习，梳理巩固，原内容2月有更新。
##参考资料
官网Innodb的内存和磁盘结构图参考资料：
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
《Innodb存储引擎》
《MySql实战45讲详解》--丁奇
994
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
