一条sql语句在MySQL的执行过程

了解sql语句在MySQL的执行步骤,对我们从查询语句、mysql配置、数据恢复等方面优化mysql的性能有很大帮助。

MySQL整体架构

以下为MySQL数据库中SQL语句在的简要执行流程

在这里插入图片描述

简单来说 MySQL 主要分为 Server 层和存储引擎层。Server层主要包括连接器、查询缓存、分析器、优化器、执行器等,还有一个通用binlog日志模块(用于整个数据库操作记录,主从复制的关键)。存储引擎层主要负责数据的存储和读取。

连接器

客户端想要对数据库进行操作时,连接器就是用来负责跟客户端建立连接、获取权限、维持和管理连接的。连接器支持短连接也支持长连接,同时为了避免频繁创建和销毁连接造成性能损失,可选择利用连接池进程管理,如druid,cp30等。

查询缓存(MySQL 8.0 版本后移除)

查询缓存主要用来缓存我们所执行的 select语句以及该语句的结果集。如果开启了查询缓存,执行查询语句的时候,会先查询缓存。如果缓存 key 被命中,就会直接返回给客户端。在数据变换频繁的表中,是不推荐使用的,当一张表的数据发生变化,其所有缓存都将清空。

分析器

分析器的工作主要是对要执行的SQL语句进行解析,最终得到抽象语法树。

一、词法分析。提取关键字(Token),比如 select,提出查询的表,提出字段名,提出查询条件等等

二、语法分析。把提取出的Token转换为抽象语法树后进行检验。主要就是判断你输入的 sql 是否正确,是否符合 MySQL 的语法

优化器

查询优化器会找出执行该语句所有可能使用的方案,然后选择一条最优查询路径,即MySQL认为的效率最高的方式,并生成执行计划。比如你一个表中创建了多个索引,优化器会根据IO和CPU成本,选出代价最小的索引进行执行。

查询成本计算可参考:https://www.jianshu.com/p/aecdcc2babdd

可通过SQL语句前添加上 explain 关键字查看执行计划(重点关注type、key、Extra字段);

执行器

根据执行计划完成SQL语句的操作,执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会去调用引擎的接口,返回接口执行的结果。

执行引擎

存储引擎是对底层物理数据执行实际操作的组件,为Server层提供各种操作数据的 API,数据是被存放在内存或者是磁盘中的。以InnoDB为例,如下图。

img

InnoDB存储引擎整体分为内存架构(Buffer Pool缓冲池)和磁盘架构(undolog和redolog)

缓冲池(BUffer Pool)

起到一个缓存的作用。MySQL 的数据最终是存储在磁盘中的,如果没有 Buffer Pool,那么每次的数据库请求都会磁盘中查找,这样必然会存在 IO 操作。但是有了 Buffer Pool,只有第一次在查询的时候会将查询的结果存到 Buffer Pool 中,这样后面再有请求的时候就会先从缓冲池中去查询,如果没有再去磁盘中查找,然后在放到 Buffer Pool 中.

undolog

undolog记录的是数据被操作前的样子,用于数据事务提交后的事务回滚,保证事务数据原子性;

redolog(InnoDB特有)

redolog记录的是数据被操作后的样子,保证即使MySQL发生了异常重启,数据也能恢复,即保证事务数据一致性

SQL语句在Server层查询流程

那么究竟一条 sql 语句是如何执行的呢,下面我们分析一下一条查询语句的执行流程

select user_id、username from t_user where username = "张三" and sex = 1

1、客户端发起查询请求,与连接器建立连接,连接器确定用户是否有查询权限,没有权限,直接返回错误信息,有执行下一步。

2、查询缓存(MySQL8.0 以前),以这条SQL语句为key在内存缓冲池中是否有结果,有直接返回结果,无则执行下一步。

3、分析器进行词法分析,提取出操作为select, 表名为 t_user, 查询字段为user_id、username,查询条件为username=“张三” 和 sex=1 ,把提取的Token转换为抽象语法树.如下图

在这里插入图片描述

接下来判断这个 sql 语句是否有语法错误,比如关键词是否正确等等,如果检查没问题就执行下一步。

4、优化器列出可能的执行方案

  a. 先查询t_user表中username=“张三”的学生,然后判断是否性别为男。
  b. 先找出学生中性别是男的学生,然后再查询username为“张三”的学生。

接下来计算两个查询计划的成本,若username字段为索引,usrname和sex为联合索引、或查询条件可能致使索引失效,查询字段为*,造成成全表扫描,都有可能影响执行方案的选择。

5、执行器进行权限校验,如果没有权限就会返回错误信息,如果有权限就执行器会调用数据库引擎接口,返回引擎的执行结果。

6、执行引擎根据执行计划查询数据,并把结果集返回客户端

执行引擎对有主键索引与无索引查询的区别

