How MySQL Executes a SELECT Query: From Connection to Result

MySQL Architecture Overview

MySQL consists of a Server layer and a Storage Engine layer. The Server layer includes the connector, query cache (deprecated), parser, optimizer, and executor. The Storage Engine layer handles actual data storage; InnoDB is the default engine since MySQL 5.5.

Connector (Connection Manager)

Clients connect via TCP using the mysql client:

mysql -h <em>host</em> -P <em>port</em> -u <em>user</em> -p

After password authentication the server returns a welcome banner and a connection ID. An authentication failure produces:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Connected sessions can be inspected with SHOW PROCESSLIST.

Query Cache (removed in MySQL 8.0)

Older MySQL versions cached result sets keyed by the exact SQL text. Any DML on a table invalidated its cache entries, making the feature unsuitable for frequently updated data. The cache was controlled by query_cache_type and was removed in MySQL 8.0 because it often became a performance bottleneck.

Parser

The parser performs lexical analysis (tokenizing keywords, identifiers, literals) and syntactic analysis (validating the statement against MySQL grammar). Syntax errors generate messages such as:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rom test_db.user_info_tab where user_id =123' at line 1

When debugging, locate the phrase “syntax to use near” to identify the offending token.

Optimizer

After parsing, the optimizer evaluates possible execution plans and selects the one with the lowest estimated cost. It chooses indexes, join order, and access methods. The chosen plan can be displayed with EXPLAIN:

EXPLAIN SELECT * FROM test_db.user_info_tab WHERE user_id = 123 AND user_name = '田螺';

If both user_id and user_name are indexed, the optimizer decides which index yields the cheapest plan.

Executor

The executor carries out the plan by invoking the storage‑engine API. For a simple SELECT on an InnoDB table without a suitable index, the executor performs a full table scan:

Fetch the first row from user_info_tab via InnoDB.

Evaluate the WHERE condition ( user_id = 123); discard non‑matching rows.

Repeat fetching subsequent rows until the table is exhausted.

Collect matching rows into a result set and send it to the client.

Typical Workflow Example

Example query and its lifecycle:

SELECT * FROM test_db.user_info_tab WHERE user_id = 123;

The statement passes through connector → (optional) query cache → parser → optimizer → executor → storage engine → client.