MySQL Architecture Explained

• 28 Apr, 2026
• 0 Comments
• 5 Mins Read

MySQL Architecture Explained

Introduction: What is MySQL Architecture?

When building high-performance applications, understanding MySQL architecture is the difference between a system that scales and one that crashes during a traffic spike. As a premier RDBMS, MySQL’s modular design allows it to power everything from microservices to massive data warehouses.

But what is the architecture of MySQL? It is a tiered system that separates query processing from data storage. However, this flexibility can be a double-edged sword. If you don’t understand how the layers interact, you’ll likely face “silent” performance killers like lock contention or optimizer failures.

In this mysql architecture explanation, we move beyond the basics to address real-world pitfalls and how to architect for stability.

1. The Layered Overview

2. Layer 1: Client Layer

This is how external applications or users connect to MySQL.

• Connection Manager: Handles TCP/IP (port 3306), Unix sockets, named pipes.

• Thread Manager: Each connection gets a dedicated thread (one-thread-per-connection). Avoids process spawning overhead.

• Authentication: Username, password, host-based validation (mysql.user table). Supports plugins (e.g., LDAP, PAM).

• SSL/TLS encryption: Encrypts data in transit.

• Connection Pooling (optional): Via a separate connection pooler (e.g., ProxySQL, or application-level).

Note: MySQL 8.0+ also supports multiplexing (more efficient for short connections) and admin port for privileged access.

3. Layer 2: Logical (Server) Layer

This is MySQL’s brain. It is engine-agnostic.

A. SQL Interface

• Receives SQL commands (SELECT, INSERT, etc.)

• Handles stored procedures, triggers, views

• Outputs result sets

B. Parser (Lexical & Syntax Analysis)

• Validates SQL syntax

• Converts SQL into a parse tree (data structure)

C. Preprocessor (Resolver)

• Checks table/column existence

• Resolves names (e.g., aliases, wildcards)

• Checks privileges

D. Optimizer (Critical for Performance)

Steps:

1. Query rewriting (e.g., IN → EXISTS if beneficial)

2. Cost-based optimization – estimates cost of different plans using table statistics (index cardinality, row count)

3. Join reordering – chooses smallest table first

4. Index selection – chooses between B-Tree, Hash, Full-text

5. Query transformation (e.g., IN to JOIN, OR to UNION)

Output: Execution Plan (EXPLAIN command)

E. Query Cache (Removed in MySQL 8.0)

• Pre-8.0: Stores result sets for identical SELECT queries. Was useful but caused lock contention.

• MySQL 8.0+: Completely removed. Use application-level cache (Redis, Memcached) or InnoDB buffer pool.

F. Caches & Buffers (at server layer)

• Table Definition Cache: Stores .frm (or data dictionary) metadata.

• Thread Cache: Reuse threads for new connections.

• Global Privilege Cache: After FLUSH PRIVILEGES.

G. Execution Engine

• Executes the plan by calling the storage engine API.

• Performs sorting, grouping, temporary tables (on disk or in memory).

4. Layer 3: Storage Engine Layer (Pluggable)

This is MySQL’s unique strength. The server layer does not know how data is stored. Each engine implements a standard interface: handler API (e.g., read_row, write_row, index_read).

Most Important Engines

How Engines Work Under the Hood (InnoDB focus)

• Buffer Pool (InnoDB): Caches both data pages and index pages. One of the most critical memory settings (innodb_buffer_pool_size).

• Change Buffer: Caches changes to secondary indexes (reduces random I/O).

• Adaptive Hash Index: InnoDB builds hash indexes automatically on hot pages.

• Doublewrite Buffer: Prevents torn pages (partial writes) – important for crash safety.

• Redo Log (ib_logfile*): Writes changes sequentially for crash recovery.

• Undo Log: Rollback and MVCC (creates old versions of rows).

5. Layer 4: Physical File System

MySQL stores data in files (OS-level). Location defined by datadir.

Key File Types

InnoDB Tablespace Architecture (simplified)

System tablespace (ibdata1)
├── Doublewrite buffer
├── Change buffer
├── Undo logs (unless separate undo tablespace)
└── Data dictionary (in 8.0, moved to mysql.ibd)

File-per-table (table.ibd)
├── B-Tree data pages
├── Index pages
└── Insert buffer bitmap

6. Memory Architecture (Important for Performance)

7. Process Flow of a Query (Example: SELECT * FROM orders WHERE id=5)

1. Client → sends SQL over TCP/IP.

2. Connection thread receives it.

3. Parser → checks syntax → parse tree.

4. Resolver → checks orders table exists, id column exists.

5. Optimizer → sees primary key index on id → cost = 1 (direct lookup). Chooses index scan.

6. Execution Engine → calls InnoDB’s index_read() with id=5.

7. InnoDB:

   • Checks buffer pool for page containing id=5.

   • If missing, reads from orders.ibd file into buffer pool.

   • Returns row.

8. Execution Engine → formats result set.

9. Server Layer → sends result back to client.

10. Connection thread → waits for next query.

8. Key Diagrams Mentally Visualize

[Client App] → [Connector (Thread)] → [SQL Interface] → [Parser] → [Optimizer]
                                                                   ↓
[Storage Engine API] ← [Execution Engine] ← [Caches] ← [Query Cache (gone)]
         ↓
[InnoDB/MyISAM] → [Buffer Pool] → [OS File System]

For UPDATE/INSERT:

Execution Engine → InnoDB → Write to Log Buffer → Write Redo Log (prepare)  
→ Buffer Pool (dirty page) → Commit → Redo Log (commit) →  
Background: Purge thread flushes to .ibd → Doublewrite buffer → Data file.

9. Evolution Summary (Versions)

10. Common Misunderstandings

• “MySQL is single-threaded” → False. Each connection gets a thread. Internally, InnoDB uses background threads (purge, page cleaner, redo log flush).

• “MyISAM is faster” → False for modern workloads. InnoDB’s row locking + buffer pool usually outperforms under concurrency.

• “Increasing buffer pool solves everything” → True only if you properly size it and have enough RAM. Oversizing causes swap.

• “Statement-based replication is safe” → Non-deterministic functions (NOW(), UUID()) cause inconsistencies. Row-based is safer (default in 8.0).

11. Further Study Suggestions

To deeply understand MySQL architecture:

1. Read High Performance MySQL (O’Reilly) – covers internals.

2. Commands to explore:

   • SHOW ENGINE INNODB STATUS\G

   • EXPLAIN FORMAT=TREE SELECT ...

   • SELECT * FROM performance_schema.memory_summary_global_by_event_name;

3. Watch for lock waits and deadlocks using performance_schema.