精华内容
下载资源
问答
  • mysql执行流程 千次阅读
    2019-05-23 11:56:57

     

    MySQL作为作为最常用的sql,了解其基础架构是十分必要的。大体来说,MySQL可以分为Server层和存储引擎层两部分。

    server层包括:连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

    而存储引擎负责数据的存储和提取。其架构模式是插件式的,支持InnoDB,MyISAM等多个存储引擎。现在最常用的是InnoDB。

    这次我们重点分析server层,以一条sql为例,来分析他的执行流程。

     

    查询语句:

    select * from T where ID=10;

    连接器:建立连接

    你会先连接到这个数据库上,这时候连接你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

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

    输完命令之后,你就需要在交互对话里面输入密码。在完成经典的TCP握手后,连接器就要开始认证你的身份。

    如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置。

    客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数wait_timeout控制的,默认值是8小时。

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

    建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。

    但是全部使用长连接后,你可能会发现,有些时候MySQL占用内存涨得特别快,这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了。

     

    怎么解决这个问题呢?可以考虑以下两种方案。

    定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。

    如果你用的是MySQL 5.7或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。

     

    查询缓存:

    连接建立完成后,你就可以执行select语句了。执行逻辑就会来到第二步:查询缓存。

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

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

    但是并不建议使用查询缓存,为什么呢?因为查询缓存往往弊大于利。

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

    你可以将参数query_cache_type设置成DEMAND,这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句,可以用SQL_CACHE显式指定

     

    分析器:分析做什么

    分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条SQL语句,MySQL需要识别出里面的字符串分别是什么,代表什么。

    据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个SQL语句是否满足MySQL语法

     

    优化器:决定怎么做

    优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。优化器阶段完成后,这个语句的执行方案就确定下来了,然后进入执行器阶段。

     

    执行器:开始执行任务

    开始执行的时候,要先判断一下你对这个表T有没有执行查询的权限,如果没有,就会返回没有权限的错误,

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

     

    更新语句:

    与查询流程不一样的是,更新流程还涉及两个重要的日志模块:redo log(重做日志)和 binlog(归档日志)。

    在MySQL里有一个问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程IO成本、查找成本都很高。在这里,MySQL使用WAL技术,WAL的全称是Write-Ahead Logging,它的关键点就是先写日志,再写磁盘。

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

     

    有了redo log,InnoDB就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe。

    重要的日志模块:binlog

    前面我们讲过,MySQL整体来看,其实就有两块:一块是Server层,它主要做的是MySQL功能层面的事情;还有一块是引擎层,负责存储相关的具体事宜。上面我们聊到的粉板redo log是InnoDB引擎特有的日志,而Server层也有自己的日志,称为binlog(归档日志)。

     

    我想你肯定会问,为什么会有两份日志呢?

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

     

    这两种日志有以下三点不同。

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

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

    redo log是循环写的,空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

     

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

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

    执行器拿到引擎给的行数据,把这个值加上1,比如原来是N,现在就是N+1,得到新的一行数据,再调用引擎接口写入这行新数据。

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

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

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

    你可能注意到了,最后三步看上去有点“绕”,将redo log的写入拆成了两个步骤:prepare和commit,这就是"两阶段提交"。

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

     

     

    更多相关内容
  • MySQL执行流程

    2020-12-14 12:10:21
    一条SQL语句是怎么执行的? 1.1 连接 首先,数据是存储在MySQL服务端的,应用程序或者工具都是客户端,客户端想要读写数据,第一步得跟服务端建立连接。 1.2 查询缓存 MySQL内部自带了一个缓存模块,但是MySQL的...
  • mysql执行流程

    2019-06-19 13:16:18

     

     

     

     

     

     

    展开全文
  • MySql执行流程

    千次阅读 2018-08-16 11:29:48
    mysql执行一个查询的过程,到底做了些什么:   客户端发送一条查询给服务器; 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。 服务器段进行SQL解析、预处理,在优化...

    mysql执行一个查询的过程,到底做了些什么:

     

    • 客户端发送一条查询给服务器;
    • 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段。
    • 服务器段进行SQL解析、预处理,在优化器生成对应的执行计划;
    • mysql根据优化器生成的执行计划,调用存储引擎的API来执行查询。
    • 将结果返回给客户端。
    •     实际上mysql执行的每一步都比较复杂,具体的过程如下:

      1、mysql客户端和服务器通讯

          mysql客户端和服务器之间的通讯协议是“半双工”的,这意味着,在任何一个时刻,要么由服务器向客户端发送数据,要么由客户端向服务器发送数据,这两个动作不能同时发生。这种协议让mysql通信简单快速,但也限制了mysql。一个明显的限制是,这意味着没办法进行流量限制。一旦一端开始发生消息,另一端要接收完整个消息才能响应他。

          客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求,他能做的事情就只是等待结果了。

          相反的,一般服务器响应给用户的数据通常很多,由多个数据包组成。当服务器开始响应客户端请求时,客户端必须完整的接受整个返回结果,而不是简单的只收取前面几条结果,然后让服务器停止发送数据。

          多数连接mysql的库函数都可以获得全部结果并缓存到内存里,还可以逐行获取所需要的数据。默认一般是获得全部结果并缓存到内存中。mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源,所以接受全部结果并缓存通常可以减少服务器的压力,让查询能够早点结束、早点释放对应的资源。

      2、查询状态

          对于mysql连接,任何时刻都有一个状态,该状态表示了mysql当前正在做什么。使用show full processlist命令查看当前状态。在一个查询生命周期中,状态会变化很多次,下面是这些状态的解释:

    • sleep:线程正在等待客户端发送新的请求;
    • query:线程正在执行查询或者正在将结果发送给客户端;
    • locked:在mysql服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,例如InnoDB的行锁,并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
    • analyzing and statistics:线程正在收集存储引擎的统计信息,并生成查询的执行计划;
    • copying to tmp table:线程在执行查询,并且将其结果集复制到一个临时表中,这种状态一般要么是做group by操作,要么是文件排序操作,或者union操作。如果这个状态后面还有on disk标记,那表示mysql正在将一个内存临时表放到磁盘上。
    • sorting Result:线程正在对结果集进行排序。
    • sending data:线程可能在多个状态间传送数据,或者在生成结果集,或者在想客户端返回数据。
    • 3、查询缓存

          在解析一个查询语句之前,如果查询缓存是打开的,那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同,那也不会匹配缓存结果,这种情况下查询就会进入下一阶段的处理。

          如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前mysql会检查一次用户权限。这仍然是无须解析查询SQL语句的,因为在查询缓存中已经存放了当前 查询需要访问的表信息。如果权限没有问题,mysql会跳过所有其他阶段,直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。

      4、查询优化处理

          查询的生命周期的下一步是将一个SQL转换成一个执行计划,mysql在依照这个执行计划和存储引擎进行交互。这包含多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。

    • 语法解析器和预处理:首先mysql通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询;预处理器则根据一些mysql规则进一步检查解析数是否合法。
    • 查询优化器:当语法树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
    • 执行计划:mysql不会生成查询字节码来执行查询,mysql生成查询的一棵指令树,然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。
    • 5、查询执行引擎

          在解析和优化阶段,mysql将生成查询对应的执行计划,mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构,而不是和很多其他的关系型数据库那样对应的字节码。

          mysql简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成。为了执行查询,mysql只需要重复执行计划中的各个操作,知道完成所有的数据查询。

      6、返回结果给客户端

          查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端,mysql仍然会返回这个查询的一些信息,如该查询影响到的行数。如果查询可以被缓存,那么mysql在这个阶段也会将结果放到查询缓存中。

          mysql将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处:服务器端无须存储太多的结果,也就不会因为返回太多结果而消耗太多的内存;这样处理也让msyql客户端第一时间获得返回的结果。

          结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的包发送,再通过tcp协议进行传输,在tcp传输的过程中,可能对mysql的封包进行缓存然后批量传输。

     

     

     

    展开全文
  • mysql执行流程 要搞清楚mysql执行流程之前,我们需要先搞明白几个重要的组件,把他们搞清楚了,自然就知道mysql执行流程是怎样的了。 一共六个组件,我们一起来看看 连接器:连接mysql服务器,进行权限验证 ...

    我曾踏足山巅,也曾进入低谷,二者都令我受益良多。 -----宝石骑士-塔里克

    你有多了解mysql?

    说到mysql,相信很多人对他都不陌生,尤其是后端开发和DBA,更是熟悉地不能再熟悉了,什么mysql是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,支持插入式存储引擎等等,这些概念也能脱口而出,但是有多少人能正确的说出mysql中一条sql的执行流程是什么?带着这个疑问,进入这篇文章的主题。

    MySQL在过去由于性能高、成本低、可靠性好,已经成为最流行的开源数据库,因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟,它也逐渐用于更多大规模网站和应用,比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

    这是mysql的一些基本资料,这些大家都知道,我也不必介绍太多,我们直接来剖析mysql的执行流程吧。

    mysql的执行流程

    要搞清楚mysql的执行流程之前,我们需要先搞明白几个重要的组件,把他们搞清楚了,自然就知道mysql的执行流程是怎样的了。

    一共六个组件,我们一起来看看

    • 连接器:连接mysql服务器,进行权限验证
    • 缓存:保存上次查询的结果,提高性能
    • 分析器:词法与语法分析
    • 优化器:对你的查询语句做出适当的优化
    • 执行器:操作存储引擎,读写数据
    • 存储引擎:存储数据

    mysql的这几个重要的组件大家应该都清楚了,那你们知道这些组件之间的关系或者说,mysql是如何利用这些组件完成一次普通的查询操作呢?我画一张图来给大家看一下。

    在这里插入图片描述

    所以mysql执行一个查询操作的完整流程为:客户端通过连接器建立连接,这个操作进行权限验证,通过之后会先前往缓存,根据sql作为key去查询,查到直接返回,否者前往分析器,经过分析器对sql语句的分析、解析,得到一个mysql可以理解的语法,随后进入优化器,mysql会根据你查询的条件进行适当的优化,之后在经过执行器,这就真正的开始前往存储引擎查询数据,最后将查询到的数据返回给客户端,顺便写入缓存(不一定)

    下面我们一起来看看每个组件的所负责的具体事项。

    在正文开始之前,我们先创建一张测试表,用户下面demo展示

    CREATE TABLE `sys_user` (
      `id` bigint(20) NOT NULL,
      `username` varchar(255) NOT NULL,
      `age` smallint(6) NOT NULL,
      `sex` tinyint(1) DEFAULT NULL,
      `mail` varchar(128) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    insert into sys_user  values (1,'zhangsan',18,1,'123@163.com');
    

    连接器

    从上面的图中我们可以看出,mysql基本分为两大组件,一个是server层、一个是存储引擎,为什么server和存储引擎会被单独分开呢?看上面的图应该也不难想象,mysql的存储引擎是可以随场景变化的,它可以是MYISAM、InnoDB、也可以是Memory,所以它被设计成了插入式。

    我们现在来看看连接器,它位于server层中,主要用于连接和权限验证,校验通过之后mysql会返回当前登录用户所拥有的权限,用于后续操作。

    怎么进行连接呢?如果是使用的Navicat 这种第三方工具,那么很简单,直接输入指定的参数即可
    在这里插入图片描述

    如果是使用命令连接,那么命令如下:

    mysql -hip地址 -P端口 -u用户名 -p密码
    

    不过不建议把密码直接写在 -p后面,可以写成下面这样:

    mysql -h127.0.0.1 -P3306  -uroot -p
    

    如果连接的是本地服务,ip与端口可以省略,直接

    mysql  -uroot -p
    

    回车之后再输入密码:

    在这里插入图片描述

    密码输入正确之后即可建立连接

    在这里插入图片描述

    连接是通过tcp协议进行连接的,需要通过握手建立连接,完成握手之后,服务器就要开始验证你的身份信息,通过则返回当前用户所拥有的权限信息,否则抛出异常,验证的信息为你输入的用户名、密码、ip等,如果用户名或者密码错误,服务器将会抛出一个错误码,随后断开连接,错误信息如下:

    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
    

    表示你输入的用户名或者密码不正确, ‘root’@'localhost’中的root表示你的用户名,localhost表示当前客户端的ip,也就是当前和服务器进行握手的客户端。

    如果是创建的用户只局限于本地连接,当外部客户端强行连接的时候会发生什么呢?我们先来创建一个 testlocal 用户,只限本地登录

    file

    尝试本地登录

    file

    本地很容易的连接成功,那我们切换到其他IP的客户端之后怎么样呢?请看
    file

    很明显,连接失败,错误的信息与用户名或密码错误基本一致,所以同学们在连接mysql的时候一定要能区分是用户名密码错误还是ip限制,我身边很多同事都踩过这个坑,一直以为是自己密码有问题,改了许多次还是不行,完全没有往ip方向联想。

    身份校验通过之后mysql就会进入到权限表中查询当前登录用户所拥有的权限,如果连接的时候使用了安全套接字(SSL),那么可以使用X.509证书进行认证,那更改的权限在何时才能生效呢?

    权限变更的生效时间

    在 mysql 启动时,所有权限表的内容都会被加载到内存中。

    当服务器监听到授权表发生改变时,已经连接的客户端将会发生如下改变:

    • 表和列权限在客户端下一次请求时生效。
    • 数据库权限改变在下一个USE db_name命令生效。
    • 全局权限的改变和密码改变在下一次客户端连接时生效。

    如果用GRANT、REVOKE或SET PASSWORD对授权表进行修改,服务器会注意到并立即重新将授权表载入内存。

    如果你手动地修改授权表(使用INSERT、UPDATE或DELETE等等),你应该执行mysqladmin flush-privilegesmysqladmin reload告诉服务器再装载授权表,否则你的更改将不会生效,除非你重启服务器。

    如果你直接更改了授权表但忘记重载,重启服务器后你的更改方生效。这样可能让你迷惑为什么你的更改没有什么变化!

    权限表一般在mysql库中。

    user:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。

    db:记录各个帐号在各个数据库上的操作权限。

    table_priv:记录数据表级的操作权限。

    columns_priv:记录数据列级的操作权限。

    连接完成之后如果没有后续的动作,这个时候,mysql会将该链接设置为空闲状态,我们一起来看看我的mysql服务器一共有多少个客户端处于连接状态,输入命令

    sqlshow processlist;
    

    file

    • id:线程的唯一标识。
    • User:启动这个线程的用户。
    • Host:记录了发送请求的客户端IP和端口信息。
    • db:当前执行的命令发生在哪个数据库中,如果没有指定为NULL,比如我刚刚查询的sql:sqlshow processlist 并没有指定数据库,所以展示NULL。
    • Command:此刻线程正在执行的命令。
    • Time:该线程处于当前状态的时间。
    • State:线程状态,与Command对应。
    • Info:记录着线程执行的sql语句,默认展示前100个字符,如果需要查看全部,请执行 show full processlist

    我们一起来看看Command一共有哪些值

    • Binlog Dump

      这是复制源上的线程,用于将二进制日志内容发送到副本。

    • Change user

      线程正在执行更改用户操作。

    • Close stmt

      线程正在关闭准备好的语句。

    • Connect

      副本连接到其源。

    • Connect Out

      副本正在连接到其源。

    • Create DB

      线程正在执行创建数据库操作。

    • Daemon

      该线程在服务器内部,而不是为客户端连接提供服务的线程。

    • Debug

      该线程正在生成调试信息。

    • Delayed insert

      该线程是一个延迟的插入处理程序。

    • Drop DB

      线程正在执行放置数据库操作。

    • Error

    • Execute

      线程正在执行准备好的语句。

    • Fetch

      线程正在从执行准备好的语句中获取结果。

    • Field List

      该线程正在检索表列的信息。

    • Init DB

      线程正在选择默认数据库。

    • Kill

      该线程正在杀死另一个线程。

    • Long Data

      执行准备好的语句的结果是线程正在检索长数据。

    • Ping

      线程正在处理服务器ping请求。

    • Prepare

      该线程正在准备一个准备好的语句。

    • Processlist

      该线程正在生成有关服务器线程的信息。

    • Query

      线程正在执行一条语句。

    • Quit

      线程正在终止。

    • Refresh

      该线程是刷新表,日志或缓存,或者重置状态变量或复制服务器信息。

    • Register Slave

      线程正在注册副本服务器。

    • Reset stmt

      该线程正在重置准备好的语句。

    • Set option

      该线程正在设置或重置客户端语句执行选项。

    • Shutdown

      线程正在关闭服务器。

    • Sleep

      线程正在等待客户端向其发送新语句。

    • Statistics

      线程正在生成服务器状态信息。

    • Time

      没用过。

    有点跑偏了,我们回到主题,若客户端一段时间没有使用则会被置为空闲(Sleep)状态,但如果客户端长时间没有操作,那么服务器就会自动将连接断开,默认时长 8 小时,不过可以通过 wait_timeout 参数设置。

    超过时长,连接被断开之后,客户端发起请求,那么该客户端将会收到一个:Lost connection to MySQL server during query,这个时候就只能通过重新建立连接进行操作。

    连接分为两种类型,一个为长连接,一个为短连接,建立连接之后,客户端发送持续请求,如果一直在同一个连接中,那么这个就是长连接,如果每个请求一个连接,则是短连接,我们知道连接会有用户信息的校验,权限的验证,比较麻烦,所以推荐使用长连接进行操作,但是长连接也不是十全十美,有利肯定就有弊,长连接过多时会导致mysql占用的内存过多,导致内存紧张,极端情况可能导致内存泄漏(OOM),那我们如何解决这个问题呢?

    1. 定时清除长连接。
    2. 通过执行 mysql_reset_connection 来重新初始化连接资源,不过要求mysql的版本在5.7或之上。

    缓存

    连接完成之后,下一步就会进入缓存,mysql会在缓存中检测之前是否执行过这条语句,如果被执行过,那么查询的结果将会以key-value的形式存储在缓存中,这个时候下一次的查询直接命中缓存,直接返回相对应的数据,如果缓存中不存在当前key(sql语句),就会进入下一个阶段 - 分析器。

    mysql判断缓存是否命中的方式很简单,mysql将缓存存放在一个引用表中,通过hash值方式应用,hash值包括:查询的sql、查询的数据库、客户端协议版本等等,mysql在判断是否命中缓存的时候不会提前解析sql的语法,而是直接使用sql语句和客户端的基本信息(协议)等等,进行hash算法,这里需要特别注意:在编写sql的时候,需要与上一次执行的sql保持完全一致,空格、注释、编码或者有其他的任何不同的地方都会导致hash出来的结果不同,从而无法命中缓存,所以在操作时需要保持一个统一的编码规范。

    除了这个还有很多情况也会导致查询的数据无法缓存,比如聚合函数、自定义函数、存储过、用户变量、临时表、mysql库中的系统表、权限表

    mysql的缓存虽然能提升查询的性能,但是也会在其他方面造成额外的消耗,具体如下:

    1. 查询之前必须先检查是否命中缓存,对于缓存中没有的sql多了一次缓存的查询。
    2. 第一次查询或者表中的数据被修改时,当前查询需要将结果写入到缓存中,带来了额外的系统消耗。
    3. mysql在写操作时会将关于当前相关缓存的数据全部清空,如果缓存的数据过大,或者缓存的sql语句过多,可能会导致很大的系统消耗。

    所以,缓存的好处可以提升查询的效率,弊端可能给系统带来额外的系统消耗,尤其是在InnoDB中的事务中,所以在使用的时候需要慎重,不可为了查询效率二盲目的使用缓存,使用不当,可能适得其反。

    那mysql如何开启缓存呢?只需要将参数 query_cache_type 设置成 DEMAND 即可,这样会导致整个mysql都是使用缓存,很明显,这是不被推荐的,所以还有一种方式,那就是按需指定,什么叫按需指定呢?就是在你需要缓存的sql语句加上 SQL_CACHE 指定使用缓存即可,代码如下:

    select SQL_CACHE * from sys_user where id = 1;
    

    在这里有两点需要特别注意哦,一:mysql在8.0版本直接将缓存模块删除,也就是说,mysql8.0所有的查询都不会走向缓存了,而是直接前往磁盘;二:查询缓存的返回直接也会校验权限信息的,如果没有权限,就算使用了缓存,也无法查询。

    分析(解析)器

    mysql在缓存中没有命中之后将会进入流程的下一步,但这里并不会直接进入解析器,而是需要先将查询的sql转换成一个执行计划,在经过这个执行计划和存储引擎进行交互,这里就包括了**:解析器、预处理、优化器、执行器**。

    生成完执行计划之后,mysql会对sql的关键字进行解析,生成一棵对应的 “解析树”,在这个解析过程中,mysql解析器会使用语法规则对sql进行解析和校验,第一步做的时词法分析,mysql执行的并不是你写的sql语句,而是将你写的sql语句解析成mysql可以执行的语句。

    生成“解析树“之前还需要校验你的sql语句写的是否有问题,是否满足mysql的语法,如果你输入的sql语句存在问题,这个时候程序将会抛出异常,结束查询。

    select * rom sys_user here id = 1;
    

    我们故意将 from 写成了 rom,where 携程here,如果执行这条sql,程序会抛出什么异常呢?请看:

    file

    错误提示在rom附近存在一处语法错误,这说明,执行语法分析时,如果存在语法问题,不管有多少处问题,检测到第一处时就直接返回,不会接着往下分析,所以我们看到报错的时候别以为只有这一处问题,一定要检查仔细,否则上了生产环境,可能就会酿成大错。

    接下来就需要进行预处理了,mysql会根据一些规则进一步的解析树是否合法,比如:表和数据列是否存在解析名字和别名是否存在歧义,我们再来看这条sql语句:

    select * from sys_user1 here id = 1
    
    > 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id = 1' at line 1
    > 时间: 0.007s
    
    

    这里同样存在两处错误,第一:表名不存在,第二:关键字where写成了here,我们发现异常出现在 ”id=1“ 的附近,那么说明先分析的语法,在执行预处理的时候才校验的表名,我们现在将关键字where修改正确

    select * from sys_user1 where id = 1
    
    > 1142 - SELECT command denied to user 'testlocal'@'113.xxx.xxx.xxx' for table 'sys_user1'
    > 时间: 0.006s
    
    

    这个时候提示表名有问题了,说明上面的顺序,先解析sql语法,然后再预处理的时候在校验表名、字段名等是否合法。

    下一步预处理还会验证权限,这里的验证一般情况下都会较快,除非权限配置相当复杂。

    优化器

    分析器完成之后,语法树已经是合法的了,这个时候优化器就登场了,优化器将这条语法树转化成执行计划。mysql官方也是很为我们开发人员着想,设置了这个优化器,在开发过程中,我们想要查询一条sql语句,执行的方式有很多,比如是否走索引、走哪条索引、关联查询哪张表做主表等等,这些都是可变的,而优化器的作用就是根据程序员写的sql语句找到一条它认为最好的执行计划。

    举个例子,请看下面这条sql:

    select id,user_id,username from math m join english e using(user_id)
    where m.score = 80 and e.score = 90 
    

    这里不建表了,随便写了一个sql语句,大家能看明白就行,主要是讲解一下什么是优化器,math:数学成绩表;english:英语成绩表,score:成绩(字段),这条sql的意思是:查询数学成绩80分并且英语成绩90分的同学信息,你觉得mysql会怎么样执行这条sql呢?

    1. 正常情况是不是先往数学成绩表(math)中查询成绩等于80的 user_id,然后拿着这些 user_id到英语成绩表(english)中查询成绩等于90的学生信息?
    2. 能不能先前往英语成绩表(english)中查询成绩等于90的user_id,然后再去数学成绩表(math)中查询成绩等于80的学生呢?

    如果数学成绩表(math)中一共有5位同学的成绩是80分,英语成绩表(english)中只有一个同学的成绩是90分,那么第二种方式是不是效率就会高很多呢?

    mysql的优化器就是做这个的,他会根据独特的算法,找到它认为效率最高的一条路径去执行,这里就存在一个疑问了,经过优化器优化的sql,是否总是最优的执行计划呢?答案是否定的,哪些情况会导致优化器生成的执行计划效果反而更差呢?以下七点参考于《高性能mysql》。

    1. 统计信息不完整或者不准确,比如InnoDB的MVCC多版本并发控制会导致表数据行的统计不准确。
    2. 执行计划中的成本并不等同于实际执行的成本,这个时候即使统计的信息很准确,优化器给出的执行计划也有可能不是最优的。举个例子,有些时候某个执行嘉华虽然需要读取更多的页面或者数据,但是它的实际成本可能会很小,为什么呢?原因很简单,如果读取的页面都是有序的或者这些页面(数据)已经被加载到内存中了,这个时候的访问成本比执行计划估计的成本小得多,mysql并不知道哪些数据存在内存,哪些数据存在磁盘中,所以IO的次数也是未知数。
    3. mysql的最优可能和你想得不一样,你可能希望执行时间越短越好,但是mysql只是基于成本模型选择最优的执行计划,而有些时候这并不是最快的执行方式。所以这里我们看到的根据执行计划成本来选择执行计划并不是完美的模型。
    4. mysql从不考虑其他并发执行的查询,这可能影响到当前查询的速度。
    5. mysql并不是完全基于成本优化,有时候也会给予一些固定的规则,例如存在全文索引的match()子句,则在存在全文索引的时候就是用全文索引,即使有时候使用别的索引和where条件可以远比全文索引的方式要快很多,但是mysql也会选择使用全文索引。
    6. mysql不会考虑不受其控制的操作成本,例如存储过程或者用户自定义函数的成本。
    7. 优化器有时候无法估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。

    mysql的优化器是一个非常复杂的组件,算法很多优化的策略也有很多,它会通过自己的优化策略选择出优化器认为最优的一个执行计划,优化策略虽然很多,但是在大致上可以分为两种:静态优化、动态优化

    静态优化:可以直接对解析树进行分析、优化。优化器可以通过代数变换将where条件转换为另外一种等价形态,这个转换不依赖条件的具体数值,即使where条件中的数值发生改变,静态优化也仍然有效。可以理解为”编译时优化“。

    动态优化:它与查询的上下文相关,影响动态优化的因素有很多,比如索引对应的数量行数、where条件中的值等等,这些都会让优化器在执行sql的时候重新进行优化。可以理解为”运行时优化“。

    所以优化器对sql进行优化的时候是选择静态优化还是动态优化取决于sql语句,静态优化只需要做一次,而动态优化在每次执行的时候都需要重新评估。

    执行器

    分析器将我们写的sql语句解析成了语法树、优化器将语法树转成了它认为最优的一条执行计划,这个时候就需要真正的去读数据了,执行器就是那个执行者,前面的工作都是准备,分析器、优化器让mysql知道了你要干什么、通过什么方式最简单有效,执行器就是拿着这些准备好的方案实施。

    假如我们现在需要一共有多少人叫 “zhangsan” 这个姓名,手写了一条SQL语句:

    select * from sys_user where username = 'zhangsan';
    
    

    这就是一个普通的where条件查询,不过执行器在执行查询之前还需要做一件事情,而且是非常重要的事情:权限验证,执行器根据库名、表名、操作类型等等查看当前用户是否具备权限操作,如果发现当前用户并不具备此权限,那么直接终端执行,直接结束。

    权限验证通过之后,执行器就会打开进入存储引擎,打开数据表进行数据读取,执行器也是通过存储引擎提供的API进行的操作。

    我们一起来看看这条sql语句的执行过程,执行器调用存储引擎api获取 sys_user 表的第一行数据判断 username 是否等于 “zhangsan”,如果是,将结果保存到返回的结果集中,如果不是,则继续读取下一行数据,直到读完整张表为止(username字段未加索引情况下),最后执行器将结果集返回给客户端。

    这就是执行器大致的执行过程,那执行器调用存储引擎的的接口是什么呢?我们一般称它为 “handler API”,查询中的每一张表都有一个 handler 示例,在优化器中创建的,事无绝对,并不是所有的操作都是通过“handler” 完成,例如:表锁

    mysql会重复执行计划中的每个操作,直到执行器查询完所有的数据为止,所以,执行器没有分析器、优化器那么的复杂,它的主要功能就是执行。

    存储引擎

    MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。

    我们常用的存储引擎一般有一下几种:

    MyISAM:拥有较高的插入,查询速度,但不支持事务

    InnoDB:支持事务的存储引擎,mysql5.5以后将它设置为默认存储引擎。

    BDB:事务型数据库的另一种选择,支持COMMIT和ROLLBACK等其他事务特性

    Memory:基于内存的存储引擎,将所有的数据都置于内存中,查询、插入、删除效率极高,是一种空间换时间的思想,不过服务重启会导致数据丢失。

    Merge:将一部分的MyISAM表联合成的一个整体,适用于大数据存储。

    存储引擎五花八门,所以mysql将它设置成了插拔式结构,提高了mysql的整体灵活性。

    总结

    mysql有哪些基本组件?

    • 连接器
    • 缓存(8.0以前,不含8.0)
    • 分析器
    • 优化器
    • 执行器
    • 存储引擎

    mysql的执行流程是什么?

    客户端 ----> 连接器 ----> 缓存 ----> 分析器 ----> 优化器 ----> 执行器 ----> 存储引擎 ----> 客户端

    本文仅仅只是介绍了mysql一个查询语句整个的执行流程,看着内容非常多,其实只要认真的去思考,无非就是几个组件之间的调用,每个组件都有着自己的使用,连接器负责登陆校验、权限验证;缓存为了提高查询效率;分析器将mysql语句解析成语法树,然后执行预处理,判断权限等;优化器则是mysql生成一条它认为最优的执行计划;执行器拿着优化器给出的执行计划开始调用存储引擎的 “handler API” 进行数据读取,最后成返回到客户端,整篇文章看起来每个组件都比较复杂,没必要去死记硬背,尝试着去理解它为什么会出现这些组件,自然就能看明白了。

    码字不易,还请各位看客老爷给个关注,让我能有继续码字的动力。

    展开全文
  • Mysql架构以及SQL语句的执行流程

    千次阅读 2021-05-05 16:02:38
    Mysql架构以及SQL语句的执行流程 在平时的开发过程中,我们使用的数据库大多数看到的只是数据库的一个整体,一般都是输入一条语句,返回一个结果集,但是如果我们不知道其内部执行的细节,当我们在碰到一些异常情况...
  • MySQL事务执行流程?

    2022-02-15 16:12:26
    事务: 一条或者多条SQL语句组成执行单元,要么全部成功,要么全部失败; 操作事务的三个步骤 开启事务:记录回滚点,并通知服务器,将要执行一组操作,要么同时成功、要么同时失败 执行sql语句:执行具体的一条或多...
  • 本文将从MySQL总体架构—>查询执行流程—>语句执行顺序来探讨一下其中的知识。 一、MySQL架构总览: 架构最好看图,再配上必要的说明文字。 下图根据参考书籍中一图为原本,再在其上添加上了自己的理解。   从...
  • MySQL中的SQL执行流程

    千次阅读 2022-03-12 13:23:30
    MySQL中的SQL执行流程 MySQL的查询流程 查询缓存:Server如果在查询缓存中发现了这条SQL语句,就会直接将结果返回给客户端;如果没有,如果进入到解析器阶段。需要说明的是,因为查询混窜往往效率不高,所以在...
  • 在解释这条语句执行流程之前,我们看看mysql的基础架构。 图来自极客时间的mysql实践,该图是描述的是MySQL的逻辑架构。 server层包括连接器、查询缓存、分析器、优化器、执行器涵盖 MySQL 的大多数核心服务功能,...
  • 本篇文章是对mysql基础中的mysqld_safe启动执行流程进行了详细的分析介绍,需要的朋友参考下
  • mysql执行查询的流程

    2021-11-11 11:38:33
    流程图如下 1. 客户端发送一个查询到mysql服务器 2. 服务器查询缓存,如何缓存命中,直接返回查询结果,否则执行后续...4. mysql根据优化器生成的执行计划,调用存储引擎的api执行查询、 5. 返回对应的查询结果 ...
  • 以前有过一篇关于MySQL查询语句的执行过程,这里总结一下update语句的执行过程。由于update涉及到数据的修改,所以,很容易推断,update语句比select语句会更复杂一些。 1,准备 创建一张test表 CREATE TABLE `...
  • 查询执行流程--->语句执行顺序来探讨一下其中的知识。从上图中我们可以看到,整个架构分为两层,上层是MySQLD的被称为的‘SQL Layer’,下层是各种各样对上提供接口的存储引擎,被称为‘StorageEngineLayer’。...
  • 一条sql语句在mysql中的执行过程

    千次阅读 2022-03-11 10:03:26
    本文详细的介绍了一条sql语句在mysql执行的全过程,其中详细的介绍了Server层的各个组件的作用以及承担的角色等。
  • MySQL——update 语句执行流程

    千次阅读 2022-02-10 09:38:09
    图1 update语句执行流程 从 Buffer Pool(内存中) 中查看是否有这条数据,没有就从磁盘中加载到缓冲池,然后对这行记录加独占锁; 把更新行记录的旧值写入 undo log(以便回滚); 更新 Buffer Pool 中的数据(成脏...
  • 理解一条sql究竟都走了哪些流程
  • MySQL 8.0 SQL 执行流程

    千次阅读 2022-03-23 23:10:22
    MySQL 8.0 SQL 执行流程 首先我们先来看下 MySQL 的经典架构图,8.0 的没怎么翻到,先看看这个了。 图上有这么几个模块: Collectos 连接器,客户端可以通过这些方式对 MySQL 服务端发起通信。 Services & ...
  • 课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程MySQL索引原理详解,MySQL事务原理与事务并发,MySQL性能优化总结与MySQL配置优化。课程安排的学习的教程与对应的学习课件,详细的学习笔以及课程...
  • mysql执行流程主要通过mysql日志(Redo log 和 Undo log)来实现 提示:以下是本篇文章正文内容,下面案例可供参考 步骤 流程图: st=>加粗样式 flowchat 总结 提示:这里对文章进行总结: 例如:以上就是今天...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 186,060
精华内容 74,424
关键字:

mysql执行流程

mysql 订阅