keep coding
0%

MySQL的update,insert流程

发表于 更新于 分类于 Disqus：
本文字数： 3.9k 阅读时长 ≈ 4 分钟

MySQL的架构图

一条SQL语句从客户端发送到服务端，需要经历很多流程

  • 客户端需要与服务端建立连接，建立连接的时候，服务端会验证客户端的登录验证，登录用户权限等操作
  • 处理SQL语句
    登录成功后，客户端发送SQL语句，需要解析SQL，然后优化SQL语句的执行，判断不同的执行类型，让执行器执行。
    如果是操作数据的操作，还需要从磁盘加载数据到内存做相应的处理。修改类的还需要回写到磁盘，以防数据丢失。
    然后基于不同的SQL语句返回处理结果
  • 客户端基于返回结果集做处理
    mysql

分析一个update的执行流程

mysql-update

当执行一条更新的SQL语句的时候，需要经历很多流程。

  • 执行需要先判断当前数据是否在内存中，如果没有需要从磁盘加载数据，到内存，然后记录当前的原始值，以备执行失败的时候回滚。
  • 然后会更新值到内存，并写入到redo log 和bin log，这个时候数据在内存中是最新的，但是在磁盘中还是原始值。所以可以认为内存的是胀数据（如果MySQL崩溃，会在重启时候重放redo恢复已经完成的操作，所以数据最终是一致）。
  • 然后MySQL根据策略刷入内存中的数据到磁盘，此时内存中的数据就和磁盘数据保持了一直（行的数据会有隐藏的列记录当前数据的最新版本号信息）
  • undo log，是用来记录历史数据和进行回滚，mvcc的非一致性读（当一个更新语句执行中，读语句会读一个最新的历史版本数据），redo log是用来进行崩溃时恢复的，保持事务数据完整性的。bin log记录数据的变更，进行数据重放，主从同步等

分析一个insert的执行流程

当执行一个insert语句的时候会相对复杂些

  • 存储引擎需要，把数据先放入 insert buffer中，但是会和update的流程相似，需要写入到 undo log,redo log,bin log中。
  • 而且如果是有 auto 自增主键ID的时候，还需要加 auto_lock(轻量级的表锁)，来生成自增ID
  • 如果插入失败，引擎会执行一个反向操作。这个自增ID也废弃了

undo log

undo log的作用

  • 记录数据更新前的值，防止更新失败后进行回滚
  • 记录数据的版本数据，在修改操作中，不防止读的操作。
  • 与redo log协作保证事务的数据一致性
  • 通常默认情况下，undo数据是存储在ibdata中的，但你也可以通过配置选项 innodb_undo_tablespaces 来将undo 回滚段分配到不同的文件中。

    redo log

    redo log主要是用来保证数据的一致性的。
    需要redo log是因为，为了提高实际数据写入到磁盘的效率，MySQL会把记录在内存中的数据批量写到磁盘中，而非每次更新的少批量数据实时更新到磁盘。那么这个时候就需要有个保证在数据库崩溃的时候，内存数据未刷入到磁盘时候的，来保证这些数据的一致性，因为毕竟这些数据已经做过了修改只是未刷新到磁盘。而redo log就是做这些事情的，因为redo log是顺序写，所以写的效率是很高。当崩溃重启后MySQL会检测redo log和数据，然后重放redo log保证数据的一致。
    undo log记录了更新之前的数据，那么redo log就是记录更新的数据。再做redo log写之前，需要先写undo log。
    redo log 包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，redo log 存储的是物理格式的日志，记录的是物理数据页面的修改信息，它是顺序写入 redo log file 中的。
    因为有了日志缓冲(redo log buffer),为了提高写入的效率。此时数据还在内存中，所以MySQL提供了配置，来让你选择写入的时机innodb_flush_log_at_trx_commit。
    • 0：日志缓存区将每隔一秒写到日志文件中，并且将日志文件的数据刷新到磁盘上。该模式下在事务提交时不会主动触发写入磁盘的操作。
      这个模式会丢失数据
    • 1：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，并且刷新到磁盘中，该模式为系统默认。这个是最安全的模式
    • 2：每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中【系统缓存中】，但是并不会同时刷新到磁盘上。该模式下，MySQL会每秒执行一次刷新磁盘操作。
      对于数据安全性来说选择1是最好的模式

redo log记录的形式

redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log
实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：
mysql-redo
同时我们很容易得知， 在innodb中，既有 redo log 需要刷盘，还有 数据页 也需要刷盘， redo log 存在的意义主要就是降低对 数据页 刷盘的要求 。在上图中， write pos 表示 redo log 当前记录的 LSN (逻辑序列号)位置， check point 表示 数据页更改记录** 刷盘后对应 redo log 所处的 LSN (逻辑序列号)位置。 write pos 到 check point 之间的部分是 redo log 空着的部分，用于记录新的记录； check point 到 write pos 之间是 redo log 待落盘的数据页更改记录。当 write pos 追上 check point 时，会先推动 check point 向前移动，空出位置再记录新的日志。
启动 innodb 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 redo log 记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 binlog )要快很多。 重启 innodb 时，首先会检查磁盘中数据页的 LSN ，如果数据页的 LSN 小于日志中的 LSN ，则会从 checkpoint 开始恢复。 还有一种情况，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

bin log

binlog记录了数据库系统所有的更新操作，主要是用来实现数据恢复和主从复制的。一方面，主从配置的MySQL集群可以利用binlog将主库中的更新操作传递到从库中，以此来实现主从数据的一致性；另一方面，数据库还可以利用binlog来进行数据的恢复。
redo log和binlog的产生方式不同。redo log是在物理存储引擎层产生，而binlog是在MySQL数据库的Server层产生的，并且binlog不仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对数据库的更改都会产生binlog。
对于 InnoDB 存储引擎而言，只有在事务提交时才会记录 biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？ mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

  • 0：不去强制要求，由系统自行判断何时写入磁盘；
  • 1：每次 commit 的时候都要将 binlog 写入磁盘；
  • N：每N个事务，才会将 binlog 写入磁盘。
    所以为了保证数据的安全性，redo log和bin log两个参数的值都需要在1这个级别上
    binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED 。
  • STATMENT 二进制文件记录的是日志的逻辑SQL语句
  • ROW 基于行的复制，记录每次数据的变化，如果是批量更改语句等会产生大量的日志
  • MIXED 基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

怎么保证数据的不丢失

回顾下数据的写入流程
记录历史数据，更新内存的数据，写入redo log,写入bin log,更新redo log为commited状态

  • 当写入undo log，未写入redo log，这个时候崩溃了【内存的数据也未落入磁盘】，数据相当于没修改。不造成影响
  • 当写入undo ，redo 为写入bin崩溃。这个时候会判断是否需要回滚等等操作。如果不做，会造成master的实际和从库【依据bin同步数据】实际数据不一致。
  • 写入 undo,redo,bin未写入commit状态，重启的时候回依据commit状态做二次处理。
    为什么不选择先写入 bin:
    当先写入bin的时候，如果未写入redo，会造成从库数据的时候不一致性。
    在MySQL内部，在事务提交时利用两阶段提交(内部XA的两阶段提交)很好地解决了上面提到的binlog和redo log的一致性问题：
  • 第一阶段： InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。此阶段对binlog不会有任何操作。
  • 第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写binlog（先调用write()将binlog内存日志数据写入文件系统缓存，再调用fsync()将binlog文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。
    综上所述，MySQL innodb依赖于undo ,redo,bin保证数据上完整的一致性

参考：
《MySQL技术内幕InnoDB存储引擎》
http://mysql.taobao.org/monthly/2016/02/01/