Comprehensive Guide to SQL Optimization Steps and Common Scenarios

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

SQL Optimization General Steps

Identify low‑efficiency SQL via slow‑query logs or similar tools.

Use EXPLAIN to analyze the execution plan, paying attention to type , rows , filtered , and extra fields.

Run SHOW PROFILE to see thread states and time consumption (enable with SET profiling = 1; and query with SHOW PROFILES; and SHOW PROFILE FOR QUERY #id; ).

Enable optimizer trace to understand why a particular plan was chosen (e.g., SET optimizer_trace='enabled=on'; , SET optimizer_trace_max_mem_size=1000000; , then query SELECT * FROM information_schema.optimizer_trace; ).

Determine the root cause and apply appropriate measures.

EXPLAIN Output 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 : unique index (often primary key) join.

const/system : single row, treated as constant.

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 higher‑cost type if it reduces the number of rows examined.

EXTRA Information

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

Using temporary : A temporary table stores intermediate results; this is usually a performance hotspot.

Using index : A covering index satisfies the query without accessing the table rows; if combined with WHERE , the index alone may not be sufficient.

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

Profiling Commands

SHOW PROFILES;
SHOW PROFILE FOR QUERY #id;

Trace Commands

SET optimizer_trace='enabled=on';
SET optimizer_trace_max_mem_size=1000000;
SELECT * FROM information_schema.optimizer_trace;

Problem‑Driven Measures

Optimize indexes.

Rewrite SQL (segment IN clauses, time ranges, incremental filtering).

Consider alternative implementations such as Elasticsearch or a data warehouse.

Handle data fragmentation.

Scenario Analysis

1. Left‑most Prefix

To use an index on (shop_id, order_no) , the query must include shop_id or the columns must be reordered to match the index definition.

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

Example query:

SELECT * FROM _t WHERE orderno='';

Because MySQL matches columns from left to right, the condition must contain shop_id to use the composite index.

2. Implicit Conversion

KEY `idx_mobile` (`mobile`)

SELECT * FROM _user WHERE mobile=12345678901;

Using a numeric literal for a character column forces an implicit conversion, causing the index to be ignored.

3. Large Pagination

KEY `idx_a_b_c` (`a`,`b`,`c`)

SELECT * FROM _t WHERE a=1 AND b=2 ORDER BY c DESC LIMIT 10000,10;

Two optimization options:

Pass the last retrieved c value and query c < xxx (requires API change).

Use delayed join with a covering index to avoid back‑table lookups:

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

KEY `idx_shopid_status_created` (`shop_id`,`order_status`,`created_at`)

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

MySQL searches IN values using an n*m algorithm; the cost model may mis‑estimate when the IN list exceeds eq_range_index_dive_limit (default 200), leading to sub‑optimal index choices.

5. Range Query Blocking

KEY `idx_shopid_created_status` (`shop_id`,`created_at`,`order_status`)

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

Range conditions on earlier columns prevent later columns from using the index.

6. NOT / != / NOT IN

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 these operators on indexed columns because they disable index usage.

7. Optimizer Skipping Indexes

If the queried rows exceed roughly 20% of the table, the optimizer may prefer a full scan via the clustered index.

SELECT * FROM _order WHERE order_status = 1;

Even with an index, low‑selectivity queries may not use it.

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 analytics, consider a data warehouse or Elasticsearch instead of raw MySQL.

9. ASC / DESC Mixed Ordering

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

Mixing ASC and DESC can cause the index to be ignored.

10. Big Data Considerations

When storing large volumes (e.g., push‑notification data) in MySQL with a 7‑day retention, frequent deletions cause fragmentation; coordinate with a DBA for fragment handling.

Reference Materials

深入浅出MySQL：数据库开发、优化与管理维护 (Tang Hanming et al.)

MySQL技术内幕——InnoDB存储引擎 (Jiang Chengyao)

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