Fork me on GitHub

一条sql执行的过程

MySql内部组件的结构

image-20220701101919878

如图所示:客户端发来一条SQL语句之后,Mysql内部组件会:

  • 连接器:管理客户端发来的连接,对其中的用户校验权限,管理MySql内部的连接池。
  • 词法分析器:对Sql语法、词法进行校验、分析。解析出一个语法树让MySql理解要去做什么事情。
  • 优化器:会对SQL可选的索引等条件计算成本,生成执行计划给执行器去执行。
  • 执行器:按照执行计划,去调用存储引擎的接口,来获取SQL语句的结果。

一条sql交互的过程

image-20220701101933226

binlog是什么

binlog是MySqlServer层实现的二进制逻辑日志,和redo log不同,redo log记录的是物理日志(表空间 + 区号 + 数据页 + 偏移量 + 修改内容),binlog的内容大概是(user表id = 1的记录name更新为xxx)是一个逻辑日志。同时redo log是innodb存储引擎实现事务中的持久性特性而存在的,在其他存储引擎不存在,而binlog是mysql都有的。

  • Mysql Server层逻辑日志。(所有引擎共享)
  • Binlog是逻辑日志,记录的是语句的原始逻辑。
  • Binlog是追加写的,不限制大小,不会像redo log覆盖几个文件循环写。

在事务Commit时,会写binlog,这个过程存在于redo log的二阶段提交过程。因为binlog常用于数据恢复和主从同步,所以要保证redo和binlog的一致性采用了两阶段。

Innodb存储引擎执行sql的过程(以更新语句为例)

image-20220701101948332

  1. 从磁盘加载数据到Buffer Pool中,innodb中的sql操作都需要加载数据到Buffer Pool中。加载数据都是以数据页的形式加载到内存中,Buffer Pool中也是数据页的形式存在的。
  2. 写undo log文件,一次更新语句需要写undolog保证事务的原子性(回滚时找到历史版本的数据)。同时undo log形成版本链,和ReadView来完成MVCC的并发控制。
  3. 更新Buffer Pool内存中记录的数据,更新完之后内存数据和磁盘数据不一致,数据页为脏页。
  4. 写redo log到内存中的redo log buffer,为后面redo log刷盘做准备。
  5. 准备提交事务的阶段,redo log刷新到磁盘,有几种策略(立即刷入、刷入OS缓存、不刷入)。这个阶段也可以理解为redo log的prepare阶段。(两阶段来保证redo log和binlog的一致性)
  6. 准备提交事务的阶段,在mysql server层的binlog写入磁盘,也有几种刷盘策略。
  7. 提交事务阶段,redolog写入commit标志,redolog的二阶段,此阶段之后事务才算真正提交。
  8. 之后的Buffer Pool会根据lru、flush链表的刷盘策略将脏页刷入磁盘。(此步不在事务阶段,线程异步刷入)
  • 为什么在事务阶段写这么多日志?
    因为innodb引擎要实现事务,undo log其实保证了事务回滚时的原子性,回滚到undo log版本链上的历史数据。

而redo log用于实现持久性,只要redo log和binlog的两阶段完成,就能保证这次变更是crash safe的,不会丢失。

binlog也会用于数据恢复和主从同步,是server层面的二进制逻辑日志,记录了语句信息。

  • 为什么不直接写入磁盘数据文件?

写那么多的日志都是在文件末尾追加写,相当于是追加写,是顺序IO;而因为更新数据要维护不同的索引树,数据的分布在磁盘上访问是随机IO,效率不是一个数量级的,这样innodb选择去写这些日志,异步线程去刷新内存中的脏页到磁盘上,来提高事务的效率。

  • 在压测数据库可以关注哪些指标?
    • QPS和TPS
    • IOPS:机器的随机IO能力。每秒可以执行多少个随机IO请求。这个指标很关键,访问磁盘中的数据就是随机IO,压测时候可以观察这个性能。
    • 吞吐量:机器的磁盘每秒可以读写的字节数据量。事务过程中会写redo binlog等日志文件,这个指标决定了大量redo log刷盘的性能。
    • latency:每写入一条数据的延迟。越低越好。
    • CPU负载:肯定是重要指标
    • 网络负载:如果带宽打满,肯定也是瓶颈。
    • 内存负载:内存吃紧肯定也是瓶颈。
-------------本文结束感谢您的阅读-------------

本文标题:一条sql执行的过程

文章作者:夸克

发布时间:2019年03月22日 - 10:03

最后更新:2022年07月01日 - 10:07

原始链接:https://zhanglijun1217.github.io/2019/03/22/一条sql执行的过程/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。