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

一 问题描述

有个这样的慢查询，执行需要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秒。