Differences Between SELECT and UPDATE Execution in MySQL: Process, Optimizer, Redo Log and Binlog

When interviewed for an Internet‑company position, a candidate may be asked to describe how MySQL executes SELECT and UPDATE statements; this article provides a comprehensive answer.

Both statements follow a similar high‑level flow, but their internal implementations differ, especially regarding logging and data modification.

Select Statement Execution

The SELECT process consists of the following steps:

Client sends the query to the server.

Server validates the user name, password and permissions.

Server checks the query cache; if a cached result exists it is returned immediately.

SQL is parsed lexically and syntactically, producing a parse tree.

The optimizer generates an execution plan that aims to minimize cost.

The executor invokes the storage‑engine interface to retrieve rows and returns the result set to the client.

MySQL’s execution is layered: the Service layer (connector, parser, optimizer, executor) and the Engine layer (pluggable storage engines).

Example table creation and data insertion:

DROP TABLE IF EXISTS User;
CREATE TABLE `User` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT 0,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `dept` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;

INSERT INTO User(name,age,address,phone,dept) VALUES('张三',24,'北京','13265543552',2);
-- (additional INSERT statements omitted for brevity)

Sample SELECT query used in the article:

SELECT dept, COUNT(phone) AS num FROM User WHERE age < 25 GROUP BY dept HAVING num >= 3 ORDER BY num DESC, dept ASC LIMIT 0,2;

Connector Phase

The connector first verifies credentials; if they fail, an "Access denied for user" error is returned. Upon successful authentication, the server checks the user’s privileges before proceeding.

Cache Phase

If the query result is cached as a key‑value pair (key = SQL text, value = result set), the cached data is returned; otherwise the query continues through the remaining phases.

Parser Phase

The parser performs lexical analysis (extracting keywords, tables, columns, conditions) and syntax analysis (ensuring the statement conforms to MySQL grammar). Errors produce messages such as "You have an error in your SQL syntax".

Optimizer Phase

The optimizer transforms the parse tree into an execution plan, choosing the best index, join order, etc. Execution plans may be cached for similar future statements, reducing planning overhead.

Executor Phase

The executor follows the plan, repeatedly calling the storage engine’s read interface to collect qualifying rows and finally returns the result set.

Update Statement Execution

UPDATE follows the same pipeline but also interacts with two logs:

redo log (physical, InnoDB‑level Write‑Ahead Log)

binlog (logical, server‑level archive log)

These logs ensure crash‑safety and replication consistency.

Redo Log

Redo log records changes before they are flushed to disk, using a circular buffer. When the buffer fills, a checkpoint writes dirty pages to disk and advances the checkpoint pointer, providing crash‑safe recovery.

Binlog

Binlog stores the original SQL statements (statement format) or row images (row format) for replication and point‑in‑time recovery.

Update Execution Steps

The executor reads the target row (using the primary‑key index) into memory.

The executor modifies the column value (e.g., age = age + 1 ) and writes the updated row back via the engine.

The engine records the change in the redo log in the prepare state.

The executor generates a binlog entry and commits the transaction, causing the redo log to transition to commit state.

This two‑phase commit guarantees that redo log and binlog remain consistent.

Redo vs. Binlog Summary

Redo log is a fixed‑size, circular, physical log providing crash‑safe recovery; binlog is an append‑only logical log used for replication. Setting innodb_flush_log_at_trx_commit=1 and sync_binlog=1 forces both logs to be flushed to disk on each transaction commit.

Understanding these differences helps developers write more efficient SQL and tune MySQL performance.