MySQL Storage Engines – Types and Details

What is a Storage Engine?

A storage engine is a software that is utilized by a database management system to create, read, and update data from a database. Most DBMS employ APIs (Application Programming Interface) to enable user interactions with the storage engines. There are two categories of storage engines; Transactional and Non-transactional storage engines.

Transactional Databases

Transactional databases imply that write operations on these databases can be undo if they are not completed. These operations are referred to as transactions. Most of the contemporary databases are transactional databases.

Non-Transactional Databases

The consequences of no Rollback/Commit are experienced. To perform a rollback operation, the user will have to do it manually using codes.

Common storage engines in MySQL

Commonly used storage engines in MySQL are InnoDB and MyISAM. The default storage engine in MySQL before version 5.5 was MyISAM. For MySQL 5.5 and later versions, the default storage engine is InnoDB. There are several other storage engines employed in MySQL. Some of them are mentioned below.

1) Memory

2) CSV

3) Merge

4) Archive

5) Federated

6) Blackhole

7) Example

Since these are the storage engines supported by MySQL, we will now discuss MyISAM and InnoDB.

MyISAM

As we have observed, MyISAM was the default storage engine used in MySQL up to version 5.5. It is comparatively faster compared to other storage engines. It is a non-transactional storage engine which was one of the major drawbacks of the MyISAM storage engine. MyISAM provides table-level locking.

Characteristics of MyISAM

There are numerous characteristics of MyISAM tables. MyISAM is stored in three files which include:

1) .frm file – It stores table format.

2) .MYD file – It is the data file.

3) .MYI file – It is the index file.

The major characteristics of MyISAM tables are as follows.

1) All data values are sorted in a manner that the low byte will be first. This is beneficial because storing data low byte first does not result in a significant speed penalty.

2) All numerical key values are sorted with the high byte first, which allows for better indexing.

3) Large files (up to 63-bit file length) are supported on file systems.

4) There is a limit of (232)2 (1.844E+19) rows.

5) The maximum number of columns per index is 16.

6) The maximum number of indexes per table is 64.

7) The maximum key length is 1000 bytes, which can be changed by recompilation.

8) When new rows are added, the index tree is split so that the high node contains only one key, which helps improve space utilization in the index tree.

MyISAM Storage Formats

MyISAM supports three different storage formats. The storage formats supported by MyISAM are as follows.

1) Fixed

2) Dynamic

3) Compressed

The first two, fixed and dynamic, are chosen automatically based on the types of columns used. The third format, compressed, can only be created with the myisampack utility. It is possible to decompress compressed tables using myisamchk –unpack. MyISAM tables are reliable because any changes made to a table by an SQL statement are written before the statement returns, but there are issues with MyISAM tables.

Problems in MyISAM tables

The common issues related to MyISAM tables are listed below.

1) Corrupted MyISAM Tables

2) Improper Closure of Tables

Let’s explore each one in detail.

1) Corrupted MyISAM Tables

Even though MyISAM tables are reliable, there is a chance that the tables can get corrupted. The following are reasons why MyISAM tables become corrupted.

1) The mysqld process is terminated in the middle of a write.

2) An unexpected computer shutdown (such as unexpected power offs).

3) Various hardware failures.

4) We are using an external program (e.g., myisamchk) to modify a table that is being modified by the server at the same time.

5) A software bug in the MySQL or MyISAM code.

These are the common reasons for MyISAM tables to become corrupted.

Symptoms of table corruption

The symptoms of table corruption include:

Receiving the following error when selecting data from a table.

“Incorrect key file for table: ‘…’. Try to repair it”

Queries not finding rows in the table or returning incomplete results.

2) Problems from Improper Table Closure

Every MyISAM index file, .MYI files, will have a counter in the header which can be used to check whether the table has been closed properly. If the following warning is received from CHECK TABLE or myisamchk, it means that this counter has gone out of sync.

“clients are using or haven’t closed the table properly”

InnoDB

InnoDB is the most widely used storage engine with transaction support. It is the default storage engine from MySQL 5.5 onwards. Unlike MyISAM, it supports row-level locking. The other important features of InnoDB include crash recovery and multi-version concurrency control. InnoDB is the only engine that provides foreign key referential integrity constraint. It is a general-purpose storage engine that offers high reliability and high performance.

InnoDB Characteristics

There are many advantages when using the InnoDB storage engine. The most important features include:

1) Its DML operations follow the ACID model with transactions featuring commit, rollback, and crash recovery capabilities to protect user data.

2) The DML operations include the following.

SELECT – retrieve data from a database.

INSERT – insert data into a table.

UPDATE – update existing data within a table.

DELETE – Delete all records from a database table.

MERGE – UPSERT operation (insert or update).

CALL – call a PL/SQL or Java subprogram.

EXPLAIN PLAN – interpretation of the data access path.

3) Row-level locking, which enhances multi-user concurrency and performance.

4) The data is arranged on disk to optimize queries based on primary keys.

5) InnoDB supports FOREIGN KEY constraints to maintain data integrity.

6) InnoDB is published under the same GNU GPL License Version 2 (of June 1991) as MySQL.

7) InnoDB supports automatic crash recovery.

Disadvantages of InnoDB Storage Engine

Increased complexity

1) Creating an InnoDB table is as simple as creating a MyISAM table.

2) InnoDB tables do not support full-text searches; it is not easy to match one or more keywords against multiple columns.

3) If your application primarily involves selecting data and performance is a priority, MyISAM tables will generally be faster and use fewer system resources.

4) For significantly large or heavily-used systems, the speed differences are unlikely to be negligible.

These are the main advantages and disadvantages of InnoDB. In most situations, InnoDB is recommended unless you have a significantly large or heavily-used system.

These are the two commonly used storage engines in MySQL. Although MySQL supports many other storage engines, these two are the most frequently used.

If you require further assistance, please contact our support department.