How Does MySQL Execute a Query? Inside the Server Architecture

Preface

In daily operations and development we often write SQL queries, but few know how MySQL actually executes them internally. This article explores the execution flow of a simple query such as

select * from test_db.user_info_tab where user_id =123;

.

1. MySQL Basic Architecture

MySQL is divided into two main parts: the Server layer and the Storage Engine layer.

Server Layer

Connector (Connection Manager) : Handles client‑server connections, authentication, and permission checks.

Query Cache : Caches query results in older MySQL versions; deprecated in recent releases because it can become a performance bottleneck.

Parser : Analyzes SQL syntax and semantics, converting statements into internal structures.

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

Executor : Executes the plan generated by the optimizer, interacts with the storage engine, and returns results to the client.

Storage Engine Layer

Responsible for data storage and retrieval. MySQL supports multiple engines such as InnoDB, MyISAM, and Memory; InnoDB is the default since MySQL 5.5.

2. Connector

To run a query, you first connect to the MySQL server:

<code>mysql -h 127.0.0.1 -P 3306 -u root -p</code>

After entering the password and completing the TCP handshake, the server confirms the connection. If the password is wrong, MySQL returns an

ERROR 1045 (28000): Access denied

message.

3. Query Cache

In older versions, MySQL checks the query cache before executing a statement. If a cached result exists, it is returned directly; otherwise, the query proceeds and the result may be stored in the cache. The cache is a simple key‑value map where the key is the SQL text and the value is the result set. Frequent updates to a table invalidate its cache, making the cache ineffective for highly‑changing data. The

query_cache_type

variable controls cache usage, but it has been removed in MySQL 8.0.

4. Parser

If the query is not served from the cache, the parser takes over. It performs lexical analysis to identify tokens such as

SELECT

, table names, and column names, then conducts syntactic analysis to ensure the statement follows MySQL grammar. Syntax errors produce messages like

ERROR 1064 (42000): You have an error in your SQL syntax

.

<code>mysql&gt; select * rom test_db.user_info_tab where user_id =123;</code><code>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rom test_db.user_info_tab where user_id =123' at line 1</code>

5. Optimizer

After parsing, the optimizer decides the best execution strategy. For a query like:

<code>select * from test_db.user_info_tab where user_id =123 and user_name='XX';</code>

it evaluates which indexes (e.g.,

user_id

or

user_name

) to use to minimize I/O. The

EXPLAIN

statement can reveal the chosen plan.

6. Executor

The executor finally runs the plan. It checks the user's permissions, then accesses the storage engine. Assuming

user_id

is not indexed and the InnoDB engine is used, the executor scans rows sequentially:

Call InnoDB to fetch the first row of

user_info_tab

.

Check whether

user_id

equals 123; if not, skip the row.

Repeat the fetch‑and‑check steps for each subsequent row until the table is exhausted.

The matching rows are collected into a result set and sent back to the client.

In summary, a MySQL SELECT query passes through the connector, optional query cache, parser, optimizer, and executor before the result is returned.