mysql IS NOT NULL优化案例

一：问题描述

今天一个开发同事反馈，一个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