Inside MySQL: How Its Architecture Handles Connections, Queries, and Storage

Client Connector

MySQL provides client connectors for external programs such as PHP, Java, .NET, and Ruby.

Connection Management

This layer handles operations related to client connections, including connection pooling, permission verification, thread reuse, and connection limits.

SQL Layer

SQL Interface

Receives SQL commands from clients and returns the results.

SQL Parser

The parser validates and parses incoming SQL commands, converting them into an abstract syntax tree (analysis tree) that is passed to subsequent stages. If parsing errors occur, an error message is returned.

SQL Optimizer

The optimizer refines the analysis tree by rewriting queries, choosing table read order, and selecting appropriate indexes. It obtains parameters, operation costs, and statistics from the storage engine to guide optimization.

SQL Cache

Before parsing, the server checks the query cache, which stores SELECT statements and their result sets. If a result is cached, the server skips parsing, optimization, and execution, returning the cached result directly.

Storage Engine Layer

The storage engine is the MySQL subsystem that interacts with files. MySQL’s storage engines are plug‑in based, allowing the storage engine layer and SQL layer to remain loosely coupled and even enabling online loading of new storage engines without restarting MySQL.

System Management

Provides overall system management services and tools, such as backup, recovery, security, master‑slave replication, and configuration.