前言

工作很长时间了，对于数据库的掌握程度却仅仅停留在表面的CRUD阶段，对于深层次的原理和技术知识了解的少之又少，随着岁数不断的增长。很多时候，出去找工作很迷茫，被面试官问的感觉自己很菜。现在利用工作休息时间，把自己现有的资料和新学的知识进行总结。也算是一种深层次的理解和掌握。本文章收录在MySQL性能优化+原理+实战专栏，更多的MySQL优化点击此处查看开篇介绍。
在以后的学习中，我会创建一个没有权限的用户，进行MySQL权限和优化的实战。
关于有些系统变量的作用及在学习中遇见的ERROR会全部会收录到开篇介绍一文当中。

在学习MySQL优化的时候，我们需要大致了解MySQL的体系架构，网上看了很多资料，大致总结了一下，如图

我们可以明确的看出MySQL的最上层是连接器，下面有连接池、系统管理和控制工具、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。大致可以分为网络连接层、数据库服务层、存储引擎层和系统文件层四大部分。接下来，我们就来简单说说每个部分的组成信息

一、网络连接层

位于整个MySQL体系架构的最上层，主要担任客户端连接器的角色。提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言。例如：Java、C、C++、Python等，各语言都是通过各自的API接口与MySQL建立连接

二、数据库服务层

数据库服务层是整个数据库服务器的核心，主要包括了连接池、SQL接口、解析器、查询优化器、缓存和系统管理和控制工具等部分。

2.1 连接池

多个线程都会去获取一个数据库连接来访问数据库。不能每次都创建一个新的数据库连接，用完然后销毁，这样效率非常低下，因此客户端需要一个数据库连接池，每次从连接池里拿一个连接来处理SQL请求，用完之后又放回连接池中，避免频繁创建销毁数据库连接，从而得知连接池的作用主要负责存储和管理客户端与数据库的连接信息

连接完成后，如果没有后续的动作，这个连接就处于空闲状态。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数wait_timeout 控制的，默认值是 8 小时

mysql> create user 'mutlis'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.02 sec)

[root@mysql2 bin]# mysql -umutlis -p123456
Server version: 8.0.32 MySQL Community Server - GPL
## 忽略部分登陆代码 ##
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set persist wait_timeout=28800;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

##使用管理员授予我们当前用户SYSTEM_VARIABLES_ADMIN(启用修改或保留全局系统变量权限)后继续修改 ##
mysql> set persist wait_timeout=100;
mysql> show variables like 'wait_timeout%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

吃惊了吧，设置了wait_timeout 不起作用，原因是wait_timeout参数：

• 对于非交互式连接，类似于jdbc连接wait_timeout的值继承自服务器端全局变量wait_timeout。
• 对于交互式连接，类似于mysql客户端连接wait_timeout的值继承自服务器端全局变量interactive_timeout。

我们使用的是交互式连接，所以当前wait_timeout是由全局变量interactive_timeout决定。

mysql> set persist interactive_timeout=20;
Query OK, 0 rows affected (0.01 sec)

## 退出数据库后重新登陆查询 ##
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 200   |
+---------------+-------+
1 row in set (0.00 sec)

判断一个连接的空闲时间，可通过show processlist & show full processlist & information_schema.processlist等查询Sleep状态的时间

• full的区别是：如果你不使用FULL关键词，则只显示每个查询的前100个字符
• 如果您有SUPER权限，您可以看到所有线程，否则只能看到自己的线程。

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

重新登陆我们刚才创建的用户，等待20S后

mysql> select current_user();
ERROR 4031 (HY000): The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.
No connection. Trying to reconnect...
Connection id:    183
Current database: *** NONE ***

+------------------+
| current_user()   |
+------------------+
| mutlis@localhost |
+------------------+
1 row in set (0.00 sec)

客户端由于不活动而被服务器断开连接。所以在这里，我们可以根据Sleep的时长和数量进行调整，缓解服务器的压力，一般默认值即可。

2.2 SQL接口

我们使用客户端连接数据库，使用 select * from mysql.user查询数据的时候，而SQL接口它就负责接收客户端发送过来的各种SQL语句，并将SQL语句发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端。

