MySQL Architecture: Server Layer vs Pluggable Storage Engines (InnoDB vs MyISAM)

A legacy e-commerce server running MySQL experiences an unexpected power outage.

When the server reboots, the operations team finds two different database tables affected:

1. orders_v2 (configured with the InnoDB storage engine): Flushes its Write-Ahead Log (WAL) during boot, applies redo log records, rolls back uncommitted transactions, and recovers completely within 3.2 seconds.
2. legacy_catalog (configured with the MyISAM storage engine): Fails to boot. System logs display: ERROR 144 (HY000): Table 'legacy_catalog' is marked as crashed and should be repaired. Rebuilding its corrupt .MYI index file takes 6 hours of system downtime.

Why did two tables in the exact same database engine behave so differently during a hardware crash?

The answer lies in MySQL’s two-tier architecture, which separates SQL parsing and query planning from physical disk storage management.

---

The Two-Tier Architecture

Unlike monolithic databases where the query parser, index manager, and disk storage engine are tightly coupled into a single binary subsystem, MySQL separates responsibilities across two decoupled layers:

+-------------------------------------------------------------------+
|                        CLIENT CONNECTIONS                         |
|                 (JDBC, ODBC, Python, Go, MySQL CLI)               |
+-------------------------------------------------------------------+
                                  |
                                  v
+-------------------------------------------------------------------+
|                        MYSQL SERVER LAYER                         |
|  - Connection Manager & Authentication                            |
|  - Thread Manager & Connection Pool                               |
|  - SQL Parser & Lexer (Generates AST)                             |
|  - Optimizer (Cost-Based Query Planner)                           |
|  - System Caches & Binary Log (Binlog) Handler                    |
+-------------------------------------------------------------------+
                                  |
                                  |  Pluggable Handler API
                                  |  (C++ Virtual Abstract Interface)
                                  v
+-------------------------------------------------------------------+
|                  PLUGGABLE STORAGE ENGINE LAYER                   |
|                                                                   |
|  +-----------------+  +-----------------+  +------------------+  |
|  |     InnoDB      |  |     MyISAM      |  |  Memory / CSV /  |  |
|  | (ACID, Locks)   |  | (Table Lock)    |  | RocksDB (MyRocks)|  |
|  +-----------------+  +-----------------+  +------------------+  |
+-------------------------------------------------------------------+
                                  |
                                  v
+-------------------------------------------------------------------+
|                     PHYSICAL DISK STORAGE                         |
|                     (.ibd, .MYD, .MYI files)                      |
+-------------------------------------------------------------------+

---

1. The MySQL Server Layer

The Server Layer handles network connectivity, SQL syntax validation, security, and query execution planning. It remains identical regardless of which storage engine holds table data.

Responsibilities of the Server Layer:

• Connection Handling: Manages client connection lifecycle using thread pools.
• Parser & Lexer: Converts SQL text strings into Abstract Syntax Trees (ASTs).
• Cost-Based Optimizer (CBO): Rewrites queries, selects join strategies, and constructs the physical Volcano Iterator execution tree.
• Binary Logging (Binlog): Records transaction events for point-in-time recovery and database replication across replica nodes.

Crucially, the Server Layer has no knowledge of how bytes are physically laid out on disk or how index leaf pages are navigated.

---

2. The Pluggable Storage Engine API (handler Interface)

The Server Layer interacts with storage engines exclusively through a unified C++ virtual abstract interface called the Handler API (handler and handlerton classes).

When the SQL execution engine needs to process a query step, it invokes C++ virtual methods on the table’s handler object:

class handler {
public:
    virtual int ha_open(const char *name, int mode, int test_if_locked);
    virtual int index_read_map(uchar *buf, const uchar *key, key_part_map keypart_map, enum ha_rkey_function find_flag);
    virtual int rnd_next(uchar *buf);
    virtual int write_row(uchar *buf);
    virtual int update_row(const uchar *old_data, uchar *new_data);
    virtual int delete_row(const uchar *buf);
    virtual int external_lock(THD *thd, int lock_type);
};

Execution Flow Example

For the query SELECT * FROM users WHERE id = 42:

