DEPENDENT SUBQUERY标记

今天发现有条sql语句执行到了大约1分钟左右，sql的格式如下：

select * from a where id in ( select max(id) from a where b1 in ('','') group by b1 )

该条语句在数据库记录在5条的时候，时间在8ms左右，可以看到是正常的

但是在16K条的时候，时间达到了90秒左右。

为了分析原因，在上述sql上增加explain，如：

explain select * from a where id in ( select max(id) from a where b1 in ('','') group by b1 )

id     select_type                       table  type                                rows           extra
1      PRIMARY                             A      ALL                               16000           Using where
2      DEPENDENT SUBQUERY                  A      ALL                               16000           Using where; Using temporary; Using filesort

发现内层和外层sql都进行了全表查询，而且内层sql的select_type为：DEPENDENT SUBQUERY

通过查询发现：

SUBQUERY：子查询中的第一个SELECT；

DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询 

也就是说外层每遍历一条记录就执行一次内层sql的查询操作，所以总的查询量在16K*16K次，所以会造成时间达到了90秒左右的情况

优化：

1 拆成两条查询语句

2 使用临时表内连接

   select * from a a1 inner join (select max(id) as id from a where b1 in('','') group by b1) a2 on a1.id=a2.id

  对该sql增加explain之后：

id    select_type    table              type        possible_keys    key           rows   extra
1     PRIMARY       <derived2>          ALL                                         2 
1     PRIMARY         a1             eq_ref          PRIMARY        PRIMARY         4    stemp.id1 
2     DERIVED         A                ALL                                        16099  Using where; Using temporary; Using filesort   

    可以看到select_type值发生了变化，而且对a1表的查询用到主键索引，速度有了很大的提升16K数据在18ms左右

总结：mysql有时优化子查询很糟糕，尤其是where 从句中的in()查询，所以再做in()中做子查询的时候需要特别注意