SQL Optimization: Common Steps, Index Types, and Practical Case Analyses

When a project grows from a small dataset to large production volumes, many SQL statements begin to show performance problems that can become system bottlenecks.

SQL Common Optimization Steps

Locate low‑efficiency SQL via slow‑query logs.

Analyze the execution plan with EXPLAIN, focusing on type, key, rows, filtered and extra.

Use SHOW PROFILE to see thread states and time consumption (enable with SET profiling = 1;).

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

Apply generic solutions: optimize indexes, rewrite SQL, consider alternative implementations (ES, data warehouse), or split data.

EXPLAIN Type Hierarchy

1、ALL          全表扫描
2、index        索引全扫描
3、range        索引范围扫描（常用于 <, <=, >=, BETWEEN, IN）
4、ref          使用非唯一索引或唯一索引前缀扫描，返回单行记录
5、eq_ref       使用唯一索引（主键）进行关联查询
6、const/system 单条记录，系统把匹配行的其他列当作常量处理
7、null         MySQL 不访问任何表或索引，直接返回结果

Although the types improve from top to bottom, the actual cost model determines the chosen plan.

EXTRA Information

1、Using filesort：需要额外一次传递来完成排序。
2、Using temporary：使用临时表保存中间结果，性能较差。
3、Using index：使用覆盖索引，避免访问表数据行。
4、Using index condition：MySQL 5.6+ 的索引下推（ICP），在存储层过滤数据。

Trace Analysis

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

General Solutions

Optimize indexes.

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

Use alternative storage such as Elasticsearch or a data warehouse.

Split data or business logic to reduce scan size.

Scenario Analyses (10 Cases)

Case 1 – Left‑most Index Matching

KEY `idx_shopid_orderno` (`shop_id`,`order_no`)
SELECT * FROM test WHERE orderno='';

To use the index, the query must include the leftmost column shop_id or reorder the index.

Case 2 – Implicit Conversion

KEY `idx_mobile` (`mobile`)
SELECT * FROM _user WHERE mobile=12345678901;

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

Case 3 – Large Pagination

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

Solutions: pass the last c value for "c < …" pagination, or use delayed join with a covering index.

Case 4 – IN + ORDER BY

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

MySQL evaluates IN by scanning each value; when the list exceeds eq_range_index_dive_limit (default 200), cost calculation may become inaccurate.

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

Range conditions on created_at prevent the optimizer from using the following order_status column.

Case 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, !=, <> and similar operators on indexed columns.

Case 7 – Optimizer Skips Index for Large Result Sets

SELECT * FROM test_order WHERE order_status=1;

If the query would read a large portion of the table, the optimizer may prefer the clustered index.

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

Consider using a data warehouse or Elasticsearch for heavy analytics.

Case 9 – Mixed ASC/DESC Order

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

Mixing ASC and DESC can cause the index to become unusable.

Case 10 – Large Data Retention

When storing massive push‑notification data in MySQL with a 7‑day retention, frequent deletions cause fragmentation; DBA intervention is required.

Overall, the article provides a systematic approach to diagnosing and fixing MySQL performance issues through careful use of indexes, query rewriting, profiling tools, and, when necessary, alternative storage solutions.