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

a6266318

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

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。