1. Server Layer calls ha_index_read_map(buf, key=42).
2. InnoDB implementation decodes key=42, navigates its internal B+ Tree index in the Buffer Pool, copies row bytes into buf, and returns control to the server.
3. If the user changed the storage engine to MyISAM or RocksDB via ALTER TABLE users ENGINE = MyISAM, zero code changes in the Server Layer are required. The Server Layer simply invokes the MyISAM implementation of ha_index_read_map.

---

3. Storage Engine Deep Dive: InnoDB vs MyISAM

To understand why MyISAM failed during the power crash while InnoDB recovered smoothly, we must compare their architectural features:

InnoDB Architecture (Clustered)       MyISAM Architecture (Heap + Index)

[ Primary Key B+ Tree ]                [ .MYD Heap File ]
  Leaf Nodes contain                     Data rows stored unordered by insertion
  FULL ROW DATA                          RID = Byte Offset (0x0A4F)
        ^                                      ^
        |                                      |
[ Secondary Index ]                    [ .MYI Index File ]
  Leaf Nodes contain                     All index leaves store 
  PRIMARY KEY (id)                       file offset pointers to .MYD

A. Data Layout Mechanics

• InnoDB (Clustered B+ Tree): Table rows are organized into a Primary Clustered Index B+ Tree (.ibd file). The primary key IS the table. Leaf pages hold actual row column payloads. Secondary indexes store the primary key value as their pointer.
• MyISAM (Heap File + Non-Clustered Indexes): Table data is stored in a simple unordered heap file (.MYD). Indexes are stored separately in an index file (.MYI). Every index leaf contains a direct byte offset file pointer into .MYD.

B. Locking Granularity

• InnoDB: Supports Row-Level Locking via Next-Key locking. Multiple threads can perform simultaneous UPDATE operations on different rows inside the same table without blocking each other.
• MyISAM: Supports only Table-Level Locking. Any INSERT, UPDATE, or DELETE statement acquires an exclusive lock on the entire table file, blocking all concurrent read and write queries.

C. Crash Recovery & Durability

• InnoDB: Enforces full ACID transactions using Write-Ahead Logging (WAL) redo logs (ib_logfile) and Undo Logs. If power fails mid-transaction, InnoDB replays the redo log during startup (ARIES protocol) to restore database consistency automatically.
• MyISAM: Writes index updates directly to page caches without a Write-Ahead Log. If a crash occurs mid-write, the .MYI index file header pointers become desynchronized from the .MYD heap data, leaving the index corrupted.

---

Feature Comparison Matrix

Feature / Capability	MySQL InnoDB	Legacy MyISAM
Primary Data Structure	Clustered B+ Tree	Unordered Heap File (.MYD)
Transaction Support (ACID)	Yes (FULL ACID)	No (Non-transactional)
Locking Granularity	Row-Level Locking	Table-Level Locking
Crash Recovery Guarantee	Automatic WAL/ARIES Recovery	Manual REPAIR TABLE required
Foreign Key Constraints	Enforced natively	Ignored
Buffer Pool / Caching	Caches Data Pages + Indexes in RAM	Caches Index Pages ONLY (Relies on OS Page Cache for data)
MVCC Read Support	Yes (Non-blocking reads)	No (Reads block on table locks)

---

Summary & Next Steps

MySQL’s pluggable storage engine architecture provides flexibility while isolating SQL layer functionality from storage implementations:

• The Server Layer manages connections, query optimization, and binary logging.
• The Handler API defines an abstract contract (ha_open, index_read_map) connecting the Server Layer to storage drivers.
• InnoDB is a fully transactional, clustered index storage engine engineered for concurrent applications and crash safety.
• MyISAM is a non-transactional heap engine that relies on table locks and lacks WAL crash safety guarantees.

In the next article, we inspect InnoDB Primary Clustered Indexes vs Secondary Index Lookups (The Double Lookup Cost).

References & Further Reading

1. Graefe, G. (1994). Volcano - An Extensible Parallel Query Evaluation System. IEEE Transactions on Knowledge and Data Engineering, 6(1), 120–135.
2. Selinger, P. G., et al. (1979). Access Path Selection in a Relational Database Management System. Proceedings of ACM SIGMOD, 23–34.

---