MySql 学习之 一条查询sql的执行过程

　　相信大家都接触过mysql数据库，而且也肯定都会写sql。我不知道大家有没有这样的感受，反正我是有过这样的想法。就是当我把一条sql语句写完了，并且执行完得到想要的结果。这时我就在想为什么我写这样的一条sql语句，就能给我查询出我想要的结果，为什么我写了update就能更新一条语句？它们的执行过程是什么样的？它们的原理是什么？那么接下来我就来谈谈这个。

select * from user where id=6

上面这条查询语句非常简单，就是查询一个id为6的用户信息。那么它的执行流程是怎么样的？别急，咱们先看一张图，

根据上面的图，咱们一步一步来分析。从图中可以看出整个执行过程大致可以分为两部分，分别是server层和引擎层。

server层中又分为连接器、分析器、查询缓存、优化器以及执行器几部分。

引擎层则是主要负责存储数据，提供读写接口。

那么接下来从头开始分析。

1、连接器

首先要操作数据库，那么必须得连接上数据库，所以这时候就用到了连接器。当你输入 “mysql -h$ip -p$端口 -u$登录名 -p ” 时就表示要进行连接数据库了，然后输入密码进行连接。如果密码或者用户名错了，则会报如下错误：

error 1045 (28000): access denied for user 'root'@'localhost' (using password: yes)

如果输入用户名和密码正确，那么连接器接下来就去权限表中查询你登录用户所拥有的权限，之后此用户操作数据的权限判断逻辑都将依赖查询到的权限。哪怕你修改了此用户的权限也还是没用，必须重新新建连接，修改的权限才会生效。

另外说到这里就顺便提一下，客户端连接mysql服务器时，如果连接一直处于空闲状态，那么到了一定的时候就会断开连接，多长时间是由 wait_timeout 控制的，其默认是8个小时。如果超过8个小时，你执行操作数据库时就回提示 “lost connection to mysql server during query”，这时只有重新连接数据库方能进行操作。

说到连接器，咱们得说一下长连接和短链接。长连接就是如果客户端一直都有请求操作数据库，那么就会一直使用这个连接进行操作。短链接就是每次执行完很少的数据库操作就断开连接了，如果再有请求就必须重新连接。

所以这里建议减少数据库的连接操作，尽量使用长连接。但是长时间使用长连接会导致一个问题，那就是mysql的占用的内存会越来越大，甚至到最后可能会出现oom情况，导致mysql异常重启，那么这就尴尬了。

针对上面的情况有两种解决办法：

（1）、定期断开长连接，或者断开一些查询占用内存比较大的操作的连接，释放资源。

（2）、如果是5.7及以上版本，可以使用 mysql_reset_connection 来重置连接，但是需要注意以下几点

• 活跃事务会被回滚，自动提交模式也会被重置；
• 释放所有表锁；
• 关闭&删除所有临时表；
• 会话变量(选项)被重置成和全局变量一致；
• 用户级变量丢失；
• prepare语句会被释放（其相应的handler也会被关闭）；
• last_insert_id值重置为0；
• 利用get_lock获取的锁会被释放。

以上是mysql官微给出的解释，所以重置连接的时候以上因素，以免对数据库中的数据产生影响。

 

2、查询缓存

客户端连接成功mysql服务器后，执行上面的一条sql时，首先会去缓存中查询是否有数据，如果有数据，那么直接把数据返回给客户端，后面的步骤都省略了。它的原理怎样的呢？请接着往下看，一条查询sql的首次执行完成后，会把sql语句作为key，把查询出来的数据作为value放入到缓存中，如果后面再有相同的查询，那么直接从缓存中取值便可。

看到这里也许你们会想缓存这么好用，那以后要多用缓存。别急，请接着往下看。查询缓存用起来确实好用，但是它有一个弊端，那就是当这个表做了更新操作时，那么此表的缓存将会全部清空。也许当你辛辛苦苦缓存起来的数据，还没来得及用时就可能被一条update语句给全部干掉。

所以如果更新比较频繁的表是不适合使用缓存的，如果是某些配置表倒是比较适合缓存的使用。

在mysql中的查询语句使不使用缓存时看query_cache_type的值，当为0时关闭缓存，当为1时表示开启缓存，当为2（demand）时表示只有在sql语句中带有 sql_cache  关键字才会使用缓存，如下sql

select sql_cache * from user where id=6

不过需要注意的一点是 mysql 8.0 版本已经把缓存功能完全移除，所以这一块需要注意一下。

3、分析器

当执行一条查询sql时，会优先取查询缓存，如果缓存中没有数据，那么便会开始sql的真正的执行流程。首先是分析器，其主要就是对sql语句进行 “词法分析” 和 “语法分析”。

词法分析 就是对sql中的单词进行逐个的分析，比如 从 select 可以识别出要执行查询操作，user则是识别成表user，id则识别成user表中字段id。

语法分析就是分析整条sql是否符合mysql的语句，比如 你故意把sql中的 where 后面不跟条件，那么语法就肯定会问题，那么此时就会给你提示 “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 '' at line 1” 。如果给出类似的提示，那么基本就是语法错了，那就得仔细检查一下写的sql语句了。

4、优化器

经过了分析器这一层，那么接下来就要进入优化器了。从分析器中我们已经知道这条sql是要执行更新还是查询操作。那么优化器便是要对这条sq执行之前l进行优化处理，有哪些优化处理呢？例如 某表有多个索引的时候 决定用哪一个索引；或者多关联（join）查询的时候，决定关联的顺序。比如下面一条sql

select * from user u join  score s using(id)  where u.id=10 and s.scores=60;

上面一条 sql 可以分为两种情况

• 既可以先从表user里面取出id=10的记录的id值，再根据id值关联到表socre，再判断score表里scores的值是否等于20。
• 也可以先从表score里面取出scores=20的记录的id值，再根据id值关联到user，再判断user表里面id的值是否等于10。

这两种方案得出的结果是一样的，但是执行的效率是不一样的，而优化器作用便是从中选择一个方案。

5、执行器

当优化器选择好了方案，那么便进入执行器阶段，这时候就要开始执行sql了。执行sql前要查询一下你对需要操作的表是否有对应的操作权限，如果没有操作权限，则会给出提示 “error 1142 (42000): select command denied to user 'b'@'localhost' for table 'xxx‘ “ 。

如果有对应表的操作权限，那么便打开表继续执行，执行器会根据定义的表的引擎，来执行引擎提供的对应读写的接口，mysql 5.5版本之后 默认的引擎为 innodb。

其大致流程如下：（假如 id 是没有索引的）

1. 调用innodb引擎接口取这个表的第一行，判断id值是不是6，如果不是则跳过，如果是则将这行存在结果集中；

2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

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

如果是 id 是有索引的，第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，基本和上面差不多。这些接口都是引擎中已经定义好的。

至此 一条 sql 便执行完成。