Analysis of CPU load surge caused by index citation in MySQL

load average is up to 280, top1, CPU is up to 336%, but IO and memory load is not very high. Based on experience, it should be another index tragedy.

According to the situation of processlist and slow query, it was found that one SQL often appeared. The number of scan records in the execution plan looked quite good, and the single execution time was 0.07s, which was not too big. At first glance, it may not have caused it, but the frequency is simply too high, and the execution plan doesn't seem perfect:

mysql> explain SELECT count(1) FROM a , b WHERE a.id = b.video_id and b.state = 1 AND b.column_id = '81'\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

Now take a look at the index of this 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, index merge is used in the execution plan, so the efficiency is naturally not achieved by using a joint index (also known as overwritten index), and the cardinality of the state field (only 1 sex) is poor, so the index effect is poor. Delete the two independent indexes and change them to a 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
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 = '81' \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

As you can see, the execution plan has changed to use only the idx_columnid_state index, and the ref type has changed to const, the execution time of SQL has changed from 0.07s to 0.00s, and the corresponding CPU load has dropped from 336% to less than 12%.

To sum up, from many historical experiences, if THE CPU load continues to be very high, but the memory and IO are ok, in this case, the first thing that comes to mind is the index problem, and 89 out of 10 can't be wrong.