当前位置: 首页> 娱乐> 明星 > MySQL如何定位慢查询以及使用EXPLAIN对SQL进行性能分析

MySQL如何定位慢查询以及使用EXPLAIN对SQL进行性能分析

时间:2025/7/11 1:10:56来源:https://blog.csdn.net/weixin_53216033/article/details/140004458 浏览次数:0次

在数据库的日常运维和开发中,SQL慢查询是一个常见且必须高度关注的问题。它不仅会影响用户体验,还可能对系统稳定性构成严重威胁。尤其是在数据量激增的情况下,如从几千到1万的数据增长到百万级,SQL查询的性能问题尤为突出。最近,我们在维护一个项目时,就遇到了这样一个挑战:某个接口的响应时间异常长,经过分析发现,问题出在SQL多表联查上。

SQL慢查询的原因

随着数据量从几千到1万快速增长到百万级,原本设计合理的SQL查询可能变得不再高效。其中,多表联查是一个常见的性能瓶颈。当多个表通过JOIN操作连接时,如果缺少合适的索引或查询条件,就可能导致数据库执行笛卡尔积操作,即返回两个表中所有可能的组合,这会消耗大量的计算资源和时间

定位慢查询

  1. 慢查询日志:大多数数据库系统都提供了慢查询日志功能,可以记录执行时间超过设定阈值的SQL语句。通过分析这些日志,我们可以快速定位到慢查询语句。
  2. 性能监控工具:使用数据库性能监控工具,可以实时监控SQL语句的执行情况,包括执行时间、扫描行数、返回行数等关键指标。这些工具通常还提供了图形化界面,方便我们直观地查看和分析数据。
  3. EXPLAIN命令:对于MySQL等数据库,我们可以使用EXPLAIN命令来查看SQL语句的执行计划,从而了解查询是如何被数据库执行的。通过分析执行计划,我们可以发现是否存在索引缺失、全表扫描等问题。

开启慢查询日志(MySQL为例)

在MySQL中,开启慢查询日志(Slow Query Log)可以识别那些执行时间超过预定阈值的查询。以下是如何在MySQL中开启慢查询日志的步骤和示例配置:

1.登录到MySQL服务器

登录到MySQL服务器。

mysql -u root -p

2.检查当前慢查询日志设置 

在MySQL命令行中,你可以查询@@slow_query_log变量来检查慢查询日志是否已经开启:

SHOW VARIABLES LIKE 'slow_query_log';

以及查询long_query_time变量来查看慢查询的阈值(以秒为单位): 

SHOW VARIABLES LIKE 'long_query_time';

 3.开启慢查询日志

 可以通过修改MySQL配置文件(my.cnfmy.ini,取决于你的操作系统和安装方式)来永久开启慢查询日志,或者在MySQL命令行中动态设置(但重启后失效):

动态设置(重启后失效):

SET GLOBAL slow_query_log = 1;  
SET GLOBAL long_query_time = 2;  -- 设置慢查询阈值为2秒

在配置文件中设置(需要重启MySQL服务):

编辑MySQL配置文件(例如/etc/mysql/my.cnf),添加下面几行:

[mysqld]  
slow_query_log = 1  
slow_query_log_file = /var/log/mysql/mysql-slow.log  -- 指定慢查询日志文件的路径  
long_query_time = 2  -- 设置慢查询阈值为2秒  
log_queries_not_using_indexes = 1  -- 可选,记录没有使用索引的查询

然后记得重启MySQL服务

sudo service mysql restart
查看慢查询日志

例:

# Time: 2024-06-27T01:19:58.175560Z
# User@Host: skip-grants user[root] @ localhost [::1]  Id:     3
# Query_time: 6.879824  Lock_time: 0.042471 Rows_sent: 1224444  Rows_examined: 3948281
use jksy_pingshan;
SET timestamp=1719451198;
SELECT * from visit_question_result vqr  join question q on q.id = vqr.question_id join anwser a on a.id = vqr.anwser_id;

1.时间戳和查询基本信息

每条记录的开头都有时间戳,如# Time: 2024-06-27T01:19:58.175560Z,这表示查询发生的精确时间,对分析查询发生的时间顺序很有帮助。接下来的“User@Host”信息说明了执行查询的用户和来源地址,例如skip-grants user[root] @ localhost [::1],表明是root用户从本地主机发起的查询。(注意这个时间是国际时间,不是北京时间)

2.查询时间统计

  • Query_time: 这个字段显示了查询的总执行时间(单位通常是秒),例如Query_time: 6.879824
  • Lock_time: 表示查询在等待锁上的总时间,如果这个值较高,可能意味着有锁竞争问题。
  • Rows_sent 和 Rows_examined: 分别代表查询返回了多少行数据以及在查找过程中检查了多少行。这两个值可以帮助评估查询效率,如果Rows_examined远大于Rows_sent,可能意味着索引使用不当或查询不够高效。

