How to Diagnose and Fix Slow MySQL Queries: A Surgeon’s Guide

Preface

"Su Gong, the order list crashed!" When I answered the call I was stunned by a 999 ms SQL response time on the monitoring screen. Over the years I’ve found a rule: all SQL problems explode at 3 am. Today I’ll dissect a slow‑SQL case with an architect’s mindset.

1 Pre‑operation Check: Locate the Problem

1.1 Use EXPLAIN to View Execution Plan

EXPLAIN shows the execution plan, like an X‑ray for SQL.

Typical slow query example (historical order query on an e‑commerce platform):

SELECT * FROM orders o LEFT JOIN users u ON o.user_id = u.id LEFT JOIN products p ON o.product_id = p.id WHERE o.create_time > '2023-01-01' AND u.vip_level > 3 AND p.category_id IN (5,8) ORDER BY o.amount DESC LIMIT 1000,20;

Result of EXPLAIN:

+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | rows    | Extra| key_len |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| 1  | SIMPLE      | o     | ALL  | idx_user_time | NULL | 1987400 | Using where; Using filesort |
| 1  | SIMPLE      | u     | ALL  | PRIMARY       | NULL | 100000  | Using where |
| 1  | SIMPLE      | p     | ALL  | PRIMARY       | NULL | 50000   | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+

Diagnosis:

Full table scans (type=ALL)

Filesort (memory warning)

Indexes not used

2 Surgical Plan: Precise Attack

2.1 Single‑Table Index Optimization

If the execution plan shows index problems, optimize the index.

Root cause: JSON field index ineffective.

Wrong usage:

ALTER TABLE users ADD INDEX idx_extend ((extend_info->'$.is_vip'));

Correct for MySQL 8.0+:

ALTER TABLE users ADD INDEX idx_vip_level (vip_level);
ALTER TABLE orders ADD INDEX idx_create_user (create_time, user_id) COMMENT 'Composite index covering query';

This creates a covering composite index.

2.2 Join Reordering (Vascular Clearance)

Bottleneck analysis: Original join order: orders → users → products.

Optimized order: (subquery filter users) → products → orders, using a small table to drive the large table.

Rewritten SQL:

SELECT o.* FROM products p INNER JOIN (
  SELECT o.id, o.amount, o.create_time FROM orders o WHERE o.create_time > '2023-01-01'
) o ON p.id = o.product_id INNER JOIN (
  SELECT id FROM users WHERE vip_level > 3
) u ON o.user_id = u.id WHERE p.category_id IN (5,8) ORDER BY o.amount DESC LIMIT 1000,20;

2.3 Force Index (Open‑brain Surgery)

When the wrong index is chosen, force the correct one:

SELECT /*+ INDEX(o idx_create_user) */ o.id, o.amount FROM orders o FORCE INDEX (idx_create_user) WHERE o.create_time > '2023-01-01';

Use derived tables to accelerate:

SELECT * FROM (
  SELECT id, amount FROM orders WHERE create_time > '2023-01-01' ORDER BY amount DESC LIMIT 1020
) tmp ORDER BY amount DESC LIMIT 1000,20;

2.4 Monitoring Index Usage (Vital‑sign Monitoring)

Check index definitions: SHOW INDEX FROM orders; Monitor index usage rate:

SELECT object_schema, object_name, index_name, count_read, count_fetch FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL;

3 Post‑operation Care: Systematic Governance

3.1 SQL Sanitization Center

Adopt coding standards to avoid full‑table scans; avoid unsafe MyBatis dynamic SQL.

Bad example (MyBatis dangerous write):

@Select("SELECT * FROM orders WHERE #{condition}")
List<Order> findByCondition(@Param("condition") String condition);

Correct parameterized query:

@Select("SELECT * FROM orders WHERE create_time > #{time}")
List<Order> findByTime(@Param("time") Date time);

Sanitization measures:

Integrate an SQL audit platform (e.g., Yearning).

MyBatis interceptor to block full‑table updates.

Automated EXPLAIN analysis pipeline.

3.2 Tackling Large‑Table “Cancer”

For massive tables, use sharding.

Example Sharding‑JDBC configuration:

// Sharding‑JDBC configuration
spring.shardingsphere.rules.sharding.tables.orders.actual-data-nodes=ds$0..1.orders_$->{2020..2023}
spring.shardingsphere.rules.sharding.tables.orders.table-strategy.standard.sharding-column=create_time
spring.shardingsphere.rules.sharding.tables.orders.table-strategy.standard.sharding-algorithm-name=time_range

Solutions:

Time‑based partition (2020‑2023 tables).

User‑ID modulo sharding.

Cold‑hot separation with OSS archiving.

Medical Summary

Optimization three‑step:

Locate: slow‑query log + execution‑plan analysis.

Slice: simplify multi‑step execution.

Rebuild: data structures matching business scenarios.

Avoidance tips:

Indexes are not silver bullets; covering indexes are key.

Break complex joins when possible.

ORDER BY + LIMIT is not pagination optimization.