优化代码时,没注意直接在update语句中,进行自己表的子查询了。
update
student
set
status = 2
where
id in (
select
id
from
student
where
student_num in (123 , 156)
and age in (23)
and status = 1
and is_delete = 0);
会报错
You can't specify target table 'student' for update in FROM clause
为什么要这么写呢,因为explain分析相同的条件,update的索引type不如select的索引type类型。想着直接优化先查查出来,再根据ID更新。(因为这个SQL不能用,使用代码实现了这种,性能提升了3-5秒)
这个SQL可以改造成join来做
update
student as c1
join
(
select
id
from
student
where
student_num in (123 , 156)
and age in (23)
and status = 1
and is_delete = 0) as c2 on
c1.id = c2.id
set
c1.status = 2;
也没啥意义,就是记录一下,最后做了联合索引优化了查询性能。