更新sql的执行过程

目录

1、内存与磁盘的逻辑结构图

2、内存缓存模块

2.1、Buffer pool

2.2、 Change Buffer

2.3、Log Buffer 

2.4、Adaptive Hash Index自适应hash索引

3、日志三剑客

3.1、数据库binlog日志格式

4、sql的更新流程

5、问题思考答疑

6、binlog文件

7、小结

前言         数据库的查询操作具有天然幂等性，不会对数据库有任何的修改。但是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语句本身）
特点： 1、Redo log是 引擎层 InnoDB特有的日志，先写redo日志； 2、循环写，固定空间会用完； 3、 物理日志，内容基于磁盘的额page页，别人不能共享； tips：物理日志只有具体引擎自己能用，别人没有共享我的物理格式；        逻辑日志可以给别的数据库用，公用的逻辑； 优点：         (1) 组提交：提高系统的吞吐量，减轻io消耗；  (2) 顺序写：顺序写日志，避免随机写，写入时间多元化；   (3)崩溃恢复： crash-safe能力；宕机 原地满血复活； binlog 是 基于时间点 的数据恢复； + 主从备份； 宕机的重启从redo log开始； 缺点：       (1) 额外的写redo log操作的开销；       (2) 数据库启动时恢复操作所需要的时间；   非双一配置： innodb_flush_logs_at_trx_commit=2     sync_binlog=1000。 为控制 redo log的写入策略，采用 innodb_flush_log_at_trx_commit 参数来控制； 0：每次事务提交都只是把rodo log留在redo log buffer中； 1:  每次将redo log直接持久化到磁盘中； 2：每次将redo log写到文件系统：page cache中；	特点： 1、Binlog是MySql Server 层逻辑日志，所有存储引擎都可以使用； 2、 追加写，不会覆盖以前的日志，用于归档，事务提交时写； 3、 逻辑日志，记录的是逻辑操作，sql或者是前后的行记录； 作用： 归档； 主从备份：   高可用的框架的实现大部分都都来源于binlog, binlog功不可没，生态的强大。 下游消费binlog，异步系统消息输入; 两种格式：  statement记录的是sql语句，节约内存：主备的数据不一致；  row格式记录的是行的内容，记两条，改变前和改变后的记录；一般采用row，但是数据量会变大； binlog 的写入流程:  binlog cache ->write binlog file - > fsync 磁盘 先把binlog从binlog cache中写到磁盘上的binlog文件； 调用fsync持久化到binlog磁盘中 非双一配置： innodb_flush_logs_at_trx_commit=2     sync_binlog=1000。 write和fsync的时机控制：提供了 sync_binlog 参数 Sync_binlog = 0 的时候，每次提交事务都只write，不fysnc； sync_binlog = 1 时，表示每次提交事务都会执行fsync； sync_binlog = n 时，每次提交都write，但是积累n时才fysnc；
undo log-撤销日志	其他：
数据恢复：当mysql数据库出现问题后，在进行数据恢复的时候，会根据redo log来决定undo log，这样来进行数据恢复； MVCC: 并发版本控制MVCC的时候，会有一个 一致性视图，里面会记录相应的回滚日志。      比如一个事务在执行到一半的时候实例崩溃了，在恢复的时候是不是先恢复redo，再根据redo log和binlog两阶段提交状态，决定执行undo回滚宕机前没有提交的事务。	持久化控制 ：“双一”和“非双一”设置 innodb_flush_log_at-trx_commit=1； 控制redo log刷盘的情况 sync_binlog=1；控制binlog刷盘的情况； 二阶段提交： binlog和 redolog 实现了二阶段提交，数据的一致性； 持久化：“非双一” 会涉及到数据的丢失

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     官网内存中的缓存资料参考：： https://dev.mysql.com/doc/refman/5.7/en/innodb-in-memory-structures.html 《Innodb存储引擎》 《MySql实战45讲详解》--丁奇