一条SQL在MySQL架构中的执行过程

1. 单表查询SQL在MySQL架构中的各个组件的执行过程

简单用一张图说明下，MySQL架构有哪些组件，接下来给大家用SQL语句分析

假如SQL语句是这样

SELECT class_no FROM student WHERE name = 'lcy' AND age > 18 GROUP BY class_no

其中name为索引，我们按照时间顺序来分析一下

1.客户端：客户端（如MySQL命令行工具、Navicat、MySQL Workbench或其他应用程序）发送SQL查询到MySQL服务器。

2.连接器：连接器负责与客户端建立连接、管理连接和维护连接。当客户端连接到MySQL服务器时，连接器验证客户端的用户名和密码，然后分配一个线程来处理客户端的请求。

3.查询缓存：查询缓存用于缓存先前执行过的查询及其结果。当收到新的查询请求时，MySQL首先检查查询缓存中是否已有相同的查询及其结果。如果查询缓存中有匹配的查询结果，MySQL将直接返回缓存的结果，而无需再次执行查询。但是，如果查询缓存中没有匹配的查询结果，MySQL将继续执行查询。查询缓存在MySQL 8.0中已被移除，不详细解释。

4.分析器：

• 解析查询语句，检查语法。
• 验证表名和列名的正确性。
• 生成查询树。

5.优化器：分析查询树，考虑各种执行计划，估算不同执行计划的成本，选择最佳的执行计划。在这个例子中，优化器可能会选择使用name索引进行查询，因为name是索引列。

6.执行器：根据优化器选择的执行计划，向存储引擎发送请求，获取满足条件的数据行。

7.存储引擎（如InnoDB）：

• 负责实际执行索引扫描，如在student表的name索引上进行等值查询，因查询全部列，涉及到回表访问磁盘。
• 在访问磁盘之前，先检查InnoDB的缓冲池（Buffer Pool）中是否已有所需的数据页。如果缓冲池中有符合条件的数据页，直接使用缓存的数据。如果缓冲池中没有所需的数据页，从磁盘加载数据页到缓冲池中。

8.执行器：

• 对于每个找到的记录，再次判断记录是否满足索引条件name。这是因为基于索引条件加载到内存中是数据页，数据页中也有可能包含不满足索引条件的记录，所以还要再判断一次name条件，满足name条件则继续判断age > 18过滤条件。
• 根据class_no对满足条件的记录进行分组。
• 执行器将处理后的结果集返回给客户端。

在整个查询执行过程中，这些组件共同协作以高效地执行查询。客户端负责发送查询，连接器管理客户端连接，查询缓存尝试重用先前查询结果，解析器负责解析查询，优化器选择最佳执行计划，执行器执行优化器选择的计划，存储引擎（如InnoDB）负责管理数据存储和访问。这些组件的协同作用使得MySQL能够高效地执行查询并返回结果集。

根据索引列过滤条件加载索引的数据页到内存这个操作是存储引擎做的。加载到内存中之后，执行器会进行索引列和非索引列的过滤条件判断。

2. SELECT的各个关键字在哪里执行？

根据执行顺序，如下：

（1）FROM：FROM子句用于指定查询所涉及的数据表。在查询执行过程中，执行器需要根据优化器选择的执行计划从存储引擎中获取指定表的数据。

（2）ON：ON子句用于指定连接条件，它通常与JOIN子句一起使用。在查询执行过程中，执行器会根据ON子句中的条件从存储引擎获取满足条件的记录。如果连接条件涉及到索引列，存储引擎可能会使用索引进行优化。

（3）JOIN：JOIN子句用于指定表之间的连接方式（如INNER JOIN, LEFT JOIN等）。在查询执行过程中，执行器会根据优化器选择的执行计划，从存储引擎中获取需要连接的表的数据。然后，执行器根据JOIN子句的类型和ON子句中的连接条件，对数据进行连接操作。

（4）WHERE：执行器对从存储引擎返回的数据进行过滤，只保留满足WHERE子句条件的记录。部分过滤条件如果涉及到索引，在存储引擎层就已经进行了过滤。

