MySQL's multi-storage engine architecture

Supporting multiple storage engines is a well-known MySQL feature and one of the key benefits of MySQL architecture. If you can understand how MySQL server interacts with the storage engine through the API, it will greatly benefit the understanding of MySQL's core infrastructure. This article will first introduce the overall logical architecture of MySQL, then analyze the MySQL storage Engine API and describe how to write your own MySQL storage engine.

MySQL logical architecture

MySQL as a large network program, data management system, the architecture is very complex. The logical architecture is roughly drawn out.

Connectors

MySQL is first a network program that defines its own application layer protocol on top of TCP. So to use MySQL, we can write code that establishes a TCP connection with MySQL server and then interacts with the protocol it defines. Of course this is more troublesome, the more convenient way is to call the SDK, such as native C API, JDBC, PHP and other languages MySQL Connector, or through ODBC. However, by accessing MySQL through the SDK, you are essentially interacting with MySQL via the MySQL protocol on a TCP connection.

Connection Management

Each TCP-based network service needs to manage client links, and MySQL is no exception. MySQL binds one thread for each connection, and then all queries on that connection are executed in this thread. To avoid the overhead of frequently creating and destroying threads, MySQL usually caches threads or uses a thread pool to avoid frequent creation and destruction of threads.

After the client connects to MySQL, authentication is required before using the MySQL feature, which is based on the user name, hostname, and password. If you connect using SSL or TLS, certificate authentication is also performed.

SQL Interface

MySQL supports a variety of SQL language interfaces such as DML (data Manipulation language), DDL (data definition Language), stored procedures, views, triggers, custom functions, and more.

Parser

MySQL parses the SQL query, creates a syntax tree for it, and enriches the query syntax tree based on the data dictionary, verifying that the client has permission to execute the query. After creating a good syntax tree, MySQL also makes syntax optimizations for SQL queries, and queries are rewritten.

Optimizer

After parsing and query rewriting, MySQL optimizes SQL based on the statistics of the syntax tree and data, including determining the reading order of the table, selecting the appropriate index, and finally generating the concrete execution steps of the SQL. The actual data operations in these specific execution steps are done through a pre-defined storage Engine API, regardless of the specific storage engine implementation.

Caches & Buffers

MySQL internally maintains some caches and buffers, such as the query cache is used to cache the execution of a SELECT statement, and if the corresponding query results can be found in it, then there is no need for query parsing, optimization and execution of the entire process.

Pluggable Storage Engine

The specific implementation of the storage engine, which implements some or all of the storage engine APIs that are well-defined by MySQL. MySQL can dynamically install or remove the storage engine, there can be a variety of storage engines exist simultaneously, you can set a different storage engine for each table. The storage engine is responsible for managing the data of the table, the actual contents of the index on top of the file system, as well as managing the cache, Buffer, transaction, log and other data and functions at runtime.

The default supported storage engines for MySQL 5.7.11 are as follows:

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, storedIn memory, usefulFor 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 the data, the database, the table definition, the contents of each row of the table, the index, are present on the file system, in the way of files. Of course, some storage engines, such as InnoDB, also support the direct management of bare devices without using file systems, but the implementation of modern file systems makes this unnecessary.

Under the file system, local disks can be used, and various storage systems such as Das, NAS, and San can be used.

Storage Engine API

MySQL defines a series of storage engine APIs to support the plug-in storage engine architecture. The API exists as a virtual function of the handler class, and you can view the details in./sql/handler.h under the code base, and you can see the description in the comments in the handler class:

/**The handlerClassis the interfaceForDynamically loadable storage engines. DoNotAdd IfdefsandTake care when addingOrchangingVirtualFunctions to avoid vtable confusion functions inThis ClassAcceptand ReturnTable columns data. Data representation formats is used:1.tablerecordformat-used to pass [partial] table records To/from storage Engine2.keytupleformat-used to pass index search tuples (aka"Keys")to storage engine. See opt_range.ccfor description of this format. Tablerecordformat ================= [Warning: this  description is work in progress and may be incomplete] The table record was stored in a fixed-size buffer:record:null_bytes, Column1_da Ta, Column2_data, ... //length reasons, omitted part of the content. */class handler: public sql_alloc{//length reason, do not list specific code. Readers can find specific content directly in the source file./sql/handler.h. } 

Below I will describe some of the storage engine APIs.

Create, open, and close tables

Create a table by using the function ' create ':

/** *name: the name of the table to be created  *from: the structure of a table type, the definition of the tables to be created, matches the contents of the Tablename.frm file that the MySQL server has created.  *info: a ha_create_info type of structure, Contains information for the CREATE TABLE statement entered by the client */int create (const  Char *name, TABLE *form, Ha_create_info *info);        

Open a table with the function opening:

/**Mode contains the following two types of O_rdonly-open read Only O_rdwr-open read/write*/int Open(const char *name, int mode, int test_if_locked); 

Close a table with a function close:

int close(void);

Lock the table

When the client invokes lock table, it is locked by the External_lock function:

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

Full table Scan

//initialize full table scan  Virtual int  rnd_init  (bool Scan); //read the next line from the table virtual int rnd_next  (byte* BUF)                 

Accessing table content by index

Call this method before using the indexint Ha_foo::index_init (UINT KEYNR,BOOL sorted)Call this method after using the indexint Ha_foo::index_end (UINT KEYNR,BOOL sorted)Read the first item of the indexInt Ha_index_first(Uchar* buf);Read the next item in the indexInt Ha_index_next(Uchar* buf);Read the previous item in the indexInt Ha_index_prev(Uchar* buf);Read the last item of the indexInt Ha_index_last(uchar * buf);  Given a key based on the index read content int index_read(uchar * buf, const uchar * key,  UINT Key_len, enum ha_rkey_function find_flag)        

Transaction processing

//开始一个事务int my_handler::start_stmt(THD *thd, thr_lock_type lock_type)//回滚一个事务int (*rollback)(THD *thd, bool all); //提交一个事务int (*commit)(THD *thd, bool all);

How to write your own storage engine

In the official MySQL documentation, there are guidance documents for writing your own storage engine, linked below.

As the start of writing your own storage engine, you can view a example storage engine in the MySQL repository, which implements the storage engine APIs that must be implemented, and can be copied as the start of writing our own storage Engine:

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

MySQL's multi-storage engine architecture