Mysql--基础知识点--86--慢查询_mysql执行慢 exectuting-CSDN博客

本文链接：https://blog.csdn.net/Chasing__Dreams/article/details/139603819

1 慢查询监控与分析工具

启用慢查询日志：设置long_query_time并分析
使用Performance Schema：监控SQL执行细节
第三方工具：如Percona Toolkit、pt-query-digest等
可视化监控：使用Prometheus+Grafana等方案

2 慢查询解决方案考虑方向

当面对MySQL慢查询问题时，可以从以下几个方面进行系统性的分析和优化：

2.1 查询优化

复杂查询

对于非常复杂的查询，尤其是包含多个子查询和联合操作的查询，考虑将其拆分为多个简单的查询，或者使用临时表。这样可以减少 MySQL 的查询优化器的压力，并可能提高查询效率。

嵌套查询

嵌套子查询可以考虑改成关联查询

事务

避免使用长事务

select

避免select *
select 列时使用覆盖索引的方式

count

使用count * 而不是count 具体的列

join

在处理多表连接时，选择合适的连接方式可以显著影响查询性能。尽量使用 INNER JOIN 而不是 OUTER JOIN，除非必须返回没有匹配数据的记录。
确定ON或者USING子句中是否有索引。

limit 分页

当偏移量大时，使用where 索引列名>偏移量

group by、order by

使用索引列
如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
关联查询时，确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引
对于联合索引，也需要符合最左匹配原则

union

使用union all 而不是union

where

基本时索引失效的一些场景Mysql索引失效的场景中除了10 group by外

2.2 索引优化

创建索引注意些什么
MySQL进阶之（六）索引的使用与设计原则中的6.5

2.3 数据库配置优化

缓冲池大小：调整innodb_buffer_pool_size
排序缓冲区：优化sort_buffer_size等参数
连接数配置：适当调整max_connections和thread_cache_size
日志配置：平衡慢查询日志开销与监控需求

2.4 架构层面优化

读写分离：将读请求分流到从库
分库分表：对大数据量表进行水平或垂直拆分
缓存层引入：使用Redis等缓存热点数据
异步处理：将非实时需求操作异步化

2.5 硬件与系统层面

存储优化：使用SSD替代HDD
内存扩容：增加服务器内存减少磁盘I/O
CPU资源：确保有足够的CPU处理能力
系统参数：调整文件描述符限制、网络参数等

2.6 应用层优化

减少交互次数：批量操作替代单条操作
合理使用事务：避免长事务和过大事务
连接池管理：合理配置和使用数据库连接池
ORM框架优化：检查生成的SQL语句效率。一般不考虑用的基本都是第三方包或库。

3 具体排查过程

2 慢查询解决方案考虑方向 中的方案会在以下的排查过程中体现

1 判断是不是网络、接口问题。接口问题应该从 该文档2.6应用层优化 考虑
2 若不是网络或接口问题，查看服务器性能cpu、内存、硬盘。若mysql的cpu很高则表示读写频率高，若网站的访问量又不高，则可能是(1)mysql参数问题；(2)linux系统参数；(3)mysql的sql语句查询慢的问题。
2.1 mysql参数的问题，采取措施：(i) 调整缓存池大小， mysql8.0可以不用考虑，8.0已经去掉了查询缓存 (ii)调整连接数大小。
2.2 linux系统参数，采取措施：(i) 调整文件句柄数 (ii) 调整tcp连接TIMEWAIT状态相关参数；MySQL–基础知识点–55–MySQL优化中系统内核优化部分
2.3 mysql的sql语句查询慢问题，采取措施：
(i) 使用show processlist; 查询当前活动连接，可以显现出谁在执行sql，以及正在执行的sql执行了多长时间。
(ii) 使用慢查询日志查看慢查询sql [@1 查询慢查询日志三个相关参数：show variables like ‘query’; @2 三个慢查询日志参数：slow_query_time 慢查询时间阈值，超过这个时间的查询的查询语句会被记录到慢查询日志里；slow_query_log 是否打开慢查询，值为off/on；slow_query_log_file 慢查询日志的位置；@3 启用慢查询 set global slow_query_log=ON @4 摸拟一条慢查询 select * from edu_user where id=1 and sleep(60);] @4 mysqldumpslow 分析慢查询日志，访问最慢的sql、查询频次最高的sql等。
(iii) 使用profiles 可以查看是具体哪一步耗时，一条sql 语句执行有多个步骤，见MySQL–基础知识点–53–profile分析SQL语句性能消耗中的starting–cleaning up，主要关注其中optimizing、executing、Sending data这三个阶段。

optimizing 慢：检查执行计划、更新统计信息、优化索引
executing 慢：优化SQL写法、添加缺失索引、避免全表扫描
Sending data 慢：限制返回字段、分页优化、网络调优

