一条SQL的生命周期

小龙 883 2022-04-26

前言

我们要更好的去优化SQL，优化数据库，我们首先必须知道SQL的执行过程。通过一张图来简单描述一下SQL的指向过程

image.png

SQL执行流程

一条SQL的执行分为三个部分：连接管理、解析与优化、存储引擎。

对应到上面的图中解释

连接管理：

从客户端发送请求到服务器端，到连接池获取连接

解析与优化

包括查询缓、存SQL解析器、查询优化器、查询存储引擎

存储引擎

到这个阶段是通过各种存储引擎访问文件系统（磁盘）

MYSQL客户端

MySQL 数据库支持很多编程语言的 API 接口，其实这句话底层的含义是很多编程语言是内置 MySQL 客户端。除此之外，MySQL 还有一个常用的客户端就是 MySQL 数据库自带的一个 mysql 命令，这个命令使用如下：

mysql -u$username -p$password -h$host -P$port

备注：
    - username 是用户名
    - password 是密码
    - host 是 MySQL 服务端地址
    - port 是 MySQL 服务端端口

一条 SQL 执行的第一步是由 MySQL 客户端发送到 MySQL 服务端。在这个步骤中直接影响数据速度的是网络，所以，数据库服务端和客户端之间最好要有良好的网络环境。

MySQL 客户端成功连接 MySQL 服务端之后，MySQL 服务端的连接池会对客户端的连接进行权限验证，当权限验证通过之后，MySQL 服务端会将客户端的链接记录在服务端的连接池中，之后的各种操作将不再进行权限认证。

image.png

MYSQL服务端为什么要加一个连接池？

如果不使用连接池，每一次客户端的请求都会去创建一个连接，使用完了就销毁，创建和销毁的过程都会有一定的时间消耗；在高并发下每个请求来了就创建一个连接，这样持续下去可能会导致内存溢出等问题。

为了解决这种资源白白浪费、有可能存储的内存溢出问题，MySQL数据库就引入了连接池。连接池的主要作用是用来保存通过了安全校验的客户端连接。当一个客户端成功连接后，并不会在操作完成之后将连接销毁，而是将这个连接保存到连接池中，这样下一次同一个客户端发起请求时就不需要在进行一次安全校验和创建连接操作了，很大程度上降低了数据库的资源消耗而且还能减少连接数据库时的延时

MySQL服务端

在 MySQL 服务端中，SQL 执行过程中是需要经过很多模块的，其中比较重要的模块是缓存、SQL 解析器、查询优化器以及 SQL 执行器等模块。下面详细介绍一下。

查询缓存

在 MySQL 数据库中，如果开启了缓存查询，每一次的查询都会在缓存器中以 KV 形式缓存一份。一条 SQL 在执行过程中，如果命中了缓存，就会跳过 SQL 解析器、查询优化器以及 SQL 执行器，并且立即返回数据，这样做的目的主要是提高数据库的性能。

其实 MySQL 数据库是将缓存以哈希的形式保存在内存中的一个引用表中，并且把本次查询的 SQL、数据库名称以及协议的 hash 值作为 key，这样做的主要目的是下一次同一个查询过来之后可以直接命中查询。

不过缓存并不是永恒不变的，也会失效，以下几个情况会导致缓存失效：

  • 该条缓存对应的数据、数据表发生变化时，缓存就会自动失效；

  • 查询过程中有变化的数据时，是不会创建缓存的，例如 now()。

所以，在使用 MySQL 数据库查询的时候，要尽量避免更改数据和使用有变化的数据。

那么，既然缓存能够提高 MySQL 数据的性能，应该怎么设置开启缓存呢？首先，我们可以使用 SHOW VARIABLES LIKE '%query_cache%'; 来查询缓存的配置项：

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

从MySQL8开始，官方移除了query_cache，移除主要有两个原因

  • 查询缓存的效果取决于缓存的命中率，只有命中缓存的查询效果才能有改善，因此无法预测其性能。

  • 查询缓存的另一个大问题是它受到单个互斥锁的保护。在具有多个内核的服务器上，大量查询会导致大量的互斥锁争用。

其中，query_cache_type 有三个选项，分别是：

