Several steps to optimizing the MySQL database

Several steps to analyze the problem:

　　1. Turn on the slow query log.

This step is to record the SQL of the slow query, to prepare for the next step, the relevant knowledge points of this step are as follows:

1. Show variables like '%slow_query_log% '; Check that the logging for the slow query is turned on.

2. Set global slow_query_log=1; Set a slow query to open.

　2. Parse the SQL statement with explain.

Ps:explain SELECT * from temp is as shown:

　　　　　　

The analysis of this step will basically find out where the problem arises. Mainly see the red box of the few points (Type,key,ref,extra). You should avoid the type of all, which means that the SQL is looked up through a full table scan, and the key indicates whether the SQL actually uses the index, and null represents none. Ref means that this operation refers to those things,

such as const (constant). The Extra represents some additional information. such as using index,using filesort (file sorting, try to avoid using).

　　Note : How do I see the order in which the steps are executed? If the ID is the same, execute from top to bottom. If the ID is different, the ID is large first executed.

　　3. Use profile to view the SQL execution cycle, and see if there is a problem with the SQL error on which step to perform.

1. Set profiling=1; The set profile function is turned on. 2. Show Profiles: Used to view the top more than 10 MySQL queries.

　　　　　　

3.show profile for Query ID;

For example: Show profile for Query 2; is to look at the execution life cycle of the second SQL,

　　　　　　

4. Modify the MySQL database configuration file, this step is generally DBA operations.

This step is mainly to modify the MySQL configuration file, such as setting the size of the query buffer, and so on.

Several steps to optimizing the MySQL database