Master MySQL SQL Optimization: Practical Steps and Real-World Cases

Preface

In early 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.

General Steps for SQL Optimization

1. Locate slow SQL via slow query log, etc.

2. Use EXPLAIN to analyze execution plan

Focus on type , rows , filtered , extra . Types from low to high efficiency:

ALL – full table scan

index – full index scan

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

ref – non‑unique index or unique‑index prefix scan, returns a single row, common in joins

eq_ref – similar to ref but uses a unique index (primary key) join

const/system – single‑row, treated as constant (e.g., primary key lookup)

null – MySQL returns result without accessing any table or index

Extra

Using filesort – MySQL must sort rows after retrieval.

Using temporary – a temporary table is created; performance is poor.

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 PROFILE

Shows thread state and time consumption of each phase. Enable with

set profiling = 1;

SHOW PROFILES;
SHOW PROFILE FOR QUERY  #{id};

4. TRACE

Trace reveals how the optimizer chooses the execution plan.

set optimizer_trace="enabled=on";
set optimizer_trace_max_mem_size=1000000;
select * from information_schema.optimizer_trace;

5. Identify Problems and Apply Measures

Optimize indexes

Rewrite SQL (segment IN, time range, filter data)

Consider alternative implementations (Elasticsearch, data warehouse)

Handle data fragmentation

Scenario Analysis

Case 1 – Left‑most matching

Index KEY `idx_shopid_orderno` (`shop_id`,`order_no`). Query must include shop_id or swap column order to use order_no index.

SELECT * FROM _t WHERE orderno='';

Case 2 – Implicit conversion

Column mobile is VARCHAR; using a numeric literal forces implicit conversion and disables the index.

SELECT * FROM _user WHERE mobile=12345678901;

Case 3 – Large pagination

Two approaches: pass the last row’s ordering column ( c) to the next query, or use a 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;

Case 4 – IN + ORDER BY

IN is implemented as n*m search; the cost model may ignore IN cost after a threshold, causing sub‑optimal plans.

Case 5 – Range query blocks subsequent columns

When a range condition appears, columns after it cannot use the index.

Case 6 – NOT / != cannot use index

Avoid NOT, !=, <>, !<, !>, NOT EXISTS, NOT IN, NOT LIKE on indexed columns.

Case 7 – Optimizer prefers full scan

If the result set is a small fraction of the table, the optimizer may still choose a full scan.

Case 8 – Complex queries

Consider using a data warehouse or Elasticsearch for heavy aggregations.

Case 9 – Mixed ASC/DESC

Mixing ASC and DESC in ORDER BY can invalidate the index.

Case 10 – Large data volume

Frequent data deletion creates fragmentation; DBA should handle it.

References

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

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

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

https://www.yuque.com/docs/share/3463148b-05e9-40ce-a551-ce93a53a2c66