浅谈mysql执行过程以及顺序

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

一: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执行过程以及顺序的文章就介绍到这了,更多相关mysql执行过程及顺序内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 有关mysql中sql的执行顺序的小问题

    今天工作中碰到一个sql问题,关于left join的,后面虽然解决了,但是通过此问题了解了一下sql的执行顺序 场景还原 为避免安全纠纷,把场景模拟. 有一个学生表-S,一个成绩表G CREATE TABLE `test_student` ( `id` bigint(20) NOT NULL COMMENT '学号', `sex` TINYINT DEFAULT '0' COMMENT '性别 0-男 1-女', `name` varchar(255) DEFAULT NULL COMMENT

  • 简单了解mysql语句书写和执行顺序

    mysql语句的书写顺序和执行顺序有很大差异. 书写顺序,mysql的一般书写顺写为: select <要返回的数据列> from <表名> <join, left join, right join...> join <join表> on <join条件> where <where条件> group by <分组条件> having <分组后的筛选条件> order by <排序条件> limit

  • mysql代码执行结构实例分析【顺序、分支、循环结构】

    本文实例讲述了mysql代码执行结构.分享给大家供大家参考,具体如下: 本文内容: 什么是代码执行结构 顺序结构 分支结构 循环结构 首发日期:2018-04-18 什么是代码执行结构: 这里所说的代码执行结构就是多条sql语句的执行顺序. 代码执行结构主要用于触发器.存储过程和函数等存储多条sql语句中. 顺序结构: 顺序结构就是从上到下依次执行sql语句 一般默认情况下都是顺序结构 分支结构: 分支结构的执行是依据一定的条件选择执行路径,它会依据我们给定的条件来选择执行那些sql语句 mys

  • sql和MySQL的语句执行顺序分析

    今天遇到一个问题就是mysql中insert into 和update以及delete语句中能使用as别名吗?目前还在查看,但是在查阅资料时发现了一些有益的知识,给大家分享一下,就是关于sql以及MySQL语句执行顺序: sql和mysql执行顺序,发现内部机制是一样的.最大区别是在别名的引用上. 一.sql执行顺序 (1)from (2) on (3) join (4) where (5)group by(开始使用select中的别名,后面的语句中都可以使用) (6) avg,sum....

  • MySQL语句执行顺序和编写顺序实例解析

    select语句完整语法: SELECT DISTINCT <select_list> FROM <left_table> <join_type> JOIN <right_table> ON <join_condition> WHERE <where_condition> GROUP BY <group_by_list> HAVING <having_condition> ORDER BY <order_

  • 简单了解MySQL SELECT执行顺序

    SELECT语句的完整语法为: (7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) HAVING <having

  • SQL语句执行深入讲解(MySQL架构总览->查询执行流程->SQL解析顺序)

    前言: 一直是想知道一条SQL语句是怎么被执行的,它执行的顺序是怎样的,然后查看总结各方资料,就有了下面这一篇博文了. 本文将从MySQL总体架构--->查询执行流程--->语句执行顺序来探讨一下其中的知识. 一.MySQL架构总览: 架构最好看图,再配上必要的说明文字. 下图根据参考书籍中一图为原本,再在其上添加上了自己的理解. 从上图中我们可以看到,整个架构分为两层,上层是MySQLD的被称为的'SQL Layer',下层是各种各样对上提供接口的存储引擎,被称为'Storage Engin

  • 浅谈mysql执行过程以及顺序

    前言:mysql在我们的开发中基本每天都要面对的,作为开发中的数据的来源,mysql承担者存储数据和读写数据的职责.因为学习和了解mysql是至关重要的,那么当我们在客户端发起一个sql到出现详细的查询数据,这其中究竟经历了什么样的过程?mysql服务端是如何处理请求的,又是如何执行sql语句的?本篇博客将来探讨这个问题: 一:mysql执行过程 mysql整体的执行过程如下图所示: 1.1:连接器 连接器的主要职责就是: ①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向

  • 浅谈Mysql多表连接查询的执行细节

    先构建本篇博客的案列演示表: create table a(a1 int primary key, a2 int ,index(a2)); --双字段都有索引 create table c(c1 int primary key, c2 int ,index(c2), c3 int); --双字段都有索引 create table b(b1 int primary key, b2 int); --有主键索引 create table d(d1 int, d2 int); --没有索引 insert

  • 浅谈MySQL数据查询太多会OOM吗

    目录 全表扫描对server层的影响 全表扫描对InnoDB的影响 InnoDB内存管理 小结 我的主机内存只有100G,现在要全表扫描一个200G大表,会不会把DB主机的内存用光? 逻辑备份时,可不就是做整库扫描吗?若这样就会把内存吃光,逻辑备份不是早就挂了? 所以大表全表扫描,看起来应该没问题.这是为啥呢? 全表扫描对server层的影响 假设,我们现在要对一个200G的InnoDB表db1. t,执行一个全表扫描.当然,你要把扫描结果保存在客户端,会使用类似这样的命令: mysql -h$

  • 浅谈Mysql主键索引与非主键索引区别

    目录 什么是索引 主键索引和普通索引的区别 索引具体采用的哪种数据结构 InnoDB使用的B+ Tree的索引模型,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点? B+ Tree的叶子节点都可以存哪些东西? 聚簇索引和非聚簇索引,在查询数据的时候有区别? Index Condition Pushdown(索引下推) 查询优化器 关于索引的题 什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据结构,索引的目的在于提高查询效率,类比字典:实际上

  • 浅谈MySQL表空间回收的正确姿势

    目录 前置说明 问题重现 删除数据原理 数据的复用 哪些操作会造成数据空洞 如何收缩表空间 小结 不知道大家有没有遇到这样的一种情况,线上业务在MySQL表上做增删改查操作,随着时间的推移,表里面的数据越来越多,表数据文件越来越大,数据库占用的空间自然也逐渐增长 为了缩小磁盘上表数据文件占用的空间,我们在最大的一张业务表中用delete命令删除了一半儿的旧数据,删除之后,磁盘上表数据文件并没有缩小,即使删除整张表的数据,文件依然没有变小,这是为什么呢? 本文将详细的分析上述问题,并给出正确回收表

  • 浅谈Mysql大数据分页查询解决方案

    目录 1.简介 2.分页插件使用 3.sql测试与分析 3.1 limit现象分析 3.2 解决之道 4 测试时走过的坑 4.1 百万数据内容都一样 4.2 写sql时,把"77"写成了77: 4.3 一个有趣的现象 总结 1.简介 之前,面阿里的时候,有个面试官问我有没有使用过分页查询,我说有,他说分页查询是有问题的,怎么解决:后来这个问题我没有回答出来:本着学习的态度,今天来解决一下这个问题: 2.分页插件使用 1.pom文件 <dependency> <grou

  • 浅谈MySQL和Lucene索引的对比分析

    MySQL和Lucene都可以对数据构建索引并通过索引查询数据,一个是关系型数据库,一个是构建搜索引擎(Solr.ElasticSearch)的核心类库.两者的索引(index)有什么区别呢?以前写过一篇<Solr与MySQL查询性能对比>,只是简单的对比了下查询性能,对于内部原理却没有解释,本文简单分析下两者的索引区别. MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式. M

  • 浅谈mysql的索引设计原则以及常见索引的区别

    索引定义:是一个单独的,存储在磁盘上的数据库结构,其包含着对数据表里所有记录的引用指针. 数据库索引的设计原则: 为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引. 那么索引设计原则又是怎样的? 1.选择唯一性索引 唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录. 例如,学生表中学号是具有唯一性的字段.为该字段建立唯一性索引可以很快的确定某个学生的信息. 如果使用姓名的话,可能存在同名现象,从而降低查询速度. 2.为经常需要排序.分组和联合操

  • 浅谈MySQL存储过程中declare和set定义变量的区别

    在存储过程中常看到declare定义的变量和@set定义的变量.简单的来说,declare定义的类似是局部变量,@set定义的类似全局变量. 1.declare定义的变量类似java类中的局部变量,仅在类中生效.即只在存储过程中的begin和end之间生效. 2.@set定义的变量,叫做会话变量,也叫用户定义变量,在整个会话中都起作用(比如某个应用的一个连接过程中),即这个变量可以在被调用的存储过程或者代码之间共享数据.如何理解呢?可以看下面这个简单例子,很好理解. (1)先执行下面脚本,创建一

  • 浅谈Python的方法解析顺序(MRO)

    方法解析顺序, Method Resolution Order 从一段代码开始 考虑下面的情况: class A(object): def foo(self): print('A.foo()') class B(object): def foo(self): print('B.foo()') class C(B, A): pass c = C() c.foo() C同时继承了类A和类B, 它们都有各自的foo()方法. 那么C的实例c调用foo()方法时, 到底是调用A.foo()还是B.foo

随机推荐