当前位置: 首页 > news >正文

【MySQL】慢sql优化全流程解析

定位慢sql

工具排查慢sql

  • 调试工具:Arthas
  • 运维工具:Skywalking

通过以上工具可以看到哪个接口比较慢,并且可以分析SQL具体的执行时间,定位到哪个sql出了问题。

启用慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。

MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

# 开启MySQL慢日志查询开关
slow_query_log=1# 设置慢日志的时间为1秒,SQL语句执行时间超过1秒,就会视为慢查询,记录到慢查询日志中
long_query_time=2

配置完成后,重启MySQL服务保证配置生效。

慢查询日志一般的返回结果如下:

# Time:2024-08-01T12:00:00.123456Z
# User@Host: root[root] @ localhost [] Id: 	8
# Query time:2.345678 Lock_time:0.012345 Rows sent:10 Rows examined: 100
SET timestamp=1650000000;
SELECT * FROM orders WHERE status ='pending" ORDER BY gmt created DEsc;

需要关注以下内容:

  • Query_time(查询时间):查询执行的总时间,单位为秒。是关键的指标,用于判断查询的性能。

  • Lock_time(锁定时间):表被锁定的时间,单位为秒。可以帮助判断是否存在锁等待问题。

  • Rows_sent(发送的行数):查询返回的行数。

  • Rows_examined(检查的行数):查询过程中检查的行数,用于判断查询的效率。

分析慢sql

profile详情

SHOW PROFILE 是 MySQL 提供的一种用于查看查询语句执行的详细步骤和资源消耗的工具。使用 SHOW PROFILE 命令可以帮助找出查询语句的瓶颈,优化查询性能。

启用 Profiling

在使用 SHOW PROFILE 之前,需要先启用 Profiling:

SET profiling = 1;

执行查询

执行你想分析的查询语句:

SELECT * FROM your_table WHERE some_column = 'some_value';

查看 Profile 列表

使用以下命令查看刚才执行的查询的 Profile:

SHOW PROFILES;

这将显示一个查询 ID 列表及其对应的查询语句和总执行时间。

查看详细的 Profile 信息

使用 SHOW PROFILE 查看某个查询 ID 的详细信息:

SHOW PROFILE FOR QUERY query_id;

查看CPU信息

SHOW PROFILE CPU FOR QUERY query_id;

explain执行计划

explain 是 MySQL 提供的一种用于分析和调试 SQL 查询的工具。

通过使用 explain,可以了解 MySQL 在执行查询时采用的具体执行计划,包括访问数据表的方式、使用的索引、连接表的顺序等信息。这些信息对于优化查询性能至关重要。

基本概念

EXPLAIN 执行计划支持 SELECTDELETEINSERTREPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句,要获取一条sql语句的执行计划,只需要在语句前加上explain关键字即可。

explain + sql语句;

执行计划的返回结果一般是这样的:

+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

返回结果中各字段的含义解释如下:

列名含义
idSELECT 查询的序列标识符
select_typeSELECT 关键字对应的查询类型
table用到的表名
partitions匹配的分区,对于未分区的表,值为 NULL
type表的访问方法
possible_keys可能用到的索引
key实际用到的索引
key_len所选索引的长度
ref当使用索引等值查询时,与索引作比较的列或常量
rows预计要读取的行数
filtered按表条件过滤后,留存的记录数的百分比
Extra附加信息

字段释意

id

查询的序列标识符,用于表示查询的执行顺序。值越大,优先级越低,执行顺序越靠后。

select_type

查询的类型,主要用于区分普通查询、联合查询、子查询等复杂的查询,常见的值有:

  • SIMPLE: 简单查询,不包含子查询或 UNION。
  • PRIMARY: 最外层的 SELECT 查询。
  • SUBQUERY: 子查询中的第一个 SELECT。
  • DERIVED: 派生表(子查询中的 FROM 子句)。
  • UNION: UNION 操作中的第二个或后续的 SELECT 查询。
  • UNION RESULT: UNION 的结果集。
table

查询用到的表名。

type(重要)

查询执行的类型,描述了查询是如何执行的。常见的类型如下,这些类型的性能从最优到最差排序为:system > const > eq_ref > ref > range > index > ALL

  • system:如果表使用的引擎对于表行数统计是精确的(如:MyISAM),且表中只有一行记录的情况下,访问方法是 system ,是 const 的一种特例。
  • const:表中最多只有一行匹配的记录,一次查询就可以找到,常用于使用主键或唯一索引的所有字段作为查询条件。
  • eq_ref:当连表查询时,前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式,常用于使用主键或唯一索引的所有字段作为连表条件。
  • ref:使用普通索引作为查询条件,查询结果可能找到多个符合条件的行。
  • range:对索引列进行范围查询,执行计划中的 key 列表示哪个索引被使用了。
  • index:查询遍历了整棵索引树,与 ALL 类似,只不过扫描的是索引,而索引一般在内存中,速度更快。
  • ALL:全表扫描。
