菜鸟笔记
提升您的技术认知

基础架构:一条查询SQL是如何执行的?

1. MySQL整体架构

MySQL可以分为Server层存储引擎,而我们通常使用的连接命令是客户端工具。

Server层主要是对于用户的SQL语句进行处理,涵盖MySQL的大部分核心功能。还包括内置函数,视图,触发器,存储过程等。

存储引擎负责数据的存储和提取。

整体架构图如下:

Server层主要模块:

  1. 连接器:连接器负责跟客户端建立连接,获取权限、维持和管理连接。
  2. 查询缓存:MySQL拿到一个查询请求后,会先去检查查询缓存。
  3. 分析器:对用户的SQL语句作解析
  4. 优化器:优化器决定使用哪个索引,决定各表的连接顺序等
  5. 执行器:执行优化后的语句

常见的存储引擎:

  • InnoDB:MySQL 5.5.5版本后成为默认的存储引擎
  • MyISAM
  • Memory:内存引擎

2. 连接器

用户在客户端输入地址、账号、密码,连接器会到权限表里面去查看你拥有的权限。这就意味着,一个用户成功连接后,即使管理员修改了权限,也不会影响已经存在的连接的权限。

如果客户端长期没有活动,那么连接器会自动断开,这个时间参数由wait_timeout参数控制。

连接分为两种:

  1. 长连接:指连接成功后,如果客户端持续有请求,则一直使用同一个连接。如果全部使用长连接,那么会导致MySQL内存涨的很快
  2. 短连接:指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。频繁建立连接很耗时。

解决方案;

  1. 定期断开长连接:使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接重连。
  2. 如果使用MySQL5.7以上版本,可以在每次执行一个比较大的操作后,通过执行mysql_reset_connection来重新初始化连接资源

3. 查询缓存

MySQL拿到查询请求后,会首先到缓存看看是否执行过这条语句。之前执行过的语句会以key-value的形式被缓存在内存中。key是查询的语句,value是查询的结果。

但是查询缓存有很多弊端:

  1. 缓存十分容易失效,只要有对一个表进行更新,那么这个表关联的缓存就会全部失效。
  2. 对于更新压力大的数据库来说,缓存命中率很低。除非是有一张静态表,不经常更新时比较适用

MySQL提供了一种按需进行缓存的方式。对于默认的SQL语句不进行缓存,需要进行缓存时加入SQL_CACHE显式指定

mysql> select SQL_CACHE * from T where ID=10

MySQL5.8之后删除了缓存功能。

4. 分析器

如果没有命中缓存,那么就开始真正执行语句了。分析器的作用就是对SQL语句做解析
例如要将T识别成表T,ID识别成列ID
分析器在这其中会做词法分析句法分析

5. 优化器

优化器的作用是对于索引的选择,表连接顺序的选择等。
例如,下面这个语句会有两种连接方式,但是两种方式的效率不一样:

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.c=20

1. 先从t1表中取出t1.c=10的记录,再和t2表关联,再选取t2.c=20的记录
2. 先从t2表中取出t2.c=20的记录,再和t1表关联,再选取t1.c=10的记录

6. 执行器

MySQL通过分析器知道了你要做什么,通过优化器知道了你要怎么做,执行器就会开始执行语句
执行器执行的步骤如下:

  1. 先判断你对这个表T有没有权限
  2. 调用InnoDB引擎接口取这个表的第一行,判断ID值是否等于10,不是就跳过,是的话就加入结果集中。
  3. 调用引擎接口取下一行,重复逻辑,直到最后一行为止
  4. 将结果集返回给客户端

此外,我们可以通过慢查询日志中的rows_examined字段查询这个语句执行过程中扫描了多少行,不同存储引擎的扫描行数是不一样的。

7. 小问题

如果表T中没有字段k,而你执行了这个语句

select * from T where k=1

那肯定是会报“不存在这个列”的错误:“Unknown column ‘k’ in ‘where clause’”,那会是在哪一个阶段报的错误?

答案:会在优化器阶段。因为优化器阶段会决定使用哪个索引,确定连接方式,如果没有该字段肯定会报错。执行器阶段是打开表去获取数据,而字段不是数据,是实现定义好的,可以直接读取,不需要打开表。