MySQL逻辑架构
逻辑架构
扩展(c/s架构 和 b/s 架构)
- c/s 架构,即 Client / Server 架构:需要安装专用的客户端软件,然后联网请求服务器获取数据。
- b/s 架构,即 Brower / Server 架构:需要安装浏览器,然后联网请求服务器获取数据
c/s架构和b/s架构的区别
- 标准:BS开发更标准一些,而CS需要在不同的系统上执行,BS只需要在浏览器上执行;
- 效率:CS效率更高,CS属于安装的软件,很多内容已经安装到客户端中了,直接联网获取数据即可;而BS运行在浏览器上,所有的数据必须经过下载才能使用;
- 升级:BS无缝升级,CS需要删除老版本,再安装新版本;
- 安全性:CS更为安全,因为必须安装软件才能使用;BS安全度较低,只要有浏览器就可以使用;
- 开发成本:CS开发成本更高,程序员必须精通各个系统;BS开发成本很低,只要浏览器能够正常运行即可;
1. 逻辑架构剖析
1.1 服务器处理客户端请求
首选 MySQL 是典型的 C/S 架构,即 Client / Server 架构,服务器端程序使用的 mysqld。
执行流程的简单概述:客户端"进程"向服务器"进程"发送一段文本(SQL语句),服务器"进程"处理后再向客户端"进程"发送一段文本(处理结果)、
注意哦:一个进程必定包含多个线程。
服务器进程对客户端进程发送的请求的简洁的处理流程图:
- 连接管理:进行用户密码的校验以及权限的校验。
- 解析和优化:解析 SQL 语句,生产一个解析树。然后对 SQL 语句进行优化处理。
- 存储引擎:调用存储引擎查询结果。文件系统会先把数据加载到缓冲池(buffer Pool)中。
MySQL 架构图:
1.2. Connectors(客户端连接器)
Connectors(客户端):指的是不同语言中与SQL的交互或者是不同语言对 MySQL连接协议的实现。
作用:跟 MySQL服务器建立 TCP 连接,然后通过定义好的协议进行交互。
MySQL Server 的结构可以分为如下三层:
- 连接层
- 服务层
- 引擎层
1.3 连接层
系统(客户端)访问 MySQL 服务器前,做的第一件事件就是建立 TCP 连接。
职责:负责用户认证,管理连接,获取权限信息。
作用:在 TCP 连接成功之后,对请求连接的用户密码进行验证,管理TCP连接池和线程池,以及获取相应的权限信息。
- 用户名或密码不对,会收到一个 Access denied for user 错误,客户端程序结束执行。
- 用户名密码验证通过,会从权限表查出账号拥有的权限与连接关联,之后的权限判断逻辑,都将依赖于此时读到的权限。
思考一个问题?
一个系统只能和MySQL服务器建立一个连接吗?;MySQL服务器只能和一个系统建立连接吗?
当然不是,系统和MySQL服务器之间是多对多的关系。但会频繁出现 TCP 连接创建销毁带来的资源耗尽,性能下降问题。所以 MySQL 服务器 针对以上问题设置了专门的 TCP连接池 来限制连接数,采用 长连接模式 复用TCP连接。这也就是 MySQL中 最大连接数参数的由来吧!
MySQL中除了有 TCP连接池 还有 线程池。
当连接收到请求后,必须要分配给一个线程专门负责与客户端的交互。那么有了 线程池 之后,每一个连接就可以直接从 线程池 中获取线程,省去了创建和销毁线程的开销。
1.4 服务层
该层是核心服务层。如 SQL接口,并完成 缓存的查询,SQL的分析和优化及部分内置函数的执行,所有跨存储引擎的功能也在这一层实现,如存储过程,存储函数,触发器,视图。
在该层,服务器会 解析查询 并创建相应的内部 解析树,并对其完成相应的 优化:如确定查询表的顺序,是否利用索引等,最后生成相应的 执行计划。
-
SQL Interface(SQL 接口):
- 接受用户的 SQL 语句,并且返回用户需要查询的结果。比如:select ... from ...
- MySQL 支持DML(数据库操作语言)DDL(数据库定义语言),存储过程,自定义函数,视图,触发器等多种SQL语言接口。
-
Parser(解析器):
- 解析器 会对接受的 SQL 语句进行语法分析,语义分析。将 SQL 语句分解成 数据结构。后续的步骤的执行和处理都是基于这个数据结构的。如果在分解构成数据结构时遇到错误,那么就说明这个 SQL 语句时不合理的。
- 当 SQL 语句被 解析器 验证和解析成功后,并为其创建 语法树,并根据数据字典查询语法树,验证该客户端是否具有执行该操作的权限。注意,当创建好语法树后,MySQL还会对SQL查询进行语法上的优化,进行查询重写。
-
Optimizer(查询优化器):
- SQL 语句在经过 解析器 语法解析后,在查询之前还会使用 查询优化器 确定 SQL 语句的执行路径,生成一个 执行计划。
- 该 执行计划 用于表明 使用那些索引 进行查询(全表检索还是使用索引检索),表之间的连接顺序等。最后按照 执行计划 中的步骤调用 存储引擎 提供的方法进行查询数据,并将查询结果返回给用户(客户端)。
- 它使用 "选取 - 投影 - 连接"策略进行查询。例如:
select id,name from dual where id = 1;
选取:根据 where 语句中的条件进行 选取,可不是将表全部查询出来以后再进行过滤的。
投影:根据select中指定的 字段名 进行属性 投影,可不是将属性全部取出以后再进行过滤的。
连接:将这两个查询条件 连接 起来生成最终查询结果。
-
Caches & Buffers (查询缓存组件 8.0 已删除):
- MySQL 内部有一些 Caches 和 Buffer,比如 Query Cache 就是用来缓存一条 select 语句的执行结果的,如果能在缓存中找到对应的查询结果,那么就不必在进行查询解析,优化和执行的整个过程了。
- 这个缓存机制是有由一些小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等。
- 这个查询缓存可以在 不同客户端之间共享的。
- 从 5.7.20 开始,不推荐使用查询缓存,并在 8.0 中删除。因为查询缓存的命中率极低(必须完全一样的 SQL 语句才能命中,多个空格都不行)。
1.5 引擎层
插件式的存储引擎 架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务需求和实际需要选择合适的存储引擎。同时还支持 开发人员设置自己的存储引擎 。
插件式存储引擎层(Storage Engines),真正的负责了MySQL中数据的存储和提取,对物理服务器级别维护的底层数据执行操作,服务器通过API与存储引擎进行通信。不同的 存储引擎 的功能不同,我们可以根据自己的实际需要进行选取。
8.0 默认使用 InnoDB 存储引擎。
SHOW ENGINES;
1.6 存储层
所有的数据,数据库,表的定义,表的每一行的内容,索引,都是存在 文件系统 上,以 文件 的方式存在的,并完成与存储引擎的交互。
当然有些 存储引擎,也支持不使用 文件系统 直接管理数据,比如 InnoDB。
查询数据库服务 文件系统 目录命令:
SELECT @@global.datadir;
# 或
SHOW VARIABLES LIKE '%datadir%';
1.7 小结
简化为三层结构:
- 连接层:客户端和服务器端建立连接,客户端发送 SQL 至服务器端;
- SQL 层(服务层):对 SQL 语句进行解析优化执行查询处理,并返回查询的结果;与数据库文件的存储方式无关;
- 存储引擎层:与数据库文件打交道,负责数据的存储和读取;
2. SQL执行流程
2.1 MySQL 中的 SQL 执行流程
2.1.1 查询缓存
做什么的:存储 SQL 语句的查询结果。
作用:缓存查询结果,提高查询效率。
概述:
数据库服务器(Server)如果在查询缓存中发现了这条 SQL 语句,就会直接将结果返回给客户端;反之如果没有,就进入到解析器阶段。
补充说明:因为查询缓存的命中率不高(效率不高),所以 8.0 之后抛弃了这个功能。
MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果会以 key - value 的形象,被直接存储在内存中。key 是查询的语句,value 是查询的结果。如果你的查询能够直接在缓存找到,那么直接返回给客户端。反之如果缓存查不到,就会继续后面的执行阶段。当执行完成后,执行结果会被存入查询缓存中。所以,如果查询命中缓存,就不需要后面复杂的操作,效率会很高。
缺点:
- 部分函数无法缓存,now()。
- 数据发生改变时,高效缓存会无效,更新操作频繁的数据库来说,查询缓存的命中率会非常低。
5.7.版本中开启查询缓存
- 查询查询缓存状态
SELECT @@query_cache_type;
# 或者
show VARIABLES LIKE '%query_cache_type%';
- 查询缓存状态说明
query_cache_type=2
# 有3个值 0代表关闭查询缓存 OFF,1代表开启ON,2(DEMAND)
- 设置查询缓存状态
SET query_cache_type = 2;
- 监控查询缓存的命中率
SHOW STATUS LIKE '%Qcache%';
2.1.2 解析器
做什么的:在解析器中对 SQL 语句进行语法分析,语义分析。
作用:解析 SQL 语句,判断 SQL 语句是否合法,生成 语法树。
概述:
当 SQL 语句进入到解析器时,就表示要真正执行语句了。首先,MySQL 需要知道你要做什么,因此需要对 SQL 语句做解析。SQL 语句的分析分为 词法分析和语法分析
- 词法分析:解析器先做 "词法分析"。SQL 语句是由多个字符串和空格组成,MySQL 需要识别出字符串分别是什么,代表什么。例如:从头开始识别,识别出 "select" 这个关键字,就表示这一条查询语句。把后续字符串"ID"识别成"列ID","from" 后面的字符串为"表"等...。
- 语法分析:紧接着要做 "语法分析"。根据 "词法分析" 的结果,语法分析器会根据语法规则,判断该 SQL 语句是否 满足MySQL语法。
当SQL语句的语法不对时,就会收到 "You have an error in your SQL syntax; " 的错误提醒
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rom pw_sample_point WHERE id = '1516611230470189057'' at line 1
注意:在错误提醒中被引号 '' 引起的信息是主要的错误信息!!!
SQL 语句生成的语法树结构:
2.1.3 优化器
做什么的:在 优化器 中确定 SQL 语句的执行路径,比如是根据 全表检索,还是根据 索引检索 等。
作用:生成最好的 执行计划。
概述:
经过了 解析器,MySQL 就知道你要做什么了。在开始之前,还要先经过 优化器 的处理。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的 执行计划。
在 查询优化器 中,可以分为 逻辑查询优化 阶段和 物理查询优化 阶段。
- 逻辑查询优化:通过改变 SQL 语句的内容来使得 SQL 查询更高效
- 物理查询优化:是基于关系代数进行的查询重写,而关系代数的每一步都对应着物理计算,物理计算往往对应着多种算法。所以需要 使用索引,提升查询
2.1.4 执行器
做什么的:根据 执行计划,找到对应的表,调用 存储引擎API 对表进行读写(真正的查询)。
作用:判断 用户权限,执行执行计划 读写数据。
概述:
在执行之前需要判断该用户是否 具备权限。如果没有,就返回权限错误。
如果有权限,就打开表继续执行。打开表的时候,执行器 会根据表的引擎定义,调用 存储引擎API 对表进行读写(查询)。存储引擎 API 是抽象接口,具体实现还是要根据表选择的存储引擎。
2.1.5 SQL 语句的执行流程:SQL语句 --> 查询缓存 --> 解析器 --> 优化器 --> 执行器
2.2 MySQL8中SQL 执行原理
SQL的执行原理,不同的 DBMS 的 SQL 的执行原理是相通的,只是在不同的软件中,各有各的实现路径、
一条 SQL 语句会经历不同的模块,那么在不同的模块中,SQL 执行所使用的资源(时间)又是怎样的呢。下面可以通过 一个系统参数进行分析。
2.2.1 开启profiling(开启资源收集)
全局/会话 共有系统变量:0 关闭,1 开启
- 查看状态
# 全局 会话 共有系统变量
SELECT @@profiling;
# 或者
SHOW SESSION VARIABLES LIKE '%profiling%';
# 或者
SHOW GLOBAL VARIABLES LIKE '%profiling%';
- 修改状态
# 会话系统变量
SET SESSION profiling = 1;
# 全局系统变量
SET GLOBAL profiling = 1;
2.2.2 查询 SQL 各个模块执行所使用的资源(时间)信息
- 查看所有 SQL 执行的记录
SHOW PROFILES;
- 查看上一条 SQL 各个模块执行的记录
SHOW PROFILE;
- 查看指定 SQL 各个模块执行的记录
SHOW PROFILE FOR QUERY 36;
# 36 对应所有 SQL 执行记录列表中的 Query_Id 字段
# 语法说明
show profile [type,type,...] for query id
-- ALL 显示所有参数的开销信息
-- block io 显示IO的相关开销信息
-- cpu 显示就cpu相关开销信息
....
SQL 各个模块执行记录查询结果说明:
-
Status (阶段)
-
starting:启动
-
Executing hook on transaction:执行挂接事务
-
starting:启动
-
checking permissions:检查权限
-
Opening tables:打开表
-
init:初始化
-
System lock:系统锁
-
optimizing:优化
-
statistics:统计
-
preparing:准备
-
Creating tmp table:创建 tmp 表
-
executing:执行
-
Sending data :发送数据
-
Sorting result :排序
-
end:结束
-
query end:查询 结束
-
waiting for handler commit:正在等待处理程序提交
-
removing tmp table:删除 tmp 表
-
closing tables:关闭表 /去除TMP 表
-
freeing items:释放物品
-
cleaning up:清理
-
-
Duration(时间)
2.2.3 SQL语法顺序
- 手写:
SELECT DISTINCT
字段,
字段 ,....
FROM
表名 (LEFT / RIGHT)
JOIN 表连接 ON [AND/OR] 连接条件
WHERE
AND
OR IN 过滤条件
GROUP BY
分组
HAVING
AND
OR IN 分组条件
ORDER BY
排序
LIMIT 分页
- 机读:
-- 第一阶段
FROM
表名 (LEFT / RIGHT)
JOIN 表连接 ON [AND / OR] 连接条件
WHERE
AND
OR IN 过滤条件
GROUP BY
分组
HAVING
AND
OR IN 分组过滤条件
-- 第二阶段
SELECT DISTINCT
字段,
字段,....
-- 第三阶段
ORDER BY
排序
LIMIT 分页
3. 数据库缓冲池(buffer pool)
InnoDB 存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面,写页面,创建新页面等操作)。而磁盘 I/O 需要消耗的很多时间。那么为了能让数据表或索引中的数据随时被我们所用,DBMS 会申请 占用内存来作为数据缓冲池,在真正访问页面之前,会把磁盘上的页缓存到内存中 Buffer Pool 之后才可以访问。
这样做的好处是可以让磁盘活动最小化,从而 减少与磁盘直接进行 I/O 的时间。这种策略对提升 SQL 性能至关重要。如果索引的数据在缓冲池里,那么访问的成本就会降低很多。
3.1 缓冲池 与 查询缓存
缓冲池和查询缓存是一个东西吗?不是。
3.1.1 缓冲池(Buffer Pool)
在 InnoDB 存储引擎有一部分(各种数据)数据会放到内存中,则这部数据占用了缓冲池的大部分内存。
InnoDB 缓冲池包括了数据页,索引页,插入缓冲,锁信息,自适应 Hash 和数据字段信息等。
- 缓冲池的重要性:
InnpDB 存储引擎在处理客户的请求时,当需要访问某个页的数据时,就会把 完整的页的数据全部加载到内存中,也就说即使我们只需要访问一条数据,那也需要先把整个页的数据加载到内存中。当数据加载到内存后就可以进行读写访问了。但进行读写访问之后并不着急把数据对应的内存空间释放掉,而是将其 缓存 起来,这样将来有请求再次访问该页数据时,就可以 省去磁盘IO了。
- 缓存原则:
" 位置 * 频次 "这个原则,可以帮我们对 I/O 访问效率进行优化。
位置:位置决定效率,位置指的是磁盘和内存,缓冲池就是为了在内存中可以直接访问数据。
频次:因为缓冲池的大小是有限的,无法将所有的数据都加载到缓冲池中,这时就涉及到优先级顺序,会 优先对使用频次高的热数据进行加载、
- 缓冲池的预读特性:
当在内存空间充足的情况下,会把我们正在使用的数据,以及它周围的一些数据,采用 预读 的机制提前加载,可以减少未来可能的磁盘 I/O 操作。
注意:"查询缓存" 是提前将查询的结果缓存了起来,而 "缓冲池" 服务于数据库整体的 I/O 操作。
3.2 缓冲池如何读取数据
缓冲池管理器会尽量将经常使用的数据保存起来,在数据库进行页面读操作的时候,首先会判断该页面是否在缓冲池中,如果存在就直接读取,如果不存在,就会通过内存或者磁盘将页面存放到缓冲池中再进行读取。
问题:如果我们执行 SQL 语句的时候更新了缓冲池中的数据,那么这些数据会马上同步到磁盘上吗?
答:当我们对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后数据库会 以一定的频率刷新 到磁盘上。注意:并不是每次发生更新操作,都会立刻进行磁盘回写。缓冲池会采用 checkpoint的机制 将数据回写到磁盘上。
3.3 查看/设置缓冲池的大小
如果你使用的是 MySQL MyISAM 存储引擎,它只缓存索引,不缓存数据,对应的键缓存参数为 "key_buffer_size",你可以用它进行查看。
如果你使用的是 InnoDB 存储引擎,可以通过查看 ""innodb_buffer_pool_size"" 变量来查看缓冲池的大小 - (全局系统变量),默认:128MB。
- 查看 InnoDB 存储引擎缓冲池的大小
SELECT @@global.innodb_buffer_pool_size;
# 或
SHOW GLOBAL VARIABLES like '%innodb_buffer_pool_size%';
- 设置 InnoDB 存储引擎缓冲池的大小
SET GLOBAL innodb_buffer_pool_size = 1073741824; # 设置为 1GB
或者:配置文件,重启 MySQL 服务后才会生效
[server]
innodb_buffer_pool_size = 1073741824 #设置 InnoDB 的缓冲池大小为 1GB
3.4 多个 Buffer Pool实例
Biffer Pool本质是 InnoDB 向操作系统申请的一块 连续的内存空间,在多线程环境下,访问 Buffer Pool中的数据都需要 (多线程)加锁 处理。那么在多线程并发访问特别高的情况下,单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候,我们可以把它 拆分成若干个小的 Buffer Pool,每个 Buffer Pool都称为一个 实例,它们都是独立的,独立大的去申请内存空间,独立的管理各种链表。所以在多线程并发访问时并不会相互影响,从而提高高并发处理能力。 总结一句话就是:多线程高并发情况下,需要开启多个 Buffer Pool 实例来提高效率。
(InnoDB 引擎)可以通过设置 "innodb_buffer_pool_instances" 的值来修改Buffer Pool实例的个数(全局系统变量),默认:1个实例
- 查看 InnoDB 存储引擎的Buffer Pool 实例数
SELECT @@global.innodb_buffer_pool_instances;
# 或
SHOW GLOBAL VARIABLES like '%innodb_buffer_pool_instances%';
或者:配置文件,重启 MySQL 服务后才会生效
[server]
innodb_buffer_pool_instances = 2 #设置 InnoDB 的缓冲池实例为 2个
每个 Buffer Pool 实例占用多少内存空间呢?
# 也就是缓冲池总共的大小 除以 缓冲池实例的个数,结果就是每个缓冲池实例占用的大小
innodb_buffer_pool_size/innodb_buffer_pool_instances
注意:InnoDB规定:当 "innodb_buffer_pool_size" 的值小于 1G 的时候设置多个实例是无效的,InnoDB 会默认把 "innodb_buffer_pool_instances" 的值修改为 1。所以建议在 Buffer Pool 大于或等于 1G 的时候再设置多个Buffer Pool实例。