【MySQL】逻辑架构

SQL server架构

MySQL是c/s架构，服务器端程序使用mysqld，客户端发送请求到服务器，服务器处理请求，在物理磁盘上的文件中找到数据，再返回给客户端。

针对MySQL5.7的架构图及执行步骤，MySQL server三层结构：

MySQL server三层结构

连接层

TCP连接池:限制连接数
线程池:控制具体操作的线程

服务层

SQL Interface: SQL接口 接收用户的sql命令,返回查询结果
parser:解析器 生成解析树
optimizer:查询优化器 生成执行计划,使用 选取-投影-连接 策略进行查询
caches&buffers:查询缓存 命中率低,mysql8.0中不再使用

引擎层

开发人员可以开发自己的存储引擎
查看存储引擎:show engines

SQL执行流程

MySQL服务器的查询流程

sql语句 —>

1. 查询缓存
   首先进入查询缓存查看是否有这条SQL语句,命中将直接返回给客户端；没有命中,就进入到解析器.
   这里要求所写的两条查询语句完全相同，以字符串形式存储，将查询语句视为key，查询结果为value，缓存中有key，则直接返回对应的value。
   查询失败的例子：
   调用now()函数，两次调用会产生不一样的结果，不适用查询缓存；
   对表结构或数据进行增删改查等操作，缓存中还是原来的数据；
   大多数情况下，查询缓存是鸡肋，mysql8.0中已将其去除。
2. 解析器
   进行词法分析、语法解析，生成分析树，明确sql接下来要做什么。接着进入查询优化器
3. 查询优化器
   进行查询优化，逻辑优化|物理优化， 确定sql语句的执行路径，全表检索or索引检索？生成最优的执行计划，接下来调用执行引擎。
4. 执行器
   判断用户是否具备权限，然后调用api接口，底层存储引擎查询数据，返回查询结果

查看执行计划

首先开启profiling:

查看profiling是否开启:
mysql> select @@profiling;
mysql> show variables like 'profiling';

设置profiling=1:
mysql> set profiling=1;

接下来执行一个SQL查询;

mysql> select * from employees;

查看当前会话所产生的所有 profiles：

mysql> show profiles; # 显示最近的几次查询

显示执行计划，查看程序的执行步骤：

mysql> show profile for query 7;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000147 |
| Executing hook on transaction  | 0.000018 |
| starting                       | 0.000025 |
| checking permissions           | 0.000020 |	#权限检查
| Opening tables                 | 0.000098 |	#打开表
| init                           | 0.000019 |	#初始化
| System lock                    | 0.000026 |	#锁系统
| optimizing                     | 0.000016 |	#优化查询
| statistics                     | 0.000041 |
| preparing                      | 0.000044 |	#准备
| executing                      | 0.000567 |	#执行
| end                            | 0.000022 |
| query end                      | 0.000010 |
| waiting for handler commit     | 0.000017 |
| closing tables                 | 0.000020 |
| freeing items                  | 0.000171 |
| cleaning up                    | 0.000056 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

此外，还可以查询更丰富的内容：

mysql> show profile cpu,block io for query 7;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000147 | 0.000030 |   0.000103 |            0 |             0 |
| Executing hook on transaction  | 0.000018 | 0.000003 |   0.000011 |            0 |             0 |
| starting                       | 0.000025 | 0.000006 |   0.000019 |            0 |             0 |
| checking permissions           | 0.000020 | 0.000004 |   0.000015 |            0 |             0 |
| Opening tables                 | 0.000098 | 0.000022 |   0.000078 |            0 |             0 |
| init                           | 0.000019 | 0.000004 |   0.000013 |            0 |             0 |
| System lock                    | 0.000026 | 0.000006 |   0.000020 |            0 |             0 |
| optimizing                     | 0.000016 | 0.000003 |   0.000012 |            0 |             0 |
| statistics                     | 0.000041 | 0.000010 |   0.000032 |            0 |             0 |
| preparing                      | 0.000044 | 0.000009 |   0.000034 |            0 |             0 |
| executing                      | 0.000567 | 0.000128 |   0.000442 |            0 |             0 |
| end                            | 0.000022 | 0.000004 |   0.000013 |            0 |             0 |
| query end                      | 0.000010 | 0.000002 |   0.000008 |            0 |             0 |
| waiting for handler commit     | 0.000017 | 0.000004 |   0.000014 |            0 |             0 |
| closing tables                 | 0.000020 | 0.000004 |   0.000015 |            0 |             0 |
| freeing items                  | 0.000171 | 0.000000 |   0.000179 |            0 |             0 |
| cleaning up                    | 0.000056 | 0.000000 |   0.000050 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

oracle中SQL执行流程

Oracle 中采用了共享池来判断 SQL 语句是否存在缓存和执行计划，通过这一步骤我们可以知道应该采用硬解析还是软解析。

共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。
软解析：库缓存中存在sql语句的执行计划，则直接拿来执行，直接进入执行器环节
硬解析：没有找到SQL语句和执行计划，则创建解析树进行解析，生成执行计划，进入优化器。为提升SQL效率，应尽量避免进入硬解析。
对于一些查询，可以使用绑定变量的方法减少硬解析，如：

SQL> select * from player where player_id = 10001;
# 使用绑定变量法优化：
SQL> select * from player where player_id = :player_id;

数据缓冲池

在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool 之后才可以访问。这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行 I/O 的时间。
优先对使用频次高的热数据进行加载。

查询缓存与数据缓冲池：
查询缓存是把之前查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。
数据缓冲池是把部分预查询的数据提前放入内存中，用到时可以直接在内存中访问，无需从磁盘中加载。

刷盘：若对数据进行了修改，优先在数据缓冲池中更新，再以一定频率（策略）同步在磁盘中。保证数据一致性。
查看缓冲池大小：

show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.07 sec)

设置缓冲池大小：

set global innodb_buffer_pool_size = 268435456;