Lou Chen

MySQL中的SQL的执行流程探索

Lou.Chen2022年11月9日
大约 5 分钟

第一步：连接器

主要步骤：

  • 与MySQL客户端建立TCP连接
  • 校验客户端的用户名和密码
  • 读取校验用户的权限，后面操作都基于此权限操作

其中的其他信息如下：

连接MySQL客户端

mysql -h[IP] -u[USER] -p[PASSWORD]

  • -h:指定主机地址。本地地址可以省略
  • -u:指定用户名
  • -p:指定密码。可以在在命令行省略，保证密码不被泄露。进入交互页面输入密码

查看 MySQL 服务被多少个客户端连接

show processlist

image-20221108134702781

Id为183和184的连接为Sleep,这意味着该用户连接完 MySQL 服务就没有再执行过任何命令，也就是说这是一个空闲的连接，空闲时长为Time对应的值。空闲连接在指定时长后会自动断开

查看空闲连接自动断开时长

show variables like 'wait_timeout'; 默认为28800秒，也就是8小时

image-20221108135314308

强制断开空闲连接

kill connection +[Id];

查看MySQL最大连接数

show variables like 'max_connections'; 默认最大连接数为151

image-20221108135713208

第二步：查询缓存

如果是SELECT，那么首先会去缓存中查询数据，缓存中的数据是以key-value保存在内存中，其中查询语句为key,查询语句的结果为value。

如果查询语句命中缓存，那么会先去缓存中查询后返回

如果查询语句没有命中缓存，那么继续往下执行，执行完毕后，将查询结果放入缓存

**缺点：**对于更新比较频繁的表，缓存命中率很低。因为只要表进行更新，那么此表的查询缓存就会清除。

在MySQL 8.0中，查询缓存直接移除。

在MySQL 8.0之前的版本，如果想关闭查询缓存，可以通过将参数 query_cache_type 设置成 DEMAND。

这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool。

第三步：解析SQL

解析器

解析SQL主要由解析器来做。

解析器会做以下几件事：

  • **词法分析：**根据操作SQL构建SQL语法树，后续方便直接取出SQL类型，表名，类型，字段名，where条件等

  • **语法分析：**根据词法分析结果，判断该SQL语法的正确性。若语法错误，则会报错。例如将from写为form

若表或者字段不存在，并不是在解析器给出的错误，**解析器**只会负责构建语法树和检查语法

第四步：执行SQL

每条SELECT查询语句主要分为以下三个阶段：

  • prepare预处理阶段，由预处理器来做
  • optimize优化阶段，由优化器来做
  • execute执行阶段，由执行器来做

预处理器

  • 检查SQL查询语句中的表或者字段是否存在

  • 将SELECT中的*扩展替换为表上的所有列

在MySQL8.0中，若报表不存在或者字段不存在则是在预处理阶段报错的

优化器

优化器的主要指责是在此阶段将SQL查询语句的执行方案确定下来。比如表中有多个索引，那么优化器就会基于查询成本的考虑，决定使用哪个索引。

可以手动使用执行计划EXPLAIN SELECT... 查看使用到哪个索引

  • explain select * from tbl_user where id=1; 其中id为主键

    • 查询计划中的key:PRIMARY 表示用到了主键索引

  • explain select id from tbl_user where id>2 and age=2; 其中id为主键，age为普通索引

    • 查询计划中的key:idx_Age 表示用到了age索引。Exta:Using index 表示为查询为覆盖索引
    • 因为查询语句为覆盖索引，那么通过二级索引age查询就比主键索引查找快，因为二级索引的叶子结点的key存储的就是主键值，拿到主键值再判断即可。

执行器

在该阶段就要执行正真正的SQL语句了，该阶段由执行器执行，并且由执行器调用存储引擎接口进行交互，存储引擎查询符合条件的数据并返回给执行器

总结

MySQL 的架构共分为两层：**Server 层 **和 存储引擎层

存储引擎层：复制数据的存储和提取

Server层：负责建立连接，查询缓存、解析SQL(解析器词法分析和语法分析)、执行SQL(预处理器、优化器、执行器)等都在Server层实现

  • 连接器：建立连接、校验用户密码、校验用户身份权限
  • 查询缓存：首先查询缓存，缓存以key-value形式存在内存中，key为查询语句，value为查询结果。若缓存命中，则返回缓存数据，若缓存不存在，则执行查询操作后写入缓存。MySQL 8.0已经移除
  • 解析SQL：解析器通过SQL进行词法分析构建出SQL语法树，方便后续提取查询类型、where条件、表名、查询字段名等。然后再进行语法分析，判断SQL语句的正确性，例如判断关键字是否错误等。
  • 执行SQL:执行SQL要进行三个阶段
    • 第一个阶段为预处理阶段，此阶段会判断查询的字段或者表名是否存在。还会将select *的*解析为所有表的列
    • 第二个阶段为优化阶段，此阶段会考虑一些运行成本，找到一个符合最佳成本的查询计划。例如：多个索引字段时，选择某一个最优的索引字段查询
    • 第三个阶段为执行阶段,此阶段会执行真正的SQL,执行器会调用存储引擎接口，存储引擎查询到记录后返回给执行器，然后由执行器返回给连接器
image-20221108180147947

参考：https://xiaolincoding.com/open in new window