省流版:能不用 in + 子查询,就不要用,可以用 join 来替代实现。
select * from A where id in (
select max(id) as id from A
where task_id in(1,2,3)
group by task_id
);
看 explain ,走了全表扫描:
改造 sql 如下:
select * from A a inner join (
select max(id) as id from A
where task_id in(1,2,3) group by task_id
) b
on a.id = b.id;
继续explain :
总结:能不用 in + 子查询,就不要用,可以用 join 来替代实现。