MySQL——执行一条select语句期间发生了什么

执行一条select语句,期间发生了什么

MySQL架构主要分为两层:Server层和存储引擎层.

Server层负责建立连接,分析和执行SQL.MySQL的大部分功能都在Server层实现.所有的内置函数和跨存储引擎的功能(存储过程,视图,触发器等)也在Server层实现.

存储引擎层主要负责数据的存储和读取.支持InnoDB,MyISAM,Memory等多个存储引擎.不同的存储引擎共用一个Server层.从MySQL5.5开始,默认的存储引擎就是InnoDB,而索引的数据结构是在存储引擎层实现的,InnoDB的索引的数据结构是B+树.

连接器

连接器负责MySQL的连接工作,因为MySQL是基于TCP实现的协议,所以首先需要经过TCP三次握手来启动MySQL服务,然后通过验证用户输入的用户名和密码,然后为此次连接的用于授予相应的权限.

查看MySQL服务被多少个客户端连接的命令:show processlist;

在MySQL中,空闲连接(建立好连接后不进行任何操作)是不能长期存在的,有wait_timeout参数控制,默认最大时长为8h.

MySQL服务器的最大连接数有max_connections控制,

MySQL中也存在长连接和短连接.长连接可以避免不必要的连接的资源消耗.但在长连接中,每次查询会使用内存连接管理对象,这些连接对象会在连接断开时释放,如果连接迟迟不断开,MySQL服务会占用过多内存资源.

解决方案

1. 定期关闭长连接
2. 客户端主动重连:MySQL5.7实现了mysql_reset_connection()接口,当连接中占用很多内存资源后,客户端会重置连接,将连接恢复到刚开始连接的状态(不需要重连和权限验证).

连接器做的工作:

1. 经过TCP三次握手启动MySQL服务
2. 验证用户输入的用户名和密码
3. 读取用户的权限并在连接中使用该权限

查询缓存

连接建立好以后,输入的SQL语句就可以发送到MySQL服务上MySQL服务接收到SQL语句后会解析SQL语句的第一个字段,如果是select的话,就会从[查询缓存]里查找数据,看是否能直接找到SQL语句对应的结果,[查询缓存]中的记录是 以key-value的形式存储的,key是SQL语句,value是SQL语句对应的结果

尽管[查询缓存]能够一定程度上提高查询的效率,但这种提升…微乎其微.因为[查询缓存]中缓存的记录会随着更新操作而清空.只要出现一个更新操作,[查询缓存]中的记录就会随之清空.因此在MySQL8.0中就将查询缓存删掉了,在MySQL8.0之前可以通过query_cache_type来手动关闭查询缓存

解析SQL

MySQL服务在收到SQL语句后会对SQL语句解析.而解析的工作通过解析器来执行.解析器会做2件事情

1. 词法分析:将SQL语句中的关键字识别出来构建成SQL语法树.
2. 语法分析:根据词法分析的结果和语法规则判断SQL语句是否符合MySQL语法.如果语法不对,会在解析时将错误返回给客户端.注意:表不存在或字段不存在的错误无法再解析时被检测出来

执行SQL

1. prepare阶段:预处理阶段

   1. 检查SQL语句中的表或字段是否存在
   2. 将select * 中的 * 扩展为表上的所有列

2. optimize阶段:优化阶段

   优化器主要负责确定SQL语句的执行方案,当表中有多个索引,选择查询成本最低的索引

   通过在查询语句前加explain字段可以查看MySQL在查询过程中使用了哪个索引(会显示一个key,key中即为使用的索引,如果key为null,则遍历整个表)

3. execute阶段:执行阶段

   在确定好SQL语句的执行方案后,由执行器负责执行SQL语句.并和存储引擎交互.交互过程可以分为3种

   1. 主键索引

      执行器将主键索引交给存储引擎,存储引擎在B+树中找到符合主键索引的记录返回给执行器,执行器再根据其他判断条件判断,如果符合则返回给客户端,否则跳过该记录继续让存储引擎查询,但因为主键索引的唯一性,所以第二次查找过程中会直接返回-1,执行器退出循环,结束查询.

   2. 全表扫描

      存储引擎会根据表中的记录从上到下读取每一条记录,然后返回给执行器,执行器判断记录是否符合条件,符合则返回,否则则跳过.直到存储引擎遍历完整张表.

   3. 索引下推

      索引下推能够减少二级索引的回表操作.索引下推指的是将执行器判断过程中依据的一些条件下推给存储引擎,由存储引擎负责判断,条件成立后再回表给执行器.

总结在执行一条select语句的整个过程

1. 连接器:TCP三次握手建立连接,管理连接,校验用户身份,读取用户权限
2. 查询缓存:在缓存中查找是否存在结果,如果存在直接返回,否则继续执行.MySQL8.0删除了查询缓存
3. 解析SQL:经过词法分析得到SQL语句中的关键字并构建SQL语法树.根据语法分析分许SQL语句是否符合MySQL语法
4. 执行SQL
   1. 预处理阶段:判断表和字段是否存在,将*扩展为表上所有列
   2. 优化阶段:确定执行SQL语句的具体方案
   3. 执行阶段:执行SQL语句,与存储引擎交互,从存储引擎中读取记录,返回给客户端.