阅读 1707

在Mysql中执行一条SQL,会经历什么?

背景

我们都经常使用Mysql作为数据库来存储与查询较常用的数据。当我们输入一行如SELECT * FROM table_name WHERE id=26这样的语句之后,Mysql如果正确执行的情况下,会输出你想要的信息。

那么,在你输入这行语句之后,一直到它显示出你想要的信息,这中间Mysql都经历了什么呢?这篇文章会简单聊一下这个事情。

Mysql基本架构图

我们先看下Mysql的一个较整体的架构图。

接下来我会以具体的SQL语句为例,详细的叙述从你在客户端输入了这个语句之后,到它返回你想要的信息,这中间具体经历了什么。

客户端

所谓客户端,即是我们登录与操作Mysql所使用的终端。我们都是在客户端对Mysql进行操作的,无论是输入连接数据库的信息,还是输入查询某个表的SQL,或者是收到Mysql返回给我们的查询信息,这些都是在客户端完成的。

连接器

用户信息验证

我们在一个客户端跟前,想要使用Mysql数据库,那么第一步就是要先连接上你要使用的数据库。

我们都知道,我们要输入命令mysql -h$ip -P$port -u$username -p

之后客户端会要求我们输入密码。再之后,如果我们输入的信息都没有问题了,我们就进入Mysql的操作界面了。

如果我们输入的信息有问题,就会收到客户端返回的报错信息。比如我们将密码输入错误了,这时就会收到"Access denied for user"这样的报错信息。

那么,这中间连接器具体做了什么呢?

首先,连接器会拿着我们输入的IP和端口,去做最经典的TCP握手,握手如果都失败了,那就自然没有后续了,直接返回相应的报错信息。

如果握手成功了,此时则会去验证我们输入的用户名和密码,验证失败则同样会返回相应的报错信息。

用户权限获取

如果用户名密码也没有问题,接下来连接器则会取出权限表读取该用户相应的权限数据。用户跟着所做的所有操作,都基于此时读取到的用户权限。

权限表共有4个:user, db, tables_priv, columns_priv

当用户通过权限验证,进行权限分配时,按照user, db, tables_priv, columns_priv的顺序进行分配。即先检查用户的全局权限表user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y,将不再检查剩余3个表;如果为N,则到db表中检查此用户对应的具体数据库,并得到db中为Y的权限;如果db中为N,则检查tables_priv中此数据库对应的具体表,取得表中的权限Y;如果为N,则到columns_priv中检查具体的列。

这也就意味着,当我们修改了某个用户的用户权限,只有到下一次该用户登录(创建新的连接)时,才会影响到该用户。

连接与等待超时

我们可以通过show processlist来查看当前所有的用户连接及其行为。

Command中的字段显示该用户目前的状态,此时这个用户是查询状态。

但若Command显示的状态是Sleep,那么说明该用户当前在等待状态。若等待超过了一段时间,则连接器会自动断开。

该超时时间由wait_timeout变量控制,可以通过show global variables like 'wait_timeout'来查看。

mysql默认为28800秒,即8小时。

长连接与短连接

所谓长连接,即用户的持续操作使用的都是同一个连接,连接在一段时间内长时间建立。

所谓短连接,即用户每做几次操作则断开,再下次操作时再进行连接。

长连接的优点是,在持续操作时,可以节省很多建立连接所需要消耗的时间。但是长连接所要存储的临时数据都在连接对象中,长时间积累,会导致系统内存溢出,具体表现 为Mysql异常重启。

短连接的优缺点与长连接相反,虽然不用担心内存溢出的问题,但短连接在持续操作的情况下多次连接,连接消耗很多时间,整体操作效率会很低。

缓存器

连接器连接完成的下一步就是缓存器的缓存查询,如果我们需要对一张静态表(不常更新)经常做查询操作,那么可能会用到缓存器。

缓存器中使用的是key-value的存储形式,key值存储的是查询语句,value值存储的是对应结果。

要注意的是,只要该表做了一次更新操作,那么该表对应的缓存就会全部被清理。因此使用场景并不多。

所以当前缓存器的使用较少。我们可以通过query_cache_type来查看缓存器是否开启。

现在一般都是默认关闭的状态。且Mysql从8.0版本会开始彻底弃用该功能。

分析器

假设我们不使用缓存器,或者通过缓存器没有命中SQL语句。

那么连接器做连接操作之后,接下来我们就输入了一个查询语句,比如:SELECT host FROM mysql.user LIMIT 1

而分析器做的事情就是对你输入的语句做 “词法分析”“语法分析”

所谓 “词法分析” ,就是判断每一个你输入的词,比如分析器首先会判断出你输入的第一个词是“SELECT”,第二个词你输入了“host”,等等。

“语法分析” 则是跟在 “词法分析” 之后,就是依据你输入的这些词来判断你输入的是否符合语法规则。

假如符合语法规则,则会顺利进行下去并返回相应信息。

假如不符合语法规则,则分析器会返回报错信息给客户端。
具体出错的地方,一般都是跟在use near之后,我们看这里就能知道语法错误出在了哪一块。

优化器

在分析器工作结束后,如果语法有问题,那么就会直接返回报错信息,且不继续向下运行。

若语法正确,那么,则会到优化器部分的工作。优化器顾名思义,就是对该语句的执行做优化。

比如,在一个语句查询某个表时,该表可能有多个索引,此时使用哪个索引会使语句的执行效率最高?这就是优化器要做的事情。

再比如,执行语句select * from t1 join t2 on t1.ID=1 and t2.ID=2

该语句执行时,是先从t1表中找到ID=1的行关联到t2表之后,再从t2表中查找ID=2的行。

还是先从t2表中找到ID=2的行关联到t1表之后,再从t1表中查找ID=1的行。

两种执行顺序可能就导致执行效率的不同,怎样选择执行顺序会提高执行效率,这也是优化器要做的事情。

执行器

在上述步骤完成之后,就轮到执行器去执行具体的语句了。

例如语句:select * from mysql.tables_priv

在执行器做具体的语句执行之前,会对该表的操作权限进行验证,验证失败则返回权限错误的报错。如下:

而实际上,权限验证不仅仅在执行器这部分会做,在分析器之后,也就是知道了该语句要“干什么”之后,也会先做一次权限验证。叫做precheck。

而precheck是无法对运行时涉及到的表进行权限验证的,比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。

如果验证成功,那么则会使用该表对应的存储引擎的接口,继续执行语句。 最后将成功执行的结果返回给客户端。

总结

简单来说,一条SQL语句在Mysql中执行,一共会经历四步(算上连接Mysql),分别是连接、分析、优化与执行。每一步都会精确执行,如果发现有问题就会返回给客户端相应的报错。只有每一步都正确执行,最终才会在客户端得到你想要查询或操作的结果。