慢查询优化案例之将子查询替换为临时表再与其他表进行表关联_优化器将子查询转换成多表查询

一 问题描述

有个这样的慢查询，执行需要1.8秒，且该慢查询在另一个慢查询中被调用了几十次，执行长达200秒：

SELECT
 
                                   ce.orgCode,
 
                                   ce.serialNum
 
                            FROM
 
                                   clue ce
 
                            LEFT JOIN clue pce ON pce.serialNum = ce.parentId
 
                            LEFT JOIN (
 
                                   SELECT
 
                                          ni.nodeCode,
 
 
 
                                   IF (
 
                                          ni.workActivityID IS NULL,
 
                                          ce.statusDate,
 
                                          ni.opreateDate
 
                                   ) AS 'statusDate'
 
                                   FROM
 
                                          nodeInfo ni
 
                                   LEFT JOIN clue ce ON ni.nodeCode = ce.serialNum
 
                                   WHERE
 
                                          (
 
                                                 ni.workActivityID = 'finish_clue'
 
                                                 AND ni.nextOperation = '00'
 
                                                 OR (
 
                                                        (
 
                                                               ni.workActivityID = 'four_approve_2'
 
                                                               AND ni.nextOperation = '4'
 
                                                        )
 
                                                        OR (
 
                                                               ni.workActivityID = 'three_approve_2'
 
                                                               AND ni.nextOperation = '4'
 
                                                        )
 
                                                 )
 
                                                 OR ce. STATUS IN (
 
                                                        '251',
 
                                                        '252',
 
                                                        '253',
 
                                                        '254',
 
                                                        '255',
 
                                                        '219B'
 
                                                 )
 
                                          )
 
                                   AND ni.nodeCode LIKE 'XS-%'
 
                                   GROUP BY
 
                                          ni.nodeCode
 
                            ) cezt ON cezt.nodeCode = ce.serialNum

执行计划如下：

查看下该表的索引：

发现group by没有走ni的索引nodeInfo_nodeCode。

强制走这个索引后，查询也还是很慢，1.8秒：

单独执行cezt里的这个sql：

SELECT
						 ni.nodeCode,
 
					IF (
						ni.workActivityID IS NULL,
						ce.statusDate,
						ni.opreateDate
					) AS 'statusDate'
					FROM
						nodeInfo ni 
					LEFT JOIN clue ce ON ni.nodeCode = ce.serialNum
					WHERE
						(
							ni.workActivityID = 'finish_clue'
							AND ni.nextOperation = '00'
							OR (
								(
									ni.workActivityID = 'four_approve_2'
									AND ni.nextOperation = '4'
								)
								OR (
									ni.workActivityID = 'three_approve_2'
									AND ni.nextOperation = '4'
								)
							)
							OR ce. STATUS IN (
								'251',
								'252',
								'253',
								'254',
								'255',
								'219B'
							)
						)
					AND ni.nodeCode LIKE 'XS-%'
					GROUP BY ni.nodeCode

也不算慢，0.1秒，也是全表扫描的ni，返回1万行数据。

二 优化思路

把这个包含group by的子查询插入到一个临时表里，再和外面的表进行关联

2.1 把以下sql的查询结果放入一个临时表里

示例：

CREATE TABLE baidd_test_20200803 AS
 
SELECT
 
                                   ni.nodeCode,
 
 
 
                            IF (
 
                                   ni.workActivityID IS NULL,
 
                                   ce.statusDate,
 
                                   ni.opreateDate
 
                            ) AS 'statusDate'
 
                            FROM
 
                                   nodeInfo ni
 
                            LEFT JOIN clue ce ON ni.nodeCode = ce.serialNum
 
                            WHERE
 
                                   (
 
                                          ni.workActivityID = 'finish_clue'
 
                                          AND ni.nextOperation = '00'
 
                                          OR (
 
                                                 (
 
                                                        ni.workActivityID = 'four_approve_2'
 
                                                        AND ni.nextOperation = '4'
 
                                                 )
 
                                                 OR (
 
                                                        ni.workActivityID = 'three_approve_2'
 
                                                        AND ni.nextOperation = '4'
 
                                                 )
 
                                          )
 
                                          OR ce. STATUS IN (
 
                                                 '251',
 
                                                 '252',
 
                                                 '253',
 
                                                 '254',
 
                                                 '255',
 
                                                 '219B'
 
                                          )
 
                                   )
 
                            AND ni.nodeCode LIKE 'XS-%'
 
                            GROUP BY
 
                                   ni.nodeCode

                               

