SQL Optimization Steps and Common Scenarios: Index Usage, EXPLAIN, Profiling, and Trace

In the early stages of application development, data volume is small and developers focus on functionality, but as production data grows, many SQL statements reveal performance problems that can become system bottlenecks.

1. Locate slow SQL statements using slow‑query logs.

2. Analyze execution plans with EXPLAIN . Pay attention to the type, rows, filtered and extra columns. The type hierarchy from least to most efficient is:

ALL – full table scan

index – full index scan

range – index range scan (used with <, <=, >=, BETWEEN, IN)

ref – non‑unique index or prefix of a unique index, returns multiple rows

eq_ref – unique index (usually primary key), returns a single row

const / system – single row, treated as a constant

null – no table or index accessed, returns result directly

Extra information (the Extra column) includes: Using filesort – MySQL needs an extra pass to sort rows. Using temporary – a temporary table is created, which is costly. Using index – a covering index is used, avoiding table row access. Using index condition – index condition pushdown (ICP) filters rows at the storage engine level.

3. Show profiling to see thread states and time consumption. Enable it with SET profiling = 1; and view results with:

SHOW PROFILES;

SHOW PROFILE FOR QUERY <span id="id">{id}</span>;

4. Trace optimizer decisions using the trace feature:

SET optimizer_trace="enabled=on";

SET optimizer_trace_max_mem_size=1000000;

SELECT * FROM information_schema.optimizer_trace;

5. Determine the problem and apply appropriate measures such as optimizing indexes, rewriting SQL, using alternative storage (ES, data warehouse), handling data fragmentation, etc.

Common scenarios and case studies :

Case 1 – Leftmost index : To use a composite index (shop_id, order_no), the query must include shop_id or the index order must be changed.

Case 2 – Implicit conversion : Querying a VARCHAR column with a numeric literal causes an implicit conversion that disables the index. Use string literals instead.

Case 3 – Large pagination : Prefer "keyset pagination" (using the last row's value) or delayed join to avoid costly OFFSET scans.

Case 4 – IN + ORDER BY : Large IN lists are evaluated as n*m searches; the optimizer may ignore cost after a threshold (default 200), leading to sub‑optimal plans.

Case 5 – Range query blocking : A range condition on the first column prevents the use of indexes on subsequent columns.

Case 6 – NOT / != : These operators prevent index usage; consider rewriting or using index condition pushdown.

Case 7 – Optimizer skips index : When the estimated row count exceeds ~20% of the table, the optimizer may prefer a full index scan.

Case 8 – Complex queries : For heavy aggregations, consider a data warehouse or external search engine (e.g., Elasticsearch).

Case 9 – Mixed ASC/DESC : Mixing sort directions disables index usage.

Case 10 – Big data : For high‑volume data with retention policies, monitor fragmentation and schedule cleanup.

References:

《深入浅出 MySQL：数据库开发、优化与管理维护》

《MySQL 技术内幕——InnoDB 存储引擎》

MySQL 5.7 EXPLAIN documentation

MySQL cost model documentation

Additional online resources

Feel free to discuss, ask questions, or contact the author for further clarification.