2.3 解析器

主要负责对请求的SQL解析成一棵“解析树”，然后根据MySQL中的一些规则对“解析树”做进一步的语法验证，确认其是否合法

2.4 查询优化器

“解析树”通过了解析器的语法检查，此时就会由优化器将其转化为执行计划并对查询语句进行优化，它使用的是“选择-投影-连接”策略进行查询（点击此处了解选择-投影-连接原理）然后与存储引擎进行交互

2.5 缓存

MySQL的缓存是由一系列的小缓存组成的。例如：MySQL的表缓存，记录缓存，MySQL中的权限缓存，引擎缓存等。MySQL中的缓存能够提高数据的查询性能，如果查询的结果能够命中缓存，则MySQL会直接返回缓存中的结果信息。但是查询缓存已经在8.0版本中删除

2.6系统管理和控制工具

提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等。

三、存储引擎层

MySQL中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互。值得一提的是，MySQL中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时，接口屏蔽了不同存储引擎之间的差异。MySQL中，最常用的存储引擎就是InnoDB和MyISAM。

InnoDB和MyISAM存储引擎需要我们重点点掌握，也是以后我们成为架构师必知必会的内容，后面文章中详细讲解。

四、系统文件层

系统文件层主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。

4.1 日志文件

• Error log错误日志： 记录遇到的所有严重的错误信息，每次启动关闭的信息信息；

• Binary log 二进制日志： 也就是binlog，记录所有修改数据库的操作；

• Query log 查询日志： 记录所有查询操作，体积较大，开启后对性能有影响。

• Slow Query log 慢查询日志： 记录所有执行时间超过long_query_time的SQL语句和达到min_examined_row_limit条距离的语句。

• InnoDB redo log： 记录InnoDB所做的物理变更和事务信息。
  中继日志：只从复制产生，用户slave机读取，复制master机的SQL操作

后面都会详细讲解。

4.2 数据文件

数据文件中主要包括了：db.opt文件、frm文件、MYD文件、MYI文件、ibd文件、ibdata文件、ibdata1文件、ib_logfile0和ib_logfile1文件等。

• db.opt文件： 主要记录当前数据库使用的字符集和检验规则等信息。
• frm文件： 存储数据表的结构信息，主要是数据表相关的元数据信息，包括数据表的表结构定义信息，每张表都会有一个frm文件。值得注意的是：MySQL8版本中的innodb存储引擎的表没有frm文件。而是合并在*.ibd文件中
• ibd文件： 存放Innodb存储引擎的数据文件和索引文件，主要存放的是独享表空间的数据和索引，每张表对应一个.ibd文件。
• ibdata文件： 存放Innodb存储引擎的数据文件和索引文件，主要存放的是共享表空间的数据和索引，所有表共用一个（或者多个）.ibdata文件，可以根据配置来指定共用的.ibdata文件个数。
• ibdata1文件： MySQL的系统表空间数据文件，主要存储MySQL的数据表元数据、Undo日志等信息。
• ib_logfile0和ib_logfile1文件： MySQL数据库中的Redo log文件，主要用于MySQL实现事务的持久性。如果在某个时间点MySQL发生了故障，此时如果有脏页没有写入到数据库的ibd文件中，在重启MySQL的时候，MySQL会根据Redo Log信息进行重做，将写入Redo Log并且尚未写入数据表的数据进行持久化操作。

4.3 配置文件

用于存在MySQL所有的配置信息，在Unix/Linux环境中是my.cnf文件，在Windows环境中是my.ini文件。

4.4 pid文件

存放MySQL进程运行时的进程号的文件，主要存在于Unix/Linux环境中，具体的存储目录可以在my.cnf或者my.ini文件中进行配置。

4.5 socket文件

socket文件和pid文件一样，都是MySQL在Unix/Linux环境中运行才会有的文件。在Unix/Linux环境中，客户端可以直接通过socket来连接MySQL

小结

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。mysql执行查询的过程如下

客户端先发送查询语句给服务器，进行sql解析，生成解析树，再预处理，生成第二个解析树，最后经过优化器，生成真正的执行计划根据执行计划，调用存储引擎的API来执行查询将结果返回给客户端。