MySQL原理--一条SQL查询语句的执行流程

原文网址:MySQL原理--一条SQL查询语句的执行流程_IT利刃出鞘的博客-CSDN博客

简介

说明

        本文介绍MySQL中一条SQL查询语句的执行流程。

        此问题也是Java后端面试中常问的一个问题。

相关网址

MySQL--query cache,buffer pool,key buffer--含义与区别_IT利刃出鞘的博客-CSDN博客

流程概述

说明

MySQL 可以分为 Server 层和存储引擎层两部分。

  • Server 层
    • 包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核 心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等)。
    • 所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。
  • 存储引擎层:
    • 负责数据的存储和提取。
    • 架构模式是插件式的,支持 InnoDB、 MyISAM、Memory 等多个存储引擎。现在常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

流程图 

1. 连接器

        连接器的作用:连接处理、授权认证、安全性。

        客户端连接命令是:mysql -h$ip -P$port -u$user -p。

        当客户端通过TCP三次握手连接到MySQL服务器时,服务器首先对其进行身份认证(可以通过用户名和密码的方式进行认证,也可以通过SSL证书进行认证)。登录认证通过后,服务器还会查询出当前用户拥有的权限,之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。也就是说,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有新建的连接才会使用新的权限设置。

        每个客户端的连接都对应着服务器上的一个线程。服务器的连接器上维护了一个线程池,避免为每个连接都创建销毁一个线程。

        如果连接完成后,未来的一段时间里没做任何操作,这个连接就处于空闲的状态,通过show processlist命令中看到空闲连接列表,如下图所示(Sleep标志就表示一个空闲连接):

         客户端如果太长时间没有操作,连接器就会自动将它断开,此时想要继续操作就要重连。这个等待时间是由参数wait_timeout控制的,通过show variables like 'wait_timeout';可以查看,默认值是28800秒,即:8小时。

         数据库建立连接的过程比较复杂,使用中应该尽量减少连接的动作,也就是尽量使用长连接。但全部使用长连接后,有些时候MySql占用的内存会飙涨的很快。这是由于MySql在执行的过程中临时使用的内存是管理在连接对象里面的,这些资源会在连接断开的时候才释放,所以如果长连接累积下来,可能导致内存占用太大,导致被系统强行杀掉OOM(Out Of Memory),进而导致MySql异常重启。

此时有两个解决办法:

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

2. 查询缓存

说明

        查询缓存(Query Cache)作用:缓存我们所执行的 SELECT 语句以及该语句的结果。

        连接建立后,执行查询语句的时候,会先查询缓存,MySQL 会先校验这个 sql 是否执行过,之前执行过的sql语句会以Key-Value 的形式缓存在MySQL内存中,Key 是查询sql语句,Value 是结果集。如果缓存 key 被命中,就会直接返回给客户端,如果没有命中,就会执行后续的操作,完成后也会把结果缓存起来,方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限,是否有该表的查询条件。

        对应的现象就是:同一条语句在MySQL执行两次,第一次和后面的时间是不一样的,后者明显快一些,这就是因为查询缓存的存在。

MySQL 不建议使用查询缓存

有两个原因:

  1. 查询缓存的访问由一个单一的全局锁来控制,这时候大量的查询将被阻塞,直至锁释放。
    1. 所以不要简单认为设置缓存必定会带来性能提升。
  2. MySQL查询缓存很容易失效
    1. 只要对表有任何的更新,这个表的所有查询缓存就会全部被清空。这就很有可能出现缓存还没被使用过就被清空的情况,或者说一个更新将所有缓存都清除的情况。专业来说就是:缓存命中率低,一般只有那种只查询不更新的表适用缓存,但是这样的表往往很少存在,一般都是什么配置表之类的。

        可以通过设置MySQL参数query_cache_type为DEMAND,这样sql默认不使用缓存。

        想要在某个sql中使用缓存就在select后使用SQL_CACHE,在某个sql中不想使用缓存就在select后使用SQL_NO_CACHE。例如:

SELECT SQL_CACHE * FROM `user_info` WHERE id = 1;
SELECT SQL_NO_CACHE * FROM `user_info` WHERE id = 1;

Query Cache已经被逐步移除

        自MySQL 5.6(2013)以来,默认情况下已禁用查询缓存,而在MySQL 8.0之后,查询缓存的整块功能已被移除,移除主要是因为性能差:MySQL :: MySQL 8.0: Retiring Support for the Query Cache

3. 分析器

        如果存在查询缓存且缓存没有命中,或者没有开启查询缓存,则会真正开始sql执行。

        首先会走到分析器。分析器主要是用来分析 SQL 语句是来干嘛的,解析sql语句,即词法分析和语法分析:

  1. 词法分析
    1. sql语句有很多单词、空格,MySQL就需要识别每个字符串所代表的是什么,是关键字,还是表名,还是列名等等。
  2. 语法分析
    1. 根据词法分析的结果,语法分析会判断你sql的语法对错,错了会提醒“you have an error in your SQL syntax”错误,并且会提示你哪里错了,一般来说语法错误会提示第一个出现错误的位置,所以你要关注的是紧接“use near”的内容。

