Detailed explanation of mysql record time-consuming sql example

mysql records time-consuming sql

MySQL can record time-consuming SQL or SQL that does not use indexes in the slow log for optimization and analysis.

1. Enable mysql slow query log:

MySQL slow query log is very useful for tracking problematic queries. It can analyze the SQL statements that consume a lot of resources in the current program. So how to turn on MySQL slow query log recording?

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

mysql> 

This means that the slow log function is not enabled. To enable it, you need to modify the MySQL configuration file and add the following two parameters in the configuration file "[mysqld]":

long_query_time=1
log-slow-queries=/var/mysql/logs/slow.log

illustrate

long_query_time

This parameter indicates the measurement time of slow query in seconds, with a minimum value of 1 and a default value of 10. Any SQL statement that takes longer than long_query_time will be recorded in the slow query log.

log-slow-queries[=file_name]

The file_name parameter is optional. The default value is host_name-slow.log. If the file_name parameter is specified, MySQL will record the slow query log in the file set by file_name. If file_name provides a relative path, MySQL will record the log in the MySQL data directory. This parameter can only be added in the configuration file and cannot be executed in the command line.

2. Configure the slow log to record unused index queries

You can add the "log_queries_not_using_indexes" parameter to the MySQL startup configuration file or command line parameters to add the query statements that do not use indexes to the slow log.

An example is as follows:

[root@localhost mysqlsla-2.03]# more /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
log_bin=/tmp/mysql/bin-log/mysql-bin.log
log_bin=ON
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


log_slow_queries=/tmp/127_slow.log
long_query_time=1
log_queries_not_using_indexes

.......

After restarting mysql, the inspection results are as follows:

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

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

mysql>

The above is a detailed explanation of the SQL example of MySQL recording time-consuming. If you have any questions, please leave a message or discuss in the community of this site. Thank you for reading and hope to help everyone. Thank you for your support of this site!