【Mysql专题】一条SQL在Mysql中是如何执行的-CSDN博客

前言

知其然当知其所以然。Mysql作为我们后端的重中之重搞懂其执行原理非常有必要。
甚至有人这么说Java一定要搞好的三板斧有【数据结构】、【Mysql】、【Spring】。只要你能舞好这三板斧基本上可以成为一个比较优秀的JavaCoder了。

前置知识

同学们知道自己写出来的SQL它的执行顺序是怎样的吗或许有小伙伴会想知道了又怎样我不知道不一样写
不一定的同学们如果你知道了执行顺序起码你在写SQL的时候不会四顾茫然而是下笔如有神。这就好像是开启了内视一样你的武功心法在经脉中式如何运气你一清二楚。
PS下面比较重要的是理解select语句的执行顺序
PS下面比较重要的是理解select语句的执行顺序
PS下面比较重要的是理解select语句的执行顺序

1查询语句顺序

1. FROM子句指定要查询的数据表
2. WHERE子句指定查询条件
3. GROUP BY子句按照指定的列进行分组
4. HAVING子句指定对分组结果的筛选条件
5. SELECT子句指定要查询的列
6. ORDER BY子句按照指定的列进行排序
7. LIMIT子句指定查询结果的数量

2插入执行顺序

1. 指定要插入的表
2. 从INSERT语句中获取要插入的数据
3. 检查要插入的数据是否符合表结构的约束条件
4. 检查要插入的数据是否与表中已有的数据产生冲突
5. 将数据插入到表中

3. 更新执行顺序

1. 指定要更新的表
2. 执行WHERE子句筛选出要更新的数据
3. 检查要更新的数据是否符合表结构的约束条件
4. 检查要更新的数据是否与表中已有的数据产生冲突
5. 更新数据

4删除执行顺序

1. 指定要删除的表
2. 执行WHERE子句筛选出要删除的数据
3. 检查要删除的数据是否符合表结构的约束条件
4. 检查要删除的数据是否与表中已有的数据产生冲突
5. 删除数据

课程内容

一、Mysql的内部组件结构

在介绍之前这边还是先给大家一个比较全面的Mysql内部组件结构图。

大体来说Mysql可以分为Server层和引擎层。

1.1 Server层

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

1.2 引擎层Store层

存储引擎层负责数据的存取。其架构模式是插件式的支持Innodb、MyIsam、Memory等多个存储引擎。现在最常用的是Innodb他从Mysql5.5.5版本开始成为了默认的存储引擎。
这是我随便截图的一个自己创建的创建的时候并没有去刻意指定引擎层