3. SQL查询内容

紧随其后的是实际执行的SQL语句,例如:

SELECT * from visit_question_result vqr  join question q on q.id = vqr.question_id join anwser a on a.id = vqr.anwser_id;

这部分信息让您直接看到导致日志记录的具体查询。 

4.特殊指令

SET timestamp=...; 这样的行出现在每个查询前,是用来设定查询的时间基准,用于在事务复制或其它需要时间一致性的场景中确保时间的一致性。

5.性能分析

在某些日志条目中,还会看到对INFORMATION_SCHEMA.PROFILING的查询,它提供了查询执行过程中的详细步骤及其耗时,是进行性能调优的重要参考。例如:

SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, ... FROM INFORMATION_SCHEMA.PROFILING ...

可以帮助识别查询中的哪个阶段最消耗时间,进而针对性优化。

删除慢查询日志
mysqladmin -uroot -p flush-logs slow

 使用mysqldumpslow工具进行MySQL性能分析

mysqldumpslow是MySQL自带的一个工具,用于解析慢查询日志,并输出按查询时间排序的查询列表。它可以从慢查询日志中提取出查询的摘要信息,并按照查询时间、查询次数或锁定时间等指标进行排序,从而帮助我们快速定位性能瓶颈。

使用mysqldumpslow的基本语法如下:

mysqldumpslow [options] [logfile ...]

其中,logfile是慢查询日志文件的路径。options是可选的参数,用于指定排序方式、输出格式等。

以下是一些常用的选项:

  • -s: 指定排序方式,如按查询时间(t)、锁定时间(l)、查询次数(c)等。
  • -t: 显示前N个查询。
  • -g: 正则表达式匹配,只显示符合指定模式的查询。
  • -v: 详细输出,显示查询的完整内容。

例如,如果你想按照查询时间对慢查询日志进行排序,并只显示前10个查询,可以使用以下命令:

mysqldumpslow -s t -t 10 /path/to/slow-query.log

mysqldumpslow的输出通常包含以下几个部分:

  • 查询时间: 查询执行的总时间。
  • 锁定时间: 查询在MySQL内部锁上表或行的时间。
  • 查询次数: 该查询在慢查询日志中出现的次数。
  • 查询语句: 实际的SQL查询语句。
如何利用mysqldumpslow进行性能调优?
  1. 定位问题查询:首先,使用mysqldumpslow找出最耗时的查询。这些查询通常是性能调优的重点。
  2. 分析查询语句:检查问题查询的SQL语句,看是否有优化的空间。例如,是否可以通过添加索引、修改查询逻辑或调整查询参数等方式来优化查询性能。
  3. 优化数据库结构:如果查询语句本身没有问题,那么可能需要考虑优化数据库结构。例如,可以通过调整表的设计、创建合适的索引或优化表的存储引擎等方式来提升性能。
  4. 调整MySQL配置:在某些情况下,可能需要调整MySQL的配置参数来优化性能。例如,可以增加缓存大小、调整线程池大小或启用某些性能优化选项等。
  5. 监控和持续调优:性能调优是一个持续的过程。在调优完成后,需要定期监控数据库的性能,并根据实际情况进行持续的调优。

使用Explain对SQL语句进行性能分析

MYSQL中有专门负责优化SELECT语句的优化器模块,主要功能:通过分析系统中收集到的统计信息,为客户端请求的Query提供它认为的最优执行计划,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,每个表采用什么顺序去访问。

当我们面对一个执行缓慢的SQL查询时,如何快速定位问题并找到优化方案呢?这时,MySQL的EXPLAIN命令就派上了用场。EXPLAIN是MySQL提供的一个分析查询的工具,它可以帮助我们分析SQL查询语句的性能瓶颈,以便我们进行针对性的优化。通过在查询语句前加上EXPLAIN关键字,MySQL会返回查询的执行计划,包括查询的每一步是如何进行的,以及是否使用了索引等信息。

EXPLAIN的使用方法

使用EXPLAIN命令非常简单,只需要在需要分析的SQL语句前加上EXPLAIN关键字就可以啦。例如:

EXPLAIN SELECT * FROM users WHERE username = 'JohnDoe';

 执行上述命令后,MySQL会返回查询的执行计划。 

注意:

EXPLAIN不会考虑Cache

EXPLAIN不可以显示在执行查询时候所作的各种优化工作

EXPLAIN不能告诉你关于触发器,存储过程的信息或者用户自定义函数对查询的影响情况 

