石桥铺做网站,django做购物网站,网页设计教程谁的好,IP不能安装wordpress文章目录 MYSQL逻辑架构1. 逻辑架构剖析1.1.1 MYSQL的逻辑架构---三层1.1.2 连接层---第一层1.1.3 服务层---第二层1.1.4 引擎层---第三层 2 服务器处理客户端请求---三部分3. SQL执行流程3.0 SQL语法顺序3.1 MYSQL中的SQL执行流程3.1.1 查询缓存3.1.2. 解析器3.1.3. 优化器3.1… 文章目录 MYSQL逻辑架构1. 逻辑架构剖析1.1.1 MYSQL的逻辑架构---三层1.1.2 连接层---第一层1.1.3 服务层---第二层1.1.4 引擎层---第三层 2 服务器处理客户端请求---三部分3. SQL执行流程3.0 SQL语法顺序3.1 MYSQL中的SQL执行流程3.1.1 查询缓存3.1.2. 解析器3.1.3. 优化器3.1.3.1 优化器作用3.1.3.2 逻辑查询(sql等价变换)和物理查询(索引)3.1.4. 执行器 3.2. MYSQL8中SQL执行原理--实操3.2.1 数据准备3.2.1 确认profiling 是否开启3.2.2 多次执行相同sql语句3.2.3 查看当前会话所产生的所有 profiles3.2.4 查看profile-执行计划3.2.5 查看更丰富的内容 3.3. MYSQL5.0中SQL执行原理了解 MYSQL逻辑架构
1. 逻辑架构剖析
1.1.1 MYSQL的逻辑架构—三层
MYSQL逻辑架构一共三层。下图针对MYSQL5.0在MYSQL8.0中没有了CacheBuffers查询缓存。具体展开如下图 连接层Connection Pool。获取MYSQL服务层连接 服务层核心SQL InterfaceSQL接口、Parser解析器、Optimizer优化器、CacheBuffer查询缓存-mysql5.0有 引擎层Pluggable Storage Engines插件式的存储引擎 MYSQL5.0中CacheBuffers查询缓存将查询结果以key-value的形式缓存key为sql语句value是查询结果。下次查询的sql和key完全一样直接返回。插件式存储引擎可以根据需要更换存储引擎与底层文件系统进行交互。
具体查询执行顺序
MYSQL客户端程序发起连接利用连接池建立连接并分配线程处理请求SQL接口接收SQL指令MYSQL5.0查询缓存有key直接返回MYSQL8.0中没有这一步缓存中没有则在解析器中进行sql解析并生成语法树核心在优化器中进行逻辑优化或物理优化物理索引是指使用索引。sql语句在语法解析之后、查询之前会使用查询优化器确定sql语句执行路径生成一个 执行计划。调用对应的API即存储引擎回到文件系统中进行查找(会先将物理层文件加载到内存中–buffer库缓冲池直接与磁盘交互代价太大)MYSQL5.0将查询结果已key-value形式缓存到查询缓存最后通过SQL接口将查询结果返回到连接池再返回到客户端。
1.1.2 连接层—第一层
连接层Connection Pool。获取MYSQL服务层连接 系统客户端访问 MySQL 服务器前做的第一件事就是 建立 TCP 连接。 经过三次握手建立连接成功后 MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。 用户名或密码不对会收到一个Access denied for user错误客户端程序结束执行用户名密码认证通过会从权限表查出账号拥有的权限与连接关联之后的权限判断逻辑都将依赖于此时读到的权限 TCP 连接收到请求后必须要 分配给一个线程 专门与这个客户端的交互。所以还会有个线程池去走后面的流程。每一个连接从线程池中获取线程省去了创建和销毁线程的开销。
1.1.3 服务层—第二层
服务层核心SQL InterfaceSQL接口、Parser解析器、Optimizer优化器、CacheBuffer查询缓存-mysql5.0有 SQL Interface: SQL接口 接收用户的SQL命令并且 返回用户需要查询的结果。比如SELECT … FROM就是调用SQL InterfaceMySQL支持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 查询缓存组件mysql5.0有mysql8.0无 MySQL内部维持着一些 Cache和Buffer比如Query Cache用来缓存一条SELECT语句的执行结果如果能够在其中找到对应的查询结果那么就不必再进行查询解析、优化和执行的整个过程了直接将结果反馈给客户端。这个缓存机制是由一系列小缓存组成的。比如表缓存记录缓存key缓存权限缓存等 。这个查询缓存可以在 不同客户端之间共享 。 从MySQL 5.7.20开始不推荐使用查询缓存并在 MySQL 8.0中删除 。
1.1.4 引擎层—第三层
引擎层Pluggable Storage Engines插件式的存储引擎
真正的负责了MySQL中数据的存储和提取对 物理服务器级别维护的底层数据执行操作服务器通过 API与存储引擎进行通信。不同的存储引擎具有的功能不同这样我们可以根据自己的实际需要进行选取。MySQL 8.0.25默认支持的存储引擎如下查询引擎语句show engines;
2 服务器处理客户端请求—三部分
MYSQL是典型的C/S架构即Client/Server架构服务器端程序使用的mysqld。
不论客户端进程和服务器进程采用哪种方式进行通信最后实现的效果都是客户端进程向服务器进程发送一段文本SQL语句服务器进程处理后再向客户端进程发送一段文本处理结果。
服务器进程对客户端进程发送的请求处理过程如下图
连接管理处理客户端连接如用户名密码校验、权限校验解析与优化解析查询语句对sql进行优化处理存储引擎调用存储引擎对物理层文件查找会先将物理层文件加载到内存中–buffer库缓冲池直接与磁盘交互代价太大
3. SQL执行流程
3.0 SQL语法顺序
随着Mysql版本的更新换代其优化器也在不断的升级优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
例查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人显示人数最多的第一名部门信息下面是经常出现的查询顺序
3.1 MYSQL中的SQL执行流程 SQL 语句在 MySQL 中的流程是 SQL语句→查询缓存→解析器→优化器→执行器 。
3.1.1 查询缓存
查询缓存 Server 如果在查询缓存中发现了这条 SQL 语句就会直接将结果返回给客户端如果没有就进入到解析器阶段。
MYSQL8.0抛弃原因 查询缓存往往效率不高所以在 MySQL8.0 之后就抛弃了这个功能。 大多数情况查询缓存就是个鸡肋为什么呢 SELECT employee_id,last_name FROM employees WHERE employee_id 101;鲁棒性大大降低只有相同的查询语句操作才会命中上述查询语句整个作为key存储如果下次中间多加了一个空格或者别的会认为key不同查询不命中继续重新去查询。查询中有时会调用函数如NOW()每次调用都是当前时间即使查询请求文本信息都一样不同时间的两次查询也会得到不同结果。缓存失效情况MySQL的缓存系统会监测涉及到的每张表只要该 表的结构或者数据被修改如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTERTABLE 、 DROP TABLE 或 DROP DATABASE 语句那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除对于 更新压力大的数据库 来说查询缓存的命中率会非常低。
3.1.2. 解析器
解析器在解析器中对 SQL 语句进行语法分析、语义分析。 分析器先做“ 词法分析 ”。你输入的是由多个字符串和空格组成的一条 SQL 语句MySQL 需要识别出里面的字符串分别是什么代表什么。 MySQL 从你输入的 “select” 这个关键字识别出来这是一个查询语句。它也要把字符串“T”识别成“表名 T”把字符串“ID”识别成“列 ID”。 词法分析步骤 接着要做“ 语法分析 ”。根据词法分析的结果语法分析器比如Bison会根据语法规则判断你输入的这个 SQL 语句是否满足 MySQL 语法 。 select department_id,job_id,avg(salary) from employees group by department_id;如果语法错误会报错 如果SQL语句正确则会生成一个这样的语法树
3.1.3. 优化器
3.1.3.1 优化器作用
优化器在优化器中会 确定SQL 语句的执行路径比如是根据 全表检索 还是根据 索引检索 等。
经过解析器MYSQL知道了你要做什么。在开始执行之前还要经过优化器的处理。一条查询可以有很多执行方式最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划。 举例如下语句是执行两个表的 join select * from test1 join test2 using(ID)
where test1.namezhangwei and test2.namemysql高级课程;方案1可以先从表 test1 里面取出 namezhangwei的记录的 ID 值再根据 ID 值关联到表 test2再判断 test2 里面 name的值是否等于 mysql高级课程。方案2可以先从表 test2 里面取出 namemysql高级课程 的记录的 ID 值再根据 ID 值关联到 test1再判断 test1 里面 name的值是否等于 zhangwei。这两种执行方法的逻辑结果是一样的但是执行的效率会有不同而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后这个语句的执行方案就确定下来了然后进入执行器阶段。 优化器是怎么选择索引的有没有可能选择错等。在后面索引部分细讲。
3.1.3.2 逻辑查询(sql等价变换)和物理查询(索引)
在查询优化器中可以分为 逻辑查询 优化阶段和 物理查询 优化阶段。 逻辑查询优化通过 SQL等价变换提升查询效率即换一种查询写法执行效率更高 如对条件表达式进行等价谓词重写条件简化对视图进行重写对子查询进行优化对连接语句进行外连接消除、嵌套连接消除等。物理查询优化通过 索引和表连接 方式等技术进行优化重点是索引。 如对单表和多表连接操作高效使用索引提升查询效率。3.1.4. 执行器
截止到现在还没有真正去读写真实的表仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。
在执行之前需要判断该用户是否 具备权限 。 如果没有就会返回权限错误。如果具备权限就执行 SQL查询并返回结果。
在 MySQL8.0 以下的版本如果设置了查询缓存这时会将查询结果进行缓存。例表 test 中ID 字段没有索引那么执行器的执行流程是这样的 select * from test where id1;如果有权限就打开表继续执行。打开表的时候执行器会根据表的引擎定义调用存储引擎API对表进行的读写。存储引擎API只是抽象接口下面还有个 存储引擎层具体实现还要看表选择的存储引擎。 调用 InnoDB 引擎接口取这个表的第一行判断 ID 值是不是1如果不是则跳过如果是则将这行存在结果集中调用引擎接口取“下一行”重复相同的判断逻辑直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。至此这个语句就执行完成了。对于有索引的表执行的逻辑也差不多。
SQL 语句在 MySQL 中的流程是 SQL语句→查询缓存→解析器→优化器→执行器 。
3.2. MYSQL8中SQL执行原理–实操
在MYSQL中对一条SQL语句的执行时间进行分析
3.2.1 数据准备
在数据库中导入SQL脚本atguigudb.sql或者打开直接执行在database中执行 导入数据库成功
3.2.1 确认profiling 是否开启 了解查询语句执行过程查看是否开启计划。开启后可以让MYSQL收集在SQL执行所使用的资源情况命令如下 select profiling; # 不写默认session级别
或者
show variables like profiling;profiling0 代表关闭我们需要把 profiling 打开即设置为 1
3.2.2 多次执行相同sql语句
多次执行查询语句select * from employees;
3.2.3 查看当前会话所产生的所有 profiles 查看当前会话所产生的所有 profiles show profiles; # 显示最近的几次查询3.2.4 查看profile-执行计划 查看执行计划查看程序的执行步骤 show profile; 当然你也可以查询指定的 Query ID比如show profile for query 8; 查询 SQL 的执行时间结果和上面是一样的因为MYSQL8.0中没有缓存CacheBuffers这里使用的MYSQL8.0版本。
3.2.5 查看更丰富的内容
以查询更丰富的内容查看cpu、io阻塞等参数情况show profile cpu,block io for query 7; 除了查看cpu、io阻塞参数情况还可以查询下列参数的利用情况
3.3. MYSQL5.0中SQL执行原理了解
上述操作在MySQL5.7中测试发现前后两次相同的sql语句执行的查询过程仍然是相同的。不是会使用缓存吗这里我们需要 显式开启查询缓存模式 。在MySQL5.7中如下设置 配置文件中开启查询缓存 在 /etc/my.cnf 中新增一行query_cache_type1 重启mysql服务systemctl restart mysqld 开启查询执行计划 由于重启过服务需要重新执行如下指令开启profiling。 set profiling1;执行查询语句两次 select * from locations;查看profiles 查看profile显示执行计划查看程序的执行步骤show profile for query 1; show profile for query 2;结论执行编号2时比执行编号1时少了很多信息从截图中可以看出查询语句直接从缓存中获取数据。
结论 在MYSQL5.0中重复查询会先直接从缓存中获取数据
注意 在MYSQL5.0中SQL必须一致否则不能命中缓存 select * from department where id10;
select * from department where id 10;同样的开启缓存的配置信息如果在MYSQL8.0中添加重启服务会报错。 MYSQL8.0中没有query_cache参数执行下面语句 show variables like %query_cache%;mysql5.0中显示 mysql8.0中显示
