Database Slow Query Log Analysis and SQL Performance Tuning in Practice

Database Slow Query Log Analysis and SQL Performance Tuning in Practice

Problem Description
The slow query log is a database log file that records SQL statements whose execution time exceeds a specified threshold. It is a core tool for performance diagnostics. This topic will explain in detail how to enable the slow query log, interpret its content, and conduct SQL performance tuning based on the log analysis results, covering the complete process and practical techniques.

Step-by-Step Explanation of the Solution Process

Step 1: Enabling and Configuring the Slow Query Log

  1. Verify and Enable the Log

    • In MySQL, use the command SHOW VARIABLES LIKE 'slow_query_log'; to check if the slow query log is enabled. If it is OFF, you need to set slow_query_log = ON in the configuration file (e.g., my.cnf) to enable it.
    • Use SHOW VARIABLES LIKE 'slow_query_log_file'; to view the storage path of the log file.

  2. Set Key Parameters

    • Threshold Setting: The long_query_time parameter defines the threshold for what is considered "slow," in seconds. For example, SET GLOBAL long_query_time = 2; will record SQL statements taking longer than 2 seconds to execute. It can be adjusted based on business sensitivity, such as 0.5 seconds, 1 second, etc.
    • Log Management:
      • Setting log_queries_not_using_indexes = ON will log all queries not using indexes (even if they execute quickly). This helps identify potential missing index issues.
      • Setting log_output = FILE ensures logs are written to a file for subsequent analysis.

Step 2: Interpreting Slow Query Log Content
Once the log is generated, a typical slow query entry contains the following key information:

  • Query_time: 3.451227: The actual execution time of the SQL statement.
  • Lock_time: 0.000135: The time spent waiting for locks.
  • Rows_sent: 1: The number of rows sent to the client.
  • Rows_examined: 1000000: The number of rows the database engine scanned to find that 1 row of data. A situation where Rows_examined is far greater than Rows_sent is a classic sign of an inefficient query.
  • SQL Statement: The complete, original SQL that caused the performance issue is recorded.
  • Timestamp, user host information, etc.

Step 3: Analyzing the Cause of Slow Queries (Core Step)
After identifying a slow SQL query, avoid optimizing blindly. It's essential to locate the bottleneck scientifically.

  1. Use EXPLAIN to Analyze the Execution Plan

    • Prepend the slow SQL with the EXPLAIN keyword (e.g., EXPLAIN SELECT * FROM orders WHERE user_id = 123;) and execute it.
    • Focus on the following columns in the EXPLAIN output:
      • type: Describes the table access/join type. Performance from best to worst is roughly: system > const > eq_ref > ref > range > index > ALL. The appearance of ALL (full table scan) or index (full index scan) usually indicates a need for optimization.
      • key: Shows the index MySQL actually decided to use. If it is NULL, it means no index was used.
      • rows: The number of rows MySQL estimates it needs to scan. This value should be considered alongside Rows_examined from the slow log; a large estimated row count is also a warning sign.
      • Extra: Contains additional information. The appearance of Using filesort (requires extra sorting) or Using temporary (requires creating a temporary table) usually negatively impacts performance.

  2. Identify Specific Problems

    • Case A: Missing Index
      • Symptom: type in EXPLAIN is ALL, key is NULL, and the rows value is huge.
      • Root Cause: Columns in the WHERE clause or JOIN conditions lack a suitable index.
    • Case B: Index Ineffectiveness
      • Symptom: Although key shows an index is used, rows remains very high.
      • Root Cause: Possible reasons include: using functions on indexed columns (e.g., WHERE DATE(create_time) = ...), implicit type conversion (e.g., querying a string column with a number), improper use of OR conditions, ambiguous prefix in LIKE queries (e.g., LIKE '%keyword'), etc.
    • Case C: Poor SQL Writing
      • Symptom: The Extra column shows Using filesort or Using temporary.
      • Root Cause: The order of columns in ORDER BY or GROUP BY doesn't match the index order; SELECT * queries unnecessary columns; complex multi-table join syntax, etc.

Step 4: Implementing Performance Tuning Solutions
Based on the analysis results from the previous step, take targeted measures.

  1. Add Indexes for Queries (Most Common and Effective)

    • Principle: Create indexes for columns frequently used in WHERE, GROUP BY, ORDER BY, and JOIN ... ON conditions.
    • Operation: CREATE INDEX idx_user_id ON orders(user_id);. When creating composite indexes, place columns with higher cardinality (more distinct values) first.

  2. Optimize SQL Query Writing

    • Avoid SELECT *: Query only the necessary columns to reduce data transfer and memory overhead.
    • Optimize Pagination Queries: For deep pagination like LIMIT 10000, 20, avoid direct use. Consider using cursor-based pagination like WHERE id > last_page_max_id LIMIT 20.
    • Break Down Complex Queries: Sometimes, breaking a complex, large multi-table join query into several simpler single-table queries and combining them at the application layer, leveraging the application server's computational power, can be more efficient than database joins.

  3. Optimize Database Architecture and Configuration

    • If single-server performance has reached its limit, consider implementing read/write separation, routing slow read queries to read-only replicas.
    • Adjust database parameters, such as innodb_buffer_pool_size (buffer pool size), to ensure hot data resides in memory.

Step 5: Verifying Optimization Effectiveness
After implementing optimization measures, verification is crucial.

  1. Execute the optimized SQL again, use EXPLAIN to review the execution plan, and confirm that type has improved and indexes are correctly used.
  2. Compare execution times before and after optimization.
  3. Continue monitoring the slow query log to confirm if the SQL has disappeared from it.

By iterating through the above five steps (Monitor -> Analyze -> Optimize -> Verify), you can systematically address database slow query issues and enhance overall system performance.