Unlock MySQL’s Pluggable Storage Engine: Architecture and How to Build Your Own

Supporting multiple storage engines is a well‑known MySQL feature and a key advantage of its architecture. Understanding how MySQL Server interacts with storage engines via the API greatly clarifies MySQL’s core infrastructure.

MySQL Logical Architecture

MySQL is a large network program and data‑management system with a complex architecture. The diagram below outlines its logical structure.

Connectors

MySQL operates over TCP and defines its own application‑layer protocol. Clients can connect via native SDKs (C API, JDBC, PHP, ODBC, etc.) which ultimately communicate with the server using this protocol.

Connection Management

Each TCP connection is bound to a thread; queries run in that thread. To avoid the overhead of constantly creating and destroying threads, MySQL caches threads or uses a thread pool.

After a client connects, authentication based on username, host, and password is performed, optionally using SSL/TLS certificates.

SQL Interface

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

Parser

The parser builds a syntax tree for each query, enriches it with metadata from the data dictionary, checks permissions, and performs syntactic optimizations such as query rewrite.

Optimizer

Using the syntax tree and statistical information, the optimizer decides table read order, chooses appropriate indexes, and generates an execution plan. The actual data operations are carried out through the storage‑engine API, independent of the specific engine implementation.

Caches & Buffers

MySQL maintains various caches and buffers, e.g., the Query Cache, which stores the result of a SELECT statement to avoid re‑parsing, optimizing, and executing the query again.

Pluggable Storage Engine

Each storage engine implements part or all of the MySQL‑defined storage‑engine API. Engines can be installed or removed dynamically, multiple engines can coexist, and each table can specify its engine. Engines manage table data, indexes, runtime caches, buffers, transactions, and logs on top of the file system.

MySQL 5.7.11 ships with the following default engines:

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)   | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

File System

All data—databases, table definitions, rows, indexes—are stored as files in the underlying file system. Some engines (e.g., InnoDB) can manage raw devices directly, but modern file systems make this unnecessary. Under the file system, storage can be local disks, DAS, NAS, SAN, etc.

Storage Engine API

MySQL defines a set of storage‑engine APIs to support its pluggable architecture. The API consists of virtual functions in the handler class, defined in ./sql/handler.h:

/**
  The handler class is the interface for dynamically loadable
  storage engines. Do not add ifdefs and take care when adding or
  changing virtual functions to avoid vtable confusion

  Functions in this class accept and return table columns data. Two data
  representation formats are used:
  1. TableRecordFormat - Used to pass [partial] table records to/from
     storage engine

  2. KeyTupleFormat - used to pass index search tuples (aka "keys") to
     storage engine. See opt_range.cc for description of this format.

  TableRecordFormat
  =================
  [Warning: this description is work in progress and may be incomplete]
  The table record is stored in a fixed-size buffer:

    record: null_bytes, column1_data, column2_data, ...

  // ... (omitted for brevity)
*/
class handler : public Sql_alloc
{
    // ... implementation details omitted
}

The following sections describe selected API functions.

Creating, Opening, and Closing Tables

Creating a table:

/**
  *name: name of the table to create
  *form: TABLE structure matching the .frm file already created by MySQL Server
  *info: HA_CREATE_INFO structure containing information from the CREATE TABLE statement
*/
int create(const char *name, TABLE *form, HA_CREATE_INFO *info);

Opening a table:

/**
  mode contains two values:
  O_RDONLY - Open read only
  O_RDWR   - Open read/write
*/
int open(const char *name, int mode, int test_if_locked);

Closing a table:

int close(void);

Table Locking

When a client issues LOCK TABLE, the engine’s external_lock function is called:

int ha_example::external_lock(THD *thd, int lock_type);

Full Table Scan

// Initialize full‑table scan
virtual int rnd_init(bool scan);

// Retrieve next row
virtual int rnd_next(byte* buf);

Index Access

// Called before using an index
int ha_foo::index_init(uint keynr, bool sorted);

// Called after using an index
int ha_foo::index_end(uint keynr, bool sorted);

// Read first entry from index
int ha_index_first(uchar *buf);

// Read next entry from index
int ha_index_next(uchar *buf);

// Read previous entry from index
int ha_index_prev(uchar *buf);

// Read last entry from index
int ha_index_last(uchar *buf);

// Read a row by key using the index
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);

How to Write Your Own Storage Engine

The official MySQL documentation provides a guide for creating custom storage engines. As a starting point, examine the EXAMPLE storage engine in the source tree, which implements the required API.

To create a new engine, copy and rename the example files:

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