Solution for mysql occupying too much CPU (adding indexes)

The following is an example of high CPU usage by MYSQL. I hope I will be inspired by my friends who have encountered similar problems. In general, MYQL occupies a high CPU usage, most of which is caused by database query code problems. Too many databases are queried. On the one hand, we need to streamline the code, and on the other hand, we 'd better set indexes for frequently used code.

When I got up this morning, I checked the machine alarm and the load was always above 4.

Top, I found mysql ranked first and was quite stable.
I cannot restart mysql.
Mysql> show processlist;
I found that there are two query statements on the xxx website that are always in the column. I will wipe the site and there will be no more than 0.3 million records, which is not a machine performance problem.

I suddenly remembered that the tmp_table_size value was too small on the Internet;
So mysql-pxxx-e "show variables;"> tmp
First, the default value is 32 MB (the number of bytes is displayed)
So Weng was happy to increase it to 256 and restart mysql .. The results were disappointing.

No, you have to come back.
Select the table and find that there is a large amount of messages in the Forum.
So:
Mysql> show columns from bbs_message;
+ ----------- + -------------- + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ----------- + -------------- + ------ + ----- + --------- + ---------------- +
| Msg_id | int (11) | NO | PRI | NULL | auto_increment |
| Board_id | int (11) | NO | MUL | 0 |
| Parent_id | int (11) | NO | MUL | 0 |
| Root_id | int (11) | NO | MUL | 0 |

In show processlist, select * from bbs_message where board_id = xxx and parent_id = xxx
And select * from bbs_message where parent_id = xxx
As soon as these two CPUs appear
Start with the index:
Add two indexes
Mysql> alter table bbs_message add index parentid (parent_id );
Alter table bbs_message add index chaxunid (board_id, parent_id );
Finally, check the index results:
Mysql> show index from bbs_message;
+ ------------- + ------------ + ---------- + -------------- + ------------- + ---------- + -------- + ------------ + ----------- +
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+ ------------- + ------------ + ---------- + -------------- + ------------- + ---------- + -------- + ------------ + ----------- +
| Bbs_message | 0 | PRIMARY | 1 | msg_id | A | 2037 | NULL | BTREE |
| Bbs_message | 1 | rootid | 1 | root_id | A | 49 | NULL | BTREE |
| Bbs_message | 1 | chaxunid | 1 | board_id | A | 3 | NULL | BTREE |
| Bbs_message | 1 | chaxunid | 2 | parent_id | A | 135 | NULL | BTREE |
| Bbs_message | 1 | parentid | 1 | parent_id | A | 127 | NULL | BTREE |
+ ------------- + ------------ + ---------- + -------------- + ------------- + ---------- + -------- + ------------ + ----------- +
5 rows in set (0.00 sec)
The load at the top is always 0.x stable.