Slow SQL Full-Chain Troubleshooting: Execution Plans, Index Failures & Lock Contention

Previous articles covered server resources, JVM tuning, and CPU/thread/memory OOM troubleshooting, solving most application and system layer issues.

In real production, 80% of interface latency, timeouts, and service cascades trace back to slow SQL . Many puzzling incidents show normal application resources (CPU, memory, threads) yet response times spike, batch timeouts occur during peaks, requests pile up, and throughput collapses — the root cause is the database becoming the system bottleneck .

Slow SQL differs from JVM faults: it is hidden, progressive, and highly contagious . Initially no alerts or errors; as data grows, latency creeps from tens of ms to hundreds, seconds, or tens of seconds, eventually dragging down the entire service or even crashing the database.

This final core chapter delivers a complete slow SQL troubleshooting SOP, execution plan interpretation, index failure root causes, transaction blocking, lock waits, and massive data optimization to eliminate database layer issues.

1. Core Insight: Why Slow SQL Is the Biggest Hidden Killer

In distributed systems, application services scale horizontally, but the database is a single-point bottleneck that cannot scale infinitely .

Chain reactions from slow SQL that every developer must remember:

Occasional interface timeouts : Single SQL execution exceeds interface timeout threshold.

Thread pool exhaustion, request pile-up : Business threads block on database queries, cannot release, filling the pool and rejecting new requests.

Transaction timeouts, deadlocks : Slow SQL holds transactions long, row locks not released, causing lock waits, deadlocks, rollbacks.

Database CPU saturation : Massive slow SQL and full table scans consume database compute, stalling all read/write requests.

System-wide cascade : One slow SQL drags down all business interfaces on that database; upstream/downstream services all timeout and circuit-break.

JVM faults are service-local; slow SQL causes full-chain cascades — hence top companies enforce zero-tolerance slow SQL governance.

2. Enterprise-Grade Slow SQL Standard Troubleshooting SOP (Ready to Use)

When facing interface latency or database performance drops, follow this six-step closed-loop process to pinpoint root cause precisely:

Enable slow query log : Locate timeout SQL, execution time, scanned rows, frequency.

EXPLAIN execution plan : Determine if index used, index invalidated, full table scan.

Verify SQL scanned rows : Distinguish large table full scan, index failure, or excessive result set.

Investigate transactions and lock waits : Confirm if slow SQL causes long transactions, row lock blocking.

Targeted optimization : Add indexes, rewrite SQL, split large transactions, optimize pagination.

Load test verification + monitoring guardrails : Validate latency after optimization; add slow SQL alerts for long-term prevention.

3. Slow Query Log Activation and Practical Analysis

First step: capture all timeout SQL . MySQL slow query log is the core data source for production troubleshooting.

1. Core Parameter Configuration (Production Standard)

# Enable slow query log
slow_query_log = 1
# Slow query threshold: execution > 1 second (production standard)
long_query_time = 1
# Log storage path
slow_query_log_file = /var/log/mysql/slow.log
# Log queries not using indexes
log_queries_not_using_indexes = 1

2. Log Core Field Interpretation

Query_time : Actual SQL execution duration, primary judgment metric.

Rows_examined : Rows scanned by database, most critical field .

Rows_sent : Rows finally returned to business.

Lock_time : Lock wait duration, indicates lock contention.

Core verdict : Scanned rows far exceeding returned rows means SQL scans massive invalid data — a typical inefficient SQL requiring optimization.

4. EXPLAIN Execution Plan Comprehensive Interpretation (Optimization Core)

After capturing slow SQL, immediately run EXPLAIN to analyze the plan — one glance judges performance issues, no guessing. Below are the most critical production fields.

1. key Field: Determine Index Usage

key not NULL : Index hit normally, SQL likely has no structural issue.

key is NULL : No index used, full table scan — high-frequency slow SQL root cause.

2. type Field: Performance Level Judgment (Top Priority)

type represents query performance level, best to worst:

system > const > eq_ref > ref > range > index > ALL

ref/range : Normal business query, acceptable performance.

index : Full index scan, poor performance, times out on large data.

ALL : Full table scan, production high-risk issue , must optimize immediately.

3. Extra Field: Locate Hidden Issues

Using filesort : File sort, index not used for sorting, extremely slow on large data.

Using temporary : Creates temporary table, common with GROUP BY, DISTINCT, very poor performance.

