Master MySQL Performance: 10 Proven SQL Optimization Techniques

Introduction

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.

General SQL Optimization Steps

Locate low‑efficiency SQL via slow‑query logs.

Analyze execution plans with EXPLAIN.

Pay attention to type , rows , filtered and extra fields.

Use SHOW PROFILE to examine query execution details.

Enable optimizer trace to see how the optimizer chooses plans.

Identify the problem and apply appropriate measures.

EXPLAIN Types

ALL : full table scan.

index : full index scan.

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

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) in joins.

const/system : single row, treated as a constant (primary key or unique index lookup).

null : no table or index accessed, returns result directly.

Although the types are ordered from least to most efficient, the optimizer’s cost model may choose a less efficient type depending on row estimates.

EXTRA Information

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

Using temporary : a temporary table stores intermediate results, which is costly and should be optimized.

Using index : a covering index is used, avoiding table row access; if using where also appears, the index cannot fully satisfy the query.

Using index condition : MySQL 5.6+ feature (ICP) that pushes predicates to the storage engine, reducing back‑table lookups.

Profiling

SHOW PROFILES;

SHOW PROFILE FOR QUERY #{id};

Enable profiling with

SET profiling = 1;

Optimizer Trace

SET optimizer_trace="enabled=on";

SET optimizer_trace_max_mem_size=1000000;

SELECT * FROM information_schema.optimizer_trace;

Problem‑Specific Measures

Optimize indexes.

Rewrite SQL (segment IN clauses, segment time ranges, filter data based on previous results).

Consider alternative storage such as Elasticsearch or a data warehouse.

Handle data fragmentation.

Scenario Analysis

1. Left‑most Prefix Matching

Index order matters; to use (shop_id, order_no), the query must include shop_id or swap the column order.

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)

2. Implicit Conversion

Using a numeric literal for a character column forces implicit conversion, causing index loss.

SELECT * FROM _user WHERE mobile = 12345678901;

3. Large Pagination

Two approaches: pass the last row’s ordering value and filter with c < xxx, or use delayed join with a subquery.

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

IN queries are executed as n*m searches; cost calculation may become inaccurate when the IN list exceeds the eq_range_index_dive_limit (default 200).

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 Index Use

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

6. NOT / != Cannot Use Index

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

SELECT * FROM _order WHERE shop_id = 1 AND order_status != 1;

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

7. Optimizer Skipping Index

If the required rows exceed ~20% of the table, the optimizer may prefer the clustered index over a secondary index.

SELECT * FROM _order WHERE order_status = 1;

8. Complex Queries

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

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

For heavy aggregations, consider a data warehouse; for very complex queries, consider alternatives like Elasticsearch.

9. Mixed ASC/DESC

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

Mixing ASC and DESC can invalidate index usage.

10. Large Data Volumes

When storing large volumes (e.g., push notifications) in MySQL with a 7‑day retention, frequent deletions cause fragmentation; DBA intervention may be required.

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