Unveiling MySQL’s Inner Workings: From SQL Query to InnoDB Storage Engine

1. Introduction

For backend developers, MySQL is a familiar database tool, but many Java engineers only know basic operations and treat the SQL execution process as a black box. This article breaks that black box by explaining MySQL’s logical architecture through the execution of a sample UPDATE statement.

2. Application Layer

2.1 Connection Thread Handling

When MySQL receives the SQL, the first step is to transmit it via the MySQL driver. Java programs use the driver (Maven dependency) to establish a network connection to the database. Connection pooling is introduced to avoid the overhead of creating a new connection for each request.

After authentication, MySQL validates the request based on username, host, and password, and checks permissions.

3. Service Layer

3.1 SQL Interface

The SQL interface component receives the statement from the connection thread and forwards it for processing.

3.2 SQL Parser

The parser converts the statement into a syntax tree, breaking the UPDATE into logical parts: target table, row condition, and new column value.

Update data in the users table

Where id = 10

Set name = “zhangsan”

3.3 SQL Optimizer

The optimizer chooses the best execution plan among possible paths, such as directly locating the row by primary key or using an index on the name column. The chosen plan becomes the execution plan.

3.4 Executor

The executor follows the execution plan and invokes the storage engine interface to perform the actual data modification.

3.5 Query Cache

MySQL historically had a query cache, but it required exact SQL matches and became invalid after any table modification. It is disabled by default in MySQL 5.7 and removed in 8.0.

4. Storage Engine Layer

4.1 Overview

The InnoDB storage engine handles data access and modification. The sample UPDATE statement is used to illustrate its core mechanisms.

4.2 Buffer Pool

The buffer pool resides in memory and caches data pages to avoid frequent disk I/O. It functions similarly to a Redis cache for the database.

4.2.1 Data Pages, Cached Pages, and Dirty Pages

Data is stored in fixed‑size pages (default 16 KB). When a page is loaded into the buffer pool, it becomes a cached page; if modified before being flushed, it is a dirty page.

4.2.2 Metadata

Metadata tracks the relationship between disk pages and their in‑memory counterparts.

4.2.3 Free List

A doubly linked free list holds metadata of empty cached pages, allowing quick allocation when loading new pages.

4.2.4 Flush List

Dirty pages are placed on a flush list to be written back to disk asynchronously.

4.2.5 LRU List

The LRU list orders cached pages by recent access. MySQL splits it into hot and cold segments to avoid evicting recently loaded pages that have not yet been accessed.

4.2.6 Summary

The buffer pool’s hot‑cold separation provides a useful reference for designing cache eviction strategies in other systems.

4.3 Undo Log

Undo logs record the inverse of each modification, enabling transaction rollback and supporting MVCC for non‑blocking reads.

4.4 Redo Log

Redo logs capture changes before they are flushed to disk, allowing recovery after a crash. They are written sequentially for high performance. MySQL’s flush policies (innodb_flush_log_at_trx_commit = 0, 1, 2) determine durability guarantees.

During commit, the executor writes to the server‑level binlog and then marks the redo log with a commit record to ensure consistency between the two logs.

5. Conclusion

By following a single UPDATE statement through MySQL’s architecture, we gain insight into how the application, service, and storage engine layers collaborate, and how components such as the buffer pool, undo log, and redo log work together to provide efficient and reliable data management.