Failure case: A subquery causes service to crash

I also know before MySQL 5.6 subquery is best to rewrite the way to join, but before the understanding is not deep enough to see the implementation plan is OK, until today the main library hang ....

Phenomenon: The SQL execution frequency is not very high, about 10s once, the first two days is good, About 1.5s can produce results, before you realize the seriousness of the problem, until the end of the afternoon the entire MySQL crash, response time is very long, the performance of the SQL execution time is very long, into nearly 100s, and each of the query connection will consume about 1 cores of the cpu,20 to occupy 20 cores, seriously affect the normal execution of other SQL

The state item in show Processlist is always shown as preparing,profile This SQL is the same.

Problem SQL and resolution plan:

Look at the situation SQL has gone index, scan the number of rows is not much, a single table record is 2w line, the implementation time 100s;

After the stop slave on the idle Slave, the problem is still determined to be caused by the SQL;

Workaround: Change it to join, or upgrade to 5.6 to resolve, the MS level returns results