1. What is a slow query?
The slow query log is a log of queries that take a significant amount of time to execute. It records all
long_query_timeSQL statements that exceed a time threshold set by parameters, helping developers analyze and optimize database query performance. By default,慢查询日志the slow query log is disabled; it must be enabled to use this feature.
2. How to locate slow SQL queries?
1. Simple version: show profile;
When you execute the SHOW PROFILE command, it displays detailed information about the server threads’ execution, including the execution time of each statement executed by each thread, I/O operations, context switching, etc.
Note: This is typically used during development and issue diagnosis, rather than being continuously enabled in a production environment.
Performance overhead :
SHOW PROFILEEnabling this feature places an additional burden on server performance. Each time a query is executed, the MySQL server collects detailed performance information, including CPU time, wait time, and context switch counts. The collection and storage of this information consumes additional CPU and memory resources, which may be unacceptable in a high-concurrency production environment.
— Enable performance monitoring
mysql> set profiling=1;— Execute SQL
mysql> SELECT * from member— Performance Analysis
mysql> show profiles;2. Detailed version: performance_schema (not recommended)
– View the properties of performance_schema
mysql> SHOW VARIABLES LIKE 'performance_schema';3. Enable slow log.
1. Check if the MySQL slow query log is enabled.
If you see that the value of slow_query_log is OFF, it means that MySQL does not enable the slow query log by default.
There’s also a `long_query_time` setting, which defaults to 10 seconds. Queries exceeding 10 seconds are considered slow. It’s recommended to configure your business logic to use 3 seconds.
If `log_queries_not_using_indexes` is set to ON, all queries that do not use indexes will be logged as slow queries.
2. How do I enable slow query logging?
Enable slow log
set global slow_query_log=‘ON’;— Persistence, configured via configuration file
[mysqld]
log_output=FILE,TABLE
slow_query_log=ON
long_query_time=0.001
slow_query_log_file = /usr/local/mysql/mysql-8.0/logs/slow_query.log
#One time, set by command, long_query_time is the time threshold. For the convenience of testing, it is considered that queries exceeding 0.001s are considered slow queries
mysql> SET GLOBAL log_output = 'FILE,TABLE';
mysql> set GLOBAL slow_query_log=ON;
mysql> SET GLOBAL long_query_time = 0.001;
mysql> SET GLOBAL slow_query_log_file = '/usr/local/mysql/mysql-8.0/logs/slow_query.log';
3. Specify the log output format
MySQL slow logs support two output formats: TABLE and FILE. Log persistence is also supported.
1. TABLE Format (Recommended): Slow query logs are stored in a table called `slow_log` in the MySQL database. Each query is inserted as a record into the table, and each record contains detailed query information, such as execution time and query statement. The main advantage of this format is its ease of querying and analysis; records can be filtered and aggregated directly using SQL statements. By querying the `slow_log` table, slow query information can be easily obtained, making it suitable for simple query analysis.
2. FILE Format: Slow query logs are stored on disk as text files. Each log entry is stored as a single line of text, containing detailed query information such as execution time and query statement. The main advantage of this format is its ease of log transmission and storage, allowing for more complex queries and analysis through file operations. By reading the slow query log file, one can gain a detailed understanding of the query execution, including execution time, and perform more in-depth analysis.
MySQL supports two output formats: TABLE and FILE. You can use the following command to check the current output format:
show variables like '%log_output%';
Set the output format:
set global log_output=’FILE’;
set global log_output=’TABLE’;
set global log_output=’FILE,TABLE’;
4. Testing
Because our timeout is set to 10 seconds, the following query will define time out
select sleep(11);View the slow log entries in the TABLE:
select * from mysql.slow_log;Initialize the slow log table:
TRUNCATE TABLE mysql.slow_log;This records information such as query time, the client that initiated the query, the number of rows scanned, and the executed SQL statement.