Analyze the problem of CPU load soaring caused by index citation in Mysql _mysql

Received a MySQL server load alarm, up a look, load average are more than 280, with top look, the CPU ran to 336%, but IO and memory load is not high, according to experience, should be an index caused by the tragedy.

Look at the processlist and slow query situation, found that there is a regular SQL, the execution plan in the number of scan records can be seen, a single execution time is 0.07s, not too big. At first glance, it may not be triggered, but the frequency is too high, and the execution plan does not look perfect:

Mysql> explain SELECT count (1) from A, b WHERE a.id = b.video_id and b.state = 1 and b.column_id = ' Bayi ' \g

1. Row ***************************
id:1
select_type:simple
table:b
type:index_merge
possible_ Keys:columnid_videoid,column_id,state,video_time_stamp,idx_videoid
key:column_id,state
key_len:4,4
ref:null
rows:100
extra:using intersect (column_id,state); Using where
*************************** 2 row ***************************
id:1
select_type:simple
table:a
type:eq_ref
possible_keys:primary
key:primary
key_len:4
ref:b.video_id
rows:1
Extra:using where; Using Index

Look at the index of the table again:

Mysql> Show index from B\G

1. Row ***************************
table:b
non_unique:0
key_name:primary
seq_in_index:1
Column _name:id
collation:a
cardinality:167483
sub_part:null
packed:null
NULL:
index_type : Btree
Comment:
index_comment:
*************************** 2. Row ***************************
table:b
Non_unique:1
key_name:column_id
seq_in_index:1
column_name:column_id
collation:a
cardinality:8374
sub_part:null
packed:null
NULL:
index_type:btree
Comment:
Index _comment:
*************************** 3. Row ***************************
table:b
non_unique:1
key_name:state
Seq_in_index:2
column_name:state
collation:a
cardinality:5
sub_part:null
packed:null
Null:
Index_type:btree
Comment:
index_comment:

You can see that in the execution plan, the index merge is used, and the efficiency is naturally not the same as the Federated Index (also known as the Overlay Index), and the cardinality (uniqueness) of the state field is poor and the index effect is poor. Delete Two separate indexes, modify the union to see how it works:

Mysql> Show index from B;

1. Row *************************** table:b non_unique:0 key_name:primary seq_in_index:1 column_name:id collation:a Card  inality:128151 sub_part:null packed:null Null:Index_type:BTREE comment:index_comment: *************************** 2. Row *************************** table:b non_unique:1 key_name:idx_columnid_state seq_in_index:1 column_name:column_i D collation:a cardinality:3203 sub_part:null packed:null Null:Index_type:BTREE comment:index_comment: ************* 3. Row *************************** table:b non_unique:1 key_name:idx_columnid_state seq_in_index:2 column_name:state  Lation:a cardinality:3463 sub_part:null packed:null Null:Index_type:BTREE comment:index_comment:mysql>  SELECT count (1) from A, b WHERE a.id = b.video_id and b.state = 1 and b.column_id = ' Bayi ' \g *************************** 1. Row *************************** id:1 select_type:simple table:b type:ref Possible_keys:Columnid_videoid,idx_videoid,idx_columnid_state key:columnid_videoid key_len:4 ref:const rows:199 extra:using where * 2. Row *************************** id:1 select_type:simple table:a type:eq_ref possible_keys:primary key:primary key_le N:4 ref:b.video_id rows:1 extra:using where;

 Using Index

You can see that the execution plan has become the only Idx_columnid_state index, and the ref type has become const,sql. The execution time has also changed from 0.07s to 0.00s, and the corresponding CPU load has dropped from 336% to 12%.

Summing up, from a number of historical experience, if the CPU load is high, but memory and IO are all right, in this case, the first thought must be the index problem, the nine is wrong.