首页手记技术分析 |...

技术分析 | 浅谈在MySQL体系下SQL语句是如何在系统中执行的及可能遇到的问题

标签：

MySQL

SQL语句大家并不陌生，但某种程度上来看，我们只是知道了这条语句是什么功能，它可以给我们得到什么样的结果，但我们如果把这条语句写错或是数据库表设计上有什么缺陷，会引发什么错误我们却无从得知，所以今天想分享一下在MySQL体系下SQL语句大致上是如何在系统中执行的，在以后SQL语句提示错误时将更好定位问题。

1、问题导入

我们以一条SELECT语句为例，我们知道SELECT语句是属于我们的DML下的DQL语言，它可以通过我们指定的字段列表和表列表并进行条件的描述来查询某张数据表中我们所需要的某些数据。假设：

mysql> select * from test_table where sname = '王五';
+-----+-------+---------+
| sno | sname | major   |
+-----+-------+---------+
|   1 | 王五  | English |
+-----+-------+---------+
1 row in set (0.00 sec)

如果我们将字段SELECT漏写成ELECT，将会：

mysql> elect * from test_table where sname = '王五';
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 test_table where sname = '王五'' at line 1

我们通过观察use near后方提示的内容：’‘elect * from test_table where sname = ‘王五’’ at line 1，可得知我们的SELECT语句写错了。

2、MySQL基本架构

那么我们在执行SQL语句的过程究竟是什么样的呢？我们需要分析一下MySQL的基本架构。如下图所示：

MySQL可以分为Server层和存储引擎层两部分。Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，比如存储过程、触发器、视图等。存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始取代MyISAM成为了默认存储引擎。

也就是说，你执行CREATE TABLE语句建表的时候，如果不指定引擎类型，默认使用的就是InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，通过engine=MyISAM, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同，支持的功能也不同。不同的存储引擎共用一个Server层，也就是从连接器到执行器的部分。接着，我们结合之前提到的SELECT语句来分析一下它是如何在其中运行的。

2.1、连接器

首先，要使用我们数据库，第一步操作就是使用：mysql -uusername -p连接数据库，-u指定用户名，-p指定密码。连接命令中的mysql是客户端工具，是访问MySQL服务器的客服端程序，用来跟服务端建立连接。在进行TCP协议的三次握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。

如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。如果此时管理员对该用户进行了某个权限的GRANT，需要该用户断开本次连接后，重新与其建立连接才可以生效。我们也可以用show processlist语句查看当前的连接。

mysql> show processlist;
+----+-----------------+-----------------+--------+---------+---------+------------------------+------------------+
| Id | User            | Host            | db     | Command | Time    | State                  | Info             |
+----+-----------------+-----------------+--------+---------+---------+------------------------+------------------+
|  5 | event_scheduler | localhost       | NULL   | Daemon  | 1389806 | Waiting on empty queue | NULL             |
| 22 | root            | localhost:57464 | mytest | Query   |       0 | init                   | show processlist |
+----+-----------------+-----------------+--------+---------+---------+------------------------+------------------+
2 rows in set (0.01 sec)

因为MySQL是基于C/S结构的软件，所以，我们的客户端通过这种方式与服务端建立连接，那么后续与服务端的交互就是面向于mysqld服务器程序了，基本交互流程大致为：

1、客户端通过服务服务器的程序—mysql语句连接服务器，并认证身份及权限客户端发送SQL指令
2、服务端接收SQL指令并处理SQL指令，返回指令操作结果
3、客户端接收结果进行解析，最后显示结果
4、客户端选择发送新SQL指令或断开与服务端连接释放资源

2.2、查询缓存

在建立连接后，我们查询数据，首先会经过查询缓存，缓存中会记录许多key-value，key是之前执行过的查询语句，value则对应语句的查询结果，如果在缓存中找不到对应的key才会继续往下方执行，但，在MySQL 8.0版本已经直接将查询缓存的整块功能删掉了，这里不多做赘述。

2.3、分析器

到这里就真正执行SQL语句了。首先，MySQL需要知道我们现在的语句要做什么，因此需要对SQL语句做解析。分析器先会对我们的语句做词法与语义的分析。我们输入的SQL语句是由多个单词（字符串）和空格组成的，MySQL需要识别出里面的字符串分别是什么，代表了什么意思。

MySQL将我们输入的"SELECT"这个字段识别出来，得知这是一个查询语句，用于检索表内记录。它也要把字符串test_table识别成数据库表test_table，把字符串sname识别成列sname。

识别完成后，就要做语法分析了。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。就比如前面的SELECT少写了一个S，就会收到“You have an error in your SQL syntax”的错误提示。

当然，也会检查语句中的部分是否有误，比如输入一个错误的列名，此列在表中并不存在，则会提示不存在对应列。

2.4、优化器

经过了分析器，MySQL知道我们的语句要做什么事了。在开始完成要做的事之前，还要先经过优化器的处理。

优化器是对于我们SQL语句进行一定的优化方案，就好比我们使用CREATE TABLE语句建数据库时通过SHOW CREATE TABLE TABLE_NAME \G查看存储的建表语句的时候，发现往往我们建表的语句和实际存储的语句是不一样的。又比如数据库表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表联结（JOIN）的时候，决定各个表的联结顺序以及过滤条件如何进行判断。

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

2.5、执行器

MySQL通过分析器知道了我们的语句要做什么事情，再通过优化器知道了该怎么做，于是就进入了执行器开始执行我们的SQL语句。

在执行的过程中，MySQL会判断目前连接用户是否具有对于对应数据库表的对应操作权限，因此，这里会判断目前连接用户是否对于test_table表具有SELECT权限，如果没有，就会返回该用户没有对于本表的对应权限的错误。

如果该连接用户具有操作test_table表的对应权限，就打开对应test_table表继续执行SQL语句。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

这里假设未设置索引，且版本在8.0以上，引擎默认，所以调用InnoDB引擎接口取test_table表的第一行，判断sname的值是不是’王五’，如果不是则跳过，用引擎接口取到下一行。如果是则将这行存在结果则找出数据，再调用引擎接口取到下一行，直到查找到最后一行，最后执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
如果是有设置索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。