MySQL，一条两层subquery的坑爹优化

最近整一些见不得人的事情，每天都忙到很晚。悲剧。

 

 

 

SELECT a.client_id,
       s.cnt s_cnt,
       a.cnt a_cnt,
       s.cnt / a.cnt s_rate
FROM (SELECT COUNT(*) cnt,
             handle_client client_id
      FROM tb_captcha cpout
      WHERE expire_time > curdate ()
      GROUP BY handle_client) a 
     LEFT JOIN (SELECT COUNT(*),
       handle_client
FROM tb_captcha
WHERE id IN (SELECT MAX(id)
             FROM tb_captcha
             WHERE task_id IN (SELECT task_id
                               FROM tb_client_task_log
                               WHERE task_code IN ('registerRobotUserEmail','registerWeibo')
                               AND   status = 0
                               AND   executed_time > '2011-03')
             GROUP BY task_id,
                      seq)
GROUP BY handle_client;) s ON a.client_id = s.client_id
ORDER BY s.cnt DESC;

 

同事写了个子查询，跑到mysql上乖乖停不下来，最后被我kill掉了。

 

哥来优化一下。

 

先建索引

在tb_captcha建索引(expire_time, handle_client), (task_id)

在tb_client_log上建索引(status, execuited_time)

 

subquery很坑爹当成exists跑。都是all scan。

查询后的的数据量太大，type为all

 

怎么改呢。

1.如果临时表不是很大，用临时表ok

2.用join代替子查询

 

用inner join优化最里面的子查询。

可以看到里层已经将unique_dependency+all 优化成ref+range。测试性能有所提高，但是没有达到数量级的提高。大概提高了4，5倍。外面那个子查询还会将整个查询死掉。外面有个index type。

 

外部子查询用临时表优化

 

因为临时表都会比较小，所以这种方式对性能有很大的提高。

原来结果根本出不来。现在的执行时间是：

 

执行时间是10ms。

 

 

呵呵。应该算成功优化了。

 

再加上left join。

 

10ms。ok。