Inside MySQL: How the Server Handles Queries from Connection to Storage Engine

1. Connection Handling

MySQL uses a client/server model. The server processes connections in three steps: handling the connection, parsing & optimization, and invoking the storage engine.

1.1 Communication Methods

TCP/IP : default protocol, listening on port 3306. Connect with -h and an IP address.

UNIX Domain Socket : on Unix-like systems, if no host is specified or --protocol=socket is used, the client connects to the socket file (default /tmp/mysql.sock).

Named Pipe & Shared Memory (Windows): enable with --enable-named-pipe and --pipe (or --protocol=pipe) for named pipes, or --shared-memory (or --protocol=memory) for shared memory.

1.2 Authentication

After the connection is established, the server validates the username and password. On success it loads the user’s privileges from the privilege tables; on failure the connection is closed.

1.3 Viewing Active Connections

Show thread statistics:

mysql> SHOW GLOBAL STATUS LIKE 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+

Two timeout variables control idle connections, both default to 28800 seconds (8 h): wait_timeout (non‑interactive) and interactive_timeout. The maximum number of simultaneous connections defaults to 151.

System variables have GLOBAL or SESSION scope. For example, max_connections is global‑only, while most others default to SESSION when no scope is specified.

2. Parsing and Optimization

After receiving a query, the server runs it through the query cache (if enabled), lexical parsing, syntax analysis, and the optimizer.

2.1 Query Cache

The built‑in query cache is disabled by default ( query_cache_type = OFF) because it requires identical SQL strings and is invalidated on any table change. MySQL 8.0+ has removed this feature.

mysql> SHOW VARIABLES LIKE 'query_cache_type';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| query_cache_type  | OFF   |
+-------------------+-------+

2.2 Parser & Preprocessor

The parser first tokenizes the SQL statement (lexical analysis) and then builds a parse tree (syntax analysis). Errors such as misspelled keywords or unmatched quotes are caught here.

The preprocessor validates semantic correctness: it checks that referenced tables and columns exist, resolves aliases, and eliminates ambiguities.

mysql> SELECT * FROM t_user WHERE user_name = '蝉沐风' AND age > 3;

2.3 Optimizer and Execution Plan

The optimizer generates one or more execution plans from the parse tree and selects the lowest‑cost plan, deciding table order, index usage, and join strategy.

Use EXPLAIN to view the chosen plan, or EXPLAIN FORMAT=JSON for detailed JSON output.

mysql> EXPLAIN SELECT * FROM t_user WHERE user_name = '';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

3. Storage Engines

After the optimizer selects a plan, the storage engine performs the actual data reads and writes. Different engines provide different trade‑offs in performance, durability, and features.

3.1 What Is a Storage Engine?

A storage engine implements the low‑level operations for tables, such as how rows are stored on disk or in memory.

3.2 Choosing an Engine

Strong consistency and transactions → InnoDB

Read‑heavy workloads with table‑level locking acceptable → MyISAM

Temporary, fast‑access tables → Memory

3.3 Creating and Altering Engines

CREATE TABLE `t_user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE t_user ENGINE=MyISAM;

3.4 Engine Differences

List supported engines:

+--------------------+---------+--------------+------+------------+
| Engine             | Support | Transactions | XA   | Savepoints |
+--------------------+---------+--------------+------+------------+
| InnoDB             | DEFAULT | YES          | YES  | YES        |
| MyISAM             | YES     | NO           | NO   | NO         |
| MEMORY             | YES     | NO           | NO   | NO         |
| CSV                | YES     | NO           | NO   | NO         |
| ARCHIVE            | YES     | NO           | NO   | NO         |
+--------------------+---------+--------------+------+------------+

Key characteristics:

MyISAM : table‑level locking, no transactions, fast inserts/selects, stores row count.

InnoDB : ACID‑compliant, row‑level locking, MVCC for concurrent reads, supports foreign keys.

Memory : stores data in RAM for rapid access; data lost on restart; suitable for temporary tables.

CSV : plain CSV files, no indexes, useful for import/export.

Archive : compressed storage for large, rarely accessed data; no indexes; read‑only.

Developers can implement custom storage engines using MySQL’s internal API. Reference: https://dev.mvsql.com/doc/internals/en/custom-engine.html