EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
原句，执行时间：2.78s
UPDATE pro_ProductEvaluate
SET hasPic = 1
WHERE
ID IN (
SELECT
c.ID
FROM
(
SELECT
a.ID
FROM
pro_ProductEvaluate a
INNER JOIN pro_ProductEvaluateImage b ON a.ID = b.pro_ProductEvaluateID
WHERE
1 = 1
AND a.CreateTime >= '2016-06-03 01:05:00'
AND a.hasPic IS NULL
AND b.State = 1
) c
)
优化后：执行时间：0.38s
update pro_ProductEvaluate
set hasPic=1
where CreateTime>= '2016-06-03 01:05:00' and hasPic is null
and exists(
select 1 from pro_ProductEvaluateImage b
where b.State=1 and pro_ProductEvaluate.id=b.pro_ProductEvaluateID
)
原句，执行时间：2.78s
UPDATE pro_ProductEvaluate
SET hasPic = 1
WHERE
ID IN (
SELECT
c.ID
FROM
(
SELECT
a.ID
FROM
pro_ProductEvaluate a
INNER JOIN pro_ProductEvaluateImage b ON a.ID = b.pro_ProductEvaluateID
WHERE
1 = 1
AND a.CreateTime >= '2016-06-03 01:05:00'
AND a.hasPic IS NULL
AND b.State = 1
) c
)
优化后：执行时间：0.38s
update pro_ProductEvaluate
set hasPic=1
where CreateTime>= '2016-06-03 01:05:00' and hasPic is null
and exists(
select 1 from pro_ProductEvaluateImage b
where b.State=1 and pro_ProductEvaluate.id=b.pro_ProductEvaluateID
)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28939273/viewspace-2117318/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
