接到报警通知，负载过高，达到800%，load也过高，有11了。
mysql版本为5.6.12-log
1 top 之后，确实是mysqld进程占据了所有资源。
2 查看error日志，无任何异常
3 show eninge innodb status/g，没有死锁信息。
4 show full processlist;
没有耗时非常大的慢sql再跑。看并发，当前的线程总数量也才30个左右。
5 查看iostat，读写正常。
到底是什么问题呢？查看slow log，发现如下sql，频繁执行，耗时在5秒之间，explain有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 -> where 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, -> 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' -> and object_status=0 -> order by web_page_div.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_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)
看sql是where后面的if判断引起的，拆分if之后，就正常了，sql耗时不到0.1秒。数据库load也降下来了。
还记录以前碰到的
(block nested loop)的案例是 join后面的on条件里面有or判断。
也会引起block nested loop，导致数据库负载过高。