Understanding MySQL Execution Process: Architecture, Query and Update Flow

Overview

This article analyzes the execution process of a SQL statement in MySQL, describing how queries and updates flow through the MySQL internals.

1. MySQL Architecture Analysis

Below is a brief architecture diagram of MySQL (image omitted).

MySQL is mainly divided into the Server layer and the Storage Engine layer.

Server layer : includes the connection manager, query cache, parser, optimizer, executor, and a common logging module (binlog). Functions that span storage engines, such as stored procedures, triggers, views, and functions, reside here.

Storage Engine : responsible for data storage and retrieval, using a pluggable architecture that supports InnoDB, MyISAM, Memory, etc. InnoDB has its own redo‑log module.

InnoDB 5.5.5 is the default engine.

Connection Manager

Handles user login, authentication, and permission checks. Once authenticated, the connection caches the user's permissions for the session, so later permission changes do not affect the active connection.

Query Cache

When a query is executed, MySQL first checks the cache for a matching key‑value entry. If found, the cached result is returned; otherwise, the query proceeds and the result is cached for future use. MySQL 8.0 removed this feature because it offered limited benefit for frequently updated data.

Parser

If the cache is missed, the parser analyzes the SQL statement, performing lexical analysis to extract keywords, tables, columns, and conditions, followed by syntax analysis to ensure the statement conforms to MySQL grammar.

Optimizer

The optimizer selects the most efficient execution plan, such as choosing the best index or join order.

Executor

After the plan is chosen, the executor checks user permissions again, then calls the storage engine interface to obtain the final result.

2. Statement Analysis

2.1 Query Statement

Example query:

select * from tb_student A where A.age='18' and A.name='张三';

Execution flow:

Permission check; if passed, MySQL 8.0 (or earlier) checks the query cache.

Parser performs lexical analysis (identifies SELECT, table name, columns, conditions) and syntax validation.

Optimizer determines possible execution plans, e.g., a. Find rows with name "张三" then filter by age 18. b. Find rows with age 18 then filter by name "张三". The optimizer picks the most efficient plan.

Executor re‑checks permissions, invokes the engine, and returns the result.

2.2 Update Statement

Example update:

update tb_student A set A.age='19' where A.name='张三';

The flow is similar to the query path but includes logging:

Locate the target row (cache may be used).

Modify the row in memory, write a redo‑log entry in the PREPARE state, and notify the executor.

Executor records a binlog entry, then the engine commits the redo‑log (COMMIT state).

Update completes.

MySQL uses both redo‑log (for crash‑recovery and transaction support) and binlog (for replication and point‑in‑time recovery). Using a two‑phase commit ensures consistency: if a crash occurs after redo‑log but before binlog, or vice‑versa, MySQL can recover correctly by checking the completeness of both logs.

3. Summary

MySQL consists of a Server layer (connection, cache, parser, optimizer, executor, binlog) and a pluggable Storage Engine layer.

Query execution flow: permission check → query cache → parser → optimizer → permission check → executor → engine.

Update execution flow: parser → permission check → executor → engine → redo‑log PREPARE → binlog → redo‑log COMMIT.

4. References

《一起构建Mysql知识网络》