Using where : Service layer filters data, index not filtering precisely, performance redundancy.

5. Nine High-Frequency Production Index Failure Root Causes (99% of Slow SQL)

Most slow SQL aren't missing indexes — indexes exist but are completely ineffective , wasted effort. Nine highest-frequency production scenarios:

1. Function operations on indexed columns : left(), substr(), date_format(), numeric operations invalidate index.

2. Implicit type conversion : String index passed number, number index passed string, auto-conversion kills index.

3. Left-side wildcard in LIKE : %xxx, %xxx% leading wildcards cannot hit B+ tree index.

4. OR without full indexes : OR left/right fields only one side indexed, entire index invalidated.

5. NOT IN / NOT EXISTS anti-queries : Large data volumes cause optimizer to abandon index for full scan.

6. Composite index violates leftmost prefix : Skipping leading index columns, unordered queries.

7. IS NULL / IS NOT NULL on indexed columns : High probability of index invalidation.

8. Severe data skew : Index column distribution heavily skewed, optimizer abandons index for full scan.

9. Result set exceeds threshold : Query returns >20% of table rows, optimizer deems full scan faster, drops index.

6. Advanced Failure: Slow SQL Triggering Transaction Blocking and Lock Waits

Beyond pure SQL latency, trickier is cascading lock failures caused by slow SQL — core root cause of many mysterious timeouts, update failures, and interface hangs.

1. Failure Mechanism

MySQL InnoDB uses row-level locks by default. After SQL executes, transaction not committed, row locks not released . If query is slow or transaction runs long, it continuously holds row locks, blocking all subsequent modify/query requests, creating lock wait pile-up, eventually causing business-wide timeouts.

2. Diagnostic Commands

# View current transaction lock waits
show engine innodb status;

# View running slow/long transactions
select * from information_schema.innodb_trx;

3. Solutions

Split large transactions, simplify transaction logic, reduce transaction duration.

Prioritize optimizing slow SQL inside transactions, shorten lock hold time.

Set reasonable transaction timeout to avoid permanent deadlock blocking.

7. Massive Data Scenario Specialized Optimization Strategies

When tables exceed tens of millions of rows, pure SQL tuning and indexing cannot fully solve the problem; architectural-level optimization is required:

Enforce paginated queries : All list/batch queries must not return full datasets; eliminate huge result sets.

Avoid SELECT * : Query only needed fields, reduce I/O transfer and memory footprint.

Cold/hot data separation : Archive historical cold data, reduce primary table volume.

Read/write splitting : Route query traffic to replicas, relieve primary pressure, avoid read/write resource contention.

Sharding : For tables beyond ten million rows, adopt sharding strategy to keep single table under ten million.

8. Emergency Slow SQL Mitigation + Long-Term Governance Standards

1. Emergency Stop-Gap Measures

Temporarily KILL stuck slow SQL, terminate blocking transactions, quickly restore database availability.

Temporarily degrade non-core query interfaces, rate-limit high-frequency queries.

Emergency add missing indexes, rewrite inefficient SQL, rapidly reduce latency.

2. Production Mandatory Governance Rules

Pre-deployment SQL review : All new/modified SQL must verify indexes and execution plans; prohibit inefficient SQL deployment.

Real-time slow SQL alerts : Integrate with monitoring platform; alert immediately on >1s slow SQL for proactive governance.

Regular inspection and optimization : Daily review slow query logs, clean up accumulated inefficient SQL.

Eliminate large transactions : Prohibit bulk insert/update/delete in single transaction, long-running transaction logic.

9. Chapter Summary

This chapter thoroughly covers the complete slow SQL troubleshooting and optimization system : from slow log capture, execution plan reading, index failure root causes, transaction lock blocking, to massive data architectural optimization — full coverage of database-layer production incidents.

Thus we complete the Server layer → JVM layer → Thread layer → Memory layer → Database layer full-dimensional production troubleshooting system, covering 99% of Java production issues, equipping engineers with enterprise-grade fault diagnosis, performance tuning, and stability assurance capabilities.

10. Series Finale Preview

Next article is the final series chapter , consolidating all knowledge into a universal troubleshooting mind map + complete generic troubleshooting SOP collection , structuring the full closed loop from alert, evidence collection, root cause location, stop-gap repair, to postmortem optimization — turning scattered knowledge into a reusable workplace methodology.