Mysql performance check and Optimization Method

I have been using the mysql database software, which is stable and highly efficient. When encountering severe performance problems, there are generally several possibilities: 1. The index is not properly built; 2. the SQL statement is too complicated; 3. The configuration is incorrect; 4. The machine cannot be loaded; 1. The index is not created. If you see that mysql consumes a lot of cpu, you can use mysql c

I have been using the mysql database software, which is stable and highly efficient. There are several possible causes for serious performance problems:

1. The index is not created;
2. SQL statements are too complex;
3. Configuration Error;
4. The machine cannot be loaded;

1. Index not created

If you see the mysql consumptionCpU is very large, you can use the mysql client tool to check.

Run

/Usr/local/mysql/bin/mysql-hlocalhost-uroot-p

Enter the password. If there is no password, you can enter the client interface without the-p parameter.

Check the current running status

Show full processlist

It can be run several times more

ThisCommandYou can see the SQL statement being executed. It informs you of the executed SQL statement, database name, execution status, Client ip address, account used, and running time.

In my cache backend, no SQL statements are displayed most of the time. I think this is normal. If you see a lot of SQL statements, this mysql will certainly have performance problems

If there is a performance problem, you can analyze it:

1. Is there any SQL statement stuck?

This is often the case. If myisam is used in the database, a write thread may lock the data table. If this statement does not end, other statements cannot be run.

ViewTimeCheck whether the statements have been executed for a long time. Pay attention to these statements.

2. A large number of identical SQL statements are being executed

In this case, the execution efficiency of the SQL statement may be low. Pay attention to these statements as well.

Then, set all the statements you suspect, and use desc (ExPlain) to check these statements.

First, let's look at a normal desc output:

Mysql> desc select * from imgs where imgId= 1651768337;
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | ExTrA |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ------- +
| 1 | SIMPLE | imgs | const | PRIMARY | 8 | const | 1 |
+ ---- + ------------- + ------- + --------------- + --------- + ------- + ------ + ------- +
1 row inSet(0.00 sec)

Note key, rows, and Extra. The results returned by this statement indicate that the SQL statement uses the PRIMARY key index for query. The number of result sets is one and Extra is not displayed, it proves that sorting or other operations are not used. From this result, we can infer that mysql will query the imgid = 1651768337 record from the index, and then retrieve all fields from the real table. This is a very simple operation.

Key indicates the index used by the current SQL statement. mysql can only use one index when executing a simple statement. Note this restriction. rows indicates that the returned result set is large or small, the result set is all matching results of a search using this index. Extra generally displays the query and sorting methods ,.

If the key is not used, or the rows is largeFileSortSorting generally affects the efficiency, for example:

Mysql> desc select * from imgs where userid = 7 mini order by clicks desc limit 10;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- + ----------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- + ----------------------------- +
| 1 | SIMPLE | imgs | ALL | NULL | 12506 | Using where; Using filesort |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------- + ----------------------------- +
1 row in set (0.00 sec)

This SQL result Assembly contains 12506 records, and filesort is used. Therefore, execution is very efficient. At this time, mysql scans the entire table and finds records matching userid = 7mini one by one, and then sorts the clicks of these records. The efficiency can be imagined. If the actual execution is still relatively fast, it is because the server memory is enough to read all the 12506 short records into the memory, so it is relatively fast, however, when the concurrency or table size increases, the efficiency problem is serious.

Then I add the userid to the index:

Create index userid on imgs (userid );

Then check:

Mysql> desc select * from imgs where userid = 7 mini order by clicks desc limit 10;
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
| 1 | SIMPLE | imgs | ref | userid | 51 | const | 8 | Using where; Using filesort |
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
1 row in set (0.00 sec)

Well, we can see that mysql has used the userid index for search. After using the userid index for one search, there are 8 results in the result set. Then, although filesort is used to sort data one by one, the efficiency problem is mitigated because the result set contains only eight partitions.

However, if I use another userid for query, the results will be different:

Mysql> desc select * from imgs where userid = admin order by clicks desc limit 10;
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
| 1 | SIMPLE | imgs | ref | userid | 51 | const | 2944 | Using where; Using filesort |
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
1 row in set (0.00 sec)

The results are basically the same as those of userid = 7mini. However, after mysql indexes a search result set with userid, the size of the result set reaches 2944. All the 2944 records are added to the memory for filesort, efficiency is much worse than that of 7 mini. There are two ways to solve this problem. The first method is to add an index and a judgment condition, because I only need to obtain the maximum 10 pieces of data based on the clicks, so there is a lot of data that I don't need to add to sort. For example, if the number of clicks is less than 10, this data may occupy a large part.

I add an index to clicks, and then add a where condition to query:

Create index clicks on imgs (clicks );

Mysql> desc select * from imgs where userid = admin order by clicks desc limit 10;
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
| 1 | SIMPLE | imgs | ref | userid, clicks | userid | 51 | const | 2944 | Using where; Using filesort |
+ ---- + ------------- + ------- + ------ + --------------- + -------- + --------- + ------- + ------ + ----------------------------- +
1 row in set (0.00 sec)

In this case, we can see that possible_keys is changed to userid, clicks, and possible_keys are all indexes that can be matched. mysql judges from possible_keys and uses one of the indexes to execute the statement. It is worth noting that, the index used by mysql may not be optimized. I still use the userid index to query mysql in this query, but the results remain unchanged. Modify the SQL statement and use index to force mysql to use the clicks index:

Mysql> desc select * from imgs use index (clicks) where userid = \ 'admin \ 'and clicks> 10 order by clicks desc limit 10
+ ---- + ------------- + ------- + --------------- + -------- + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + -------- + --------- + ------ + ------------- +
| 1 | SIMPLE | imgs | range | clicks | 4 | NULL | 5455 | Using where |
+ ---- + ------------- + ------- + --------------- + -------- + --------- + ------ + ------------- +
1 row in set (0.00 sec)

At this time, mysql uses the clicks index for query, but the result set is larger than userid! Again:

Mysql> desc select * from imgs use index (clicks) where userid = \ 'admin \ 'and clicks> 1000 order by clicks desc limit 10
+ ---- + ------------- + ------- + --------------- + -------- + --------- + ------ + ------------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + --------------- + -------- + --------- + ------ + ------------- +
| 1 | SIMPLE | imgs | range | clicks | 4 | NULL | 312 | Using where |
+ ---- + ------------- + ------- + --------------- + -------- + --------- + ------ + ------------- +