本文目录:

  • 写在开头
  • SQL为什么查询速度会慢?
  • MySQL 执行流程
  • 1.MySQL 客户端/服务端通信
  • 2.MySQL 查询缓存
  • 3.MySQL 查询优化处理阶段
  • 3.1 查询优化器优化阶段
  • 3.2 MySQL执行计划(重点介绍)
  • 4.查询执行引擎
  • 5.返回结果到客户端



写在开头

  MySQL 凭借其不花钱的特性,在我们项目开发中占有很大一部分。虽然我们会用 sql语句来进行数据查询,但是你有了解过 MySQL 是如何进行查询的吗?

  在面试中,我们也经常被问到SQL 优化的内容,加个索引是最简单而且高效的一种方法。那么你有了解过SQL为什么查询速度会慢吗

SQL为什么查询速度会慢?

  一个SQL查询的生命周期,大致可以按照顺序来看:从客户端到服务端、在服务器上进行解析、生成执行计划 、执行、并返回结果给客户端。其中在执行阶段包含了大量的为了检索数据到存储引擎的调用、以及调用后对数据的处理过程,包括排序、分组等。

  查询速度慢的原因在于:某些不必要的额外操作。比如:某些操作被额外的重复执行很多次,某些操作执行太慢。优化查询的目的,就是减少和消除这些操作所花费的时间。

  接下来,我们就从 MySQL 底层,来了解一下它到底是怎么一个执行流程。

MySQL 执行流程

mysql 字符 查找 函数_mysql 字符 查找 函数


MySQL 执行流程,分为 5 步:

  1. 客户端发送一条查询给服务器;
  2. MySQL 服务器端先检查缓存。如果命中缓存,则直接返回结果;否则进入下一阶段;
  3. MySQL 服务器端进行 SQL 解析、预处理,再由优化器生成对应的执行计划;
  4. MySQL 根据优化器生成的执行计划,调用存储引擎的 API 来执行查询;
  5. 将查询结果返回客户端。

下来我们就开始对 MySQL 执行的每一步进行详细了解。

1.MySQL 客户端/服务端通信

  MySQL 客户端与服务端的通信,采用的是"半双工"的方式。此处涉及到一个数据传输的概念,数据传输共分为单工、半双工、全双工

  • 单工:数据传输只能在在一个方向上传输,一方固定为发送端,另一方固定为接收端;举例:电视机
  • 半双工:数据允许在两个方向上传输。在某一时刻,只允许数据在一个方向上传输,即:同一时间只可以有一方接收或者发送消息,可以实现双向通信。   举例:对讲机
  • 全双工:数据允许 同时 在两个方向上传输。即:发送设备和接收设备都具有独立的接收和发送能力,在同一时间可以同时接收和发送消息,实现双向通信。举例:电话通信

查询 MySQL客户端/服务端连接状态:

  对于一个 MySQL 连接,或者说一个线程,时刻都有一个状态来标识这个连接正在做的操作。我们可以通过命令:show full processlist / show processlist 来查看。

mysql 字符 查找 函数_缓存_02


我们可以通过上面内容,我们可以来查看当前 MySQL 线程处于哪种状态。Command 常用的线程命令值有以下几种:

  1. Sleep 线程正在等待客户端发送数据
  2. Execute 线程正在执行准备好的语句
  3. Query 连接线程正在执行查询

Command 全部线程命令值,可参考官网介绍:https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html

常见的 State 状态 有以下几种:

  1. Locked 线程正在等待表锁的释放 (MySQL 之前版本是 Locked,新版本锁的粒度更细了)
  2. Sorting result 线程正在对结果进行排序
  3. Sending data 向请求段返回数据

日常开发中,如果某个连接被锁住了,我们可通过kill {id}的方式将该连接杀掉。上面只介绍了常见的几种状态,全部状态可参考官网介绍:(MySQL 5.7 版本)https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html

2.MySQL 查询缓存

MySQL 默认是关闭缓存的,如需使用缓存,需要我们手动开启。下文介绍默认缓存已开启。如何开启缓存,请继续往下读。

同样的语句(同样的语句指:SQL语句必须一模一样,多一个空格,少一个空格都认为不是一个同样的语句)。

  1. 如果缓存不存在,MySQL 会将执行的语句和结果 以key-value的形式存储起来;(简单理解为 key=sql,value=结果集)
  2. 再次查询时,发现缓存存在,则不会进行后续的查询流程,直接从缓存返回。

如何打开MySQL缓存:

修改配置文件,来开启 MySQL 缓存:

vi /etc/my.cnf,在 [mysqld] 中添加:

query_cache_size = 64M
query_cache_type = ON(两个属性需同时配置,缓存才能开启生效)

然后,重启 mysql 服务

service mysqld restart

通过命令:show variables like ‘query_cache%’;来查看是否开启缓存(此处为单引号,显示错误是CSDN问题)。

mysql 字符 查找 函数_执行计划_03


可以通过命令:show status like qcache%;来查看缓存的相关情况

mysql 字符 查找 函数_mysql 字符 查找 函数_04


缓存失效问题:

  MySQL 只要表中的数据发生变化,缓存便会失效。

  比如:我们使用语句select * from user where id = 1查询,此时 Qcache_inserts会加 1;当再次使用该语句查询,此时Qcache_hits命中数会加 1。

  此时,我们使用语句update user set name=xxx where id = 5修改表中数据,尽管修改的是 id = 5 的这条,和 id = 1 没有任何关系,但是此时你再来 select * from user where id = 1查询时, Qcache_inserts会加 1,说明缓存已经失效。

MySQL缓存缺点: 只要修改表中数据,尽管与已缓存的数据无关联,但是所有缓存都会失效,这是一个不好的点。