OFF（0）：关闭缓存。
ON（1）：开启缓存。
DEMAND（2）：按需开启缓存，加上 SQL_CACHE 关键字才会缓存。

要开启查询缓存可以在 MySQL 数据库的配置文件 my.cnf 中添加 query_cache_type = 1 即可。如果需要使用 DEMAND 的话，就需要配置成 query_cache_type = 2 。如果需要缓存的话，就需要在查询语句上增加 SQL_CACHE 关键字

SQL 解析器

当没有命中缓存时，这个时候 MySQL 数据库就得去查询数据了。在查询之前必须解析客户端发送过来的以一系列字符串和空格组成的 SQL，此时就必须用到 MySQL 数据库中的另一个模块：SQL 解析器。

SQL 解析器的主要功能是解析客户端发送过来的 SQL，就比如匹配到 SQL 中是以 select 开头的，那就可以认定其为查询语句；以 insert 开头的 SQL，就可以认定其为插入语句。但如果在判断的过程中，出现了匹配不到的这种情况，就会报出 ERROR 1064 (42000): You have an error in your SQL syntax; 的错误。具体如下：

mysql> elect * from info;
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 'elect * from info' at line 1

一般情况下，具体的错误信息都是在最后边的单引号内，具体问题具体对待。

查询优化器

查询优化器，顾名思义就是优化 SQL 的执行效率，也就是说查询优化器的具体功能是为了找到 SQL 的最佳执行方案。

在 MySQL 数据库中的查询优化器优化 SQL 具体有两个方面，分别是逻辑层面和物理层面。

物理层面主要是跟硬件有关，很难通过逻辑去优化，所以这里我们从逻辑层面说明一下。

逻辑层面的优化主要有命中索引优化、顺序优化、排序优化等。例如连表查询，具体如下：

select * from province inner join city on city.fid = province.id where province.id = 1;
+----+-----------+----+--------------+------+
| id | name      | id | name         | fid  |
+----+-----------+----+--------------+------+
|  1 | 上海市    |  1 | 徐汇区         |    1 |
|  1 | 上海市    |  2 | 浦东新区       |    1 |
+----+-----------+----+--------------+------+

上面的 SQL 连接 province 和 city 两个数据表，在内存中，有如下两种情况。

  • 第一种情况：首先查询 province 表中 id 为 1 的数据，然后再查询 city 表中 fid 为 1 的数据。

  • 第二种情况：首先查询 city 表中的所有数据，然后再判断 city 表中的 fid 是否等于 1。

这两种情况的结果是一样的，这个时候查询优化器内部通过算法的方式判断哪个方案的效率更高，进而选择哪个方案。

SQL 执行器

当分析完 SQL 并且选择合适的方案之后，就开始执行 SQL 了，执行 SQL 就需要使用 MySQL 数据库提供的 SQL 执行器模块。

SQL 执行模块首先会判断当前用户是否对该表有相关的操作权限（如果命中了缓存，将会在返回缓存数据之前进行权限认证）。权限判断通过之后才会调用存储引擎去操作对应的数据表，然后将操作的结果返回。

总结

在这篇文章中，我们按照 SQL 的执行顺序介绍了一条 SQL 从客户端到返回数据期间经过的各个模块。这里我简单将各个要点汇总一下。

  • MySQL 客户端主要是用来将 SQL 发送至服务端的一个模块。

  • 连接池主要是用来保存成功连接 MySQL 服务端的链接的，这样做的好处是可以防止数据库连接短时间内不断重复创建，减少了资源浪费，提高了数据库的访问速度。

  • 数据库缓存模块是将之前访问过的数据保存在内存中，这样做的好处是避免相同的一个任务重复执行的问题，可以提高数据库速度，并且也可以降低数据库资源消耗。

  • SQL 解析器主要是用于解析 SQL 的，搞清楚这条 SQL 的具体目的——查询还是更新数据。

  • SQL 优化器是通过内部的算法选择执行这条 SQL 效率最高的方案。

  • 搞清了 SQL 的具体操作，也选择了最优的执行方案，最后就开始执行 SQL，SQL 执行器的主要作用除了调用存储引擎接口获取数据之外，还有权限认证的作用。

至此，一条 SQL 的生命周期就结束了。

# mysql