测试干货！数据库性能调优之慢SQL优化

最新推荐文章于 2024-06-27 21:02:16 发布

美团程序员

最新推荐文章于 2024-06-27 21:02:16 发布

阅读量647

点赞数 15

分类专栏：技术分享软件测试自动化测试文章标签：数据库 sql adb

本文链接：https://blog.csdn.net/qq_73332379/article/details/139031102

版权

软件测试同时被 3 个专栏收录

1065 篇文章 8 订阅

订阅专栏

自动化测试

829 篇文章 9 订阅

订阅专栏

技术分享

389 篇文章 0 订阅

订阅专栏

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

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

例如如下SQL语句

SELECT department_id FROM employee WHERE name = 'Lucy' AND age > 18
GROUP BY department_id

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

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

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

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

4.分析器：

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

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

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

7.存储引擎（如InnoDB）：

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

8.执行器：

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

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

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

二、查询SQL关键字执行顺序

执行顺序，如下：

1、对存储引擎的操作

（1）FROM：用于查询SQL的数据表。执行器会根据优化器选择的执行计划从存储引擎中获取相关表的数据。

（2）ON：与JOIN一起使用，用于指定连接条件。执行器会根据ON给定的条件条件从存储引擎获取匹配条件的记录。如果连接条件涉及到索引列，存储引擎会使用索引进行优化。

（3）JOIN：指定表之间连接方式（如INNER JOIN,LEFT JOIN等）。执行器会根据优化器选择的执行计划，从存储引擎中获取连接表数据。然后执行器根据JOIN连接类型和ON连接条件，对数据连接处理。

（4）WHERE：执行器对从存储引擎返回的数据进行过滤，只保留满足WHERE子句条件的记录。过滤条件如有索引，存储引擎层会通过索引过滤后返回。

2、对返回结果集的操作

（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是索引。我们按照时间顺序来分析一下

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

2.查询缓存：MySQL服务器在处理查询之前，会先检查查询缓存。如果查询缓存中已经存在该结果集，服务器将直接返回缓存中的结果。

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

4.优化器：根据解析器提供的信息生成执行计划。优化器会分析多种可能的执行策略，并选择成本最低的策略。在这个示例中，优化器会选择age索引和subject_id与score的联合索引。对于连接操作，优化器还要决定连接策略，例如是否使用Nested-Loop Join或Hash Join等一些连接策略。优化器还会根据表的大小、索引、查询条件和统计信息来决定哪张表作为驱动表，以及选择最佳的连接策略。例如，如果两个表的大小差异很大，Nested-Loop Join可能是一个好的选择，而对于大小相似的两个表，Hash Join或Sort-Merge Join可能更加高效。

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

6.存储引擎（如InnoDB）：管理数据存储和检索。存储引擎首先接收来自执行器的请求，该请求可能是基于优化器的执行计划。

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

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

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

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

我们再以全局视野来分析一下

1.确定驱动表: 首先，MySQL优化器会选择一个表作为"驱动表"。通常，返回记录数较少的表会被选为驱动表。假设employee_score表中满足subject_id = 3 AND score > 80条件的记录数量较少，那么这张表可能被选为驱动表。这是优化器的工作，它预估哪个表作为驱动表更为高效，制定执行计划。虽然驱动表的选择很大程度上是基于预估的返回记录数，但实际选择还会受其他因素影响，例如表之间的连接类型、可用的索引等。

2.使用驱动表的索引进行筛选: 优化器会首先对驱动表进行筛选。如果employee_score是驱动表，优化器会使用subject_id和score的联合索引来筛选出subject_id = 3 AND score > 80的记录。这是执行器按照优化器的计划向存储引擎发出请求，获取需要的数据。存储引擎负责访问索引，并根据索引定位到实际的数据页，从而获取数据行。

3.连接操作: 执行器会基于上一步从驱动表中筛选出的记录对另一个表（即employee表）进行连接。这时，执行器会使用employee表上的索引（如id索引）来高效地找到匹配的记录。

4.一步的筛选: 在连接的过程中，执行器会考虑employee表的其他筛选条件，如age > 18，通常连接后才过滤筛选，这也是执行器的工作，执行器在连接过程中或之后，根据优化器制定的计划进一步筛选结果集。但是这里employee表的age索引其叶子节点包含age和主键id信息，在进行连接时，可以直接按照age范围扫描该索引，利用其叶子节点中的id信息进行高效的JOIN操作，因此在连接时就完成筛选，这个过程由MySQL优化器自动完成。从上面可以看到，当存在可以被利用的索引时，MySQL可以在连接过程中执行这些过滤操作。

5.返回结果: 这是执行器最后的步骤，返回最终的查询结果。

四、总结

本文采用一张简单的架构图说明了MySQL查询中使用的组件和组件间关系。

解析了一条sql语句从客户端请求mysql服务器到返回给客户端的整个生命周期流程。

列举了单表sql、关联表sql 两种不同SQL在整个生命周期中的执行顺序以及内部组件逻辑关系。

通过如上案例的解析可以让开发者们掌握到单表sql、关联表sql的底层sql知识，为理解慢sql的产生和优化鉴定基础。