（5）GROUP BY：执行器对满足WHERE子句条件的记录按照GROUP BY子句中指定的列进行分组。

（6）HAVING：执行器在进行分组后，根据HAVING子句条件对分组后的记录进行进一步过滤。

（7）SELECT：执行器根据优化器选择的执行计划来获取查询结果。

（8）DISTINCT：执行器对查询结果进行去重，只返回不重复的记录。

（9）ORDER BY：执行器对查询结果按照ORDER BY子句中指定的列进行排序。

（10）LIMIT：执行器根据LIMIT子句中指定的限制条件对查询结果进行截断，只返回部分记录

3. 表关联查询SQL在MySQL架构中的各个组件的执行过程

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

这个例子中，student_id和subject是联合索引，age是索引。

我们按照时间顺序来分析一下

1.连接器：当客户端连接到MySQL服务器时，连接器负责建立和管理连接。它验证客户端提供的用户名和密码，确定客户端具有相应的权限，然后建立连接。

2.查询缓存：MySQL服务器在处理查询之前，会先检查查询缓存。如果查询缓存中已经存在相同的查询及其结果集，服务器将直接返回缓存中的结果，而不再执行后续的查询处理。由于查询缓存在MySQL 8.0中已被移除，我们在这个示例中不再详细讨论。

3.解析器：解析器的主要任务是解析SQL查询语句，确保查询语法正确。解析器会将查询语句分解成多个组成部分，例如表、列、条件等。在这个示例中，解析器会识别出涉及的表（student和score）以及需要的列（id、name、age、subject、score）。

4.优化器：优化器的职责是根据解析器提供的信息生成执行计划。它会分析多种可能的执行策略，并选择成本最低的策略。在这个示例中，优化器可能会分析各种表扫描和索引扫描的组合，最终选择一种成本最低的执行计划。

5.执行器：根据优化器生成的执行计划处理查询，向存储引擎发送请求，获取满足条件的数据行。

6.存储引擎（如InnoDB）：存储引擎负责管理数据的存储和检索。

• 存储引擎首先接收来自执行器的请求。请求可能包括获取满足查询条件的数据行，以及使用哪种扫描方法（如全表扫描或索引扫描）。
• 假设执行器已经决定使用索引扫描。在这个示例中，存储引擎可能会先对student表进行索引扫描（使用age索引），然后对score表进行索引扫描（使用student_id和subject的联合索引）。
• 存储引擎会根据请求查询相应的索引结构。在student表中，存储引擎会找到满足age > 18条件的记录。在score表中，存储引擎会找到满足subject = 'math' AND score > 80条件的记录。
• 一旦找到了满足条件的记录，存储引擎需要将这些记录所在的数据页从磁盘加载到内存中。存储引擎首先检查缓冲池（InnoDB Buffer Pool），看这些数据页是否已经存在于内存中。如果已经存在，则无需再次从磁盘加载。如果不存在，存储引擎会将这些数据页从磁盘加载到缓冲池中。
• 加载到缓冲池中的记录可以被多个查询共享，这有助于提高查询效率。

7.执行器：处理连接、排序、聚合、过滤等操作。

• 在内存中执行连接操作，将student表和score表的数据行连接起来。
• 对连接后的结果集进行过滤，只保留满足查询条件（age > 18、subject = 'math'、score > 80）的数据行。
• 将过滤后的数据行作为查询结果返回给客户端。

前面说过，根据存储引擎根据索引条件加载到内存的数据页有多数据，可能有不满足索引条件的数据，如果执行器不再次进行索引条件判断， 则无法判断哪些记录满足索引条件的，虽然在存储引擎判断过了，但是在执行器还是会有索引条件age > 18、subject = 'math'、score > 80的判断。

4. LEFT JOIN将过滤条件放在子查询中再关联和放在WHERE子句上有什么区别？

先看例子

查询1

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

查询2

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc 
ON s.id = sc.student_id

查询3

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id AND s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

