[MySQLCPU]线上飙升800%，load达到12的解决过程_MySQL

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'<= end_time    ->       ->     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)

(Block Nested Loop)的案例是 join后面的on条件里面有or判断。

也会引起Block Nested Loop，导致数据库负载过高。