storage engines implement data handling at the physical level. They handle the data files, the data and the index caches if they exist, and whatever is necessary to efficiently manage and read the data.
For each table, one file exists. These files contain the definition of the table, and are created and used by the server. see the available storage engines: Using the
SHOW ENGINES statement or querying the
SELECT ENGINE, SUPPORT FROM information_schema.ENGINES
FRM is a file extension for formatting used with MySQL. FRM stands for FoRMat. FRM files are used to define the format of a table used with MySQL. MySQL is a cross-platform relational database. FRM files will have the same name as the table they reference, but with a .FRM extension. FRM files are created by MySQL when the table is created.
XtraDB and InnoDB
InnoDB became the default engine with MariaDB 5.5 and MySQL 5.5. Percona maintains an InnoDB fork called XtraDB; it is InnoDB with bug fixes applied by Percona.By default, MariaDB uses XtraDB. For compatibility with InnoDB and MySQL, the commands still mention InnoDB but the XtraDB fork is used instead.InnoDB is a high-performance, general-purpose storage engine that supports transactions with savepoints, XA transactions, and foreign keys. Savepoints are intermediate states that can be saved in the middle of a transaction and can then be restored if necessary. XA is a special type of transaction designed for operations that involve multiple resources. When a table is created, a storage engine should be specified. If not, the default storage engine will be used. The default storage engine is specified in the storage_engine system variable.
InnoDB data structures
InnoDB tables are contained in tablespaces. A tablespace is a file that contains data and indexes for one or more tables. In old MariaDB and MySQL versions, all the tables are created in a system tablespace. If the innodb_file_per_table system variable is set to 1, which is the default since the 10.0 Version, each table is stored in a separate tablespace. This variable is dynamic, so it is possible to store some tables in separate files, and others in the system tablespace.
The system tablespace, by default, also contains InnoDB’s data dictionary, the undo logs, the change buffer, and the doublewrite buffer. The data dictionary is a metadata collection of all InnoDB tables, columns, and indexes. The system tablespace is stored in the data directory, in the ibdata files.
A tablespace is a storage location where the actual data underlying database objects can be kept.It provides a layer of abstraction between physical and logical data, and serves to allocate storage for all DBMS managed segments.Tablespaces specify only the database storage locations, not the logical database structure, or database schema.By using tablespaces, an administrator also can control the disk layout of an installation.Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.
This storage engine is developed by Tokutek and has been included in MariaDB since Version 5.5, though it must be installed and enabled separately. It supports transactions with savepoints, XA transactions, but** not foreign keys and full-text indexes**. Its main peculiarity is the use of a *new data structure* for indexes: the fractal trees.each node has a buffer. This buffer contains the changes that need to be applied to the nodes that are more in-depth. Only when the buffer is full are the changes applied altogether. If the changes need to be written to disk, this is an important optimization, because writing fewer and bigger blocks is usually much faster.
Another important feature of TokuDB is data compression.compression level depends on the dataset.
Fractal trees and compression make TokuDB suitable to work with datasets that are too big to be entirely stored in memory. For such workloads, TokuDB can be faster than InnoDB.
MyISAM and Aria
MyISAM was historically the default storage engine for MySQL and MariaDB, before Version 5.5.It is a relatively simple engine, optimized for read-heavy workloads where there are just a few writes or no writes at all. MyISAM is good for data warehousing and more generally for data reporting where data can be appended to tables, but not modified or deleted.
Aria is designed to be MyISAM’s successor. It uses logs that allow data recovery after a crash.Aria uses a different data format called PAGE that is generally faster and never fragments too much.but it is possible to use the FIXED or DYNAMIC formats for compatibility with MyISAM.users should be aware that bulk writes are slower in Aria, particularly where duplicate indexed values exist. Both MyISAM and Aria do not support transactions and foreign keys.
The MRG_MyISAM storage engine, also called MERGE, can be used to build a table on multiple MyISAM identical tables, to work around the file size limit of the operating system.
/dev/null storage engine **(anything you write to it disappears).The BLACKHOLE storage engine is inherited from MySQL. *BLACKHOLE tables are always empty*. Modifications have no effect on them and queries *always return an empty result set*.
It reads and writes data into other instances of MariaDB. XA transactions are supported. SPIDER has been designed for data sharding.
The CONNECT storage engine is a MariaDB-specific storage engine that allows reading and writing data from and to external sources. The data sources can be MariaDB or MySQL connections, ODBC connections, files, and directories.
The ARCHIVE storage engine handles compressed tables. It has several limitations, such as the inability to modify or delete data after an insertion, and is quite slow. Nowadays, compressed InnoDB, MyISAM, or TokuDB tables are always preferable.
The CassandraSE storage engine connects to the Apache Cassandra NoSQL server to read and write data.
storage engine is used to allow MariaDB to access a table that is stored in the Sphinx database server.
Hash based, stored in memory, useful for temporary tables.The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.
The CSV storage engine stores data in text files using comma-separated values format.
The FEDERATED storage engine lets you access data from a remote MariaDB database without using replication or cluster technology.Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.
The performance_schema storage engine is only used internally for the tables in the performance_schema databases. The only reason why a database administrator (DBA) should be aware of it is that a specific statement exists to check how much memory is consumed by the performance_schema by using the
SHOW ENGINE performance_schema STATUS command.