4. 优化器

        经过了分析器后,即使sql语句语法没问题,在真正执行sql之前,还需要经过优化器的优化处理。优化器会对你的sql选择它认为最优的执行方案去执行(优化器的选择不一定是最优的)。

优化器的作用是:

  1. 在sql中可以使用多个索引的时候,决定使用哪个最优索引。
  2. 在一个语句有多表关联(join)的时候,重新定义各个表的连接顺序。也就是说数据表的关联并不总是按照在查询中指定的顺序决定。
  3. 重写查询,消除冗余的操作,选择子任务最优的策略。比如在WHERE之后根一个1=1的条件,显然,查询条件中的 1=1 是完全多余的。在经过优化器优化之后的sql中这个条件将被去除。但有时候优化器的选择并不是最好的。
  4. 将外连接转换为内连接。并非所有的outer join都必须以外连接的方式执行,优化器能够识别等价条件并重写查询。
  5. 优化count()、min()和max(),比如MyISAM就维护了一个变量来记录总数,比如mix()和max()的字段如果有索引,则直接取索引树的最左边和最右边的值即可。
  6. 提前终止查询。查询已经满足条件时,MySQL总是能够立刻终止查询,比如使用了limit字句的时候。
  7. 索引覆盖扫描。如果索引中包含需要返回的所有数据,则无需回表查询对应的数据行。
  8. 负责生成执行计划,也就是常说的通过EXPLAIN看到的内容。

如下SQL:

SELECT C FROM T WHERE  A= 'value1' AND B = 'value2';

        假设A、B字段都使用了单独的索引,其中A上扫描了 100个符合条件的数据行,B 上扫描 50个符合条件的数据行,最终结果只有30个数据行符合两个条件。

        如果走A索引会有100个数据行,接着进行匹配找到其中的30个与 B 中的值匹配记录,其中就有 70 次是失败了。先根据 B会有 50 个数据行,接着进行匹配找到其中的 30 个与 A中的值匹配的记录,只有 20次是失败的。显然,选择使用B索引的效率会更高。

索引选择的逻辑

        优化器选择索引的目的就是选择一个扫描行数最少的方案。行数越少,磁盘读取越少。但扫描行数不是唯一标准,优化器还会结合是否使用临时表,是否回表,是否排序等因素。

        扫描行数怎么判断?真正执行语句之前,mysql不知道具体有多少条,只能根据统计信息估算。这个统计信息就是索引的“区分度”。索引上不同值越多,区分度越好。而一个索引上不同值的个数称为“基数”,使用show index from tablename可以查看,但在统计信息中,基数值都不一定准确。

        MySQL是怎样得到索引的基数的呢?实际上是使用采样统计的方法,为什么要采样统计呢?因为把整张表取出来一行行统计,虽然可以得到精确的结果,但是代价太高了,所以只能选择“采样统计”。

        采样统计的时候,InnoDB默认会选择N个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。

        数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过1/M的时候,会自动触发重新做一次索引统计。

        在MySQL中,有两种存储索引统计的方式,可以通过设置参数innodb_stats_persistent的值来选择:

  1. 设置为on的时候,表示统计信息会持久化存储。这时,默认的N是20,M是10。
  2. 设置为off的时候,表示统计信息只存储在内存中。这时,默认的N是8,M是16。

由于是采样统计,所以不管N是20还是8,这个基数都是很容易不准的。analyze table tablename命令,可以用来重新统计索引信息。force index则可以让查询强制选择指定的索引。

5. 执行器

        经过优化器之后这个语句具体如何执行就定下来了,接下来进入执行器阶段,开始执行sql。

        首先会对当前用户进行执行权限校验,如果没有权限,就会返回没有权限的错误:ERROR 1142 (42000): SELECT command denied to user。

        如果有权限,就会去调用引擎的接口,返回接口执行的结果。执行的时候,从第一行开始一行一行的去判断是否满足条件,读取一行的接口都是执行引擎定义好的,直接调用即可,直到取到表的最后一行,有索引的执行起来可能就好点,执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

        数据库的慢查询日志中看到看一个rows_examined字段,表示这个语句执行过程中扫描了多少行,这个值就是在执行器每次调用引擎获取数据行的时候累加的,在有些场景下,执行器调用一次,在引擎内部则扫描了很多行,因此引擎扫描行数跟rows_examined并不是完全相同的。

EXPLAIN命令可以看到执行计划,通过rows字段看到我们扫描了多少行:

  • 20
    点赞
  • 3
    收藏
  • 打赏
    打赏
  • 4
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:技术黑板 设计师:CSDN官方博客 返回首页
评论 4

打赏作者

IT利刃出鞘

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值