一:问题描述
今天一个开发同事反馈,一个sql执行得特别慢,让帮忙优化下。
这个sql有5000多行。里面有很多重复的sql(只是查询条件不一样) left join。
这里只摘出部分sql:
SELECT
ce.orgCode,
ce.serialNum
FROM
clue ce
LEFT JOIN clue pce ON pce.serialNum = ce.parentId
LEFT JOIN (
SELECT
serialNum
FROM
material
WHERE
lightchange1 IS NULL
) zx ON zx.serialNum = ce.materialCode
LEFT JOIN (
SELECT
serialCode,
MAX(endResult) endResult
FROM
approveInfo ai
WHERE
endResult IS NOT NULL
AND startDate <= STR_TO_DATE(
'2019-11-30 23:59:59 23:59:59',
'%Y-%m-%d %H:%i:%s'
)
GROUP BY
serialCode
) sh ON IF (
ce.parentId != '',
pce.serialNum,
ce.serialNum
) = sh.serialCode
LEFT JOIN (
SELECT
serialNum
FROM
operate
WHERE
operate IN ('04', '05')
AND isRecommended = '1'
) zla ON
IF (
ce.parentId != '',
pce.serialNum,
ce.serialNum
) = zla.serialNum
WHERE
ce.fOperate NOT IN ('00', '01')
AND zx.serialNum IS NOT NULL
AND
IF (
ce.parentId != "",
pce. STATUS,
ce. STATUS
) IN (
'251',
'252',
'253',
'254',
'255'
)
AND
IF (
ce.parentId != "",
pce.statusDate,
ce.statusDate
) >= STR_TO_DATE(
'2019-11-01 00:00:00 00:00:00',
'%Y-%m-%d %H:%i:%s'
)
AND
IF (
ce.parentId != "",
pce.statusDate,
ce.statusDate
) <= STR_TO_DATE(
'2019-11-30 23:59:59 23:59:59',
'%Y-%m-%d %H:%i:%s'
)
AND
IF (
ce.parentId != '',
pce.fOperate,
ce.fOperate
) = '03'
AND sh.endResult IN (1, 2)
AND zla.serialNum IS NOT NULL
执行计划:
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------- ---------- ------ ------------------ ------------------ ------- -------------------- ------ -------- ----------------------------------------------------
1 PRIMARY operate (NULL) ALL (NULL) (NULL) (NULL) (NULL) 722 1.80 Using where
1 PRIMARY material (NULL) ALL (NULL) (NULL) (NULL) (NULL) 156 9.00 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY ce (NULL) ALL (NULL) (NULL) (NULL) (NULL) 177 8.00 Using where; Using join buffer (Block Nested Loop)
1 PRIMARY pce (NULL) ref ind_clue_serialNum ind_clue_serialNum 153 JiJianDB.ce.parentId 1 100.00 Using where
1 PRIMARY <derived3> (NULL) ref <auto_key0> <auto_key0> 153 func 11 20.00 Using where; Using index
3 DERIVED ai (NULL) index ind_serialCode ind_serialCode 153 (NULL) 1216 30.00 Using where
该查询执行时间3秒。当不执行最后一行的AND zla.serialNum IS NOT NULL时,查询只需要0.0几秒。
所以确认IS NOT NULL会让查询变慢。
二:优化思路
使用IFNULL(条件字段,'自定义取值')!='自定义取值' 实现IS NOT NULL效果。
示例:
IFNULL(zla.serialNum,'kong' )!='kong'
在测试环境试验,执行时间从1分钟47秒降为了0.4秒,效果杠杠的。
--本篇文章主要参考了https://blog.csdn.net/qq_41210350/article/details/89893945