本文分享自华为云社区《一条SQL怎么被MySQL架构中的各个组件操作履行的？》，作者：砖业洋__。

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

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

一条SQL如何被MySQL架构中的各个组件操作执行的

假如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

  1. 衔接器：客户端与服务器树立衔接。

  2. 查询缓存：查看缓存是否存在此查询的成果。假如有，直接回来成果。不然，持续履行。

  3. 解析器：解析查询句子，查看语法是否正确。

  4. 优化器：决议运用哪些索引进行查询优化，以及确定衔接次序。

  5. 履行器：开端恳求履行子查询。

  6. 存储引擎（InnoDB）：首要，对student表进行扫描，将满意条件s.age > 18的记载对应的数据页加载到缓冲池(假如缓冲池没有这个页的数据)。然后，运用subject = 'math' AND score > 80对score表进行扫描，将满意条件的记载对应的数据页加载到缓冲池(假如缓冲池没有这个页的数据)。

  7. 履行器：对从存储引擎获取的数据运用一切的过滤条件，过滤后的成果存入暂时表，履行主查询，从暂时表中获取数据，将s和sc进行左衔接，依据s.id = sc.student_id组合成果。将衔接后的成果回来给客户端。

从这里咱们能够看出，查询2是先过滤后衔接，每张表的索引都很重要，假如没设置好索引，单表过滤会全表扫描。

写SQL的时候，查询1和查询2究竟选用哪种办法呢？

依据不同状况各有运用场景，需求注意的是，关于查询2，子查询的成果集被存储在一个暂时表中，暂时表不会承继原始索引，包含集合索引和非集合索引，所以刚刚的比如中，暂时表中s.id和sc.student_id现已不是任何索引列了。关于查询1，终究满意相关条件s.id = sc.student_id的一切记载都会被加载到内存后再进行过滤。

  1. 当单表过滤后的数据量较小时，查询2或许是一个更好的挑选，由于它能够减少相关操作的数据量，从而进步查询功率。子查询阶段，MySQL仍然会运用原始表上的索引进行过滤。子查询履行完成后，将过滤后的数据存储在暂时表中。所以查询2的办法能够优化的点便是在单表查询时尽或许的运用索引。

  2. 当单表过滤后的数据量较大时，查询1或许更合适，由于它能够更好地运用索引进行相关操作。这样能够减少相关操作的时刻开支，查询2由于暂时表不承继索引，表相关的时刻开支比较大。

5. 集合索引和全表扫描有什么差异呢？

走 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的同享表空间文件存储一切的数据和索引，包含集合索引和二级索引（又称非集合索引或辅佐索引）。

点击关注，榜首时刻了解华为云新鲜技术~