Inside MySQL: How the Server Handles Connections, Parsing, and Storage Engines

1. Connection Handling

MySQL uses a client/server architecture. The client sends a SQL statement to the server, which parses, optimizes and returns the result. The server first establishes a connection via one of several transport methods.

TCP/IP : default protocol; server listens on port 3306. Connect with mysql -h <em>host_ip</em>.

UNIX domain socket : on Unix‑like systems the default socket file is /tmp/mysql.sock. When client and server run on the same host you can use the socket or 127.0.0.1 for TCP/IP.

Named pipe / shared memory (Windows): enable with --enable-named-pipe and use --pipe or --protocol=pipe; shared memory uses --shared-memory or --protocol=memory.

After the transport is established the connector authenticates the user name and password. On failure the connection is closed; on success the server loads the user’s privileges for the session.

To view current connections: SHOW GLOBAL STATUS LIKE 'Thread%'; Key fields: Threads_connected – active connections. Threads_running – non‑sleeping threads (concurrent queries).

Idle connections are closed after the timeout defined by wait_timeout (non‑interactive) and interactive_timeout, both defaulting to 28800 seconds (8 hours). The maximum number of simultaneous connections is controlled by max_connections (default 151).

2. Parsing and Optimization

When a query reaches the server it passes through three stages: query cache (if enabled), parser/preprocessor, and optimizer.

2.1 Query Cache

Older MySQL versions provided a query cache that returned results for identical statements. The cache is disabled by default ( query_cache_type = OFF) because it requires an exact text match and is invalidated on any table change. MySQL 8.0 removed the feature entirely.

2.2 Parser and Preprocessor

The parser performs lexical analysis (tokenizing) and syntax analysis (building a parse tree). Errors such as misspelled keywords or unmatched quotes are caught here. After parsing, the preprocessor checks semantic information—e.g., whether referenced tables and columns exist—and produces a refined parse tree.

Example of a syntax error:

mysql> chanmufeng;
ERROR 1064 (42000): You have an error in your SQL syntax …

2.3 Optimizer and Execution Plan

The cost‑based optimizer examines the parse tree, generates alternative execution plans and selects the one with the lowest estimated cost. Typical actions include join order selection, index choice, sub‑query flattening and predicate rewrite.

To view the chosen plan:

EXPLAIN SELECT * FROM t_user WHERE user_name = '';

For richer details use EXPLAIN FORMAT=JSON or enable optimizer_trace.

3. Storage Engine

The storage engine is a plug‑in responsible for reading and writing table data. Different engines provide different trade‑offs in durability, concurrency and performance.

3.1 Specifying an Engine

Create a table with a specific engine using the ENGINE= clause; if omitted MySQL uses the default engine (InnoDB in 5.7+).

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

Change an existing table’s engine:

ALTER TABLE t_user ENGINE=MyISAM;

3.2 Engine Comparison

Run SHOW ENGINES; to list supported engines and their capabilities.

+--------------------+---------+--------------+------+------------+
| 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:

InnoDB : transactional, row‑level locking, MVCC, foreign‑key support; default for most workloads.

MyISAM : table‑level locking, no transactions, fast for read‑heavy workloads.

MEMORY : stores data in RAM for ultra‑fast access; data is lost on restart; suited for temporary tables.

CSV : each table is a plain CSV file; no indexes; useful for data exchange.

ARCHIVE : compressed, write‑once/read‑many storage; no indexes, no UPDATE/DELETE.

3.3 Choosing an Engine

If strong consistency and transactions are required, choose InnoDB.

If reads dominate and table‑level locks are acceptable, MyISAM may be appropriate.

For temporary, fast‑lookup tables, use MEMORY.

When none of the built‑in engines meet specific requirements, a custom engine can be developed following the MySQL internals documentation: https://dev.mvsql.com/doc/internals/en/custom-engine.html

3.4 Engine Files

The data directory is given by the datadir variable (e.g., /var/lib/mysql/). Each database has its own subdirectory. Different engines store data in different file sets: InnoDB creates two files, MEMORY one file, and MyISAM three files.