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

embedded/2024/9/22 19:21:28/

sql_0">定位慢sql

sql_2">工具排查慢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_45">分析慢sql

profile详情

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

启用 Profiling

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

sql>mysql">SET profiling = 1;

执行查询

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

sql>mysql">SELECT * FROM your_table WHERE some_column = 'some_value';

查看 Profile 列表

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

sql>mysql">SHOW PROFILES;

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

查看详细的 Profile 信息

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

sql>mysql">SHOW PROFILE FOR QUERY query_id;

查看CPU信息

sql>mysql">SHOW PROFILE CPU FOR QUERY query_id;

explain执行计划

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

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

基本概念

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

sql>mysql">explain + sql语句;

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

sql>mysql">+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| 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_184">优化慢sql

sql_186">sql优化方案

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

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

深分页优化查询

传统分页

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

sql>mysql">SELECT * FROM table_name ORDER BY column_name LIMIT 10 OFFSET 1000;

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

深分页

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

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

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

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

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

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

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

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


http://www.ppmy.cn/embedded/90711.html

相关文章

使用uniapp+Django开发的在线工具网站

引言 在当今数字化时代,在线工具网站为用户提供了便捷的服务和功能,本文分享了我使用UniApp和Django开发的一款多平台在线工具网站。通过这个项目,我探索了跨平台开发与强大的后端框架结合的优势,实现了用户友好的界面和稳健的功…

24/8/4算法笔记 梯度下降

通过迭代地调整参数,沿着目标函数梯度的反方向(即最陡峭的下降方向)进行搜索,从而找到函数的局部最小值。 导入库 import matplotlib.pyplot as plt import numpy as np 构建方程和导数 #构建方程 f lambda x:(x-3.5)**2-4.…

【微服务】springboot 整合 SA-Token 使用详解

目录 一、前言 二、认证与授权介绍 2.1 什么是认证 2.1.1 认证的目的 2.1.2 认证基本步骤 2.2 什么是授权 2.2.1 常用的授权模型 三、微服务中常用的认证安全框架 3.1 Spring Security 3.1.1 Spring Security 特点 3.2 JWT (JSON Web Tokens) 3.2.1 JWT特点 3.3 其…

.xml文件和.xsd文件

.xml 文件和 .xsd 文件举例说明 .xml 文件示例 这是一个简单的 XML 文件示例,表示一本书的详细信息: <?xml version="1.0" encoding="UTF-8"?> <!-- 定义书籍信息 --> <书籍 xmlns:xsi="http://www.w3.org/2001/XMLSchema-inst…

java基础--字符串用法

一、前言&#xff08;在java中字符串的重要性&#xff09; 在 Java 编程中&#xff0c;字符串&#xff08;String&#xff09;的重要性不言而喻&#xff0c;它几乎贯穿于所有的应用程序和系统中。以下是 Java 中字符串重要性的几个方面&#xff1a; 1. 数据表示和处理 文本数…

将本地的业务写成成可供RPC远程调用的方法

第一步&#xff1a;首先我们先定义proto文件&#xff0c;这些proto文件将会为远程调用者提供调用的方法&#xff0c;为login方法。 2.重写UserServiceRpc类中的Login方法。 在Login中做的操作主要是&#xff0c;得到requst里面的参数&#xff0c;然后调用本地的Login方法&#…

Linux 下 Anaconda/Miniconda 环境安装教程

安装 Anaconda 步骤 1: 下载 Anaconda 访问 Anaconda 官方网站: Anaconda Distribution 选择 Linux 版: 点击下载相应的 Linux 版本。 在 terminal 中下载: 你可以直接在终端使用 wget 命令。 wget https://repo.anaconda.com/archive/Anaconda3-2023.07-Linux-x86_64.sh请确…

单例模式保证了一个类只有一个实例,并提供一个访问它的全局访问点,该实例被所有程序模块共享。解释其实现思路

实现思路 私有化构造函数 将类的构造函数声明为私有&#xff0c;防止外部代码使用 new 运算符直接创建类的实例。这一步确保了只有类自身能创建实例&#xff0c;从而保证实例的唯一性。 使用类的私有静态指针变量指向类的唯一实例 在类的内部声明一个静态指针变量&#xff0c;用…