MySQL的逻辑架构

逻辑架构

1. 连接层

负责与客户端建立TCP连接，经过三次握手成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。

TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个连接池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

2. 服务层

SQL Interface: SQL接口
接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口
Parser: 解析器
- 对SQL语句进行语法分析、语义分析，解析为语法树。
- 如果解析过程出错，说明SQL语法是错误的。
- 验证客户端是否具有执行权限。
- 对SQl查询进行语法上的优化，进行查询重写。
Optimizer: 查询优化器
- 使用解析器返回的语法树生成一个执行计划。执行计划表明该SQL应该使用哪儿些索引、表之间的连接顺序如何。
- 然后使用生成的执行计划调用存储引擎提供的方法来真正的执行查询，并将结果返回给用户。
- 它使用选取-投影-连接策略进行查询：
  SELECT id,name FROM student WHERE gender = '女'; -- 1. 先根据WHERE语句进行选取 -- 2. 再根据id和name进行属性投影 -- 3. 将上述两个条件连接起来形成查询结果
Caches & Buffers：查询缓存组件
MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。这个查询缓存可以在不同客户端之间共享。
在MySQL8.0缓存已经被去除，因为缓存的命中条件太低。

3. 引擎层

插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

4.存储层

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用DAS、NAS、SAN等各种存储系统。

SQL执行流程

1. 查询缓存

Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。

在MySQL8.0下，查询缓存已经被移除，主要是因为：

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。
只有相同的查询操作才会命中查询缓存，两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。因此 MySQL 的查询缓存命中率不高。
如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，那这个请求就不会被缓存。比如函数NOW()所得到的结果始终不同
MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了 INSERT 、 UPDATE 、 DELETE、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！对于更新压力大的数据库来说，查询缓存的命中率会非常低。

MySQL5.7下开启查询缓存：

在MySQL5.7下，查询缓存的功能是默认关闭的

查看查询缓存是否开启：

mysql> show variables like 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.02 sec)

开启查询缓存，修改my.cnf

# 共有三个值可以选择：
# 0 关闭
# 1 开启
# 2 按需开启，可以在SQL语句中使用SQL_CACHE和SQL_NO_CACHE显示指定查询缓存，如下示例
# 	select SQL_CACHE * from test;
# 	select SQL_NO_CACHE * from test;
query_cache_type = 2
query_cache_size = 600000

查询查询缓存的命中率：

show status like '%Qcache%';

Qcache_free_blocks表示已分配内存块中空闲块数量；
Qcache_total_blocks表示当前查询缓存占用的内存块数量；
Qcache_free_memory表示缓存空闲空间大小；
Qcache_hits表示缓存命次数；
Qcache_inserts表示缓存未命中时，数据写入缓存次数；
Qcache_queries_in_cache表示缓存查询语句数量；
Qcache_lowmem_prunes表示缓存修剪次数，缓存满时，会使用LRU算法移除最久未被使用缓存，此值较大，说明缓存空间太小；
Qcache_not_cached表示没有被缓存的查询sql数量。

2. 解析器

解析器负责对SQL语句进行语法分析、语义分析。

主要分为以下几个步骤：

词法分析
MySQL 从你输入的select这个关键字识别出来，这是一个查询语句。它也要把字符串T识别成“表名 T，把字符串ID识别成列 ID。
语法分析
根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果SQL语句正确，则会生成一个这样的语法树：
```
select department_id,job_id,avg(salary) from employees group by department_id;
```
image-20220224113139374

3. 优化器

在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引检索等。查询优化又分为逻辑查询优化和物理查询优化两个阶段：

逻辑查询优化：通过改变SQL语句让SQL查询变得更高效，同时为屋里查询优化提供更多的候选执行计划。通常采用对SQL语句进行等价变换并对查询重写。比如，对等价谓词重写、对视图重写、对子查询优化、条件简化、嵌套连接消除等。
物理查询优化：也是对查询重写，他会通过物理计算，计算各种物理路径的代价，从中虚着呢最小的作为执行计划。在这个阶段中，对于单表与多表的连接操作，需要高效的使用索引，提升查询效率。

