更新语句在MySQL中是如何执行的

一条SQL的执行过程

  • 用户请求

  • Tomcat开启线程,获取数据库连接池并处理请求,将SQL发送到MySQL服务器

  • MySQL服务器中会有线程在监听SQL得到来

  • 该监听线程监听到SQL到来后,会将SQL发送给SQL接口(用于处理SQL,将SQL往下传递)

  • SQL接口会将SQL语句发送给SQL解析器(解析SQL语句,到底是执行INSERT还是UPDATE等操作)

  • SQL解析器将解析后的SQL语句发送给查询优化器(优化SQL语句的执行顺序等,提高SQL执行效率)

  • 查询优化器将优化好的SQL发送给执行器(创建对应的执行计划,具体怎样去执行SQL)

  • 执行器创建好执行计划后会调用存储引擎(访问数据)

  • 存储引擎通过访问内存磁盘的数据,然后返回获取到的数据

更新语句的执行

InnoDB存储引擎中,有一个缓冲池(Buffer Pool),这里会缓存磁盘文件中的一部分数据,如果你多次查询同样的数据,那么存储引擎就可以不用去访问磁盘,而直接从缓冲池中获取数据即可,并且在执行操作的时候,对数据行进行加锁操作,保证同一时间内只有一个线程可以访问这条数据

在执行更新操作的时候,首先会把旧数据写入到undo日志文件中,便于之后的事务回滚操作

接着会将该条数据更新到缓冲池中

之后会将这条数据写入到存储引擎的redo log buffer中,这也是一个缓冲区,记录下你对数据所作出的修改,用于在因数据库服务器宕机等原因导致修改数据丢失时对数据进行恢复

当我们提交事务时,存储引擎会根据一定的策略将redo日志从redo log buffer写入到磁盘文件中去,通过数据库配置项innodb_flush_log_at_trx_commit来配置

innodb_flush_log_at_trx_commit有3种值

  • innodb_flush_log_at_trx_commit = 0:事务提交后不将redo日志刷入磁盘,这可能导致在数据库宕机时无法对修改的数据进行恢复

  • innodb_flush_log_at_trx_commit = 1:事务提交后立即将redo日志刷入磁盘,这种策略可能会影响mysql的性能,因为每次都要进行I/O操作,将redo日志刷入磁盘,推荐使用该策略作为默认的redo刷盘策略

  • innodb_flush_log_at_trx_commit = 2:事务提交后先将redo日志写入os cache,再由系统决定什么时候将redo日志刷入磁盘,这种策略可能也会导致redo日志的丢失,如果是机器直接宕机,但此时redo日志还在os cache中,那么它就丢失了

binlog

与redo、undo不同,binlog是属于MySQL自己的日志文件,被称为归档日志,用于记录对数据的所有操作,比如更新了一条数据,更新后得值是什么

在MySQL提交事务时,同时会写入binlog

与redo日志一样,binlog也有自己的刷盘策略,通过参数sync_binlog来控制

sync_binlog有2种值,对应着2种刷盘策略

  • sync_binlog = 0(默认): 提交事务时,先将binlog写入到os cache,再由系统决定什么时候刷盘,此时如果机器宕机,那么binlog可能就会丢失

  • sync_binlog = 1:提交事务时,强制刷盘,binlog永远不会丢失

binlog刷盘后,就会完成事务的最终提交,此时会把本次更新对应的binlog文件名和这次新的binlog在日志文件中的位置都写入到redo日志中,并写入一个commit标记

为什么要写入commit标记
为了保证redo日志和binlog日志的一致性,防止在事务成功提交前,MySQL突然宕机而导致redo、binlog的数据不一致,只有在redo和binlog数据一致的情况下,才能判定这次事务是成功提交的

os后台I/O线程随机将buffer pool中的数据刷到磁盘文件
事务提交完成后,标志着这次数据修改成功了,但此时磁盘文件中的数据和buffer pool中的数据还不一致,os后台I/O线程会在某个时间将buffer pool中的数据刷入磁盘。

此时即使机器宕机,数据也不会丢失了,在重启后,mysql会根据redo日志恢复之前做过的修改到缓冲池中,然后再由os将数据刷盘