mysql的in+子查询

【mysql最糟糕的子查询：in+子查询】
select * from film where film_id in (select film_id from film_actor where actor_id = 1)

一般认为：
先执行子查询，然后再执行父查询

即
select group_concat(film_id) from film_actor where actor_id = 1 // 返回101,103
select * from film where film_id in (101,103)

实际
mysql将相关的外层表压到子查询中
select * from film where exists (select * from film_actor where actor_id=1 and film.film_id = film_actor.film_id)
先对film全表扫描，根据返回的film_id逐个对film_actor进行子查询
如果外层表很大，则该查询很糟糕

改写：
select film.* from film inner join film_actor using(film_id) where actor_id = 1

另一个优化方法：
使用 group_concat 在 in 中构造一个由逗号分隔的列表