mysql查询慢排查

最新推荐文章于 2025-07-01 00:18:34 发布

Mr_戴先森

最新推荐文章于 2025-07-01 00:18:34 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44024436/article/details/124042406

这篇博客主要涉及SQL查询分析，通过`iostat`检查系统负载和I/O性能，关注高负载和100%利用率可能存在的性能问题。接着展示了如何查看当前执行的SQL语句以及超过5秒的查询，最后给出了一段复杂的SQL查询进行分析，可能涉及大数据处理和数据库优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#第一步：做一般性检查,查看一下负载 ,大于10,的负载可以就会有问题了 uptime #查看一下io情况,如果%util 可以是不是有性能问题.如果100%说明有性能问题. rMB/s wMB/s 可以看出是读取,还是写的性能问题
iostat -xm 2 avg-cpu: %user %nice %system %iowait %steal %idle 10.58 0.00 0.88 16.47 0.00 72.07
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 1.00 0.00 0.50 0.00 0.01 24.00 0.00 3.00 3.00 0.15
sdb 633.50 67.50 5643.50 350.00 98.53 2.25 34.44 74.32 12.61 0.17 100.00
dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-1 0.00 0.00 0.00 1.50 0.00 0.01 8.00 0.00 3.00 1.00 0.15
#第二步：先查看一下当前的执行的语句 select * from information_schema.PROCESSLIST;
#查询大于5秒的当前查询语句
select * from information_schema.PROCESSLIST where COMMAND!=‘Sleep’ and TIME>5 limit 100;
#第三步：得到如下sql进行分析
explain
SELECT DISTINCT
t.isDel,
t.updateTime,
t.beginTime,
t.updatePeriodNum,
t.updatePeriod,
t.createUserId,
t.cityCode,
t.resourceId,
t.resourceName,
t.dataSource,
t.resourceFirstClass, t.resourceSecondClass,
t.resourceTag,
t.resourceFirstTag,
t.resourceSecondTag,
t.resourceThirdTag,
t.resourceFourthTag,
t.resourceFifthTag,
t.resourceDesc,
t.dataSet,
t.dataSetCode,
t.dataSetCnName,
t.location,
t.providerName,
t.createTime,
t.dataNumChangeTime,
t.dealTime,
t.dataScaleNum,
t.resourceStatus,
t.isLocal,
t.isMerge,
t.isStd,
t.providerDept,
t.version,
t.isSourceSearchNeeded,
t.resourceSource,
t.source,
t.sourceFirst,
t.sourceSecond,
t.collectDepartment,
t.dataCityCode,
t.isShare,
t.resourceDataSource,
t.sjzy_yt,
t.sourceTableName,
t.lastDataTime,
t.standardFieldRate, t.resourceAccess,
t.resourceDept,
t.isStatisticsNum,
t.appSysNum,
t.appSysName, t.resourceFirstUpdateTime,
t.dataSubSource,
t.synStatus,
t.issueStatus, t.resourceApproveStatus,
t.sensitivityLevel,
t.stopReason,
t.sjbgxfs,
t.resourceInfoType,
t.shareTime,
t.serviceNo, t.collectPoliceCategory,
t.dataStorePeriod, t.resourceStoreBranch,
t.resourceNumber,
t.resourceAuth,
t.sjzy_yt AS sjzyyt,
IFNULL( b.increment, 0 ) AS daylyDataScaleNum,
1 AS isLocal, ‘’
AS checkStatus,
date_format( y.minBusiTime,
‘%Y-%m-%d %H:%i:%s’ ) AS firstBusinessTime,
date_format( y.lastBusiTime, ‘%Y-%m-%d %H:%i:%s’ ) AS lastBusinessTime, y.lastImtm AS imtm,
( a.BaseType IS NOT NULL ) AS isStandardDataSetCode FROM t_resource_data t LEFT JOIN t_resource_data_time y ON t.resourceId = y.resid LEFT JOIN t_resource_dic_element a ON t.dataSetCode = a.BaseType LEFT JOIN ( SELECT loga.resourceId, logb.increment FROM ( SELECT resourceId, max( logTime ) latestLogTime FROM t_resource_data_scale_log WHERE logTime >= CURDATE( ) GROUP BY resourceId ) loga LEFT JOIN t_resource_data_scale_log logb ON logb.resourceId = loga.resourceId AND logb.logTime = loga.latestLogTime ) b ON t.resourceId = b.resourceId WHERE 1 = 1 AND ifnull( t.isDel, 0 ) = 0 AND sjzy_yt IN ( ‘1’, ‘12’ )