MySQL:一条SQL是如何执行的

一条SQL是如何执行的

  1. 首先客户端通过tcp/ip发送一条sql语句到server层的SQL interface

  2. SQL interface接到该请求后,先对该条语句进行解析,验证权限是否匹配

  3. 验证通过以后,分析器会对该语句分析,是否语法有错误

  4. 接下来是优化器器生成相应的执行计划,选择最优的执行计划

  5. 之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL锁,则等待,如果没有,则加在该表上加短暂的MDL(S)
    (如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件)

  6. 进入到引擎层,首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息

  7. 通过元数据信息,去lock info里查出是否会有相关的锁信息,并把这条update语句需要的锁信息写入到lock info里(锁这里还有待补充)

  8. 然后涉及到的老数据通过快照的方式存储到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里会记录多条页面的修改信息。

因为group commit的原因,这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上

  1. 同时修改的信息,会按照event的格式, 记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数,
    一旦commit之后,dump线程会从binlog_cache里把event主动发送给slave的I/O线程)

  2. 之后把这条sql,需要在二级索引上做的修改,写入到change buffer page,等到下次有其他sql需要读取该二级索引时,再去与二级索引做merge
    (随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)

  3. 此时update语句已经完成,需要commit或者rollback。这里讨论commit的情况,并且双1

  4. commit操作,由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性),
    所以提交分为prepare阶段与commit阶段

  5. prepare阶段,将事务的xid写入,将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)

  6. commit阶段,由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit

  7. 当binlog和redo log都已经落盘以后,如果触发了刷新脏页的操作,先把该脏页复制到doublewrite buffer里,把doublewrite buffer里的刷新到

innodb 架构

MySQL架构

  • server层:连接管理、解析分析SQL,操作存储引起
    • 连接器:连接管理,权限验证
    • 查询缓存:缓存查询结果,命中直接返回
    • 分析器:词法分析、语法分析
    • 优化器:生成执行接话,索引选择
  • 存储引擎:存储数据,提供读写接口个
    • MyISQM
    • InnoDB

查询缓存

查询缓存是MySQL8.0以下版本为了提高查询的速度都一个功能。

查询缓存的更新策略是当表中的数据发生变化,则删除表的查询缓存,弊端是当表存在大量的更新维护缓存一个大的开销,收益没有开销大。查询缓存也是有它的用武之地的在表数据不经常更新的情况下是个不错的选择。

redo log(重做日志)

为什么要有redo log?为了实现事务的持久性,innodb增加了redo log,它是innodb特有的日志,redo日志是一个环形的日志记录器,当有一个记录要变更时,innodb先将变更操作写入redo log,同时存储引擎会在适当的时候将这个更新的操作写到磁盘。

redo log的写入过程。redo log 是一个环形日志记录器,其中write pos是当前日志写的位置、checkpoint是日志擦除的位置,一个写入一个擦出。如果当write pos追上checkpoint(写满了)此时就要停下所有的更新操作,将已写的更新到数据文件。

redo log的地位。redo log保证了MySQL即使发生异常或重新,记录的日志都不会丢失,保证了事务的持久性。MySQL这种技术也被称为WAL(Write Ahead Loggin)先写日志,他的关键点是:先写日志、再落盘。

binlog(归档文件)

binlog能用来做备份恢复和主从复制。

binlog的类型:

  • row:记录日志被修改的形式,只有value,不存在连表
    • 优:能够保证记录数据的一致性,每个修改语句的值是一样的
    • 缺:日志较大占空间
  • statement:记录执行的SQL语句
    • 缺:会引起数据不一致,例如SQL中使用了date函数,则再次执行时间就不一样了
  • mixed:row和statement的折中方案,MySQL判断SQL是否会引发主备不一致,如果会引起就用row,否则用statement。

binlog与redo log的区别是:

  1. redo log是innodb引擎特有的;binlog是MySQL Server层的实现,所有引擎都可以使用
  2. redo log是物理日志,记录的是“在某个物理页上做了什么”;binlog是逻辑日志,记录了SQL语句原始逻辑或操作,例如给id=2的字段c字段加1.
  3. redo log是循环写的,大量写会导致空间紧张;binlog是追加写的,文件到一定大小就切换下一个

以一个update操作为例,redo log与binlog的交互流程是这样的:

  1. 执行器调用存储引擎的接口查找id=2的数据,存储引擎依靠B+树结构进行搜索,如果这个记录在内存中就直接返回给执行器;否则,需要将该记录从磁盘读入内存,然后返回。
  2. 执行器拿到记录,把对应的值修改,得到一个新纪录,然后在调用存储引擎层的接口写入新数据。
  3. 存储引擎将数据更新到内存中(innodb_buffer_pool),同时将这个操作记录到redo log中,此时redo log处于prepare状态。然后告知执行器完成了可以提交事务。
  4. 执行器生成这个操作的binlog,并将其写入磁盘。
  5. 执行器调用存储层的事务提交接口,引擎把刚刚写入的redo log的状态改为commit状态,更新完成。

两段提交

两段协议分为两步:

  1. 预提交
  2. 最终提交

使用两段提交为了保持redo log与binlog的一致。举个例子:

写reod log后Server挂掉了,之后的binlog没有写,此时因为redo log还没有最终提交,崩坏恢复对未提交的操作进行回滚,此时数据仍是一致性的。

数据库系统之外,两段协议已常用在跨系统保持数据的一致性。

总结

MySQL服务端分为两层:Server层和存储引擎层。

Server层负责连接管理, SQL解析、优化和执行管理;存储引擎负责数据存储及索引维护。

两个重要日志模块:redo log、binlog。

两段提交协议(2PC)。

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×