mysql查询慢排查

#第一步：做一般性检查,查看一下负载 ,大于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’ )​