How MySQL Executes SQL: From Connection Pools to Log Files Explained

Preface

Developers often write dozens of SQL statements daily, but many are unclear about how the system actually communicates with MySQL, how MySQL stores data, and how it manages transactions. This article explains the complete interaction between a system and MySQL.

MySQL Driver

The MySQL driver establishes the low‑level connection to the database. Once the connection is ready, the application can send SQL statements for CRUD operations.

In a web environment (e.g., Tomcat), multiple concurrent requests create many connections, which can waste resources if each request opens and closes a connection.

Database connection pool: maintains a set of ready connections; the system borrows a connection from the pool and returns it after use, eliminating the need to repeatedly create and destroy connections.

Common connection pools include Druid, C3P0, DBCP. Using a pool greatly reduces the overhead of creating and destroying connections.

Database Connection Pool

Instead of creating a new connection for every request, the system obtains a connection from the pool, solving performance loss caused by frequent connection creation.

MySQL also has its own internal connection pool, so both the application and MySQL manage connections efficiently.

Network Connections Are Handled by Threads

Each network request is processed by a dedicated thread; MySQL processes SQL requests with separate threads.

SQL Interface

After a thread receives a request, it forwards the SQL statement to the SQL interface for processing.

Query Parser

For example, the SQL statement:

SELECT stuName, age, sex FROM students WHERE id=1

The parser translates the human‑readable SQL into a form MySQL can understand.

MySQL chooses the most efficient execution path based on its optimizer.

MySQL Query Optimizer

The optimizer selects the cheapest index using a cost model that considers I/O cost (default 1 per page) and CPU cost (default 0.2 per row).

It then generates an execution plan.

Storage Engine

The optimizer calls the storage engine to actually execute the SQL. Data resides in memory (buffer pool) or on disk.

Understanding the storage engine is essential; detailed coverage will follow in future articles.

Executor

The executor follows the execution plan, invoking the storage engine to perform the operation.

Buffer Pool

The InnoDB buffer pool caches data in memory, similar to Redis, to avoid costly disk I/O. When a query accesses data, MySQL first checks the buffer pool; if the data is missing, it loads it from disk and caches it.

InnoDB looks for the row in the buffer pool.

If not found, it loads the row from disk into the buffer pool.

The row receives an exclusive lock for the update.

Undo Log

Before modifying a row, MySQL writes the original version to the undo log so the transaction can be rolled back if needed.

Redo Log

After the update, MySQL records the new version in the redo log buffer, which is later flushed to the redo log file on disk to guarantee durability.

Transaction Commit Steps

Prepare the UPDATE statement.

InnoDB checks the buffer pool (or loads from disk) and writes the original row to the undo log.

The update is performed in the buffer pool.

The modified row is written to the redo log buffer.

During commit, the redo log buffer is flushed to the redo log file.

The operation is also recorded in the binlog.

The binlog file name and position are stored in the redo log, followed by a commit marker.

Binlog

The binlog records logical changes (e.g., “UPDATE students SET stuName='X' WHERE id=1”) and is used for replication and point‑in‑time recovery. It can be written in STATEMENT, ROW, or MIXED mode.

Property

Redo Log

Binlog

File size

Fixed (configurable)

Configurable via max_binlog_size Implementation

InnoDB engine layer

MySQL server layer (all engines)

Write method

Circular write

Append‑only

Use case

Crash‑safe recovery

Replication & backup

Conclusion

We have covered how MySQL handles a simple UPDATE: from the driver and connection pool, through thread processing, parsing, optimization, storage engine interaction, buffer pool caching, undo/redo logging, binlog recording, and finally transaction commit. Future articles will dive deeper into buffer pool internals.