mysql check执行流程,MySQL==> SQL执行流程剖析

一、MySQL基本架构

从该图可以看出，MySQL 主要分为 Server 层和存储引擎层：

Server 层中包罗毗邻器，查询缓存，剖析器，优化器，执行器，涵盖 MySQL 的大多数焦点服务功效，以及所有的内置函数(如日期、时间、数学和加密函数等)，所有跨存储引擎的功效(存储历程、触发器、视图等)都在这一层实现。

存储引擎层主要卖力最终数据的存储和提取，其架构模式是插件式的，支持 InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本最先成为了默认存储引擎。

二、SELECT 语句的执行历程

SELECT 语句的执行历程为：毗邻、查询缓存、词法剖析，语法剖析，语义剖析，组织执行树，天生执行计划、执行器执行计划，下面最先梳理一次完整的查询流程：

毗邻器

毗邻器卖力与客户端确立毗邻,获取权限,维持和治理毗邻

毗邻下令: >mysql -uroot -p123456 -h127.0.0.1 -P3306 -A

其中 mysql 是自带的一个客户端毗邻工具

毗邻的基本流程: 认证用户名+密码 -> 权限列表中查询拥有的权限

后续的权限判断,都依赖于此时读到的权限.因此在修改权限之后要想生效需要重新登录

毗邻确立后,无其他动作,则此毗邻将处于空闲状态.

查看下令: >show processlist

Command列若显示为Sleep,则示意空闲

若毗邻后无任何动作,毗邻器会自动断开

控制参数: wait_timeout, 默认8小时

针对非交互式毗邻

例: jdbc的方式

控制参数: interactive_timeout, 默认8小时

针对交互式毗邻(交互式毗邻:即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项)

例:终端的毗邻方式

长毗邻&短毗邻

建议使用长毗邻,削减资源确立时的开销,由于确立毗邻的历程比较复杂(开空间,验证密码,查权限…)

然则若是都使用长毗邻,则可能会导致MySQL占用内存增进过快而泛起内存溢出

缘故原由是MySQL在执行历程中暂且使用的内存是治理在毗邻工具内里的.这些资源是要到毗邻断开时才会释放的.

历久积累会导致内存溢出,被系统强行kill掉,征象就是MySQL异常重启

解决:

定期断开长毗邻.使用一段时间/程序判断执行过一个占用内存极大的查询事后断开毗邻,之后在重连

MySQL5.7之后,可在每次执行一个较大的操作后,执行mysql_reset_connection来重新初始化毗邻资源.此历程不需要重连和重新做权限验证.会将毗邻恢复到刚刚建立完成时的状态.

查询缓存

执行过的查询会被 MySQL 以 key-value 的形式缓存起来. key 是查询语句, value 是查询效果

实际使用时不建议开启此功效.缘故原由是 MySQL 在执行 update 时会将整个表的所有缓存都失效

定制化设置:

query_cache_type=DEMAND, 默认的 SQL 都不使用查询缓存.

显示指定使用缓存:

select SQL_CACHE * from tb_xxx where id=xxx;

MySQL8.0以上版本已将此功效废弃

剖析器

剖析SQL语句

词法剖析: 剖析输入的语句的每个单词,将 select 识别为查询语句,from 之后的字符串识别为表等

从 information schema 内里获得表的结构信息

语法剖析: 基于词法剖析的效果,语法剖析器会判断是否知足 MySQL 语法规则

优化器

经由剖析器之后, MySQL 即知道详细需要做什么操作,然则在详细操作之前要先经由优化器

优化:

表中若存在多个索引时,选择该使用哪个索引

多表关联时,决议各表的毗邻顺序

执行器

详细该执行的操作.

执行之前要先判断对表的操作是否具备权限.若是没有会返回权限错误的提醒

若是存在查询缓存,会在查询缓存返回效果时来做权限验证,查询会在优化器之前挪用 precheck 验证权限

具备权限之后,即打开表最先执行.打开表时会凭据表的引擎界说来选择详细的引擎,并挪用其接口来执行

执行历程(无索引,InnoDB):

挪用 InnoDB 引擎取此表的第一行数据,判断 Where 条件是否知足,知足则将此行存在效果集中, 不知足则跳过

挪用 InnoDB 引擎取下一行数据,重复上述逻辑,直到最后一行

执行器将所有知足条件的行作为效果集返回给客户端

问题:

对表的权限验证为何是在执行器阶段来执行？

SQL语句要操作的表不只是SQL字面上那些,例如触发器，得在执行器阶段(历程中)才气确定。优化器等其他阶段是无能为力的

存储引擎

卖力数据的存储和提取

总结

1、客户端与服务端毗邻,毗邻器来卖力确立毗邻

cmd: mysql -uroot -p123456 -h127.0.0.1 -P3306 -A

