MySQL Engines

In this chapter, we will talk about MySQL storage engines.

Storage engine is a software module that a database management system uses to create, read, update the data from a database.

There are two types of storage engines in MySQL: transactional and non-transactional.

For MySQL 5.5 and later:

The default storage engine is InnoDB.

For MySQL prior to version 5.5:

The default storage engine was MyISAM.

IMPORTANT: Choosing the right storage engine is an important strategic decision, which will impact future development.

InnoDB:

InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint. Oracle recommends using InnoDB for tables except for specialized use cases.

MyISAM:

MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used mostly in Web and data warehousing.

Row-Level Locking:

MySQL uses row-level locking for InnoDB tables to support simultaneous write access by multiple sessions, making them suitable for multi-user, highly concurrent, and OLTP applications.

Advantages of row-level locking:

• Fewer lock conflicts when different sessions access different rows.
• Fewer changes for rollbacks.
• Possible to lock a single row for a long time.

Table-Level Locking:

MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, permitting only one session to update those tables at a time. This locking level makes these storage engines more suitable for read-only, read-mostly, or single-user applications.

Advantages of table-level locking:

• Relatively little memory required (row locking requires memory per row or group of rows locked)
• Fast when used on a large part of the table because only a single lock is involved.
• Fast if you often do GROUP BY operations on a large part of the data or must scan the entire table frequently.

Reference: https://dev.mysql.com

Thank you.

Hello,

I love cooking. Yes ! you read it right. I love cooking however I am fond of writing technical blogs too.

I work as a Cloud Developer Support Engineer II at DigitalOcean.

I hope my articles helps you to sail out from the Ocean of the technical problems. Also on other hand helps to take a deep dive into different type of technologies.

Powered by Facebook Comments