MySQL - 01一条SQL的执行过程

KAID
235 阅读5分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

theme: juejin

MySQL - 一条SQL的执行过程

update table set age = 18 where id = 1;

执行这条SQL,MySQL都经历了哪些？

image.png

1.客户端与服务端连接

  1. 首先是经典的TCP三次握手建立连接
  2. 然后验证用户名、密码,查询权限
  3. 将权限缓存到服务端的系统表中
  4. 在这条连接断开前，都会基于缓存下的权限来约束操作，也就是说：连接期间，服务器更新了这个客户端的权限后，这个连接操作是不受影响的；直到连接断开后，重新建立连接时，会重新查询权限缓存表中

2.服务端 Server层

2.1 Server层架构分为如下：

  1. 连接器 是与客户端建立连接通信
  2. 查询缓存
    1. MySQL利用缓存区提高查询速度；
    2. 但是弊大于利，因为对表的一次更新，就会使缓存区中这个表的所有缓存数据失效了，命中率非常低;
    3. 一般的业务场景建议关闭查询缓存；
    4. 在MySQL8.0的版本，直接将查询缓存的整块功能都删掉了，也就是说，8.0之后，Server层就没有查询缓存了;
    5. PS：如果你的表数据是静态的(比如省市地区、过往年份的历史数据)，不会进行更新操作，那么你就可以开启查询缓存，会提高查询效率；
  3. 分析器
    1. 词法分析，识别关键词：select、update、delete、insert
    2. 语法分析，是否满足MySQL语法
  4. 优化器
    1. 选择走哪个索引(MySQL内部会按照某种算法 估算走每个索引的成本，然后取最优)
    2. 处理join关联顺序
  5. 执行器
    1. 首先是校验权限
    2. 根据表的储存引擎定义，调用引擎对应接口，执行语句
  6. 其它：所有内置函数、存储过程、触发器、视图等都在Server层实现

3.服务端 储存引擎层

image.png

  1. 首先按照SQL语句，从Buffer Pool缓冲池中查询数据是否存在。如果不存在，则就到磁盘中查数据，将所在页的整页数据都加载到 buffer pool 缓存池中；
  2. 开始写undolog日志，记录回滚日志，用于事务回滚
  3. 修改 buffer pool中的数据
  4. 写 redoLog日志到 redoLogBuffer中，标记状态为 prepare
  5. 写 binLog到binlog cache中，等到整个事务提交时，再把binlog cache写到binlog文件中；（ 用于崩溃恢复、主备同步）
  6. 写binlog成功后，将redolog的状态标记为commit
  7. 提交事务

注：

  • 4、5、6三步组成了两阶段提交，保证了binlog、redolog日志一致性，进而保证数据一致性
  • 后台有一个线程，每隔1S开始将 redoLog buffer中的数据，写到磁盘文件中；InnoDB将 写redoLog磁盘的动作分为两阶段:
    • 一是 write到磁盘，但是没有持久化(fsync)；物理上是在page cache里面
    • 二是 持久化到磁盘，对应的是 hard disk
    • 具体的写入策略，由参数可配置：innodb_flush_log_at_trx_commit
  • Buffer Pool缓存池大小一般为4G，可配置
    • 分为young区 (5/8) 和old (3/8) 区；根据缓存数据的查询命中率来区分；
    • 把内存中的数据写入磁盘的过程，术语是 flush。InnoDB 会在后台主动flush。 ps：如果内存中的数据还没有flush到磁盘，服务器宕机了；则此时我们可以用redoLog日志来恢复内存中的数据
    • 干净页是指：内存中的页数据和磁盘中的数据是一致的； 脏页是指：内存中的页数据和磁盘中的数据不一致，还没来得及flush；
    • 查询数据时，数据没有在buffer Pool中，buffer Pool空间又已经满了；此时，就要清除掉一页数据，然后才能将此次要查询的数据页加载到缓存池中处理；如果该页是脏页，则还需将脏页的数据刷写到磁盘中，才能清除脏页；如是干净页则不用刷数据；

4.返回数据

储存引擎层执行完毕后，就会返回结果给客户端；MySQL是 “边读边发”，这个概念很重要

  1. 如果客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长；

  2. Server层可以理解为两个阶段，查询数据阶段和发送数据阶段。

    MySQL查询语句进入执行阶段后，首先把状态设置成“Sending data”； 发送执行结果的列相关信息给客户端，把状态设置成"Sending to client"; 继续执行查询语句的流程； 执行完成后，把状态设置成空字符串。

    • sending data：可以理解为在存储引擎中执行

    • Sending to client：

      1、获取一行，写到net_buffer中。默认16K

      2、重复获取行，直到net_buffer写满，调用网络接口发出去。

      3、如果发送成功，就清空net_buffer，继续取下一行，并写入net_buffer

      4、如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈写满了，进入等待。直到网络栈重新可写，在继续发送。

  3. MySQL采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在server端保存完整的结果集；

avatar
文章
阅读
粉丝