Uncovering the Root Causes of MySQL Slow Queries: A Step‑by‑Step Guide

Introduction

Slow SQL is a common pain point in high‑performance systems. The article uses a classic interview question to explore the possible reasons behind slow queries and outlines a systematic analysis process.

1. Capturing Slow SQL

Java services often rely on middleware such as Alibaba Druid to log slow queries. MySQL provides built‑in slow‑query logging via the slow_query_log variable, which is disabled by default. Key parameters include:

slow_query_log : 1 to enable, 0 to disable.

slow_query_log_file : Path to the log file (default host_name-slow.log).

long_query_time : Threshold in seconds (default 10, range 0‑31536000).

min_examined_row_limit : Minimum rows examined to record a query.

log_output : FILE, TABLE, or NONE (default FILE).

log_timestamps : UTC or SYSTEM (SYSTEM is recommended).

log_queries_not_using_indexes : Record queries that ignore indexes.

log_slow_admin_statements : Include admin statements.

Typically only slow_query_log and long_query_time need adjustment; thresholds like 1 s or 3 s are common.

Configuration Methods

Two ways to enable slow‑query logging:

# Edit MySQL config file (persistent)
vim /etc/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/sql-slow.log
long_query_time = 1
log_timestamps = SYSTEM
log_output = FILE

# Set variables at runtime (temporary)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/sql-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 100;

Verify the settings:

SHOW VARIABLES LIKE '%slow_query_log%';

2. Analyzing the Slow Log Content

A typical log entry includes timestamp, user/host, query time, lock time, rows sent, rows examined, and the original SQL. Example:

# Time: 2022-11-02T09:23:37.004885Z
# User@Host: wtopps[wtopps] @ localhost []  Id: 10831
# Query_time: 1.609214  Lock_time: 0.003828 Rows_sent: 2050008  Rows_examined: 2150010
SET timestamp=1667381015;
SELECT A.* FROM `user` A LEFT JOIN grade B ON A.`id` = B.`user_id`;

Key fields:

Query_time : Execution time (seconds).

Lock_time : Time waiting for locks.

Rows_sent : Rows returned to the client.

Rows_examined : Rows examined by the engine.

From MySQL 8.0.14 onward, log_slow_extra can be enabled to capture additional metrics such as thread ID, I/O statistics, and sort details.

SET GLOBAL log_slow_extra = ON;

3. Execution Plan Analysis (EXPLAIN)

EXPLAIN reveals how MySQL intends to execute a query. Focus on two primary metrics:

Number of rows scanned.

Number of rows returned.

A low scan‑to‑return ratio indicates efficient use of indexes. The article lists three ways MySQL applies WHERE conditions, from best to worst:

Filter rows using an index (engine‑level).

Covering index (no table lookup).

Full table scan with filtering (server‑level).

Understanding Using index vs. Using where helps identify whether an index is fully utilized.

4. PROFILE Analysis

PROFILE provides per‑stage timing, CPU, and I/O usage. Enable it per session: SET SESSION profiling = 1; Optionally increase the history size: SET SESSION profiling_history_size = 50; Example workflow:

EXPLAIN SELECT * FROM user WHERE name='小六' AND code=300000003;
SHOW PROFILES;
SHOW PROFILE BLOCK IO, CPU, MEMORY, SOURCE FOR QUERY 1;

The output shows that the “executing” stage (reading and processing rows) often dominates runtime, especially when large I/O is involved.

5. OPTIMIZER_TRACE

OPTIMIZER_TRACE (available since MySQL 5.6) records the optimizer’s decision tree in JSON. Enable it: SET SESSION optimizer_trace='enabled=on'; After running EXPLAIN, query the trace:

SELECT * FROM information_schema.optimizer_trace;

The JSON contains steps such as join preparation, condition processing, row estimation, and final plan selection. Key fields to inspect:

range_access_plan : Chosen index and estimated rows.

rows_for_plan and cost_for_plan : Estimated workload.

chosen : Whether the plan was selected.

Because the trace can be large, the article suggests focusing on ref_optimizer_key_uses and rows_estimation sections.

6. Engine Parameter and System I/O Analysis

MySQL’s I/O performance is influenced by OS‑level metrics and InnoDB parameters. Use iostat -x 1 -m to monitor CPU, I/O wait, throughput, and queue length.

Important InnoDB variables:

innodb_io_capacity / innodb_io_capacity_max : Limit IOPS (e.g., 16000 for SSD).

trx_commit and sync_binlog : Control flush frequency; “1” forces a flush each transaction (higher I/O), “20” batches writes (lower I/O).

When I/O metrics (%util, r_await, w_await) approach hardware limits, consider scaling hardware or offloading reads.

7. Other Causes

Beyond SQL logic, performance can degrade due to:

Network jitter : Latency between application and DB servers, especially across regions without dedicated links.

Very large tables : InnoDB stores rows in the primary‑key B‑Tree; tables with tens of millions of rows can suffer even with good indexes. Sharding or partitioning may be required.

Conclusion

The majority of slow queries stem from sub‑optimal SQL (missing indexes, unnecessary columns, poor join order, or inefficient LIKE patterns). Using EXPLAIN, PROFILE, and OPTIMIZER_TRACE together usually pinpoints the bottleneck. System‑level checks (I/O, network, table size) are also essential for a complete performance diagnosis.

References to official MySQL documentation and community articles are provided for deeper reading.