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

朴灿烈づ我的快乐病毒、 2023-07-15 09:09 2阅读 0赞

# 背景 #

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

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

# Mysql基本架构图 #

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

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

# 客户端 #

所谓客户端，即是我们登录与操作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`来查看当前所有的用户连接及其行为。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 1]  
Command中的字段显示该用户目前的状态，此时这个用户是查询状态。

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

该超时时间由wait\_timeout变量控制，可以通过`show global variables like 'wait_timeout'`来查看。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 2]  
mysql默认为28800秒，即8小时。

### 长连接与短连接 ###

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

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

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

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

# 缓存器 #

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

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

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

所以当前缓存器的使用较少。我们可以通过`query_cache_type`来查看缓存器是否开启。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 3]  
现在一般都是默认关闭的状态。且Mysql从8.0版本会开始彻底弃用该功能。

# 分析器 #

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

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

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

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

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

假如符合语法规则，则会顺利进行下去并返回相应信息。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 4]  
假如不符合语法规则，则分析器会返回报错信息给客户端。  
![在这里插入图片描述][2020031609511461.png]  
具体出错的地方，一般都是跟在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`

在执行器做具体的语句执行之前，会对该表的操作权限进行验证，验证失败则返回权限错误的报错。如下：  
![在这里插入图片描述][20200316095121891.png]  
而实际上，权限验证不仅仅在执行器这部分会做，在分析器之后，也就是知道了该语句要“干什么”之后，也会先做一次权限验证。叫做precheck。

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

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

# 总结 #

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70]: /images/20230528/c74ef04c0678422cb9787db6d585bb54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 1]: /images/20230528/92e895f88fe24da9a65daca7a6fb77e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 2]: /images/20230528/96e55db852b1472d838393d01d8ed1d5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 3]: /images/20230528/1d4393b46a3740fb9b01efccef3858cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h0eTQ2NTY1_size_16_color_FFFFFF_t_70 4]: /images/20230528/3b66ad80aad84051acbd376f7cfa4701.png
[2020031609511461.png]: /images/20230528/cc85f5eb4fe8470aa95ec6a4a312b8ab.png
[20200316095121891.png]: /images/20230528/1fad7db79f2b4606818c0a6558367133.png