Understanding MySQL Query Execution Process and Architecture

When you need to retrieve data from a database with a statement like SELECT A, B, C FROM T WHERE ID = XX, you may wonder what the database actually does with that request.

We take MySQL as an example to reveal its query process and give an overview of its internal components.

MySQL Architecture

MySQL can be divided into the Server layer and the Storage Engine layer.

Server layer includes the connection manager, query cache, parser, optimizer, executor, and other cross‑engine features such as stored procedures, triggers, views, functions, and the binlog.

Storage Engine layer is responsible for data storage and retrieval. It follows a plug‑in architecture supporting InnoDB, MyISAM, Memory, etc. InnoDB, which provides transactions, has been the default engine since MySQL 5.5.5.

Connection Manager

The connection manager handles user login, authentication, and permission checks.

If the password is wrong, MySQL returns “Access denied for user” and terminates the client.

When authentication succeeds, the manager reads the user’s privileges from the privilege tables; those privileges remain effective for the duration of the connection even if an administrator changes them later.

Query Cache

After a client connects, MySQL first looks in the query cache to see whether the same SQL statement has been executed before. If a matching key is found, the cached result is returned immediately; otherwise the statement proceeds to the next stage.

Although the cache can be useful, it is frequently invalidated by any UPDATE on the involved tables, so its hit rate is low for write‑heavy workloads. MySQL 8.0 has removed the query cache entirely.

Parser

If the query is not served from the cache, the parser performs lexical analysis and syntax analysis.

Lexical analysis : tokenizes the SQL string, extracting keywords, table names, column names, and conditions.

Syntax analysis : checks the token sequence against MySQL grammar and reports errors such as “You have an error in your SQL syntax”.

The parser produces an abstract syntax tree (AST) that the optimizer will use.

Optimizer

After parsing, the optimizer evaluates the AST, using statistics from the data dictionary to choose the most efficient execution plan.

It contains many complex optimization techniques that surpass what most programmers can devise.

It can access statistics such as row counts and column distributions.

It selects an effective execution plan that the programmer would rarely be able to determine manually.

In short, the optimizer transforms the syntax tree into a query tree that defines the execution strategy.

Executor

The executor carries out the plan produced by the optimizer. It first checks the user’s execution privileges; if the user lacks permission, an error is returned. Otherwise it invokes the storage engine interface to obtain the result.

Statement Walk‑through

Consider the following real SQL query:

select id,name,sex,phoone from user t where t.age='26' and t.account='javadaily'

The client connects to the server; authentication failures abort the process.

In MySQL 8.0 and earlier, the server checks the query cache for a matching key.

The parser extracts the table user , columns id,name,sex,phoone , and conditions age=26 and account=javadaily , then validates the syntax.

The optimizer evaluates possible plans and selects the most efficient one (plan a or plan b).

a. First locate rows where account=javadaily , then filter by age = 26. b. First locate rows where age=26 , then filter by account=javadaily .

Finally, the executor checks permissions and either returns the result or raises an error.