邢台企业做网站哪儿好,wordpress刷新按钮,西安企业征信报告去哪里打,珠海商城网站一、逻辑架构 1.1 逻辑架构剖析1.1.1 连接层1.1.2 服务层01、基础服务组件02、SQL Interface#xff1a;SQL 接口03、Parser#xff1a;解析器04、Optimizer#xff1a;查询优化器05、Caches Buffers#xff1a; 查询缓存组件 1.1.3 引擎层1.1.4 存储层1.1.5 总结 1.… 一、逻辑架构 1.1 逻辑架构剖析1.1.1 连接层1.1.2 服务层01、基础服务组件02、SQL InterfaceSQL 接口03、Parser解析器04、Optimizer查询优化器05、Caches Buffers 查询缓存组件 1.1.3 引擎层1.1.4 存储层1.1.5 总结 1.2 数据库缓冲池1.2.1 为什么要有缓冲池Buffer Pool01、Buffer Pool 有多大02、Buffer Pool 缓存什么 1.2.2 如何管理 Buffer Pool01、如何管理空闲页02、如何管理脏页03、如何提高缓存命中率04、脏页什么时候会被刷入磁盘 1.3 一条 select 语句的执行流程1.3.1 连接器1.3.2 查询缓存1.3.3 解析器1.3.4 执行器01、预处理器02、优化器03、执行器 1.3.5 总结 1.4 一条 update 语句的执行流程 1.1 逻辑架构剖析
服务器在处理客户端发来的请求时都做了什么处理才能产生最后的处理结果呢下面是一张大致的流程图 具体展开是这样的大致可以分为连接层、服务层、引擎层、存储层四层结构
1.1.1 连接层
客户端连接器也就是 MySQL 服务器之外的客户端程序与具体的语言相关负责处理客户端的连接请求、验证用户身份以及向客户端发送响应。同时连接器还负责处理事务和锁定并确保数据完整性。
连接池提供了多个用于客户端与服务器交互的线程。
客户端访问 MySQL 服务器之前做的第一件事就是建立 TCP 连接。经过三次握手连接成功后MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取
如果用户名或密码不对会收到一个 Access denied for user 错误客户端程序结束执行如果用户名密码认证通过会从权限表查出账号拥有的权限与连接关联之后的权限判断逻辑都将依赖于此时读到的权限。
TCP 连接收到请求后必须要分配一个线程专门与这个客户端交互所以还会有个线程池去走后面的流程。每一个连接从线程池中获取线程省去了创建和销毁线程的开销。
1.1.2 服务层
Server 层主要负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这里实现主要包括连接器、查询缓存、解析器、预处理器、优化器和执行器等。另外所有的内置函数比如日期、时间、数学和加密函数等和所有的跨存储引擎的功能比如存储过程、触发器、视图等都在 Server 层实现。
01、基础服务组件
用于系统管理和控制。
02、SQL InterfaceSQL 接口
接收 SQL 指令返回查询结果。
接收用户的 SQL 命令并且返回用户需要查询的结果。比如 SELECT… FROM 就是调用了 SQL Interface。MySQL 支持 DML数据操作语言、DDL数据定义语言、存储过程、视图、触发器、自定义函数等多种 SQL 语言接口。
03、Parser解析器
如果没有命中缓存就要开始真正地执行语句了解析器会对 SQL 语句进行语法解析、语义解析、词法解析生成语法树。
在解析器中对 SQL 语句进行语法解析、语义解析、词法解析。将 SQL 语句分解成数据结构并将这个结构 传递到后续步骤中之后 SQL 语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误那么就说明这个 SQL 语句是不合理的。在 SQL 命令传递到解析器的时候会被解析器验证和解析并为其创建语法树并根据数据字典丰富查询语法树会验证该客户端是否具有执行该查询的权限。创建好语法树之后MySQL 还会对 SQL 查询进行语法上的优化从而进行查询重写。
04、Optimizer查询优化器
核心组件对 SQL 进行优化根据执行计划和表统计信息确定最优的查询执行方式决策是否使用索引等来获取最优结果。
SQL 语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径从而生成一个执行计划。这个执行计划表明应该使用哪些索引进行查询全表检索还是使用索引检索表之间的连接顺序如何最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询并将查询结果返回给用户。它使用“选取-投影-连接”策略进行查询。如SELECT id, name FROM student WHERE gender 女;这个 SELECT 查询先根据 WHERE 语句进行选取而不是将表全部查询出来以后再进行 gender 过滤。 这个 SELECT 查询先根据 id 和 name 进行属性 投影而不是将属性全部取出以后再进行过滤将这两个查询条件 连接起来生成最终查询结果。
05、Caches Buffers 查询缓存组件
以 key-value 的方式缓存查询结果。
MySQL 内部维持着一些 Cache 和 Buffer比如 Query Cache 用来缓存一条 SELECT 语句的执行结果如果能在其中找到对应的查询结果那么就不需要再进行查询解析、优化和执行的整个过程了直接将结果返回给客户端。这个缓存机制是由一系列小缓存组成的。比如表缓存记录缓存key缓存权限缓存等 。这个查询缓存可以在不同客户端之间共享。从 MySQL 5.7.20 开始不推荐使用查询缓存并在 MySQL 8.0 中删除 。
1.1.3 引擎层
插件式存储引擎层 Storage Engines在查询过程中可切换存储引擎与底层的文件系统交互。
真正的负责了 MySQ L中数据的存储和提取对物理服务器级别维护的底层数据执行操作服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同这样我们可以根据自己的实际需要进行选取。
1.1.4 存储层
所有的数据数据库、表的定义表的每一行的内容和索引都是存在 文件系统 上以 文件的方式存在的并完成与存储引擎的交互。当然有些存储引擎比如 InnoDB也支持不使用文件系统直接管理裸设备但现代文件系统的实现使得这样做没有必要了。在文件系统之下可以使用本地磁盘可以使用 DAS、NAS、SAN 等各种存储系统。
1.1.5 总结
SQL 的执行流程可以简化为这样
简化为三层结构
连接层客户端和服务器端建立连接客户端发送 SQL 至服务器端SQL 层服务层对 SQL 语句进行查询处理与数据库文件的存储方式无关存储引擎层与数据库文件打交道负责数据的存储和读取。
1.2 数据库缓冲池
1.2.1 为什么要有缓冲池Buffer Pool
MySQL 的数据是存储在磁盘里的但是如果每次都从磁盘里读取数据磁盘 I/O 需要消耗的时间很多性能是极差的。所以就考虑到加个缓存来存取数据下次查询同样的数据的时候直接从内存中读取即可。
InnoDB 存储引擎设计了一个缓冲池Buffer Pool来提高数据库的读写性能。这样做的好处是可以让磁盘活动最小化从而减少与磁盘直接进行 I/O 的时间 。这种策略对提升 SQL 语句的查询性能来说至关重要如果索引的数据在缓冲池里那么访问的成本就会降低很多。
那么有了缓冲池后
当读取数据时如果数据存在于 Buffer Pool 中客户端就会直接读取 Buffer Pool 中的数据否则再去磁盘中读取。当修改数据时首先是修改 Buffer Pool 中数据所在的页然后将其页设置为脏页最后由后台线程将脏页写入到磁盘。
01、Buffer Pool 有多大
Buffer Pool 是在 MySQL 启动的时候向操作系统申请的一片连续的内存空间默认配置下 Buffer Pool 只有 128MB 。
可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小一般建议设置成可用物理内存的 60%~80%。
02、Buffer Pool 缓存什么
InnoDB 会把存储的数据划分为若干个「页」以页作为磁盘和内存交互的基本单位一个页的默认大小为 16KB。因此Buffer Pool 同样需要按「页」来划分。
在 MySQL 启动的时候InnoDB 会为 Buffer Pool 申请一片连续的内存空间然后按照默认的 16KB 的大小划分出一个个的页 Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的之后随着程序的运行才会有磁盘上的页被缓存到 Buffer Pool 中。
所以MySQL 刚启动的时候我们会观察到使用的虚拟内存空间很大而使用到的物理内存空间却很小这是因为只有这些虚拟内存被访问后操作系统才会触发缺页中断接着将虚拟地址和物理地址建立映射关系。
Buffer Pool 除了缓存「索引页」和「数据页」还包括了 undo 页插入缓存、自适应哈希索引、锁信息等等 为了更好的管理这些在 Buffer Pool 中的缓存页InnoDB 为每一个缓存页都创建了一个控制块控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等。
控制块也是占有内存空间的它是放在 Buffer Pool 的最前面然后才是缓存页 其中控制块和缓存页之间灰色的部分称为碎片空间。至于这个碎片空间是怎么产生的是因为分配足够多的控制块和缓存页后可能会剩余一点空间而这点空间并不够再分配一对控制块和缓存页了所以这点空间自然也用不到了因此就被称为碎片了。当然了如果 Buffer Pool 大小设置的刚刚好的话也可能不会产生碎片。 查询一条记录就只需要缓冲一条记录吗 不是的。当我们查询一条记录时InnoDB 是会把整个页的数据加载到 Buffer Pool 中的因为通过索引只能定位到磁盘中的页而并不能定位到页中的某一条记录。将页加载到 Buffer Pool 后再通过页里的页目录去定位到某条具体的记录。
1.2.2 如何管理 Buffer Pool
01、如何管理空闲页
Buffer Pool 是一片连续的内存空间当 MySQL 运行一段时间后这段连续的内存空间中的缓存页既有空闲的也有被使用的。
当我们从磁盘读取数据时如果通过遍历这一片连续的内存空间来找到空闲的缓存页的话效率是非常低的。
所以为了能够快速地找到空闲的缓存页可以使用链表结构将空闲缓存页的「控制块」作为链表的节点这个链表称为 Free 链表空闲链表 Free 链表上除了有控制块还有一个头节点该头节点包含链表的头节点地址尾节点地址以及当前链表中节点的数量等信息。
Free 链表节点是一个一个的控制块而每个控制块包含着对应缓存页的地址所以相当于 Free 链表节点都对应一个空闲的缓存页。
有了 Free 链表后每当需要从磁盘中加载一个页到 Buffer Pool 中时就从 Free 链表中取一个空闲的缓存页并且把该缓存页对应的控制块的信息填上然后把该缓存页对应的控制块从 Free 链表中移除。
02、如何管理脏页
设计 Buffer Pool 除了能提高读性能还能提高写性能也就是更新数据的时候不需要每次都要写入磁盘而是将 Buffer Pool 对应的缓存页标记为脏页然后再由后台线程将脏页写入到磁盘。
为了能快速知道哪些缓存页是脏的于是就设计出 Flush 链表它跟 Free 链表类似的链表的节点也是控制块区别在于 Flush 链表的元素都是脏页 有了 Flush 链表后后台线程就可以遍历 Flush 链表将脏页写入到磁盘。
03、如何提高缓存命中率
Buffer Pool 的大小是有限的对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中而一些很少访问的数据希望可以在某些时机可以淘汰掉从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据同时还能保证常用数据留在 Buffer Pool 中。
要实现这个最容易想到的就是最少最近使用 LRULeast recently used算法。
该算法的思路是链表头部的节点是最近使用的而链表末尾的节点是最久没被使用的。那么当空间不够了就淘汰最久没被使用的节点从而腾出空间。
简单的 LRU 算法的实现思路是这样的
当访问的页在 Buffer Pool 里就直接把该页对应的 LRU 链表节点移动到链表的头部。当访问的页不在 Buffer Pool 里除了要把页放入到 LRU 链表的头部还要淘汰 LRU 链表末尾的节点。
如下图所示假设 LRU 链表长度为 5LRU 链表从左到右有 12345 的页 如果访问了 3 号的页因为 3 号页在 Buffer Pool 里所以把 3 号页移动到头部即可 接下来访问了 8 号页因为 8 号页不在 Buffer Pool 里所以需要先淘汰末尾的 5 号页然后再将 8 号页加入到头部
所以我们可以了解到Buffer Pool 里有三种页和链表来管理数据
Free Page空闲页表示此页未被使用位于 Free 链表Clean Page干净页表示此页已被使用但是页面未发生修改位于 LRU 链表。Dirty Page脏页表示此页「已被使用」且「已经被修改」其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后内存数据和磁盘数据一致那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。
简单的 LRU 算法并没有被 MySQL 使用因为简单的 LRU 算法无法避免预读失效和 Buffer Pool 污染两个问题。 什么是预读失效 MySQL 的预读机制程序是有空间局部性的靠近当前被访问数据的数据在未来很大概率会被访问到。所以MySQL 在加载数据页时会提前把它相邻的数据页一并加载进来目的是为了减少磁盘 IO。
但是可能这些被提前加载进来的数据页并没有被访问相当于这个预读是白做了这个就是预读失效。
如果使用简单的 LRU 算法就会把预读页放到 LRU 链表头部而当 Buffer Pool空间不够的时候还需要把末尾的页淘汰掉。
如果这些预读页如果一直不会被访问到就会出现一个很奇怪的问题不会被访问的预读页占用了 LRU 链表前排的位置而末尾淘汰的页可能是频繁访问的页这样就大大降低了缓存命中率。 怎么解决预读失效而导致缓存命中率降低的问题 我们不能因为害怕预读失效而将预读机制去掉大部分情况下局部性原理还是成立的。
要避免预读失效带来影响最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短让真正被访问的页移动到 LRU 链表的头部从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。
那到底怎么才能避免呢
MySQL 是这样做的它改进了 LRU 算法将 LRU 划分了 2 个区域old 区域 和 young 区域young 区域在 LRU 链表的前半部分old 区域则是在后半部分如下图 old 区域占整个 LRU 链表长度的比例可以通过 innodb_old_blocks_pct 参数来设置默认是 37代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37。
划分这两个区域后预读的页就只需要加入到 old 区域的头部当页被真正访问的时候才将页插入 young 区域的头部。如果预读的页一直没有被访问就会从 old 区域移除这样就不会影响 young 区域中的热点数据。
举个例子
假设有一个长度为 10 的 LRU 链表其中 young 区域占比 70 %old 区域占比 30 %: 现在有个编号为 20 的页被预读了这个页只会被插入到 old 区域头部而 old 区域末尾的页10号会被淘汰掉 如果 20 号页一直不会被访问它也没有占用到 young 区域的位置而且还会比 young 区域的数据更早被淘汰出去。
如果 20 号页被预读后立刻被访问了那么就会将它插入到 young 区域的头部young 区域末尾的页7号会被挤到 old 区域作为 old 区域的头部这个过程并不会有页被淘汰 虽然通过划分 old 区域 和 young 区域避免了预读失效带来的影响但是还有个问题无法解决那就是 Buffer Pool 污染的问题。 什么是 Buffer Pool 污染 当某一个 SQL 语句扫描了大量的数据时在 Buffer Pool 空间比较有限的情况下可能会将 Buffer Pool 里的所有页都替换出去导致大量热数据被淘汰了等这些热数据又被再次访问的时候由于缓存未命中就会产生大量的磁盘 IOMySQL 性能就会急剧下降这个过程被称为 Buffer Pool 污染。
注意Buffer Pool 污染并不只是查询语句查询出了大量的数据才出现的问题即使查询出来的结果集很小也会造成 Buffer Pool 污染。
比如在一个数据量非常大的表执行了这条语句
select * from t_user where name like %xiaolin%;可能这个查询出来的结果就几条记录但是由于这条语句会发生索引失效所以这个查询过程是全表扫描的接着会发生如下的过程
从磁盘读到的页加入到 LRU 链表的 old 区域头部当从页里读取行记录时也就是页被访问的时候就要将该页放到 young 区域头部接下来拿行记录的 name 字段和字符串 xiaolin 进行模糊匹配如果符合条件就加入到结果集里如此往复直到扫描完表中的所有记录。
经过这一番折腾原本 young 区域的热点数据就都会被替换掉。
举个例子假设需要批量扫描2122232425 这五个页这些页都会被逐一访问读取页里的记录 在批量访问这些数据的时候会被逐一插入到 young 区域头部 可以看到原本在 young 区域的热点数据 6 和 7 号页都被淘汰了这就是 Buffer Pool 污染的问题。 怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题 像前面这种全表扫描的查询很多缓冲页其实只会被访问一次但是它却只因为被访问了一次而进入到 young 区域从而导致热点数据被替换了。
但是LRU 链表中 young 区域就是热点数据只要我们提高进入到 young 区域的门槛就能有效地保证 young 区域里的热点数据不会被替换掉。
所以MySQL 是这样做的进入到 young 区域条件增加了一个停留在 old 区域的时间判断。
具体是这样做的在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间
如果后续的访问时间与第一次访问的时间在某个时间间隔内那么该缓存页就不会被从 old 区域移动到 young 区域的头部如果后续的访问时间与第一次访问的时间不在某个时间间隔内那么该缓存页移动到 young 区域的头部
这个间隔时间是由 innodb_old_blocks_time 控制的默认是 1000 ms。
也就说只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件才会被插入到 young 区域头部这样就解决了 Buffer Pool 污染的问题 。
另外MySQL 针对 young 区域其实做了一个优化为了防止 young 区域节点频繁移动到头部。young 区域前面 1/4 被访问不会移动到链表头部只有后面的 3/4被访问了才会。
04、脏页什么时候会被刷入磁盘
引入了 Buffer Pool 后当修改数据时首先是修改 Buffer Pool 中数据所在的页然后将其页设置为脏页但是磁盘中还是原数据。
因此脏页需要被刷入磁盘保证缓存和磁盘数据一致但是若每次修改数据都刷入磁盘则性能会很差因此一般都会在一定时机进行批量刷盘。
可能大家担心如果在脏页还没有来得及刷入到磁盘时MySQL 宕机了不就丢失数据了吗
这个不用担心InnoDB 的更新操作采用的是Write Ahead Log预写日志 策略即先写日志再写入磁盘通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。
下面几种情况会触发脏页的刷新
当 redo log 日志满了的情况下会主动触发脏页刷新到磁盘Buffer Pool 空间不足时需要将一部分数据页淘汰掉如果淘汰的是脏页需要先将脏页同步到磁盘MySQL 认为空闲时后台线程会定期将适量的脏页刷入到磁盘MySQL 正常关闭之前会把所有的脏页刷入到磁盘
在我们开启了慢 SQL 监控后如果你发现「偶尔」会出现一些用时稍长的 SQL这可能是因为脏页在刷新到磁盘时可能会给数据库带来性能开销导致数据库操作抖动。
如果间断出现这种现象就需要调大 Buffer Pool 空间或 redo log 日志的大小。
1.3 一条 select 语句的执行流程
现有一条查询语句
select * from product where id 1;这条查询语句在 MySQL 运行期间都发生了什么? 这张图是非常经典的一个执行流程图从客户端建立起连接后最主要的就是 Server 层和存储引擎层的交互下面就来展开说一说每一个功能模块的作用
1.3.1 连接器
连接器连接会经过三个步骤只有上一步骤通过了才会继续往下执行
建立连接客户端连接 MySQL 服务需要经过 TCP 三次握手因为 MySQL 是基于 TCP 协议进行传输的如果报错就说明连接失败校验用户身份校验客户端的用户名和密码如果用户名或密码错误客户端的程序就结束执行获取权限用户身份验证通过后连接器就会获取该用户的权限保存起来后续该用户在此连接里的任何操作都会基于连接开始时读取到的权限进行权限逻辑的判断。
所以如果一个用户已经建立了连接即使管理员中途修改了该用户的权限也不会影响已经存在连接的权限。修改完成后只有再新建的连接才会使用心得权限设置。
这里有几个问题 如何查看 MySQL 服务被多少个客户端连接了 可以执行命令来查看
show processlist其中有几列比如User 表示哪个用户连接了 MySQL 服务Command 表示当前用户执行的命令Time 表示空闲时长。 空闲连接会一直占用着吗? 空闲连接不会一直占用着MySQL 中定义了空闲连接的最大空闲时长由 wait_timeout 参数控制的默认值是 8 小时28800秒如果空闲连接超过了这个时间连接器就会自动将它断开。
mysql show variables like wait_timeout;
----------------------
| Variable_name | Value |
----------------------
| wait_timeout | 28800 |
----------------------
1 row in set (0.00 sec)我们也可以自己手动断开空闲的连接使用 kill connection id 命令
mysql kill connection 6;
Query OK, 0 rows affected (0.00 sec)注意一个处于空闲状态的连接被服务端主动断开后这个客户端并不会马上知道而是等到客户端在发起下一个请求的时候才会收到报错信息“ERROR 2013 (HY000): Lost connection to MySQL server during query”。 MySQL 的连接数有限制吗 MySQL 服务支持的最大连接数由 max_connections 参数控制如果超过这个值系统就会拒绝接下来的连接请求并报错提示“Too many connections”。
MySQL 的连接与 HTTP 一样也有长连接和短连接的概念
# MySQL中的长连接和短连接
# 短连接
连接 mysql 服务TCP 三次握手
执行sql
断开 mysql 服务TCP 四次挥手
# 长连接
连接 mysql 服务TCP 三次握手
执行sql
执行sql
执行sql
....
断开 mysql 服务TCP 四次挥手# HTTP中的长连接和短连接
# 长连接
建立连接 — — 数据传输 — — 保持连接心跳— — 数据传输 — — 保持连接心跳… — — 关闭连接
# 短连接
建立连接 — — 数据传输 — — 关闭连接…建立连接 — — 数据传输 — — 关闭连接可以看到使用长连接的好处就是可以减少建立连接和断开连接的过程所以一般推荐使用长连接。
但是使用长连接后可能会占用内存增多因为 MySQL 在执行查询过程中临时使用内存管理连接对象这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多将导致 MySQL 服务占用内存太大有可能会被系统强制杀掉这样会发生 MySQL 服务异常重启的现象。 如何解决长连接占用内存的问题 有两种解决方式 第一种定期断开长连接。既然断开连接后就会释放连接占用的内存资源那么我们可以定期断开长连接。 第二种客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口注意这是接口函数不是命令那么当客户端执行了一个很大的操作后在代码里调用 mysql_reset_connection 函数来重置连接达到释放内存的效果。这个过程不需要重连和重新做权限验证但是会将连接恢复到刚刚创建完时的状态。
1.3.2 查询缓存
连接器的工作完成后客户端就可以向 MySQL 服务发送 SQL 语句了MySQL 服务收到 SQL 语句后就会解析出 SQL 语句的第一个字段看看是什么类型的语句。
如果 SQL 是查询语句select 语句MySQL 就会先去查询缓存 Query Cache 里查找缓存数据看看之前有没有执行过这一条命令这个查询缓存是以 key-value 形式保存在内存中的key 为 SQL 查询语句value 为 SQL 语句查询的结果。
如果查询的语句命中查询缓存那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中那么就要往下继续执行等执行完后查询的结果就会被存入查询缓存中。
这里需要说明的是查询缓存缓存的不是查询计划而是查询对应的结果。这就意味着查询匹配的鲁棒性大大降 低只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同例如空格、注释、大小写等都会导致缓存不会命中。因此MySQL 的查询缓存命中率并不高。
另外MySQL 的缓存系统会监测涉及到的每张表只要该表的结构或者数据被修改比如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句那么使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除所以对于更新压力大的数据库来说查询缓存的命中率会非常低。
所以MySQL 8.0 版本直接将查询缓存删掉了也就是说 MySQL 8.0 开始执行一条 SQL 查询语句不会再走到查询缓存这个阶段了。
对于 MySQL 8.0 之前的版本如果想关闭查询缓存我们可以通过将参数 query_cache_type 设置成 DEMAND。
注意 这里需要说明的是查询缓存是 Server 层的也就是 MySQL 8.0 版本移除的是 Server 层的查询缓存并不是 Innodb 存储引擎中的 buffer pool。
1.3.3 解析器
在正式执行 SQL 查询语句之前MySQL 会对 SQL 语句做解析解析器会做两件事情 词法解析 MySQL 会根据我们输入的字符串识别出关键字比如这条语句 select usernme, ismale from userinfo where age 20 and level 5 and 1 1;在分析之后会得到 10 个 Token其中有三个 keyword分别为 select、from 和 where。 语法解析 根据词法分析的结果语法解析器会根据语法规则判断我们输入的这个 SQL 语句是否满足 MySQL 语法如果没问题就会构建出 SQL 语法树这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。 如果我们输入的 SQL 语句语法不对就会在解析器这个阶段报错。但需要注意的是表不存在或者字段不存在并不是在解析器里做的解析器只负责检查语法和构建语法树但是不会去查表或者字段存不存在。
1.3.4 执行器
经过了解析器解析、优化器优化后接着才开始真正地执行 SQL 查询语句的流程了每条 SELECT 查询语句流程主要可以分为三个阶段
prepare 阶段也就是预处理阶段optimize 阶段也就是优化阶段execute 阶段也就是执行阶段。
01、预处理器
预处理阶段大概做了两件事
检查 SQL 查询语句中的表或者字段是否存在如果不存在就会在执行 SQL 查询语句的 prepare 阶段中报错将 select * 中的 * 符号扩展为表上的所有列。
02、优化器
经过预处理阶段后还需要为 SQL 查询语句先制定一个执行计划选择查询成本最小的计划这个也是由优化器来完成的。
在优化器中会确定 SQL 语句的执行路径比如是根据全表检索还是根据索引检索等。我们可以使用 explain关键字来查看 SQL 语句的执行计划。
03、执行器
在执行器执行的过程中它与存储引擎交互此交互是以记录页为单位的。执行器和存储引擎的交互过程大概分为三部分
主键索引查询全表扫描索引下推
具体的可以参考链接执行一条select语句期间发生了什么
1.3.5 总结
执行一条 SQL 查询语句期间发生了什么大概有以下几个步骤 连接器建立连接管理连接、校验用户身份 查询缓存查询语句如果命中查询缓存则直接返回否则继续往下执行。MySQL 8.0 已删除该模块 解析 SQL通过解析器对 SQL 查询语句进行词法分析、语法分析然后构建语法树方便后续模块读取表名、字段、语句类型 执行 SQL执行 SQL 共有三个阶段 预处理阶段检查表或字段是否存在将 select * 中的 * 符号扩展为表上的所有列 优化阶段基于查询成本的考虑 选择查询成本最小的执行计划 执行阶段根据执行计划执行 SQL 查询语句从存储引擎读取记录返回给客户端。
1.4 一条 update 语句的执行流程 大致的执行流程与 select 语句是一样的这里需要注意存储引擎层存储和读取数据时记录日志阶段 调用存储引擎接口后会先从 Buffer Pool 获取数据页如果没有就从磁盘中读入 Buffer Pool然后判断更新前后的记录是否一样 开启事务修改数据之前先记录 undo log写入 Buffer Pool 的 undo page 开始更新 page data 中的记录被修改的数据页称为脏页修改会被记录到内存中的 redo log buffer 中再刷盘到磁盘的 redo log 文件此时事务是 perpare阶段 这个时候更新就完成了当时脏页不会立即写入磁盘而是由后台线程完成这里会用 double write 来保证脏页刷盘的可靠性 还没结束呢这时候可以通知 Server 层可以正式提交数据了 执行器记录 binlog cache事务提交时才会将该事务中的 binglog 刷新到磁盘中 这个时候 Update 语句完成了 Buffer Pool 中数据页的修改、undo 日志、redo log 缓存记录以及记录 binlog cache 缓存 commit 阶段这个阶段是将 redo log 中事务状态标记为 commit 此时 binlog 和 redo log 都已经写入磁盘如果触发了刷新脏页的操作先把脏页 copy 到 double write buffer 里Double Write Buffer 的内存数据刷到磁盘中的共享表空间 ibdata再刷到数据磁盘上数据文件 ibd 中。 流程完结。
