[大数据]Mysql高级篇学习总结4：Mysql逻辑架构剖析、sql执行流程、数据库缓冲池设置

1、逻辑架构剖析

1.1 服务器处理客户端请求

Mysql是典型的C/S架构，既’Client/Server’架构，服务端程序使用的是mysqld。

以Mysql5.7为例，MySQL服务器端的逻辑架构说明如下图。其中在Mysql8.0及其之后移除了查询缓存的逻辑步骤，也就是不再进行缓存了。

1.2 Connectors

Connectors，指的是不同语言中与SQL的交互。Mysql首先是一个网络程序，在TCP之上定义了自己的应用层协议。所以要使用Mysql，我们可以编写代码，跟Mysql Server建立TCP连接，之后按照其定义号的协议进行交互。

或者比较方便的办法是调用SDK，比如Native C API, JDBC, PHP等各语言Mysql Conector。但是通过SDK来访问Mysql，本质上还是在TCP连接上通过Mysql协议跟Mysql进行交互。

1.3 第一层：连接层

客户端访问Mysql服务器前，做的第一件事就是建立TCP连接。
经过三次握手建立连接成功后，Mysql服务器对TCP传输过来的账号密码做身份认证、权限获取等。

由于多个系统都可以和Mysql服务建立连接，为了解决TCP无限创建、以及TCP频繁创建销毁带来的资源耗尽、性能下降问题。Mysql服务器里有专门的TCP连接池限制连接数，采用长连接模式复用TCP连接，来解决该问题。

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

所以连接管理的职责是负责认证、管理连接、获取权限信息。

1.4 第二层：服务层

第二层架构主要完成大多数的核心服务功能。在该层，服务器会解析查询并创建响应的内部解析树，并对其完成对应的优化。如果是SELECT语句，服务器还会查询内部的缓存。
１）SQL Interface：SQL接口

• 接收用户的sql命令，并返回用户需要的查询结果。

２）Parse：解析器

• 在解析器中对sql语句进行语法分析、语义分析。将sql语句分解成数据结构，并将这个结构传递到后续步骤，以后sql语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。
• 在sql命令传递到解析器的时候，会被解析器验证和解析，并为期创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，Mysql还会对sql查询进行语法上的优化，进行查询重写。

３）Optimizer：查询优化器

• sql语句在语法解析之后，查询之前会使用查询优化器确定sql语句的执行路径，生成一个执行计划。
• 这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正地执行查询，并将查询结果返回给用户。
• 它使用“选取－投影－连接”策略进行查询。比如：

SELECT id, name FROM student WHERE gender = '女';

这个SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来后再对gender进行过滤。
这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出之后再进行过滤，将这两个查询条件连接起来生成最终查询结果。

４）Caches & Buffers：查询缓存组件

• Mysql内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。
• 从Mysql 5.7.20开始，不推荐使用查询缓存，并在Mysql 8.0中删除。

1.5 第三层：引擎层

和其他数据库相比，Mysql优点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎，同时开源的Mysql还允许开发人员设置自己的存储引擎。

插件式存储引擎层（Storage Engines），真正地负责了Mysql数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。

可以使用命令 show engines 来查看下默认支持的存储引擎如下：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

1.6 存储层

所有的数据、数据库、表的定义，表的每一行的内容、索引、都是存储在文件系统上，以文件的方式存在，并完成与存储引擎的交互。

2、SQL执行流程

2.1 Mysql中的sql执行流程

１、查询缓存：Server如果在查询缓存中发现了这条sql语句，就会直接将结果返回给客户端；如果没有，就进入解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在Mysql 8.0 之后就抛弃了这个功能。
２、解析器：在解析器中对sql语句进行语法分析、语义分析。
分析器先做“词法分析”。你输入的是由多个字符串和空格组成的一条sql语句，Mysql需要识别出里面的字符串分别是什么，代表什么。

