精华内容
下载资源
问答
  • mysql执行过程
    2022-03-30 10:32:41

    一.mysql的逻辑架构图

    在这里插入图片描述
    从图中可以看出mysql主要分为service层和引擎层
    service层:Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核
    心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎
    的功能都在这一层实现,比如存储过程、触发器、视图等。

    不同的引擎共用一个service层,不同的引擎会采用相同的service层,即连接器到执行器的部分。

    引擎层:而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、MyISAM、
    Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开
    始成为了默认存储引擎。
    在mysql5.5.5后,MySQL默认的存储引擎是innoDB,每张表可以使用不同的存储引擎,如何在建表的时候指定存储引擎?
    在create table的末尾使用engine=memory,memory也是一种存储引擎。
    以下对service中的每一个组件进行详细的介绍

    二.service层各个组件

    1.连接器

    第一步,你会先连接到这个数据库上,这时候接待你的就是连接器。连接器负责跟客户端建
    立连接、获取权限、维持和管理连接。连接命令一般是这么写的:

    mysql -h$ip -P$post -u$user -p
    

    注意这里密码可以直接跟在p后,但是不建议这样做,尤其是连接生产服务器时,有泄露密码的风险

    连接命令中的 mysql 是客户端工具,用来跟服务端建立连接。在完成经典的 TCP 握手后,
    连接器就要开始认证你的身份,这个时候用的就是你输入的用户名和密码。

    如果用户名或密码不对,你就会收到一个"Access denied for user"的错误,然后客户端
    程序结束执行

    如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里
    面的权限判断逻辑,都将依赖于此时读到的权限。

    这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改, 也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置

    连接完成后,如果你没有后续的动作,这个连接就处于空闲状态,你可以在 show
    processlist 命令中看到它。文本中这个图是 show processlist 的结果,其中的
    Command 列显示为“Sleep”的这一行,就表示现在系统里面有一个空闲连接

    在这里插入图片描述
    其中可以看到5是一个空闲连接,因为其通过连接后什么事也没做。6就是我们当前的这个客户端,执行了一个show processlist命令,不是空闲接连。

    自动断开连接

    客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数 wait_timeout
    控制的,默认值是 8 小时。
    如果在连接被断开之后,客户端再次发送请求的话,就会收到一个错误提醒: Lost
    connection to MySQL server during query。这时候如果你要继续,就需要重连,然后
    再执行请求了。

    长连接

    数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。
    连接
    则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。

    建立连接的过程通常是比较复杂的(大多数连接都是消耗性能时间的,因为其涉及到了tcp连接等操作),所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。

    但是全部使用长连接后,你可能会发现,有些时候 MySQL 占用内存涨得特别快,这是因
    为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断
    开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉
    (OOM),从现象看就是 MySQL 异常重启了。
    那么如何解决mysql因长连接占用内存长得非常快呢?
    一:定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,
    断开连接,之后要查询再重连(当然这个断开时间要衡量好,不然会退化为断连接)
    二:如果使用是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行
    mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验
    证,但是会将连接恢复到刚刚创建完时的状态。(即不需要通过连接器,优化了因网络连接等消耗的性能)

    这里补充一下,mysql_reset_connection会造成哪些影响

    1. 回滚活跃的事务并重新设置自动提交模式
    2. 释放所有表锁
    3. 关闭或删除所有的临时表
    4. 重新初始化会话的系统变量值
    5. 丢失用户定义的设置变量
    6. 释放prepare语句
    7. 关闭handler变量
    8. 将last_insert_id()值设置为0
    9. 释放get_lock()获取的锁
    10. 清空通过mysql_bind_param()调用定义的当前查询属性

    2.查询缓存(mysql 8.0之前)

    连接建立完成后,你就可以执行 select 语句了。执行逻辑就会来到第二步:查询缓存。
    MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执
    行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中key 是查询的
    语句,value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key,那么这个
    value 就会被
    直接返回给客户端。

    如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询
    缓存中。你可以看到,如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直
    接返回结果,这个效率会很高

    但是大多数情况下我会建议你不要使用查询缓存,为什么呢?因为查询缓存往往弊大于利。

    查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。
    因此很可能你费劲地把结果存起来,还没使用呢,就被一个更新全清空了。对于更新压力大
    的数据库来说,查询缓存的命中率会非常低。除非你的业务就是有一张静态表(即很少进行增删改),很长时间才
    会更新一次。比如,一个系统配置表,那这张表上的查询才适合使用查询缓存。

    好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置
    成 DEMAND,这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓
    存的语句,可以用 SQL_CACHE 显式指定,像下面这个语句一样:

     mysql> select SQL_CACHE * from T where ID=10;
    

    注意:在MySQL 8.0 版本已经移除了查询缓存的功能,不能在使用了(若使用就会报语法错误)。

    3.分析器

    如果没有命中查询缓存,就要开始真正执行语句了。首先,MySQL 需要知道你要做什么,
    因此需要对 SQL 语句做解析。

    词法分析

    分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句,
    MySQL 需要识别出里面的字符串分别是什么,代表什么。
    MySQL 从你输入的"select"这个关键字识别出来,这是一个查询语句。它也要把字符
    串“T”识别成“表名 T”,把字符串“ID”识别成“列 ID”。
    即词法分析阶段分析sql语句中每个词的含义,包括字段等。

    语法分析

    做完了这些识别以后,就要做“语法分析”。根据词法分析的结果,语法分析器会根据语法
    规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。(将这些词语组合在一起是否会出现语法问题)
    如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒,比
    如下面这个语句 select 少打了开头的字母“s”。

    一般语法错误会提示第一个出现错误的位置,所以只需要查看报错位置的前面就可以轻松地找出问题所在。

    4.优化器

    优化器是在表里面有多个索引的时候,决定使用哪个索引(暂不详讲);或者在一个语句有多表关联
    (join)的时候,决定各个表的连接顺序

    5.执行器

    MySQL 通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器
    阶段,开始执行语句。
    开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返
    回没有权限的错误,如下所示 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果
    的时候,做权限验证。查询也会在优化器之前调用 precheck 验证权限)

    mysql> select * from T where ID=10;
    ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
    

    如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这
    个引擎提供的接口

    比如我们这个例子中的表 T 中,ID 字段没有索引,那么执行器的执行流程是这样的:

    1. 调用 InnoDB 引擎接口取这个表的第一行,判断 ID 值是不是 10,如果不是则跳过,如
      果是则将这行存在结果集中;
    2. 调用引擎接口取“下一行”,重复相同的判断逻辑,直到取到这个表的最后一行。
    3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
      至此,这个语句就执行完成了。

    对于有索引的表,执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接
    口,之后循环取“满足条件的下一行”这个接口,这些接口都是引擎中已经定义好的。
    你会在数据库的慢查询日志中看到一个 rows_examined 的字段,表示这个语句执行过程中
    扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。
    在有些场景下,执行器调用一次,在引擎内部则扫描了多行,因此引擎扫描行数跟
    rows_examined 并不是完全相同的。

    小问:
    语句 select * from T where k=1,报“不存在这个列”的错误: “Unknown column ‘k’ in
    ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢?

    答案:
    分析器的词法分析阶段


    本文章参考极客时间mysql之45讲笔记。

    更多相关内容
  • Mysql执行过程与BufferPool缓存机制.png
  • MySQL执行流程

    千次阅读 多人点赞 2020-04-21 17:59:51
    一条SQL语句是怎么执行的? 1.1 连接 首先,数据是存储在MySQL服务端的,应用程序或者工具都是客户端,客户端想要读写数据,第一步得跟服务端建立连接。 1.2 查询缓存 MySQL内部自带了一个缓存模块,但是MySQL的...

    1. 一条SQL语句是怎么执行的?

    在这里插入图片描述

    1.1 连接

    首先,数据是存储在MySQL服务端的,应用程序或者工具都是客户端,客户端想要读写数据,第一步得跟服务端建立连接。

    1.2 查询缓存

    MySQL内部自带了一个缓存模块,但是MySQL的缓存默认是关闭的,因为MySQL自带的缓存的应用场景有限,第一个是它要求SQL语句必须一模一样,例如中间多一个空格、字母大小写不同都被认为是不一样的SQL语句。
    第二个是表里面的任何一条数据发生变化的时候,这张表所有的缓存都会失效,所以对于大量数据更新的应用,也不适合。
    缓存这一块还是交给ORM框架,或者独立的缓存服务,比如Redis来处理更合适。
    在MySQL8.0中,查询缓存已经被移除了。
    可以使用这行语句来查看MySQL查询缓存的相关属性

    show variables like 'query_cache%'; 
    

    1.3 语法解析和预处理

    假如随便执行一个字符串,服务器报了一个1064的错:
    [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the rightsyntaxtousenear’penyuyan’atline1 它是怎么知道输入的内容是错误的?
    这个就是MySQL的Parser解析器和Preprocessor预处理模块。
    这一步主要做的事情是对SQL语句进行词法和语法分析和语义的解析

    1.3.1.词法解析

    词法分析就是把一个完整的SQL语句打碎成一个个的单词。
    比如一个简单的SQL语句:

    select name from user where id=1
    

    它会打碎成8个符号,每个符号是什么类型,从哪里开始到哪里结束。

    1.3.2.语法解析

    第二步就是语法分析,语法分析会对SQL做一些语法检查,比如单引号有没有闭合,
    然后根据MySQL定义的语法规则,根据SQL语句生成一个数据结构。
    这个数据结构我们把它叫做解析树(select_lex)。
    在这里插入图片描述
    词法语法分析是一个非常基础的功能,编译器、搜索引擎如果要识别语句,必须也要有词法语法分析功能。

    1.3.3 预处理器

    如果写了一个词法和语法都正确的SQL,但是表名或者字段不存在,会在哪里报错?
    是解析的时候报错还是执行的时候报错?
    实际上还是在解析的时候报错,解析SQL的环节里面有个预处理器。
    它会检查生成的解析树,解决解析器无法解析的语义。比如,它会检查表和列名是否存在,检查名字和别名,保证没有歧义。
    预处理之后得到一个新的解析树。

    1.4. 查询优化(Query Optimizer)与查询执行计划

    1.4.1 什么是优化器?

    解析树是一个可以被执行器认识的数据结构。
    一条SQL语句是不是只有一种执行方式?或者说数据库最终执行的SQL是不是就是发送的SQL?
    这个答案是否定的。一条SQL语句是可以有很多种执行方式的,最终返回相同的结果,他们是等价的。但是如果有这么多种执行方式,这些执行方式怎么得到的?最终选择哪一种去执行?根据什么判断标准去选择?
    这个就是MySQL的查询优化器的模块(Optimizer)。
    查询优化器的目的就是根据解析树生成不同的执行计划(ExecutionPlan),然后选择一种最优的执行计划,MySQL里面使用的是基于开销(cost)的优化器,那种执行计划开销最小,就用哪种。
    可以使用这个命令查看查询的开销:

    show status like 'Last_query_cost';
    

    1.4.2 优化器可以做什么?
    MySQL的优化器能处理哪些优化类型呢?
    举两个简单的例子:
    1、当我们对多张表进行关联查询的时候,以哪个表的数据作为基准表(先访问哪张表)。
    2、有多个索引可以使用的时候,选择哪个索引。
    3、对于查询条件的优化,比如移除1=1 之类的恒等式,移除不必要的括号,表达式的计算,子查 询和连接查询的优化。

    1.4.3. 优化器得到的结果

    优化器最终会把解析树变成一个执行计划(execution_plans),执行计划也是一个数据结构。
    当然,这个执行计划是不是一定是最优的执行计划呢?不一定,因为MySQL也有可能覆盖不到所有的执行计划。
    我们怎么查看MySQL的执行计划呢?比如多张表关联查询,先查询哪张表?在执行查询的时候可能用到哪些索引,实际上用到了什么索引?
    MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN,就可以看到执行计划的信息。

    EXPLAIN select cust_name from customer where cust_id=14;
    

    如果要得到详细的信息,还可以用FORMAT=JSON。

    1.5 存储引擎

    1.5.1 存储引擎基本介绍

    表在存储数据的同时,还要组织数据的存储结构,这个存储结构就是由我们的存储引擎决定的,所以我们也可以把存储引擎叫做表类型。
    在MySQL里面,支持多种存储引擎,他们是可以替换的,所以叫做插件式的存储引擎。

    1.5.2 查看存储引擎

    查看数据库里面已经存在的表的存储引擎:

    show table status from `ssm_crm`;
    

    在MySQL里面,我们创建的每一张表都可以指定它的存储引擎,而不是一个数据库只能使用一个存储引擎。存储引擎的使用是以表为单位的(所以叫表类型)。而且,创建表之后还可以修改存储引擎。
    默认情况下,每个数据库都有一个自己的文件夹,存储在服务器端,可以通过下面这条语句查看

    show variables like 'datadir';
    

    任何一个存储引擎都有一个frm文件,这个是表结构定义文件。
    不同的存储引擎存放数据的方式不一样,产生的文件也不一样,innodb 是 1 个,
    memory没有,myisam是两个。

    1.5.3 存储引擎比较

    我们可以用这个命令查看MySQL对存储引擎的支持情况:

    show engines;
    

    其中有存储引擎的描述和对事务、XA协议和savepoints的支持。
    XA协议用来实现分布式事务(分为本地资源管理器,事务管理器)。
    Savepoints用来实现子事务(嵌套事务)。创建了一个Savepoints之后,事务就可以回滚到这个点,不会影响到创建Savepoints之前的操作。
    在这里插入图片描述
    MyISAM(3个文件)

    MySQL自带的存储引擎,由ISAM升级而来。
    应用范围比较小。表级锁定限制了读/写的性能,因此在Web 和数据仓库配置中,它通常用于只读或以读为主的工作。
    特点:
    支持表级别的锁(插入和更新会锁表)。不支持事务。
    拥有较高的插入(insert)和查询(select)速度。
    存储了表的行数(count速度更快)。
    (怎么快速向数据库插入100万条数据?我们有一种先用MyISAM插入数据,然后
    修改存储引擎为InnoDB的操作。)
    适合:只读之类的数据分析的项目。

    InnoDB(2个文件)

    mysql5.7中的默认存储引擎。 InnoDB是一个事务安全(与ACID兼容)的MySQL存储引擎,它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁(不升级为更粗粒度的锁)和Oracle风格的一致非锁读提高了多用户并发性和性能。InnoDB将用户数据存储在聚集索引中,以减少基于主键的常见查询的I/O。为了保持数据完整性,InnoDB还支持外键引用完整性约束。

    特点:

    • 支持事务,支持外键,因此数据的完整性、一致性更高。
    • 支持行级别的锁和表级别的锁。
    • 支持读写并发,写不阻塞读(MVCC)。
    • 特殊的索引存放方式,可以减少IO,提升查询效率。
    • 适合:经常更新的表,存在并发读写或者有事务处理的业务系统。

    Memory(1 个文件)

    将所有数据存储在RAM中,以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少; InnoDB及其缓冲池内存区域提供了一种通用、持久的方法来将大部分或所有数据保存在内存中,而ndbcluster为大型分布式数据集提供了快速的键值查找。

    特点:

    • 把数据放在内存里面,读写的速度很快,但是数据库重启或者崩溃,数据会全部消失。只适合做临时表。
    • 将表中的数据存储到内存中。
    • 默认使用哈希索引。

    CSV(3个文件)

    它的表实际上是带有逗号分隔值的文本文件。 csv表允许以csv格式导入或转储数据,以便与读写相同格式的脚本和应用程序交换数据。因为csv 表没有索引,所以通常在正常操作期间将数据保存在innodb表中,并且只在导入或导出阶段使用csv表。
    特点:不允许空行,不支持索引。格式通用,可以直接编辑,适合在不同数据库之
    间导入导出。

    不同的存储引擎提供的特性都不一样,它们有不同的存储机制、索引方式、锁定水平等功能。
    我们在不同的业务场景中对数据操作的要求不同,就可以选择不同的存储引擎来满足我们的需求,这个就是MySQL支持这么多存储引擎的原因。

    1.5.4 如何选择存储引擎

    • 如果对数据一致性要求比较高,需要事务支持,可以选择InnoDB。
    • 如果数据查询多更新少,对查询性能要求比较高,可以选择MyISAM。
    • 如果需要一个用于查询的临时表,可以选择Memory。
    • 如果所有的存储引擎都不能满足你的需求,并且技术能力足够,可以根据官网内部手册用C语言开发一个存储引擎,按照这个开发规范,实现相应的接口,给执行器操作。也就是说,为什么能支持这么多存储引擎,还能自定义存储引擎,表的存储引擎改了对Server访问没有任何影响,就是因为大家都遵循了一定了规范,提供了相同的操作接口。

    1.6. 执行引擎(Execution Engine),返回结果

    执行器,或者叫执行引擎,它利用存储引擎提供的相应的API来完成操作。最后把数据返回给客户端,即使没有结果也要返回。

    总结:一条SQL语句的执行流程大致为,客户端先与MySQL服务器建立连接,然后,发送一条查询语句,如果MySQL开启了缓存的话,会将SQL语句存到缓存中,然后,解析器进行词法和语法的解析,接着预处理器会进行再次检查,比如检查表名和列名是否存在,没有问题的话,优化器会对SQL语句进行优化,生成一个执行计划,交给执行器执行SQL,执行器调用存储引擎,存储引擎读取磁盘数据,将查询结果交给执行器,执行器再将查询结果反馈给客户端或缓存。

    2. 架构分层

    总体上,我们可以把 MySQL 分成两层,执行操作的服务层,和存储管理数据的存储引擎层(参考 MyBatis:接口、核心、基础)
    在这里插入图片描述

    2.1 服务层

    包括客户端跟服务端的连接,查询缓存的判断、对 SQL 语句进行词法和语法的解析(比如关键字怎么识别,别名怎么识别,语法有没有错误等等)。然后就是优化器,MySQL 底层会根据一定的规则对我们的 SQL 语句进行优化,最后交给执行器去执行。

    2.2 存储引擎层

    存储引擎就是我们的数据真正存放的地方,在 MySQL 里面支持不同的存储引擎。再往下就是文件管理系统,内存或者磁盘。

    3. 一条更新 SQL 是如何执行的?

    更新流程和查询流程有什么不同呢?基本流程也是一致的,也就是说,它也要经过解析器、优化器的处理,最后交给执行器。 区别就在于拿到符合条件的数据之后的操作。

    3.1 缓冲池 Buffer Pool

    InnoDB作为MySQL的存储引擎,数据是存放在磁盘中的,但如果每次读写数据都需要磁盘IO,效率会很低。为此,InnoDB提供了缓存(Buffer Pool),作为访问数据库的缓冲:当从数据库读取数据时,会首先从Buffer Pool中读取,如果Buffer Pool中没有,则从磁盘读取后放入Buffer Pool;当向数据库写入数据时,会首先写入Buffer Pool,Buffer Pool中修改的数据会定期刷新到磁盘中 。在这里插入图片描述

    修改数据的时候,先修改内存缓冲池里面的页。内存的数据页和磁盘数据不一致的时候,我们把它叫做脏页。那脏页什么时候同步到磁盘呢?
    InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘,每隔一段时间就一次性地把多个修改写入磁盘,这个动作就叫做刷脏

    3.2 InnoDB 内存结构和磁盘结构

    官方文档
    在这里插入图片描述

    内存结构里面主要是 Buffer Pool、Change Buffer、Log Buffer、AHI,下面分别讲一下。

    Buffer Pool

    官方文档

    Buffer Pool 缓存的是 page 页面信息。
    查看服务器状态,里面有很多跟 Buffer Pool 相关的信息,这些状态都可以在官网查到详细的含义。

    SHOW STATUS LIKE '%innodb_buffer_pool%';
    

    在这里插入图片描述
    Buffer Pool 默认大小是 128M(134217728 字节),可以调整。
    查看参数(系统变量):

    SHOW VARIABLES like '%innodb_buffer_pool%';
    

    (redo)Log Buffer

    Buffer Pool的使用大大提高了读写数据的效率,但是也带了新的问题:如果MySQL宕机,而此时Buffer Pool中修改的数据还没有刷新到磁盘,就会导致数据的丢失,事务的持久性无法保证。为了避免这个问题,InnoDB 把所有对页面的修改操作专门写入一个日志文件Redo Log。如果有未同步到磁盘的数据,数据库在启动的时候,会从这个日志文件进行恢复操作(实现 crash-safe)。我们说的事务的 ACID 里面 D(持久性),就是用它来实现的。
    在这里插入图片描述

    这个日志文件就是磁盘的 redo log(叫做重做日志),对应于/var/lib/mysql/目录下的 ib_logfile0 和 ib_logfile1,默认 2 个文件,每个 48M,可以使用如下命令查看

    show variables like 'innodb_log%';
    
    参数含义
    innodb_log_file_size指定每个文件的大小,默认 48M
    innodb_log_files_in_group指定文件的数量,默认为 2
    innodb_log_group_home_dir指定文件所在路径,相对或绝对。如果不指定,则为datadir 路径。

    3.3 redo log如何保证事务的持久性?

    Redo log可以简单分为以下两个部分:

    • 一是内存中重做日志缓冲 (redo log buffer),是易失的,在内存中
    • 二是重做日志文件 (redo log file),是持久的,保存在磁盘中

    这里再细说下写入Redo Log的时机:

    • 在数据页修改完成之后,在脏页刷出磁盘之前,写入redo日志。注意的是先修改数据,后写日志
    • redo日志比数据页先写回磁盘
    • 聚集索引、二级索引、undo页面的修改,均需要记录Redo日志。

    在这里插入图片描述
    在 MySQL中,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。为了解决这个问题,MySQL 的设计者就采用了日志(redo log)来提升更新效率。

    当事务提交时,先将 redo log buffer 写入到 redo log file 进行持久化,待事务的commit操作完成时才算完成。这种做法也被称为 Write-Ahead Log(预先日志持久化),在将数据写入磁盘前,先将内存中相应的日志页持久化。

    具体来说,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log(redo log buffer)里面,并更新内存(buffer pool),这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候(如系统空闲时),将这个操作记录更新到磁盘里面(刷脏页)。

    3.4 同样是写磁盘,为什么不直接写到 db file 里面去?为什么先写日志再写磁盘?

    如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中,那么找到相应的数据需要等到磁臂旋转到指定的页,然后盘片寻找到对应的扇区,才能找到我们所需要的一块数据,一次进行此过程直到找完所有数据,这个就是随机 IO,读取数据速度较慢。

    假设我们已经找到了第一块数据,并且其他所需的数据就在这一块数据后边,那么就不需要重新寻址,可以依次拿到我们所需的数据,这个就叫顺序 IO。

    刷盘是随机 I/O,而记录日志是顺序 I/O(连续写的),顺序 I/O 效率更高。因此先把修改写入日志文件,在保证了内存数据的安全性的情况下,可以延迟刷盘时机,进而提升系统吞吐。

    3.5 数据写入后的最终落盘,是从 redo log 更新过来的还是从 buffer pool 更新过来的呢?

    实际上,redo log 并没有记录数据页的完整数据,所以它并没有能力自己去更新磁盘数据页,也就不存在由 redo log 更新过去数据最终落盘的情况。

    ① 数据页被修改以后,跟磁盘的数据页不一致,称为脏页。最终数据落盘,就是把内存中的数据页写盘。这个过程与 redo log 毫无关系。

    ② 在崩溃恢复场景中,InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新,就会将它读到内存,然后让 redo log 更新内存内容。更新完成后,内存页变成脏页,就回到了第一种情况的状态。

    3.6 redo log buffer 是什么?是先修改内存,还是先写 redo log 文件?

    在一个事务的更新过程中,日志是要写多次的。比如下面这个事务:

    begin;
    INSERT INTO T1 VALUES ('1', '1');
    INSERT INTO T2 VALUES ('1', '1');
    commit;
    
    

    这个事务要往两个表中插入记录,插入数据的过程中,生成的日志都得先保存起来,但又不能在还没 commit 的时候就直接写到 redo log 文件里。

    因此就需要 redo log buffer 出场了,它就是一块内存,用来先存 redo 日志的。也就是说,在执行第一个 insert 的时候,数据的内存被修改了,redo log buffer 也写入了日志。

    但是,真正把日志写到 redo log 文件,是在执行 commit 语句的时候做的。

    redo log buffer 本质上只是一个 byte 数组,但是为了维护这个 buffer 还需要设置很多其他的 meta data,这些 meta data 全部封装在 log_t 结构体中。

    3.7 redo log顺序写入磁盘?

    redo log以顺序的方式写入文件,当全部文件写满的时候则回到第一个文件相应的起始位置进行覆盖写,每次提交事务之后,都先将相关的操作日志写入redo日志文件中,并且都追加到文件末尾,这是一个顺序I/O。
    在这里插入图片描述
    图中展示了一组 4 个文件的 redo log 日志,checkpoint 是当前要擦除的位置,擦除记录前需要先把对应的数据落盘(更新内存页,等待刷脏页)。write pos 到 checkpoint 之间的部分可以用来记录新的操作,如果 write pos 和 checkpoint 相遇,说明 redolog 已满,这个时候数据库停止进行数据库更新语句的执行,转而进行 redo log 日志同步到磁盘中。checkpoint 到 write pos 之间的部分等待落盘(先更新内存页,然后等待刷脏页)。

    有了 redo log 日志,那么在数据库进行异常重启的时候,可以根据 redo log 日志进行恢复,也就达到了 crash-safe。

    redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1,这样可以保证 MySQL 异常重启之后数据不丢失。

    3.8 redo log 有什么特点?

    1. redo log 是 InnoDB 存储引擎实现的,并不是所有存储引擎都有。支持崩溃恢复是 InnoDB 的一个特性。
    2. redo log 是物理日志,记录的是“在某个数据页上做了什么修改”。
    3. redo log 的大小是固定的,前面的内容会被覆盖,一旦写满,就会触发 buffer pool到磁盘的同步,以便腾出空间记录后面的修改。
    4. redo log 的内容主要是用于崩溃恢复。磁盘的数据文件,数据来自 buffer pool(只有 redo log 写满了,不能再记录更多内存的数据了,才把 buffer pool 刷盘,然后覆盖redo log)。

    3.9 undo Log

    undo Log(撤销日志或者回滚日志) 记录了事务发生之前的数据状态(不包括 select)。如果修改数据时出现异常,可以用 undo log 来实现回滚操作**(实现原子性)**。在执行 undo 的时候,仅仅是将数据从逻辑上恢复至事务之前的状态,而不是从物理页面上操作实现的,属于逻辑格式的日志。

    实现原子性的关键,是当事务回滚时能够撤销所有已经成功执行的sql语句。 InnoDB 实现回滚,靠的是undo log :当事务对数据库进行修改时,InnoDB 会生成对应的undo log ,如果事务执行失败或调用了rollback,导致事务需要回滚,便可以利用undo log中的信息将数据回滚到修改之前的样子。

    3.10 bin Log

    前面讲的两个日志是存储引擎(InnoDB)层的日志,而 Server 层也有自己的日志,称为 bin Log(归档日志)。

    binlog 以事件的形式记录了所有的 DDL 和 DML 语句,比如“给 ID=1 这一行的count 字段加 1 ”,因为它记录的是操作而不是数据值,属于逻辑日志)。binlog 可以用来做主从复制和崩溃恢复。
    跟 redo log 不一样,它的文件内容是可以追加的,没有固定大小限制。
    在开启了 binlog 功能的情况下,我们可以把 binlog 导出成 SQL 语句,把所有的操作重放一遍,来实现数据的(归档)恢复。
    binlog 的另一个功能就是用来实现主从复制,它的原理就是从服务器读取主服务器的 binlog,然后执行一遍

    为什么会有两份日志呢?

    因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

    redo Log 和 bin Log的不同点

    1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
    2. redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
    3. redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

    3.11 更新语句流程

    在这里插入图片描述

    有了对这两个日志的概念性理解后,再来看执行器和 InnoDB 引擎在执行这个 update 语句时的内部流程。

    ① 执行器先找引擎取 ID=1 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=1 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。

    ② 执行器拿到引擎给的行数据,修改行数据,得到新的一行数据,再调用引擎接口写入这行新数据。

    ③ 引擎将这行新数据更新到内存(InnoDB Buffer Pool)中,同时将这个更新操作记录到 redo log 里面,此时 redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。

    ④ 执行器生成这个操作的 binlog,并把 binlog 写入磁盘。

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

    其中将 redo log 的写入拆成了两个步骤:prepare 和 commit,这就是两阶段提交(2PC)。

    3.12 为什么必须有“两阶段提交”呢?

    如果不使用两阶段提交,假设当前 ID=2 的行,字段 c 的值是 0,再假设执行 update 语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了 crash,会出现什么情况呢?

    先写 redo log 后写 binlog。 假设在 redo log 写完,binlog 还没有写完的时候,MySQL 进程异常重启。由于我们前面说过的,redo log 写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复后这一行 c 的值是 1。
    但是由于 binlog 没写完就 crash 了,这时候 binlog 里面就没有记录这个语句。因此,之后备份日志的时候,存起来的 binlog 里面就没有这条语句。
    然后你会发现,如果需要用这个 binlog 来恢复临时库的话,由于这个语句的 binlog 丢失,这个临时库就会少了这一次更新,恢复出来的这一行 c 的值就是 0,与原库的值不同。

    先写 binlog 后写 redo log。 如果在 binlog 写完之后 crash,由于 redo log 还没写,崩溃恢复以后这个事务无效,所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以,在之后用 binlog 来恢复的时候就多了一个事务出来,恢复出来的这一行 c 的值就是 1,与原库的值不同。

    可以看到,如果不使用“两阶段提交”,那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致

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

    3.13 两阶段提交原理

    两阶段提交原理描述:

    ① redo log 写盘,InnoDB 事务进入 prepare 状态。

    ② 如果前面 prepare 成功,binlog 写盘,那么再继续将事务日志持久化到 binlog,如果持久化成功,那么 InnoDB 事务则进入 commit 状态

    redo log 和 binlog 有一个共同的数据字段,叫 XID。崩溃恢复的时候,会按顺序扫描 redo log:

    ① 如果碰到既有 prepare、又有 commit 的 redo log,就直接提交;

    ② 如果碰到只有 parepare、而没有 commit 的 redo log,就拿着 XID 去 binlog 找对应的事务。binlog无记录(不完整),回滚事务,binlog有记录(完整),提交事务。

    推荐阅读 count(*)实现原理+两阶段提交总结

    如有不对或不足之处,欢迎指正,谢谢!

    展开全文
  • mysql执行过程以及顺序 前言:mysql在我们的开发中基本每天都要面对的,作为开发中的数据的来源,mysql承担者存储数据和读写数据的职责。因为学习和了解mysql是至关重要的,那么当我们在客户端发起一个sql到出现详细的...

    mysql执行过程以及顺序

    前言:mysql在我们的开发中基本每天都要面对的,作为开发中的数据的来源,mysql承担者存储数据和读写数据的职责。因为学习和了解mysql是至关重要的,那么当我们在客户端发起一个sql到出现详细的查询数据,这其中究竟经历了什么样的过程?mysql服务端是如何处理请求的,又是如何执行sql语句的?本篇博客将来探讨这个问题:

    本篇博客的目录

    一:mysql执行过程

    二:mysql执行过程中的状态

    三:mysql执行的顺序

    四:总结

     一:mysql执行过程

    mysql整体的执行过程如下图所示:

     1.1:连接器

    连接器的主要职责就是:

    ①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行,其中mysql在与客户端连接TC/IP的

    ②验证请求用户的账户和密码是否正确,如果账户和密码错误,会报错:Access denied for user 'root'@'localhost' (using password: YES)

    ③如果用户的账户和密码验证通过,会在mysql自带的权限表中查询当前用户的权限:

    mysql中存在4个控制权限的表,分别为user表,db表,tables_priv表,columns_priv表,mysql权限表的验证过程为:

    1:User表:存放用户账户信息以及全局级别(所有数据库)权限,决定了来自哪些主机的哪些用户可以访问数据库实例
      Db表:存放数据库级别的权限,决定了来自哪些主机的哪些用户可以访问此数据库 
     Tables_priv表:存放表级别的权限,决定了来自哪些主机的哪些用户可以访问数据库的这个表 
     Columns_priv表:存放列级别的权限,决定了来自哪些主机的哪些用户可以访问数据库表的这个字段 
      Procs_priv表:存放存储过程和函数级别的权限

    2:先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在,存在则通过验证。

    3:通过身份认证后,进行权限分配,按照user,db,tables_priv,columns_priv的顺序进行验证。即先检查全局权限表user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y,将不再检查db, tables_priv,columns_priv;如果为N,则到db表中检查此用户对应的具体数据库,并得到db中为Y的权限;如果db中为N,则检查tables_priv中此数据库对应的具体表,取得表中的权限Y,以此类推

    4:如果在任何一个过程中权限验证不通过,都会报错

    1.2:缓存

        mysql的缓存主要的作用是为了提升查询的效率,缓存以key和value的哈希表形式存储,key是具体的sql语句,value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端 。不过需要注意的是在mysql的8.0版本以后,缓存被官方删除掉了。之所以删除掉,是因为查询缓存的失效非常频繁,如果在一个写多读少的环境中,缓存会频繁的新增和失效。对于某些更新压力大的数据库来说,查询缓存的命中率会非常低,mysql为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了,比较推荐的一种做法是将缓存放在客户端,性能大概会提升5倍左右

    1.3:分析器

       分析器的主要作用是将客户端发过来的sql语句进行分析,这将包括预处理与解析过程,在这个阶段会解析sql语句的语义,并进行关键词和非关键词进行提取、解析,并组成一个解析树。具体的关键词包括不限定于以下:select/update/delete/or/in/where/group by/having/count/limit等.如果分析到语法错误,会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.

    比如:select *  from user where userId =1234;

    在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,mysql会自动判断关键词和非关键词,将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表,同时假如User表中不存在userId这个字段同样会报错:unknown column in field list.

    1.4:优化器

    能够进入到优化器阶段表示sql是符合mysql的标准语义规则的并且可以执行的,此阶段主要是进行sql语句的优化,会根据执行计划进行最优的选择,匹配合适的索引,选择最佳的执行方案。比如一个典型的例子是这样的:

    表T,对A、B、C列建立联合索引,在进行查询的时候,当sql查询到的结果是:select xx where  B=x and A=x and C=x.很多人会以为是用不到索引的,但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:where A=x and B=x and C=X,这种优化会为了底层能够匹配到索引,同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器

    1.5:执行器

     在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎,主要有一下存储的引擎,不过常用的还是myisam和innodb:

     引擎以前的名字叫做:表处理器(其实这个名字我觉得更能表达它存在的意义)负责对具体的数据文件进行操作,对sql的语义比如select或者update进行分析,执行具体的操作。在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式,记录都redolog中

    二:执行的状态

    可以通过命令:show full processlist,展示所有的处理进程,主要包含了以下的状态,表示服务器处理客户端的状态,状态包含了从客户端发起请求到后台服务器处理的过程,包括加锁的过程、统计存储引擎的信息,排序数据、搜索中间表、发送数据等。囊括了所有的mysql的所有状态,其中具体的含义如下图:

     三:sql的执行顺序

     事实上,sql并不是按照我们的书写顺序来从前往后、左往右依次执行的,它是按照固定的顺序解析的,主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用,sql在执行的过程中会有不同的临时中间表,一般是按照如下顺序:

    例子: select distinct s.id  from T t join  S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2  order by s.create_time limit 5;

    3.1:from

    第一步就是选择出from关键词后面跟的表,这也是sql执行的第一步:表示要从数据库中执行哪张表。

    实例说明:在这个例子中就是首先从数据库中找到表T

    3.2:join on

    join是表示要关联的表,on是连接的条件。通过from和join on选择出需要执行的数据库表T和S,产生笛卡尔积,生成T和S合并的临时中间表Temp1。on:确定表的绑定关系,通过on产生临时中间表Temp2.

    实例说明:找到表S,生成临时中间表Temp1,然后找到表T的id和S的id相同的部分组成成表Temp2,Temp2里面包含着T和Sid相等的所有数据

    3.3:where

    where表示筛选,根据where后面的条件进行过滤,按照指定的字段的值(如果有and连接符会进行联合筛选)从临时中间表Temp2中筛选需要的数据,注意如果在此阶段找不到数据,会直接返回客户端,不会往下进行.这个过程会生成一个临时中间表Temp3。注意在where中不可以使用聚合函数,聚合函数主要是(min\max\count\sum等函数)

    实例说明:在temp2临时表集合中找到T表的name="Yrion"的数据,找到数据后会成临时中间表Temp3,temp3里包含name列为"Yrion"的所有表数据

    3.4:group by 

    group by是进行分组,对where条件过滤后的临时表Temp3按照固定的字段进行分组,产生临时中间表Temp4,这个过程只是数据的顺序发生改变,而数据总量不会变化,表中的数据以组的形式存在

    实例说明:在temp3表数据中对mobile进行分组,查找出mobile一样的数据,然后放到一起,产生temp4临时表。

    3.5:Having

    对临时中间表Temp4进行聚合,这里可以为count等计数,然后产生中间表Temp5,在此阶段可以使用select中的别名

    实例说明:在temp4临时表中找出条数大于2的数据,如果小于2直接被舍弃掉,然后生成临时中间表temp5

    3.6:select

    对分组聚合完的表挑选出需要查询的数据,如果为*会解析为所有数据,此时会产生中间表Temp6

    实例说明:在此阶段就是对temp5临时聚合表中S表中的id进行筛选产生Temp6,此时temp6就只包含有s表的id列数据,并且name="Yrion",通过mobile分组数量大于2的数据

    3.7:Distinct

    distinct对所有的数据进行去重,此时如果有min、max函数会执行字段函数计算,然后产生临时表Temp7

    实例说明:此阶段对temp5中的数据进行去重,引擎API会调用去重函数进行数据的过滤,最终只保留id第一次出现的那条数据,然后产生临时中间表temp7

    3.8:order by 

    会根据Temp7进行顺序排列或者逆序排列,然后插入临时中间表Temp8,这个过程比较耗费资源

    实例说明:这段会将所有temp7临时表中的数据按照创建时间(create_time)进行排序,这个过程也不会有列或者行损失

     3.9:limit

    limit对中间表Temp8进行分页,产生临时中间表Temp9,返回给客户端。

    实例说明:在temp7中排好序的数据,然后取前五条插入到Temp9这个临时表中,最终返回给客户端

    ps:实际上这个过程也并不是绝对这样的,中间mysql会有部分的优化以达到最佳的优化效果,比如在select筛选出找到的数据集

     四:总结

     本篇博客总结了mysql的执行过程,以及sql的执行顺序,理解这些有助于我们对sql语句进行优化,以及明白mysql中的sql语句从写出来到最终执行的轨迹,有助于我们对sql有比较深入和细致的理解,提高我们的数据库理解能力。同时,对于复杂sql的执行过程、编写都会有一定程度的意义。

    展开全文
  • MySQL执行过程及执行顺序

    千次阅读 2022-05-31 17:24:01
    一、MySQL执行过程 MySQL整体的执行过程如下图所示 1 1.1 连接器 1.1.1连接器的主要职责 1、负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送...

    一、MySQL执行过程

    MySQL整体的执行过程如下图所示

    1

    1.1 连接器

    1.1.1连接器的主要职责

    1、负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行,其中MySQL在与客户端连接TC/IP的。

    2、验证请求用户的账户和密码是否正确,如果账户和密码错误,会报错:Access denied for user 'root'@'localhost' (using password: YES)

    3、如果用户的账户和密码验证通过,会在MySQL自带的权限表中查询当前用户的权限。

    MySQL中存在4个控制权限的表,分别为user表,db表,tables_priv表,columns_priv表:

    • user表:存放用户账户信息以及全局级别(所有数据库)权限,决定了来自哪些主机的哪些用户可以访问数据库实例
    • db表:存放数据库级别的权限,决定了来自哪些主机的哪些用户可以访问此数据库
    • tables_priv表:存放表级别的权限,决定了来自哪些主机的哪些用户可以访问数据库的这个表
    • columns_priv表:存放列级别的权限,决定了来自哪些主机的哪些用户可以访问数据库表的这个字段

    1.1.2 MySQL权限表的验证过程

    1、 先从user表中的Host,User,Password这3个字段中判断连接的IP、用户名、密码是否存在,存在则通过验证。

    2、通过身份认证后,进行权限分配,按照user,db,tables_priv,columns_priv的顺序进行验证。即先检查全局权限表user,如果user中对应的权限为Y,则此用户对所有数据库的权限都为Y,将不再检查db,tables_priv,columns_priv;如果为N,则到db表中检查此用户对应的具体数据库,并得到db中为Y的权限;如果db中为N,则检查tables_priv中此数据库对应的具体表,取得表中的权限Y,以此类推。

    3、如果在任何一个过程中权限验证不通过,都会报错。

    1.2 缓存

    MySQL的缓存主要的作用是为了提升查询的效率,缓存以key和value的哈希表形式存储,key是具体的SQL语句,value是结果的集合。如果无法命中缓存,就继续走到分析器的这一步,如果命中缓存就直接返回给客户端。不过需要注意的是在MySQL的8.0版本以后,缓存被官方删除掉了。之所以删除掉,是因为查询缓存的失效非常频繁,如果在一个写多读少的环境中,缓存会频繁的新增和失效。对于某些更新压力大的数据库来说,查询缓存的命中率会非常低,MySQL为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了,比较推荐的一种做法是将缓存放在客户端,性能大概会提升5倍左右。

    1.3 分析器

    分析器的主要作用是将客户端发过来的SQL语句进行分析,这将包括预处理与解析过程,在这个阶段会解析SQL语句的语义,并进行关键词和非关键词进行提取、解析,并组成一个解析树。具体的关键词包括不限定于以下:select/update/delete/or/in/where/group by/having/count/limit等。如果分析到语法错误,会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.

    比如:select * from user where userId =1234;

    在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,MySQL会自动判断关键词和非关键词,将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表,同时假如user表中不存在userId这个字段同样会报错:unknown column in field list.

    1.4 优化器

    能够进入到优化器阶段表示SQL是符合MySQL的标准语义规则的并且可以执行的,此阶段主要是进行SQL语句的优化,会根据执行计划进行最优的选择,匹配合适的索引,选择最佳的执行方案。比如一个典型的例子是这样的:

    表T,对A、B、C列建立联合索引,在进行查询的时候,当SQL查询到的结果是:select xx where B=x and A=x and C=x,很多人会以为是用不到索引的,但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条SQL优化为:where A=x and B=x and C=X,这种优化会为了底层能够匹配到索引,同时在这个阶段是自动按照执行计划进行预处理,MySQL会计算各个执行方法的最佳时间,最终确定一条执行的SQL交给最后的执行器。

    1.5 执行器

    在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎,主要有以下存储的引擎,不过常用的还是myisam和innodb:

    2

    引擎以前的名字叫做:表处理器(其实这个名字我觉得更能表达它存在的意义)负责对具体的数据文件进行操作,对SQL的语义比如select或者update进行分析,执行具体的操作。在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式,记录都redolog中。

    二、执行的状态

    可以通过命令:show full processlist,展示所有的处理进程,主要包含了以下的状态,表示服务器处理客户端的状态,状态包含了从客户端发起请求到后台服务器处理的过程,包括加锁的过程、统计存储引擎的信息,排序数据、搜索中间表、发送数据等。囊括了所有的MySQL的所有状态,其中具体的含义如下图:

    3

    三、SQL的执行顺序

    事实上,SQL并不是按照我们的书写顺序来从前往后、左往右依次执行的,它是按照固定的顺序解析的,主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用,SQL在执行的过程中会有不同的临时中间表,一般是按照如下顺序:

    4

    5

    例子:select distinct s.id from T t join S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2 order by s.create_time limit 5;

    这里有几个需要注意的地方:

    1、SQL语句是从FROM开始执行的,而不是SELECT。MySQL在执行SQL查询语句的时,首先是将数据从硬盘加载到数据缓冲区中,以便对这些数据进行操作。

    2、SELECT是在FROM和GROUP BY 之后执行的。这就导致了无法在WHERE中使用SELECT中设置字段的别名作为查询条件。

    3、UNION是排在ORDER BY之前的。虽然数据库允许SQL语句对UNION段中的子查询或者派生表进行排序,但是这并不能说明在 UNION 操作过后仍保持排序后的顺序。

    4、在MySQL中SQL的逻辑查询是根据上述进行查询,但MySQL可能并不完全会按照逻辑查询处理方式进行查询。MySQL有2个组件:1),分析SQL语句的Parser;2)、优化器Optimizer;MySQL在执行查询之前,都会选择一条自认为最优的查询方案去执行,获取查询结果。一般情况下都能计算出最优的查询方案,但在某些情况下,MySQL给出的查询方案并不是很好的查询方案。

    5、存在索引时,优化器优先使用索引的插叙条件,当索引为多个时,优化器会直接选择效率最高的索引去执行。

    四、SQL语句where后条件执行先后顺序

    4.1 结论

    • 针对MySQL,其条件执行顺序是 从左往右,自上而下。

    • 针对Orcale,其条件执行顺序是从右往左,自下而上。

    4.2 MySQL

    MySQL where执行顺序是从左往右执行的,在数据量小的时候不用考虑,但数据量多的时候要考虑条件的先后顺序,此时应遵守一个原则:排除越多的条件放在第一个。

    在用MySQL查询数据库的时候,连接了很多个过滤条件,发现非常慢。例如:select… where p.languages_id=1 and t.type=1 and p.products_id in(472,474),,这样查询需要20多秒,虽然在各个字段上都建立了索引。用分析Explain SQL一分析,发现在第一次分析过程中就返回了几万条数据:where d p.languages_id=1 ,然后再依次根据条件缩小范围。

    然后稍微改变一下where字段的位置之后,速度就有了明显地提高:where p.products_id in(472,474) and p.languages_id=1 and t.type=1,这样第一次的过滤条件是p.products_id in(472,474),它返回的结果只有不到10条,接下来还要根据其它的条件来过滤,自然在速度上有了较大的提升。经过实践发现,不要以为where中的字段顺序无所谓,可以随便放在哪,应该尽可能地第一次就过滤掉大部分无用的数据,只返回最小范围的数据。

    大多时候MySQL会自动根据SQL语句做出优化,使用最优的SQL语句进行查询。有时候MySQL无法根据SQL语句做出最优的优化顺序,所以还是要我们自己预判断出哪种过滤是最优,毕竟自己才最懂自己的数据。



    作者:陈琰AC
    链接:https://www.jianshu.com/p/ff8bc0ec6365
     

    “做程序员,圈子和学习最重要”因为有有了圈子可以让你少走弯路,扩宽人脉,扩展思路,学习他人的一些经验及学习方法!同时在这分享一下是一直以来整理的Java后端进阶笔记文档和学习资料免费分享给大家!需要资料的朋友私信我扣【06】
     

    展开全文
  • Mysql 执行流程.doc

    2022-07-09 22:45:38
    Mysql 执行流程.doc
  • sql和mysql执行顺序,发现内部机制是一样的。最大区别是在别名的引用上。 一、sql执行顺序 (1)from (2) on (3) join (4) where (5)group by(开始使用select中的别名,后面的语句中都可以使用) (6) avg,...
  • mysql执行流程

    2019-06-19 13:16:18
  • 主要介绍了MySQL存储过程中实现执行动态SQL语句的方法,实例分析了MySQL中构造及执行动态SQL语句的相关技巧,具有一定参考借鉴价值,需要的朋友可以参考下
  • mysql执行流程

    千次阅读 2019-05-23 11:56:57
    server层包括:连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器...
  • drop PROCEDURE if exists my_procedure; create PROCEDURE my_procedure() BEGIN declare my_sqll varchar(500);... 您可能感兴趣的文章:mysql 存储过程中变量的定义与赋值操作mysql存储过程详解mysq
  • mysql执行查询的流程

    2021-11-11 11:38:33
    流程图如下 1. 客户端发送一个查询到mysql服务器 2. 服务器查询缓存,如何缓存命中,直接返回查询结果,否则执行后续...4. mysql根据优化器生成的执行计划,调用存储引擎的api执行查询、 5. 返回对应的查询结果 ...
  • Mysql 执行一条语句的过程

    万次阅读 多人点赞 2022-01-26 18:07:12
    负责管理客户端的连接,比如mysql -u root -p,就是客户端与连接器之间完成的,连接分为长连接和短连接,建议使用长连接,因为建立连接是相对复杂的一个过程。但是长连接也有优化的空间,即长连接过多,随着执行大的...
  • 一条sql语句在mysql中的执行过程

    千次阅读 2022-03-11 10:03:26
    本文详细的介绍了一条sql语句在mysql执行的全过程,其中详细的介绍了Server层的各个组件的作用以及承担的角色等。
  • 课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程MySQL索引原理详解,MySQL事务原理与事务并发,MySQL性能优化总结与MySQL配置优化。课程安排的学习的教程与对应的学习课件,详细的学习笔以及课程...
  • MySQL数据库:SQL语句的执行过程

    万次阅读 多人点赞 2021-02-22 03:59:20
    我们的系统在和 MySQL 数据库进行通信前,需要先和数据库建立连接,而这个功能就是由MySQL驱动底层帮我们完成的,建立完连接之后,我们只需要发送 SQL 语句就可以执行 CRUD 了。如下图所示: 一次 SQL 请求就会...
  • MySQL事务执行流程?

    2022-02-15 16:12:26
    事务: 一条或者多条SQL语句组成执行单元,要么全部成功,要么全部失败; 操作事务的三个步骤 开启事务:记录回滚点,并通知服务器,将要执行一组操作,要么同时成功、要么同时失败 执行sql语句:执行具体的一条或多...
  • 事务完整流程:1.事务开始2.申请锁资源,对id=2这行数据上排他锁3.将需要修改的data pages读取到innodb_buffer_cache4.记录id=2的数据到undo log5.记录id=2修改后的数据到redo log buffer6.将buffer cach...
  • mysql 执行存储过程

    千次阅读 2022-03-29 21:19:44
    MySQL 5.0 版本开始支持存储过程。 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。 存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可...
  • MySQL中的SQL执行流程

    千次阅读 2022-03-12 13:23:30
    MySQL中的SQL执行流程 MySQL的查询流程 查询缓存:Server如果在查询缓存中发现了这条SQL语句,就会直接将结果返回给客户端;如果没有,如果进入到解析器阶段。需要说明的是,因为查询混窜往往效率不高,所以在...
  • mysql 一条sql执行流程

    2021-01-27 13:37:39
    1.1 select语句执行过程下图是 MySQL 的一个简要架构图,从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图,连接器: 身份认证和...
  • MySQL 执行计划和MySQL执行过程

    千次阅读 2018-07-30 18:13:53
    MYSQL执行流程 客户端连接服务器。 查询缓存。不会直接查询数据库。会从缓存中查看是否存在相同语句的执行计划,如果存在将省略语法检查、语言检查、加锁、权限核对、SQL优化等过程。直接执行SQL语句。(以下为没...
  • 一条SQL语句在MySQL执行过程全解析

    万次阅读 多人点赞 2019-06-23 16:45:41
    一条SQL语句在MySQL执行过程全解析。
  • MySQL执行过程以及顺序

    千次阅读 2019-10-10 14:26:09
    前言:MySQL在我们的开发中基本每天都要面对的,作为开发中的数据中间件,MySQL承担者存储数据和读写数据的职责。因为学习和了解MySQL是至关重要的,那么当我们在客户端...一:MySQL执行过程 二:MySQL执行过程中的状态 三...
  • Mysql查询语句的执行流程
  • 最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在mysql中的执行流程,包括sql的查询在mysql内部会怎么流转,sql语句的更新是怎么完成的。...
  • Mysql中select语句执行流程 1、mysql架构 mysql结构(照片有点模糊,但是不妨碍理解。大家也可自行查找) mysql主要功能模块: 1)连接器(连接/线程处理器):主要负责管理客户端连接,权限验证。 2)查询缓存:负责...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 623,541
精华内容 249,416
关键字:

mysql执行过程

mysql 订阅
友情链接: 6315707.rar