CREATE TABLE `student_info` (
  `student_id` int(11) DEFAULT NULL,
  `student_name` varchar(255) DEFAULT NULL,
  `student_age` int(255) DEFAULT NULL,
  `student_score` decimal(8,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

下面我们重点来分析连接器、查询缓存、分析器、优化器、执行器分别主要干了哪些事情。

二、连接器

不得不说很多初学者刚接触Mysql的时候都有点傻傻分不清Mysql的服务端跟客户端分别是什么。简单来说我们在服务器安装的Mysql5.X或者Mysql8.x就是服务端如下图用PhpStudy提示要安装的数据库服务

上面这些就是服务端它首先是一个独立的软件跟我们Java什么的没有半毛钱关系。

既然是服务端那我们通常就需要一些客户端去连接使用。我们常用的客户端navicat、mysql front、jdbc、SQLyog等其实可以看做是【可视化客户端】的一些种类除此之外当然还有非可视化的最典型的非可视化的就是我们在服务端上使用mysql -h host -u root -p这种交互式的如果你们看了我最开始的结构图会发现我把【Java程序】也当作是客户端的一种了
而客户端要向mysql发起通信都必须先跟Server端建立通信连接而建立连接的工作就是有连接器完成的。

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

[root@192 ~]# mysql -h host[数据库地址] -u root[用户] -p root[密码] -P 3306

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

1. 如果用户名或密码不对你就会收到一个"Access denied for user"的错误然后客户端程序结束执行。
2. 如果用户名密码认证通过连接器会到权限表里面查出你拥有的权限。之后这个连接里面的权限判断逻辑都将依赖于此时读到的权限。

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

mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password'; //创建新用户
mysql> grant all privileges on *.* to 'username'@'%'; //赋权限,%表示所有(host)
mysql> flush privileges //刷新数据库
mysql> update user set password=password(”123456″) where user=’root’;(设置用户名密码)
mysql> show grants for root@"%"; 查看当前用户的权限

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

客户端如果长时间不发送command到Server端连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的默认值是 8 小时。
查看wait_timeout

mysql> show global variables like "wait_timeout";
mysql>set global wait_timeout=28800; 设置全局服务器关闭非交互连接之前等待活动的秒数

如果在连接被断开之后客户端再次发送请求的话就会收到一个错误提醒 Lost connection to MySQL server during query。这时候如果你要继续就需要重连然后再执行请求了。
数据库里面长连接是指连接成功后如果客户端持续有请求则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接下次查询再重新建立一个开发当中我们大多数时候用的都是长连接,把连接放在Pool内进行管理但是长连接有些时候会导致 MySQL 占用内存涨得特别快这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来可能导致内存占用太大被系统强行杀掉OOM从现象看就是 MySQL 异常重启了

怎么解决这类问题呢

1. 定期断开长连接。使用一段时间或者程序里面判断执行过一个占用内存的大查询后断开连接之后要查询再重连
2. 如果你用的是 MySQL 5.7 或更新版本可以在每次执行一个比较大的操作后通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证但是会将连接恢复到刚刚创建完时的状态

三、查询缓存Mysql8.0后已移除

常用的一些操作

mysql>show databases; 显示所有数据库
mysql>use dbname 打开数据库
mysql>show tables; 显示数据库mysql中所有的表
mysql>describe user; 显示表mysql数据库中user表的列信息

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

查询缓存过程
MySQL 拿到一个查询请求后会先到查询缓存看看之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value对的形式被直接缓存在内存中。key 是查询的语句value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key那么这个 value 就会被直接返回给客户端。
如果语句不在查询缓存中就会继续后面的执行阶段。执行完成后执行结果会被存入查询缓存中。你可以看到如果查询命中缓存MySQL 不需要执行后面的复杂操作就可以直接返回结果这个效率会很高。

查询缓存缺点
但其实大多数情况下Mysql的查询缓存是一个鸡肋的功能。为什么呢
因为查询缓存的失效非常频繁Mysql的缓存失效策略是只要有对一个表的更新这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来还没使用呢就被一个更新全清空了。对于更新压力大的数据库来说查询缓存的命中率会非常低。

查询缓存建议
一般建议大家在静态表里使用查询缓存什么叫静态表呢就是一般我们极少更新的表。比如一个系统配置表、字典表那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种【按需使用】的方式。你可以将my.cnf参数 query_cache_type 设置成 DEMAND。

my.cnf
#query_cache_type有3个值 0代表关闭查询缓存OFF1代表开启ON2DEMAND代表当sql语句中有SQL_CACHE关键词时才缓存
query_cache_type=2

这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句可以用 SQL_CACHE 显式指定像下面这个语句一样

mysql> select SQL_CACHE * from test where ID=5

查看当前mysql实例是否开启缓存机制

mysql> show global variables like "%query_cache_type%";

监控查询缓存的命中率

mysql> show status like'%Qcache%'; //查看运行的缓存信息

• Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks如果该值显示较大则说明查询缓存中的内存碎片过多了可能在一定的时间进行整理。
• Qcache_free_memory:查询缓存的内存大小通过这个参数可以很清晰的知道当前系统的查询内存是否够用是多了还是不够用DBA可以根据实际情况做出调整。
• Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大缓存效果越理想。
• Qcache_inserts: 表示多少次未命中然后插入意思是新来的SQL请求在缓存中未找到不得不执行查询处理执行查询处理后把结果insert到查询缓存中。这样的情况的次数次数越多表示查询缓存应用到的比较少效果也就不理想。当然系统刚启动后查询缓存是空的这很正常。
• Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值用户可以适当的调整缓存大小。
• Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
• Qcache_queries_in_cache:当前缓存中缓存的查询数量。
• Qcache_total_blocks:当前缓存的block数量。

PSmysql8.0已经移除了查询缓存功能

四、分析器

如果没有命中查询缓存就要开始真正执行语句了。这是第三步分析sql语句。分析过程如下

1. 首先MySQL 需要知道你要做什么因此需要对 SQL 语句做解析
2. 分析器先会做【词法分析】。你输入的是由多个字符串和空格组成的一条 SQL 语句MySQL 需要识别出里面的字符串分别是什么代表什么
3. MySQL 从你输入的"select"这个关键字识别出来这是一个查询语句。它也要把字符串“T”识别成“表名 T”把字符串“ID”识别成“列 ID”
4. 做完了这些识别以后就要做【语法分析】。根据词法分析的结果语法分析器会根据语法规则判断你输入的这个 SQL 语句是否满足 MySQL 语法我们在写sql的时候经常遇到的[Err] 1064 - You have an error in your SQL syntax;就是【语法分析】器分析过后给你的结果

mysql> select * fro test where id=1;
ERROR 1064 (42000): 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 'fro test where id=1' at line 1

4.1 词法分析器原理

词法分析器分成6个主要步骤完成对sql语句的分析

1. 词法分析
2. 语法分析
3. 语义分析
4. 构造执行树
5. 生成执行计划
6. 执行计划

下图是SQL词法分析的过程步骤

SQL语句的分析分为词法分析与语法分析mysql的词法分析由MySQLLexMySQL自己实现的完成语法分析由Bison生成。
那么除了Bison外Java当中也有开源的词法结构分析工具例如Antlr4ANTLR从语法生成一个解析器可以构建和遍历解析树可以在IDEA工具当中安装插件antlr v4 grammar plugin。插件使用详见课程。
经过bison语法分析之后会生成一个这样的语法树

至此我们分析器的工作任务也基本圆满了。接下来进入到优化器。

五、优化器

经过了分析器MySQL 就知道你要做什么了。在开始执行之前还要先经过优化器的处理。优化器有什么作用呢
优化器是在表里面有多个索引的时候决定使用哪个索引或者在一个语句有多表关联join的时候决定各个表的连接顺序不然光凭程序员自己写很多多表联查的SQL性能都有点惨不忍睹。比如你执行下面这样的语句这个语句是执行两个表的 join

mysql> select * from test1 join test2 using(ID) where test1.name=yangguo and test2.name=xiaolongnv;

上面这条sql原则上可以这么查询

1. 可以先从表 test1 里面取出 name=yangguo的记录的 ID 值再根据 ID 值关联到表 test2再判断 test2 里面 name的值是否等于 yangguo
2. 也可以先从表 test2 里面取出 name=xiaolongnv 的记录的 ID 值再根据 ID 值关联到 test1再判断 test1 里面 name 的值是否等于 yangguo

这两种执行方法的逻辑结果是一样的但是执行的效率会有不同而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后这个语句的执行方案就确定下来了然后进入执行器阶段。如果你还有一些疑问比如优化器是怎么选择索引的有没有可能选择错等等。

六、执行器

开始执行的时候要先判断一下你对这个表 T 有没有执行查询的权限如果没有就会返回没有权限的错误如下所示 (在工程实现上如果命中查询缓存会在查询缓存返回结果的时候做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

mysql> select * from test where id=1;

如果有权限就打开表继续执行。打开表的时候执行器就会根据表的引擎定义去使用这个引擎提供的接口。比如我们这个例子中的表 test 中ID 字段没有索引那么执行器的执行流程是这样的

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

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

学习总结

1. 复习sql的执行顺序特别是查询语句的执行顺序
2. 学习Mysql服务的内部组件结构图
3. 学习Mysql内部各组件的作用