Understanding MySQL Architecture: Layers, Components, and Storage Engines

During a recent interview a candidate was asked "What is the architecture of MySQL?" Although the candidate had years of Java development experience, he was surprised by the question, which prompted this detailed overview of MySQL’s internal structure.

MySQL Architecture Diagram

The overall MySQL architecture can be visualized as a layered system similar to a typical software architecture, with distinct responsibilities at each level.

Layered Structure

Connection Layer

This layer handles communication with clients and supports many protocols, such as JDBC for Java.

Service Layer

Connection Pool

Manages and reuses client connections; using a pool can dramatically improve performance when the number of connections grows.

Query Cache

When a query is received, MySQL first checks the query cache for a matching key‑value pair. If found, the cached result is returned immediately, otherwise execution proceeds. In MySQL 8.0 the query cache was removed, and in earlier versions it is often disabled because cache invalidation occurs on any table update.

Parser

The parser performs lexical analysis to break the SQL string into tokens and then syntactic analysis to validate the statement against MySQL grammar. Syntax errors produce messages such as "You have an error in your SQL syntax".

Example of a syntax error (missing "E" in WHERE ):

Optimizer

The optimizer chooses the best execution plan, selecting appropriate indexes and join orders when multiple tables are involved.

SELECT a.id, b.id FROM t_user a join t_user_detail b WHERE a.id=b.user_id and a.user_name='田维常' and b.id=10001

Executor

After the plan is determined, the executor checks permissions, opens the required tables, and invokes the storage engine to retrieve or modify data.

Storage Engine Layer

MySQL uniquely supports multiple storage engines. Prior to MySQL 5.5 the default was MyISAM; from 5.5 onward the default is InnoDB.

MyISAM vs. InnoDB

MyISAM uses table‑level locking and does not support transactions, while InnoDB provides row‑level locking and full ACID transaction support, making InnoDB the preferred choice for most applications.

Guidelines for choosing an engine:

Need transactions? → InnoDB

High concurrent writes? → InnoDB

Read‑heavy with static data? → MyISAM

Full‑text search without third‑party tools? → MyISAM (otherwise InnoDB with plugins)

System File Storage Layer

This layer stores data files, log files, PID files, and configuration files on the operating system.

Data Files

Examples include db.opt (default charset and collation), *.frm (table metadata), *.MYD and *.MYI for MyISAM, and *.ibd , ibdata* for InnoDB.

Log Files

Common logs can be inspected with the following commands:

show variables like '%log_error%';   -- error log

show variables like '%log_bin%';      -- binary log status

show variables like '%slow_query%';  -- slow query log

show variables like '%general%';     -- general query log

Configuration Files

MySQL configuration is stored in my.cnf or my.ini . The PID file records the server process ID, and the socket file enables local Unix‑socket connections.

Summary

MySQL can be viewed as a multi‑layered software system: a connection layer similar to an API gateway, a cache layer akin to application‑level caching, a parser and optimizer comparable to request validation and code optimization, a storage‑engine layer representing the persistence layer, and a file system layer that mirrors the underlying OS storage. Understanding these components helps developers troubleshoot, optimize performance, and answer interview questions confidently.