Analysis of CPU load soaring caused by index in MySQL _ MySQL

This article mainly introduces how to analyze the CPU load soaring caused by MySQL indexes, and introduces the huge CPU burden caused by independent indexes, to remind you to pay attention to the CPU load when using indexes in MySQL, you can refer to the following mysql server load warning when a friend needs to see that the load average has soared to more than 280, from the top view, the CPU ran to 336%, but the IO and memory load was not high. based on experience, it should be caused by an index again.

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.