mysql left join执行异常缓慢优化案例

最新推荐文章于 2023-11-22 16:09:53 发布

雅冰石

最新推荐文章于 2023-11-22 16:09:53 发布

阅读量1k

点赞数

分类专栏： 2 MySql 学习 # MySql 性能调优

本文链接：https://blog.csdn.net/yabingshi_tech/article/details/106257591

版权

2 MySql 学习同时被 2 个专栏收录

331 篇文章 5 订阅

订阅专栏

MySql 性能调优

46 篇文章 4 订阅

订阅专栏

一：问题描述

生产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秒了。

雅冰石

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql left join执行异常缓慢优化案例

一：问题描述生产有这个这样的慢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.materi.
复制链接

扫一扫