Mysql SQL Query Optimization Method

Mysql tutorial slow query Analysis Method 1:

I am using this method.

MySQL and later versions Support recording slow SQL statements.

Mysql> show variables like 'long % ';
Note: This long_query_time is used to define how many seconds are slower to calculate as "Slow query"

+ ----------------- + ----------- +
| Variable_name | value |
+ ----------------- + ----------- +
| Long_query_time | 10.000000 |
+ ----------------- + ----------- +
1 row in set (0.00 sec)
Mysql> set long_query_time = 1;
Note: I set 1, that is, if the execution time exceeds 1 second, the query is slow.

Query OK, 0 rows affected (0.00 sec)
Mysql> show variables like 'slow % ';
+ --------------------- + --------------- +
| Variable_name | value |
+ --------------------- + --------------- +
| Slow_launch_time | 2 |
| Slow_query_log | on |
Note: whether to Enable Logging

| Slow_query_log_file |/tmp/slow. log |
NOTE: Where to set

+ --------------------- + --------------- +
3 rows in set (0.00 sec)
Mysql> set global slow_query_log = 'on'
Note: Enable Logging

Once the slow_query_log variable is set to on, mysql starts recording immediately.

In/etc/my. cnf, you can set the initial values of the above mysql global variables.

Long_query_time = 1 slow_query_log_file =/tmp/slow. log
 

 

Mysql slow query Analysis Method 2:

 

Mysqldumps tutorial low command

/Path/mysqldumpslow-s c-t 10/tmp/slow-log
This will output 10 SQL statements with the maximum number of records, of which:

-S indicates the sorting method. c, t, l, and r are sorted by the number of records, time, query time, and number of returned records, ac, at, al, and ar indicate reverse descriptions;

-T indicates the top n, that is, the number of previous data records returned;

-G, followed by a regular expression matching mode, which is case insensitive;

For example

/Path/mysqldumpslow-s r-t 10/tmp/slow-log
You can obtain up to 10 queries from the returned record set.

/Path/mysqldumpslow-s t-t 10-g "left join"/tmp/slow-log