Comprehensive Guide to MySQL Query Optimization and Common Pitfalls

In the early stages of application development, small data volumes cause developers to prioritize functionality, but as production data grows, many SQL statements expose performance problems that can become system bottlenecks.

SQL Optimization General Steps

Locate low‑efficiency SQL statements via slow‑query logs.

Analyze execution plans with EXPLAIN.

Use SHOW PROFILE to examine thread states and time consumption.

Enable optimizer trace to understand why a particular execution plan was chosen.

Apply appropriate measures based on the analysis results.

Key Fields in EXPLAIN Output

type : ALL (full table scan), index (full index scan), range (index range scan for <, <=, >=, BETWEEN, IN), ref (non‑unique index or prefix scan returning a single row), eq_ref (unique index scan, usually primary key), const/system (single row treated as constant), null (no table or index accessed).

Focus on type, rows, filtered, and extra columns.

Extra Information

Using filesort: MySQL performs an extra pass to sort rows after scanning.

Using temporary: MySQL creates a temporary table for intermediate results, which is costly.

Using index: A covering index is used, avoiding table row access; if combined with Using where, the index cannot fully satisfy the query.

Using index condition: Index Condition Pushdown (ICP) filters rows at the storage engine level.

Profiling Commands

SHOW PROFILES;</code>
<code>SHOW PROFILE FOR QUERY #{id};

Optimizer Trace Commands

SET optimizer_trace="enabled=on";</code>
<code>SET optimizer_trace_max_mem_size=1000000;</code>
<code>SELECT * FROM information_schema.optimizer_trace;

Typical Optimization Measures

Optimize indexes.

Rewrite SQL (segment IN queries, time‑range queries, incremental filtering).

Consider alternative storage/processing (e.g., Elasticsearch, data warehouse).

Handle data fragmentation.

Scenario Analyses

1. Left‑most Prefix Matching : Indexes work best when query conditions follow the leftmost columns of a composite index.

2. Implicit Type Conversion : Comparing a character column with a numeric literal forces MySQL to convert values, causing index loss.

3. Large Pagination : For huge offsets, either pass the last retrieved key and use WHERE c < ... or apply delayed join with a covering index.

SELECT t1.* FROM _t t1, (SELECT id FROM _t WHERE a=1 AND b=2 ORDER BY c DESC LIMIT 10000,10) t2 WHERE t1.id = t2.id;

4. IN + ORDER BY : Use a composite index matching the order of IN values and the ORDER BY column; be aware of the cost model limit ( eq_range_index_dive_limit) that may affect plan choice.

SELECT * FROM _order WHERE shop_id=1 AND order_status IN (1,2,3) ORDER BY created_at DESC LIMIT 10;

5. Range Query Blocking Subsequent Columns : Once a range condition is applied, later columns cannot use the index efficiently.

SELECT * FROM _order WHERE shop_id=1 AND created_at > '2021-01-01 00:00:00' AND order_status=10;

6. NOT, !=, <> and Similar Operators : These operators prevent index usage; avoid them on indexed columns.

SELECT * FROM _order WHERE shop_id=1 AND order_status NOT IN (1,2);

7. Optimizer Skipping Indexes : When a query touches a large portion of the table (≈20%+), the optimizer may favor a full scan via the clustered index. SELECT * FROM _order WHERE order_status = 1; 8. Complex Queries : For heavy aggregations or multi‑condition filters, consider moving the workload to a data warehouse or search engine like Elasticsearch.

SELECT SUM(amt) FROM _t WHERE a=1 AND b IN (1,2) AND c > '2020-01-01';

9. Mixed ASC/DESC Order : Mixing sort directions can invalidate index usage.

SELECT * FROM _t WHERE a=1 ORDER BY b DESC, c ASC;

10. Big Data Considerations : Storing massive time‑sensitive data in MySQL may require frequent cleanup, leading to fragmentation that should be managed by a DBA.

Author: 狼爷 (Wolf Master) – Source: cnblogs.com/powercto/p/14410128.html