一条SQL查询语句是如何执行的

平时我们使用的数据库，看到的通常是一个整体，比如我们执行一条查询SQL，返回一个结果集，却不知道这条语句在MySQL内部是如何执行的，接下来我们就来简单的拆解一下MySQL，看看MySQL是由哪些“零件”组成的，在这个过程中逐步的揭开MySQL的面纱,对MySQL有个深入的理解。这样在我们以后遇到MySQL的一些异常或者问题的时候，就可以快速定位问题并解决问题。

下边通过一张图来看一下SQL的执行流程，从中可以清楚的看到SQL语句在MySQL的各个功能模块中执行的过程。

从上图来看，MySQL大体上可以分为Server层和存储引擎层两部分。

Server层：包含连接器、查询缓存、分析器、优化器、执行器等，涵盖了MySQL的大部分核心服务功能，以及所有的内置函数，所有的跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等，

存储引擎层：负责数据的存储和提取，其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。其中最常用的存储引擎是InnoDB，从MySQL5.5.5版本开始就成为了默认的存储引擎。也就是在创建表的时候，如果不指定存储引擎类型，默认就是使用InnoDB，如果需要使用别的存储引擎，在创建表的时候在create table语句中使用engine = MyISAM，来指定使用M有ISAM引擎创建表。不同的存储引擎的表数据存取方式不同，支持的功能也不相同，以后我们再慢慢分析。

从图中我们可以看到Server层由多个组件，从连接器开始到执行器，接下来我们使用一条简单的查询语句，来依次分析每个组件的作用。

select * from T where ID = 10;

连接器

第一步，会先连接到这个数据库上，这个时候首先遇到的是连接器。连接器是负责跟客户端建立连接、获取权限、维持和管理连接。一般连接命令是这样写的：

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

输入命令之后，就需要在交互对话中输入密码，密码也可以直接写在-p后面，但是这种操作一般是开发过程中，连接生产服务器不建议这样做，因为可能会导致密码泄露。

连接命令中的mysql是客户端工具，用来和服务端建立连接，在完成经典的TCP握手后，连接器就开始认证身份，这个时候用到的就是输入的用户名和密码。

• 用户名或密码不对时，就会报一个“Access denied for user”的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你这个用户名的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，建立连接后权限就确定下来了，如果这个时候使用管理员账号修改了这个用户的权限，也是需要下次重新连接时生效。

连接完成后，如果没有后续操作，这个连接就处于空闲状态，可以使用show processlist命令查询，下图中的Command列显示为Sleep的这一行，就表示现在系统中有一个空闲连接。

客户端如果长时间处于空闲状态，连接器就会自带将它断开连接，这个时间由参数wait_timeout控制，默认值是8小时。

如果在断开连接后，客户端再次发送请求的话，就会收到一个错误提示：“Lost connection to MySQL　server　during　query”。这个时候如果需要继续操作数据库，就需要重新连接然后再执行请求。

数据库里面有两种连接分别是长连接和短连接，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接，短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个连接。因为建立连接的过程通常比较复杂，所以建议尽量减少建立连接的动作，也就是尽量使用长连接而不是短连接。

当我们全部使用长连接后，会发现有时候MySQL专用内存涨的特别快，这是因为MySQL再执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放，所以长时间使用长连接累计下来，可能导致内存占用太大，被系统强行杀掉，也就是我们有时候看到的MySQL异常重启。

这个问题也不是不能解决的，常用的方案主要有以下两种：

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果MySQL版本是5.7或以上版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限校验，但是会将连接恢复到刚创建完成的状态。

查询缓存

在建立完成后，就可以执行select语句了，执行逻辑就会来到查询缓存。MySQL拿到一个查询请求后，会先到缓存查查看看，如果之前执行过的语句就会将执行过的语句和结果以key-value对的形式，被直接存放在内存中，key是查询语句，value是结果。如果查询语句在缓存中可以查到这个key，就直接把结果返回给客户端。如果语句不在缓存中，就会继续执行后边的阶段。执行完成后，将执行结果存入缓存中。

