网站怎么做网络推广,手机广西网,网站推广软文范例,企业网站推广方法一、 一条简单SQL在MySQL执行过程
一张简单的图说明下#xff0c;MySQL架构有哪些组件和组建间关系#xff0c;接下来给大家用SQL语句分析 例如如下SQL语句
SELECT department_id FROM employee WHERE name Lucy AND age 18
GROUP BY department_id其中name为索引MySQL架构有哪些组件和组建间关系接下来给大家用SQL语句分析 例如如下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、对存储引擎的操作
1FROM用于查询SQL的数据表。执行器会根据优化器选择的执行计划从存储引擎中获取相关表的数据。
2ON 与JOIN一起使用用于指定连接条件。执行器会根据ON给定的条件条件从存储引擎获取匹配条件的记录。如果连接条件涉及到索引列存储引擎会使用索引进行优化。
3JOIN指定表之间连接方式如INNER JOIN,LEFT JOIN等。执行器会根据优化器选择的执行计划从存储引擎中获取连接表数据。然后执行器根据JOIN连接类型和ON连接条件对数据连接处理。
4WHERE执行器对从存储引擎返回的数据进行过滤只保留满足WHERE子句条件的记录。过滤条件如有索引存储引擎层会通过索引过滤后返回。
2、对返回结果集的操作
5GROUP BY执行器对满足WHERE条件的记录按照GROUP BY指定的列分组。
6HAVING执行器在执行分组后根据HAVING条件对分组后的记录再次过滤。
7SELECT执行器根据优化器选择的执行计划和指定列获取查询结果。
8DISTINCT执行器对查询结果进行去重只返回不重复的记录。
9ORDER BY执行器对查询结果按照ORDER BY子句中指定的列进行排序。
10LIMIT执行器根据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的数据行。
◦ 将过滤后的数据行作为查询结果返回给客户端。 前面说过根据存储引擎根据索引条件加载到内存的数据页有多数据可能有不满足索引条件的数据如果执行器不再次进行索引条件判断 则无法判断哪些记录满足索引条件的虽然在存储引擎判断过了但是在执行器还是会有索引条件 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可以在连接过程中执行这些过滤操作。返回结果: 这是执行器最后的步骤返回最终的查询结果。
四、总结
本文采用一张简单的架构图说明了MySQL查询中使用的组件和组件间关系。
解析了一条sql语句从客户端请求mysql服务器到返回给客户端的整个生命周期流程。
列举了单表sql、关联表sql 两种不同SQL在整个生命周期中的执行顺序和及内部组件逻辑关系。
通过如上案例的解析可以让开发者们掌握到单表sql、关联表sql的底层sql知识为理解慢sql的产生和优化鉴定基础。 作者京东物流 高峰 来源京东云开发者社区 自猿其说Tech 转载请注明来源
