Architectural Overview

MySQL's internal architecture is split into two distinct layers: the Server layer and the Storage Engine layer. The Server layer handles connection establishment, query parsing, and execution logic. The Storage Engine layer manages physical data storage and retrieval.

When a SELECT statement is executed, it passes through several core modules: the Connector, Query Cache (deprecated), Parser, Optimizer, and Executor, before finally interacting with the Storage Engine.

Connection Management

The Connector is responsible for establishing the client connection, authenticating the user, and managing session privileges. A typical connection is initiated via the command line:

# Connecting to a remote instance
mysql -h 192.168.1.50 -u admin_user -p
# Connecting locally
mysql -u root -p

After the TCP handshake, the Connector validates the provided credentials. If authentication fails, an "Access denied" error terminates the process. If successful, the system loads the user's privilege matrix. Any subsequent privilege modifications applied to this user account will not affect the current active session; only new connections will reflect the updated permissions.

Idle connections are automatically terminated by the server based on the wait_timeout parameter, which defaults to 28800 seconds (8 hours). Clients must reconnect if a "Lost connection" error occurs.

Persistent connections (long connections) are generally preferred over short connections to avoid the overhead of repeated TCP handshakes. However, long connections can lead to excessive memory consumption because temporary resources allocated during query execution are bound to the session object and only released upon disconnection. To mitigate Out-Of-Memory (OOM) risks, applications can either periodically close long connections or, in MySQL 5.7+, invoke mysql_reset_connection to reinitialize the session context without requiring a full re-authentication.

Query Cache

Once connected, the client submits a SQL statement. For SELECT queries, MySQL historically checked the Query Cache, a key-value memory store mapping the exact query string to its result set.

If a match was found, the result was returned directly, bypassing subsequent execution stages. However, this feature proved inefficient. Any write operation on a table invalidated all cached entries associated with that table, leading to abysmal hit rates in write-heavy environments. Consequently, the Query Cache was completely removed in MySQL 8.0. In older versions, it could be disabled by setting query_cache_type to DEMAND.

Query Parsing

If the cache is bypassed or unavailable, the Parser performs lexical and syntactic analysis.

Lexical analysis breaks the query string into discrete tokens, identifying keywords like SELECT and FROM, as well as identifiers such as table and column names. For instance, parsing SELECT item_name FROM inventory yields four distinct tokens.

Syntax analysis evaluates these tokens against MySQL's grammar rules. If the syntax is invalid, an error is thrown. Valid queries are transformed into an Abstract Syntax Tree (AST), providing a structured representation for downstream modules.

Execution Stages

The execution pipeline consists of three distinct phases: Preprocessing, Optimization, and Execution.

Preprocessing

The Preprocessor validates the semantic integrity of the AST. It verifies that referenced tables and columns exist in the database schema and expands wildcard selectors (*) into the explicit list of columns belonging to the queried table. Queries referencing non-existent objects fail at this stage.

Optimization

The Optimizer evaluates potential execution paths to determine the most cost-effective plan. When multiple indexes are available, it calculates the I/O and CPU costs associated with each option, selecting the path with the lowest estimated overhead.

Consider an inventory table with both a primary key index on item_id and a secondary index on item_name. Executing the following query:

EXPLAIN SELECT * FROM inventory WHERE item_id > 5 AND item_name LIKE 'A%';

Both indexes could theoretically be utilized. However, the Optimizer recognizes that the secondary index suffices for this covering index query, avoiding the heavier cost of traversing the primary key B+ tree. The resulting execution plan will indicate the secondary index was chosen.

Execution

The Executor uses the Optimizer's plan to interact with the Storage Engine, fetching rows iteratively. This interaction varies based on the chosen access strategy.

Primary Key Index Lookup (const)

For an exact match on a primary key:

SELECT * FROM inventory WHERE item_id = 1;

The Optimizer selects a const access type. The Executor calls the Storage Engine's index lookup interface. InnoDB traverses the primary key B+ tree to locate the record. If found, the row is returned to the Executor, which verifies any remaining conditions (none in this case) and sends it to the client. Subsequent loop iterations immediately terminate, as a const scan guarantees a single match.

Full Table Scan (ALL)

When no applicable index exists:

SELECT * FROM inventory WHERE item_category = 'Peripheral';

The Optimizer defaults to an ALL access type. The Executor instructs the Storage Engine to fetch the first row. The Server layer evaluates the WHERE clause against the retrieved row. If it matches, the row is dispatched to the client; otherwise, it is skipped. The Executor loops, continuously requesting the next row from the Storage Engine until the entire table is scanned.

Index Condition Pushdown (ICP)

Introduced in MySQL 5.6, Index Condition Pushdown optimizes secondary index traversals by shifting part of the WHERE clause evaluation from the Server layer down to the Storage Engine layer.

Assume an employees table contains a composite secondary index on (experience, salary). Given the following query:

SELECT * FROM employees WHERE experience > 3 AND salary = 50000;

Range conditions stop composite index matching, meaning only the experience column is fully utilized for index navigation.

Without ICP, InnoDB locates secondary index entries matching experience > 3, performs a primary key lookup for each entry, and returns the complete rows to the Server. The Server then filters these rows by checking salary = 50000, discarding mismatches.

With ICP enabled, InnoDB evaluates the salary = 50000 condition directly against the secondary index record before executing the primary key lookup. Rows failing the salary check are skipped immediately, drastically reducing unnecessary primary key lookups. The execution plan will display "Using index condition" in the Extra field when ICP is active.