五分钟了解一下MySQL的体系结构

阳光穿透心脏的1/2处 2022-09-09 06:46 208阅读 0赞

### 文章目录 ###

*  MySQL体系结构
 *   *  Client connector
     *  MySQL Server
     *   *  Connection pool
         *  Service utilities
         *  SQL 层
         *   *  SQL interface
             *  解析器（Parser）
             *  优化器（Optimizer）
             *  缓存（Cache）
         *  存储引擎
         *  物理存储层
     *  参考

# MySQL体系结构 #

下图来自《MySQL技术内幕(InnoDB存储引擎)第2版》书中的图：

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16]

然后我们根据这个图来讲解一下每个部分的功能和作用。

## Client connector ##

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 1]

首先我们可以看到最上面的 connector部分**负责处理客户端的连接请求，与客户端创建连接**，目前MySQL几乎支持所有的连接类型，例如常见的JDBC、Python、GO等。

应用程序通过接口( 如ODBC、JDBC)来连接MySQL。最先连接处理的是连接层，连接层包括**通信协议、线程处理、用户名密码认证**三个部分。

*  通信协议负责检测客户端版本是否兼容MySQL服务端。
 *  线程处理是指每一个连接请求都会分配一个对应的线程，
 *  相当于一条SQL对应一个线程，一个线程对应一个逻辑 CPU，并会在多个逻辑CPU之间进行切换。
 *  用户认证也就是用户登录身份的认证和鉴权和安全管理,就是用户执行操作权限校验。

## MySQL Server ##

下面就是MySQL Server部分

### Connection pool ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 2]

这里包括connection pool，数据库给客户端创建的连接都在这里处理和存储，用一个线程管理一个连接，包括了用户认证：也就是用户登录身份的认证和鉴权和安全管理：就是用户执行操作权限校验。验证用户名和密码是否正确（数据库mysql的user表中进行验证），如果错误返回错误通知（Access denied for user ‘’@‘localhost’ to database ‘mysql’），如果正确，则会去 mysql 的权限表（mysql中的 user、db、columns\_priv、Host 表，分别存储的是全局级别、数据库级别、表级别、列级别、配合 db 的数据库级别）查询当前用户的权限。

### Service utilities ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 3]

service utilities是管理服务&工具集，包括备份恢复、安全管理、集群管理服务和工具。

### SQL 层 ###

SQL层包含权限判断、查询缓存、解析器、预处理、查询优化器、缓存和执行计划。

*  查询缓存通过 Query Cache 进行操作，如果数据在 Query Cache 中，则直接返回结果给客户端。
 *  查询解析器针对 SQL 语句进行解析，判断语法是否正确。预处理器对解析器无法解析的语义进行处理。
 *  优化器对 SQL 进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的 API 接口，通过存储引擎层访问数据。

#### SQL interface ####

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 4]

负责接收客户端来的各种SQL语句比如DML、DDL和存储过程等

#### 解析器（Parser） ####

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 5]

对客户端传来的 sql 进行分析，这将包括预处理与解析过程，并进行关键词的提取、解析，并组成一个解析树。具体的解析词包括但不局限于 `select/update/delete/or/in/where/group by/having/count/limit` 等，如果分析到语法错误，会直接抛给客户端异常：`ERROR:You have an error in your SQL syntax`.

比如：`select * from user where userId =1234`;

在分析器中就通过语义规则器将`select from where`这些关键词提取和匹配出来,mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：`unknown column in field list`.

#### 优化器（Optimizer） ####

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 6]

进入优化器说明sql语句是符合标准语义规则并且可以执行。优化器**会根据执行计划选择最优的选**择，匹配合适的索引，选择最佳的方案。比如一个典型的例子是这样的：

表T,对A、B、C列建立联合索引(A,B,C)，在进行查询的时候，当sql查询条件是:`select xx where B=x and A=x and C=x`.很多人会以为是用不到索引的，但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:`where A=x and B=x and C=x`,这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器

