MySql一条查询语句的执行流程是怎么样的?

1.前言

一条sql语句到底在执行时经历了什么?探究这个问题是学习mysql的重要步骤,面试时常被问到,也使得学习mysql时也有了知识框架的支撑,明白我们背的知识点到底用在哪里,笔者觉得这一点还是很重要的。
注:对一个知识点的总结不仅包含知识点本身,还包含对该知识点的联想,这个联想是在面试时可能被追问的,也可以自己主动说出来(我还知道。。。)加分的。

2.知识点

MySQL 执行流程是怎样的?

首先要知道的是,我们可以把mysql分成两层,server层和数据库引擎层,前者主要是对我们的查询进行处理(主要包括 {连接器},{查询缓存}、{解析器}、{预处理器、优化器、执行器} 等),后者是数据真正存储的地方(从 MySQL 5.5 版本开始, InnoDB 成为了 MySQL 的默认存储引擎)。

一条查询的执行流程如下:

第一步:通过连接器连接 MySQL 服务

mysql -h$ip -u$user -p

[连接器联想1]: 连接经过TCP 三次握手,断开经过四次挥手
[连接器联想2]: 如果用户密码都没有问题,连接器就会获取该用户的权限,然后保存起来,后续该用户在此连接里的任何操作,都会基于连接开始时读到的权限进行权限逻辑的判断,意思是管理员修改已登录用户的权限需要等他重新登录才生效
[连接器联想3]: 如何查看 MySQL 服务被多少个客户端连接了?show processlist
[连接器联想4]: 空闲连接会一直占用着吗?MySQL 定义了空闲连接的最大空闲时长,由 wait_timeout 参数控制的,默认值是 8 小时(28880秒),如果空闲连接超过了这个时间,连接器就会自动将它断开。
[连接器联想5]: MySQL 的连接数有限制吗?最大连接数由 max_connections 参数控制,超过这个值,系统就会拒绝接下来的连接请求,并报错提示“Too many connections”。
[连接器联想6]: 怎么解决长连接占用内存的问题?MySQL 的连接也跟 HTTP 一样,有短连接和长连接的概念,长连接的好处就是可以减少建立连接和断开连接的过程,但是,使用长连接后可能会占用内存增多,因为 MySQL 在执行查询过程中临时使用内存管理连接对象,这些连接对象资源只有在连接断开时才会释放。有两种解决方式。第一种,定期断开长连接。第二种,客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口来重置连接,达到释放内存的效果。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。
[连接器联想7]: 连接器的工作?与客户端进行 TCP 三次握手建立连接;校验客户端的用户名和密码,如果用户名或密码不对,则会报错;如果用户名和密码都对了,会读取该用户的权限,然后后面的权限逻辑判断都基于此时读取到的权限;

第二步:查询缓存

连接器得工作完成后,客户端就可以向 MySQL 服务发送 SQL 语句了,MySQL 服务收到 SQL 语句后,就会解析出 SQL 语句的第一个字段,看看是什么类型的语句。

如果 SQL 是查询语句(select 语句),MySQL 就会先去查询缓存( Query Cache )里查找缓存数据。

但是其实查询缓存挺鸡肋的。对于更新比较频繁的表,查询缓存的命中率很低的,因为只要一个表有更新操作,那么这个表的查询缓存就会被清空。

所以,MySQL 8.0 版本直接将server层查询缓存删掉了。

第三步:解析SQL

在正式执行 SQL 查询语句之前, MySQL 会先对 SQL 语句做解析,这个工作交由「解析器」来完成。

解析器会做两件事情:词法分析语法分析

[解释器联想1]: 词法分析:MySQL 会根据你输入的字符串识别出关键字出来,例如,SQL语句 select username from userinfo,在分析之后,会得到4个Token,其中有2个Keyword,分别为select和from.
[解释器联想2]: 语法分析:根据词法分析的结果,语法解析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法,如果没问题就会构建出 SQL 语法树,这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
[解释器联想3]: 解如果我们输入的 SQL 语句语法不对,就会在解析器这个阶段报错。(释器的主要作用)
[解释器联想4]: 解释器只负责检查语法和构建语法树,但是不会去查表或者字段存不存在。

第四步:执行 SQL

解析SQL无误后,执行SQL需要经过三个步骤:预处理器、优化器、执行器。

预处理器
  • 检查 SQL 查询语句中的表或者字段是否存在;
  • select * 中的 * 符号,扩展为表上的所有列;
优化器

优化器主要负责将 SQL 查询语句的执行计划确定下来,比如在表里面有多个索引的时候,优化器会基于查询成本的考虑,来决定选择使用哪个索引。

[优化器联想1]: 要想知道优化器选择了哪个索引,我们可以在查询语句最前面加个 explain 命令,这样就会输出这条 SQL 语句的执行计划。explain select * from product where id = 1
[优化器联想2]: 一般来讲普通索引查询效率高于主键索引,当索引覆盖时会先考虑普通索引的B+树上查询,这就是执行计划,是优化器决定的。

执行器

确定了执行计划,接下来 MySQL 就真正开始执行语句了,在执行的过程中,执行器就会和存储引擎交互了,交互是以记录为单位的。

  • 主键索引查询 select * from product where id = 1; 让InnoDB引擎通过主键索引B+树搜索id=1的记录。
  • 全表扫描 select * from product where name = 'iphone'; 查询条件没有用到索引,触发全表扫描,查询每一条记录判断是否满足条件。
  • 索引下推 (MySQL 5.6 推出的查询优化策略)

[索引下推联想1]: 索引下推能够减少二级索引在查询时的回表操作,提高查询的效率,因为它将 Server 层部分负责的事情,交给存储引擎层去处理了。select * from t_user where age > 20 and reward = 100000;不使用索引下推(MySQL 5.6 之前的版本)时,定位到 age > 20 的一条记录,获取主键值,然后进行回表操作,将完整的记录返回给 Server 层,Server 层再判断该记录的 reward 是否等于 100000。而使用索引下推后,判断记录的 reward 是否等于 100000 的工作交给了存储引擎层:定位到 age > 20 的第一条记录,存储引擎定位到二级索引后,先不执行回表操作,而是先判断一下该索引中包含的列(reward列)的条件(reward 是否等于 100000)是否成立。如果条件不成立,则直接跳过该二级索引。如果成立,则执行回表操作,将完成记录返回给 Server 层。

MySQL 执行流程是怎样的?总结:

(总结只是简单总结,也就是被问到时该说的,上面的知识点,是可能被追问时涉及的,或者自己说出来的加分项。)

  • 连接器:建立连接,管理连接、校验用户身份;
  • 查询缓存:查询语句如果命中查询缓存则直接返回,否则继续往下执行。MySQL 8.0 已删除该模块;
  • 解析 SQL,通过解析器对 SQL 查询语句进行词法分析、语法分析,然后构建语法树,方便后续模块读取表名、字段、语句类型;
  • 执行 SQL:执行 SQL 共有三个阶段:
    • 预处理阶段:检查表或字段是否存在;将 select * 中的 * 符号扩展为表上的所有列。
    • 优化阶段:基于查询成本的考虑, 选择查询成本最小的执行计划;
    • 执行阶段:根据执行计划执行 SQL 查询语句,从存储引擎读取记录,返回给客户端;

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部