Analysis of the CPU load surge caused by index in MySQL, mysql soar

Analysis of the CPU load surge caused by index in MySQL, mysql soar

When I received a mysql server load warning, I went up and saw that the load average was over 280. I used the top command to check that the CPU ran to 336%, but the load on I/O and memory was not high, based on experience, it should be another index-related tragedy.

Check processlist and slow query, and find that an SQL statement often appears. The number of scan records in the execution plan is still acceptable. the time consumed for a single execution is 0.07 s, which is not too large. At first glance, it may not be caused by it, 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 = '81'\G

*************************** 1. row ***************************id: 1select_type: SIMPLEtable: btype: index_mergepossible_keys: columnid_videoid,column_id,state,video_time_stamp,idx_videoidkey: column_id,statekey_len: 4,4ref: NULLrows: 100Extra: Using intersect(column_id,state); Using where*************************** 2. row ***************************id: 1select_type: SIMPLEtable: atype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: b.video_idrows: 1Extra: Using where; Using index

Let's look at the index of the table:

mysql> show index from b\G

*************************** 1. row ***************************Table: bNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 167483Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 2. row ***************************Table: bNon_unique: 1Key_name: column_idSeq_in_index: 1Column_name: column_idCollation: ACardinality: 8374Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 3. row ***************************Table: bNon_unique: 1Key_name: stateSeq_in_index: 2Column_name: stateCollation: ACardinality: 5Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:

We can see that index merge is used in the execution plan, and the efficiency is naturally not achieved by using the combined index (also called overwriting index), and the base (uniqueness) of the state field is too bad, poor indexing performance. Delete two independent indexes and change them to join to see how they work:

mysql> show index from b;

*************************** 1. row ***************************Table: bNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 128151Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 2. row ***************************Table: bNon_unique: 1Key_name: idx_columnid_stateSeq_in_index: 1Column_name: column_idCollation: ACardinality: 3203Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 3. row ***************************Table: bNon_unique: 1Key_name: idx_columnid_stateSeq_in_index: 2Column_name: stateCollation: ACardinality: 3463Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment: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: 1select_type: SIMPLEtable: btype: refpossible_keys: columnid_videoid,idx_videoid,idx_columnid_statekey: columnid_videoidkey_len: 4ref: constrows: 199Extra: Using where*************************** 2. row ***************************id: 1select_type: SIMPLEtable: atype: eq_refpossible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: b.video_idrows: 1Extra: Using where; Using index

We can see that the execution plan has changed to the idx_columnid_state index, and the ref type has changed to const. The SQL Execution time has also changed from 0.07s to 0.00 s, the corresponding CPU load also suddenly drops from 336% to less than 12%.

In summary, from multiple historical experiences, if the CPU load continues to be high but the memory and I/O are both good, in this case, the first thing that comes to mind is the index problem.