Inside MySQL: How SQL Queries Are Processed from Connection to Execution

MySQL is divided into two main layers: the service layer and the storage engine layer .

Service layer : Includes the connector, query cache, parser, optimizer, executor, and implements cross‑engine features such as stored procedures, triggers, and views.

Storage engine layer : Contains storage engines like MyISAM, InnoDB, and Memory, with InnoDB being the default.

SQL Statement Execution Process

1. Connector : The client logs in (e.g., mysql -u user -p), performs a TCP handshake, and authenticates the user.

2. Query Cache : MySQL first checks the query cache for a matching statement and result. If found, it returns the cached result; otherwise, execution proceeds. Note: the query cache was removed in MySQL 8.0.

3. Parser : Performs lexical analysis to tokenize the SQL and syntax analysis to validate the statement. Errors produce messages such as You have an error in your SQL syntax.

4. Optimizer : Determines the most efficient execution plan, choosing indexes and join methods.

5. Executor : Checks permissions, opens the target table, and invokes the storage engine to read or modify data.

The storage engine provides the actual data‑access interfaces.

Update SQL Log Recording

MySQL uses Write‑Ahead Logging (WAL): changes are first written to a log before being persisted to disk.

Redo Log

Redo log is an InnoDB‑specific physical log that records page‑level modifications. It is a fixed‑size circular buffer (e.g., four 1 GB files) with a write position and checkpoint. It guarantees crash‑safe recovery by persisting committed changes.

Binlog

Binlog is a server‑level logical log that records the original SQL statements (statement format) or row changes (row format). It is append‑only and can be used by all storage engines.

Redo Log and Binlog Execution Order

Internal Process of an UPDATE Statement

1. The executor retrieves the row with ID=2 using the storage engine's index lookup. If the page is not in memory, it is read from disk.

2. The executor modifies the row (e.g., increments a column) and passes the new row to the engine.

3. The engine updates the row in memory and writes the change to the redo log in a prepare state.

4. The executor generates a binlog entry and writes it to disk.

5. The executor commits the transaction; the engine changes the redo log entry to commit , finalizing the update.

The two‑phase commit (prepare and commit) ensures that if a failure occurs after the redo log is prepared but before the binlog is written, the transaction can be rolled back, preserving data consistency.

Source: https://blog.51cto.com/14612701/2505993 (Author: wx5dcb7577ac572)