How MySQL Executes a Query: From Connection to Execution Engine

Server Layer

The Server layer consists of five core components that work across all storage engines: Connector, Query Cache, Parser, Optimizer, and Executor. Functions such as stored procedures, triggers, and views are implemented here.

Storage Engine Layer

The Storage Engine layer handles actual data storage and retrieval. It is pluggable and supports engines like InnoDB, MyISAM, and Memory. Since MySQL 5.5 the default engine is InnoDB, but you can specify another engine in a CREATE TABLE statement, e.g., ENGINE=MEMORY.

Connection Process

The Connector establishes a TCP connection, authenticates the user, and loads privileges. Example command to connect: mysql -u root -p If authentication succeeds, the session’s permission set is fixed for the lifetime of that connection. You can view idle connections with: show processlist When the Command column shows Sleep, the connection is idle; the wait_timeout variable (default 8 hours) controls how long MySQL will keep such a connection alive.

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

Long vs. Short Connections

Long connections keep the same TCP session for multiple queries, reducing connection overhead. However, each connection holds memory resources; many long connections can cause high memory usage and even OOM crashes. Mitigation strategies include periodically closing idle connections, resetting a connection after a large query with mysql_reset_connection (available from MySQL 5.7), or manually managing connection lifetimes.

Query Cache

Before executing a query, MySQL checks the query cache. If the exact statement is cached, the stored result is returned instantly. The cache stores SQL → Result pairs in memory. Because any table update invalidates all cached queries on that table, the cache is often more harmful than helpful. It can be disabled globally (default in MySQL 8.0) or enabled selectively with query_cache_type=DEMAND and the SQL_CACHE hint.

Parser

The Parser first performs lexical analysis, breaking the SQL string into tokens (keywords, identifiers, literals). It then conducts syntax analysis, verifying that the token sequence conforms to MySQL’s grammar. Errors produce messages such as “You have an error in your SQL syntax” and point to the offending token.

Optimizer

The Optimizer decides which indexes to use and determines the join order when multiple tables are involved. For example, for the query

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

the optimizer may choose to scan t1 first or t2 first, depending on index availability and statistics, to minimize I/O.

Executor

The Executor carries out the plan produced by the Optimizer. It checks table‑level permissions, opens the appropriate storage engine, and reads rows. If a table lacks an index, the executor performs a full table scan, repeatedly calling the engine’s “fetch next row” interface until all matching rows are collected. With an indexed table, the executor uses the engine’s “fetch by index” interface to retrieve only qualifying rows.

Example of a full‑scan flow on a table without an index:

Call the InnoDB engine to fetch the first row and test userid=10.

Repeatedly call the engine’s “fetch next row” method, applying the same test, until the last row is reached.

Return the set of rows that satisfied the condition to the client.

When an index exists, the executor uses the engine’s indexed‑lookup interface, dramatically reducing the number of rows examined.

Permission Checks

Before any data access, the Executor verifies that the session’s user has SELECT privileges on the target table. If not, an error such as

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'user'

is returned.

Reference: 极客时间 – MySQL实战45讲