16、MySQL进阶：体系结构初探

![](https://cloud.cxykk.com/images/2024/2/2/1546/1706860015647.png)

### **数据库**

根据数据库的类型或者功能或者数据库的发展方向，可以把数据库大致分成两类

- 关系型数据库
- 非关系性数据库，或者叫 SQL 和 NoSQL

当然了关系型数据库又可以分为传统的关系型数据库和 NewSQL

![](https://cloud.cxykk.com/images/2024/2/2/1547/1706860021846.png)

------------

### MySQL 主流分支

MySQL 从最初的 1.0、3.1 到后来的 5.x ,到今天的8.x，发生了各种各样的变化。

被Oracle 收购后，MySQL 的版本其实主要有几个分支，除了需要付费的 MySQL 企业版本，还有很多 MySQL 社区版本。

![](https://cloud.cxykk.com/images/2024/2/2/1547/1706860027713.png)

------------

### 主要有3个分支

- 第一条分支 MySQL 官方版本 ，目前已经到了8.0
- 第二个非常流行的开源分支版本叫 Percona Server，它是 MySQL 的技术支持公司 Percona 推出的，也是在实际工作中经常碰到的。Percona Server 在 MySQL 官方版本的基础上做了一些补丁和优化，同时推出了一些工具。
- 另外一个非常不错的版本叫 MariaDB，它是 MySQL 的公司被 Oracle 收购后，MySQL 的创始人 Monty 先生，按原来的思路重新写的一套新数据库，同时也把 InnoDB 引擎作为主要存储引擎，也算 MySQL 的分支。

------------

### **MySQL 体系结构由 Client Connectors 层、MySQL Server 层及存储引擎层组成**

**Client Connectors 层**

负责处理客户端的连接请求，与客户端创建连接。目前 MySQL 几乎支持所有的连接类型，例如常见的 JDBC、Java、Python、Go 等。

**MySQL Server 层**

MySQL Server 层主要包括 Connection Pool、Service & utilities、SQL interface、Parser解析器、Optimizer 查询优化器、Caches 缓存等模块。

![](https://cloud.cxykk.com/images/2024/2/2/1550/1706860211397.png)

- Connection Pool，负责处理和存储数据库与客户端创建的连接，一个线程负责管理一个连接。Connection Pool 包括了用户认证模块，就是用户登录身份的认证和鉴权及安全管理，也就是用户执行操作权限校验。
- Service & utilities 是管理服务&工具集，包括备份恢复、安全管理、集群管理服务和工具。
- SQL interface，负责接收客户端发送的各种 SQL 语句，比如 DML、DDL 和存储过程等。
- Parser 解析器会对 SQL 语句进行语法解析生成解析树。
- Optimizer 查询优化器会根据解析树生成执行计划，并选择合适的索引，然后按照执行计划执行 SQL 语言并与各个存储引擎交互。
- Caches 缓存包括各个存储引擎的缓存部分，比如：InnoDB 存储的 Buffer Pool、MyISAM 存储引擎的 key buffer 等，Caches 中也会缓存一些权限，也包括一些 Session 级别的缓存。

------------

### **插件式的存储引擎层**

存储引擎包括 MyISAM、InnoDB，以及支持归档的 Archive 和内存的 Memory 等。MySQL是插件式的存储引擎，只要正确定义与 MySQL Server 交互的接口，任何引擎都可以访问MySQL 。

**存储引擎底部是物理存储层，是文件的物理存储层，包括二进制日志、数据文件、错误日志、慢查询日志、全日志、redo/undo 日志等。**

------------

### 一条SQL的执行过程

我们用一条 SQL SELECT 语句的执行轨迹来说明客户端与 MySQL 的交互过程

![](https://cloud.cxykk.com/images/2024/2/2/1550/1706860217109.png)

1. 通过客户端/服务器通信协议与 MySQL 建立连接。
2. 查询缓存，这是 MySQL 的一个可优化查询的地方，如果开启了 Query Cache 且在查询缓存过程中查询到完全相同的 SQL 语句，则将查询结果直接返回给客户端；如果没有开启Query Cache 或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成解析树。
3. 预处理器生成新的解析树。
4. 查询优化器生成执行计划。
5. 查询执行引擎执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的 API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server 过滤后将查询结果缓存并返回给客户端。若开启了 Query Cache，这时也会将SQL 语句和结果完整地保存到 Query Cache 中，以后若有相同的 SQL 语句执行则直接返回结果。

------------

### **鸡肋的查询缓存**

说下这个查询缓存 ，其实很鸡肋的， **8.0已经默认去掉了查询缓存。**

因为查询缓存往往弊大于利。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

一般建议在静态表里使用查询缓存，什么叫静态表呢？就是一般我们极少更新的表。比如，一个系统配置表、字典表，那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将my.cnf参数 query_cache_type 设置成 DEMAND。

```python
my.cnf
#query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE关键词时才缓存
query_cache_type=2

```

这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

```python
mysql> select SQL_CACHE * from test where ID=5；
```

查看当前mysql实例是否开启缓存机制
```python
mysql> show global variables like "%query_cache_type%";
```
监控查询缓存的命中率:

```python
mysql> show status like'%Qcache%'; # 查看运行的缓存信息
```

![](https://cloud.cxykk.com/images/2024/2/2/1550/1706860222776.png)

- Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
- Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
- Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
- Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
- Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
- Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
- Qcache_queries_in_cache:当前缓存中缓存的查询数量。
- Qcache_total_blocks:当前缓存的block数量。