The slow query log consists of SQL statements that take longer than a few seconds to execute for long_query_time and require at least min_examined_row_limit rows to be examined. The slow query log can be used to find queries that take a long time to execute and can therefore be optimized. However, examining long and slow query logs can be a time-consuming task;

mysql long_query_time: The default value is 10 seconds;

The slow query log is that when the query does not return a result for a certain period of time, MySQL will record the executed SQL in the log. This log is called the slow query log. By analyzing the slow query log, you can quickly find out the slow SQL statement, and then optimize it.

The main parameters of the slow query are as follows, which will be described in detail later:

1. Whether to enable the slow query log (slow_query_log)
   By default, MySQL does not enable slow query logs, you need to manually enable them

show variables like 'slow_query_log';
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF    |
+----------------+-------+
1 row in set (0.02 sec)

Viewing the Enabled Status

SHOW VARIABLES LIKE '%slow_query_log%'

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

Enabling Slow Query

set global slow_query_log = 1

If the set global_slow_query_log = 1 command is used to enable slow query logs, they are generated only for the current database and become invalid after MYSQL is restarted.

For this to take effect permanently, you must modify the configuration file my.cnf (as well as other system variables). Modify the my.cnf file and add or modify parameters under [mysqld]

slow_query_log and slow_query_log_file, and then restart the MySQL server. Also configure the following two lines into the my.cnf file

1
2
3
slow_query_log =1

slow_query_log_file=/var/lib/mysql/tim-slow.log
With respect to the slow query parameter slow_query_log_fie, which specifies the path to the slow query log file, a default file named host_name-slow.log is given by default (if slow_query_log_file is not specified).

Again, this can be modified using commands or configured in my.cnf. If the runtime is exactly equal to long_query_time, it will not be recorded. That is, greater than long_query_time in MySQL source code, not greater than or equal to long_QUERy_time!

Set the record threshold:

set global long_query_time=3;

Again, this can be modified using commands or configured in my.cnf. If the runtime is exactly equal to long_query_time, it will not be recorded. That is, greater than long_query_time in MySQL source code, not greater than or equal to long_QUERy_time!

Set the record threshold:

set global long_query_time=3;

So after opening slow query log, what kind of SQL conference record to slow query inside?

Use show variables like ‘long_query_time%’ to view the default time length, in seconds:

Record slow query SQL time (long_query_time)
The slow query log also has an important parameter long_query_time. The default slow query log time of MySQL is 10 seconds. The default size can be modified by modifying the long_query_time threshold. After setting the value of long_query_time, the MySQL database will record all SQL statements whose execution time exceeds this value. SQL statements whose execution time is exactly equal to long_query_time will not be recorded.

Restart MySQL and query again:

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.02 sec)
 
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

At this point, the MySQL data directory will generate slow query log files:

-rw-r----- 1 mysql mysql       180 Sep 11 16:33 slow-log

We execute a SQL that takes a long time, and then view the slow query log file

[root@jeespring mysql]# mysqldumpslow slow-log 
 
Reading mysql slow query log from slow-log
Count: 1  Time=25.13s (25s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[223.70.230.100]
  SELECT * FROM `t_user1` where email='S'

3. Whether to log SQL that does not use indexes (log_queries_not_using_indexes)
   Another parameter related to the slow query log is log_queries_not_using_indexes. If the running SQL statement does not use an index, the MySQL database will also record the SQL statement to the slow query log file.

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

Enable the threshold for logging slow query logs without using indexes

mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)
[root@jeespring mysql]# mysqldumpslow slow-log 
 
Reading mysql slow query log from slow-log
Count: 1  Time=25.13s (25s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@[223.70.230.166]
  SELECT * FROM `t_user1` where email='S'
 
Count: 3  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (3), root[root]@localhost
  SELECT * FROM `account` account where name ='S'

4. log_throttle_queries_not_using_indexes
   MySQL version 5.6.5 has added a parameter log_throttle_queries_not_using_indexes, which is used to indicate the number of SQL statements that are allowed to be logged to the slow log without using indexes per minute. The value defaults to 0, which means no limit. In a production environment, if no index is used, such SQL statements will be frequently recorded in the slow log, resulting in an increase in the size of the slow log file.

mysql> show variables like 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
1 row in set (0.00 sec)