一条SQL的心路历程

一条SQL的心路历程

今天在极客时间上学习丁奇大佬的《MySQL 45讲》的时候，看到了一条高质量的评论，结合我自己的理解，将它分享出来。喜欢的同学可以购买原版课程进行学习，里面的内容很丰富。

当我们在客户端执行一条SQL的时候，例如：

update table set a=a+1 where id=1；

此时在MySQL内部到底发生了哪些事情？

1.首先客户端通过TCP/IP协议发送一条update的SQL语句到Server层的SQL接口组件。该过程会经历连接的三次握手。 2.SQL 接口组件接到该请求后，先对该条语句进行解析，验证权限是否匹配，此处验证的是用户账号的权限。 3.验证通过以后，分析器会对该语句分析,是否语法有错误等，分析过程分为词法分析和语法分析，词法分析包含select关键字解析、字段列名解析等，语义分析则告诉用户输入的语句是否有语法错误。 4.接下来是优化器器生成相应的执行计划，优化器会对比多个执行计划，最后选择最优的执行计划 5.之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL锁，则等待。 如果没有，则加在该表上加短暂的MDL锁，如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件。 6.进入到innodb引擎层，首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息 7.通过元数据信息,去lock info里查出id=1的记录上是否会有相关的锁信息，如果有的话，需要按照指定的规则进行等待，等待过程完成后把这条update语句需要的锁信息写入到lock info里，执行这条SQL语句 8.然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,如果data page里有就直接载入到undo page里，如果没有，则需要去磁盘里取出相应page的数据，载入到undo page里，并且记录undo log修改的redo log。

9.在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级别的参数, 一旦commit之后，dump线程会从binlog_cache里把event主动发送给slave的I/O线程 11.之后把这条sql,需要在二级索引上做的修改，写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge，随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大。 12.此时update语句已经完成，需要commit 13.commit操作，由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性),所以提交分为prepare阶段与commit阶段 14.prepare阶段,将事务的xid写入，将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时) 15.commit阶段，由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit 16.当binlog和redo log都已经落盘以后，如果触发了刷新脏页的操作，使用memcpy函数先把该脏页复制到doublewrite buffer里，把doublewrite buffer里的脏页按照1MB的顺序刷新到共享表空间，然后通过page cleaner线程把脏页写入到磁盘中。