Understanding MySQL Slow Query Analysis with EXPLAIN

When an interface response is slow, the root cause is often a sluggish SQL query; developers need to diagnose the issue by examining MySQL’s internal processing flow.

The article first introduces MySQL’s architecture, covering the client connection, business logic modules, connection management, query cache, parser, optimizer, executor, storage engine, and physical files.

It then explains how the EXPLAIN keyword simulates the optimizer to reveal the execution plan, listing important columns such as id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, and Extra.

The "type" column indicates join types from best to worst (const → eq_ref → ref → range → index → all), with visual examples for each case.

The "key" column shows the actual index used; a NULL value means no index was applied.

The "Extra" column provides additional execution details, highlighting common messages like "Using index" (covering index, good performance), "Using filesort" (external sorting, to avoid), and "Using temporary" (temporary table creation, often caused by ORDER BY or GROUP BY without proper indexes).

Finally, the article advises enabling MySQL’s slow query log to pinpoint problematic statements and summarizes the key points for effective SQL optimization.