MySQL Slow Query Analysis: Root Causes and Optimization Strategies

This article provides a comprehensive analysis of the causes of slow SQL queries in MySQL databases, covering both system-level and database-level factors. The author explains that "slow" queries have two meanings: queries that are slower than normal (e.g., 100ms vs. 10ms) and queries exceeding the configured slow query threshold (e.g., 500ms).

System-Level Factors

Network Layer Issues: Network packet loss and retransmission can increase data transfer time. During peak traffic (e.g., Double Eleven sales), NIC bandwidth saturation can cause significant delays. Network topology also matters—longer network paths between applications add latency (e.g., 3ms per additional hop).

IO-Related Issues: Disk IO can be impacted by backup operations using compression (xtrabackup with compress option), which consumes CPU and IO resources. RAID card battery charging/discharging cycles change write cache policy from writeback to writethrough, dramatically reducing IO performance. The choice of IO scheduler (noop, deadline, anticipatory) also affects performance—deadline is recommended for database workloads.

CPU Configuration: CPU power management should be set to "maximum performance" mode. Instruction set availability (e.g., HW machines with disabled instruction sets) can cause 15% performance degradation.

Database-Level Factors

Missing or Incorrect Indexes: Full table scans occur when proper indexes are missing or incorrectly designed.

Implicit Type Conversion: When implicit conversion occurs, MySQL's optimizer cannot use appropriate indexes, leading to full table scans. Common scenarios include: mixed types in IN parameters, type mismatches in WHERE clauses (string column compared with numeric value), and inconsistent character sets in JOIN conditions.

Execution Plan Errors: Due to MySQL optimizer limitations, incorrect execution plans may be selected. For example, choosing PRIMARY key index over a more appropriate composite index.

Large Data Volume: Even with indexes, queries returning millions of rows (e.g., 10w+ records) can be slow.

Metadata Lock (MDL) Waits: Long-running queries or uncommitted transactions can block DDL operations, causing subsequent queries to wait.

Concurrent Row Updates: High concurrency scenarios (e.g., flash sales) where multiple sessions update the same row cause lock contention and deadlock detection overhead (O(n) complexity).

Skewed Data Distribution: Queries on columns with highly unbalanced value distributions (e.g., status field with 99.9% values being "1") perform differently for different values.

Inefficient SQL Patterns: Deep pagination queries (e.g., LIMIT 149420, 20) require scanning many rows before retrieving the requested page. Solutions include using covering indexes with "deferred join" technique.

Table Structure Design: Field type choices significantly impact performance—larger VARCHAR or TEXT fields increase both response time and network bandwidth usage.

InnoDB Dirty Page Flushing: Under heavy DML activity, aggressive flushing can saturate IO capacity, causing normally fast queries to slow down.

Undo Retention: Long-running transactions or uncommitted operations prevent UNDO log cleanup, causing queries to traverse more undo records and execute slower.