Unlock MySQL Performance: Deep Dive into Query Optimization & Index Design

Understanding MySQL query optimization starts with its logical architecture, which consists of three layers: client, server (parser, optimizer, cache, built‑in functions) and storage engine.

The query execution process involves client‑server communication, optional query cache lookup, parsing, preprocessing, cost‑based optimization, and finally execution by the storage engine via handler APIs, after which results are streamed back to the client.

Client‑server protocol is half‑duplex; large queries require increasing max_allowed_packet, and responses are sent in complete packets.

Query cache stores results keyed by the exact query text; any difference (including whitespace) prevents a hit, and the cache is invalidated when any referenced table changes.

Parsing creates a syntax tree, which the optimizer transforms into an execution plan using cost estimates (e.g., last_query_cost).

The optimizer chooses the cheapest plan, can reorder joins, use index scans, apply LIMIT early, and prefers covering indexes.

mysql> select * from t_message limit 10;
...省略结果集...
mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+

Index design is crucial. MySQL uses B‑Tree (B+Tree) indexes; leaf pages store indexed columns, internal pages store pointers. Proper index ordering follows the “leftmost prefix” rule, and high‑selectivity columns should appear first.

Common pitfalls include using functions or expressions on indexed columns, over‑using prefix indexes, creating redundant or unused indexes, and combining multiple range conditions that prevent index usage.

Covering indexes avoid table lookups, and index‑ordered scans can satisfy ORDER BY without extra sorting.

For large OFFSET pagination, use “bookmark” queries or delayed joins to scan only needed rows.

UNION should be written as UNION ALL when duplicate removal is unnecessary, and predicates should be pushed down into each subquery.

In summary, grasping the internal stages of MySQL query processing, cost‑based optimization, and thoughtful index strategy enables measurable performance gains.