(iv) explain分析慢查询sql执行任务，通过查看 EXPLAIN 的输出，你可以了解查询的性能瓶颈，并据此进行优化。并且可以根据trace【MYSQL 索引分析工具——trace】的结果进行更详细的分析。例如，如果你发现查询没有使用索引，或者查询了过多的行，那么你可能需要重新考虑索引策略，或者修改查询以使其更高效。
(v)根据(iv)的分析结果进行优化：[1]索引优化：添加合适的索引[2]查询语句优化
3 若以上都没问题，考虑数据量大的问题。可以从架构层考虑：(i) 读写分离；(ii) 应用级别的缓存；(iii)分库分表分区。

补充：

mysqldumpslow是MySQL提供的一个命令行工具，主要用于分析MySQL的慢查询日志（slow query log）。以下是关于mysqldumpslow的详细介绍和使用方法：

一、基本用途
分析慢查询日志：从慢查询日志中提取信息，并以易读的方式展示最耗时的查询，以便进一步分析和优化。
提高性能：帮助开发人员和数据库管理员识别和优化数据库中的慢查询，提高系统的性能和响应时间。
二、使用方法
查看慢查询日志
在命令行中输入mysqldumpslow /path/to/slowquery.log，以查看整个慢查询日志文件中的所有慢查询。
排序选项
-s参数用于指定排序方式，可以按查询时间、锁等待时间、返回行数等进行排序。
-s t：按时间顺序（从最新到最旧）排序。
-s at：按查询时间长短排序，显示查询时间最长的慢查询。
-s al：按锁等待时间长短排序。
-s ar：按返回行数排序。
指定显示的记录数
-t NUM参数用于指定显示多少条慢查询记录。默认情况下，mysqldumpslow将显示前10条慢查询记录。
过滤特定的查询
使用-g PATTERN参数进行正则匹配，筛选出与特定模式相关的慢查询。
其他选项
-v：输出debug信息。
-d：指定输出排序规则，包括多种排序方式的组合。
-r：输出结果反序排序，默认是降序（desc）排序。
-a：不将数字抽象为N、字符抽象为S。
-n NUM：将超过N个数字的数值字符抽象显示。
-l：总时间包含锁定时间。
三、注意事项
在使用mysqldumpslow之前，需要确保MySQL的慢查询日志功能已经开启，并且慢查询日志文件存在且可读取。
mysqldumpslow工具通常与MySQL数据库服务器一起安装，无需单独安装。
虽然mysqldumpslow提供了基本的慢查询分析功能，但对于更复杂的性能问题，可能需要结合其他工具和方法进行综合分析和优化。
通过合理地使用mysqldumpslow工具，可以有效地识别和优化MySQL数据库中的慢查询，提高系统的整体性能和响应速度。

EXPLAIN 是 MySQL 中的一个关键字，用于分析 SQL 查询的执行计划。当你对查询性能有疑问，或者想知道 MySQL 是如何执行某个查询时，EXPLAIN 可以提供非常有价值的信息。

使用 EXPLAIN 的基本语法是：
EXPLAIN SELECT ... FROM ... WHERE ...;
eg: EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
当你执行一个带有 EXPLAIN 的查询时，MySQL 不会实际执行这个查询，而是返回一个关于查询执行划的表格。这个表格包含了多个列，每个列都提供了关于查询执行的不同方面的信息。
想知道 MySQL 是如何执行某个查询时，EXPLAIN 可以提供非常有价值的信息。

以下是一些常见的 EXPLAIN 输出列及其描述：
想知道 MySQL 是如何执行某个查询时，EXPLAIN 可以提供非常有价值的信息。

id：查询标识符，每个 SELECT（和某些子查询）都会有一个唯一的 ID。
select_type：查询的类型（例如 SIMPLE、SUBQUERY、UNION 等）。
table：查询涉及的表。
type：连接类型（例如 ALL、index、range、ref、eq_ref、const 等），它告诉你 MySQL 如何连接表。ref 和 const 通常是好的，而 ALL 则可能表示性能问题。
possible_keys：查询可能使用的索引。
key：实际使用的索引（如果 MySQL 决定使用索引的话）。
key_len：使用的索引的长度。
ref：哪些列或常量被用作索引查找的引用。
rows：MySQL 估计需要检查的行数（注意，这只是一个估计值）。
Extra：包含 MySQL 解决查询的附加信息（例如使用了哪些文件排序、使用了临时表等）。
想知道 MySQL 是如何执行某个查询时，EXPLAIN 可以提供非常有价值的信息。

通过查看 EXPLAIN 的输出，你可以了解查询的性能瓶颈，并据此进行优化。例如，如果你发现查询没有使用索引，或者查询了过多的行，那么你可能需要重新考虑索引策略，或者修改查询以使其更高效。