什么情况下,数据不会被缓存:

  1. 加上SQL_NO_CACHE 参数将不缓存
  2. now()、current_date() 等函数的查询,不会缓存(select *,now() from user; 这种是不会进入缓存的)
  3. 查询语句不涉及表,不会缓存(select version(); ----查询MySQL版本号 )
  4. 查询的语句是系统表,不会缓存(select * from mysql.user;)
  5. 查询的语句的结果,超过了 query_cache_limit 时,也不会缓存

MySQL默认关闭缓存原因?

  1. 在查询之前,必须先检查是否命中缓存,浪费计算资源;
  2. 如果查询可以被缓存,name执行完成后,MySQL发现缓存中并没有这条数据,则会将结果存入缓存,这将会带来额外的系统消耗;
  3. 针对表的写入更新数据时,对应表的所有缓存将都会失效;
  4. 如果查询缓存很大或者碎片很多时,这个操作可能带来很大的系统消耗。

MySQL缓存适用场景:

  适用于以读为主,数据生成之后就不常改变的业务。比如门户类、新闻类、BI 报表类、论坛类等。

3.MySQL 查询优化处理阶段

 MySQL 的查询优化处理,分为以下三个阶段:

  • 解析SQL
      通过 Lex 此法分析、Yacc语法分析,将 SQL 语句解析成解析树。想了解Yacc、Lex ,请参考:Yacc 与 Lex 快速入门
  • 预处理阶段
      根据 MySQL 的语法规则,进一步检查解析树的合法性。如:检查数据的表和列是否存在,解析名字和别名的设置等,还会进行权限的相关验证操作
  • 查询优化器阶段
      查询优化器,可以将解析树转化为执行计划一条查询可以由很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
3.1 查询优化器优化阶段

  MySQL的查询优化,使用了很多的优化策略来生成一个最优的执行计划。优化策略可以分为两种:静态优化动态优化

静态优化可以直接对解析树进行分析,并完成优化。例如优化器可以通过一些简单的代数变换,将 where 条件转换成另一种等价形式。静态优化不依赖于特别的数值,如 where 条件中带入的一些参数等。静态优化在第一次完成后就一直有效,即使使用不同的参数重复查询也不会变化,可以认为是一种"编译时优化"。

相反,动态优化则和查询的上下文有关,也可能和很多其他因素有关。例如 where 条件中的取值、索引中条目对应的数据行数等,这些需要每次查询的时候重新评估,可以认为是"运行时优化"。

下面是一些MySQL能够处理的优化类型:

1. 重新定义关联表的顺序

  数据表的关联并不总是按照在查询中指定的顺序进行,决定关联的顺序是优化器很重要的一部分功能。

2. 将外连接转化成内连接

  并不是所有的outer join语句都必须以外连接的方式执行。诸多因素,例如where条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询,让其可以调整关联顺序。

3. 使用等价变换规则

  MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。例如:(5=5 and a>5)将被改写为a>5。类似的,如果有(a<b and b=c)and a=5,则会被改写为 b>5 and b=c and a=5。

4. 优化count()、min()和max()

  索引和列是否为空通常可以帮助 MySQL 优化这类表达式。例如,要找到一列的最小值,只需要查询对应B-tree索引最左端的记录,MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-tree索引中,优化器会讲这个表达式最为一个常数对待。类似的,如果要查找一个最大值,也只需要读取B-tree索引的最后一个记录。如果MySQL使用了这种类型的优化,那么在 explain 中 Extra 字段就可以看到"select tables optimized away"。从字面意思可以看出,它表示优化器已经从执行计划中移除了该表,并以一个常数取而代之。(该字段在:不会MySQL调优?先来瞅瞅SQL的执行计划吧有介绍到)

  类似的,没有任何 where 条件的 count(*) 查询通常也可以使用存储引擎提供的一些优化,例如,MyISAM维护了一个变量来存放数据表的行数。

5. 预估并转化为常数表达式

6. 覆盖索引扫描

  当索引中的列包含所有查询中需要使用的列的时候,MySQL就可以使用索引返回需要的数据,而无需查询对应的数据行。

7. 子查询优化

  MySQL在某些情况下可以将子查询转换成一种效率更高的形式,从而减少多个查询多次对数据进行访问。

8. 提前终止查询

  在发现已经满足查询需求的时候,MySQL总是能够立即终止查询。一个典型的例子就是当使用了 limit 子句的时候。除此之外,MySQL还有几种情况也会提前终止查询,例如发现了一个不成立的条件,这时MySQL可以立即返回一个空结果。

mysql 字符 查找 函数_执行计划_05


上面的例子可以看出,查询在优化阶段就已经终止。

9. 等值传播

10. 列表in()的比较

  在很多数据库系统中,in()完全等同于多个or条件的字句,因为这两者是完全等价的。在MySQL中这点是不成立的,MySQL将in()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个o(log n)复杂度的操作,等价转换成or的查询的复杂度为o(n),对于in()列表中有大量取值的时候,MySQL的处理速度会更快。

3.2 MySQL执行计划(重点介绍)

   MySQL执行计划,这一步在 MySQL 整个执行流程中占据不少的分量,并且这一部分也经常被拿来在面试中提问。所以本文将 MySQL 执行计划单独摘出来进行介绍。请跳转链接学习:不会MySQL调优?先来瞅瞅SQL的执行计划吧

4.查询执行引擎

  在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构。

5.返回结果到客户端

  查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端,MySQL仍然会返回这个查询的一些信息,如查询影响到的行数。

  1. 如果查询可以被缓存,那么MySQL在这个阶段,会将结果存放到查询缓存中。

这样处理有两个好处:① 服务器无需存储太多的结果,也就不会因为要返回太多的结果而消耗太多的内存。② 这样的处理也让MySQL客户端第一时间获得返回的结果。

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