数据库►MySQL

MySQL架构体系及SQL查询执行流程初探

简述: 自工作以来，大部分的时间接触的关系型都是 MySQL 数据库系统，但使用都比较浅显基础(CRUD)。最近打算重新深入学习下 MySQL，安装的版本是 5.7.21，学习记录于此，便日后温习查看。

首先来看看 MySQL 的架构图，然后依次来看看每一部分是做什么的。

1：Client

连接器（Connectors），也就是通常所说的 MySQL 客户端。不同的编程语言提供了对应的 API 与 MySQL 服务系统进行交互通讯。

  • 通过 show variables like '%connection%; 来查看 MySQl 服务器的连接信息。默认最大连接数是151。
1
2
3
4
5
6
7
8
9
mysql> show variables like '%connection%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| max_connections          | 151             |
| max_user_connections     | 0               |
+--------------------------+-----------------+

2：MySQL Server

Server 层是 MySQL 提供服务的核心。主要分为以下几个模块（查询缓存 MySQL8.0 版本被删除，故此不做记录📝）

2.1：Connection Pool（连接池）

连接池主要实现以下功能

2.1.1：Authentication（进行身份验证）**

terminal 下连接命令如下 mysql -u root -h localhost - p。如果用户名密码认证通过，连接器会到 权限表 里面查出用户拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

一旦一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接完成后如果没有使用，该连接就处于 Sleep 状态。如果超过 MySQL 服务器配置的 wait_time 值，可通过 show variables like '%wait%'; 查看该值，默认是 28800 秒，该连接就会被回收，使用时就需要重新建立连接了。

如果客户端持续有请求，则一直使用同一个连接，则称该连接为长连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。由于建立连接比较复杂，应当减少连接的操作而使用长连接。但是过多使用长连接会使得内存飙升（因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放）导致 MySQL 异常重启。

解决方案1 ：定期断开长连接。使用一段时间后或者程序里面判断执行过一个占用内存的大查询后，定期断开连接，之后要查询时再重新建立连接；

解决方案2 ：对于 MySQL 5.7 及后续版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2.1.2：Thread Reuse（线程重用）
2.1.3：Connection Limits（连接限制)
2.1.4：Check Memory (内存检测)
2.1.5：缓存

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个 cpu 上运行，服务器会负责缓存线程。因此，不需要为每一个新建的连接创建或销毁线程，或者直接使用线程池功能，来维护大量的可用连接。

2.2：SQL Interface（SQL 接口）

用于执行 DML, DDL, 存储过程, 视图, 触发器等。接收客户端发来的sql请求查询，并返回需要查询的结构。

2.3：Parser（解析器）

解析器通过关键字将SQL语句进行解析（词法分析 + 预发分析），并生成对应的数据结构（解析树）。然后对其进行各种优化，包括重写查询，决定查询的读写顺序，以及选择须使用的索引等。MySQL解析器将使用MySQL语法规则验证和解析查询。

预处理器则根据一些MySQL规则进行进一步检查解析书是否合法，例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。

2.4：Optimizer（优化器）

优化器会将 解析树 转化成 执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是 找到这其中最好的执行计划。生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。

如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存，当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。

eg：怎么使用索引，怎么处理表的 Join 优化器处理

例如一个数据表建立的组合索引 idx_table_c1c2c3c4, 当查询条件为 where c3='c3' and c2='c2' and c4='c4' and c1='c1'; 时查询优化器会自动调整和优化，此时等价于 where c1='c1' and c2='c2' and c4='c4' and c3='c3';。但是最佳实践是按照索引建立的顺序来使用，以此来减少底层优化器的工作量。

解析器，预处理器，优化器处理流程 如下图

图片引用自 程序员历小冰

2.5：Executor（执行器）

根据 优化器 的执行计划去调用存储引擎 API 接口获取数据

3：Management Serveices & Utilities（系统管理和控制工具）

备份，安全恢复，复制，集群，管理，配置，迁移和元数据。

4：Pluggable Storage Engines（可插拔的存储引擎）

  • 存储引擎针对数据表

5：文件系统，文件，日志

补充: 一条 SQL 查询语句各个关键字解析的顺序过程

SQL 语句解析过程

补充: 使用show profile 分析 SQL 语句执行流程及其耗时情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> show profiles;
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration   | Query                                                      |
+----------+------------+------------------------------------------------------------+
|       36 | 6.57003650 | select * from employee                                     |
|       37 | 0.02818325 | select * from employee limit 1                             |
|       38 | 0.00047475 | select * from employee limit 1                             |
+----------+------------+------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 37;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.015488 | 0.000507 |   0.000047 |          512 |             0 |
| checking permissions | 0.000019 | 0.000015 |   0.000002 |            0 |             0 |
| Opening tables       | 0.000606 | 0.000166 |   0.000015 |          112 |             0 |
| init                 | 0.006238 | 0.000140 |   0.000000 |          136 |             0 |
| System lock          | 0.000083 | 0.000083 |   0.000000 |            0 |             0 |
| optimizing           | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
| statistics           | 0.000188 | 0.000189 |   0.000000 |          240 |             0 |
| preparing            | 0.000018 | 0.000017 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| Sending data         | 0.005445 | 0.000000 |   0.001869 |         1480 |             0 |
| end                  | 0.000012 | 0.000000 |   0.000010 |            0 |             0 |
| query end            | 0.000009 | 0.000000 |   0.000009 |            0 |             0 |
| closing tables       | 0.000007 | 0.000000 |   0.000007 |            0 |             0 |
| freeing items        | 0.000046 | 0.000000 |   0.000047 |            0 |             0 |
| cleaning up          | 0.000014 | 0.000000 |   0.000013 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

写着写着发现提及很多知识点还是未知，路漫漫其修远兮，需要学的东西还多，愿每日都比昨日进步，即使进步很微小。那先吃饭去了🍚

todo

  • 数据库实例了解

参考

MySQL实战45讲

一条SQL语句在MySQL中是如何执行的

本来以为mysql即将消亡，没想到它却越来越强大

MySQL体系架构

mysql体系架构图以及二进制安装mariadb

MySQL中的实例、数据库关系简介