一:问题描述
生产Mysql(版本5.7.18)有个这样的慢sql:
SELECT * FROM (
SELECT
ml.*, GROUP_CONCAT(sd. NAME) NAME,GROUP_CONCAT(sd.unit) unit,GROUP_CONCAT(sd.postsRank) postsRank
FROM transfer tr
LEFT JOIN clue ce ON ce.serialNum=tr.clueCode
LEFT JOIN material ml ON ce.materialCode=ml.serialNum
LEFT JOIN suspected sd ON ml.serialNum=sd.serialCode
LEFT JOIN (
SELECT nodeCode,COUNT(id) returnNumber FROM nodeInfo WHERE nodeid='jt_submit' AND nextOperation='2'
GROUP BY nodeCode
) thcs ON ce.serialNum=thcs.nodeCode
WHERE 1=1
AND ce.channel IN(2)
AND tr.operateDate >= STR_TO_DATE('2018-05-15 00:00:00','%Y-%m-%d %H:%i:%s')
AND tr.operateDate <= STR_TO_DATE('2020-04-30 23:59:59','%Y-%m-%d %H:%i:%s')
GROUP BY ce.serialNum
UNION ALL
SELECT
jce.*, GROUP_CONCAT(sd. NAME) NAME,GROUP_CONCAT(sd.unit) unit,GROUP_CONCAT(sd.postsRank) postsRank
FROM material jce
LEFT JOIN transfer tr ON jce.serialNum = tr.materialCode
LEFT JOIN suspected sd ON jce.serialNum=sd.serialCode
WHERE EXISTS(
SELECT tr.materialCode
FROM transfer tr
LEFT JOIN material ce ON ce.serialNum=tr.clueCode
LEFT JOIN (
SELECT materialCode,gd.returnNumber FROM clue ce
LEFT JOIN (SELECT nodeCode,COUNT(id) returnNumber FROM nodeInfo WHERE nodeid='jt_submit' AND nextOperation='2'
GROUP BY nodeCode
) gd ON ce.serialNum=gd.nodeCode
WHERE gd.nodeCode IS NOT NULL
) thcs ON ce.serialNum = thcs.materialCode
LEFT JOIN (
SELECT ce.serialNum FROM transfer tr
LEFT JOIN material ce ON ce.serialNum=tr.clueCode
LEFT JOIN material jtce ON jtce.serialNum = tr.materialCode
LEFT JOIN `time` te ON jtce.serialNum = te.materialCodeJT
WHERE 1=1
AND ce.channel1 IN(26)
AND DATEDIFF(ce.statusDate,IF(te.askDate!="",DATE_SUB(te.askDate,INTERVAL -IFNULL(te.delayDay,0) DAY) ,tr.operateDate)) >= IF(te.askDate!="","1","90")
) cs ON ce.serialNum=cs.serialNum
WHERE 1=1
AND ce.channel1 IN(26)
AND tr.materialCode =jce.serialNum
AND tr.operateDate >= STR_TO_DATE('2018-05-15 00:00:00','%Y-%m-%d %H:%i:%s')
AND tr.operateDate <= STR_TO_DATE('2020-04-30 23:59:59','%Y-%m-%d %H:%i:%s')
GROUP BY ce.serialNum
)
GROUP BY jce.serialNum
) ght WHERE 1=1
LIMIT 0 ,5
查询只返回5条记录,却需要30秒。
执行计划如下:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ------------------ ---------- ---------- ------ --------------------------------- ------------------ ------- --------------------- ---------- -------- ---------------------------------------------------------------------
1 PRIMARY <derived2> (NULL) ALL (NULL) (NULL) (NULL) (NULL) 1431508031 100.00 (NULL)
2 DERIVED tr (NULL) ALL (NULL) (NULL) (NULL) (NULL) 1188 11.11 Using where; Using temporary; Using filesort
2 DERIVED ce (NULL) ref ind_clue_serialNum ind_clue_serialNum 153 JiJianDB.tr.clueCode 1 10.00 Using where
2 DERIVED ml (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 100.00 Using where; Using join buffer (Block Nested Loop)
2 DERIVED sd (NULL) ALL (NULL) (NULL) (NULL) (NULL) 2725 100.00 Using where; Using join buffer (Block Nested Loop)
2 DERIVED <derived3> (NULL) ALL (NULL) (NULL) (NULL) (NULL) 9 100.00 Using where; Using join buffer (Block Nested Loop)
3 DERIVED nodeInfo (NULL) ref nodeInfo_nodeId,nodeInfo_nodeCode nodeInfo_nodeId 93 const 90 10.00 Using index condition; Using where; Using temporary; Using filesort
4 UNION jce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 100.00 Using where; Using temporary; Using filesort
4 UNION tr (NULL) ALL (NULL) (NULL) (NULL) (NULL) 1188 100.00 Using where; Using join buffer (Block Nested Loop)
4 UNION sd (NULL) ALL (NULL) (NULL) (NULL) (NULL) 2725 100.00 Using where; Using join buffer (Block Nested Loop)
5 DEPENDENT SUBQUERY ce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 10.00 Using where
5 DEPENDENT SUBQUERY tr (NULL) ALL (NULL) (NULL) (NULL) (NULL) 1188 0.11 Using where; Using join buffer (Block Nested Loop)
5 DEPENDENT SUBQUERY ce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 385 100.00 Using where
5 DEPENDENT SUBQUERY <derived7> (NULL) ref <auto_key0> <auto_key0> 57 JiJianDB.ce.serialNum 2 100.00 Using where
5 DEPENDENT SUBQUERY tr (NULL) ALL (NULL) (NULL) (NULL) (NULL) 1188 100.00 Using where; Using join buffer (Block Nested Loop)
5 DEPENDENT SUBQUERY ce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 100.00 Using where; Using join buffer (Block Nested Loop)
5 DEPENDENT SUBQUERY jtce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 100.00 Using where; Using join buffer (Block Nested Loop)
5 DEPENDENT SUBQUERY te (NULL) ALL (NULL) (NULL) (NULL) (NULL) 18 100.00 Using where; Using join buffer (Block Nested Loop)
7 DERIVED nodeInfo (NULL) ref nodeInfo_nodeId,nodeInfo_nodeCode nodeInfo_nodeId 93 const 90 10.00 Using index condition; Using where; Using temporary; Using filesort
执行计划看起来也没异常。
尝试查询下各个部分的sql,定位哪里慢,发现可疑sql:
SELECT tr.materialCode
FROM transfer tr
LEFT JOIN material de ON de.serialNum=tr.clueCode
LEFT JOIN (
SELECT materialCode,gd.returnNumber
FROM clue ce
LEFT JOIN (SELECT nodeCode,COUNT(id) returnNumber FROM nodeInfo WHERE nodeid='jt_submit' AND nextOperation='2'
GROUP BY nodeCode ) gd ON ce.serialNum=gd.nodeCode
WHERE gd.nodeCode IS NOT NULL
) thcs ON de.serialNum = thcs.materialCode
#单独执行LEFT JOIN前面这部分sql,速度很快,0.1秒,返回5500条数据。
LEFT JOIN (
#单独执行LEFT JOIN后面小括号里的这部分sql也很快,0.2秒,返回106条数据。
#但是当这两个查询left join,就慢了。
SELECT ce.serialNum FROM transfer tr
LEFT JOIN material ce ON ce.serialNum=tr.clueCode
LEFT JOIN material jtce ON jtce.serialNum = tr.materialCode
LEFT JOIN `time` te ON jtce.serialNum = te.materialCodeJT
WHERE 1=1
AND ce.channel1 IN(26)
AND DATEDIFF(ce.statusDate,IF(te.askDate!="",DATE_SUB(te.askDate,INTERVAL -IFNULL(te.delayDay,0) DAY) ,tr.operateDate)) >= IF(te.askDate!="","1","90")
) cs ON de.serialNum=cs.serialNum
WHERE 1=1
AND de.channel1 IN(26)
执行计划:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------- ---------- ------ --------------------------------- --------------- ------- --------------------- ------ -------- ---------------------------------------------------------------------
1 PRIMARY de (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 10.00 Using where
1 PRIMARY ce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 385 100.00 Using where
1 PRIMARY <derived3> (NULL) ref <auto_key0> <auto_key0> 57 JiJianDB.ce.serialNum 2 100.00 Using where
1 PRIMARY tr (NULL) ALL (NULL) (NULL) (NULL) (NULL) 1188 10.00 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY tr (NULL) ALL (NULL) (NULL) (NULL) (NULL) 1188 100.00 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY ce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 100.00 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY jtce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 402 100.00 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY te (NULL) ALL (NULL) (NULL) (NULL) (NULL) 18 100.00 Using where; Using join buffer (Block Nested Loop)
3 DERIVED nodeInfo (NULL) ref nodeInfo_nodeId,nodeInfo_nodeCode nodeInfo_nodeId 93 const 90 10.00 Using index condition; Using where; Using temporary; Using filesort
二 问题定位
个人估计当left join前后连接的是嵌套多个left join的sql时,查询性能很差
三 解决办法
将后面那部分嵌套left join的sql数据插入到表里,跟这个表进行关联
CREATE TEMPORARY TABLE baidd_tmp AS
SELECT ce.serialNum FROM transfer tr
LEFT JOIN material ce ON ce.serialNum=tr.clueCode
LEFT JOIN material jtce ON jtce.serialNum = tr.materialCode
LEFT JOIN `time` te ON jtce.serialNum = te.materialCodeJT
WHERE 1=1
AND ce.channel1 IN(26)
AND DATEDIFF(ce.statusDate,IF(te.askDate IS NOT NULL,DATE_SUB(te.askDate,INTERVAL -IFNULL(te.delayDay,0) DAY) ,tr.operateDate)) >= IF(te.askDate IS NOT NULL,"1","90");
SELECT tr.materialCode
FROM transfer tr
LEFT JOIN material ce ON ce.serialNum=tr.clueCode
LEFT JOIN (
SELECT materialCode,gd.returnNumber FROM clue ce
LEFT JOIN (SELECT nodeCode,COUNT(id) returnNumber FROM nodeInfo WHERE nodeid='jt_submit' AND nextOperation='2'
GROUP BY nodeCode
) gd ON ce.serialNum=gd.nodeCode
WHERE gd.nodeCode IS NOT NULL
) thcs ON ce.serialNum = thcs.materialCode
LEFT JOIN baidd_tmp cs ON ce.serialNum=cs.serialNum
WHERE 1=1
AND ce.channel1 IN(26)
查询由30秒变为1秒了。