【慢SQL性能优化】一条SQL的生命周期

清疚 2024-05-23 22:27 66阅读 0赞

## 【慢SQL性能优化】 一条SQL的生命周期 ##

### 一条简单SQL在MySQL执行过程 ###

*  一张简单的图说明下，MySQL架构有哪些组件和组建间关系，接下来给大家用SQL语句分析
 *  ![在这里插入图片描述][de6054a865cd4e21840642495d7e5eda.png]
 *  例如如下SQL语句
 *      SELECT department_id FROM employee WHERE name = 'Lucy' AND age > 18 
        GROUP BY department_id
 *  其中`name`为索引，我们按照**时间顺序**来分析一下
 *  客户端：如MySQL命令行工具、Navicat、DBeaver或其他应用程序发送SQL查询到MySQL服务器。
 *  连接器：负责与客户端建立连接、管理连接和维护连接。当客户端连接到`MySQL`服务器时，连接器验证客户端的用户名和密码，然后分配一个线程来处理客户端的请求。
 *  查询缓存：查询缓存用于缓存先前执行过的查询及其结果。当收到新的查询请求时，`MySQL`首先检查查询缓存中是否已有相同的查询及其结果。如果查询缓存中有匹配的查询结果，`MySQL`将直接返回缓存的结果，而无需再次执行查询。但是，如果查询缓存中没有匹配的查询结果，`MySQL`将继续执行查询。
 *  分析器：
    
     *  解析查询语句，检查语法。
     *  验证表名和列名的正确性。
     *  生成查询树。
 *  优化器：分析查询树，考虑各种执行计划，估算不同执行计划的成本，选择最佳的执行计划。在这个例子中，优化器可能会选择使用`name`索引进行查询，因为`name`是索引列。
 *  执行器：根据优化器选择的执行计划，向存储引擎发送请求，获取满足条件的数据行。
 *  存储引擎（如`InnoDB`）：
    
     *  负责实际执行索引扫描，如在`employee`表的`name`索引上进行等值查询，因查询全部列，涉及到回表访问磁盘。
     *  在访问磁盘之前，先检查`InnoDB`的缓冲池（`Buffer Pool`）中是否已有所需的数据页。如果缓冲池中有符合条件的数据页，直接使用缓存的数据。如果缓冲池中没有所需的数据页，从磁盘加载数据页到缓冲池中。
 *  执行器：
    
     *  对于每个找到的记录，再次判断记录是否满足索引条件`name`。这是因为基于索引条件加载到内存中是数据页，数据页中也有可能包含不满足索引条件的记录，所以还要再判断一次`name`条件，满足`name`条件则继续判断`age > 18`过滤条件。
     *  根据`department_id`对满足条件的记录进行分组。
     *  执行器将处理后的结果集返回给客户端。
 *  在整个查询执行过程中，这些组件共同协作以高效地执行查询。客户端负责发送查询，连接器管理客户端连接，查询缓存尝试重用先前查询结果，解析器负责解析查询，优化器选择最佳执行计划，执行器执行优化器选择的计划，存储引擎（如`InnoDB`）负责管理数据存储和访问。这些组件的协同作用使得`MySQL`能够高效地执行查询并返回结果集。

### 查询SQL关键字执行顺序 ###

*  执行顺序，如下：
 *  **对存储引擎的操作**
 *  （1） `FROM`：用于查询SQL的数据表。执行器会根据优化器选择的执行计划从存储引擎中获取相关表的数据。
 *  （2）`ON`： 与`JOIN`一起使用，用于指定连接条件。执行器会根据`ON`给定的条件条件从存储引擎获取匹配条件的记录。如果连接条件涉及到索引列，存储引擎会使用索引进行优化。
 *  （3）`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`子句中指定的限制条件对查询结果进行截断，只返回部分记录

### 表关联查询SQL在MySQL中的执行过程 ###

