（转）一条SQL更新语句是如何执行的

名词

1. MySQL 里经常说到的 WAL 技术，Write-Ahead Logging 第一个日志模块 redo log 也叫日志重写，是InnoDB 引擎特有的日志

              - write pos and checkpoint
              - crash-safe

1. 另外一个日志模块binlog 归档日志，是server（功能层面）具有的日志 ## 参数
2. innodb_flush_log_at_trx_commit redo log 用于保证 crash-safe 能力,innodb_flush_log_at_trx_commit=1,表示 表示每次事务的 redo log，都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启，不会丢失数据
3. sync_binlog 表示每次事务的 binlog 都持久化到磁盘，这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启，binlog日志不会丢失

执行update语句，innodb引擎干了什么

1. prepare阶段
2. 写binlog
3. commit 阶段

            当在2之前崩溃时，
            重启恢复：后发现没有commit，回滚。备份恢复：没有binlog，两个log数据一致
            当在3之前崩溃时，
            重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog，两个log数据 一致。
            总结：一个事务正常执行是要commit 才算完，但是崩溃恢复过程的话，可以接受“redolog prepare 并且binlog完整” 的情况。

完整的一次update，数据库都干了什么

1. 首先客户端通过tcp/ip发送一条sql语句到server层的SQL interface
2. SQL interface接到该请求后，先对该条语句进行解析，验证权限是否匹配SQL interface接到该请求后，先对该条语句进行解析，验证权限是否匹配
3. 验证通过以后，分析器会对该语句分析,是否语法有错误等验证通过以后，分析器会对该语句分析,是否语法有错误等
4. 接下来是优化器器生成相应的执行计划，选择最优的执行计划接下来是优化器器生成相应的执行计划，选择最优的执行计划
5. 之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL，则等待。 如果没有，则加在该表上加短暂的MDL(S)之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL，则等待。 如果没有，则加在该表上加短暂的MDL(S) (如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件)
6. 进入到引擎层，首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息进入到引擎层，首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息
7. 通过元数据信息,去lock info里查出是否会有相关的锁信息，并把这条update语句需要的 锁信息写入到lock info里(锁这里还有待补充)通过元数据信息,去lock info里查出是否会有相关的锁信息，并把这条update语句需要的 锁信息写入到lock info里(锁这里还有待补充)
8. 然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,并且记录undo log修改的redo (如果data page里有就直接载入到undo然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,并且记录undo log修改的redo (如果data page里有就直接载入到undo page里，如果没有，则需要去磁盘里取出相应page的数据，载入到undo page里)
9. 在innodb_buffer_pool的data page做update操作。并把操作的物理数据页修改记录到redo log buffer里 由于update这个事务会涉及到多个页面的修改，所以redo log buffer里会记录多条页面的修改信息。在innodb_buffer_pool的data page做update操作。并把操作的物理数据页修改记录到redo log buffer里 由于update这个事务会涉及到多个页面的修改，所以redo log buffer里会记录多条页面的修改信息。因为group commit的原因，这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上
10. 同时修改的信息，会按照event的格式,记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数, 同时修改的信息，会按照event的格式,记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数,一旦commit之后，dump线程会从binlog_cache里把event主动发送给slave的I/O线程)
11. 之后把这条sql,需要在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge之后把这条sql,需要在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge(随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大）
12. 此时update语句已经完成，需要commit或者rollback。这里讨论commit的情况，并且双1此时update语句已经完成，需要commit或者rollback。这里讨论commit的情况，并且双1
13. commit操作，由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性), 所以提交分为prepare阶段与commit阶段commit操作，由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性), 所以提交分为prepare阶段与commit阶段
14. prepare阶段,将事务的xid写入，将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)prepare阶段,将事务的xid写入，将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)
15. commit阶段，由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commitcommit阶段，由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit
16. 当binlog和redo log都已经落盘以后，如果触发了刷新脏页的操作，先把该脏页复制到doublewrite buffer里，把doublewrite buffer里的刷新到共享表空间，然后才是通过page cleaner线程把脏页写入到磁盘中.当binlog和redo log都已经落盘以后，如果触发了刷新脏页的操作，先把该脏页复制到doublewrite buffer里，把doublewrite buffer里的刷新到共享表空间，然后才是通过page cleaner线程把脏页写入到磁盘中

参考地址：https://blog.csdn.net/weixin_39602697/article/details/84863292