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
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.
| Feature | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| B-tree indexes | Yes | Yes | Yes | No | No |
| Backup/point-in-time recovery (note 1) | Yes | Yes | Yes | Yes | Yes |
| Cluster database support | No | No | No | No | Yes |
| Clustered indexes | No | No | Yes | No | No |
| Compressed data | Yes (note 2) | No | Yes | Yes | No |
| Data caches | No | N/A | Yes | No | Yes |
| Encrypted data | Yes (note 3) | Yes (note 3) | Yes (note 4) | Yes (note 3) | Yes (note 3) |
| Foreign key support | No | No | Yes | No | Yes (note 5) |
| Full-text search indexes | Yes | No | Yes (note 6) | No | No |
| Geospatial data type support | Yes | No | Yes | Yes | Yes |
| Geospatial indexing support | Yes | No | Yes (note 7) | No | No |
| Hash indexes | No | Yes | No (note 8) | No | Yes |
| Index caches | Yes | N/A | Yes | No | Yes |
| Locking granularity | Table | Table | Row | Row | Row |
| MVCC | No | No | Yes | No | No |
| Replication support (note 1) | Yes | Limited (note 9) | Yes | Yes | Yes |
| Storage limits | 256TB | RAM | 64TB | None | 384EB |
| T-tree indexes | No | No | No | No | Yes |
| Transactions | No | No | Yes | No | Yes |
| Update statistics for data dictionary | Yes | Yes | Yes | Yes | Yes |
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