How to enable mysql slow query

1. What is slow query?

When the time it takes for MySQL to execute a SQL query statement exceeds a specific threshold, the query will be marked as a slow query. A slow query is usually defined as a query that takes more than one second, but this threshold can be adjusted depending on the situation.

Slow queries are usually caused by the following reasons:

1. The SQL query statement itself is not optimized enough.

2. The index in the database is not complete enough.

3. The physical structure design of the database is unreasonable.

MySQL performance is severely affected when slow queries are present, as it causes the database server's CPU and memory usage to spike. Therefore, we need to locate the cause of slow query as soon as possible and optimize it.

2. How to enable slow query?

In MySQL, it is very simple to enable the slow query function. We only need to add the following parameters to the MySQL configuration file:

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

Among them, the log-slow-queries parameter is used to specify slow queries. The path and file name of the log file. The long_query_time parameter is used to specify the query time threshold in seconds. All queries that take more than 1 second will be recorded in the slow query log file.

In order for the new configuration to take effect, we need to restart the MySQL service after adding these two parameters. In the CentOS operating system, we can use the following command to restart the MySQL service:

systemctl restart mysqld

Of course, this command may also vary depending on the system, please adjust it according to the specific situation.

3. How to analyze slow query logs?

It is necessary to review the slow query log regularly to discover and deal with slow query problems. Before this, the slow query log needs to be enabled. We can use the mysqldumpslow tool that comes with MySQL to analyze slow query logs. This tool supports multiple sorting methods and can easily help us find the cause of slow queries.

The following are several commonly used commands:

# 按查询次数从大到小排序
mysqldumpslow -s c /var/log/mysql/mysql-slow.log

# 按查询时间从大到小排序
mysqldumpslow -s t /var/log/mysql/mysql-slow.log

# 按查询锁定的行数从大到小排序
mysqldumpslow -s l /var/log/mysql/mysql-slow.log

Before using these commands, we need to ensure that we have permission to access the slow query log file. Slow query log files are usually stored under the path /var/log/mysql/mysql-slow.log.

Analyzing slow query logs is not an easy task and requires certain experience and skills. Usually, we analyze slow query logs based on multiple dimensions such as the execution time of the query, the number of queries, the number of locked rows in the query, etc., in order to find the direction for optimization.

4. How to optimize slow queries?

After analyzing the slow query log, we need to propose an optimization plan based on the analysis results. The following are several common optimization solutions:

1. Optimize SQL query statements: Modify SQL query statements, use indexes as much as possible, and avoid using query methods such as full table scans.

2. Optimize indexes: Add or modify indexes to tables in the database to complete query operations faster.

3. Optimize the physical structure: Adjust the physical structure of the database, including table partitioning, partitioning and other operations, in order to better manage the database.

4. Optimize caching: Use caching technology to reduce the number of database queries as much as possible, thereby increasing the data query speed.

To optimize slow queries, you need to comprehensively consider multiple factors such as the physical and logical structure of the database and query statements. This is a very complex process. During the optimization process, interference to the database system should be minimized while ensuring system stability.

The above is the detailed content of How to enable mysql slow query. For more information, please follow other related articles on the PHP Chinese website!