【慢SQL性能优化】 一条SQL的生命周期 | 京东物流技术团队

一、 一条简单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. 分析器:

◦ 解析查询语句,检查语法。

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

◦ 生成查询树。

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

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

  3. 存储引擎(如InnoDB):

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

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

  1. 执行器:

◦ 对于每个找到的记录,再次判断记录是否满足索引条件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_idscore是联合索引,age是索引。 我们按照时间顺序来分析一下

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

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

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

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

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

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

◦ 存储引擎首先接收来自执行器的请求。请求可能包括获取满足查询条件的数据行,以及使用哪种扫描方法(如全表扫描或索引扫描)。

◦ 假设执行器已经决定使用索引扫描。在这个示例中,存储引擎可能会先对employee表进行索引扫描(使用age索引),然后对employee_score表进行索引扫描(使用subject_idscore的联合索引)。

◦ 存储引擎会根据请求查询相应的索引。在employee索引中会找到满足age > 18条件的记录。在employee_score索引中找到满足subject_id = 3 AND score > 80条件的记录。

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

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

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

◦ 在内存中执行连接操作,将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_idscore的联合索引来筛选出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的产生和优化鉴定基础。

作者:京东物流 高峰

来源:京东云开发者社区 自猿其说Tech 转载请注明来源

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/121211.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

k8s 目录和文件挂载到宿主机

k8s生产中常用的volumes挂载方式有:hostPath、pv,pvc、nfs 1.hostPath挂载 hostPath是将主机节点文件系统上的文件或目录挂载到Pod 中,同时pod中的目录或者文件也会实时存在宿主机上,如果pod删除,hostpath中的文…

线性代数 | 矩阵运算 加减 数乘 矩阵的幂运算

