[MySQL Optimization case] Series-a typical index raises the issue of CPU load spikes

Received a MySQL server load alarm, up a look, load average all soared to more than 280, with top A 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 SQL often appear, the number of scan records in the execution plan to see also can, a single execution time is 0.07s, not too big. At first glance, it may not have been 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

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

Then look at the index of the table:

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:

As you can see in the execution plan, the index merge is used, and efficiency naturally does not come with a federated index (also called an overlay index), and State the cardinality (uniqueness) of the field is poor and the index is poor. Delete Two independent indexes and change them into a joint to see how the results are:

Mysql> Show index from B;

1. Row ***************************
Table:b
non_unique:0
Key_name:primary
Seq_in_index:1
Column_name:id
Collation:a
cardinality: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_id
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
Collation:a
cardinality:3463
Sub_part:null
Packed:null
Null:
Index_type:btree
Comment:
Index_comment:

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: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_len:4
ref:b.video_id
Rows:1
Extra:using where; Using Index

You can see that the execution plan becomes a idx_columnid_state index only, and the ref type becomes const, and the SQL execution time is changed from 0.07s to 0.00s, the corresponding CPU load also dropped from 336% to less than 12%.

In summary, from a number of historical experience, if the CPU load continues to be very high, but the memory and Io are OK, in this case, the first thought must be the index problem, nine out of ten can not be wrong.

--------------------------------------Split Line--------------------------------------

Hall of Understanding ( http://zhishuedu.com Training is a professional quality training brand jointly launched by senior MySQL expert Ye Jinlong and Wu Bingxi, which mainly includes MySQL DBA combat optimization and Python Operation Development Course, which is the most conscientious and quality training course in the industry.

This article comes from " Lao ye teahouse "blog, be sure to keep this source http://imysql.blog.51cto.com/1540006/1879883

This article is from the "Lao Ye teahouse" blog, please be sure to keep this source http://imysql.blog.51cto.com/1540006/1883563

[MySQL Optimization case] Series-a typical index raises the issue of CPU load spikes