MySQL SQL query optimization method

MySQL Tutorial slow Query analysis method one:

This method I am using, hehe, more like this kind of instantaneous sex.

More than mysql5.0 versions can support logging of slower-performing SQL statements.

Mysql> Show variables like ' long% ';
Note: This long_query_time is used to define the "slow query" which is slower than the number of seconds.

+-----------------+-----------+
| 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, the execution time of more than 1 seconds is slow query.

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: Log logging is turned on

| Slow_query_log_file | /tmp/slow.log |
Note: set to what location

+---------------------+---------------+
3 Rows in Set (0.00 sec)
mysql> set global slow_query_log= ' on '
Note: Open the log record

Once the Slow_query_log variable is set to On,mysql, the record is immediately started.

/ETC/MY.CNF inside can set the above MySQL global variable's initial value.

Long_query_time=1 Slow_query_log_file=/tmp/slow.log

MySQL Slow query analysis method two:

Mysqldumps Tutorial Low Command

/path/mysqldumpslow-s c-t 10/tmp/slow-log
This outputs the 10 SQL statements that have the highest number of records:

-S, is to indicate the way in which C, T, L, R are sorted according to the number of records, time, query time, the number of records returned, AC, at, AL, AR, the corresponding flashback;

-T is the meaning of top N, which is the data that returns the previous number of bars;

-G, you can write a regular matching mode, the case is not sensitive;

Like what

/path/mysqldumpslow-s r-t 10/tmp/slow-log
Gets the 10 queries that return the recordset the most.

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