文章目录 1. 矩阵加减和数乘2.矩阵与矩阵的乘法2.1相乘条件:看中间,取两头2.2 相乘计算方法 3. 矩阵的幂3.1 观察归纳法3.2 邻项相消法3.3 化为对角 4.矩阵求逆(除法)4.1 判断是否可逆(证明题或者要求求出逆矩阵&#…

centos 7部署Mysql8.0主从

Mysql官网中关于部署主从的网址 环境准备: 搭建虚拟机和安装Mysql之前的文章中已经涉及,在此不再赘述。 主从IPMysql账号密码主192.168.213.4root/Root1234!从192.168.213.5root/Root1234! 1、主数据库设置 配置my.cnf 一般存放于/etc/。 主从配…

VSCode修改主题为Eclipse 绿色护眼模式

前言 从参加开发以来,一直使用eclipse进行开发,基本官方出新版本,我都会更新。后来出来很多其他的IDE工具,我也尝试了,但他们的主题都把我劝退了,黑色主题是谁想出来?😂 字体小的时…

【原创分享】LDO电源PCB设计要点

LDO模块是Low Drop-Out的缩写,也称为低压差稳压器。它是一种电子组件,主要用于将高电压降至较低电压,并提供稳定的电源供应。LDO模块通常由一个直流电压调节器和一个电流放大器组成。其工作原理是通过在输入和输出之间创建一个稳定的电压差&a…

Linux 中断实验

一.什么是中断 中断是指 CPU 在执行程序的过程中,出现了某些突发事件急待处理, CPU 必须暂停当前程序的执行,转去处理突发事件,处理完毕后又返回原程序被中断的位置继续执行。由于中断的存在极大的提高了 CPU 的运行效率&#x…

拓展认知边界:如何给大语言模型添加额外的知识

Integrating Knowledge in Language Models P.s.这篇文章大部分内容来自Stanford CS224N这门课Integrating Knowledge in Language Models这一节😁 为什么需要给语言模型添加额外的知识 1.语言模型会输出看似make sense但实际上不符合事实的内容 语言模型在生成…

Springboot集成redis和mybatis-plus及websocket异常框架代码封装

在软件开发过程中,一款封装完善简洁大气的全家桶框架,能大大提升开发人员的工作效率,同时还能降低代码的复杂程序,也便于后期方便维护。本文所涉及源代码在文章最后,有下载链接。 本文章所涉及封装的框架,…

Android修行手册-实现利用POI将图片插入到Excel中(文末送书)

点击跳转>Unity3D特效百例点击跳转>案例项目实战源码点击跳转>游戏脚本-辅助自动化点击跳转>Android控件全解手册点击跳转>Scratch编程案例点击跳转>软考全系列 👉关于作者 专注于Android/Unity和各种游戏开发技巧,以及各种资源分享&…

C# 查询腾讯云直播流是否存在的API实现

应用场景 在云考试中,为防止作弊行为的发生,会在考生端部署音视频监控系统,当然还有考官方监控墙系统。在实际应用中,考生一方至少包括两路直播流: (1)前置摄像头:答题的设备要求使…

Spring Boot + EasyUI Datebox和Datetimebox样例

使用EasyUI的Datebox和Datetimebox组件,并对其进行适当的改造,比如更改日期格式、设置默认值或者将当前时间设置为默认值。 一、运行结果 二、实现代码 1.代码框架 2.实现代码 SpringBootMainApplication.java: package com.xj.main;import org.spri…

奇安信360天擎getsimilarlist存在SQL注入漏洞

奇安信360天擎getsimilarlist存在SQL注入漏洞 一、产品描述二、漏洞描述三、漏洞复现1.手动复现2.自动化复现①nulei扫描yaml ②小龙POC检测工具下载地址 免责声明:请勿利用文章内的相关技术从事非法测试,由于传播、利用此文所提供的信息或者工具而造成的…

oled显示器程序(IIC)从stm32f103移植到stm32f429出现bug不显示-解决移植失败问题

出现问题处: 刚开始更换了这两行代码,然后更换位置后,oled正常显示,如下为正确顺序 I2C_Configuration();//配置CPU的硬件I2COLED_Init();//OLED初始化 在这段代码中,I2C_Configuration() 函数用于配置CPU的硬件 I2C…

什么变量能够影响苦艾酒的味道?

没有一个答案可以描述每种苦艾酒的味道,因为每个生产商生产的苦艾酒都不一样。甜苦艾酒的味道与干苦艾酒不同,即使在这些类别中,甜的和干的苦艾酒的味道也会彼此不同,这取决于制造商、他们使用的草药和植物药的类型、他们用这些植…

如何估算业务需要多少代理IP量?

在互联网相关的行业中,很多业务都需要用到代理IP工具,比如数据采集、市场调查、SEO优化、品牌保护、跨境运营等,可以说代理IP已成为许多业务中不可或缺的一部分。代理IP可以帮助用户隐蔽真实IP地址,提高网络活动的范围和安全性&am…

会打字就能编程,自动写代码的ai助手 | 通义灵码

通义灵码介绍 通义灵码是一款由阿里云出品的智能编码辅助工具。 它基于通义大模型,可以提供行级/函数级实时续写、自然语言生成代码、单元测试生成、代码注释生成、代码解释、研发智能问答、异常报错排查等能力。 它支持Java、Python、Go、C/C、JavaScript、Type…

机器人阻抗与导纳控制的区别

机器人自身的非线性动力学(由柔软性引起的)导致控制精度下降,因此难以描述准确的动力学。 导纳控制和阻抗控制都是基于位置与力关系的模式,被认为具有鲁棒性和安全性。然而,当机器人与刚体接触时,导纳控制常…

【Qt之QVariant】使用

介绍 QVariant类类似于最常见的Qt数据类型的联合。由于C禁止联合类型包括具有非默认构造函数或析构函数的类型,大多数有趣的Qt类不能在联合中使用。如果没有QVariant,则QObject::property()和数据库操作等将会受到影响。 QVariant对象同时持有一个单一…

基于单片机的多层电梯控制仿真系统

**单片机设计介绍, 基于单片机的多层电梯控制仿真系统 文章目录 一 概要二、功能设计设计思路 三、 软件设计原理图 五、 程序六、 文章目录 一 概要 基于单片机的多层电梯控制仿真系统是一个复杂的系统,它需要结合单片机技术、控制理论、电子技术以及人…

使用swagger-typescript-api

引言 前后端分离大致是这样的 后端:控制层 / 业务层 / 数据操作层前端:控制层 / 视图层 前后端的控制层,实际上就是前后端接口的对接 前后端分离,实现了更好地解耦合,但也引入了接口对接的过程,这个过程…