[MySQL CPU] on the line soared 800%,load reached 12 resolution process

Received alarm notification, the load is too high, reached 800%,load is too high, there are 11.

MySQL version number is 5.6.12-log

After 1 top, it is true that the mysqld process occupies all the resources.

2 Viewing the error log, no matter what the exception

3 show Eninge InnoDB status\g, no deadlock information.

4 show full processlist;

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

5 view Iostat, read and write normal.

What is the problem? View slow log, found for example, the following SQL, run frequently, time-consuming between 5 seconds, explain has a Using join buffer (Block Nested Loop)

Mysql> Explain select web_page_object.web_page_object_id, web_page_object.object_id, web_div_ 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_div,web_page_object,object_media as om1,object_media as Om2 wher    E web_page_div.id=web_page_object.web_page_div_id and web_page_object.object_media_id=om1.object_media_id and web_page_div.web_page_id=1200, and if (Web_page_object.object_media_id1=0, W EB_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_ti ORDER BY web_page_div.id,web_page_object.position_sort;+----+ and object_status=0, ME -------------+-----------------+--------+-----------------------+---------+---------+-------------------------------------------+-------+------------ ----------------------------------------+| 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_div | Eq_ref | Primary,idx | PRIMARY | 4 |     db_jiapin.web_page_object.web_page_div_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)

See that SQL is caused by the if inference behind the where, after splitting the IF, it is normal and SQL takes less than 0.1 seconds. The database load is also lowered.

And record what you've met.

(Block Nested Loop) is a case where the on condition behind the join has an or inference.

Also causes the Block Nested Loop, which causes the database to load too high.