接着要做“语义分析”。根据词法分析的结果，语法分析器会根据语法规则，判断输入的这条sql语句是否满足mysql语法。
３、优化器：在优化器中会确定sql语句的执行路径，比如是根据全表检索，还是根据索引检索等。
经过了解析器，Mysql就知道了你要做什么了。一条查询可以有很多种执行方式，虽然最后都返回相同的结果，而优化器的作用就是找到这里面最好的执行计划。
４、执行器：得到了执行计划之后，就进入了执行器阶段。
在执行之前需要判断该用户是否具备权限。如果没有，就会返回权限错误。如果具备权限，就执行sql查询并返回结果。在mysql 8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

如果有权限，就打开表继续执行。打开表的饿时候，执行器就会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎。

2.2 Mysql 5.7 中的sql执行原理

１、确定profiling是否开启
使用 select @@profiling 来查询语句底层执行的过程是否开启计划。开启后可以让mysql收集在sql执行时所使用的资源情况。

select @@profiling;
# 或者
show variables like '%profiling%';

该功能默认是关闭的：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

Profiling功能由mysql会话变量：profiling控制。profiling=0代表关闭，需要把profiling打开的话，将值设置为１：

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

２、执行sql
查询dbtest1数据库下的emp1表中的所有数据（该表目前为空，没有插入过数据）：

mysql> use dbtest1;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| emp1              |
| emp2              |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from emp1;
Empty set (0.00 sec)

３、查看profiles
可以使用show profiles 查看历史的sql命令，然后通过show profile for query id 命令得到该sql语句在每一步骤中的时间。因此如果某一个sql语句运行时间特别长，我们可以通过该条命令来排查究竟在哪一个步骤中的耗时最长。

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00009775 | user dbtest1       |
|        2 | 0.00018275 | SELECT DATABASE()  |
|        3 | 0.00055750 | show databases     |
|        4 | 0.00031600 | show tables        |
|        5 | 0.00047575 | show tables        |
|        6 | 0.00012400 | select * emp1      |
|        7 | 0.00019350 | SELECT DATABASE()  |
|        8 | 0.00046650 | show tables        |
|        9 | 0.00035350 | select * from emp1 |
+----------+------------+--------------------+
9 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 9;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000081 |
| checking permissions | 0.000015 |
| Opening tables       | 0.000030 |
| init                 | 0.000027 |
| System lock          | 0.000014 |
| optimizing           | 0.000008 |
| statistics           | 0.000021 |
| preparing            | 0.000017 |
| executing            | 0.000007 |
| Sending data         | 0.000054 |
| end                  | 0.000009 |
| query end            | 0.000013 |
| closing tables       | 0.000013 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000020 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

除了查看每个阶段的耗时之外，还可以查看cpu、io阻塞等情况：

SHOW PROFILE [type] [FOR QUERY n] [LIMIT row_count [OFFSET offset]]
type:{
    ALL  -- 显示所有参数的开销信息
    BLOCK IO --　显示IO的相关开销
    CONTEXT SWITCHES  --　上下文切换相关开销
    CPU  --　显示CPU相关开销
    IPC  -- 显示发送和接受相关开销
    MEMORY  --　显示内存相关开销
    PAGE FAULTS  --　显示页面错误相关开销
    SOURCE  --　显示和Source_function, Source_file, Source_line相关开销
    SQAPS  --　显示交换次数相关开销
}

举例：

mysql> show profile cpu, block io for query 9;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000081 | 0.000000 |   0.000079 |            0 |             0 |
| checking permissions | 0.000015 | 0.000000 |   0.000015 |            0 |             0 |
| Opening tables       | 0.000030 | 0.000000 |   0.000030 |            0 |             0 |
| init                 | 0.000027 | 0.000000 |   0.000027 |            0 |             0 |
| System lock          | 0.000014 | 0.000000 |   0.000015 |            0 |             0 |
| optimizing           | 0.000008 | 0.000000 |   0.000007 |            0 |             0 |
| statistics           | 0.000021 | 0.000000 |   0.000021 |            0 |             0 |
| preparing            | 0.000017 | 0.000000 |   0.000017 |            0 |             0 |
| executing            | 0.000007 | 0.000000 |   0.000007 |            0 |             0 |
| Sending data         | 0.000054 | 0.000000 |   0.000054 |            0 |             0 |
| end                  | 0.000009 | 0.000000 |   0.000009 |            0 |             0 |
| query end            | 0.000013 | 0.000000 |   0.000013 |            0 |             0 |
| closing tables       | 0.000013 | 0.000000 |   0.000012 |            0 |             0 |
| freeing items        | 0.000027 | 0.000000 |   0.000027 |            0 |             0 |
| cleaning up          | 0.000020 | 0.000000 |   0.000019 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

