一条update SQL在MySQL中结束生命历程

一条update SQL在MySQL中结束生命历程

一、Server层阶段：
1、连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
2、分析器：验证通过以后，分析器会对该语句分析，判断是否语法有错误等。
3、优化器：选择索引，生成执行计划。
4、执行器：根据优化器生成的执行计划，调用存储引擎API执行SQL。


二、InnoDB引擎层阶段：
1、事务执行阶段：
    进入InnoDB后引擎层，首先会判断该SQL涉及到的页是否存在于缓存中，如果不存在则从磁盘读取该行记录所在的数据页并加载到BP缓冲池。
    假设不存在，然后通过 B+Tree 读取到磁盘的索引页加载到BP缓冲池中 ，如何加载到BP缓冲池中：
    首先 通过 space id 和 page no 哈希计算之后把 索引页加载到指定的 buffer pool instance 中
    判断 free list 是否有空闲页可用(Innodb_buffer_pool_pages_free、 Innodb_buffer_pool_wait_free)，没有则淘汰脏页或者lru list的old页
    把数据页 copy到 free list中，然后加载到 lru list的 old区的 midpoint（头部）；
    通过二分法查找该页对应的记录，试图给这个SQL涉及到的行记录加上排他锁，过程如下：
    如果事务当前记录的行锁被其他事务占用的话，就需要进入锁等待；
    进入锁等待之后，同时判断会不会由于自己的加入导致了死锁；
    检测到没有锁等待和不会造成死锁之后，行记录加上排他锁。
    写逻辑的undo：
    将修改前的记录写入undo中
    修改当前行的值，填写事务编号，使用回滚指针指向undo log中的修改前的行
    从而构建回滚段，用于回滚数据和实现MVCC的多版本
    写redo log buffer：
    先判断redo log buffer是否够用，redo log buffer不够用就等待，体现在状态值 Innodb_log_waits 上;
    在 BP缓冲池 的 Lru list中old区的midpont中对该数据页的行记录的字段值做更新操作，并把修改之后的字段值写入到redo log buffer中
    并给LSN加上当前redo log写入的长度(写入长度为 length 的redo log，LSN就会加上 length)
    （因为redo group commit的原因，这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上）
    字段值在BP缓冲池更新成功以后，对应的数据页就是脏页了
    写binlog cache：
    同时修改的信息，会按照event的格式,记录到binlog_cache中。
    写change buffer:
    之后把这条sql, 需要在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge
    (随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)
    事务commit or rollback:
    此时update语句已经完成，需要commit或者rollback。这里讨论双1即sync_binlog=1 和 innodb_flush_log_at_trx_commit=1；

2、假设事务COMMIT
（1）事务的COMMIT 分为prepare阶段与commit阶段
        事务的COMMIT操作，在存储引擎层与server层之间采用的是内部XA；
        两阶段提交协议, 保证两个事务的一致性,这里主要保证redo log和binlog的原子性；
（2）redo log prepare:
        写入redo log处于prepare状态 并且写入事务的xid；
        将redo log buffer 刷新到 redo log磁盘文件中，用于崩溃恢复；  #刷盘的方式由 innodb_flush_log_at_trx_commit 决定
（3）binlog write&fsync: 执行器把 binlog cache 里的完整事务和 redo log prepare中的XID写入到binlog中
        dump线程会从binlog_cache里把event主动发送给slave的I/O线程，同时执行fsync刷盘(大事务的话这步非常耗时)，并清空binlog cache。
        #事务中写 binlog 的部分日志：
        190511 11:06:54 server id 123306  end_log_pos 439 CRC32 0x1c809de0     Xid = 614
        COMMIT/*!*/;
        binlog刷盘的方式由 sync_binlog 决定；binlog写入完成，事务就算是成功。
        事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog file 中。当sync_binlog为1时，当binlog落盘以后才会通知dump thread进行主从复制
（4）redo log commit: commit阶段，由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit，说明事务提交成功。
（5）事务提交成功，释放行记录持有的排他锁；
（6）当binlog和redo log都已经落盘以后，如果触发了刷新脏页的操作:
        先把该脏页复制到doublewrite buffer里，其次把doublewrite buffer里的刷新到共享表空间（ibdata），然后才是把脏页写入到磁盘中；
        这时候内存页与磁盘的数据页一致。

3、假设事务ROLLBACK
    如果事务因为异常或者被显式的回滚了，那么所有数据变更都要改回去。这里就要借助回滚日志中的数据来进行恢复了。
    对于in-place(原地)更新，将数据回滚到最老版本；
    对于delete+insert方式进行的，标记删除的记录清理删除标记，同时把插入的聚集索引和二级索引记录也会被直接删除。

三、至此，一条update SQL在MySQL中结束生命历程