2.2 在该临时表的nodeCode上创建一个索引

CREATE INDEX ind_tmp_nodeCode   ON baidd_test_20200803(nodeCode);

2.3 改写sql

将所有以下sql：

(
 
                                   SELECT
 
                                            ni.nodeCode,
 
 
 
                                   IF (
 
                                          ni.workActivityID IS NULL,
 
                                          ce.statusDate,
 
                                          ni.opreateDate
 
                                   ) AS 'statusDate'
 
                                   FROM
 
                                          nodeInfo ni
 
                                   LEFT JOIN clue ce ON ni.nodeCode = ce.serialNum
 
                                   WHERE
 
                                          (
 
                                                 ni.workActivityID = 'finish_clue'
 
                                                 AND ni.nextOperation = '00'
 
                                                 OR (
 
                                                        (
 
                                                               ni.workActivityID = 'four_approve_2'
 
                                                               AND ni.nextOperation = '4'
 
                                                        )
 
                                                        OR (
 
                                                               ni.workActivityID = 'three_approve_2'
 
                                                               AND ni.nextOperation = '4'
 
                                                        )
 
                                                 )
 
                                                 OR ce. STATUS IN (
 
                                                        '251',
 
                                                        '252',
 
                                                        '253',
 
                                                        '254',
 
                                                        '255',
 
                                                        '219B'
 
                                                 )
 
                                          )
 
                                   AND ni.nodeCode LIKE 'XS-%'
 
                                   GROUP BY ni.nodeCode
 
                            )

替换为baidd_test_20200803

2.4 示例

修改前的sql： 

SELECT
 
                                   ce.orgCode,
 
                                   ce.serialNum
 
                            FROM
 
                                   clue ce
 
                            LEFT JOIN clue pce ON pce.serialNum = ce.parentId
 
                            LEFT JOIN (
 
                                   SELECT
 
                                            ni.nodeCode,
 
 
 
                                   IF (
 
                                          ni.workActivityID IS NULL,
 
                                          ce.statusDate,
 
                                          ni.opreateDate
 
                                   ) AS 'statusDate'
 
                                   FROM
 
                                          nodeInfo ni
 
                                   LEFT JOIN clue ce ON ni.nodeCode = ce.serialNum
 
                                   WHERE
 
                                          (
 
                                                 ni.workActivityID = 'finish_clue'
 
                                                 AND ni.nextOperation = '00'
 
                                                 OR (
 
                                                        (
 
                                                               ni.workActivityID = 'four_approve_2'
 
                                                               AND ni.nextOperation = '4'
 
                                                        )
 
                                                        OR (
 
                                                               ni.workActivityID = 'three_approve_2'
 
                                                               AND ni.nextOperation = '4'
 
                                                        )
 
                                                 )
 
                                                 OR ce. STATUS IN (
 
                                                        '251',
 
                                                        '252',
 
                                                        '253',
 
                                                        '254',
 
                                                        '255',
 
                                                        '219B'
 
                                                 )
 
                                          )
 
                                   AND ni.nodeCode LIKE 'XS-%'
 
                                   GROUP BY ni.nodeCode
 
                            ) cezt ON cezt.nodeCode = ce.serialNum
 
                           

修改后的sql：

SELECT
 
                                   ce.orgCode,
 
                                   ce.serialNum
 
                            FROM
 
                                   clue ce
 
                            LEFT JOIN clue pce ON pce.serialNum = ce.parentId
 
                            LEFT JOIN baidd_test_20200803 cezt ON cezt.nodeCode = ce.serialNum

查询结果由1.8秒降为0.02秒。