MySQL reads slow SQL statements by slow query log

When MySQL uses the slow query log to locate those SQL statements that perform less efficiently, and start with the--log-slow-queries[=file_name] option, Mysqld writes a full execution time of more than Long_ Query_time The log file of the SQL statement for the second, by looking at this log file to locate the less efficient SQL.

The slow query log is not logged after the query is finished, so when the application reflects the problem of execution efficiency, querying the slow query log does not locate the problem, you can use the show processlist command to view the current threads in progress, including the status of the thread, whether the table is locked, etc., to view the SQL in real time , while optimizing some of the lock table operations.

Here's an example of how to locate an inefficient SQL statement using a slow query log:

To turn on the slow query log, configure the sample:

[Mysqld]
Log-slow-queries

Add the above configuration item in the MY.CNF configuration file and restart the MySQL service, when the MySQL slow query function takes effect. The slow query log is written to the parameter DataDir (data directory) under the specified path, and the default file name is Host_name-slow.log.

As with the error log and query log, the format of slow query logging is also plain text and can be read directly. The following example shows the settings and the read process for the slow query log.

First, check the value of Long_query_time.

Mysql> Show variables like ' long% ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| Long_query_time | 10 |
+-----------------+-------+
1 row in Set (0.00 sec)

To facilitate testing, the slow query time will be modified to 5 seconds.

Mysql> set long_query_time=5;
Query OK, 0 rows affected (0.02 sec)

Execute the following two query statements in turn.

The first query does not appear in the slow query log because the query time is less than 5 seconds:

Mysql> Select COUNT (*) from order2008;
+----------+
| COUNT (*) |
+----------+
| 208 |
+----------+
1 row in Set (0.00 sec)

The second query should appear in the slow query log because the query time is greater than 5 seconds:

Mysql> Select COUNT (*) from T_user;
+----------+
| COUNT (*) |
+----------+
| 6552961 |

View the slow query log.

[Email protected] mysql]# more Localhost-slow.log
# time:081026 19:46:34
# [email protected]: root[root] @ localhost []
# query_time:11 lock_time:0 rows_sent:1 rows_examined:6552961
Select COUNT (*) from T_user;

From the above log, you can find that the query time is more than 5 seconds of SQL, and less than 5 seconds does not appear in this log.

If you have a lot of records in the slow query log, you can use the Mysqldumpslow tool (the MySQL client installation comes with) to subtotal the slow query log. The following example summarizes the log file Mysql_master-slow.log, showing only summary results:

[Email protected]_master mysql_data] #mysqldumpslow mysql_master-slow.log
Reading MySQL slow query log from Mysql_master-slow.log
Count:2 time=11.00s (22s) lock=0.00s (0s) rows=1.0 (2), Root[root] @mysql_master
Select COUNT (N) from T_user;

For SQL text exactly the same, only the variable statements, Mysqldumpslow will be automatically treated as the same statement to be counted, the value of the variable is substituted with N. This statistic will greatly increase the efficiency of the user reading the slow query log and quickly locate the SQL bottleneck of the system.

MySQL reads slow SQL statements by slow query log