*      SELECT s.id, s.name, s.age, es.subject, es.score 
        FROM employee s JOIN employee_score es ON s.id = es.employee_id 
        WHERE s.age >18 AND es.subject_id =3 AND es.score >80;
 *  这个例子中，`subject_id`和`score`是联合索引，`age`是索引。 我们按照**时间顺序**来分析一下
 *  连接器：当客户端连接到`MySQL`服务器时，连接器负责建立和管理连接。它验证客户端提供的用户名和密码，确定客户端具有相应的权限，然后建立连接。
 *  查询缓存：`MySQL`服务器在处理查询之前，会先检查查询缓存。如果查询缓存中已经存在该结果集，服务器将直接返回缓存中的结果。
 *  解析器：解析并检查`SQL`语法正确性。解析器会将查询语句分解成多个组成部分，例如表、列、条件等。在这个示例中，解析器会识别出涉及的表（`employee`和`employee_score`）以及需要的列（`id、name、age、subject、score`）。
 *  优化器：根据解析器提供的信息生成执行计划。优化器会分析多种可能的执行策略，并选择成本最低的策略。在这个示例中，优化器会选择`age`索引和`subject_id`与`score`的联合索引。对于连接操作，优化器还要决定连接策略，例如是否使用`Nested-Loop Join`或`Hash Join`等一些连接策略。优化器还会根据表的大小、索引、查询条件和统计信息来决定哪张表作为驱动表，以及选择最佳的连接策略。例如，如果两个表的大小差异很大，`**Nested-Loop Join**`可能是一个好的选择，而对于大小相似的两个表，`**Hash Join**`**或**`**Sort-Merge Join**`可能更加高效。
 *  执行器：根据优化器生成的执行计划执行查询，向存储引擎发送请求，获取满足条件的数据行。
 *  存储引擎（如`InnoDB`）：管理数据存储和检索。存储引擎首先接收来自执行器的请求，该请求可能是基于优化器的执行计划。
    
     *  存储引擎首先接收来自执行器的请求。请求可能包括获取满足查询条件的数据行，以及使用哪种扫描方法（如全表扫描或索引扫描）。
     *  假设执行器已经决定使用索引扫描。在这个示例中，存储引擎可能会先对`employee`表进行索引扫描（使用`age`索引），然后对`employee_score`表进行索引扫描（使用`subject_id`和`score`的联合索引）。
     *  存储引擎会根据请求查询相应的索引。在`employee`索引中会找到满足`age > 18`条件的记录。在`employee_score`索引中找到满足`subject_id = 3 AND score > 80`条件的记录。
     *  一旦找到了满足条件的记录，存储引擎需要将这些记录所在的数据页从磁盘加载到内存中。存储引擎首先检查缓冲池（`InnoDB Buffer Pool`），看这些数据页是否已经存在于内存中。如果已经存在，则无需再次从磁盘加载。如果不存在，存储引擎会将这些数据页从磁盘加载到缓冲池中
     *  加载到缓冲池中的记录可以被多个查询共享，这有助于提高查询效率。
 *  执行器：处理连接、排序、聚合、过滤等操作。
    
     *  在内存中执行连接操作，将`employee`表和`employee_score`表的数据行连接起来。
     *  对连接后的结果集进行过滤，只保留满足查询条件（`age > 18、subject_id = 3、score > 80`）的数据行。
     *  将过滤后的数据行作为查询结果返回给客户端。
 *  我们再以**全局视野来分析**一下
 *  确定驱动表: 首先，`MySQL`优化器会选择一个表作为"驱动表"。通常，返回记录数较少的表会被选为驱动表。假设`employee_score`表中满足`subject_id = 3 AND score > 80`条件的记录数量较少，那么这张表可能被选为驱动表。这是优化器的工作，它预估哪个表作为驱动表更为高效，制定执行计划。虽然驱动表的选择很大程度上是基于预估的返回记录数，但实际选择还会受其他因素影响，例如表之间的连接类型、可用的索引等。
 *  使用驱动表的索引进行筛选: 优化器会首先对驱动表进行筛选。如果`employee_score`是驱动表，优化器会使用`subject_id`和`score`的联合索引来筛选出`subject_id = 3 AND score > 80`的记录。这是执行器按照优化器的计划向存储引擎发出请求，获取需要的数据。存储引擎负责访问索引，并根据索引定位到实际的数据页，从而获取数据行。
 *  连接操作: 执行器会基于上一步从驱动表中筛选出的记录对另一个表（即`employee`表）进行连接。这时，执行器会使用`employee`表上的索引（如`id`索引）来高效地找到匹配的记录。
 *  进一步的筛选: 在连接的过程中，执行器会考虑`employee`表的其他筛选条件，如`age > 18`，通常连接后才过滤筛选，这也是执行器的工作，执行器在连接过程中或之后，根据优化器制定的计划进一步筛选结果集。但是这里`employee`表的`age`索引其叶子节点包含`age`和主键`id`信息，在进行连接时，可以直接按照`age`范围扫描该索引，利用其叶子节点中的`id`信息进行高效的`JOIN`操作，因此在连接时就完成筛选，这个过程由`MySQL`优化器自动完成。从上面可以看到，当存在可以被利用的索引时，`MySQL`可以在连接过程中执行这些过滤操作。
 *  返回结果: 这是执行器最后的步骤，返回最终的查询结果。

[de6054a865cd4e21840642495d7e5eda.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/23/758d1743fd2448d0b28691e6c9de5bc1.png