#### 缓存（Cache） ####

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 7]

> 也称为查询缓存，存储的数据是以键值对的形式进行存储，如果开启了缓存，那么在一条查询sql语句进来时会先判断缓存中是否包含当前的sql语句键值对，如果存在直接将其对应的结果返回，如果不存在再执行后面一系列操作。如果没有开启则直接跳过。

> 相关操作：
> 
>  *  查看缓存配置：show variables like ‘have\_query\_cache’;
>  *  查看是否开启：show variables like ‘query\_cache\_type’;
>  *  查看缓存占用大小：show variables like ‘query\_cache\_size’;
>  *  查看缓存状态信息：show status like ‘Qcache%’;

> 缓存失效场景：
> 
> 1.  查询语句不一致。前后两条查询SQL必须完全一致。
> 2.  查询语句中含有一些不确定的值时，则不会缓存。比如 now()、current\_date()、curdate()、curtime()、rand()、uuid()等。
> 3.  不使用任何表查询。如 select ‘A’;
> 4.  查询 mysql、information\_schema 或 performance\_schema 数据库中的表时，不会走查询缓存。
> 5.  在存储的函数，触发器或事件的主体内执行的查询。
> 6.  如果表更改，则使用该表的所有高速缓存查询都变为无效并从缓存中删除，这包括使用 MERGE 映射到已更改表的表的查询。一个表可以被许多类型的语句改变，如 insert、update、delete、truncate rable、alter table、drop table、drop database。

通过上面的失效场景可以看出缓存是很容易失效的，所以如果不是查询次数远大于修改次数的话，使用缓存不仅不能提升查询效率还会拉低效率（每次读取后需要向缓存中保存一份，而缓存又容易被清除）。所以在 **MYSQL5.6默认是关闭缓存**的，并且在 8.0 直接被移除了。

### 存储引擎 ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 8]

再下面是MySQL所支持的各个存储引擎，这里只是举例写了一些。有最原始的MyISAM，以及InnoDB、支持归档的Archive和内存的Memory等。MySQL是插件式的存储引擎，只要写好跟MySQL Server的接口，任何引擎都可以进入MySQL里来，这也是MySQL流行的原因之一.

### 物理存储层 ###

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 9]

最下面是物理存储层，是文件的物理存储，包括二进制日志、数据文件、错误日志、慢查询日志、全日志，redo/undo日志等。

## 参考 ##

[原理：一条 sql 的执行过程详解][sql]

[10分钟搞透：技术人必会的MySQL体系结构与存储引擎！][10_MySQL]

[MySQL体系结构与存储引擎][MySQL]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/b82f6e49454f41f5a55c404864429cf4.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/8f3e89bbe4da47ffaf33b06f4b13e7d6.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220829/40219ce509544cb2b118c119c7eb63e9.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220829/9b6c2ea97d284d4c9f737b0db14402b2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 4]: /images/20220829/3bd2b8563ad94eeebdcc05e42769a460.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 5]: /images/20220829/eb90bf7d3c5949928f65775838ad51b4.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 6]: /images/20220829/46e3fd6a7b744844b0e0c31b3b255566.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 7]: /images/20220829/bfd0a37e585349b1ae9d58c707225eb2.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 8]: /images/20220829/a02887522a2f4b02a17e51aea139f4d4.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAYy4_size_20_color_FFFFFF_t_70_g_se_x_16 9]: /images/20220829/4f00eea8c79c4b8986f7551d72f68b56.png
[sql]: https://mp.weixin.qq.com/s/1XajvJfP93Q6pTdrGGdzKg
[10_MySQL]: https://mp.weixin.qq.com/s/nOqdo33GZ-EuXtHm46XgKA
[MySQL]: https://mp.weixin.qq.com/s/9B-q2U_dtgscGMf6_xBYkg