磁盘上的数据是以数据页的格式存储的,数据页的之间通过双向链表的格式相互引用,每一个数据页中有多个数据行通过单链表的增量形式连接。数据页中有一张目录页(page directory)里面根据数据行的主键存放了一个目录,数据行被分散存储到不同的槽位里去的。

在这里插入图片描述

如果一张表中有建立主键索引,磁盘在存储数据时会建立一颗B+索引树,非叶子节点为索引页,叶子节点为数据页。

在这里插入图片描述

无索引查询

从第一个数据页开始把数据页从磁盘加载到执行引擎的缓存池中,进行遍历,若该缓存页(数据页)没有查找到,根据双向链表查找下一个数据页,直到查到为止.

有主键索引查询

先通过二分查找在索引页查找到所属的数据页,把数据页加载到缓存池Buffer pool中,到缓存页(数据页)的页目录里根据主键进行二分查找。在页目录中定位到主键对应的数据在哪个槽位里后进行遍历。

详细可参考MySQL索引

总结流程

权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎

InnoDB存储引擎的更新操作

上面介绍的是查询语句执行流程,而一条更新语句的的查询过程基本是沿用上面的步骤,但在更新的时候要记录日志。下面我们来探索一下在InnoDB存储引擎是如何保证更新语句的成功执行。以下面语句为例(user_id=1这条记录原始值为“张三”)。

update t_user set username = "李四" where user_id = 1

1、InnoDB会先去BufferPool中去查找user_id=1的数据,没找到就会去磁盘中查找,如果查找到就会将这条记录加载到缓冲池BufferPool中,由于是更新操作,InnoDB会对这条记录加锁。

2、我们都知道SQL语句执行前默认是开启事务的,考虑到更新失败后的数据回滚,把更新前的数据写入undolog中

3、更新缓存池BufferPool的值为“李四”

在这里插入图片描述

4、此时内存中的数据已经修改,但磁盘中的数据还未更新,为避免数据的丢失,需要把更新的值写入redo log Buffer中,

5、此时就可以准备事务的提交了,提交事务的同时会根据一定策略把redo log从buffer中刷入磁盘。此时若MySQL崩溃,重启可根据redo log日志把数据重新写入BufferPool中,避免了数据的丢失。此时redo log 处于 prepare 状态

可通过innodb_flush_log_at_trx_commit 参数来设置redo log刷盘策略。值为 0 表示不刷入磁盘; 值为 1 表示立即刷入磁盘 ;值为 2 表示先刷到 os cache

在这里插入图片描述

6、到这里InnoDB层面的操作就差不多了,接下来执行器会生成更新操作的 binlog,并把binlog 日志写入磁盘

7、把本次更新的binlog文件名称和本次更新的binlog日志在文件里面的位置,都写入到redo日志文件中,同时在redo log日志中打上一个commit标记。此时redo log 处于 prepare 状态。至此,事务提交成功。

8、事务提交成功后,后台会另起io线程把脏数据刷入磁盘中。

在这里插入图片描述

为什么写了redolog日志还要写binlog日志

性质不同,redolog是InnoDB独有的,文件大小有限,主要用于崩溃恢复,binlog是MySQL层面的日志,通过追加的方式记录,主要用于主从复制和数据恢复。

为什么要两阶段提交

我们看到redolog的提交分为两阶段,prepare和commit。为什么不先写undolog或者binlog呢。第5步和第6步都是事物开始的时候,一旦任意一步执行完毕因为系统宕机了。只写了两个中的任意一个。此时就会导致redo和binlog日志不一致的情况。并且即使执行了5,6步成功了,也不能保证redo和binlog日志是一样的。这就会导致了数据不一致的问题。而commit就是保证连个日志数据一致,事务才算提交成功。

总结流程

BufferPool查找数据—》写入undo log---->修改内存数据-----》写入redo log buffer------>提交事务时,把redo log刷盘-----》binlog 刷盘----》redo log 完成commit标记—》内存中脏数据刷入磁盘

优化总结

查询语句

1、查询的时候用什么字段查什么字段,

2、避免索引失效(如子查询、!=、like 以%开头,复合索引不在第一列索引、使用函数)

MySQL参数配置

1、InnoDB很依赖Buffer Pool ,对查询性能有很大优化,可通过innodb_buffer_pool_size参数来设置缓冲池大小(内存的50%–60%)

2、redolog刷盘策略,innodb_flush_log_at_trx_commit 参数,MySQL默认为1,立即刷盘,保证数据安全性。0为不刷盘,纯内存操作,一旦MySQL宕机,很容易造成数据丢失,适合不重要的数据。2为写入OS cache,间隔写入磁盘,也可能造成数据丢失,适合高并发写入。

参考资料

书籍:高性能MySQL(第三版)

博客:

一条sql语句经历了哪儿些过程

https://juejin.cn/post/6920076107609800711

MySQL索引

  • 7
    点赞
  • 20
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值