[MySQLCPU] the online traffic soared by 800%, and the load reached 12. _ MySQL

[MySQLCPU] the online traffic soared by 800%. an alarm was triggered when the load reached 12. the load reached 800%, and the load was too high. 11 times.

MySQL version 5.6.12-log

After 1, mysqld processes occupy all resources.

2. View error logs without any exceptions.

3 show eninge innodb status/G, no deadlock information.

4 show full processlist;

There is no time-consuming slow SQL statement to run again. Depending on concurrency, the current total number of threads is about 30.

5. check iostat. the read and write operations are normal.

What is the problem? View the slow log and find that the following SQL statements are executed frequently, which takes 5 seconds. the explain statements include the Using join buffer (Block Nested Loop)

mysql> explain select web_page_object.web_page_object_id,-> web_page_object.object_id,-> web_p_name,web_page_object.position_sort,web_page_object.end_time,om1.label,om1.file,jump_url,om2.label as label1,om2.file as file1-> from web_page_p,web_page_object,object_media as om1,object_media as om2-> where web_page_p.id=web_page_object.web_page_p_id-> and web_page_object.object_media_id=om1.object_media_id-> and web_page_p.web_page_id=1200-> and if(web_page_object.object_media_id1=0,-> web_page_object.object_media_id=om2.object_media_id,-> web_page_object.object_media_id1=om2.object_media_id)->-> and '2014-05-01 15:09:49'>=start_time-> and '2014-05-01 15:09:49'<= end_time-> -> and object_status=0-> order by web_page_p.id,web_page_object.position_sort;+----+-------------+-----------------+--------+-----------------------+---------+---------+-------------------------------------------+-------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra|+----+-------------+-----------------+--------+-----------------------+---------+---------+-------------------------------------------+-------+----------------------------------------------------+|1 | SIMPLE| web_page_object | ALL| object_media_id_index | NULL| NULL| NULL| 51165 | Using where; Using temporary; Using filesort ||1 | SIMPLE| web_page_p| eq_ref | PRIMARY,idx | PRIMARY | 4 | db_jiapin.web_page_object.web_page_p_id | 1 | Using where||1 | SIMPLE| om1 | eq_ref | PRIMARY | PRIMARY | 4 | db_jiapin.web_page_object.object_media_id | 1 | Using where||1 | SIMPLE| om2 | ALL| NULL| NULL| NULL| NULL| 74759 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-----------------+--------+-----------------------+---------+---------+-------------------------------------------+-------+----------------------------------------------------+Using join buffer (Block Nested Loop)

Check that the SQL statement is caused by the if statement after where. after the SQL statement is split into if statements, the SQL statement takes less than 0.1 seconds. The database load is also lowered.

It also records the previously encountered

(Block Nested Loop) The case is that there is or judgment in the on Condition After join.

It will also cause Block Nested Loop, resulting in high database load.