一条SQL查询语句的执行过程

来源于极客时间《MySQL实战》

最近刚刚购买了极客时间的课程《MySQL实战45讲》，我会在这里将学习到的知识点做一个总结。

本节主要是讲 MySQL 的基础架构。比如执行以下语句时：

mysql> select * from T where ID=10;
复制代码

这条语句在MySQL内部是如何处理的。

基础架构图

如图 主体分为两部分:Server和存储引擎部分。

Server包括：连接器、缓存查询、分析器、优化器、执行器。存储过程、触发器、视图等功能都在Server层处理。

存储引擎负责数据的存储和提取。常见的有InnoDB、MyISAM。

连接器

连接器与客户端建立连接、获取权限、维持和管理连接。

常见的命令：

mysql -uroot -ppassword

用来和服务器建立连接，TCP握手后完成认证过程。

• 账号密码不对，报错："Access denied for user"
• 认证通过，在权限表中查询拥有的权限

只有在连接过程才会从权限表中读取权限信息，中途对权限的修改不会影响已经建立的连接，只有重新登陆后才会使用新的权限信息。

建立连接后如果长时间处于空闲状态， "show processlist" 命令可以看到到处于sleep状态的连接。

若规定时间内无活动，则会自动断开连接。 规定时间 由 wait_timeout 控制，默认为8小时。断开后，再次发请回会提示 "Lost connection to MySQL server during query" ,只能重新连接。

防止数据库中出现占用大量内存的情况，可以用一下方法解决：

"mysql_reset_connection"

查询缓存

建立连接后，就可以执行select操作，这是会执行第二部分：查询缓存。

一个请求进来，首先查询缓存，是否存在该记录。之前执行过的语句，则会以语句为key,执行后的结果集为value存储在内存中。

• 如果在缓存中查找到，则回直接返回给客户端。
• 如果没有找到记录，继续执行后面的操作，并将该语句与结果分别以key-value存入内存。

不建议使用查询缓存，此操作弊大于利 ( MySQL8.0后将会删除该功能 )

对于一个表，只要有更新便会将涉及到该表的缓存全部清空。

只适用于不常更新的静态表

推荐：

-将query_cache_type 设置为 DEMAND,默认SQL不使用查询缓存，针对需要查询缓存的语句，使用SQL_cache显示指定，例如：

select SQL_CACHE * from T where ID = 10;
复制代码

分析器

这条SQL要做什么

1. 词法分析
   识别SQL关键字，提出主要成分。MySQL根据 "select" 得出这是一条查询语句，根据 "from T" 识别表T，将 "ID" 识别为列名。
2. 语法分析
   根据语法规则判断是否符合SQL的语法。如果出现错误，则提示： "You have an error in your SQL syntax..." ,后面是错误的地方，需要你关注 "use naer" 后的内容。

优化器

这条SQL如何做

这个步骤将会选取最优的执行方式，例如：

1. 当涉及到多个索引时，决定用哪个索引
2. 多表关联时，决定连接顺序

   select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
   复制代码

   该步骤决定了先从t1表中取出c=10的ID值还是先查找出t2表中d=20的ID值。

执行器

真正的执行步骤

根据连接器取到的权限判断，是否对表T有权限。

• 若有权限，打开表继续操作。

打开表后，根据表的引擎定义，使用引擎提供的接口。 例：

• 表T中ID字段无索引：

  • 调用InnoDB提供的接口，取出第一行，当ID=10时，将该行数据放入结果集。若不符合则字啊次调用引擎接口获取下一行，再次判断，直至最后一行数据。
  • 将结果集存入缓存、返回给客户端。

• 表T中ID字段有索引：

  • 调用InnoDB的 "获取满足条件的第一行" 接口，server层还会再判断一次值是否正确，然后放入结果集，接着继续访问 "满足条件的下一行" 接口，这些接口是引擎已经定义好的。

在MySQL的慢查询日志中，rows_examined字段表示该语句执行过程中扫描了多少行，这个值就是调用引擎获取数据行的时候添加的。

某些场景下，执行器调用一次，引擎内部会扫描很多行，因此 引擎扫描行数跟rows_examined并不完全相同 ，这一点后文会详细说明。

评论区知识点：

• 问题：如果查询语句中的字段不存在，会在哪个阶段报错？

  • 答案：会在分析器阶段报错。
  • 《高性能MySQL》提到解析器和预处理器 解析器：处理语法和解析查询，生成一颗对应的解析树。 预处理器：进一步检查解析树的合法性，比如：数据表和数据列是否存在，别名是否有歧义等。如果通过则生成新的解析树，在提交给优化器。

• Connect_timeout 指的是“连接过程中”的等待时间

• wait_timeout指的是“连接完成后，使用过程中”的等待时间