Understanding MySQL SQL Execution Process with a Single Diagram

Execution Overview

When a client connects to MySQL Server and sends an SQL statement, the server builds a parse tree and passes the statement to the optimizer. The optimizer evaluates index costs and selects the best execution plan, then the InnoDB engine is invoked to carry out the statement.

Execution Process

Connection Manager

The connection manager authenticates the client, allocates a thread, and creates a session for the client to issue SQL statements.

Parser

The parser checks the SQL syntax, reports errors to the client, and converts the statement into an internal data structure for later stages.

Optimizer

After parsing, the optimizer generates alternative execution plans, evaluates available indexes, join methods, and estimated costs, then chooses the plan with the lowest cost.

Executor

The executor follows the chosen plan, calls InnoDB engine functions, fetches rows, and performs sorting, aggregation, and filtering before returning the result to the client.

Undo Log

When the executor modifies data, InnoDB starts a transaction and writes an undo log that records the before‑image of each change, enabling rollback if the transaction fails.

Record Cache and Index Lookup

InnoDB caches rows in the record cache to accelerate repeated reads. Updates may be applied directly to the memory page if the page is cached; otherwise a disk read is required.

Unique index: Guarantees uniqueness; updates are applied directly to the memory page when consistency is maintained.

Non‑unique index: Updates are applied directly to the memory page; if the page is not in memory, the change is recorded in the change buffer for asynchronous disk write.

Change Buffer

The change buffer (formerly insert buffer) batches secondary‑index insert, delete, and update operations to reduce random I/O. Unique indexes bypass this mechanism because they require immediate disk writes to preserve uniqueness.

Redo Log

All data modifications are also written to the redo log, a circular file that records each step of a transaction to guarantee durability. The redo log has two states: prepare while changes are being written, and commit after the transaction is finalized.

Binlog and Transaction Commit

MySQL writes the executed SQL statements to the binary log (binlog). The binlog is essential for replication and point‑in‑time recovery because it captures the logical changes. After InnoDB signals that the redo log is ready, MySQL writes the statement to the binlog, then marks the redo log as committed. A transaction is considered successfully committed only after its entry appears in the binlog.

Redo Log vs. Binlog

Two‑phase commit:

Data pages are updated in memory and the changes are recorded in the redo log (prepare state).

MySQL Server writes either the SQL statement or row changes to the binlog, then sets the redo log to commit state.

Redo log alone cannot replace the binlog because the binlog is needed for replication and for reconstructing the exact SQL operations on a replica.

Redo Log

Records InnoDB engine changes and enables crash‑safe recovery.

Binlog

Records all statements executed by the MySQL server, supporting data archiving, backup, and master‑slave replication.

If only the redo log were written, a master could recover its data after a crash, but a replica would miss the changes because it relies on the binlog.

Log Dump Thread

The master creates a log dump thread for each replica to read and send binlog contents, locking the binlog during transmission.

I/O Thread

The replica’s I/O thread connects to the master, requests the binlog, and writes the received data to the relay‑log.

SQL Thread

The replica’s SQL thread reads the relay‑log, parses the events, and replays them to keep the replica synchronized with the master.