Mysql SELECT 语句执行过程

整个 SELECT 语句查询流程

1、客户端/服务端通信协议(Connectors)

Mysql 客户端/服务端通讯协议是半双工的,这就意味着在任意时刻只能有一端能发送数据,要么是客户端向服务端发送数据,要么是服务端向客户端发送数据,这两个动作不能同时进行,一旦一端开始发送数据,另外一端要完整的接收整个消息之后才能进行后续的响应动作,所以无法也无须将一个消息切成多个小块独立发送,也没有办法进行流量控制.客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置 max_allowed_packet 参数,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常

相比于客户端,服务端响应给客户端的数据通常会很多,由多个数据包组成,但是当服务器响应客户端请求时,客户端必须完成的接收整个返回结果集,而不能简单的只取前面几条结果,然后让服务器停止发送,所以在实际开发中尽量保持查询简单并且只返回必须的数据,减少通信间数据包的大小和数量,这也是查询中尽量避免使用 select * 以及加上 limit 限制的原因之一

首先你会先连接到这个数据库上,这时候接待你的就是连接器.连接器负责跟客户端建立连接,获取权限、维持和管理连接

具体的连接命令如下

mysql -h$ip -P$port -u$user -p

连接命令中的 mysql 是客户端工具,用来跟服务端建立连接.在完成经典的 TCP 三次握手之后,连接器就会根据你输入的用户名和密码来认证你的身份,如果用户名和密码不正确,你就会收到一个错误,然后客户端程序结束执行

Access denied for user....

如果用户名和密码都认证通过,连接器会从权限表中查出你拥有的权限,之后这个连接里面的权限判断逻辑,都将依赖于此时读到的权限,这就意味着,一个用户成功建立连接之后,即使你使用管理员账号对这个用户的权限做了修改,也不会影响已经存在的连接权限,修改完成之后,只有再建立新的连接才会使用新的权限设置,所以用户权限是连接级别的
连接完成后,如果你没有后续的动作,这个连接就处于空闲状态,你可以使用 show processlist 命令进行查看,下图 Command 列中 Sleep 状态的连接就是空闲连接

客户端如果太长时间没有动静,连接器就会自动将它断开,这个是时间是由 wait_timeout 参数控制的,默认时间是 8h,可以通过命令进行查看

show variables like 'wait_timeout'

如果连接被断开之后,客户端再次发送请求的话,就会收到一个错误提醒 

Lost connection to MySQL server during query

这个时候如果你要继续,就需要重连,然后再执行请求了

长连接和短连接
长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接

短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个

通常情况下建立连接的过程通常是比较复杂的,所以建议在使用的过程中尽量减少建立连接的动作,也就是尽量使用长连接

但是长连接有一个弊端,Mysql 在执行过程中临时使用的内存都是保存在连接对象里面的,这些资源会在连接断开的时候才释放,如果长连接累积下来,可能导致内存占用特别大,最终 OOM 被系统强行杀掉,从现象看就是 Mysql 异常重启了
怎么解决长连接的问题呢,有两种方案
1、定期断开长连接,在使用一段时间或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询时再重连
2、如果使用的是 Mysql 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源,这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态

2、SQL 接口转发 SQL 请求

客户端与服务端建立连接以后,服务端通过 SQL 接口接收客户端发送过来的 SQL 命令

3、查询缓存

如果查询缓存是打开的,那么 Mysql 会检查这个查询语句是否命中查询缓存中的数据,如果当前查询语句恰好命中查询缓存,再检查一次用户权限后直接返回缓存中的结果,这种情况下,查询不会解析,也不会生成执行计划,更不会执行.Mysql 将缓存存放在一个引用表(类似于 HashMap 的数据结构),通过一个哈希值索引,这个哈希值索引通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来,所以查询在任何字符上的不同(大小写不同、多了注释、多了少了空格),都会导致缓存不会命中

Myslq 查询缓存系统会跟踪查询中涉及到的每个表,如果这些表发生了 DML、DDL 操作,那么和这张表相关的所有缓存数据都将失效,而且查询缓存对系统的额外消耗也不仅仅在写操作,读操作也不例外
1、任何查询语句在开始之前都需要经过检查,即使这条 Sql 语句永远不会命中缓存
2、如果查询结果可以被缓存,那么执行完成后,会将结果存入缓存,也会带来额外的系统消耗
基于以上情况,Mysql 8.0 及之后的版本都将查询缓存移除了

4、解析器

如果没有命中查询缓存,就要开始真正执行 SQL 语句了,首先 Mysql 需要知道你要做什么,因此需要对 SQL 语句做解析,分析器先会做词法分析,你输入的由多个字符串和空格组成的一条 SQL 语句,Mysql 需要识别出来里面的字符串分别是什么,代表什么含义,Mysql 从你输入的 select 这个关键字识别出来这是一个查询语句,它也要把字符串 T 识别成表名 T,把字符串 ID 识别成列 ID,做完这些识别之后,就要做语法分析了,根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 Mysql 语法,如果你的语句不对,就会收到 You have an error in your SQL syntax 的错误提醒,比如下面这个语句 from 写成了 form

mysql> select ID form T where id = 1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form T where id = 1' at line 1

5、优化器

经过了分析器,Mysql 就知道你要做什么了,在开始执行之前还要先经过优化器的处理,优化器是在表里面有多个索引的时候决定使用哪个索引,或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序,比如你执行下面这样的语句

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

你既可以先从 t1 表中取出 c=10 的所有记录,然后通过 ID 值关联到 t2 表,最后过滤出 t2 表中 d=20 的所有记录

你也可以先从 t2 表中取出 d=20 的素有记录,然后通过 ID 值关联到 t1 表,最后过滤出 t1 表中 c=10 的所有记录

这两种执行方法的逻辑结果是一样的,但是执行效率会有不同,而优化器的作用就是根据自身的评估决定使用哪一种方案

6、执行器

Mysql 通过分析器知道了你要做什么,通过优化器知道该怎么做,于是就进入了执行器阶段,开始真正的执行语句了

开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,这个权限是在建立连接的时候就确定的(Mysql 的权限是连接级别的),如果没有,就会返回没有权限的错误,如下所示

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限就打开表继续执行,打开表的时候,执行器会根据表的引擎定义,去使用这个引擎提供的接口

比如我们这个例子中的表 T 中,ID 字段没有索引,那么执行器的执行流程是这个样子的

1、调用 InnoDB 存储引擎提供的接口取这个表的第一行,判断该行的 ID 值是不是 10,如果不是则跳出,如果是则将这行存放在结果集中

2、调用执行引擎接口获取下一行,重复相同的判断逻辑,直到取到这个表的最后一行

3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端