possible_keys

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ,则表示没有索引可以使用。

key(重要)

key 列表示 MySQL 实际使用到的索引。如果为 NULL,则表示未用到索引。

Extra(重要)

这列包含了 MySQL 解析查询的额外信息,通过这些信息,可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下:

  • Using index:表明查询使用了覆盖索引,不用回表,查询效率非常高。
  • Using index condition:表示查询优化器选择使用了索引下推这个特性。
  • Using where:表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
  • Using filesort:在排序时使用了文件排序而不是索引排序,通常是因为无法使用索引进行排序。
  • Using temporary:MySQL 需要创建临时表来存储查询的结果,常见于 ORDER BY 和 GROUP BY。
  • Using join buffer (Block Nested Loop):连表查询的方式,表示当被驱动表的没有使用索引的时候,MySQL 会先将驱动表读出来放到 join buffer 中,再遍历被驱动表与驱动表进行查询。

优化慢sql

sql优化方案

根据explain执行计划的返回结果,我们可以根据以下字段进行sql优化:

  • 通过keykey_len检査是否命中了索引(索引本身存在是否有失效的情况)
  • 通过type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或全表扫描
  • 通过extra字段判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复

深分页优化查询

传统分页

传统分页通常使用 OFFSETLIMIT 来实现

SELECT * FROM table_name ORDER BY column_name LIMIT 10 OFFSET 1000;

这种方法对于小数据集或页数较小时效果较好,但在数据量非常大的情况下,OFFSET 的值越大,数据库需要扫描的行数就越多,性能会急剧下降。

深分页

深分页通过避免使用 OFFSET 来提高性能

1.覆盖索引+子查询: 这种方法通过子查询使用覆盖索引快速定位到分页的起始位置,外部查询从该位置获取实际数据,避免大量数据扫描和回表操作。

如本例中通过子查询定位到了第100001页的起始位置,向后获取100行数据。

SELECT * FROM users WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1) LIMIT 100;

这种方法避免了大量数据扫描,适用于有索引列的情况。

2.存储分页结果: 另一种方法是将分页结果存储在缓存(如 Redis)或临时表中,从而避免频繁查询数据库。例如:

-- 第一次查询并缓存结果
SELECT * FROM table_name ORDER BY column_name LIMIT 1000;
-- 将结果缓存起来,随后从缓存中进行分页

这种方法适用于需要多次访问相同分页结果的场景。

相关文章:

【MySQL】慢sql优化全流程解析

定位慢sql 工具排查慢sql 调试工具:Arthas运维工具:Skywalking 通过以上工具可以看到哪个接口比较慢,并且可以分析SQL具体的执行时间,定位到哪个sql出了问题。 启用慢查询日志 慢查询日志记录了所有执行时间超过指定参数(lon…...

RabbitMQ高级特性 - 消息分发(限流、负载均衡)

文章目录 RabbitMQ 消息分发概述如何实现消费分发机制(限制每个队列消息数量)使用场景限流背景实现 demo 非公平发送(负载均衡)背景实现 demo RabbitMQ 消息分发 概述 RabbitMQ 的队列在有多个消费者订阅时,默认会通过…...

信号处理——自相关和互相关分析

1.概括 在信号处理中,自相关和互相关是相关分析非常重要的概念,它们能分析一个信号或两个信号在时间维度的相似性,在振动测试分析、雷达测距和声发射探伤得到了广泛的应用。自相关分析的研究对象为一个信号,互相关分析的研究对象…...

如何解决部分设备分辨率不适配

1)如何解决部分设备分辨率不适配 2)Unity中如何实现草的LOD 3)使用了Play Asset Delivery提交版本被Google报错 4)如何计算弧线弹道的落地位置 这是第396篇UWA技术知识分享的推送,精选了UWA社区的热门话题,…...

C#插件 调用存储过程(输出参数类型)

存储过程 CREATE PROCEDURE [dbo].[GetSum]num1 INT,num2 INT,result INT OUTPUT AS BEGINselect result num1 num2 END C#代码 using Kingdee.BOS; using Kingdee.BOS.App.Data; using Kingdee.BOS.Core.Bill.PlugIn; using Kingdee.BOS.Util; using System; using System.…...

代码随想录算法训练营day32 | 509. 斐波那契数 、70. 爬楼梯 、746. 使用最小花费爬楼梯

碎碎念:开始动态规划了!加油! 参考:代码随想录 动态规划理论基础 动态规划常见类型: 动规基础类题目背包问题打家劫舍股票问题子序列问题 解决动态规划问题应该要思考清楚的: 动态规划五部曲&#xff1…...

