Understanding MySQL’s Full Architecture: Layers, Components, and Storage Engines

This article presents a concise overview of MySQL’s internal architecture, describing the four logical layers and the components they contain.

MySQL Architecture Overview

The architecture is divided into four layers: the client layer, the server (service) layer, the storage‑engine layer, and the system‑file layer.

Client Layer

The topmost layer consists of MySQL connectors. Clients issue SQL statements through this layer.

Connection Handling : The server accepts a client request, establishes a TCP connection, and allocates a dedicated thread for that client.

Authentication : User name and password are verified when the connection is created.

Security : After authentication, the server checks the client’s privileges before executing any statement.

Clients may use the command‑line client, graphical tools, or language‑specific APIs (e.g., Java, C/C++, Python).

Server (Service) Layer

This layer implements the logical functions of MySQL and is often called “the brain” of the database.

Thread Handling : Provides an independent thread for each client connection.

Parser : Performs lexical analysis, tokenization, and syntax/semantic analysis to build a parse tree.

Optimizer : Applies rewrite rules, chooses table‑scan order, selects appropriate indexes, and otherwise optimizes query execution.

Query Cache : Stores complete result sets of previous queries. The cache is deprecated in MySQL 5.7.20 and removed in MySQL 8.0.

Buffer and Cache : Caches recent query results and intermediate data to avoid repeated parsing and execution.

Table Metadata Cache : Keeps metadata about databases, tables, and indexes in memory; the cache grows as more objects are opened.

Key Cache : Holds identifiers for cached objects; primarily used by the MyISAM engine for index blocks.

Storage Engine Layer

The storage‑engine layer is responsible for actual data storage and retrieval. The server communicates with engines via a pluggable API, allowing different engines to be selected per table.

InnoDB Storage Engine

Default transactional engine; supports ACID transactions and automatic crash recovery.

Optimized for many short‑lived transactions.

Data are stored in one or more tablespace files (e.g., .ibd).

Uses clustered indexes: the primary key is the clustering key, and secondary indexes store the primary‑key value.

MyISAM Storage Engine

Default engine for MySQL 5.1 and earlier; provides full‑text and GIS indexes.

Does not support transactions, row‑level locking, or crash recovery.

Suited for read‑only or small tables.

Each table consists of a data file ( .MYD) and an index file ( .MYI).

Other Built‑in Storage Engines

Archive Engine : Allows only INSERT and SELECT; data are compressed with zlib, resulting in low I/O.

Blackhole Engine : Discards all data; queries are logged only, useful for testing.

CSV Engine : Stores tables as plain CSV files for easy exchange.

Federated Engine : Acts as a proxy to remote tables on other MySQL servers.

Memory Engine : Keeps all data in RAM; data are lost on server restart.

Merge Engine : Provides a virtual table that merges several MyISAM tables.

NDB Cluster Engine : Offers distributed, high‑availability, fault‑tolerant storage.

Third‑Party Storage Engines

Developers can implement custom engines via MySQL’s pluggable storage‑engine API, creating engines tailored for OLTP, columnar storage, or other specialized workloads.

System File Layer

This layer persists MySQL data on the underlying file system and mediates interaction between storage engines and disk.

Stores data files, log files, configuration files, PID files, and socket files.

Key file types include .frm (table definition), .ibd (InnoDB tablespace), binary logs ( binlog), redo logs ( redo log), and error logs ( error log).

Understanding these layers helps locate specific MySQL features and clarifies how requests flow from the client down to the physical storage.