优化器最终将会生成一个执行计划。

4. 执行器

执行器负责执行优化器提供的执行计划。这时候，MySQL才开始读取真实的数据表。

根据执行计划执行SQL，并调用底层存储引擎的API，真实去访问数据，获取结果，并返回给客户端
如果是8.0以下，且开启了查询缓存，将会把查询结果缓存到查询缓存中

查看SQL的执行流程

开启profiling

确认profiling是否开启：

-- 默认查询session级别的： select @@session.profiling;
-- 也可以查询global级别的： select @@global.profiling;
mysql> select @@profiling;   
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.05 sec)

开启profiling：

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

准备测试表

create table EMP
(
    EMPNO    int         not null
        primary key,
    ENAME    varchar(10) null,
    JOB      varchar(9)  null,
    MGR      int         null,
    HIREDATE date        null,
    SAL      double      null,
    COMM     double      null,
    DEPTNO   int         null
);

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-03', 3000, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7934, 'MILLER', 'CLERK', 7782, '1981-01-23', 1300, null, 10);

MySQL5.7版本

未开启查询缓存

mysql> Select * from EMP;
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000680 |   开始
| checking permissions | 0.000087 |   检查权限
| Opening tables       | 0.000253 |   打开表
| init                 | 0.000457 |   初始化
| System lock          | 0.000168 |   开启系统锁
| optimizing           | 0.000032 |   sql优化
| statistics           | 0.000167 |   统计数据
| preparing            | 0.000124 |   准备
| executing            | 0.000019 |   执行
| Sending data         | 0.001543 |   发送数据
| end                  | 0.000065 |   结束
| query end            | 0.000118 |   查询结束
| closing tables       | 0.000127 |   关闭表
| freeing items        | 0.000242 |   释放空间
| cleaning up          | 0.000224 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

开启了查询缓存

第一次执行：

mysql> show profile for query 8;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000203 |
| Waiting for query cache lock   | 0.000074 |
| starting                       | 0.000020 |
| checking query cache for query | 0.001122 |
| checking permissions           | 0.000095 |
| Opening tables                 | 0.000510 |
| init                           | 0.000269 |
| System lock                    | 0.001001 |
| Waiting for query cache lock   | 0.000040 |
| System lock                    | 0.001972 |
| optimizing                     | 0.000067 |
| statistics                     | 0.000545 |
| preparing                      | 0.000153 |
| executing                      | 0.000015 |
| Sending data                   | 0.003646 |
| end                            | 0.000057 |
| query end                      | 0.000075 |
| closing tables                 | 0.000069 |
| freeing items                  | 0.000057 |
| Waiting for query cache lock   | 0.000014 |
| freeing items                  | 0.000527 |
| Waiting for query cache lock   | 0.000106 |
| freeing items                  | 0.000069 |
| storing result in query cache  | 0.000325 |  存储到查询缓存中
| cleaning up                    | 0.000100 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.01 sec)

第二次查询：

mysql> show profile for query 9;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000238 |
| Waiting for query cache lock   | 0.000027 |
| starting                       | 0.000011 |
| checking query cache for query | 0.000279 |  检查缓存
| checking privileges on cached  | 0.000094 |
| checking permissions           | 0.000345 |
| sending cached result to clien | 0.000528 |  使用缓存数据
| cleaning up                    | 0.000070 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

MySQL8.0版本

mysql> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000422 |
| Executing hook on transaction  | 0.000026 |
| starting                       | 0.000018 |
| checking permissions           | 0.000015 |
| Opening tables                 | 0.000234 |
| init                           | 0.000020 |
| System lock                    | 0.000022 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000042 |
| preparing                      | 0.000045 |
| executing                      | 0.000297 |
| end                            | 0.000105 |
| query end                      | 0.000010 |
| waiting for handler commit     | 0.000033 |
| closing tables                 | 0.000021 |
| freeing items                  | 0.000091 |
| cleaning up                    | 0.000086 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)

最后更新于1年前