Detailed description of time-consuming SQL instances recorded by mysql, and detailed description of mysqlsql instances

Detailed description of time-consuming SQL instances recorded by mysql, and detailed description of mysqlsql instances

Mysql records time-consuming SQL statements

Mysql records time-consuming SQL statements or SQL statements without indexes in slow log for optimization analysis.

1. mysql slow query log enabled:

Mysql slow query logs are very useful for queries with tracing problems. We can analyze the resource-consuming SQL statements in the current program. How can we open mysql slow query log records?

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

This indicates that the slow log function is not enabled. To enable the configuration file of mysql, add the following two parameters in the configuration file "[mysqld:

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

Description

Long_query_time

This parameter indicates the Metric Time of slow query. The unit is second and the minimum value is 1. The default value is 10. All SQL statements whose execution time exceeds long_query_time are recorded in the slow query log.

Log-slow-queries [= file_name]

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

2. Record the configuration in slow log if no index is used

Add the "log_queries_not_using_indexes" parameter to the mysql STARTUP configuration file or command line parameters to add unused index query statements to slow log.

Example:

[root@localhost mysqlsla-2.03]# more /etc/my.cnf [mysqld]datadir=/var/lib/mysqllog_bin=/tmp/mysql/bin-log/mysql-bin.loglog_bin=ONsocket=/var/lib/mysql/mysql.sockuser=mysql# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1log_slow_queries=/tmp/127_slow.loglong_query_time=1log_queries_not_using_indexes.......

After you restart mysql, the check 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 time-consuming SQL instance recorded by mysql. If you have any questions, please leave a message or go to the community on this site for discussion. Thank you for reading this article and hope to help you. Thank you for your support for this site!