In actual work, I face many users who often encounter a problem, that is, when using
MySQL, it takes a long time to execute a statement to return. Why? When you are using MySQL, when you find some performance problems, such as executing a statement that takes a long time to return results, we call it a slow query. Generally speaking, the reasons for a slow query are:
There is a problem with your index design, which may cause each execution statement to be a full table scan, which is time-consuming;
Your SQL statement is not written well, which may lead to longer query time;
MySQL chooses the wrong index, which will also cause a full table scan.
Normally, before the business goes online, the slow query of MySQL will be turned on in the test environment, that is, longquerytime is set to 0, so as to ensure that every statement is recorded in the slow log. For specific configuration, please refer to the following .
MySQL's slow query log is a log record provided by MySQL, which is used to record statements in MySQL whose response time exceeds the threshold. By default, MySQL does not start the slow query log. We need to manually set this parameter. Of course, if it is not required for tuning, it is generally not recommended to enable this parameter, because turning on the slow query log will bring more or less certain The performance impact.
The slow query log supports writing log records to files and also supports writing log records to database tables.
Slow query configuration
By default, the value of slowquerylog is OFF, indicating that the slow query log is disabled. It can be turned on by setting the value of slowquerylog, as shown below:
mysql> show variables like'%slow_query_log%'
->;
+---------------------+--------------------------- ----+
| Variable_name | Value |
+---------------------+--------------------------- ----+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+--------------------------- ----+
2 rows in set (0.00 sec)
Enable slow query log:
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
Then look at the status:
mysql> show variables like'%slow_query_log%';
+---------------------+--------------------------- ----+
| Variable_name | Value |
+---------------------+--------------------------- ----+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/mysql-slow.log |
+---------------------+--------------------------- ----+
2 rows in set (0.00 sec)
Use set global slowquerylog=1 to turn on the slow query log only for the current database, and it will become invalid after MySQL restarts. If you want to take effect permanently, you must modify the configuration file my.cnf (the same is true for other system variables).
For example, as follows:
[root@mysql ~]# vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysql-slow.log
So after the slow query log is turned on, what kind of SQL will be recorded in the slow query log? Whether it will be written to the slow log is controlled by the parameter longquerytime. By default, the value of longquerytime is 10 seconds, which can be modified by command or in the my.cnf parameter. The case where the running time is exactly equal to longquerytime will not be recorded. In other words, in the MySQL source code, the judgment is greater than longquerytime, not greater than or equal. Starting from MySQL 5.1, longquerytime began to record the running time of SQL statements in microseconds. Previously, it was only recorded in seconds. If it is recorded in the table, only the integer part will be recorded, not the microsecond part.
Check the long_time value, the default is 10 seconds.
mysql> show variables like'%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
Set the value, for example, set it to 5 seconds here:
mysql> set global long_query_time=5;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like'%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
As shown above, the variable longquerytime is modified, but the value of the query variable longquerytime is still 10.
After using the command set global longquerytime=5 to modify, you need to reconnect or open a new session to see the modified value. Because by using show variables like'longquerytime' to view the variable values of the current session.
You can also use show global variables like'longquerytime'; instead of reconnecting the session, as shown below:
mysql> show global variables like'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
We set the value of longquerytime to 2
mysql> set global long_query_time=2;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
Then execute a statement with a timeout of 5 seconds (reconnection to MySQL is required):
[root@mysql ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \g.
Your MySQL connection id is 4
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type'help;' or'\h' for help. Type'\c' to clear the current input statement.
mysql> show variables like'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.02 sec)
mysql> select sleep(5)
->;
+----------+
| sleep(5) |
+----------+
| 0 |
+----------+
1 row in set (5.01 sec)
