Master MySQL Performance: Step-by-Step SQL Optimization Techniques

1. Introduction

When an application grows from a prototype with a few rows to production with large volumes, many SQL statements start to expose performance problems that can become system bottlenecks. This guide presents a systematic method for diagnosing and fixing such issues in MySQL.

2. General Optimization Workflow

Identify low‑efficiency statements using the slow‑query log, performance_schema, or other monitoring tools.

Run EXPLAIN (or EXPLAIN ANALYZE in newer versions) and examine the type , rows , filtered and Extra columns.

Enable profiling with SET profiling = 1; and inspect per‑statement timing via SHOW PROFILES and SHOW PROFILE FOR QUERY N.

Turn on the optimizer trace ( SET optimizer_trace="enabled=on"; and SET optimizer_trace_max_mem_size=1000000;) and query information_schema.optimizer_trace to understand why a particular plan was chosen.

Based on the analysis, apply the appropriate remedy: index tuning, query rewrite, alternative storage, or fragmentation handling.

3. Detailed Technical Guidance

3.1 EXPLAIN type Values

ALL : full table scan.

index : full index scan (no row lookup).

range : index range scan, used with <, <=, >=, BETWEEN, IN, etc.

ref : non‑unique index scan or unique‑index prefix scan; returns a row set, common in joins.

eq_ref : unique index lookup (primary‑key join), returns at most one row.

const / system : single‑row lookup treated as a constant.

NULL : no table or index accessed; result is constant.

3.2 Extra Information

Using filesort : MySQL must sort rows after retrieval, adding an extra pass.

Using temporary : Results are stored in a temporary table, which is usually a performance hotspot.

Using index : A covering index satisfies the query without accessing the table rows.

Using index condition (ICP, MySQL 5.6+): Index condition push‑down filters rows at the storage engine level, reducing row‑fetch overhead.

3.3 Profiling Commands

SET profiling = 1;</code>
<code>SHOW PROFILES;</code>
<code>SHOW PROFILE FOR QUERY 42;

3.4 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;

3.5 Common Remedies

Adjust or add indexes (covering indexes, composite indexes, left‑most prefix).

Rewrite SQL: split large IN lists, limit time‑range scans, filter early, avoid functions on indexed columns.

Consider alternative storage for heavy analytics (Elasticsearch, data warehouse).

Handle table fragmentation (periodic OPTIMIZE TABLE or pt‑online‑schema‑change).

4. Scenario‑Based Analyses

4.1 Left‑most Index Matching

MySQL can use a composite index only from its leftmost column onward. To use an index (shop_id, order_no) for a query on order_no alone, the predicate must also include shop_id or the index order must be rearranged.

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)</code>
<code>SELECT * FROM t WHERE shop_id=5 AND order_no='ABC123';

4.2 Implicit Type Conversion

Comparing a numeric literal to a character column forces MySQL to convert the column, which disables the index. Always match data types.

KEY `idx_mobile` (`mobile`)</code>
<code>SELECT * FROM _user WHERE mobile='12345678901';

4.3 Large Pagination

When OFFSET is large, two techniques reduce cost:

Use “keyset pagination”: pass the last retrieved key (e.g., c) and query c < ? instead of OFFSET.

Perform a delayed join: first select only primary keys for the required page, then join back to the full table.

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

4.4 IN + ORDER BY

MySQL evaluates an IN list by scanning each value. When the list exceeds eq_range_index_dive_limit (default 200 in 5.6+), the cost model may ignore the index, leading to sub‑optimal plans.

KEY `idx_shopid_status_created` (`shop_id`,`order_status`,`created_at`)</code>
<code>SELECT * FROM _order WHERE shop_id=1 AND order_status IN (1,2,3) ORDER BY created_at DESC LIMIT 10;

4.5 Range Query Blocking Subsequent Index Use

A range condition on the leftmost column prevents the optimizer from using indexes on later columns.

KEY `idx_shopid_created_status` (`shop_id`,`created_at`,`order_status`)</code>
<code>SELECT * FROM _order WHERE shop_id=1 AND created_at > '2021-01-01 00:00:00' AND order_status=10;

4.6 NOT / != on Indexed Columns

Predicates such as NOT IN, !=, NOT EXISTS on indexed columns prevent index usage. Rewrite the logic if possible.

SELECT * FROM _order WHERE shop_id=1 AND order_status NOT IN (1,2);</code>
<code>SELECT * FROM _order WHERE shop_id=1 AND order_status != 1;

4.7 Optimizer Skipping Index

If the estimated result set exceeds roughly 20 % of the table, the optimizer prefers a full scan via the clustered index rather than a secondary index.

SELECT * FROM _order WHERE order_status = 1;

4.8 Complex Queries

Heavy aggregations or multi‑condition filters are often better served by a data warehouse or search engine.

SELECT SUM(amt) FROM _t WHERE a=1 AND b IN (1,2,3) AND c > '2020-01-01';</code>
<code>SELECT * FROM _t WHERE a=1 AND b IN (1,2,3) AND c > '2020-01-01' LIMIT 10;

4.9 Mixed ASC/DESC Order

Mixing ascending and descending order on different columns can cause the index to be ignored.

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

4.10 Large Data Volumes for Push‑Notification Workloads

Storing recent notification data (e.g., 7‑day retention) in MySQL is acceptable, but frequent deletions cause fragmentation. Periodic OPTIMIZE TABLE or a dedicated purge process is required.

5. References

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

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

MySQL EXPLAIN Output – https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MySQL Cost Model – https://dev.mysql.com/doc/refman/5.7/en/cost-model.html

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