【MySQL】MySQL基础架构

MySQL是一个关系型数据库管理系统，由瑞典公司开发，目前属于Oracle公司。 MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

基础架构：

MySQL的

基本架构示意图，可以清楚地看到SQL语句在MySQL的各个功能模块中的执行过程：

SELECT 语句的执行过程

SELECT 语句的执行过程为：连接、查询缓存、词法分析，语法分析，语义分析，构造执行树，生成执行计划、执行器执行计划

大体来说，MySQL可以分为Server层和存储引擎层两部分，进一步细分则可以分为四层：

（1）连接层

主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层上引入了线程池的概念。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。如果数据库创建和销毁连接的开销很大，或者相对于所执行的具体数据操作，连接所耗的资源过多，此时就可能通过连接池来改进性能。

连接命令常用写法：

mysql -h$ip -P$port -u$user -p

连接命令中的mysql是客户端工具，用来跟服务端建立连接。在完成经典的TCP握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表mysql.user里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

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

show processlist命令可以显示目前所有连接信息，其中的Command列显示为“Sleep”表示现在系统里面有一个空闲连接。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wo6L8Yp0-1610703559801)(https://s3-us-west-2.amazonaws.com/secure.notion-static.com/241eefaa-0fd8-4f70-bbf9-7d8018e624ff/Untitled.png)]

MYSQL的默认设置是在数据库连接超过8小时没有使用后将其断开，如果将这个时间改成更大的数值，那么连接超时所需的时间就会更长，也就意味着更不容易超时。网络上提供的修改方法一般是修改/etc/my.cnf，在这个文件中添加一行wait_timeout=你需要设置的超时时间 。实际上有一种比较简单的方法来修改这个参数：

首先作为超级用户登录到MYSQL，注意必须是超级用户，否则后面会提示没有修改权限。然后输入

show global variables like 'wait_timeout';

--回车执行后显示目前的超时时间：
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
123456789101112131415

上面显示的是默认的超时时间，即8个小时(单位是秒)。现在重新设置该参数，例如我们要将超时时间设置成10个小时，可以输入：

set global wait_timeout=36000;

--回车执行，显示：

Query OK, 0 rows affected (0.00 sec)
12345

这种方法比较直观，而且设置的参数立即生效。但如果/etc/my.cnf中没有配置，则重启服务后，global变量会从/etc/my.cnf中读取新的变量值。

如果 MYSQL_OPT_RECONNECT 没有被设置为 1（开启），那么mysql_ping()不会完成自动重连，只是简单返回一个error。但是当设置了MYSQL_OPT_RECONNECT为1时，超时后再查看processlist，则自动建立的连接不在列表中，但事实上连接确实建立并被使用了。

（2）服务层

服务层的系统管理/控制工具分为：

①查询缓存：查询请求后，会先到查询缓存看看，如果查询缓存有Key（执行的SQL）命中的查询结果，查询语句就可以直接去查询缓存中取数据（Value）返回。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

大多数情况下我会建议不要使用查询缓存，因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。很长时间才会更新一次的静态表，比如，系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL也提供了这种“按需使用”的方式。你可以将参数query_cache_type设置成DEMAND，这样默认都不使用查询缓存。对于对于频繁查询的需要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样：

mysql> select SQL_CACHE * from T where ID=10；

需要注意的是，MySQL 8.0版本直接将查询缓存的整块功能删掉了，也就是说8.0开始彻底没有这个功能了。

②SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果。③分析器：SQL命令传递到解析器的时候会被解析器验证和解析④优化器：SQL语句在查询之前会使用查询优化器对查询进行优化，比如有where条件时，优化器来决定先投影还是先过滤，以及索引优化等，在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序等，优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器。

⑤**执行器：**MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，在工程实现上，如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限

在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

（3）引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM等多个存储引擎，现在最常用的存储引擎是InnoDB，正是可插拔的、模块化插件架构便于切换引擎。

（4）存储层

数据存储层，主要是将数据存储在运行于设备的文件系统之上，并完成与存储引擎的交互。