MySQL – Storage Engines

Note: A large chunk of this article was gotten from the official MySQL documentation https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

Database Corner: Beginner's Guide to MySQL Storage Engines ...

A database engine (or storage engine) is the underlying software component that a database management system uses to create, read, update and delete data from a database.

The two most used MySQL engines are InnoDB and MyIsiam among others like Archive, NDB, Memory etc.

InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialised use cases.

InnoDB engine is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback and crash recovery capabilities to protect user data. InnoDB implements row-level locking during update/delete operations which improves performance when compared to table-level locking.

It is important to note that different engines can be used for different tables in the same database. To know the supported engines in your MySQL server, run the SHOW ENGINES command.

Choosing between different storage engines

The various engines were designed with different use cases in mind, even though the InnoDB happens to be just enough in most cases, it is also important to know the capabilities of the other storage engines.

The following table provides an overview of some storage engines provided with MySQL, with clarifying notes following the table.

FeatureMyISAMMemoryInnoDBArchiveNDB
B-tree indexesYesYesYesNoNo
Backup/point-in-time recovery (note 1)YesYesYesYesYes
Cluster database supportNoNoNoNoYes
Clustered indexesNoNoYesNoNo
Compressed dataYes (note 2)NoYesYesNo
Data cachesNoN/AYesNoYes
Encrypted dataYes (note 3)Yes (note 3)Yes (note 4)Yes (note 3)Yes (note 3)
Foreign key supportNoNoYesNoYes (note 5)
Full-text search indexesYesNoYes (note 6)NoNo
Geospatial data type supportYesNoYesYesYes
Geospatial indexing supportYesNoYes (note 7)NoNo
Hash indexesNoYesNo (note 8)NoYes
Index cachesYesN/AYesNoYes
Locking granularityTableTableRowRowRow
MVCCNoNoYesNoNo
Replication support (note 1)YesLimited (note 9)YesYesYes
Storage limits256TBRAM64TBNone384EB
T-tree indexesNoNoNoNoYes
TransactionsNoNoYesNoYes
Update statistics for data dictionaryYesYesYesYesYes

Notes:

1. Implemented in the server, rather than in the storage engine.

2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

3. Implemented in the server via encryption functions.

4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest tablespace encryption is supported.

5. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

6. InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.

7. InnoDB support for geospatial indexing is available in MySQL 5.7 and later.

8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

9. See the discussion later in this section.

REFERENCES