Understanding MySQL Architecture and SQL Execution Flow

Overview

This article explains how MySQL processes SQL statements, covering the internal architecture, the roles of each component, and the step‑by‑step execution of SELECT and UPDATE queries.

MySQL Architecture

MySQL consists of a Server layer and a storage‑engine layer.

Server layer: connector, query cache (removed after MySQL 8.0), parser, optimizer, executor, and the binary log (binlog) used by all engines.

Storage‑engine layer: pluggable engines such as InnoDB, MyISAM, Memory. InnoDB provides its own redo log.

Server‑layer components

Connector: handles authentication and permission checks.

Query cache: caches SELECT results (removed in MySQL 8.0).

Parser: performs lexical and syntactic analysis of the SQL.

Optimizer: chooses the best execution plan (indexes, join order, etc.).

Executor: invokes the storage engine to retrieve or modify data.

SQL Statement Analysis

1. SELECT statement

Example:

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

Execution steps:

Permission check; if MySQL <8.0, check query cache.

Parser performs lexical analysis (identifies keywords, tables, columns) and syntax validation.

Optimizer generates possible execution plans and selects the most efficient one.

Executor validates permissions again, calls the engine, and returns the result set.

2. UPDATE statement

Example:

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

Execution steps (InnoDB):

Locate the target row (query cache is bypassed).

Modify the row in memory, write a redo log entry (prepare state).

Executor records a binlog entry, then commits the redo log.

Transaction completes.

The article also explains why MySQL uses both binlog and redo log to guarantee crash‑safety and data consistency.

Conclusion

MySQL’s Server layer handles authentication, parsing, optimization, execution, and logging.

Storage engines are plug‑in modules; InnoDB is the default with its own redo log.

SELECT execution flow: permission → (cache) → parser → optimizer → executor → engine.

UPDATE execution flow adds redo log and binlog handling to ensure durability.