乐趣区

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

jiezi

欢送来到 GreatSQL 社区分享的 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 的值是不是 ’ 王五 ’，如果不是则跳过，用引擎接口取到下一行。如果是则将这行存在后果则找出数据，再调用引擎接口取到下一行，直到查找到最初一行，最初执行器将上述遍历过程中所有满足条件的行组成的记录集作为后果集返回给客户端。
  • 如果是有设置索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中曾经定义好的。

3、小结

以上是对 MySQL 整体大抵构造的总结，通过一条 SQL 语句，咱们大抵察看了全貌，作为 DBA 须要对 MySQL 整体构造有肯定意识，这样有助于咱们更好地了解 MySQL 的外部是如何运作的，在晓得语句如何运作的状况下，也不便咱们疾速锁定问题和增强对具体业务的利用能力。

Enjoy GreatSQL :)

文章举荐：

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6…

万答 #12，MGR 整个集群挂掉后，如何能力主动选主，不必手动干涉
https://mp.weixin.qq.com/s/07…

『2021 数据技术嘉年华·ON LINE』：《MySQL 高可用架构演进及实际》
https://mp.weixin.qq.com/s/u7…

一条 sql 语句慢在哪之抓包剖析
https://mp.weixin.qq.com/s/AY…

万答 #15，都有哪些状况可能导致 MGR 服务无奈启动
https://mp.weixin.qq.com/s/in…

技术分享 | 为什么 MGR 一致性模式不举荐 AFTER
https://mp.weixin.qq.com/s/rN…

对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr…

GitHub:
https://github.com/GreatSQL/G…

Bilibili：
https://space.bilibili.com/13…

微信 &QQ 群：
可搜寻增加 GreatSQL 社区助手微信好友，发送验证信息“加群”退出 GreatSQL/MGR 交换微信群

QQ 群：533341697
微信小助手：wanlidbc

本文由博客一文多发平台 OpenWrite 公布！

退出移动版