mysql 字符查找函数

转载

数码墨鱼 2024-10-29 22:18:27

文章标签 mysql 字符查找函数 MySQL 缓存执行计划 文章分类 MySQL 数据库

本文目录：

写在开头
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 步：

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

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

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

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

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

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

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

mysql 字符查找函数_缓存_02

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

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

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

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

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

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

2.MySQL 查询缓存

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

同样的语句（同样的语句指：SQL语句必须一模一样，多一个空格，少一个空格都认为不是一个同样的语句）。

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

如何打开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缓存缺点： 只要修改表中数据，尽管与已缓存的数据无关联，但是所有缓存都会失效，这是一个不好的点。

什么情况下，数据不会被缓存：

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

MySQL默认关闭缓存原因？

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

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的处理速度会更快。