Unveiling MySQL’s Logical Architecture: Layers, Modules, and Optimization

MySQL’s logical architecture consists of three hierarchical layers.

First (top) layer: Provides generic services such as connection handling, authentication, and security, which are not unique to MySQL but are required by any client‑server application.

Second layer (SQL Layer): The core of MySQL. It performs permission checks, SQL parsing, query‑plan optimization, query‑cache handling, and implements built‑in functions (date, time, math, encryption). Features like stored procedures, triggers, and views are also part of this layer.

Third layer (Storage Engine Layer): Implements the actual data‑access operations. Multiple storage engines cooperate here, each exposing its capabilities through a unified Storage Engine API, which hides engine‑specific details from the SQL layer. The API supports operations such as transaction start and row retrieval.

Connection Management and Security – Each client connection runs in its own thread, with thread caching to avoid creating/destroying threads per request. Authentication is based on username, host, and password; SSL connections use X.509 certificates, and the server validates client privileges for each requested operation.

Optimization and Execution – MySQL parses a query into an internal parse tree, then applies various optimizations (query rewrite, table‑scan order, index selection). Users can give optimizer hints or request an EXPLAIN to see the chosen plan. The optimizer relies on storage‑engine statistics (e.g., supported index types) to choose the best execution strategy. Query cache is consulted before parsing; a cache hit returns the result directly.

MySQL Logical Modules

1. Connectors

Interfaces for different programming languages to communicate with SQL.

2. Management Services & Utilities

System administration and control tools.

3. Connection Pool

Manages buffered client connections and thread handling.

4. SQL Interface

Receives SQL commands from clients and returns query results.

5. Parser

Implemented with Lex and YACC; validates and parses incoming SQL statements into a structured representation.

6. Optimizer

Analyzes queries using statistics and a “select‑project‑join” strategy to produce an optimal execution plan.

7. Cache and Buffer (Query Cache)

Caches result sets of SELECT queries keyed by a hash of the query; invalidated automatically when underlying tables change.

8. Storage Engine Interface

Provides a plug‑in architecture allowing different storage engines to be used interchangeably; MySQL is one of the few DBMSs with this capability.