Understanding MySQL Logical Architecture and the Pluggable Storage Engine API

MySQL’s support for multiple storage engines is a core architectural advantage; understanding how the MySQL server interacts with storage engines via a defined API helps grasp its fundamental design.

MySQL Logical Architecture MySQL is a complex networked data management system. Its logical layers include Connectors, Connection Management, SQL Interface, Parser, Optimizer, Caches & Buffers, and a Pluggable Storage Engine that delegates actual data operations to engine-specific implementations.

Connectors Clients communicate with MySQL over TCP using the MySQL protocol. Access can be programmed directly via the protocol or more conveniently through SDKs such as the Native C API, JDBC, PHP connectors, or ODBC.

Connection Management Each client connection is bound to a thread; MySQL caches threads or uses a thread pool to reduce creation overhead. Authentication (user, host, password) and optional SSL/TLS certificate verification occur before any SQL execution.

SQL Interface MySQL supports DML, DDL, stored procedures, views, triggers, and user‑defined functions.

Parser SQL statements are parsed into syntax trees, enriched with metadata from the data dictionary, permission‑checked, and optionally rewritten for optimization.

Optimizer Based on the syntax tree and table statistics, the optimizer decides table access order, index usage, and generates an execution plan that ultimately invokes storage‑engine API calls.

Caches & Buffers Internal caches such as the Query Cache can store complete SELECT results, bypassing parsing, optimization, and execution when a matching result is found.

Pluggable Storage Engine Storage engines implement the MySQL‑defined storage‑engine API. MySQL can load, unload, and use multiple engines simultaneously, assigning different engines per table. Engines manage on‑disk files, indexes, caches, buffers, transactions, and logs.

MySQL 5.7.11 ships with engines such as InnoDB, MyISAM, MEMORY, BLACKHOLE, CSV, ARCHIVE, PERFORMANCE_SCHEMA, etc. Example output of SHOW ENGINES;:

+--------------------+---------+------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                              | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row‑level locking, and FK   | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables               | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temp tables | NO           | NO   | NO         |
| ...                | ...     | ...                                                  | ...          | ...  | ...        |
+--------------------+---------+------------------------------------------------------+--------------+------+------------+

File System All data, table definitions, rows, and indexes are stored as files on a file system. Some engines (e.g., InnoDB) can operate on raw devices, but modern file systems make this unnecessary. Underlying storage may be local disks, DAS, NAS, SAN, etc.

Storage Engine API The API is defined as virtual functions in the handler class (see ./sql/handler.h). Key function groups include table lifecycle, locking, scanning, index access, and transaction handling.

Creating, Opening, and Closing Tables

/**
 * name: name of the table to create
 * form: TABLE structure matching the .frm definition
 * info: HA_CREATE_INFO containing CREATE TABLE options
 */
int create(const char *name, TABLE *form, HA_CREATE_INFO *info);

int open(const char *name, int mode, int test_if_locked); // mode: O_RDONLY or O_RDWR

int close(void);

Table Locking int ha_example::external_lock(THD *thd, int lock_type); Full Table Scan

virtual int rnd_init(bool scan);
virtual int rnd_next(byte *buf);

Index Access

int ha_foo::index_init(uint keynr, bool sorted);
int ha_foo::index_end(uint keynr, bool sorted);
int ha_index_first(uchar *buf);
int ha_index_next(uchar *buf);
int ha_index_prev(uchar *buf);
int ha_index_last(uchar *buf);
int index_read(uchar *buf, const uchar *key, uint key_len, enum ha_rkey_function find_flag);

Transaction Handling

// Start a transaction
int my_handler::start_stmt(THD *thd, thr_lock_type lock_type);
// Rollback a transaction
int (*rollback)(THD *thd, bool all);
// Commit a transaction
int (*commit)(THD *thd, bool all);

Writing Your Own Storage Engine MySQL’s official documentation provides guidance (see Custom Engine Documentation ). A practical starting point is the example engine in the source tree; you can rename its files to create a new engine:

sed -e s/EXAMPLE/FOO/g -e s/example/foo/g ha_example.h > ha_foo.h
sed -e s/EXAMPLE/FOO/g -e s/example/foo/g ha_example.cc > ha_foo.cc

By implementing the required virtual methods, you can integrate a custom storage engine into MySQL’s pluggable architecture.