Understanding MySQL Architecture: A Comprehensive Overview of Its Logical Layers

Welcome to the "Internet Full‑Stack Architecture" public account. After the high readership of the "Deep Dive into Redis" series, I am committed to creating systematic technical articles, and this piece begins a series on MySQL.

Based on a recent poll, many readers are eager for MySQL content, as MySQL is a critical database for companies of all sizes, and understanding it also helps with other relational databases due to their shared concepts.

Before diving into a technology, it is useful to grasp its overall architecture. MySQL, the world’s most popular open‑source database, has a logical architecture divided into four layers, as shown in the diagram below.

The four logical layers are:

Connection Layer

Core Service Layer

Storage Engine Layer

Data Storage Layer

1. Connection Layer

This topmost layer handles generic client‑server services such as connection handling, authentication, and security.

Connection handling: When a client connects, MySQL allocates a dedicated thread for that connection, caching it so that new connections do not require thread creation and destruction each time.

Authentication: Authentication is performed based on host, user, and password, e.g., root@localhost (username) and localhost (host).

Security: After a client connects, MySQL checks whether the client has permission to execute specific requests.

Example of showing privileges:

mysql> show privileges \G
*************************** 1. row ***************************
  Privilege: Alter
  Context: Tables
  Comment: To alter the table
*************************** 2. row ***************************
  Privilege: Alter routine
  Context: Functions,Procedures
  Comment: To alter or drop stored functions/procedures

2. Core Service Layer

This layer implements all logical functions of the MySQL DBMS; it is essentially the brain of the server. It includes MySQL services and tools, the SQL interface, parser, optimizer, and cache.

MySQL services and tools: Provide management capabilities such as backup & recovery, security, replication, clustering, sharding, and workbench utilities.

SQL interface: The Structured Query Language (SQL) is the primary language for interacting with MySQL, covering DML, DDL, stored procedures, views, and triggers.

Parser: The parser converts a query into an internal parse tree. It performs lexical analysis, syntax analysis, semantic analysis, and code generation.

Optimizer: After building the parse tree, MySQL applies techniques such as query rewrite, table scan ordering, and index selection. Example:

EXPLAIN SELECT * FROM world.city;

Cache: The query cache stores complete result sets of SELECT statements. If a new query matches a cached result, MySQL can skip parsing, optimization, and execution, returning the cached output directly.

3. Storage Engine Layer

MySQL’s pluggable storage engine architecture makes it unique. The storage engine handles data extraction and storage, communicating with the server via a defined interface. Different engines offer various locking, transaction, and indexing features; InnoDB is the default and most widely used.

Supported storage engines include MyISAM, InnoDB, Federated, CSV, Memory, etc.

4. Data Storage Layer

This bottom layer consists of the file system that stores data files, log files, and other persistent objects.

Conclusion

The article uses a diagram to illustrate MySQL’s overall architecture, providing a high‑level view that helps readers build a comprehensive understanding of the database’s key components.

Such knowledge is frequently tested in technical interviews, especially for senior development positions, because a solid grasp of MySQL’s architecture is essential for working with the most popular relational database system.

Despite claims that relational databases are outdated compared to NoSQL or distributed systems, MySQL remains highly relevant and indispensable for many applications.

Please support the author by liking, sharing, or commenting.