【人工智能专栏】Learning Rate Decay 学习率衰减

Learning Rate Decay 学习率衰减 使用格式 optimizer = torch.optim.SGD(model.paraters(), lr=0.1, momentum=0.9, weight_decay=1e-4) scheduler = torch.optim...

浙大版《C语言程序设计(第3版)》题目集

练习4-11 统计素数并求和 本题要求统计给定整数M和N区间内素数的个数并对它们求和。 输入格式: 输入在一行中给出两个正整数M和N(1≤M≤N≤500)。 输出格式: 在一行中顺序输出M和N区间内素数的个数以及它们的和,数字间以空格分隔。 输入…...

【学习笔记】Day 2

一、进度概述 1、inversionnet_train_light 试运行——未成功 2、DL-FWI基础入门培训-1,2,以及作业1的完成——暂未完成作业 二、详情 1、inversionnet_train_light 试运行 在补充完相关依赖后,运行仍有报错 产生原因:这个代码在当…...

Java中的Map(如果想知道Java中有关Map的知识点,那么只看这一篇就足够了!)

前言:在Java编程语言中,集合框架(Collection Framework)提供了一系列用于存储和操作数据的接口和类。其中,Map和Set是两个非常重要的接口,分别用于存储键值对和无重复元素的集合。 ✨✨✨这里是秋刀鱼不做梦…...

裸金属服务器详解

在云计算飞速发展的今天,裸金属服务器(Bare Metal Server, BMS)作为一种兼具传统物理服务器性能和虚拟化服务优势的计算资源,正逐渐成为企业和个人用户的重要选择。今天我们就来了解下关于裸金属服务器的定义、核心特点以及其在各…...

等待唤醒机制两种实现方法-阻塞队列

桌子上有面条-》吃货执行 桌子上没面条-》生产者制造执行 1、消费者等待 消费者先抢到CPU执行权,发现桌子上没有面条,于是变成等待wait状态,并释放CPU执行权,此时的CPU肯定会被厨师抢到,初始开始做面条,…...

数组项相加和 – 如何将 JavaScript 数组中的数字相加

JavaScript 中的数组是一个对象,它允许您在单个变量名称下存储多个值的有序集合,并以多种方式操作这些值。 在本文中,您将学习如何使用几种不同的方法计算给定数组中所有数字的总和。 具体来说,使用以下方法得到数组中所有数字的总…...

C#和S7-1200PLC S7.NET通信

1、一步步建立一个C#项目 一步步建立一个C#项目(连续读取S7-1200PLC数据)_s7协议批量读取-CSDN博客文章浏览阅读1.7k次,点赞2次,收藏4次。这篇博客作为C#的基础系列,和大家分享如何一步步建立一个C#项目完成对S7-1200PLC数据的连续读取。首先创建一个窗体应用。_s7协议批量…...

常用命令git branch

Git Branch 命令总结 列出分支 git branch&#xff1a;显示本地分支&#xff0c;当前分支会被标记。git branch -r&#xff1a;显示远程分支。git branch -a&#xff1a;显示所有本地和远程分支。 创建分支 git branch <branch_name>&#xff1a;创建一个新分支但不自…...

Android 制作系统签名

一、切换目录 cd build/target/product/security二、执行命令 1)将使用.pk8生成platform.priv.pem (.pem即可,文件名可随意修改)openssl pkcs8 -in platform.pk8 -inform DER -outform PEM -out platform.pem -nocrypt2)生成.p12,此时需输入两次密码,并且要记住 -name后所设置…...

C语言第13篇

1.下面程序是计算n个数的平均值,请填空.______ #include<stdio.h> void main( ) { int i,n; float x,avg0.0; scanf("%d",&n); for(i0;i<n;i) { scanf("%f",&x); avgavg______; } avg________; printf("avg%f\n",avg); } A) …...

基于FPGA的数字信号处理(22)--进位保存加法器(Carry Save Adder, CSA)

目录 1、拆解多个数的加法 2、进位保存加法器 3、CSA的优点和缺点 4、CSA电路的实现 文章总目录点这里&#xff1a;《基于FPGA的数字信号处理》专栏的导航与说明 1、拆解多个数的加法 考虑3个4bits数相加&#xff0c;10 4 7 21 的过程是这样的&#xff1a; 其中的红色数…...

idea使用free流程,2024idea、2023idea都可以安装免费使用

1.先到官网下载&#xff0c;这里选择win系统的&#xff0c;点击下图的.exe https://www.jetbrains.com/idea/download/?sectionwindows 2.下载好后基本上就是一直点击“下一步”到直到安装好&#xff0c;安装好后先打开软件后关闭退出 3.下载配配套资料 链接: https://pan.ba…...

