Choosing a Storage Engine in MySQL Database

MySQL makes use of a component called the storage engine to determine the type of data structures it uses for storing data. To the surprise of some, MySQL doesn’t store data in tables, per se. A table is a logical abstraction of the underlying data on disk or in memory. Further, the disk files could be of a different type depending on the storage engine. Which storage engine to use depends on one’s storage needs, as we’ll explore.

ACID-Compliant Tables

A single, logical operation on data stored in a database, being a transaction, ACID (Atomicity, Consistency, Isolation, and Durability) represents a set of transaction properties that a transaction must satisfy to provide a guarantee regarding data validity. InnoDB storage engine (the default) is the only non-distributed storage engine that is ACID compliant. The NDB storage engine used in the distributed NDB Cluster is also ACID-compliant.

In Memory Storage

The MEMORY storage engine stores all data in memory (RAM), and is used for fast lookup of data. InnoDB supports a buffer pool memory area to store all or most data in memory. NDB supports in-memory storage of data sets in a distributed environment.

Row Level vs Table Level Locking

Only InnoDB supports row-level locking to allow concurrent read and write operations on a subset of rows. Data Manipulation Language (DML) operations make use of row-level locking.

Table-level locking is performed at the table level and prevents concurrent changes to data. InnoDB makes use of table-level locking for Data Definition Language (DDL) operations. The MyISAM storage engine only supports this type of locking.

Indexing

Indexing is used for fast lookup of row data for specific column values.  InnoDB supports diverse types of indexes  (B-tree, clustered, secondary, full-text, spatial, adaptive hash). InnoDB is the only type that supports a clustered index, also called a table’s primary key, that stores data rows along with the primary key. Other storage engines, except ARCHIVE and CSV, support at least some types of indexing.

OLTP Workloads

The InnoDB storage engine is the best option for Online Transaction Processing (OLTP) workloads. This is due to being ACID-compliant with support for row-level locking, multi-version concurrency control (MVCC), buffering and caching, foreign keys, and clustered index.

Read Workloads

MyISAM is the obvious choice for read-only or read-mostly workloads, typically used in a data warehouse. MyISAM is not recommended for transactional queries involving both read and write operations even though it supports them. This is because the table-level locking blocks table access during the duration of a write.

Archiving Data

For archiving seldom-needed data, the ARCHIVE storage engine is the best option. It makes use of low-maintenance tables that are compact; however, unindexed. When large quantities of data need to be archived, use ARCHIVE.

Exporting and Importing Data

The CSV storage engine is used to export data to the CSV format on disk files. Use CSV when data needs to be exchanged with scripts and applications. CSV storage engine makes use of plain text files, which makes it easy to import data.

Datamart

To create a datamart environment from multiple MySQL servers, use the Federated storage engine, which presents one logical database to an end user.

As we have explored, the choice of storage engine depends on the use case. Understanding the workloads the database is going to be used for is the best way to select the right data storage engine that will give you the best results.