Mysql CPU Usage Long-term 100% Solutions Memo

The recent CPU usage of one server remains at 100% for a long time, and viewing process discovery is caused by the MySQL service. So search all the information, and finally successfully solve the problem. Memo and share, hope to help you novice friends.

(The server running environment is similar to the Windows Server2008,linux mentality.) Only the command-line tools are different. ）

First enter the MySQL database via cmd input mysql-h localhost-u root-p Enter

View current MySQL frequently used SQL statements by show Processlist;

Calling this command repeatedly finds that multiple similar select statements often appear

After finding out that the field in the WHERE clause was not indexed, MySQL had to start with the first record and then read the entire table until it found the relevant row. The larger the table, the more time it takes.

ALTER TABLE  ' table name ' ADD INDEX ( ' field name ' ) ;      

The CPU immediately drops to about 20% after the index of the related field is created by the above statement. Solve the problem successfully.

The index button in the "Action" List of the table structure makes indexing easier to handle, more suitable for lazy people, and a click of the mouse to solve the Tips:phpmyadmin (*￣︶￣)

Summarize:

The field that is used in the conditions in the WHERE, JOIN, MAX (), MIN (), ORDER by clauses should be indexed by index. The index is used to quickly find a row with a specific value on a column. If the table has an index to the column of the query, MySQL can quickly reach a location to find the middle of the data file, and there is no need to consider all the data.

If a table has 1000 rows, this is at least 100 times times faster than sequential reads. All MySQL indexes (PRIMARY, unique, and index) are stored in the B-tree.

According to the development documentation for MySQL, index indexes are used to:

1 to quickly find the row  that matches a WHERE clause 2 retrieves rows from other tables when a junction (join) is executed.   3 To find the max () or min () value  for a specific index column 4 , sort or Group a table if sorting or grouping is done on the leftmost front of an available key (for example, order by key_part_1,key_part_2). If all key values are partially followed by DESC, the key is read in reverse order.   5, in some cases, a query can be optimized to retrieve values without consulting the data file. If all columns used for some tables are numeric and form the leftmost prefix of some keys, the values can be retrieved from the index tree for faster.  

Mysql CPU Usage Long-term 100% Solutions Memo