Mastering MySQL Slow Query Diagnosis and Index Optimization

Introduction

The tcorder table stores millions of e‑commerce orders; a typical pagination query suffers from severe latency due to a non‑optimal index layout. The article starts with a concrete slow‑SQL example and demonstrates systematic analysis.

Slow‑SQL Analysis

Typical query:

select order_id from tcorder where is_main = 1 and buyer_id = 117075031 order by create_time desc, order_id asc limit 0,10;

Key metrics from SHOW PROFILE and EXPLAIN show high cost (≈2017 ms), large logical_read (≈40 000 pages) and a Using filesort operation.

Index Theory

The article reviews B+Tree vs. B‑Tree, node structure, page size (16 KB), record layout, and how index height grows with row count. For tcorder (≈8 M rows) the primary‑key B+Tree height is 4, secondary index height is 3. Formulas for maximum records per page are provided.

Index Push‑Down (ICP)

When a condition can be evaluated inside the storage engine, Using index condition appears in EXPLAIN. Example:

explain select * from tcorder where parent_id = 37 and from_group > 3;

Shows index_condition filtering both columns, reducing back‑table lookups from 10 to 1.

Sorting Optimization

MySQL can use an index for ORDER BY only if the sort order matches the index leaf order. The article lists sortable and non‑sortable cases and explains filesort cost, in‑memory quicksort, priority‑queue optimization, and disk‑based merge sort. Adding the primary key to the sort key stabilizes order.

EXPLAIN Formats

Both table and JSON output are illustrated. JSON provides detailed cost_info, index_condition, and attached_condition sections, helping to pinpoint where filtering occurs.

Query Profiler

Shows how to enable profiling, retrieve stages (e.g., starting, optimizing, Sending data), and interpret the INFORMATION_SCHEMA.PROFILING table. Highlights that profiling is removed in MySQL 8.0 in favor of EXPLAIN ANALYZE.

Index Change SOP

Identify top slow‑SQLs via dbTrace and extract SQL_PATTERN_ID.

Gather full SQL with parameters and trace ID.

Analyze with EXPLAIN and monitor execution statistics.

Confirm root cause (index, CPU, I/O, network) and discuss with owners.

Design new composite index ordering fields by selectivity, partition key, and sort key.

Submit DMS design ticket, test in staging, then roll out gradually (per‑shard or per‑instance gray‑scale).

Silence old index, verify new index visibility with EXPLAIN, monitor for a week.

Perform shadow‑table testing, pressure testing, and full‑scale validation before dropping the old index.

Gray‑Scale Deployment

Deploy index changes on 1 % of traffic, monitor RT, slow‑SQL count, CPU, and I/O. Use DMS to control execution order, pause if anomalies appear, and silence old indexes after confirming stability.

Common Slow‑SQL Causes

Missing selective indexes → full scans and many logical reads.

Range vs. equality order in composite indexes.

LIKE patterns with leading wildcard.

Functions on indexed columns (e.g., IFNULL) preventing push‑down.

Optimizer choosing a sub‑optimal index due to stale statistics.

Filesort caused by mismatched ORDER BY.

Large COUNT(*) scans.

Application‑side issues: GC pauses, high active connections, disk I/O bottlenecks, network packet loss.

References

MySQL 5.7 SHOW PROFILE documentation.

MySQL ORDER BY optimization guide.

MySQL LIMIT optimization guide.

Juejin article on MySQL order‑by internals.

Alibaba internal technical blogs.

Team Introduction

The author is from the Taobao‑Tian Marketing & Transaction Technology team, responsible for high‑traffic e‑commerce systems, price engine, and large‑scale event support.