前言:本篇博文主要记录尚硅谷的MySQL视频<<MySQL数据库教程天花板,mysql安装到mysql高级,强!硬!>>中P110-112节视频笔记。
视频链接:https://www.bilibili.com/video/BV1iq4y1u7vj?p=110
本篇博文目录:
一.逻辑架构剖析
数据库服务器是如何处理客服端的处理的,下图以查询请求为例展示:
具体详细的过程如下图所示,下图给出的是MySQL5.7版本的数据库服务器框架图:
1.连接层
连接层操作流程如下:
系统(客户端)访问 MySQL 服务器前,做的第一件事就是建立 TCP 连接。经过三次握手建立连接成功后, MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
- 用户名或密码不对,会收到一个Access denied for user错误,客户端程序结束执行
- 用户名密码认证通过,会从权限表查出账号拥有的权限与连接关联,之后的权限判断逻辑,都将依 赖于此时读到的权限
TCP 连接收到请求后,必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池,去走后面的流程。每一个连接从线程池中获取线程,省去了创建和销毁线程的开销。
2.服务层
服务层中的四大组件:
- SQL Interface: SQL接口
- Parser: 解析器
- Optimizer: 查询优化器
- Caches & Buffers: 查询缓存组件
SQL Interface: SQL接口组件
- SQL接口组件作用是去接收用户的SQL命令,并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface的语言接口
- MySQL支持DML(数据操作语言)、DDL(数据定义语言)、存储过程、视图、触发器、自定 义函数等多种SQL语言接口
Parser: 解析器组件
- 在解析器中对 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语句的执行结果,如果能够在其中找到对应的查询结果,那么就不必再进行查询解析、优化和执行的整个过 程了,直接将结果反馈给客户端。
- 这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等 。
- 这个查询缓存可以在 不同客户端之间共享 。
- 从MySQL 5.7.20开始,不推荐使用查询缓存,并在 MySQL 8.0中删除 。
3.引擎层
插件式存储引擎层( Storage Engines),真正的负责了MySQL中数据的存储和提取,对物理服务器级别维护的底层数据执行操作,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。
MySQL 8.0.25默认支持的存储引擎如下:
5.6.40默认支持存储引擎如下:
4.存储层
所有的数据,数据库、表的定义,表的每一行的内容,索引,都是存在
文件系统
上,以文件
的方式存在的,并完成与存储引擎的交互。当然有些存储引擎比如InnoDB,也支持不使用文件系统直接管理裸设备,但现代文件系统的实现使得这样做没有必要了。在文件系统之下,可以使用本地磁盘,可以使用DAS、NAS、SAN等各种存储系统。
存储数据的文件目录在计算机中位置可以通过 show variables like '%datadir%';
进行查询:
homerwork数据库都是以如下所示的文件进行存储:
5.小结
MySQL架构图本节开篇所示。下面为了熟悉SQL执行流程方便,我们可以简化如下:
简化为三层结构:
- 连接层:客户端和服务器端建立连接,客户端发送 SQL 至服务器端;
- SQL 层(服务层):对 SQL 语句进行查询处理;与数据库文件的存储方式无关;
- 存储引擎层:与数据库文件打交道,负责数据的存储和读取。
二.SQL执行流程
数据库服务器的具体执行流程如下图所示:
- 连接器:客户端与服务器通过TCP网络协议建立连接,服务器对连接的客户端进行认证与授权,通过连接池来分配数据库资源给客户端。
- 查询缓存:客户端传递过来的SQL语句如果在服务器缓存中存在,就会直接将结果返回给客户端;如果没有,就进入到解析器阶段。需要说明的是,因为查询缓存往往效率不高,所以在 MySQL8.0 之后就抛弃了这个功能。
- 解析器:在解析器中对 SQL 语句进行语法分析、语义分析,然后进入到查询优化器中。
- 优化器:在优化器中会确定 SQL 语句的执行路径,比如是根据 全表检索 ,还是根据 索引检索 等。
- 执行器:截止到现在,还没有真正去读写真实的表,仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。
1.连接阶段
TCP连接池用来管理客户端的连接,而数据库连接池用来分配数据库资源,连接阶段的执行流程如下图所示:
2.缓存阶段
查询缓存阶段的流程图如下图所示:
查询缓存存在的问题:
- 在MySQL 中的查询缓存,不是缓存查询计划,而是查询对应的结果。这就意味着查询匹配的 鲁棒性大大降低 ,只有 相同的查询操作才会命中查询缓存 。
两个查询请求在任何字符上的不同(例如:空格、注释、大小写),都会导致缓存不会命中
。因此 MySQL 的 查询缓存命中率不高 。 - 如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表,如 mysql 、information_schema、 performance_schema 数据库中的表,那这个请求就不会被缓存。以某些系统函数举例,可能同样的函数的两次调用会产生不一样的结果,
比如函数 NOW ,每次调用都会产生最新的当前时间,如果在一个查询请求中调用了这个函数,那即使查询请求的文本信息都一样,那不同时间的两次查询也应该得到不同的结果,如果在第一次查询时就缓存了,那第二次查询的时候直接使用第一次查询的结果就是错误的!
- 此外,既然是缓存,那就有它缓存失效的时候 。MySQL的缓存系统会监测涉及到的每张表,只要该表的结构或者数据被修改,如
对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTERTABLE 、 DROP TABLE 或 DROP DATABASE 语句,那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除!对于更新压力大的数据库来说,查询缓存的命中率会非常低。
3.解析器阶段
解析器阶段的流程:
- 词法分析:你输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是什么,代表什么。 MySQL 从你输入的"select"这个关键字识别出来,这是一个查询语句。它也要把字符串“T”识别成“表名 T”,把字符串“ID”识别成“列 ID”。
- 语法分析:根据词法分析的结果,语法分析器(比如:Bison)会根据语法规则,判断你输入的这个 SQL 语句是否 满足 MySQL 语法 。
- SQL语句正确:如果SQL语句正确,则会生成一个这样的语法树。
语法树如下图所示:
4.优化器阶段
优化器的作用就是从不同的执行方案中选择一个方案进行执行。
举例:如下语句是执行两个表的 join:
select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程';
- 方案1:可以先从表 test1 里面取出 name='zhangwei’的记录的 ID 值,再根据 ID 值关联到表 test2,再判断test2 里面 name的值是否等于 ‘mysql高级课程’。
- 方案2:可以先从表 test2 里面取出 name=‘mysql高级课程’ 的记录的 ID 值,再根据 ID 值关联到 test1,再判断 test1 里面 name的值是否等于 zhangwei。
这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。优化
器阶段完成后,这个语句的执行方案就确定下来了,然后进入执行器阶段。
5.执行器阶段
经过优化器阶段后选出一个执行计划交给执行器进行执行。
在执行之前需要判断该用户是否 具备权限 。如果没有,就会返回权限错误。如果具备权限,就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本,如果设置了查询缓存,这时会将查询结果进行缓存。
例子:
执行的SQL语句如下:
select * from test where id=1;
比如:表 test 中,ID 字段没有索引,那么执行器的执行流程是这样的:
调用 InnoDB 引擎接口取这个表的第一行,判断 ID 值是不是1,如果不是则跳过,如果是则将这行存在结果集中;调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
至此,这个语句就执行完成了。对于有索引的表,执行的逻辑也差不多。
SQL 语句在 MySQL 中的流程是: SQL语句→查询缓存→解析器→优化器→执行器 ,如下图所示:
三.MySQL中MySQL Profiling的使用
为了更精准定位一条SQL语句的性能问题,需要清楚地知道这条SQL语句运行时消耗了多少系统资源我们可以通过Profiling工具进行观察。
默认情况下Profiling是关闭的,如下:
备注:系统返回下列内容,0表示未启用Profiling,1表示已启用Profiling。
通过 set profiling = 1;
进行开启,如下:
通过 show profiles;
可以查看当前会话所产生的所有 profiles:
我们可以根据Query_ID查询具体的Profile信息:
使用 show profile for query 3;
查看Query_ID =3 的Profile信息,如下:
我们可以携带更多的参数,如下:
profile中的Status记录该条SQL语句执行流程:
四.数据库缓存池(buffer pool)
InnoDB 存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页面(包括读页面、写页面、创建新页面等操作)。而磁盘 I/O 需要消耗的时间很多,而在内存中进行操作,效率则会高很多,为了能让数据表或者索引中的数据随时被我们所用,DBMS 会申请 占用内存来作为数据缓冲池 ,在真正访问页面之前,需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访问。这样做的好处是可以让磁盘活动最小化,从而 减少与磁盘直接进行 I/O 的时间 。要知道,这种策略对提升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里,那么访问的成本就会降低很多。
1.缓冲池(Buffer Pool)
在 InnoDB 存储引擎中有一部分数据会放到内存中,缓冲池则占了这部分内存的大部分,它用来存储各种数据的缓存,如下图所示:
从图中,你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典信息等。
缓存的重要性:
对于使用InnoDB作为存储引擎的表来说,不管是用于存储用户数据的索引(包括聚簇索引和二级索引),还是各种系统数据,都是以页的形式存放在表空间中的,而所谓的表空间只不过是InnoDB对文件系统上一个或几个实际文件的抽象,也就是说我们的数据说到底还是存储在磁盘上的。但是各位也都知道,磁盘的速度慢的跟乌龟一样,怎么能配得上“快如风,疾如电"的CPU呢?这里,缓冲池可以帮助我们消除CPU和磁盘之间的鸿沟。所以InnoDB存储引擎在处理客户端的请求时,当需要访问某个页的数据时,就会把完整的页的数据全部加载到内存中,也就是说即使我们只需要访问一个页的一条记录,那也需要先把整个页的数据加载到内存中。将整个页加载到内存中后就可以进行读写访问了,在进行完读写访问之后并不着急把该页对应的内存空间释放掉,而是将其缓存起来,这样将来有请求再次访问该页面时,就可以省去磁盘IO的开销了。
缓存池原则:
"
位置 * 频次
”这个原则,可以帮我们对 I/O 访问效率进行优化。首先,位置决定效率,提供缓冲池就是为了在内存中可以直接访问数据,其次,频次决定优先级顺序,因为缓冲池的大小是有限的,比如磁盘有 200G,但是内存只有 16G,缓冲池大小只有 1G,就无法将所有数据都加载到缓冲池里,这时就涉及到优先级顺序,会优先对使用频次高的热数据进行加载
。
查询缓存:
查询缓存是提前把查询结果缓存起来,这样下次不需要执行就可以直接拿到结果。需要说明的是,在MySQL 中的查询缓存,不是缓存查询计划,而是查询对应的结果( key为查询的SQL语句,Value为查询的结果 )。因为命中条件苛刻,而且只要数据表发生变化,查询缓存就会失效,因此命中率低。
缓存池执行流程图如下:
2.缓冲池如何读取数据
缓冲池管理器会尽量将经常使用的数据保存起来,在数据库进行页面读操作的时候,首先会判断该页面是否在缓冲池中,如果存在就直接读取,如果不存在,就会通过内存或磁盘将页面存放到缓冲池中再进行读取,缓存在数据库中的结构和作用如下图所示:
如果我们执行 SQL 语句的时候更新了缓存池中的数据,那么这些数据会马上同步到磁盘上吗?
实际上,当我们对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后数据库会以一定的频率刷新到磁盘上。注意并不是每次发生更新操作,都会立刻进行磁盘回写。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上,这样做的好处就是提升了数据库的整体性能。比如,当缓冲池不够用时,需要释放掉一些不常用的页,此时就可以强行采用checkpoint的方式,将不常用的脏页回写到磁盘上,然后再从缓冲池中将这些页释放掉。这里脏页((dirty page)指的是缓冲池中被修改过的页,与磁盘上的数据页不一致。
3.查看/设置缓冲池的大小
下面的操作都针对 InnoDB 存储引擎,具体操作如下:
通过 show variables like '%storage_engine%';
查询当前所使用的引擎,如下:
通过 show variables like 'innodb_buffer_pool_size';
查询InnoDB 引擎的缓冲池大小,如下:
MySQL的配置文件中的参数如下:
注释:单位为B,即上图中的大小为125829120B=125829120/1024/1024=120MB。
通过 set global innodb_buffer_pool_size = 268435456;
(268435456/1024/1024=256MB)设置InnoDB 引擎的缓冲池大小,如下:
备注:MySQL5.7及以后版本,改参数是动态的,修改后,无需重启MySQL,但是低版本,静态的,修改后,需要重启MySQL
多个Buffer Pool实例:
类似于缓存池一样,实际会有多个Buffer Pool实例,通过
show variables like 'innodb_buffer_pool_instances';
可以查询缓存池的个数,如下:
上面显示Buffer Pool的数量为8所以平均分下来,每个Buffer Poo占120/8=15M,即每个缓存池占15M。