来源 | OSCHINA 社区

作者 | 华为云开发者联盟-砖业洋_

原文链接：https://my.oschina.net/u/4526289/blog/8703122

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

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

• 
  

• 解析查询语句，检查语法。

• 验证表名和列名的正确性。

• 生成查询树。

• 
  

• 负责实际执行索引扫描，如在 student 表的 name 索引上进行等值查询，因查询全部列，涉及到回表访问磁盘。

• 在访问磁盘之前，先检查 InnoDB 的缓冲池（Buffer Pool）中是否已有所需的数据页。如果缓冲池中有符合条件的数据页，直接使用缓存的数据。如果缓冲池中没有所需的数据页，从磁盘加载数据页到缓冲池中。

• 
  

• 对于每个找到的记录，再次判断记录是否满足索引条件 name。这是因为基于索引条件加载到内存中是数据页，数据页中也有可能包含不满足索引条件的记录，所以还要再判断一次 name 条件，满足 name 条件则继续判断 age > 18 过滤条件。

• 根据 class_no 对满足条件的记录进行分组。

• 执行器将处理后的结果集返回给客户端。

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

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 表进行索引扫描（使用 age 索引），然后对 score 表进行索引扫描（使用 student_id 和 subject 的联合索引）。

• 存储引擎会根据请求查询相应的索引结构。在 student 表中，存储引擎会找到满足 age > 18 条件的记录。在 score 表中，存储引擎会找到满足 subject = 'math' AND score > 80 条件的记录。

• 一旦找到了满足条件的记录，存储引擎需要将这些记录所在的数据页从磁盘加载到内存中。存储引擎首先检查缓冲池（InnoDB Buffer Pool），看这些数据页是否已经存在于内存中。如果已经存在，则无需再次从磁盘加载。如果不存在，存储引擎会将这些数据页从磁盘加载到缓冲池中。

• 加载到缓冲池中的记录可以被多个查询共享，这有助于提高查询效率。

• 
  

• 在内存中执行连接操作，将 student 表和 score 表的数据行连接起来。

• 对连接后的结果集进行过滤，只保留满足查询条件（age > 18、subject = 'math'、score > 80）的数据行。

• 将过滤后的数据行作为查询结果返回给客户端。

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

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;

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;

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

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 进行过滤，将结果集返回给客户端。

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 组合结果。将连接后的结果返回给客户端。

1. 当单表过滤后的数据量较小时，查询 2 可能是一个更好的选择，因为它可以减少关联操作的数据量，从而提高查询效率。子查询阶段，MySQL 依然会利用原始表上的索引进行过滤。子查询执行完成后，将过滤后的数据存储在临时表中。所以查询 2 的方式可以优化的点就是在单表查询时尽可能的利用索引。

2. 当单表过滤后的数据量较大时，查询 1 可能更合适，因为它可以更好地利用索引进行关联操作。这样可以减少关联操作的时间开销，查询 2 因为临时表不继承索引，表关联的时间开销比较大。

5. 聚集索引和全表扫描有什么区别呢？

首个中文医学知识LLM：真正的赛华佗—华驼(HuaTuo)