历程:

验证用户名+密码

从权限列表中查询所拥有的权限

2、判断是否掷中查询缓存,检查当前是否开启了查询缓存(query_cache_type)

若是开启了查询缓存,则用当前 sql 作为 key 去缓存中查询,若是存在,则直接返回效果

3、剖析SQL: 剖析器事情,剖析SQL,先做词法剖析

识别出关键字如 select,insert, from 后的表, where 后的查询条件等

4、语法剖析,基于词法剖析的效果,来识别当前的SQL是否知足语法划定,好比关键字的使用先后顺序等

5、优化SQL执行,优化器事情,优化SQL的执行

如:选择要使用的索引

如:连表时选择的毗邻顺序

6、执行SQL,执行器事情,执行SQL语句

判断对此表是否具有查询权限

权限具备,则打开表最先执行

凭据表的引擎界说,选择详细的引擎,去调引擎的接口执行查询

查询到的数据放入内存中,放入效果集里.

查询完毕后,将效果集返回给客户端

三、UPDATE 语句执行历程

UPDATE 语句执行历程总体上和 SELECT 语句是差不多的，分为：毗邻、查询缓存、词法剖析，语法剖析，语义剖析，组织执行树，天生执行计划、执行器执行计划。然则有两个历程是完全不一样的：

第一个是查询缓存阶段，SELECT 语句是去缓存查有没有相同 SELECT 语句，并将其效果取出返回给客户端，而 UPDATE 语句是去清空该表的查询缓存。

第二个是执行器阶段，SELECT 语句是将磁盘上的数据取出，而 UPDATE 语句是先查到这些数据，然后举行更新并写入磁盘。

其他包罗毗邻、词法剖析、语法剖析、天生执行计划等历程都是一样的。

更新流程涉及到两个主要的日志模块，binlog(归档日志) 和 redo log(重做日志)

binlog

binlog 纪录了对 MySQL 数据库执行更改的所有操作，然则不包罗 SELECT 和 SHOW 这类操作，由于这类操作对数据自己并没有修改。若操作自己并没有导致数据库发生转变，那么该操作也会写入二进制日志。MySQL 的主从赋值就是依赖 binlog。

redo log

redo log又称重做日志文件，用于纪录事务操作的转变，纪录的是数据修改之后的值，不管事务是否提交都市纪录下来。在实例和介质失败(media failure)时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用 redo log 恢复到掉电前的时刻，以此来保证数据的完整性。

binlog 和 redo log 的区别

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

redo log 是物理日志，纪录的是 “在某个数据页上做了什么修改”。binlog 是逻辑日志，纪录的是这个语句的原始逻辑，好比“给 ID=2 这一行的 c 字段加 1 ”。

redo log 是循环写的，空间牢固会用完。binlog 是可以追加写入的，“追加写”是指 binlog 文件写到一定巨细后会切换到下一个，并不会笼罩以前的日志。

通过以上对 binlog 和 redo log 的形貌，我们再来看看 UPDATE 语句的执行历程：

例如

mysql> update T set c=100 where ID=2;

流程

执行器先挪用引擎接口取 ID=2 这一行

若是 ID 是主键，引擎直接用树搜索找到这一行。

若是 ID=2 这一行所在的数据页原本就在内存中，就直接返回给执行器

否则，需要先从磁盘读入内存，然后再返回。

执行器拿到引擎给的行数据

把这个值改为 100，获得新的一行数据，再挪用引擎接口写入这行新数据。

引擎将这行新数据更新到内存中

同时将这个更新操作纪录到 redo log 内里，此时 redo log 处于 prepare 状态。

然后见告执行器执行完成了，随时可以提交事务。

执行器天生这个操作的 binlog，并把 binlog 写入磁盘。

执行器挪用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交(commit)状态，更新完成。

redo 两阶段提交

凭据上面的先容，可以发现 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是”两阶段提交”。

缘故原由

由于 redo log 和 binlog 是两个自力的逻辑，若是不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者接纳反过来的顺序，会导致数据的不一致泛起。

简单说，redo log 和 binlog 都可以用于示意事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

举例

账本记上 卖一瓶可乐(redo log为 prepare状态)，然后收钱放入钱箱(bin log纪录)然后回过头在账本上打个勾(redo log置为commit)示意一笔买卖竣事。

若是收钱时买卖被打断，回过头来整理此次买卖，发现只有记账没有收钱，则买卖失败，删掉账本上的纪录(回滚)。

若是收了钱后被终止，然后回过头发现账本有纪录(prepare)而且钱箱有本次收入(bin log)，则继续完善账本(commit)，本次买卖有用。

原文链接：https://www.cnblogs.com/L-Test/p/12810076.html

本站声明:网站内容来源于网络,若有侵权,请联系我们,我们将及时处理。