先给出结论: 查询2和3是一样的，也就是过滤条件放在子查询中和放在on上面是一样的，后面就只讨论查询1、2，查询1和查询2是不一样的，过滤条件放在where子句中和放在子查询再关联查询出的结果也是有区别的。

分析一下

从运行结果来看，对于查询1

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

在这个查询中，首先执行LEFT JOIN，将student表和score表连接起来。连接操作是基于s.id = sc.student_id条件进行的。LEFT JOIN操作会保留左表（student表）中的所有行，即使它们在右表（score表）中没有匹配的行。如果右表中没有匹配的行，那么右表的列将显示为NULL。

然后，WHERE子句会过滤连接后的结果集，只保留那些满足s.age > 18 and sc.subject = 'math' and sc.score > 80条件的行。这意味着，右表为NULL的记录将被排除，因为右表的过滤条件sc.subject = 'math' and sc.score > 80条件不满足。

对于查询2：

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (select id, name, age from student where age > 18) s
LEFT JOIN (select subject, score from score where subject = 'math' AND score > 80) sc 
ON s.id = sc.student_id

在这个查询中，我们首先执行两个子查询。第一个子查询从student表中选择所有age > 18的行，而第二个子查询从score表中选择所有subject = 'math' and score > 80的行。这意味着，在进行连接操作之前，我们已经对两个表分别进行了过滤。

接下来，执行LEFT JOIN操作，将过滤后的s和sc子查询的结果集连接起来，基于s.id = sc.student_id条件。因为LEFT JOIN操作会保留左表（s子查询的结果集）中的所有行，右表为NULL的记录包含了。

结果差异：

查询1和查询2的主要区别在于WHERE子句和子查询的使用。查询1在连接操作后应用过滤条件，这可能导致右表为NULL的关联记录因为右表的过滤条件而被排除在外。而查询2在连接操作之前就已经过滤了表中的数据，这意味着查询结果会包含所有左表过滤条件的记录，以及右表过滤条件的记录和NULL的记录。

如果查询1想保留右表为NULL的记录，只需要改为WHERE s.age > 18 AND (sc.student_id is null OR (sc.subject = 'math' AND sc.score > 80));这样查询1和2会有相同的结果集。

我们分析一下这两个查询在MySQL架构中各个组件中执行的区别

对于查询1：

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
WHERE s.age > 18 AND sc.subject = 'math' AND sc.score > 80;

1. 连接器：客户端与服务器建立连接。
2. 查询缓存：检查缓存是否存在此查询的结果。如果有，直接返回结果。否则，继续执行。
3. 解析器：解析查询语句，检查语法是否正确。
4. 优化器：对查询进行优化，生成执行计划，决定连接和过滤条件的顺序等。
5. 执行器：开始请求执行查询。
6. 存储引擎（InnoDB）：从磁盘或者缓冲池读取满足条件的数据行（s.id = sc.student_id），因为是left join，所以即便sc.student_id为null也会被关联。
7. 执行器：将从存储引擎获取的数据行进行左连接，应用过滤条件s.age > 18 and sc.subject = 'math' and sc.score > 80进行过滤，将结果集返回给客户端。

当查询包含索引列的条件时，MySQL的存储引擎会首先利用索引在磁盘上定位到满足索引条件的记录。接着，将这些索引数据对应的数据页加载到内存中的缓冲池。然后，执行器在内存中对这些记录进行进一步的过滤，根据索引条件和非索引列的条件来过滤数据。

当查询涉及到非聚集索引时，需要回表的操作会导致聚集索引和非聚集索引都被加载到内存中。但是，如果查询只涉及到聚集索引（如主键查询），那么只需要加载聚集索引的数据页即可。

对于查询2

SELECT s.id, s.name, s.age, sc.subject, sc.score
FROM (SELECT id, name, age FROM student WHERE age > 18) s
LEFT JOIN (SELECT student_id, subject, score FROM score WHERE subject = 'math' AND score > 80) sc 
ON s.id = sc.student_id