2/2
InnoDB
InnoDB is the default engine of MySQL 5.7. It’s a complete transactional engine wich supports since yet the full text search, last argument that pleaded , until recently in Mylsam’s favor.
Although its control mechanisms on writings and integrity of data make InnoDB a slightly slower engine than Mylsan and the performances of his full text search are poor yet. Additionnaly, it mobilizes more resources because InnoDB tables have to be loaded in RAM to function optimally.
Anyway, in a context of massive concurrential accesses, its lock management at row level definitly stands out from Mylsam. It’s important to note that the transactional operation of InnoDB makes it vulnerable to dead loacks which will have to be correctly managed on applicative level.
An InnoDB table involved in a foreign key constraint can’t be partitionned and vice versa
MEMORY (formerly HEAP)
The MEMORY engine is mainly used for the creation of temporary tables.
It’s fast because it works exclusively in memory, but does not keep data in case of unexpected server shutdown. So it must be reserved for punctual treatments, not critics ones.
MERGE
The MERGE engine allows to federate several Mylsam tables in an unique one, provided these latter have the same exact structure.
In that case, the MERGE table acts as a partitioned table in which partitions are the original tables.
Except that organisation of data in an authentic partitioned table allows an unmached performance gain.
Thus, the MERGE table is half-way between the view and the partitioned table : less convenient than first because of its structures constraints, and less performant than the second because of the poorly mastered repartition of data.
MROONGA
MROONGA is an engine under Lesser GPL licence which the greater advantage is the implementation of a fast full text search and of GIS functions including spacial indexes support. It is supposed to be included in MariaDB since version 10.0.15 what has, unfortunatly could not being confirmed by the tests done within the redaction of this book with the MariaDB 10.1.7 installation.
Anyway, this engine is available as a plugin for MySQL, on Windows, Linux and MacOS X. Moreover, it is theoretically possible to compile it from provided sources.
MyISAM
Although Mylsam stay a fast engine with a low memory footprint (only indexes are loaded in RAM), it logically should not be used in production environment considering that InnoDB now supports the full text search too. The approximative security of written data in case of crash, the impossibility to support transactionnal mode and the frequent blockages in case of concurrents accesses on the same table releguate MyIsam to the outsider rank.
However, its use is still possible on non critic data when perfomances gap warrant, or to archive static data compressed with myisamack
NDB (or NDBCluster)
NBD, also called NDBCluster, is the engine used by MySQL Cluster. This engine spreads the table storage on various nodes of data using the sharding technique, predicting redundancy. It’s a complete engine that works exclusively with memory and whose the only major drawback to this day (apart from some flaws related to architecture) is the non support of the full text search. The distribution of the data on various servers can allocate the load in case of reading but also of writting on cluster’s nodes.
Thus, adding nodes, it is possible to increase the processing and storage capabilities in a quasi-linear way., until a certain limit, especially the one inherent to volumes of data exchanged between the different elements of the cluster.
TokuDB
The TokuDB engine is a fast transactionnal engine which has the distinction of relying on the technology of fractal trees for the management of its index.
Very effective when it comes to change the structure of a table and defer changes in
indexes it also has all the features of a mature engine.
TokuDB offers several compression algorithms. It has been developped by the Tokutek society which was thereafter acquired by Percona, in 2015. TokuDB makes integral part of MariaDB and can be installed on MySQL as a plugin.
TokuDB is mentioned in the possible solutions for Big Data.
XtraDB
XtraDB is an improvment of InnoDB engine, proposed by Percona in his Percona Server and in MariaDB. It is not downloadable as an independant plugin for installation on a MySQL server that is why it was not taken into account in the comparitive below.
Summary table
The following table summarizes the main characteristics of each engine. The boxes with dashes do not apply.
| Engine | Transactions | Acid | Foreign Keys | Crash-safe | Lock | FullText | GIS types | GIS index | Partitionnement | Compression | Clustering natif | Snapshot |
| ARIA | No | No | No | Yes | Table | Yes | Yes | Yes | Yes | No | No | No |
| ARCHIVE | No | No | No | Yes | Table | No | Yes | No | Yes | Yes | No | No |
| BLACKHOLE | No | – | No | – | – | No | No | No | Yes | – | No | – |
| CSV | No | No | No | No | Table | No | No | No | No | No | No | No |
| FEDERATED | No | No | Yes | Yes | – | No | No | No | No | – | No | – |
| InnoDB | Yes | Yes | Yes | Yes | Row | Yes | Yes | Yes | Yes | No | No | Yes |
| MEMORY(HEAP) | No | No | No | No | Table | No | No | No | Yes | No | No | No |
| MERGE | No | No | No | No | Table | No | No | No | No | Yes | No | – |
| MROONGA | No | No | No | No | Column | Yes | Yes | Yes | No | No | No | No |
| NDB | Yes | Yes | Yes | Yes | Row | No | Yes | No | – | No | Yes | Yes |
| MyISAM | No | No | No | No | Table | Yes | Yes | Yes | Yes | Yes | Yes | No |
| TokuDB | Yes | Yes | No | Yes | Row | No | No | No | Yes | Yes | No | Yes |
| XtraDB | Yes | Yes | Yes | Yes | Row | Yes | Yes | No | Yes | No | Yes | Yes |
A compressed MyISAM table is only accessible in read-only mode.
Glossary
Native clustering : It is about the possibility to replicate data on several servers and to work on them synchronously, without any additional overlay software.
A clarification on this topic : if XtraDB and NDB support both clustering, they rely on different technologies that each have advantages and disadvantages.
It is therefore necessary to identify its problematic in order to choose the right solution. This topic is developed in the chapter dedicated to clustering.
- Acid : acronym for atomicity, consistency, isolation and durability. This combination of properties guarantees the reliability of a transaction.
- Snapshot : The possibility to obtain a consistant backup of the database, meaning constinstency between data.
- Crash-safe : The possibility to automatically repair a table from a log file in case of server crash.
The FEDERATED engine foreign key support works provided that federate table manage this type of constraint themselves. Considering the lack of information available on this topic, this operation was successfully tested on two federated InnoDB tables.
As part of these tests it appears that full text search does not work with the FEDERATED engine, even if federated tables rely themselves on an engine which supports the full text search