摘要：一条SQL如何被MySQL架构中的各个组件操作执行的，执行器做了什么？存储引擎做了什么？表关联查询是怎么在存储引擎和执行器被分步执行的？本文带你探探究竟！
本文分享自华为云社区《一条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）：
8.执行器：
在整个查询执行过程中，这些组件共同协作以高效地执行查询。客户端负责发送查询，连接器管理客户端连接，查询缓存尝试重用先前查询结果，解析器负责解析查询，优化器选择最佳执行计划，执行器执行优化器选择的计划，存储引擎（如InnoDB）负责管理数据存储和访问。这些组件的协同作用使得MySQL能够高效地执行查询并返回结果集。
根据索引列过滤条件加载索引的数据页到内存这个操作是存储引擎做的。加载到内存中之后，执行器会进行索引列和非索引列的过滤条件判断。
根据执行顺序，如下：
（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子句中指定的限制条件对查询结果进行截断，只返回部分记录
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）：存储引擎负责管理数据的存储和检索。
7.执行器：处理连接、排序、聚合、过滤等操作。
前面说过，根据存储引擎根据索引条件加载到内存的数据页有多数据，可能有不满足索引条件的数据，如果执行器不再次进行索引条件判断， 则无法判断哪些记录满足索引条件的，虽然在存储引擎判断过了，但是在执行器还是会有索引条件age > 18、subject = 'math'、score > 80的判断。
先看例子
查询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;
当查询包含索引列的条件时，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
从这里我们可以看出，查询2是先过滤后连接，每张表的索引都很重要，如果没设置好索引，单表过滤会全表扫描。
写SQL的时候，查询1和查询2到底采用哪种方式呢？
根据不同情况各有应用场景，需要注意的是，对于查询2，子查询的结果集被存储在一个临时表中，临时表不会继承原始索引，包括聚集索引和非聚集索引，所以刚刚的例子中，临时表中s.id和sc.student_id已经不是任何索引列了。对于查询1，最终满足关联条件s.id = sc.student_id的所有记录都会被加载到内存后再进行过滤。
走 PRIMARY索引（聚集索引）和全表扫描有什么区别 呢？准确来说，使用InnoDB存储引擎的情况下，全表扫描的数据和聚集索引的数据在InnoDB表空间中的存储位置是相同的，也就是说它们的内存地址也是相同的。所以你也可以理解为，他们其实都是在聚集索引上操作的（聚集索引B+树的叶子结点是根据主键排好序的完整的用户记录，包含表里的所有字段），区别就在于
全表扫描将聚集索引B+树的叶子结点从左到右依次顺序扫描并判断条件。
聚集索引是利用二分思想将聚集索引B+树到指定范围区间进行扫描，比如select * from demo_info where id in (1, 2)这种条件字段是主键id，可以很好的利用PRIMARY索引进行二分的快速查询。
在MyISAM中，全表扫描的数据和索引数据的存储位置是分开的。然而MyISAM已经被InnoDB取代，不再是MySQL的推荐存储引擎，从MySQL5.5开始，InnoDB就成了MySQL的默认存储引擎。
默认情况下，InnoDB使用一个名为ibdata1的共享表空间文件存储所有的数据和索引，包括聚集索引和二级索引（又称非聚集索引或辅助索引）。