但是，大部分情况下都不建议使用查询缓存，这是因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要对表做一次更新操作，这个表上所有的查询缓存都会被清空，因此经常会出现刚把结果放入缓存还没使用，就被一个更新清空了，所以对于更新很频繁的数据库来说，查询缓存的命中率很低。除非是很久才更新一次的数据表，比如系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL提供了query_cache_type参数来设置是否查询缓存，将该参数设置成DEMAND这样对于默认的SQL语句都不使用查询缓存，如果确定需要使用查询缓存的语句，可以用SQL_CACHE来显式指定，如下：

mysql> select SQL_CACHE * from T where ID = 2;

如果你使用的MySQL8.0版本就不用考虑这个问题，因为8.0版本开始彻底的没有这个功能了。

分析器

接着上面一步，如果没有命中查询缓存，就开始真的执行语句了，首先MySQL需要知道你要做什么，会对SQL语句进行解析。

分析器会先做“词法分析”，你输入的SQL语句中由多个字符串和空格组成，MySQL需要识别出里面的字符串分别是什么，代表什么。如上边的SQL语句，MySQL从你输入的select关键字识别出来，这是查询语句，它也会把字符串T识别成表名“T“，把字符串ID识别成”列ID“。

做完了这些识别之后，就会做”语法分析“，根据词法分析的结果，语法分析会根据语法规则，判断输入的SQL语句是否满足MySQL的语法要求。

如果SQL语句有问题，就会有”You have an error in your SQL syntax“的错误提醒

mysql> elect * from t where ID=1;

ERROR 1064 (42000): 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 'elect * from t where ID=1' at line 1

一般语法错误会提示第一个出现错误的位置，user near紧接的内容就是报错内容相关。

优化器

经过了分析器，MySQL就知道你要做什么了，在执行之前，还要经过优化器处理。

优化器是在表里有多个索引的时候，决定使用哪个索引；或着在一个语句中有多表关联的时候，决定各个表的连接顺序。如下的语句：

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

• 既可以先从t1表里取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2表里的值是否等于20
• 也可以先从t2表里取出d = 20的记录ID值，再根据ID关联到t1表，再判断t1表里面c1 的值是否等于10

这两个执行的逻辑结果是一样的，但是执行效率是不同的，优化器在这里的作用就是决定选择哪一种方案。优化器遵循的原则：尽可能扫描少的数据库行纪录。

优化器阶段完成后，这个SQL语句的执行方案就确定下来了，进入执行阶段。

执行器

通过前面几步操作，MySQL已经知道了你要做什么，也优化了做的方式，就进入执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误。这里还有一个细节，如果在第二步查询缓存的时候命中缓存，会在缓存返回结果的时候做权限校验。查询也会在优化器之前调用precheck验证权限。

主要这里是对表的权限进行校验，而连接器是验证用户的身份。

如果有权限，就打开表继续执行，打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。如我们这个例子的表T中，ID字段没有添加索引，那么执行流程如下：

1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这一行放入结果集中。
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成一个结果集返回给客户端。

到这里，这个查询SQL就执行完成了。

对于有索引的表，执行的逻辑大同小异，第一次调用的是“取满足条件的第一行”这个接口，然后循环取“满足条件的下一行”这个接口，这些接口都是引擎中定义好的。

在数据库的慢查询日志中可以看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行，这个值是在执行器每次调用引擎的时候累加的，有时候执行器调用一次，在引擎内部扫描了多行，隐藏引擎扫描行数跟rows_examined并不完全相同。

到这里，对于一个SQL语句完整执行流程各个阶段有了初步认识，对于我们后续深入学习MySQL有个基础，最后分享一个小故事。
更多干货内容可以关注公众号故里学Java查看

连接器：门卫，想进请出示准入凭证（工牌、邀请证明一类）。“你好，你是普通员工，只能进入办公大厅，不能到高管区域”此为权限查询。
分析器：“您需要在公司里面找一张头发是黑色的桌子？桌子没有头发啊！臣妾做不到”
优化器：“要我在A B两个办公室找张三和李四啊？那我应该先去B办公室找李四，然后请李四帮我去A办公室找张三，因为B办公室比较近且李四知道张三具体工位在哪”
执行器：“好了，找人的计划方案定了，开始行动吧，走你！糟糕，刚门卫大哥说了，我没有权限进B办公室”