设计模式 之 —— 抽象工厂模式

目录 什么是抽象工厂模式&#xff1f; 定义 特点 抽象工厂模式&#xff08;java代码示例&#xff09; 首先定义第一个接口 实现第一个接口的类 定义第二个接口 实现第二个接口的类 * 创建抽象工厂类 创建扩展了 AbstractFactory 的工厂类 饮料工厂 食物工厂 * 创建一个…...

Spring Boot 实现流式响应(兼容 2.7.x)

在实际开发中&#xff0c;我们可能会遇到一些流式数据处理的场景&#xff0c;比如接收来自上游接口的 Server-Sent Events&#xff08;SSE&#xff09; 或 流式 JSON 内容&#xff0c;并将其原样中转给前端页面或客户端。这种情况下&#xff0c;传统的 RestTemplate 缓存机制会…...

三维GIS开发cesium智慧地铁教程(5)Cesium相机控制

一、环境搭建 <script src"../cesium1.99/Build/Cesium/Cesium.js"></script> <link rel"stylesheet" href"../cesium1.99/Build/Cesium/Widgets/widgets.css"> 关键配置点&#xff1a; 路径验证&#xff1a;确保相对路径.…...

微软PowerBI考试 PL300-在 Power BI 中清理、转换和加载数据

微软PowerBI考试 PL300-在 Power BI 中清理、转换和加载数据 Power Query 具有大量专门帮助您清理和准备数据以供分析的功能。 您将了解如何简化复杂模型、更改数据类型、重命名对象和透视数据。 您还将了解如何分析列&#xff0c;以便知晓哪些列包含有价值的数据&#xff0c;…...

浪潮交换机配置track检测实现高速公路收费网络主备切换NQA

浪潮交换机track配置 项目背景高速网络拓扑网络情况分析通信线路收费网络路由 收费汇聚交换机相应配置收费汇聚track配置 项目背景 在实施省内一条高速公路时遇到的需求&#xff0c;本次涉及的主要是收费汇聚交换机的配置&#xff0c;浪潮网络设备在高速项目很少&#xff0c;通…...

深入浅出深度学习基础:从感知机到全连接神经网络的核心原理与应用

文章目录 前言一、感知机 (Perceptron)1.1 基础介绍1.1.1 感知机是什么&#xff1f;1.1.2 感知机的工作原理 1.2 感知机的简单应用&#xff1a;基本逻辑门1.2.1 逻辑与 (Logic AND)1.2.2 逻辑或 (Logic OR)1.2.3 逻辑与非 (Logic NAND) 1.3 感知机的实现1.3.1 简单实现 (基于阈…...

GitFlow 工作模式(详解)

今天再学项目的过程中遇到使用gitflow模式管理代码&#xff0c;因此进行学习并且发布关于gitflow的一些思考 Git与GitFlow模式 我们在写代码的时候通常会进行网上保存&#xff0c;无论是github还是gittee&#xff0c;都是一种基于git去保存代码的形式&#xff0c;这样保存代码…...

淘宝扭蛋机小程序系统开发:打造互动性强的购物平台

淘宝扭蛋机小程序系统的开发&#xff0c;旨在打造一个互动性强的购物平台&#xff0c;让用户在购物的同时&#xff0c;能够享受到更多的乐趣和惊喜。 淘宝扭蛋机小程序系统拥有丰富的互动功能。用户可以通过虚拟摇杆操作扭蛋机&#xff0c;实现旋转、抽拉等动作&#xff0c;增…...

WebRTC从入门到实践 - 零基础教程

WebRTC从入门到实践 - 零基础教程 目录 WebRTC简介 基础概念 工作原理 开发环境搭建 基础实践 三个实战案例 常见问题解答 1. WebRTC简介 1.1 什么是WebRTC&#xff1f; WebRTC&#xff08;Web Real-Time Communication&#xff09;是一个支持网页浏览器进行实时语音…...

深入浅出Diffusion模型:从原理到实践的全方位教程

I. 引言&#xff1a;生成式AI的黎明 – Diffusion模型是什么&#xff1f; 近年来&#xff0c;生成式人工智能&#xff08;Generative AI&#xff09;领域取得了爆炸性的进展&#xff0c;模型能够根据简单的文本提示创作出逼真的图像、连贯的文本&#xff0c;乃至更多令人惊叹的…...

Ubuntu系统复制(U盘-电脑硬盘)

所需环境 电脑自带硬盘&#xff1a;1块 (1T) U盘1&#xff1a;Ubuntu系统引导盘&#xff08;用于“U盘2”复制到“电脑自带硬盘”&#xff09; U盘2&#xff1a;Ubuntu系统盘&#xff08;1T&#xff0c;用于被复制&#xff09; &#xff01;&#xff01;&#xff01;建议“电脑…...