Database Slow Query Log Analysis and Optimization Practice

Problem Description
The slow query log is a database log file that records SQL statements whose execution time exceeds a specified threshold, serving as a core tool for performance optimization. Interviews often examine how to configure the slow query log, analyze its content, and the complete process of SQL tuning based on the log.

I. Slow Query Log Configuration and Activation

1. Core Parameter Explanation

   • slow_query_log: Set to ON to enable the slow query log (default OFF)
   • long_query_time: Defines the slow query threshold (unit: seconds), e.g., 10.0 means queries exceeding 10 seconds are recorded
   • slow_query_log_file: Specifies the storage path for the log file
   • log_queries_not_using_indexes: Whether to log queries not using indexes (note: may generate a large volume of logs)

2. Configuration Example (MySQL)

   -- Dynamic setting (invalid after restart)  
   SET GLOBAL slow_query_log = 1;  
   SET GLOBAL long_query_time = 2;  
   SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';  
   
   -- Write to configuration file my.cnf for permanent effect  
   [mysqld]  
   slow_query_log = 1  
   long_query_time = 2  
   slow_query_log_file = /var/log/mysql/slow.log  
   

II. Slow Query Log Content Parsing
A single slow query record contains the following key information (using MySQL as an example):

# Time: 2023-10-01T10:00:00.123456Z  
# User@Host: user[root] @ localhost []  
# Query_time: 5.002031  Lock_time: 0.001000 Rows_sent: 1  Rows_examined: 1000000  
SET timestamp=1696154400;  
SELECT * FROM orders WHERE create_date < '2023-01-01';  

• Query_time: Actual SQL execution time (core metric)
• Lock_time: Time waiting for table locks
• Rows_sent: Number of rows returned to the client
• Rows_examined: Number of rows examined at the server layer (higher scans usually indicate worse performance)

III. Slow Query Log Analysis Tools

1. mysqldumpslow (MySQL Official Tool)

   # Count the top 3 most time-consuming slow queries  
   mysqldumpslow -s t -t 3 /var/log/mysql/slow.log  
   
   # Count queries with the longest lock time  
   mysqldumpslow -s l -t 3 /var/log/mysql/slow.log  
   

   • -s Sorting method (t: by time, l: by lock time, r: by returned rows)
   • -t Limit output count

2. pt-query-digest (Percona Toolkit)

   # Generate detailed analysis report  
   pt-query-digest /var/log/mysql/slow.log > slow_report.txt  
   

   Output includes:

   • Total query count and execution time distribution
   • Response time percentage for each SQL
   • Most frequently executed query statements

IV. Practical Optimization Steps Based on Logs

1. Identify High-Cost Queries

   • Prioritize optimizing queries with high execution frequency and long average duration (formula: frequency × single duration)
   • Focus on queries where Rows_examined is much greater than Rows_sent (may indicate full table scans)

2. Analyze Execution Plan of Specific SQL

   EXPLAIN SELECT * FROM orders WHERE create_date < '2023-01-01';  
   

   • Check if the type column is ALL (full table scan)
   • Observe if the key column uses an index

3. Common Optimization Methods

   • Add Index: Create an index for the WHERE condition field create_date

     ALTER TABLE orders ADD INDEX idx_create_date(create_date);  
     

   • Rewrite Query: Avoid SELECT *, only return necessary fields
   • Pagination Optimization: Change LIMIT 10000,10 to WHERE id > last maximum ID

4. Verify Optimization Effectiveness

   • Compare the reduction ratio of Query_time and Rows_examined before and after optimization
   • Use EXPLAIN to confirm index effectiveness

V. Advanced Practical Techniques

1. Dynamic Log Sampling Activation

   -- Enable logging of 1% of slow queries during business peak hours  
   SET GLOBAL log_slow_rate_limit = 1;  
   SET GLOBAL log_slow_rate_type = 'query';  
   

2. Monitor Log File Size
   • Regularly archive historical logs (e.g., daily rotation)
   • Use log_rotation_size parameter to limit single file size

Through systematic use of slow query logs, database performance bottlenecks can be precisely located, achieving an optimization cycle from 'reactive firefighting' to 'proactive prevention'.