Mysql-一条SQL查询的执行过程

作为CRUD工程师，了解Mysql中SQL查询的执行过程，对我们从查询优化，数据库配置，数据还原方面都有帮助。

数据准备

首先，我们先准备一张学生表，学生表的学号student_id为主键id，学生姓名studnet_name为普通id。

然后，执行一条查询语句，用作分析查询过程：SELECT student_name FROM students where student_id < 3;。

-- 创建学生表
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50),
    age INT,
    city_id INT
);
-- 为学生表插入一些示例数据
INSERT INTO students (student_name, age, city_id)
VALUES
    ('张三', 18, 1),('李四', 19, 2),('王五', 20, 3),('赵六', 21, 1),('孙七', 22, 2),('周八', 23, 3),('吴九', 17, 1),('郑十', 16, 2);
-- 在学生表的 student_name 上创建索引
CREATE INDEX idx_student_name ON students(student_name);

总体过程介绍

总的过程大致可以分为三步。

首先，在网络连接层，用户发出查询sql请求，Mysql的连接器负责处理和客户端的连接，并对用户进行身份认证和权限确认。

第二步，进入Mysql的服务层，主要的功能都在这一步进行处理，包括查询缓存、解析器、优化器之类，比如SQL解析、优化、索引选择，到最后生成执行计划。

最后，第三步调用存储引擎提供的API查询数据库中数据。

image.png

详细步骤

连接器

当我们执行任何命令时，都需要先进行数据库连接，这时迎接我们的就是数据库连接器，验证我们的账号和密码，当账号密码正确进行数据库后，数据库会保持这个连接，如果我们没有后续动作，这个连接会保持空闲，默认保持空闲8小时后会Mysql会主动断开连接。

我们可以通过show processlist查看当前的连接情况。

image.png

查询缓存

在查询缓存前，Mysql会单独调用一个权限模块看用户是否具有查询相关表权限，当有权限时才会进行后续操作。此后，会先去查看缓存，如果缓存命中，就直接返回数据，不需要执行后面的操作了；如果没有命中，当后面的查询执行完之后，会将查询结果放到缓存中。

但Mysql默认是没有开启缓存的，并且在Mysql8.0之后直接移除了这个功能。

如果命中缓存那效率确实很高，但查询缓存失效非常频繁，你的SQL只要有一点不一样就不会命中缓存，或者只要有对一个表进行更新，这个表上所有的查询缓存都会被清空。因此可能你很费劲的存了很多缓存了，还没使用呢，就被一个更新全清空了。

分析器

如果没有命中缓存，就进入了正式的处理阶段，首先是分析器，我们传递过去的还只是一个字符串，分析器要将这段字符串转成Mysql看得懂的语言，会进行三段处理。

• 词法分析，将完整的SQL字符串切分成一个一个的关键字段，比如SELECT、数据库名、表名、WHERE等。例如上面的SELECT student_name FROM students where student_id < 3就会切分成：

-   关键字：`SELECT`、`FROM`、`WHERE`
-   表名：`students`
-   列名：`student_name`
-   条件：`student_id < 3`

• 语法分析，如果语法错误，Mysql将会给我们报错提示，例如我们将WHERE写成了WHORE：

image.png

如果语言正确，Mysql就会根据规则将SQL生成一颗解析树。

SELECT
    |-- student_name
FROM
    |-- students
WHERE
    |-- student_id < 3
        |-- student_id
        |-- 3

• 预处理器，它会去除语句中的注释部分，以避免注释对后续的分析和执行产生干扰；处理特殊字符和转义：确保特殊字符被正确处理和解释；进行一些简单的语法检查和预处理：例如检查表中的列是否存在，表是否存在，检查是否存在不符合规范的字符组合等。

优化器

优化器的主要作用是为给定的SQL语句生成最优的执行计划，生成一个预计执行成本最低、效率最高的执行计划，具体来说，优化器会考虑以下几个方面来确定最佳执行计划：

1. 表的连接方式：
   • 对于涉及多个表的查询，决定是使用嵌套循环连接、哈希连接还是排序合并连接等。例如，如果查询涉及 students 表和 courses 表，并且两个表通过 student_id 关联，优化器会根据表的大小、索引等情况决定采用哪种连接方式。
2. 索引的使用：
   • 评估是否使用索引以及使用哪个索引能够更快地获取数据。对于上述的示例 SELECT student_name FROM students where student_id < 3，student_name和student_id上都有索引，但student_id为主键索引，效率高于普通索引，所以优化器会选择使用该索引来快速定位满足条件的数据。
3. 执行顺序：
   • 确定各个操作的执行顺序，以最小化中间结果集的大小和计算成本。比如先执行筛选条件还是先进行表连接。
4. 子查询的优化：
   • 优化子查询的执行方式，可能将其转换为连接操作或者其他更高效的形式。

通过综合考虑这些因素，优化器能够辅助我们调整SQL，从而提高数据库的响应性能，减少资源消耗。但主要的SQL语句优化还是需要我们自己完成。

执行器

根据优化器生成的执行计划，执行器会实际执行对数据库的操作，并将结果返回给客户端。

具体来说，对于示例 SELECT student_name FROM students WHERE student_id < 3 ，执行器的工作包括：

1. 按照执行计划确定的数据读取方式，从存储引擎中获取数据。例如，如果优化器决定使用索引，执行器会通过索引快速定位到满足条件的数据所在的磁盘位置，并读取数据。
2. 执行筛选操作：根据 WHERE 子句中的条件 student_id < 3，对读取的数据进行逐行判断，过滤出符合条件的数据。
3. 提取所需的列：按照 SELECT 子句指定的 student_name 列，从符合条件的行中提取出相应的数据。
4. 将最终的结果返回给客户端：将处理得到的结果集返回给发起查询的客户端应用程序。

至此，这个语句就执行完毕了。