MySQL's slow query log is an important tool for optimizing database performance, helping to locate performance bottlenecks by recording SQL statements whose execution time exceeds a specified threshold. 1. Enable slow query logs to set slow_query_log, slow_query_log_file and long_query_time parameters in the configuration file; 2. Use the mysqldumpslow or pt-query-digest tools to analyze logs, and focus on key fields such as Query_time, Lock_time, Rows_sent and Rows_examined; 3. Common problems include the lack of indexes that lead to full table scanning, unreasonable query design, and low efficiency in sorting and grouping operations. They can be optimized by adding indexes, rewriting query statements, and optimizing compound indexes. Regularly viewing and combining business scenario analysis slow query logs can help identify potential performance problems.

MySQL's Slow Query Log is an important tool for optimizing database performance. If you find that the database response is slow, or want to troubleshoot potential performance bottlenecks in advance, enabling and analyzing slow query logs is a good starting point.

What is a slow query log?

Slow query logs are SQL statements whose execution time exceeds the specified threshold. By default, the "slow" standard is 10 seconds, but you can adjust this time according to actual needs.

After it is enabled, MySQL will record the queries that meet the conditions into the log, including execution time, number of scanned rows, whether to use indexes, etc. This information can help you quickly locate which SQL is the culprit of "straight".

To enable slow query logs, you can add them in the configuration file my.cnf or my.ini :

 slow_query_log = 1
slow_query_log_file = /path/to/your/slow-query.log
long_query_time = 1

The slow query time is set to 1 second here, which is more suitable for the performance requirements of most production environments.

How to analyze slow query logs?

With the log, the next step is to analyze it. Although you can view the content by opening the log file directly, it is not intuitive enough. Some tools are recommended to help understand log content, such as:

• mysqldumpslow : This is a command line tool that comes with MySQL, which can summarize and sort slow queries.

  Example:

   mysqldumpslow -s at -t 10 /path/to/slow-query.log

  The above command will be sorted by the average execution time, showing the first 10 slowest queries.

• pt-query-digest : This tool in Percona Toolkit is more powerful and supports more complex analysis, such as classification statistics by user, IP, execution plan and other dimensions.

In addition to tools, you also need to pay attention to several key fields in the log:

• Query_time: Total execution time
• Lock_time: the time to wait for the lock
• Rows_sent: The number of rows returned to the client
• Rows_examined: Number of scanned rows

If a query scans a large amount of data but returns only a few results, it is likely that the appropriate index is missing, or the query conditions are not accurate enough.

Frequently Asked Questions and Optimization Suggestions

After analyzing the slow query, the following common situations are usually encountered:

1. Lack of indexing leads to full table scanning

This type of problem usually manifests as Rows_examined is very large and Rows_sent is very small. For example:

 SELECT * FROM orders WHERE customer_id = 123;

If customer_id has no index, the entire order table will be scanned every time the execution is executed. The solution is simple: add an index for customer_id .

2. The query design is unreasonable

Some queries may be written in a more complex way, such as nesting multi-layer subqueries, unnecessarily joining multiple tables, or not using paging restrictions. Such problems can be optimized by simplifying logic or splitting queries.

For example:

 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

This writing method is not efficient when the data volume is large, so you can consider rewriting it to JOIN:

 SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.total > 1000;

3. The time-consuming operation of sorting and grouping

If you see a query Using filesort or Using temporary , it means that MySQL cannot effectively utilize indexes when sorting or grouping. At this time, you should check whether there is a suitable composite index that can support these operations.

For example, for the following statement:

 SELECT name, COUNT(*) FROM sales GROUP BY product_id ORDER BY COUNT(*) DESC;

It can be very slow if there is no proper index. You can try to create an index for (product_id, name) to improve grouping efficiency.

Basically that's it. Slow query logs are not a panacea, but it is a great place to start. As long as you check regularly and analyze the actual business scenarios, you can often find many hidden performance problems.

The above is the detailed content of Leveraging the MySQL Slow Query Log for Tuning. For more information, please follow other related articles on the PHP Chinese website!