3、数据库缓冲池（buffer pool）

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面。

而磁盘I/O需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。

这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行I/O的时间。要知道，这种策略对提升sql语句的查询性能来说至关重要，如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

3.1 缓冲池vs查询缓存

１、缓冲池（Buffer Pool）
首先要明确的是：缓冲池和查询缓存不是一个东西。

在InnoDB存储引擎中，有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种数据的缓存，包括：

• 数据页
• 插入缓存
• 自适应索引哈希
• 索引页
• 锁信息
• 数据字典信息

１）缓存池的重要性：
InnoDB存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中，也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。

将整个页加载到内存中后就可以进行读写访问了，在进行完读写访问之后，并不着急把该页对应的内存空间释放掉，而是将其缓存起来，这样将来有请求再次访问该页面时，就可以省去磁盘IO的开销了。

２）缓存原则
“位置　*　频次”　原则，会优先对使用频次高的热数据进行加载。

３）缓冲池的预读特性
为了提升I/O效率，当我们使用了一些数据，大概率还会使用它周围的一些数据，因此mysql采用“预读”的机制提前加载，可以减少未来可能的磁盘I/O操作。

3.2 缓冲池如何读取数据

注意，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录，然后数据库会以一定的频率刷新到磁盘上。因此并不是每次发生更新操作，都会立刻进行磁盘回写。

缓冲池会采用一种叫做checkpoint 的机制将数据回写到磁盘上，这样做的好处就是提升了数据库的整体性能。

比如，当缓冲池不够用时，需要释放掉一些不常用的页，此时就可以强行采用 checkpoint 的方式。将不常用的脏页会写到磁盘上，然后再从缓冲池中将这些页释放掉。这里的脏页（dirty page）指的是缓冲池中的被修改过的页，与磁盘上的数据页不一致。

3.3 查看/设置缓冲池的大小

如果使用的是MYSQL MyISAM存储引擎，它只缓存索引，不缓存数据，对应的键缓存参数为：key_buffer_size，可以用这个参数查看。

如果使用的是InnoDB存储引擎，可以通过查看 innodb_buffer_pool_size 变量查看缓冲池的大小。命令如下：

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

此时InnoDB的缓冲池的大小为：134217728/1024/1024=128M。
如果要修改缓冲池大小为256M:

set global innodb_buffer_pool_size=268435456;

或者修改配置文件：

[server]
innodb_buffer_pool_size = 268435456

3.4 多个buffer pool实例

Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer Pool中的数据都需要加锁处理。

在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，我们可以把他们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，他们都是独立的，独立地申请内存空间，独立地管理各种链表，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。

可以在服务器启动的时候通过innodb_buffer_pool_instance的值来修改Buffer Pool实例的个数：

[server]
innodb_buffer_pool_instances=2

这样就表明我们要创建２个Buffer Pool实例。
可以使用 show variables like ‘innodb_buffer_pool_instances’ 查看缓冲池的个数：

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

注意：
１）每个Buffer Pool实例占用的大小，等于Buffer Pool的总内存空间除以实例个数；
２）Buffer Pool实例也不是创建的越多越好，分别管理各个Buffer Pool也是需要性能开销的，InnoDB规定：当innodb_buffer_pool_size的值小于1G的时候，设置多个实例是无效的，InnoDB会默认把innodb_buffer_pool_size的值修改为１。所以只要当Buffer Pool大于或等于1G的时候，比较时候设置多个Buffer Pool实例。