How to identify and optimize slow queries in MySQL? (slow query log, performance_schema)

To optimize MySQL slow query, you need to use slow query log and performance_schema: 1. Enable slow query log and set thresholds to record slow query; 2. Use performance_schema to analyze query execution details, find out performance bottlenecks and optimize.

introduction

When you are trapped in the performance optimization maze of MySQL databases, identifying and optimizing slow queries is undoubtedly the key to cracking the maze. Today, we will explore in-depth how to use MySQL's slow query log and performance_schema, two powerful tools, to reveal and solve the culprits that slow down your database. Whether you are a fledgling database novice or an experienced database expert, this article can provide you with practical guidance and unique insights.

Review of basic knowledge

Before you begin to explore specific optimization strategies, you might as well quickly review the tools we will use - slow query log and performance_schema.

The slow query log is a feature of MySQL that records queries whose execution time exceeds a certain threshold. This threshold can be set by the long_query_time variable. By analyzing these logs, we can find slow queries that affect database performance.

performance_schema is a performance monitoring system in MySQL. It provides finer granular performance data, including query execution time, lock waiting time, etc. Through performance_schema, we can not only see the overall execution time of the query, but also gain an in-depth understanding of the performance of the query at each stage.

Core concept or function analysis

Definition and function of slow query log

Slow query log is a log file used by MySQL to record queries whose execution time exceeds the set threshold. Its main function is to help us identify which queries have negative impacts on database performance. By analyzing the slow query log, we can find out the queries that need to be optimized and take corresponding measures.

For example, suppose we set long_query_time to 1 second, then all queries that have been executed for more than 1 second will be recorded in the slow query log.

 -- Enable slow query log SET GLOBAL slow_query_log = 'ON';
-- Set the slow query threshold to 1 second SET GLOBAL long_query_time = 1;

How performance_schema works

The working principle of performance_schema is to monitor MySQL's internal operations through a series of events. These events include query execution, file I/O, lock waiting, etc. By analyzing these events, we can gain insight into the performance of the query at each stage and find performance bottlenecks.

For example, we can view the execution time of a query through the following query:

 SELECT EVENT_NAME, TIMER_WAIT
FROM performance_schema.events_statements_current
WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID());

Example of usage

Basic usage: analyze slow query logs

The most basic way to analyze slow query logs is to use the mysqldumpslow tool, which can help us quickly find the slowest query. For example:

 mysqldumpslow -st -t 10 /path/to/slow-query.log

This command lists the 10 queries that have the longest execution time in the slow query log. Here -st means sorting by time, -t 10 means displaying the first 10 records.

Advanced usage: use performance_schema to optimize query

performance_schema can provide more detailed performance data. For example, we can analyze the execution plan of a query through the following query:

 SELECT * FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT LIKE '%SELECT%'
ORDER BY TIMER_WAIT DESC
LIMIT 1;

This query returns the SELECT statement with the longest recent execution time and displays its execution plan and other performance data. Through this data, we can have a deeper understanding of the performance bottlenecks of the query and take corresponding optimization measures.

Common Errors and Debugging Tips

When using slow query logs and performance_schema, you may encounter some common errors and misunderstandings. For example:

• Forgot to enable slow query log : If slow query log is not enabled, then we cannot record slow query. It can be enabled by SET GLOBAL slow_query_log = 'ON'; ;.
• Set the threshold too high : If long_query_time is set too high, some queries that need to be optimized may be missed. It is recommended to adjust this threshold according to actual conditions.
• Ignoring the overhead of performance_schema : performance_schema will bring certain performance overhead, especially in high-load environments. The overhead can be controlled through the performance_schema_consumer_global_instrumentation variable.

Performance optimization and best practices

In practical applications, optimizing slow query requires combining multiple methods and tools. Here are some optimization strategies and best practices:

• Index optimization : By analyzing slow query logs and performance_schema data, find queries that have not used indexes or are inappropriately used, and add or adjust the index.
• Query rewrite : Some queries can improve performance by rewriting. For example, rewrite complex subqueries to JOIN, or use temporary tables to reduce duplicate calculations.
• Cache optimization : The rational use of MySQL's query cache and application-level cache can significantly improve query performance.
• Hardware Upgrades : In some cases, hardware bottlenecks can be the main cause of slow queries. Appropriate hardware upgrades, such as adding memory or using SSDs, can significantly improve database performance.

During the optimization process, the following points need to be paid attention to:

• Monitoring and tuning is an ongoing process : database performance optimization is not a one-time job, and requires continuous monitoring and tuning.
• Testing and Verification : Before applying any optimization scheme in a production environment, it must be fully tested and verified in the test environment to ensure that no new problems are introduced.
• Balancing performance and resource consumption : While pursuing performance, resource consumption must also be considered. Excessive optimization may lead to waste of resources and affect the overall performance of the system.

Through the above methods and practices, we can effectively identify and optimize slow queries in MySQL, thereby improving the overall performance of the database. I hope this article can provide you with strong support and inspiration on the road to MySQL performance optimization.

The above is the detailed content of How to identify and optimize slow queries in MySQL? (slow query log, performance_schema). For more information, please follow other related articles on the PHP Chinese website!