EXPlAIN结果解读

  1. id:查询的标识符,每个SELECT(或子查询)都有一个唯一的标识符。
  2. select_type:查询的类型(SIMPLE、SUBQUERY、UNION等)。
  3. table:输出结果集的表。
  4. type:连接类型(system、const、eq_ref、ref、fulltext、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL等)。这一列非常重要,因为它表示了MySQL如何连接表。通常,我们希望看到的结果是constrefrangeindex,而不是ALL(全表扫描)。
  5. possible_keys:可能应用在这张表上的索引。注意这只是可能,并不一定会真正使用。
  6. key:实际使用的索引。如果没有使用索引,则显示为NULL。
  7. key_len:使用的索引的长度。在不使用所有的索引列的情况下,可以决定使用的长度。
  8. ref:显示哪些列或常量被用作索引查找上的条件。
  9. rows:MySQL估计为了找到所需的行而要检查的行数。注意,这只是一个估计值,并不总是准确的。
  10. Extra:包含MySQL解决查询的详细信息。这一列包含的信息非常丰富,如“Using where”、“Using temporary”、“Using filesort”等,这些都可以帮助我们更好地理解查询的执行过程。

详情介绍

1. id
  • 描述:查询的标识符,每个SELECT(或子查询)都有一个唯一的标识符。
  • 作用:帮助识别查询中的子查询和它们的执行顺序。
2. select_type
  • 描述:查询的类型,标识了查询的来源。
  • 类型及含义
    • SIMPLE:不包含子查询或UNION的简单SELECT查询。
    • PRIMARY:最外层的SELECT查询。
    • DERIVED:以FROM子句中的子查询方式出现的SELECT语句。
    • UNION:UNION中的第二个或之后的SELECT查询。
    • UNION RESULT:从UNION的结果集中获取数据的SELECT查询。
    • SUBQUERY:不在FROM子句中出现的子查询,通常在SELECT语句中使用。
    • DEPENDENT SUBQUERY:子查询依赖外层查询的结果集。
3. table

4. type

5. possible_keys

6. key

7. key_len

8. ref

9. rows

10. Extra

  • 描述:输出结果集的表。
  • 作用:显示查询涉及的表。
  • 描述:连接类型,表示MySQL如何连接表。
  • 类型及含义
    • system:表只有一行(等于const)。
    • const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
    • eq_ref:所有部分都使用了唯一索引或主键。
    • ref:所有部分都使用了非唯一索引或唯一索引的非唯一前缀。
    • fulltext:使用全文索引。
    • ref_or_null:与ref类似,但是包含了mysql对NULL的搜索。
    • index_merge:表示使用了索引合并优化方法。
    • unique_subquery:用于IN子查询,该子查询返回一个唯一值。
    • index_subquery:与unique_subquery类似,但是它返回非唯一值。
    • range:对索引进行范围检索。
    • index:全索引扫描,意味着MySQL将扫描整个索引来查找匹配的行。
    • ALL:全表扫描,意味着MySQL需要扫描整张表来找到匹配的行。
  • 描述:可能应用在这张表上的索引。
  • 作用:显示可能应用在这张表上的索引,注意是可能,并不一定会真正使用。
  • 描述:实际使用的索引。
  • 作用:显示MySQL决定使用的索引。
  • 描述:使用的索引的长度。
  • 作用:在不使用所有的索引列的情况下,可以决定使用的长度。
  • 描述:显示哪些列或常量被用作索引查找上的条件。
  • 作用:帮助理解索引是如何被使用的。
  • 描述:MySQL估计为了找到所需的行而要检查的行数。
  • 作用:虽然是估计值,但可以作为优化查询的一个参考。
  • 描述:包含MySQL解决查询的详细信息。
  • 作用:提供了查询执行过程中的额外信息,如是否使用了WHERE子句、是否使用了临时表、是否进行了文件排序等。

如何使用EXPLAIN进行优化?

  1. 避免全表扫描:通过优化查询条件,尽量使MySQL能够使用索引来查询数据,而不是进行全表扫描。
  2. 优化索引:根据查询的需求,合理设计索引,避免冗余的索引或不必要的索引。
  3. 优化查询语句:尽量避免在查询语句中使用复杂的嵌套子查询或JOIN操作,这些都会增加查询的复杂度。
  4. 使用LIMIT限制返回结果集:当只需要返回部分结果时,使用LIMIT可以显著减少MySQL需要处理的行数。
  5. 定期维护数据库:通过定期更新统计信息、重建索引等操作,保持数据库的性能。

下一章讲解如何优化SQL查询

关键字:MySQL如何定位慢查询以及使用EXPLAIN对SQL进行性能分析

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

责任编辑: